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