Thread: getTables is this right?

Hi,

I am having problems getting info on tables with getTables. I get null
returned all of the time.

I looked at this url, which I don't know if it is very recent or not.


http://www.postgresql.org/docs/pgsql/src/interfaces/jdbc/org/postgresql/jdbc
2/DatabaseMetaData.java



public java.sql.ResultSet getTables(String catalog, String schemaPattern,
String tableNamePattern, String types[]) throws SQLException
  {
    // Handle default value for types
    if(types==null)
      types = defaultTableTypes;

    if(tableNamePattern==null)
      tableNamePattern="%";

    // the field descriptors for the new ResultSet
    Field f[] = new Field[5];
    java.sql.ResultSet r;       // ResultSet for the SQL query that we need
to do
    Vector v = new Vector();            // The new ResultSet tuple stuff

    f[0] = new Field(connection, "TABLE_CAT", iVarcharOid, 32);
    f[1] = new Field(connection, "TABLE_SCHEM", iVarcharOid, 32);
    f[2] = new Field(connection, "TABLE_NAME", iVarcharOid, 32);
    f[3] = new Field(connection, "TABLE_TYPE", iVarcharOid, 32);
    f[4] = new Field(connection, "REMARKS", iVarcharOid, 32);

I was using this code



snip:


//String [] onlytables = new String[3];
            //onlytables[0] = "TABLE";
            //onlytables[1] = "INDEX";
            ResultSet tableRS =
m_inputCon.getMetaData().getTables(null,null,"%",null);

/* I had used the String [] onlytables in place of null, but I got

        java.sql.SQLException: ERROR:  parser: parse error at or near ")"

        at org.postgresql.Connection.ExecSQL(Connection.java:393)
        at
org.postgresql.jdbc2.DatabaseMetaData.getTables(DatabaseMetaData.java:1673)
        at TypeConvert.getTableInfo(TypeConvert.java:155)
        at Replicator.TransDatabase(Replicator.java:704)
        at Replicator.jButton1ActionPerformed(Replicator.java:597)
        at Replicator.access$7(Replicator.java:592)
        at Replicator$8.actionPerformed(Replicator.java:370)
        at
javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1450)
        at javax.swing.AbstractButton$ForwardActionEvents.actionPerfor
*/

            while (tableRS.next())

            {


                String tableType = tableRS.getString(4);  // make sure that
this is a table and not an index.
                                                                              .
                System.out.println("TABLE TYPE IS: "+tableType);
                if ( tableType.compareTo( "TABLE") !=0 ) {

                    tableRS.next();
                }



                String onetable = tableRS.getString(3); // table name int is
faster that "TABLE_NAME"


snip:

Now when  I read the JDBC API It says that the TABLE_NAME is column 3 and
TABLE_TYPE should be column 4 in the getTables ResultSet.
It looks to me like that is messed up with.

 f[2] = new Field(connection, "TABLE_NAME", iVarcharOid, 32);
 f[3] = new Field(connection, "TABLE_TYPE", iVarcharOid, 32);


I may be way off here, in my guessing, but I can't get getTables table type
to return a type.

I am using the 7.0 driver with the jar named 7.0-1.2.jar and Postgress 7.0.3
on the server end.


I hope I didn't get my versions messed up, but that is the other thing I am
not certain about, what is the latest version of the driver and is it
nessicarly in  the current "latest" postgress download?


Thanks,

Eric









Frazier Consulting
http://www.kwinternet.com/eric
(250) 655 - 9513

At 23:39 09/03/01 -0800, Eric Frazier wrote:

>Hi,
>
>I am having problems getting info on tables with getTables. I get null
>returned all of the time.

Where is it returning null? It should always return a result set.

>I was using this code
>
>snip:
>
>//String [] onlytables = new String[3];
>             //onlytables[0] = "TABLE";
>             //onlytables[1] = "INDEX";
>             ResultSet tableRS =
>m_inputCon.getMetaData().getTables(null,null,"%",null);
>
>/* I had used the String [] onlytables in place of null, but I got
>
>         java.sql.SQLException: ERROR:  parser: parse error at or near ")"

Yes as you had a NULL in onlytables[2]. Change your array to String[2] and
it should work.

>Now when  I read the JDBC API It says that the TABLE_NAME is column 3 and
>TABLE_TYPE should be column 4 in the getTables ResultSet.
>It looks to me like that is messed up with.
>
>  f[2] = new Field(connection, "TABLE_NAME", iVarcharOid, 32);
>  f[3] = new Field(connection, "TABLE_TYPE", iVarcharOid, 32);

No, the array f is 0 based, ie numbered from 0..4. JDBC is 1 based, so
column 1 maps to element 0 in the array. Don't ask why they made JDBC work
from 1 and not 0, it catches everyone out.

>I may be way off here, in my guessing, but I can't get getTables table type
>to return a type.
>
>I am using the 7.0 driver with the jar named 7.0-1.2.jar and Postgress 7.0.3
>on the server end.

That should work, and it seems to match what I've got running here.


>I hope I didn't get my versions messed up, but that is the other thing I am
>not certain about, what is the latest version of the driver and is it
>nessicarly in  the current "latest" postgress download?

No, I'm trying to get things sorted, but my day job has to take priority at
the moment, hence I'm a bit behind with things.

Peter