Re: getTables not returning 10 columns etc

On Dec 9, 12:48 pm, da(dot)(dot)(dot)(at)ttc-cmc(dot)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(dot)(dot)(dot)(at)postgresql(dot)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.