Thread: ResultSetMetaData Implementation Patch
Hi
The patch attached implements the following Result Set Meta Information
methods in AbstractJdbc1ResultSetMetaData:
getTableName(...)
getSchemaName(...)
isNullable(...)
getColumnName(...)
getColumnLabel(...)
Also attached is a test case for the above methods.
And also thanks to Kris Jurka for his efforts in guiding me in the do's/do
nots of JDBC Driver development.
J-A
? ResultSetMetaInfoImplementation.diff.patch
? addrsmdtest.patch
? b
? message.eml
? org/postgresql/Test.java
? org/postgresql/test/jdbc2/ResultSetMetaDataTest.java
Index: org/postgresql/core/Field.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/core/Field.java,v
retrieving revision 1.2
diff -c -r1.2 Field.java
*** org/postgresql/core/Field.java 29 May 2003 03:21:32 -0000 1.2
--- org/postgresql/core/Field.java 14 Nov 2003 12:36:17 -0000
***************
*** 13,18 ****
--- 13,19 ----
package org.postgresql.core;
import java.sql.*;
+
import org.postgresql.core.BaseConnection;
/*
***************
*** 22,28 ****
private int length; // Internal Length of this field
private int oid; // OID of the type
private int mod; // type modifier of this field
! private String name; // Name of this field
private BaseConnection conn; // Connection Instantation
--- 23,35 ----
private int length; // Internal Length of this field
private int oid; // OID of the type
private int mod; // type modifier of this field
! private String name; // Name of this field (the column label)
! private int tableOid; // OID of table ( zero if no table )
! private int positionInTable;
!
! // cache-fields
! private Integer nullable;
! private String columnName;
private BaseConnection conn; // Connection Instantation
***************
*** 57,62 ****
--- 64,90 ----
this(conn, name, oid, length, 0);
}
+ /*
+ * Construct a field based on the information fed to it.
+ *
+ * @param conn the connection this field came from
+ * @param name the name of the field
+ * @param oid the OID of the field
+ * @param length the length of the field
+ * @param tableOid the OID of the columns' table
+ * @param positionInTable the position of column in the table (first column is 1, second column is 2, etc...)
+ */
+ public Field(BaseConnection conn, String name, int oid, int length, int mod, int tableOid, int positionInTable)
+ {
+ this.conn = conn;
+ this.name = name;
+ this.oid = oid;
+ this.length = length;
+ this.mod = mod;
+ this.tableOid = tableOid;
+ this.positionInTable = positionInTable;
+ }
+
/*
* @return the oid of this Field's data type
*/
***************
*** 110,114 ****
--- 138,236 ----
{
return conn.getSQLType(oid);
}
+
+ /*
+ * @return the columns' table oid, zero if no oid available
+ */
+ public int getTableOid()
+ {
+ return tableOid;
+ }
+
+ /*
+ * @return instantiated connection
+ */
+ public BaseConnection getConn()
+ {
+ return conn;
+ }
+
+ public int getPositionInTable()
+ {
+ return positionInTable;
+ }
+
+ public int getNullable() throws SQLException
+ {
+ if (nullable != null)
+ {
+ return nullable.intValue();
+ }
+ if (tableOid == 0)
+ {
+ nullable = new Integer(java.sql.ResultSetMetaData.columnNullableUnknown);
+ return nullable.intValue();
+ }
+ java.sql.Connection con = (java.sql.Connection) conn;
+ ResultSet res = null;
+ PreparedStatement ps = null;
+ try
+ {
+ ps = con.prepareStatement("SELECT attnotnull FROM pg_catalog.pg_attribute WHERE attrelid = ? AND attnum =
?;");
+ ps.setInt(1, tableOid);
+ ps.setInt(2, positionInTable);
+ res = ps.executeQuery();
+ int nullResult = java.sql.ResultSetMetaData.columnNullableUnknown;
+ if (res.next())
+ {
+ nullResult = res.getBoolean(1) ? java.sql.ResultSetMetaData.columnNoNulls :
java.sql.ResultSetMetaData.columnNullable;
+ }
+ nullable = new Integer(nullResult);
+ return nullResult;
+ } finally
+ {
+ if (res != null)
+ res.close();
+ if (ps != null)
+ ps.close();
+ }
+ }
+
+ public String getColumnName() throws SQLException
+ {
+ if (conn.getPGProtocolVersionMajor() < 3) {
+ return name;
+ }
+ if (columnName != null)
+ {
+ return columnName;
+ }
+ if (tableOid == 0)
+ {
+ return columnName = "";
+ }
+ java.sql.Connection con = (java.sql.Connection) conn;
+ ResultSet res = null;
+ PreparedStatement ps = null;
+ try
+ {
+ ps = con.prepareStatement("SELECT attname FROM pg_catalog.pg_attribute WHERE attrelid = ? AND attnum =
?");
+ ps.setInt(1, tableOid);
+ ps.setInt(2, positionInTable);
+ res = ps.executeQuery();
+ String columnName = "";
+ if (res.next())
+ {
+ columnName = res.getString(1);
+ }
+ return columnName;
+ } finally
+ {
+ if (res != null)
+ res.close();
+ if (ps != null)
+ ps.close();
+ }
+ }
}
Index: org/postgresql/core/QueryExecutor.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/core/QueryExecutor.java,v
retrieving revision 1.27
diff -c -r1.27 QueryExecutor.java
*** org/postgresql/core/QueryExecutor.java 17 Sep 2003 08:21:36 -0000 1.27
--- org/postgresql/core/QueryExecutor.java 14 Nov 2003 12:36:19 -0000
***************
*** 21,27 ****
public class QueryExecutor
{
! //This version of execute does not take an existing result set, but
//creates a new one for the results of the query
public static BaseResultSet execute(String[] p_sqlFrags,
Object[] p_binds,
--- 21,27 ----
public class QueryExecutor
{
! //This version of execute does not take an existing result set, but
//creates a new one for the results of the query
public static BaseResultSet execute(String[] p_sqlFrags,
Object[] p_binds,
***************
*** 43,50 ****
return qe.execute();
}
! //This version of execute reuses an existing result set for the query
! //results, this is used when a result set is backed by a cursor and
//more results are fetched
public static void execute(String[] p_sqlFrags,
Object[] p_binds,
--- 43,50 ----
return qe.execute();
}
! //This version of execute reuses an existing result set for the query
! //results, this is used when a result set is backed by a cursor and
//more results are fetched
public static void execute(String[] p_sqlFrags,
Object[] p_binds,
***************
*** 111,117 ****
PSQLException error = null;
! if (pgStream == null)
{
throw new PSQLException("postgresql.con.closed", PSQLState.CONNECTION_DOES_NOT_EXIST);
}
--- 111,117 ----
PSQLException error = null;
! if (pgStream == null)
{
throw new PSQLException("postgresql.con.closed", PSQLState.CONNECTION_DOES_NOT_EXIST);
}
***************
*** 184,190 ****
case 'Z':
// read ReadyForQuery
//TODO: use size better
! if (pgStream.ReceiveIntegerR(4) != 5) throw new PSQLException("postgresql.con.setup",
PSQLState.CONNECTION_UNABLE_TO_CONNECT);
//TODO: handle transaction status
char l_tStatus = (char)pgStream.ReceiveChar();
l_endQuery = true;
--- 184,190 ----
case 'Z':
// read ReadyForQuery
//TODO: use size better
! if (pgStream.ReceiveIntegerR(4) != 5) throw new PSQLException("postgresql.con.setup",
PSQLState.CONNECTION_UNABLE_TO_CONNECT);
//TODO: handle transaction status
char l_tStatus = (char)pgStream.ReceiveChar();
l_endQuery = true;
***************
*** 201,211 ****
//if an existing result set was passed in reuse it, else
//create a new one
! if (rs != null)
{
rs.reInit(fields, tuples, status, update_count, insert_oid, binaryCursor);
}
! else
{
rs = statement.createResultSet(fields, tuples, status, update_count, insert_oid, binaryCursor);
}
--- 201,211 ----
//if an existing result set was passed in reuse it, else
//create a new one
! if (rs != null)
{
rs.reInit(fields, tuples, status, update_count, insert_oid, binaryCursor);
}
! else
{
rs = statement.createResultSet(fields, tuples, status, update_count, insert_oid, binaryCursor);
}
***************
*** 218,224 ****
StringBuffer errorMessage = null;
! if (pgStream == null)
{
throw new PSQLException("postgresql.con.closed", PSQLState.CONNECTION_DOES_NOT_EXIST);
}
--- 218,224 ----
StringBuffer errorMessage = null;
! if (pgStream == null)
{
throw new PSQLException("postgresql.con.closed", PSQLState.CONNECTION_DOES_NOT_EXIST);
}
***************
*** 291,301 ****
//if an existing result set was passed in reuse it, else
//create a new one
! if (rs != null)
{
rs.reInit(fields, tuples, status, update_count, insert_oid, binaryCursor);
}
! else
{
rs = statement.createResultSet(fields, tuples, status, update_count, insert_oid, binaryCursor);
}
--- 291,301 ----
//if an existing result set was passed in reuse it, else
//create a new one
! if (rs != null)
{
rs.reInit(fields, tuples, status, update_count, insert_oid, binaryCursor);
}
! else
{
rs = statement.createResultSet(fields, tuples, status, update_count, insert_oid, binaryCursor);
}
***************
*** 414,420 ****
//TODO: better handle the msg len
int l_len = pgStream.ReceiveIntegerR(4);
//read l_len -5 bytes (-4 for l_len and -1 for trailing \0)
! status = connection.getEncoding().decode(pgStream.Receive(l_len-5));
//now read and discard the trailing \0
pgStream.Receive(1);
try
--- 414,420 ----
//TODO: better handle the msg len
int l_len = pgStream.ReceiveIntegerR(4);
//read l_len -5 bytes (-4 for l_len and -1 for trailing \0)
! status = connection.getEncoding().decode(pgStream.Receive(l_len-5));
//now read and discard the trailing \0
pgStream.Receive(1);
try
***************
*** 468,474 ****
private void receiveFieldsV3() throws SQLException
{
//TODO: use the msgSize
- //TODO: use the tableOid, and tablePosition
if (fields != null)
throw new PSQLException("postgresql.con.multres", PSQLState.CONNECTION_FAILURE);
int l_msgSize = pgStream.ReceiveIntegerR(4);
--- 468,473 ----
***************
*** 477,491 ****
for (int i = 0; i < fields.length; i++)
{
! String typeName = pgStream.ReceiveString(connection.getEncoding());
int tableOid = pgStream.ReceiveIntegerR(4);
! int tablePosition = pgStream.ReceiveIntegerR(2);
int typeOid = pgStream.ReceiveIntegerR(4);
int typeLength = pgStream.ReceiveIntegerR(2);
int typeModifier = pgStream.ReceiveIntegerR(4);
int formatType = pgStream.ReceiveIntegerR(2);
! //TODO: use the extra values coming back
! fields[i] = new Field(connection, typeName, typeOid, typeLength, typeModifier);
}
}
/*
--- 476,489 ----
for (int i = 0; i < fields.length; i++)
{
! String columnLabel = pgStream.ReceiveString(connection.getEncoding());
int tableOid = pgStream.ReceiveIntegerR(4);
! short positionInTable = (short)pgStream.ReceiveIntegerR(2);
int typeOid = pgStream.ReceiveIntegerR(4);
int typeLength = pgStream.ReceiveIntegerR(2);
int typeModifier = pgStream.ReceiveIntegerR(4);
int formatType = pgStream.ReceiveIntegerR(2);
! fields[i] = new Field(connection, columnLabel, typeOid, typeLength, typeModifier, tableOid,
positionInTable);
}
}
/*
***************
*** 501,511 ****
for (int i = 0; i < fields.length; i++)
{
! String typeName = pgStream.ReceiveString(connection.getEncoding());
int typeOid = pgStream.ReceiveIntegerR(4);
int typeLength = pgStream.ReceiveIntegerR(2);
int typeModifier = pgStream.ReceiveIntegerR(4);
! fields[i] = new Field(connection, typeName, typeOid, typeLength, typeModifier);
}
}
}
--- 499,509 ----
for (int i = 0; i < fields.length; i++)
{
! String columnLabel = pgStream.ReceiveString(connection.getEncoding());
int typeOid = pgStream.ReceiveIntegerR(4);
int typeLength = pgStream.ReceiveIntegerR(2);
int typeModifier = pgStream.ReceiveIntegerR(4);
! fields[i] = new Field(connection, columnLabel, typeOid, typeLength, typeModifier);
}
}
}
Index: org/postgresql/jdbc1/AbstractJdbc1ResultSetMetaData.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSetMetaData.java,v
retrieving revision 1.6
diff -c -r1.6 AbstractJdbc1ResultSetMetaData.java
*** org/postgresql/jdbc1/AbstractJdbc1ResultSetMetaData.java 13 Sep 2003 04:02:15 -0000 1.6
--- org/postgresql/jdbc1/AbstractJdbc1ResultSetMetaData.java 14 Nov 2003 12:36:21 -0000
***************
*** 4,19 ****
import org.postgresql.core.Field;
import org.postgresql.util.PSQLException;
import org.postgresql.util.PSQLState;
! import java.sql.SQLException;
! import java.sql.Types;
! import java.util.Vector;
public abstract class AbstractJdbc1ResultSetMetaData
{
-
protected Vector rows;
protected Field[] fields;
/*
* Initialise for a result with a tuple set and
* a field descriptor set
--- 4,21 ----
import org.postgresql.core.Field;
import org.postgresql.util.PSQLException;
import org.postgresql.util.PSQLState;
!
! import java.sql.*;
! import java.util.*;
public abstract class AbstractJdbc1ResultSetMetaData
{
protected Vector rows;
protected Field[] fields;
+ private Hashtable tableNameCache;
+ private Hashtable schemaNameCache;
+
/*
* Initialise for a result with a tuple set and
* a field descriptor set
***************
*** 131,143 ****
*/
public int isNullable(int column) throws SQLException
{
! /*
! * TODO This needs a real implementation, taking into account columns
! * defined with NOT NULL or PRIMARY KEY, CHECK constraints, views,
! * functions etc.
! */
! return java.sql.ResultSetMetaData.columnNullableUnknown;
! }
/*
* Is the column a signed number? In PostgreSQL, all numbers
--- 133,141 ----
*/
public int isNullable(int column) throws SQLException
{
! Field field = getField(column);
! return field.getNullable();
! }
/*
* Is the column a signed number? In PostgreSQL, all numbers
***************
*** 225,240 ****
}
/*
- * What is the suggested column title for use in printouts and
- * displays? We suggest the ColumnName!
- *
* @param column the first column is 1, the second is 2, etc.
* @return the column label
* @exception SQLException if a database access error occurs
*/
public String getColumnLabel(int column) throws SQLException
{
! return getColumnName(column);
}
/*
--- 223,238 ----
}
/*
* @param column the first column is 1, the second is 2, etc.
* @return the column label
* @exception SQLException if a database access error occurs
*/
public String getColumnLabel(int column) throws SQLException
{
! Field f = getField(column);
! if (f != null)
! return f.getName();
! return "field" + column;
}
/*
***************
*** 246,270 ****
*/
public String getColumnName(int column) throws SQLException
{
! Field f = getField(column);
! if (f != null)
! return f.getName();
! return "field" + column;
}
/*
- * What is a column's table's schema? This relies on us knowing
- * the table name....which I don't know how to do as yet. The
- * JDBC specification allows us to return "" if this is not
- * applicable.
- *
* @param column the first column is 1, the second is 2...
! * @return the Schema
* @exception SQLException if a database access error occurs
*/
public String getSchemaName(int column) throws SQLException
{
! return "";
}
/*
--- 244,300 ----
*/
public String getColumnName(int column) throws SQLException
{
! Field field = getField(column);
! return field.getColumnName();
}
/*
* @param column the first column is 1, the second is 2...
! * @return the Schema Name
* @exception SQLException if a database access error occurs
*/
public String getSchemaName(int column) throws SQLException
{
! Field field = getField(column);
! if (field.getTableOid() == 0)
! {
! return "";
! }
! Integer tableOid = new Integer(field.getTableOid());
! if (schemaNameCache == null)
! {
! schemaNameCache = new Hashtable();
! }
! String schemaName = (String) schemaNameCache.get(tableOid);
! if (schemaName != null)
! {
! return schemaName;
! } else
! {
! java.sql.Connection con = (java.sql.Connection) field.getConn();
! ResultSet res = null;
! PreparedStatement ps = null;
! try
! {
! String sql = "SELECT n.nspname FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE n.oid =
c.relnamespaceAND c.oid = ?;";
! ps = con.prepareStatement(sql);
! ps.setInt(1, tableOid.intValue());
! res = ps.executeQuery();
! schemaName = "";
! if (res.next())
! {
! schemaName = res.getString(1);
! }
! schemaNameCache.put(tableOid, schemaName);
! return schemaName;
! } finally
! {
! if (res != null)
! res.close();
! if (ps != null)
! ps.close();
! }
! }
}
/*
***************
*** 341,357 ****
}
/*
- * Whats a column's table's name? How do I find this out? Both
- * getSchemaName() and getCatalogName() rely on knowing the table
- * Name, so we need this before we can work on them.
- *
* @param column the first column is 1, the second is 2...
* @return column name, or "" if not applicable
* @exception SQLException if a database access error occurs
*/
public String getTableName(int column) throws SQLException
{
! return "";
}
/*
--- 371,421 ----
}
/*
* @param column the first column is 1, the second is 2...
* @return column name, or "" if not applicable
* @exception SQLException if a database access error occurs
*/
public String getTableName(int column) throws SQLException
{
! Field field = getField(column);
! if (field.getTableOid() == 0)
! {
! return "";
! }
! Integer tableOid = new Integer(field.getTableOid());
! if (tableNameCache == null)
! {
! tableNameCache = new Hashtable();
! }
! String tableName = (String) tableNameCache.get(tableOid);
! if (tableName != null)
! {
! return tableName;
! } else
! {
! java.sql.Connection con = (java.sql.Connection) field.getConn();
! ResultSet res = null;
! PreparedStatement ps = null;
! try
! {
! ps = con.prepareStatement("SELECT relname FROM pg_catalog.pg_class WHERE oid = ?");
! ps.setInt(1, tableOid.intValue());
! res = ps.executeQuery();
! tableName = "";
! if (res.next())
! {
! tableName = res.getString(1);
! }
! tableNameCache.put(tableOid, tableName);
! return tableName;
! } finally
! {
! if (res != null)
! res.close();
! if (ps != null)
! ps.close();
! }
! }
}
/*
Index: org/postgresql/jdbc2/AbstractJdbc2ResultSetMetaData.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSetMetaData.java,v
retrieving revision 1.5
diff -c -r1.5 AbstractJdbc2ResultSetMetaData.java
*** org/postgresql/jdbc2/AbstractJdbc2ResultSetMetaData.java 13 Sep 2003 04:02:15 -0000 1.5
--- org/postgresql/jdbc2/AbstractJdbc2ResultSetMetaData.java 14 Nov 2003 12:36:23 -0000
***************
*** 119,141 ****
}
/*
- * Indicates the nullability of values in the designated column.
- *
- * @param column the first column is 1, the second is 2...
- * @return one of the columnNullable values
- * @exception SQLException if a database access error occurs
- */
- public int isNullable(int column) throws SQLException
- {
- /*
- * TODO This needs a real implementation, taking into account columns
- * defined with NOT NULL or PRIMARY KEY, CHECK constraints, views,
- * functions etc.
- */
- return java.sql.ResultSetMetaData.columnNullableUnknown;
- }
-
- /*
* Is the column a signed number? In PostgreSQL, all numbers
* are signed, so this is trivial. However, strings are not
* signed (duh!)
--- 119,124 ----
***************
*** 221,269 ****
}
/*
- * What is the suggested column title for use in printouts and
- * displays? We suggest the ColumnName!
- *
- * @param column the first column is 1, the second is 2, etc.
- * @return the column label
- * @exception SQLException if a database access error occurs
- */
- public String getColumnLabel(int column) throws SQLException
- {
- return getColumnName(column);
- }
-
- /*
- * What's a column's name?
- *
- * @param column the first column is 1, the second is 2, etc.
- * @return the column name
- * @exception SQLException if a database access error occurs
- */
- public String getColumnName(int column) throws SQLException
- {
- Field f = getField(column);
- if (f != null)
- return f.getName();
- return "field" + column;
- }
-
- /*
- * What is a column's table's schema? This relies on us knowing
- * the table name....which I don't know how to do as yet. The
- * JDBC specification allows us to return "" if this is not
- * applicable.
- *
- * @param column the first column is 1, the second is 2...
- * @return the Schema
- * @exception SQLException if a database access error occurs
- */
- public String getSchemaName(int column) throws SQLException
- {
- return "";
- }
-
- /*
* What is a column's number of decimal digits.
*
* @param column the first column is 1, the second is 2...
--- 204,209 ----
***************
*** 336,354 ****
}
}
- /*
- * Whats a column's table's name? How do I find this out? Both
- * getSchemaName() and getCatalogName() rely on knowing the table
- * Name, so we need this before we can work on them.
- *
- * @param column the first column is 1, the second is 2...
- * @return column name, or "" if not applicable
- * @exception SQLException if a database access error occurs
- */
- public String getTableName(int column) throws SQLException
- {
- return "";
- }
/*
* What's a column's table's catalog name? As with getSchemaName(),
--- 276,281 ----
Index: org/postgresql/test/jdbc2/Jdbc2TestSuite.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/test/jdbc2/Jdbc2TestSuite.java,v
retrieving revision 1.7
diff -c -r1.7 Jdbc2TestSuite.java
*** org/postgresql/test/jdbc2/Jdbc2TestSuite.java 22 Sep 2003 05:38:01 -0000 1.7
--- org/postgresql/test/jdbc2/Jdbc2TestSuite.java 14 Nov 2003 12:36:24 -0000
***************
*** 35,40 ****
--- 35,41 ----
// ResultSet
suite.addTestSuite(ResultSetTest.class);
+ suite.addTestSuite(ResultSetMetaDataTest.class);
// Time, Date, Timestamp
suite.addTestSuite(DateTest.class);
package org.postgresql.test.jdbc2;
import org.postgresql.test.TestUtil;
import junit.framework.TestCase;
import java.sql.*;
public class ResultSetMetaDataTest extends TestCase
{
private Connection conn;
private Statement stmt;
private ResultSet rs;
private ResultSetMetaData rsmd;
public ResultSetMetaDataTest(String name)
{
super(name);
}
protected void setUp() throws Exception
{
conn = TestUtil.openDB();
TestUtil.createTable(conn, "rsmd1", "a int primary key, b text, c decimal(10,2)");
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT a,b,c,a+c as total,oid FROM rsmd1");
rsmd = rs.getMetaData();
}
protected void tearDown() throws Exception
{
TestUtil.dropTable(conn, "rsmd1");
TestUtil.closeDB(conn);
rs.close();
stmt.close();
rsmd = null;
rs = null;
stmt = null;
conn = null;
}
public void testGetColumnCount() throws SQLException {
assertTrue(rsmd.getColumnCount() == 5);
}
public void testGetColumnLabel() throws SQLException {
assertTrue(rsmd.getColumnLabel(1).equals("a"));
assertTrue(rsmd.getColumnLabel(4).equals("total"));
}
public void testGetColumnName() throws SQLException {
assertTrue(rsmd.getColumnName(1).equals("a"));
assertTrue(rsmd.getColumnName(5).equals("oid"));
System.out.println(">>"+rsmd.getColumnName(4));
if (TestUtil.haveMinimumServerVersion(conn,"7.4")) {
assertTrue(rsmd.getColumnName(4).equals(""));
}
}
public void testGetColumnType() throws SQLException {
assertTrue(rsmd.getColumnType(1) == Types.INTEGER);
assertTrue(rsmd.getColumnType(2) == Types.VARCHAR);
}
public void testGetColumnTypeName() throws SQLException {
assertTrue(rsmd.getColumnTypeName(1).equals("int4"));
assertTrue(rsmd.getColumnTypeName(2).equals("text"));
}
public void testGetPrecision() throws SQLException {
assertTrue(rsmd.getPrecision(3) == 10);
}
public void testGetScale() throws SQLException {
assertTrue(rsmd.getScale(3) == 2);
}
public void testGetSchemaName() throws SQLException {
if (TestUtil.haveMinimumServerVersion(conn,"7.4")) {
assertTrue(rsmd.getSchemaName(1).equals("public"));
assertTrue(rsmd.getSchemaName(4).equals(""));
}
}
public void testGetTableName() throws SQLException {
if (TestUtil.haveMinimumServerVersion(conn,"7.4")) {
assertTrue(rsmd.getTableName(1).equals("rsmd1"));
assertTrue(rsmd.getTableName(4).equals(""));
}
}
public void testIsNullable() throws SQLException {
if (TestUtil.haveMinimumServerVersion(conn,"7.4")) {
assertTrue(rsmd.isNullable(1) == ResultSetMetaData.columnNoNulls);
assertTrue(rsmd.isNullable(2) == ResultSetMetaData.columnNullable);
assertTrue(rsmd.isNullable(4) == ResultSetMetaData.columnNullableUnknown);
} else {
assertTrue(rsmd.isNullable(1) == ResultSetMetaData.columnNullableUnknown);
}
}
}
Jan-Andre le Roux wrote:
> Hi
>
> The patch attached implements the following Result Set Meta Information
> methods in AbstractJdbc1ResultSetMetaData:
>
> getTableName(...)
> getSchemaName(...)
> isNullable(...)
> getColumnName(...)
> getColumnLabel(...)
>
> Also attached is a test case for the above methods.
>
> And also thanks to Kris Jurka for his efforts in guiding me in the
> do's/do nots of JDBC Driver development.
>
>
> J-A
disregard previous patch, attached is the newest patch
J-A
? ResultSetMetaInfoImplementation.diff.patch
? addrsmdtest.patch
? b
? message.eml
? org/postgresql/Test.java
? org/postgresql/test/jdbc2/ResultSetMetaDataTest.java
Index: org/postgresql/core/Field.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/core/Field.java,v
retrieving revision 1.2
diff -c -r1.2 Field.java
*** org/postgresql/core/Field.java 29 May 2003 03:21:32 -0000 1.2
--- org/postgresql/core/Field.java 14 Nov 2003 13:10:30 -0000
***************
*** 13,18 ****
--- 13,19 ----
package org.postgresql.core;
import java.sql.*;
+
import org.postgresql.core.BaseConnection;
/*
***************
*** 22,28 ****
private int length; // Internal Length of this field
private int oid; // OID of the type
private int mod; // type modifier of this field
! private String name; // Name of this field
private BaseConnection conn; // Connection Instantation
--- 23,35 ----
private int length; // Internal Length of this field
private int oid; // OID of the type
private int mod; // type modifier of this field
! private String name; // Name of this field (the column label)
! private int tableOid; // OID of table ( zero if no table )
! private int positionInTable;
!
! // cache-fields
! private Integer nullable;
! private String columnName;
private BaseConnection conn; // Connection Instantation
***************
*** 57,62 ****
--- 64,90 ----
this(conn, name, oid, length, 0);
}
+ /*
+ * Construct a field based on the information fed to it.
+ *
+ * @param conn the connection this field came from
+ * @param name the name of the field
+ * @param oid the OID of the field
+ * @param length the length of the field
+ * @param tableOid the OID of the columns' table
+ * @param positionInTable the position of column in the table (first column is 1, second column is 2, etc...)
+ */
+ public Field(BaseConnection conn, String name, int oid, int length, int mod, int tableOid, int positionInTable)
+ {
+ this.conn = conn;
+ this.name = name;
+ this.oid = oid;
+ this.length = length;
+ this.mod = mod;
+ this.tableOid = tableOid;
+ this.positionInTable = positionInTable;
+ }
+
/*
* @return the oid of this Field's data type
*/
***************
*** 110,114 ****
--- 138,236 ----
{
return conn.getSQLType(oid);
}
+
+ /*
+ * @return the columns' table oid, zero if no oid available
+ */
+ public int getTableOid()
+ {
+ return tableOid;
+ }
+
+ /*
+ * @return instantiated connection
+ */
+ public BaseConnection getConn()
+ {
+ return conn;
+ }
+
+ public int getPositionInTable()
+ {
+ return positionInTable;
+ }
+
+ public int getNullable() throws SQLException
+ {
+ if (nullable != null)
+ {
+ return nullable.intValue();
+ }
+ if (tableOid == 0)
+ {
+ nullable = new Integer(java.sql.ResultSetMetaData.columnNullableUnknown);
+ return nullable.intValue();
+ }
+ java.sql.Connection con = (java.sql.Connection) conn;
+ ResultSet res = null;
+ PreparedStatement ps = null;
+ try
+ {
+ ps = con.prepareStatement("SELECT attnotnull FROM pg_catalog.pg_attribute WHERE attrelid = ? AND attnum =
?;");
+ ps.setInt(1, tableOid);
+ ps.setInt(2, positionInTable);
+ res = ps.executeQuery();
+ int nullResult = java.sql.ResultSetMetaData.columnNullableUnknown;
+ if (res.next())
+ {
+ nullResult = res.getBoolean(1) ? java.sql.ResultSetMetaData.columnNoNulls :
java.sql.ResultSetMetaData.columnNullable;
+ }
+ nullable = new Integer(nullResult);
+ return nullResult;
+ } finally
+ {
+ if (res != null)
+ res.close();
+ if (ps != null)
+ ps.close();
+ }
+ }
+
+ public String getColumnName() throws SQLException
+ {
+ if (conn.getPGProtocolVersionMajor() < 3) {
+ return name;
+ }
+ if (columnName != null)
+ {
+ return columnName;
+ }
+ if (tableOid == 0)
+ {
+ return columnName = "";
+ }
+ java.sql.Connection con = (java.sql.Connection) conn;
+ ResultSet res = null;
+ PreparedStatement ps = null;
+ try
+ {
+ ps = con.prepareStatement("SELECT attname FROM pg_catalog.pg_attribute WHERE attrelid = ? AND attnum =
?");
+ ps.setInt(1, tableOid);
+ ps.setInt(2, positionInTable);
+ res = ps.executeQuery();
+ String columnName = "";
+ if (res.next())
+ {
+ columnName = res.getString(1);
+ }
+ return columnName;
+ } finally
+ {
+ if (res != null)
+ res.close();
+ if (ps != null)
+ ps.close();
+ }
+ }
}
Index: org/postgresql/core/QueryExecutor.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/core/QueryExecutor.java,v
retrieving revision 1.27
diff -c -r1.27 QueryExecutor.java
*** org/postgresql/core/QueryExecutor.java 17 Sep 2003 08:21:36 -0000 1.27
--- org/postgresql/core/QueryExecutor.java 14 Nov 2003 13:10:32 -0000
***************
*** 21,27 ****
public class QueryExecutor
{
! //This version of execute does not take an existing result set, but
//creates a new one for the results of the query
public static BaseResultSet execute(String[] p_sqlFrags,
Object[] p_binds,
--- 21,27 ----
public class QueryExecutor
{
! //This version of execute does not take an existing result set, but
//creates a new one for the results of the query
public static BaseResultSet execute(String[] p_sqlFrags,
Object[] p_binds,
***************
*** 43,50 ****
return qe.execute();
}
! //This version of execute reuses an existing result set for the query
! //results, this is used when a result set is backed by a cursor and
//more results are fetched
public static void execute(String[] p_sqlFrags,
Object[] p_binds,
--- 43,50 ----
return qe.execute();
}
! //This version of execute reuses an existing result set for the query
! //results, this is used when a result set is backed by a cursor and
//more results are fetched
public static void execute(String[] p_sqlFrags,
Object[] p_binds,
***************
*** 111,117 ****
PSQLException error = null;
! if (pgStream == null)
{
throw new PSQLException("postgresql.con.closed", PSQLState.CONNECTION_DOES_NOT_EXIST);
}
--- 111,117 ----
PSQLException error = null;
! if (pgStream == null)
{
throw new PSQLException("postgresql.con.closed", PSQLState.CONNECTION_DOES_NOT_EXIST);
}
***************
*** 184,190 ****
case 'Z':
// read ReadyForQuery
//TODO: use size better
! if (pgStream.ReceiveIntegerR(4) != 5) throw new PSQLException("postgresql.con.setup",
PSQLState.CONNECTION_UNABLE_TO_CONNECT);
//TODO: handle transaction status
char l_tStatus = (char)pgStream.ReceiveChar();
l_endQuery = true;
--- 184,190 ----
case 'Z':
// read ReadyForQuery
//TODO: use size better
! if (pgStream.ReceiveIntegerR(4) != 5) throw new PSQLException("postgresql.con.setup",
PSQLState.CONNECTION_UNABLE_TO_CONNECT);
//TODO: handle transaction status
char l_tStatus = (char)pgStream.ReceiveChar();
l_endQuery = true;
***************
*** 201,211 ****
//if an existing result set was passed in reuse it, else
//create a new one
! if (rs != null)
{
rs.reInit(fields, tuples, status, update_count, insert_oid, binaryCursor);
}
! else
{
rs = statement.createResultSet(fields, tuples, status, update_count, insert_oid, binaryCursor);
}
--- 201,211 ----
//if an existing result set was passed in reuse it, else
//create a new one
! if (rs != null)
{
rs.reInit(fields, tuples, status, update_count, insert_oid, binaryCursor);
}
! else
{
rs = statement.createResultSet(fields, tuples, status, update_count, insert_oid, binaryCursor);
}
***************
*** 218,224 ****
StringBuffer errorMessage = null;
! if (pgStream == null)
{
throw new PSQLException("postgresql.con.closed", PSQLState.CONNECTION_DOES_NOT_EXIST);
}
--- 218,224 ----
StringBuffer errorMessage = null;
! if (pgStream == null)
{
throw new PSQLException("postgresql.con.closed", PSQLState.CONNECTION_DOES_NOT_EXIST);
}
***************
*** 291,301 ****
//if an existing result set was passed in reuse it, else
//create a new one
! if (rs != null)
{
rs.reInit(fields, tuples, status, update_count, insert_oid, binaryCursor);
}
! else
{
rs = statement.createResultSet(fields, tuples, status, update_count, insert_oid, binaryCursor);
}
--- 291,301 ----
//if an existing result set was passed in reuse it, else
//create a new one
! if (rs != null)
{
rs.reInit(fields, tuples, status, update_count, insert_oid, binaryCursor);
}
! else
{
rs = statement.createResultSet(fields, tuples, status, update_count, insert_oid, binaryCursor);
}
***************
*** 414,420 ****
//TODO: better handle the msg len
int l_len = pgStream.ReceiveIntegerR(4);
//read l_len -5 bytes (-4 for l_len and -1 for trailing \0)
! status = connection.getEncoding().decode(pgStream.Receive(l_len-5));
//now read and discard the trailing \0
pgStream.Receive(1);
try
--- 414,420 ----
//TODO: better handle the msg len
int l_len = pgStream.ReceiveIntegerR(4);
//read l_len -5 bytes (-4 for l_len and -1 for trailing \0)
! status = connection.getEncoding().decode(pgStream.Receive(l_len-5));
//now read and discard the trailing \0
pgStream.Receive(1);
try
***************
*** 468,474 ****
private void receiveFieldsV3() throws SQLException
{
//TODO: use the msgSize
- //TODO: use the tableOid, and tablePosition
if (fields != null)
throw new PSQLException("postgresql.con.multres", PSQLState.CONNECTION_FAILURE);
int l_msgSize = pgStream.ReceiveIntegerR(4);
--- 468,473 ----
***************
*** 477,491 ****
for (int i = 0; i < fields.length; i++)
{
! String typeName = pgStream.ReceiveString(connection.getEncoding());
int tableOid = pgStream.ReceiveIntegerR(4);
! int tablePosition = pgStream.ReceiveIntegerR(2);
int typeOid = pgStream.ReceiveIntegerR(4);
int typeLength = pgStream.ReceiveIntegerR(2);
int typeModifier = pgStream.ReceiveIntegerR(4);
int formatType = pgStream.ReceiveIntegerR(2);
! //TODO: use the extra values coming back
! fields[i] = new Field(connection, typeName, typeOid, typeLength, typeModifier);
}
}
/*
--- 476,489 ----
for (int i = 0; i < fields.length; i++)
{
! String columnLabel = pgStream.ReceiveString(connection.getEncoding());
int tableOid = pgStream.ReceiveIntegerR(4);
! short positionInTable = (short)pgStream.ReceiveIntegerR(2);
int typeOid = pgStream.ReceiveIntegerR(4);
int typeLength = pgStream.ReceiveIntegerR(2);
int typeModifier = pgStream.ReceiveIntegerR(4);
int formatType = pgStream.ReceiveIntegerR(2);
! fields[i] = new Field(connection, columnLabel, typeOid, typeLength, typeModifier, tableOid,
positionInTable);
}
}
/*
***************
*** 501,511 ****
for (int i = 0; i < fields.length; i++)
{
! String typeName = pgStream.ReceiveString(connection.getEncoding());
int typeOid = pgStream.ReceiveIntegerR(4);
int typeLength = pgStream.ReceiveIntegerR(2);
int typeModifier = pgStream.ReceiveIntegerR(4);
! fields[i] = new Field(connection, typeName, typeOid, typeLength, typeModifier);
}
}
}
--- 499,509 ----
for (int i = 0; i < fields.length; i++)
{
! String columnLabel = pgStream.ReceiveString(connection.getEncoding());
int typeOid = pgStream.ReceiveIntegerR(4);
int typeLength = pgStream.ReceiveIntegerR(2);
int typeModifier = pgStream.ReceiveIntegerR(4);
! fields[i] = new Field(connection, columnLabel, typeOid, typeLength, typeModifier);
}
}
}
Index: org/postgresql/jdbc1/AbstractJdbc1ResultSetMetaData.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSetMetaData.java,v
retrieving revision 1.6
diff -c -r1.6 AbstractJdbc1ResultSetMetaData.java
*** org/postgresql/jdbc1/AbstractJdbc1ResultSetMetaData.java 13 Sep 2003 04:02:15 -0000 1.6
--- org/postgresql/jdbc1/AbstractJdbc1ResultSetMetaData.java 14 Nov 2003 13:10:34 -0000
***************
*** 4,19 ****
import org.postgresql.core.Field;
import org.postgresql.util.PSQLException;
import org.postgresql.util.PSQLState;
! import java.sql.SQLException;
! import java.sql.Types;
! import java.util.Vector;
public abstract class AbstractJdbc1ResultSetMetaData
{
-
protected Vector rows;
protected Field[] fields;
/*
* Initialise for a result with a tuple set and
* a field descriptor set
--- 4,21 ----
import org.postgresql.core.Field;
import org.postgresql.util.PSQLException;
import org.postgresql.util.PSQLState;
!
! import java.sql.*;
! import java.util.*;
public abstract class AbstractJdbc1ResultSetMetaData
{
protected Vector rows;
protected Field[] fields;
+ private Hashtable tableNameCache;
+ private Hashtable schemaNameCache;
+
/*
* Initialise for a result with a tuple set and
* a field descriptor set
***************
*** 131,143 ****
*/
public int isNullable(int column) throws SQLException
{
! /*
! * TODO This needs a real implementation, taking into account columns
! * defined with NOT NULL or PRIMARY KEY, CHECK constraints, views,
! * functions etc.
! */
! return java.sql.ResultSetMetaData.columnNullableUnknown;
! }
/*
* Is the column a signed number? In PostgreSQL, all numbers
--- 133,141 ----
*/
public int isNullable(int column) throws SQLException
{
! Field field = getField(column);
! return field.getNullable();
! }
/*
* Is the column a signed number? In PostgreSQL, all numbers
***************
*** 225,240 ****
}
/*
- * What is the suggested column title for use in printouts and
- * displays? We suggest the ColumnName!
- *
* @param column the first column is 1, the second is 2, etc.
* @return the column label
* @exception SQLException if a database access error occurs
*/
public String getColumnLabel(int column) throws SQLException
{
! return getColumnName(column);
}
/*
--- 223,238 ----
}
/*
* @param column the first column is 1, the second is 2, etc.
* @return the column label
* @exception SQLException if a database access error occurs
*/
public String getColumnLabel(int column) throws SQLException
{
! Field f = getField(column);
! if (f != null)
! return f.getName();
! return "field" + column;
}
/*
***************
*** 246,270 ****
*/
public String getColumnName(int column) throws SQLException
{
! Field f = getField(column);
! if (f != null)
! return f.getName();
! return "field" + column;
}
/*
- * What is a column's table's schema? This relies on us knowing
- * the table name....which I don't know how to do as yet. The
- * JDBC specification allows us to return "" if this is not
- * applicable.
- *
* @param column the first column is 1, the second is 2...
! * @return the Schema
* @exception SQLException if a database access error occurs
*/
public String getSchemaName(int column) throws SQLException
{
! return "";
}
/*
--- 244,300 ----
*/
public String getColumnName(int column) throws SQLException
{
! Field field = getField(column);
! return field.getColumnName();
}
/*
* @param column the first column is 1, the second is 2...
! * @return the Schema Name
* @exception SQLException if a database access error occurs
*/
public String getSchemaName(int column) throws SQLException
{
! Field field = getField(column);
! if (field.getTableOid() == 0)
! {
! return "";
! }
! Integer tableOid = new Integer(field.getTableOid());
! if (schemaNameCache == null)
! {
! schemaNameCache = new Hashtable();
! }
! String schemaName = (String) schemaNameCache.get(tableOid);
! if (schemaName != null)
! {
! return schemaName;
! } else
! {
! java.sql.Connection con = (java.sql.Connection) field.getConn();
! ResultSet res = null;
! PreparedStatement ps = null;
! try
! {
! String sql = "SELECT n.nspname FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE n.oid =
c.relnamespaceAND c.oid = ?;";
! ps = con.prepareStatement(sql);
! ps.setInt(1, tableOid.intValue());
! res = ps.executeQuery();
! schemaName = "";
! if (res.next())
! {
! schemaName = res.getString(1);
! }
! schemaNameCache.put(tableOid, schemaName);
! return schemaName;
! } finally
! {
! if (res != null)
! res.close();
! if (ps != null)
! ps.close();
! }
! }
}
/*
***************
*** 341,357 ****
}
/*
- * Whats a column's table's name? How do I find this out? Both
- * getSchemaName() and getCatalogName() rely on knowing the table
- * Name, so we need this before we can work on them.
- *
* @param column the first column is 1, the second is 2...
* @return column name, or "" if not applicable
* @exception SQLException if a database access error occurs
*/
public String getTableName(int column) throws SQLException
{
! return "";
}
/*
--- 371,421 ----
}
/*
* @param column the first column is 1, the second is 2...
* @return column name, or "" if not applicable
* @exception SQLException if a database access error occurs
*/
public String getTableName(int column) throws SQLException
{
! Field field = getField(column);
! if (field.getTableOid() == 0)
! {
! return "";
! }
! Integer tableOid = new Integer(field.getTableOid());
! if (tableNameCache == null)
! {
! tableNameCache = new Hashtable();
! }
! String tableName = (String) tableNameCache.get(tableOid);
! if (tableName != null)
! {
! return tableName;
! } else
! {
! java.sql.Connection con = (java.sql.Connection) field.getConn();
! ResultSet res = null;
! PreparedStatement ps = null;
! try
! {
! ps = con.prepareStatement("SELECT relname FROM pg_catalog.pg_class WHERE oid = ?");
! ps.setInt(1, tableOid.intValue());
! res = ps.executeQuery();
! tableName = "";
! if (res.next())
! {
! tableName = res.getString(1);
! }
! tableNameCache.put(tableOid, tableName);
! return tableName;
! } finally
! {
! if (res != null)
! res.close();
! if (ps != null)
! ps.close();
! }
! }
}
/*
Index: org/postgresql/jdbc2/AbstractJdbc2ResultSetMetaData.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSetMetaData.java,v
retrieving revision 1.5
diff -c -r1.5 AbstractJdbc2ResultSetMetaData.java
*** org/postgresql/jdbc2/AbstractJdbc2ResultSetMetaData.java 13 Sep 2003 04:02:15 -0000 1.5
--- org/postgresql/jdbc2/AbstractJdbc2ResultSetMetaData.java 14 Nov 2003 13:10:36 -0000
***************
*** 119,141 ****
}
/*
- * Indicates the nullability of values in the designated column.
- *
- * @param column the first column is 1, the second is 2...
- * @return one of the columnNullable values
- * @exception SQLException if a database access error occurs
- */
- public int isNullable(int column) throws SQLException
- {
- /*
- * TODO This needs a real implementation, taking into account columns
- * defined with NOT NULL or PRIMARY KEY, CHECK constraints, views,
- * functions etc.
- */
- return java.sql.ResultSetMetaData.columnNullableUnknown;
- }
-
- /*
* Is the column a signed number? In PostgreSQL, all numbers
* are signed, so this is trivial. However, strings are not
* signed (duh!)
--- 119,124 ----
***************
*** 221,269 ****
}
/*
- * What is the suggested column title for use in printouts and
- * displays? We suggest the ColumnName!
- *
- * @param column the first column is 1, the second is 2, etc.
- * @return the column label
- * @exception SQLException if a database access error occurs
- */
- public String getColumnLabel(int column) throws SQLException
- {
- return getColumnName(column);
- }
-
- /*
- * What's a column's name?
- *
- * @param column the first column is 1, the second is 2, etc.
- * @return the column name
- * @exception SQLException if a database access error occurs
- */
- public String getColumnName(int column) throws SQLException
- {
- Field f = getField(column);
- if (f != null)
- return f.getName();
- return "field" + column;
- }
-
- /*
- * What is a column's table's schema? This relies on us knowing
- * the table name....which I don't know how to do as yet. The
- * JDBC specification allows us to return "" if this is not
- * applicable.
- *
- * @param column the first column is 1, the second is 2...
- * @return the Schema
- * @exception SQLException if a database access error occurs
- */
- public String getSchemaName(int column) throws SQLException
- {
- return "";
- }
-
- /*
* What is a column's number of decimal digits.
*
* @param column the first column is 1, the second is 2...
--- 204,209 ----
***************
*** 336,354 ****
}
}
- /*
- * Whats a column's table's name? How do I find this out? Both
- * getSchemaName() and getCatalogName() rely on knowing the table
- * Name, so we need this before we can work on them.
- *
- * @param column the first column is 1, the second is 2...
- * @return column name, or "" if not applicable
- * @exception SQLException if a database access error occurs
- */
- public String getTableName(int column) throws SQLException
- {
- return "";
- }
/*
* What's a column's table's catalog name? As with getSchemaName(),
--- 276,281 ----
Index: org/postgresql/test/jdbc2/Jdbc2TestSuite.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/test/jdbc2/Jdbc2TestSuite.java,v
retrieving revision 1.7
diff -c -r1.7 Jdbc2TestSuite.java
*** org/postgresql/test/jdbc2/Jdbc2TestSuite.java 22 Sep 2003 05:38:01 -0000 1.7
--- org/postgresql/test/jdbc2/Jdbc2TestSuite.java 14 Nov 2003 13:10:37 -0000
***************
*** 35,40 ****
--- 35,41 ----
// ResultSet
suite.addTestSuite(ResultSetTest.class);
+ suite.addTestSuite(ResultSetMetaDataTest.class);
// Time, Date, Timestamp
suite.addTestSuite(DateTest.class);
On Fri, 14 Nov 2003, Jan-Andre le Roux wrote: > Jan-Andre le Roux wrote: > > Hi > > > > The patch attached implements the following Result Set Meta Information > > methods in AbstractJdbc1ResultSetMetaData: > > > > getTableName(...) > > getSchemaName(...) > > isNullable(...) > > getColumnName(...) > > getColumnLabel(...) > > After some further testing I came across a situation where this fails to work properly. When querying the DatabaseMetaData class a ResultSet is created in memory without it coming from the server and a real table. ResultSetMetaData.getColumnName() should still return the correct column name given to the Field. Attached is a test case. Kris Jurka