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

IIRC This is what hibernate actually does.

Dave Cramer

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

On 30 July 2015 at 21:16, Balázs Zsoldos <balazs(dot)zsoldos(at)everit(dot)biz> wrote:

> 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*
>>>>>
>>>>>
>>>>
>>>
>>
>