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

One of the solutions is that I quote in every database and every statement.
If that is the only way, I can accept that. The one who has to write SQL
statements by hand on the command line might get a bit angry :-).

*Zsoldos Balázs*
Rendszertervező | Software architect

+36 70 594 9234 | balazs(dot)zsoldos(at)everit(dot)biz

*EverIT Kft.*
1137 Budapest, Katona József utca 17. III. em. 2.
http://www.everit.biz I info(at)everit(dot)biz

Ezen üzenet és annak bármely csatolt anyaga bizalmas, jogi védelem alatt
áll, a nyilvános közléstől védett. Az üzenetet kizárólag a címzett, illetve
az általa meghatalmazottak használhatják fel. Ha Ön nem az üzenet
címzettje, úgy kérjük, hogy telefonon, vagy e-mail-ben értesítse erről az
üzenet küldőjét és törölje az üzenetet, valamint annak összes csatolt
mellékletét a rendszeréből. Ha Ön nem az üzenet címzettje, abban az esetben
tilos az üzenetet vagy annak bármely csatolt mellékletét lemásolnia,
elmentenie, az üzenet tartalmát bárkivel közölnie vagy azzal visszaélnie.

This message and any attachment are confidential and are legally
privileged. It is intended solely for the use of the individual or entity
to whom it is addressed and others authorised to receive it. If you are not
the intended recipient, please telephone or email the sender and delete
this message and any attachment from your system. Please note that any
dissemination, distribution, copying or use of or reliance upon the
information contained in and transmitted with this e-mail by or to anyone
other than the recipient designated above by the sender is unauthorised and
strictly prohibited.

On Fri, Jul 31, 2015 at 3:06 AM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:

> 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(dot)zsoldos(at)everit(dot)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(at)fastcrypt(dot)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(dot)zsoldos(at)everit(dot)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*
>>>>
>>>>
>>>
>>
>