Обсуждение: 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
Hi, Thanks greatly for all of your help. I found that my "unexplained error" with building 7.1 with ant was because I already had a database named test in postgress. I just renamed the db in the Build.xml to test2 and everything went just fine. I still can not return a result set with non null values from 7.0-1.2 The TABLE_TYPE is always null. I did make sure to have no nulls in my String [] ,but even when I used a null in place of the String [] I didn't get the TABLE_TYPE But it is ok now, I got the 7.1 beta driver working and it is not having any such problems. :) Thanks for your time. Eric At 03:57 PM 3/12/01 +0000, Peter Mount wrote: >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 ")" > Frazier Consulting http://www.kwinternet.com/eric (250) 655 - 9513