Обсуждение: Why is columnNames in Connection.prepareStatement(sql, columnNames[]) automatically quoted

Поиск

Список

Период

Сортировка

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

От

Balázs Zsoldos

Дата:

31 июля 2015 г., 03:32:16

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

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

От

Dave Cramer

Дата:

31 июля 2015 г., 03:47:40

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

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

От

Dave Cramer

Дата:

31 июля 2015 г., 03:49:50

Actually I think I understand your issue. How would you propose this would work for people who actually have mixed case column names ??

Dave Cramer

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

On 30 July 2015 at 20:47, 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

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

От

Balázs Zsoldos

Дата:

31 июля 2015 г., 04:03:44

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

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

От

Dave Cramer

Дата:

31 июля 2015 г., 04:07:19

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

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

От

Balázs Zsoldos

Дата:

31 июля 2015 г., 04:11:39

Hi Dave,

I am not sure. I checked other JDBC drivers. I guess I did not meet the same problem due to the reason that other databases either use upper-case letters by default or they do not even care about the columnNames parameter. E.g.: MySQL JDBC driver simply does not care about that parameter, it is the same as calling with the Statement.RETURN_GENERATED_KEYS integer parameter.

I am not sure if this is documented in any JDBC documentation. I would guess that if I want to use camel-case field names, I should quote the column names by myself like in the SQL statement. E.g.: Connection.executeUpdate("insert into \"myTable\"...", "\"myIdField\"");.

I am not sure that would be the right solution. If you do not want to change this, I can understand as both can be an acceptable behavior. In that case I will try asking the developers of the technology I use (Querydsl) to handle this on their side.

Kind regards,

Balazs

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

Actually I think I understand your issue. How would you propose this would work for people who actually have mixed case column names ??

Dave Cramer

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

On 30 July 2015 at 20:47, 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

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

От

Balázs Zsoldos

Дата:

31 июля 2015 г., 04:16:07

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.zsoldos@everit.biz

EverIT Kft.
1137 Budapest, Katona József utca 17. III. em. 2.

http://www.everit.biz I info@everit.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@fastcrypt.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.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

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

От

Dave Cramer

Дата:

31 июля 2015 г., 04:18:17

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.zsoldos@everit.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.zsoldos@everit.biz

EverIT Kft.
1137 Budapest, Katona József utca 17. III. em. 2.
http://www.everit.biz I info@everit.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@fastcrypt.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.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

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

От

Mark Rotteveel

Дата:

31 июля 2015 г., 09:41:25

On Fri, 31 Jul 2015 02:32:10 +0200, 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?

Unfortunately, the JDBC specification isn't entirely clear at this point.
When we implemented this in Jaybird, we took the exact opposite of what
PostgreSQL did: the user has to provide the literal columnnames that would
be used in the generated query. If the columnnames had to be quoted, then
the user would have to quote them explicitly. Looking back this is probably
not how I would do it now (and I might change this in a future version).

Most of the JDBC metadata API assumes storage format for things like table
and column names. For Firebird for example tableName, tablename and
TABLENAME (unquoted), are stored as TABLENAME, while "tableName" (quoted)
is stored as tableName. This means that for JDBC metadata methods you need
to provide the objectname as it is stored (ie TABLENAME for the unquoted
example, and tableName for the quoted), if we extrapolate from that, that
would mean that the columnNames array would also need to contain the names
in storage format (so if storage format doesn't include quotes, then the
driver would need to quote them).

This becomes even more obvious if you assume that methods like
Connection.prepareStatement(sql, columnNames[]) are (also) called by
applications that have used metadata-discovery to select the columns to
request (they would use the object names as returned from an earlier
metadata call like getColumns).

Long story short, it looks like the current behavior of the PostgreSQL
driver is probably correct. If you always use unquoted objectnames, you
might be able to use the metadata storesXXXIdentifiers (eg
storesLowerCaseIdentifiers() and storesUpperCaseIdentifiers()) and
supportsMixedCaseQuotedIdentifiers to decide whether you need to uppercase
or lowercase the column names.

From your description, it looks like Oracle does exactly the same thing as
the PostgreSQL driver (unquoted object names in Oracle are stored
uppercase).

Mark

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

От

dmp

Дата:

31 июля 2015 г., 18:41:04

Balázs Zsoldos 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 :-).


Hello Balázs,

    I have a generic access tool for databases and support Derby, H2, HSQL,
MariaDB, MS SQL, MSAccess, MySQL, Oracle, PostgreSQL, and SQLite. Early on
I did not quote table, column names, etc. In not doing so it became obvious
the tool would not be able to work with some users mixed upper and lower
case character definitions for identifiers.

    Most of the database do use quotes for identifiers for the reason Dave
indicated to support upper and lowercase character definitions for users.

    I also advise properly quoting field data that are not explicity defined
as numbers. Case by case context of course.

danap.

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: Why is columnNames in Connection.prepareStatement(sql, columnNames[]) automatically quoted