I think as a JDBC interface, the question is best asked from the
perspective of its following a JDBC standard. The following link:
http://java.sun.com/developer/onlineTraining/Database/JDBC20Intro/JDBC20.html
for the DataBase MetaData suggests that "part of the bad news" with
using this class, is that "Several of the methods, including those that
return information about database and table components, use confusing
name patterns. Depending on the database, the information may be in
upper, lower, or mixed case, and the patterns are case-sensitive. As a
result, *there are methods that need to be called to discover* how the
information is stored *before attempting to get the information*." The
most important part being that last sentence.
;;;;;;;
To me, it only makes sense to work the way it is described as working
already; that is of course, IMO. If the requirement is not to delimit
mixed case, that would eliminate any requirement for delimited names
except for embedded blanks & other non-digit characters, or names
beginning with digits. I would dislike that, because then any
un-delimited character shifting either automated to an editor or by
accident or for visual appeal, might similarly fail with /not found/.
Some non-GUI tools will automatically parse a statement to delimit
mixed-case names, and GUI tools similarly if not when presented then
when dragging into a statement or whatever. This behavior seems
consistent with those tools knowing that SQL for the database would be
unable to locate the proper identifier without its delimiters.
Here are examples that concur with case insensitive as the rule (find
on either 'mixed' or 'delimit'):
http://www.dbvis.com/products/dbvis/faq.html#6.5 ORACLE ex
http://www.dbvis.com/products/dbvis/faq.html#6.12 Many DB ex
http://docs.ingres.com/cmdref/StandardFlagsandParameters Ingres
http://datavision.sourceforge.net/DataVision/edit.html#namecase
http://archives.postgresql.org/pgsql-jdbc/2005-08/msg00040.php PG
http://www.dbforums.com/showthread.php?t=1232183 DB2
http://publib.boulder.ibm.com/infocenter/iadthelp/v7r0/index.jsp?topic=/com.ibm.etools.iseries.toolbox.doc/rzahhjdbcenhancev5r2.htm
Using mixed case in column names
IBM Toolbox for Java methods must match either column
names provided by the user or column names provided
by the application with the names that are on the
database table. In either case, when a column name is
not enclosed in quotes, IBM Toolbox for Java changes
the name to uppercase characters before matching it
against the names on the server. When the column name
is enclosed in quotes, it must exactly match the name
on the server or IBM Toolbox for Java throws an
exception.
;; I would expect the same of all identifiers; i.e. consistency
Then some other topics which suggest it is more dynamic:
http://publib.boulder.ibm.com/infocenter/weahelp/5.1/index.jsp?topic=/com.ibm.websphere.db2e.doc/dbeapr1103.html
Table 2 lists methods that can inquire of the rules; e.g.
_Method Rtn_ Method Name & Description
boolean storesMixedCaseIdentifiers()
Does this database treat mixed-case unquoted
SQL identifiers as case insensitive and store
them in mixed case?
boolean stores<<snip>>
;; With those methods, an interface may be able to infer whether the
un-delimited identifier should not be forced to upper case.
http://msdn2.microsoft.com/en-us/library/ms378652.aspx
http://javadiff.sourceforge.net/jdiff/reports/j2se131_j2se14_docs/changes/java.sql.DatabaseMetaData.html
;; The DatabaseMetadata methods defined in the above links as well
Apparently /compliance with SQL92/ requires that names be case
insensitive. "ISO Entry SQL-92 standard: mixed case (case-sensitive;
preserves case for delimited identifiers); regular identifiers are
uppercase (case-insensitive; forces all letters to uppercase)."
If it is still perceived as a defect, and/or JDBC defines that the
names are always case sensitive [for that class], then you could report
it to your service provider to get a somewhat more /official/ answer
versus what lurkers here [like me] have to offer.
Regards, Chuck
--
All comments provided "as is" with no warranties of any kind
whatsoever and may not represent positions, strategies, nor views of my
employer
Post by z***@gmail.comI have jt400 version 6 to connect to a DB2 server on AS400. In my JDBC
program I am using DatabaseMetadata.getColumns() to get the metadata
of a table. My table name is in MixedCase(eg:Employees). But this
method is not returning me any details. But if I place the table name
in double quotes("), we are getting result. For Upper case
tables(eg:EMPLOYEES), it is working as expected
But the issue here, in all other DBs including DB2 on windows (I
used db2jcc.jar), the behaviour is not this. For these DBs using JDBC
require table name only for metadata and no double quotes required for
mixed case tables.
So is this a bug with JT400 or is this the desired approach?
For me, the issue is, my same java program will not work across DBs
Hope some body can help me
The environment is iSeries v5r4.
Driver class:com.ibm.as400.access.AS400JDBCDriver
Connection STring :jdbc:as400://MISDEVO/;libraries=customer, xpsfil
The code snippet is
..
DatabaseMetadata jMetadata = connection.getMetaData();
String tableName ="Employees";
String tableSchema ="CUSTOMER";
String tableCatalog ="MISDEVO";
jMetadata.getColumns( tableCatalog,
tableSchema,
tableName,
null);