Re: Why is columnNames in Connection.prepareStatement(sql, columnNames[]) automatically quoted

I'd have to look at an example. Currently my thought here is that you provide UPPER case column names because of Oracle, and they are being quoted in pg so it doesn't find any. You would like them unquoted to be compatible with Oracle. The reason we quote them is for people who actually have mixed case names, so if we don't quote them what will they do ?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 30 July 2015 at 21:03, Balázs Zsoldos <balazs.zsoldos@everit.biz> wrote:

Hi,

As much as I saw in the source code, the original SQL statement is extended with RETURNING "columnName". This is done by calling AbstractJdbc3Statement.addReturning(...). If column names are provided, escape parameter is true, that means the column name will be quoted in the SQL statement.

 

Regards,

Balázs Zsoldos

On Fri, Jul 31, 2015 at 2:47 AM, Dave Cramer <pg@fastcrypt.com> wrote:

Hi,

Yes there is a chance to change the driver, but I need more info.  What do you mean they are automatically quoted ???

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 30 July 2015 at 20:32, Balázs Zsoldos <balazs.zsoldos@everit.biz> wrote:

Hi,

I would like to write database independent SQL statements. Everything works well, until I call Connection.prepareStatement(sql, columnNames[]).

In the SQL statement nothing is quoted and it works well. I am wondering, why the values in the columnNames are automatically quoted? Is it a bug or a feature?

More info:

I create the fields and tables in DDL without quoting them. Table and field names become upper or lower case based on the engine. E.g.: In case of Oracle, they will have upper-case names, in PostgreSQL they will have lower-case names.

If I use Oracle, I must provide the columnNames parameter for the mentioned function with upper-case letters otherwise it does not work (I think that is a bug, too, but I guess it is harder to send a patch for Oracle than PostgreSQL :-) ). If I use any other database engines (Derby, SQLServer, Hsqldb) everything works well.

If I use PostgreSQL, however, I must provide lower-case letters for the parameter as it is quoted inside. I do not think it should be quoted. If someone wants to quote those column names, it would be possible by providing the Strings with quotes like Connection.prepareStatement(sql, "\"myCamelCaseColumnName\"");

Do you think there is any chance to change this in PostgreSQL JDBC driver or shall I write separate code for Postgres?

Regards,

Balázs Zsoldos