CODEWITHSUNDEEP
javaoracle-databasejdbc
user2018791
user2018791

Reputation: 1153

get a lowercase column from ResultSet using column name

I am using Oracle 12cr1 database. Looks like I can not get a value from ResultSet using the column name if the column name is lowercase.

Create table create table "Tab" (col number, "col" varchar2(10)), so the second column is lowercase. If I call getString("col") I will get the value of the first column. If I call getString("\"col\"") I will get Invalid column name error.

However, it works fine if I use column index.

Upvotes: 2

Views: 5679

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 108941

From the JDBC API documentation of ResultSet (emphasis mine):

Column names used as input to getter methods are case insensitive. When a getter method is called with a column name and several columns have the same name, the value of the first matching column will be returned. The column name option is designed to be used when column names are used in the SQL query that generated the result set. For columns that are NOT explicitly named in the query, it is best to use column numbers. If column names are used, the programmer should take care to guarantee that they uniquely refer to the intended columns, which can be assured with the SQL AS clause.

In other words, for the API column names (or, more correctly, column labels) are case insensitive, so what you want is not possible. You will need to make your column labels unique by providing an AS column label, or you should get the column by index instead.

Upvotes: 2

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

Since one shouldn't be using SELECT * in production anyway, renaming the column in the SELECT will fix the problem:

SELECT
    col AS col1
,   "col" AS col2
FROM "Tab"

Now your two columns have different aliases, avoiding the ambiguity.

Upvotes: 0

Related Questions