Thread: getTables not returning 10 columns etc
Is there any documentation that list for the all get* methods etc where it is by design intent that the PG JDBC driver may not have a result set with all the columns as documented in the JDBC docs? For example getTables has 10 columns and the PG driver returns 5 or the documentation uses upper case names such as TABLE_CAT but PG driver returns lower case names etc.
On Thu, Dec 8, 2011 at 8:10 PM, the6campbells <the6campbells@gmail.com> wrote: > Is there any documentation that list for the all get* methods etc > where it is by design intent that the PG JDBC driver may not have a > result set with all the columns as documented in the JDBC docs? > > For example getTables has 10 columns and the PG driver returns 5 or > the documentation uses upper case names such as TABLE_CAT but PG > driver returns lower case names etc. Which documentation ? By default postgres uses lower case names. You would have to provide a test case for use to diagnose this further. > > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc
On Dec 8, 9:47 pm, p...@fastcrypt.com (Dave Cramer) wrote: > On Thu, Dec 8, 2011 at 8:10 PM, the6campbells <the6campbe...@gmail.com> wrote: > > Is there any documentation that list for the all get* methods etc > > where it is by design intent that the PG JDBC driver may not have a > > result set with all the columns as documented in the JDBC docs? > > > For example getTables has 10 columns and the PG driver returns 5 or > > the documentation uses upper case names such as TABLE_CAT but PG > > driver returns lower case names etc. > > Which documentation ? By default postgres uses lower case names. > > You would have to provide a test case for use to diagnose this further. > > > > > -- > > Sent via pgsql-jdbc mailing list (pgsql-j...@postgresql.org) > > To make changes to your subscription: > >http://www.postgresql.org/mailpref/pgsql-jdbc > > -- > Sent via pgsql-jdbc mailing list (pgsql-j...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-jdbc re documentation http://docs.oracle.com/javase/6/docs/api/java/sql/ResultSetMetaData.html re example Server: 9.0.4 Driver: PostgreSQL Native Driver: PostgreSQL 9.1 JDBC3 (build 901) ResultSet rs = meta.getTables(null, null, null, null); ResultSetMetaData rsmd = rs.getMetaData(); System.out.println(rsmd.getColumnCount()); This will print the value of 5 not 10. Change the code to for (int i = 1; i <= rsmd.getColumnCount(); i++) { System.out.println(rsmd.getColumnName(i)); } http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getTables(java.lang.String, java.lang.String, java.lang.String, java.lang.String[]) returns names as follows was expecting upper case names similar to rs.getString("TABLE_NAME") table_cat table_schem table_name table_type remarks
On Dec 8, 9:47 pm, p...@fastcrypt.com (Dave Cramer) wrote: > On Thu, Dec 8, 2011 at 8:10 PM, the6campbells <the6campbe...@gmail.com> wrote: > > Is there any documentation that list for the all get* methods etc > > where it is by design intent that the PG JDBC driver may not have a > > result set with all the columns as documented in the JDBC docs? > > > For example getTables has 10 columns and the PG driver returns 5 or > > the documentation uses upper case names such as TABLE_CAT but PG > > driver returns lower case names etc. > > Which documentation ? By default postgres uses lower case names. > > You would have to provide a test case for use to diagnose this further. > > > > > -- > > Sent via pgsql-jdbc mailing list (pgsql-j...@postgresql.org) > > To make changes to your subscription: > >http://www.postgresql.org/mailpref/pgsql-jdbc > > -- > Sent via pgsql-jdbc mailing list (pgsql-j...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-jdbc not sure my reply was sent previously so will try again ResultSet rs = meta.getTables(null, null, null, null); ResultSetMetaData rsmd = rs.getMetaData(); for (int i = 1; i <= rsmd.getColumnCount(); i++) { System.out.println(rsmd.getColumnName(i)); } http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getTables(java.lang.String, java.lang.String, java.lang.String, java.lang.String[]) was expecting 10 not 5 columns and the names of the result columns for the metadata methods to be independent in terms of how RDBMS vendors may hold their system catalog. assuming JDBC result column names are as per the docs UPPER CASE.
On Thu, Dec 8, 2011 at 7:22 PM, the6campbells <the6campbells@gmail.com> wrote:
was expecting 10 not 5 columns and the names of the result columns for
the metadata methods to be independent in terms of how RDBMS vendors
may hold their system catalog.
assuming JDBC result column names are as per the docs UPPER CASE.
The java documentation you link to certainly does seem to imply that all 10 columns should be there and doesn't seem to make allowances for any of them being optional. As for the lower case thing, I recently noticed that all of my column names come back in lower case, even if explicitly aliased to a mixed case or upper case name. A bit of googling revealed that the sql standard says nothing is case sensitive unless double quoted, so something like this:
select columnName as "columnName" from table
should return a mixed-case column name and anything else is fair game for any form of capitalization or lack thereof. Alternatively, if the table is created with column names in double quotes:
create table "MixedCase" ("MyColumn" varchar);
the case of the letters will be preserved without the explicit alias - and you will also have to use mixed case and double quotes when referring to the table and column names in any queries. Since the java docs you pointed to don't specify that the names should be anything other than the usual case-insensitive style of column names that sql says is the default, the driver clearly feels free to return them in all lower case. A quick test shows that it doesn't appear that the db even remembers your original capitalization if you don't use double quotes in the create statement, so it probably simply isn't possible for the dd or driver to offer a switch that would force the columns to come back in their original form. I have always used all-lower-case until I inherited code from elsewhere that assumed mixed case in queries would deliver mixed case in column metadata. Once I found the problem, I simply started writing code that never assumes case sensitivity with table and column names, which is arguably more correct. The unfortunate thing is that if someone should write code that assumes lower case, a bug isn't likely to be detected unless/until the behaviour of the code changes or you switch/upgrade databases. It's unfortunate that the widespread use of ORM makes database switches easy enough in many cases that they may be performed with insufficient testing of dependent code. That said, it is hard to see where someone modifying an underlying db, even just upgrading to a new version, without testing sufficient to detect this problem has anyone to blame but themselves. That's the argument I expect you'd get from the developers, anyway. Assuming the more simple argument of "all databases force everything to lower case" isn't valid. I don't know, since I've been postgres-only for so long, I can't remember what anything else does.
Sio the short answer is that unless you explicitly request them in a case-sensitive manner, all column names should be handled in a case-insensitive manner - so your code shouldn't complain about the capitalization. The missing columns does appear to be a real problem, based on my very quick perusal of the documentation, but I'm hardly an expert.
On Fri, Dec 9, 2011 at 1:50 AM, Samuel Gendler
<sgendler@ideasculptor.com> wrote:
>
>
> On Thu, Dec 8, 2011 at 7:22 PM, the6campbells <the6campbells@gmail.com>
> wrote:
>>
>>
>> was expecting 10 not 5 columns and the names of the result columns for
>> the metadata methods to be independent in terms of how RDBMS vendors
>> may hold their system catalog.
>> assuming JDBC result column names are as per the docs UPPER CASE.
>>
>
> The java documentation you link to certainly does seem to imply that all 10
> columns should be there and doesn't seem to make allowances for any of them
> being optional. As for the lower case thing, I recently noticed that all of
> my column names come back in lower case, even if explicitly aliased to a
> mixed case or upper case name. A bit of googling revealed that the sql
> standard says nothing is case sensitive unless double quoted, so something
> like this:
>
> select columnName as "columnName" from table
>
> should return a mixed-case column name and anything else is fair game for
> any form of capitalization or lack thereof. Alternatively, if the table is
> created with column names in double quotes:
>
> create table "MixedCase" ("MyColumn" varchar);
>
> the case of the letters will be preserved without the explicit alias - and
> you will also have to use mixed case and double quotes when referring to the
> table and column names in any queries. Since the java docs you pointed to
> don't specify that the names should be anything other than the usual
> case-insensitive style of column names that sql says is the default, the
> driver clearly feels free to return them in all lower case. A quick test
> shows that it doesn't appear that the db even remembers your original
> capitalization if you don't use double quotes in the create statement, so it
> probably simply isn't possible for the dd or driver to offer a switch that
> would force the columns to come back in their original form. I have always
> used all-lower-case until I inherited code from elsewhere that assumed mixed
> case in queries would deliver mixed case in column metadata. Once I found
> the problem, I simply started writing code that never assumes case
> sensitivity with table and column names, which is arguably more correct.
> The unfortunate thing is that if someone should write code that assumes
> lower case, a bug isn't likely to be detected unless/until the behaviour of
> the code changes or you switch/upgrade databases. It's unfortunate that the
> widespread use of ORM makes database switches easy enough in many cases that
> they may be performed with insufficient testing of dependent code. That
> said, it is hard to see where someone modifying an underlying db, even just
> upgrading to a new version, without testing sufficient to detect this
> problem has anyone to blame but themselves. That's the argument I expect
> you'd get from the developers, anyway. Assuming the more simple argument of
> "all databases force everything to lower case" isn't valid. I don't know,
> since I've been postgres-only for so long, I can't remember what anything
> else does.
>
> Sio the short answer is that unless you explicitly request them in a
> case-sensitive manner, all column names should be handled in a
> case-insensitive manner - so your code shouldn't complain about the
> capitalization. The missing columns does appear to be a real problem, based
> on my very quick perusal of the documentation, but I'm hardly an expert.
>
>
Yes, the missing columns are a real issue. JDBC 2.0 used to only require 5.
Dave Cramer
dave.cramer(at)credativ(dot)ca
http://www.credativ.ca
On Fri, Dec 9, 2011 at 9:12 AM, Dave Cramer <pg@fastcrypt.com> wrote: > On Fri, Dec 9, 2011 at 1:50 AM, Samuel Gendler > <sgendler@ideasculptor.com> wrote: >> >> >> On Thu, Dec 8, 2011 at 7:22 PM, the6campbells <the6campbells@gmail.com> >> wrote: >>> >>> >>> was expecting 10 not 5 columns and the names of the result columns for >>> the metadata methods to be independent in terms of how RDBMS vendors >>> may hold their system catalog. >>> assuming JDBC result column names are as per the docs UPPER CASE. >>> >> >> The java documentation you link to certainly does seem to imply that all 10 >> columns should be there and doesn't seem to make allowances for any of them >> being optional. As for the lower case thing, I recently noticed that all of >> my column names come back in lower case, even if explicitly aliased to a >> mixed case or upper case name. A bit of googling revealed that the sql >> standard says nothing is case sensitive unless double quoted, so something >> like this: > > Yes, the missing columns are a real issue. JDBC 2.0 used to only require 5. So what do these other 5 columns mean? Is there a mapping to a postgresql database ? Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca
> re documentation > http://docs.oracle.com/javase/6/docs/api/java/sql/ResultSetMetaData.html > > re example > > Server: 9.0.4 Driver: PostgreSQL Native Driver: PostgreSQL 9.1 JDBC3 > (build 901) > > ResultSet rs = meta.getTables(null, null, null, null); > ResultSetMetaData rsmd = rs.getMetaData(); > System.out.println(rsmd.getColumnCount()); > > This will print the value of 5 not 10. > > Change the code to > for (int i = 1; i <= rsmd.getColumnCount(); i++) { > System.out.println(rsmd.getColumnName(i)); > } > > http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getTables(java.lang.String, > java.lang.String, java.lang.String, java.lang.String[]) > returns names as follows was expecting upper case names similar to > rs.getString("TABLE_NAME") > table_cat > table_schem > table_name > table_type > remarks The second reference you give to the DatabaseMetaData indicates in the javadocs information for the getTables(): "Note: Some databases may not return information for all tables." Indeed the PostgreSQL JDBC does only return the first five as does MySQL, but SQLite gives all ten. As far as the column names as others have said one should not rely on any particular case defaults. I learned with the MyJSQLView application to always stipulate names with the database identifer to fully qualifier table and column names. Example: -- MyJSQLView SQL Dump -- Version: 3.31 -- WebSite: http://myjsqlview.org -- -- Host: 127.0.0.1 -- Generated On: 2011.12.09 AD at 09:30:36 MST -- SQL version: PostgreSQL 9.0.1 -- Database: key_tables -- -- -- Table structure for table "public"."keY_tAble2" -- DROP TABLE IF EXISTS "public"."keY_tAble2"; CREATE TABLE "public"."keY_tAble2" ( "Host" char(60) DEFAULT '' NOT NULL, "Db" char(64) DEFAULT '' NOT NULL, "Username" char(16) DEFAULT '' NOT NULL, "select_priv" boolean DEFAULT true NOT NULL, PRIMARY KEY ("Host","Db","Username") ); -- -- Dumping data for table "public"."keY_tAble2" -- INSERT INTO "public"."keY_tAble2" ("Host", "Db", "Username", "select_priv") VALUES('cindy', 'sample', 'danap', 't'); Output from getTables(); Table CAT: null Table Schem: public Table Name: keY_tAble2 Table Type: TABLE Remarks: null
On Dec 9, 12:48 pm, da...@ttc-cmc.net (dmp) wrote: > > re documentation > >http://docs.oracle.com/javase/6/docs/api/java/sql/ResultSetMetaData.html > > > re example > > > Server: 9.0.4 Driver: PostgreSQL Native Driver: PostgreSQL 9.1 JDBC3 > > (build 901) > > > ResultSet rs = meta.getTables(null, null, null, null); > > ResultSetMetaData rsmd = rs.getMetaData(); > > System.out.println(rsmd.getColumnCount()); > > > This will print the value of 5 not 10. > > > Change the code to > > for (int i = 1; i <= rsmd.getColumnCount(); i++) { > > System.out.println(rsmd.getColumnName(i)); > > } > > >http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.ht..., > > java.lang.String, java.lang.String, java.lang.String[]) > > returns names as follows was expecting upper case names similar to > > rs.getString("TABLE_NAME") > > table_cat > > table_schem > > table_name > > table_type > > remarks > > The second reference you give to the DatabaseMetaData indicates in the > javadocs information for the getTables(): > > "Note: Some databases may not return information for all tables." > > Indeed the PostgreSQL JDBC does only return the first five as does MySQL, > but SQLite gives all ten. As far as the column names as others have said > one should not rely on any particular case defaults. I learned with the > MyJSQLView application to always stipulate names with the database identifer > to fully qualifier table and column names. > > Example: > > -- MyJSQLView SQL Dump > -- Version: 3.31 > -- WebSite:http://myjsqlview.org > -- > -- Host: 127.0.0.1 > -- Generated On: 2011.12.09 AD at 09:30:36 MST > -- SQL version: PostgreSQL 9.0.1 > -- Database: key_tables > -- > -- > -- Table structure for table "public"."keY_tAble2" > -- > > DROP TABLE IF EXISTS "public"."keY_tAble2"; > CREATE TABLE "public"."keY_tAble2" ( > "Host" char(60) DEFAULT '' NOT NULL, > "Db" char(64) DEFAULT '' NOT NULL, > "Username" char(16) DEFAULT '' NOT NULL, > "select_priv" boolean DEFAULT true NOT NULL, > PRIMARY KEY ("Host","Db","Username") > ); > > -- > -- Dumping data for table "public"."keY_tAble2" > -- > > INSERT INTO "public"."keY_tAble2" ("Host", "Db", "Username", "select_priv") > VALUES('cindy', 'sample', 'danap', 't'); > > Output from getTables(); > > Table CAT: null > Table Schem: public > Table Name: keY_tAble2 > Table Type: TABLE > Remarks: null > > -- > Sent via pgsql-jdbc mailing list (pgsql-j...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-jdbc- Hide quoted text - > > - Show quoted text - Just to clarify my point. I am fully aware of quoted identifiers and RDBMS engines using different ways to hold their metadata names. What I was noting is that the getTables methods (like ODBC) choose to return their response as a rowset. The JDBC API documentation consistently uses a case blind (UPPER) representation for the column names of that result set. That is independent of the values in given rows in the result set which pertain to the tables etc in a database. Put another way, doesn't JDBC prescribe a canonical naming convention for the columns of the rowsets for the various metadata methods.