From 3d21bf82c3e27396bd3598810cbcc6f7cdc05adf Mon Sep 17 00:00:00 2001 From: Peter Mount Date: Tue, 13 Feb 2001 16:39:06 +0000 Subject: [PATCH] Some more including the patch to DatabaseMetaData backed out by Bruce. Tue Feb 13 16:33:00 GMT 2001 peter@retep.org.uk - More TestCases implemented. Refined the test suite api's. - Removed need for SimpleDateFormat in ResultSet.getDate() improving performance. - Rewrote ResultSet.getTime() so that it uses JDK api's better. Tue Feb 13 10:25:00 GMT 2001 peter@retep.org.uk - Added MiscTest to hold reported problems from users. - Fixed PGMoney. - JBuilder4/JDBCExplorer now works with Money fields. Patched Field & ResultSet (lots of methods) for this one. Also changed cash/money to return type DOUBLE not DECIMAL. This broke JBuilder as zero scale BigDecimal's can't have decimal places! - When a Statement is reused, the previous ResultSet is now closed. - Removed deprecated call in ResultSet.getTime() Thu Feb 08 18:53:00 GMT 2001 peter@retep.org.uk - Changed a couple of settings in DatabaseMetaData where 7.1 now supports those features - Implemented the DatabaseMetaData TestCase. Wed Feb 07 18:06:00 GMT 2001 peter@retep.org.uk - Added comment to Connection.isClosed() explaining why we deviate from the JDBC2 specification. - Fixed bug where the Isolation Level is lost while in autocommit mode. - Fixed bug where several calls to getTransactionIsolationLevel() returned the first call's result. --- src/interfaces/jdbc/CHANGELOG | 28 ++ src/interfaces/jdbc/org/postgresql/Field.java | 26 +- .../jdbc/org/postgresql/ResultSet.java | 52 +++- .../jdbc/org/postgresql/jdbc2/Connection.java | 54 +++- .../postgresql/jdbc2/DatabaseMetaData.java | 16 +- .../postgresql/jdbc2/PreparedStatement.java | 4 + .../jdbc/org/postgresql/jdbc2/ResultSet.java | 62 ++-- .../jdbc/org/postgresql/jdbc2/Statement.java | 9 + .../jdbc/org/postgresql/test/JDBC2Tests.java | 138 ++++++++- .../postgresql/test/jdbc2/ConnectionTest.java | 92 +++++- .../test/jdbc2/DatabaseMetaDataTest.java | 273 ++++++++++++++++++ .../org/postgresql/test/jdbc2/DateTest.java | 126 ++++++++ .../postgresql/test/jdbc2/JBuilderTest.java | 44 +++ .../org/postgresql/test/jdbc2/MiscTest.java | 47 +++ .../org/postgresql/test/jdbc2/TimeTest.java | 123 ++++++++ .../postgresql/test/jdbc2/TimestampTest.java | 136 +++++++++ .../jdbc/org/postgresql/util/PGmoney.java | 22 +- 17 files changed, 1149 insertions(+), 103 deletions(-) create mode 100644 src/interfaces/jdbc/org/postgresql/test/jdbc2/DatabaseMetaDataTest.java create mode 100644 src/interfaces/jdbc/org/postgresql/test/jdbc2/DateTest.java create mode 100644 src/interfaces/jdbc/org/postgresql/test/jdbc2/JBuilderTest.java create mode 100644 src/interfaces/jdbc/org/postgresql/test/jdbc2/MiscTest.java create mode 100644 src/interfaces/jdbc/org/postgresql/test/jdbc2/TimeTest.java create mode 100644 src/interfaces/jdbc/org/postgresql/test/jdbc2/TimestampTest.java diff --git a/src/interfaces/jdbc/CHANGELOG b/src/interfaces/jdbc/CHANGELOG index 1da6284119..b02e4d3756 100644 --- a/src/interfaces/jdbc/CHANGELOG +++ b/src/interfaces/jdbc/CHANGELOG @@ -1,3 +1,31 @@ +Tue Feb 13 16:33:00 GMT 2001 peter@retep.org.uk + - More TestCases implemented. Refined the test suite api's. + - Removed need for SimpleDateFormat in ResultSet.getDate() improving + performance. + - Rewrote ResultSet.getTime() so that it uses JDK api's better. + +Tue Feb 13 10:25:00 GMT 2001 peter@retep.org.uk + - Added MiscTest to hold reported problems from users. + - Fixed PGMoney. + - JBuilder4/JDBCExplorer now works with Money fields. Patched Field & + ResultSet (lots of methods) for this one. Also changed cash/money to + return type DOUBLE not DECIMAL. This broke JBuilder as zero scale + BigDecimal's can't have decimal places! + - When a Statement is reused, the previous ResultSet is now closed. + - Removed deprecated call in ResultSet.getTime() + +Thu Feb 08 18:53:00 GMT 2001 peter@retep.org.uk + - Changed a couple of settings in DatabaseMetaData where 7.1 now + supports those features + - Implemented the DatabaseMetaData TestCase. + +Wed Feb 07 18:06:00 GMT 2001 peter@retep.org.uk + - Added comment to Connection.isClosed() explaining why we deviate from + the JDBC2 specification. + - Fixed bug where the Isolation Level is lost while in autocommit mode. + - Fixed bug where several calls to getTransactionIsolationLevel() + returned the first call's result. + Tue Feb 06 19:00:00 GMT 2001 peter@retep.org.uk - Completed first two TestCase's for the test suite. JUnit is now recognised by ant. diff --git a/src/interfaces/jdbc/org/postgresql/Field.java b/src/interfaces/jdbc/org/postgresql/Field.java index b73c224e51..6450858843 100644 --- a/src/interfaces/jdbc/org/postgresql/Field.java +++ b/src/interfaces/jdbc/org/postgresql/Field.java @@ -16,12 +16,12 @@ public class Field public int oid; // OID of the type public int mod; // type modifier of this field public String name; // Name of this field - + protected Connection conn; // Connection Instantation - + public int sql_type = -1; // The entry in java.sql.Types for this field public String type_name = null;// The sql type name - + /** * Construct a field based on the information fed to it. * @@ -38,7 +38,7 @@ public class Field this.length = length; this.mod = mod; } - + /** * Constructor without mod parameter. * @@ -51,7 +51,7 @@ public class Field { this(conn,name,oid,length,0); } - + /** * @return the oid of this Field's data type */ @@ -59,7 +59,7 @@ public class Field { return oid; } - + /** * the ResultSet and ResultMetaData both need to handle the SQL * type, which is gained from another query. Note that we cannot @@ -72,7 +72,7 @@ public class Field { if(sql_type == -1) { type_name = (String)conn.fieldCache.get(new Integer(oid)); - + // it's not in the cache, so perform a query, and add the result to // the cache if(type_name==null) { @@ -84,12 +84,12 @@ public class Field conn.fieldCache.put(new Integer(oid),type_name); result.close(); } - + sql_type = getSQLType(type_name); } return sql_type; } - + /** * This returns the SQL type. It is called by the Field and DatabaseMetaData classes * @param type_name PostgreSQL type name @@ -103,7 +103,7 @@ public class Field sql_type=typei[i]; return sql_type; } - + /** * This table holds the org.postgresql names for the types supported. * Any types that map to Types.OTHER (eg POINT) don't go into this table. @@ -128,7 +128,7 @@ public class Field "time", "abstime","timestamp" }; - + /** * This table holds the JDBC type for each entry above. * @@ -140,7 +140,7 @@ public class Field Types.SMALLINT, Types.INTEGER,Types.INTEGER, Types.BIGINT, - Types.DECIMAL,Types.DECIMAL, + Types.DOUBLE,Types.DOUBLE, Types.NUMERIC, Types.REAL, Types.DOUBLE, @@ -151,7 +151,7 @@ public class Field Types.TIME, Types.TIMESTAMP,Types.TIMESTAMP }; - + /** * We also need to get the type name as returned by the back end. * This is held in type_name AFTER a call to getSQLType. Since diff --git a/src/interfaces/jdbc/org/postgresql/ResultSet.java b/src/interfaces/jdbc/org/postgresql/ResultSet.java index cec62614ca..bea07e639b 100644 --- a/src/interfaces/jdbc/org/postgresql/ResultSet.java +++ b/src/interfaces/jdbc/org/postgresql/ResultSet.java @@ -25,11 +25,11 @@ public abstract class ResultSet protected Connection connection; // the connection which we returned from protected SQLWarning warnings = null; // The warning chain protected boolean wasNullFlag = false; // the flag for wasNull() - + // We can chain multiple resultSets together - this points to // next resultSet in the chain. protected ResultSet next = null; - + /** * Create a new ResultSet - Note that we create ResultSets to * represent the results of everything. @@ -52,8 +52,8 @@ public abstract class ResultSet this.this_row = null; this.current_row = -1; } - - + + /** * Create a new ResultSet - Note that we create ResultSets to * represent the results of everything. @@ -69,7 +69,7 @@ public abstract class ResultSet { this(conn,fields,tuples,status,updateCount,0); } - + /** * We at times need to know if the resultSet we are working * with is the result of an UPDATE, DELETE or INSERT (in which @@ -83,7 +83,7 @@ public abstract class ResultSet { return (fields != null); } - + /** * Since ResultSets can be chained, we need some method of * finding the next one in the chain. The method getNext() @@ -95,7 +95,7 @@ public abstract class ResultSet { return (java.sql.ResultSet)next; } - + /** * This following method allows us to add a ResultSet object * to the end of the current chain. @@ -109,7 +109,7 @@ public abstract class ResultSet else next.append(r); } - + /** * If we are just a place holder for results, we still need * to get an updateCount. This method returns it. @@ -120,7 +120,7 @@ public abstract class ResultSet { return updateCount; } - + /** * We also need to provide a couple of auxiliary functions for * the implementation of the ResultMetaData functions. In @@ -133,7 +133,7 @@ public abstract class ResultSet { return rows.size(); } - + /** * getColumnCount returns the number of columns * @@ -143,7 +143,7 @@ public abstract class ResultSet { return fields.length; } - + /** * Returns the status message from the backend.

* It is used internally by the driver. @@ -154,7 +154,7 @@ public abstract class ResultSet { return status; } - + /** * returns the OID of a field.

* It is used internally by the driver. @@ -166,7 +166,7 @@ public abstract class ResultSet { return fields[field-1].getOID(); } - + /** * returns the OID of the last inserted row */ @@ -174,12 +174,36 @@ public abstract class ResultSet { return insertOID; } - + /** * This is part of the JDBC API, but is required by org.postgresql.Field */ public abstract void close() throws SQLException; public abstract boolean next() throws SQLException; public abstract String getString(int i) throws SQLException; + + /** + * This is used to fix get*() methods on Money fields. It should only be + * used by those methods! + * + * It converts ($##.##) to -##.## and $##.## to ##.## + */ + public String getFixedString(int col) throws SQLException { + String s = getString(col); + + // Handle SQL Null + if(s==null) + return null; + + // Handle Money + if(s.charAt(0)=='(') { + s="-"+org.postgresql.util.PGtokenizer.removePara(s).substring(1); + } + if(s.charAt(0)=='$') { + s=s.substring(1); + } + + return s; + } } diff --git a/src/interfaces/jdbc/org/postgresql/jdbc2/Connection.java b/src/interfaces/jdbc/org/postgresql/jdbc2/Connection.java index 8b505d1ab9..7e01943da4 100644 --- a/src/interfaces/jdbc/org/postgresql/jdbc2/Connection.java +++ b/src/interfaces/jdbc/org/postgresql/jdbc2/Connection.java @@ -17,7 +17,7 @@ import org.postgresql.largeobject.*; import org.postgresql.util.*; /** - * $Id: Connection.java,v 1.6 2001/01/31 08:26:02 peter Exp $ + * $Id: Connection.java,v 1.7 2001/02/13 16:39:02 peter Exp $ * * A Connection represents a session with a specific database. Within the * context of a Connection, SQL statements are executed and results are @@ -39,8 +39,16 @@ public class Connection extends org.postgresql.Connection implements java.sql.Co // This is a cache of the DatabaseMetaData instance for this connection protected DatabaseMetaData metadata; + /** + * The current type mappings + */ protected java.util.Map typemap; + /** + * Cache of the current isolation level + */ + protected int isolationLevel = java.sql.Connection.TRANSACTION_READ_COMMITTED; + /** * SQL statements without parameters are normally executed using * Statement objects. If the same SQL statement is executed many @@ -179,8 +187,10 @@ public class Connection extends org.postgresql.Connection implements java.sql.Co return; if (autoCommit) ExecSQL("end"); - else + else { ExecSQL("begin"); + doIsolationLevel(); + } this.autoCommit = autoCommit; } @@ -213,6 +223,7 @@ public class Connection extends org.postgresql.Connection implements java.sql.Co ExecSQL("commit"); autoCommit = true; ExecSQL("begin"); + doIsolationLevel(); autoCommit = false; } @@ -231,6 +242,7 @@ public class Connection extends org.postgresql.Connection implements java.sql.Co ExecSQL("rollback"); autoCommit = true; ExecSQL("begin"); + doIsolationLevel(); autoCommit = false; } @@ -258,7 +270,18 @@ public class Connection extends org.postgresql.Connection implements java.sql.Co } /** - * Tests to see if a Connection is closed + * Tests to see if a Connection is closed. + * + * Peter Feb 7 2000: Now I've discovered that this doesn't actually obey the + * specifications. Under JDBC2.1, this should only be valid _after_ close() + * has been called. It's result is not guraranteed to be valid before, and + * client code should not use it to see if a connection is open. The spec says + * that the client should monitor the SQLExceptions thrown when their queries + * fail because the connection is dead. + * + * I don't like this definition. As it doesn't hurt breaking it here, our + * isClosed() implementation does test the connection, so for PostgreSQL, you + * can rely on isClosed() returning a valid result. * * @return the status of the connection * @exception SQLException (why?) @@ -370,10 +393,20 @@ public class Connection extends org.postgresql.Connection implements java.sql.Co * @see java.sql.DatabaseMetaData#supportsTransactionIsolationLevel */ public void setTransactionIsolation(int level) throws SQLException + { + isolationLevel = level; + doIsolationLevel(); + } + + /** + * Helper method used by setTransactionIsolation(), commit(), rollback() + * and setAutoCommit(). This sets the current isolation level. + */ + private void doIsolationLevel() throws SQLException { String q = "SET TRANSACTION ISOLATION LEVEL"; - switch(level) { + switch(isolationLevel) { case java.sql.Connection.TRANSACTION_READ_COMMITTED: ExecSQL(q + " READ COMMITTED"); @@ -384,7 +417,7 @@ public class Connection extends org.postgresql.Connection implements java.sql.Co return; default: - throw new PSQLException("postgresql.con.isolevel",new Integer(level)); + throw new PSQLException("postgresql.con.isolevel",new Integer(isolationLevel)); } } @@ -396,14 +429,17 @@ public class Connection extends org.postgresql.Connection implements java.sql.Co */ public int getTransactionIsolation() throws SQLException { + clearWarnings(); ExecSQL("show xactisolevel"); SQLWarning w = getWarnings(); if (w != null) { - if (w.getMessage().indexOf("READ COMMITTED") != -1) return java.sql.Connection.TRANSACTION_READ_COMMITTED; else - if (w.getMessage().indexOf("READ UNCOMMITTED") != -1) return java.sql.Connection.TRANSACTION_READ_UNCOMMITTED; else - if (w.getMessage().indexOf("REPEATABLE READ") != -1) return java.sql.Connection.TRANSACTION_REPEATABLE_READ; else - if (w.getMessage().indexOf("SERIALIZABLE") != -1) return java.sql.Connection.TRANSACTION_SERIALIZABLE; + String m = w.getMessage(); + clearWarnings(); + if (m.indexOf("READ COMMITTED") != -1) return java.sql.Connection.TRANSACTION_READ_COMMITTED; else + if (m.indexOf("READ UNCOMMITTED") != -1) return java.sql.Connection.TRANSACTION_READ_UNCOMMITTED; else + if (m.indexOf("REPEATABLE READ") != -1) return java.sql.Connection.TRANSACTION_REPEATABLE_READ; else + if (m.indexOf("SERIALIZABLE") != -1) return java.sql.Connection.TRANSACTION_SERIALIZABLE; } return java.sql.Connection.TRANSACTION_READ_COMMITTED; } diff --git a/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java b/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java index 64435d81bd..c718a3ac5c 100644 --- a/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java +++ b/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java @@ -736,7 +736,7 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData */ public boolean supportsOuterJoins() throws SQLException { - return false; + return true; // yes 7.1 does } /** @@ -748,7 +748,7 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData */ public boolean supportsFullOuterJoins() throws SQLException { - return false; + return true; // yes in 7.1 } /** @@ -760,7 +760,7 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData */ public boolean supportsLimitedOuterJoins() throws SQLException { - return false; + return true; // yes in 7.1 } /** @@ -1009,7 +1009,7 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData */ public boolean supportsUnion() throws SQLException { - return false; + return true; // 7.0? } /** @@ -1617,8 +1617,7 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData * * *

The valid values for the types parameter are: - * "TABLE", "INDEX", "LARGE OBJECT", "SEQUENCE", "SYSTEM TABLE" and - * "SYSTEM INDEX" + * "TABLE", "INDEX", "SEQUENCE", "SYSTEM TABLE" and "SYSTEM INDEX" * * @param catalog a catalog name; For org.postgresql, this is ignored, and * should be set to null @@ -1722,9 +1721,8 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData // IMPORTANT: the query must be enclosed in ( ) private static final String getTableTypes[][] = { {"TABLE", "(relkind='r' and relhasrules='f' and relname !~ '^pg_' and relname !~ '^xinv')"}, - {"VIEW", "(relkind='v' and relname !~ '^pg_' and relname !~ '^xinv')"}, - {"INDEX", "(relkind='i' and relname !~ '^pg_' and relname !~ '^xinx')"}, - {"LARGE OBJECT", "(relkind='r' and relname ~ '^xinv')"}, + {"VIEW", "(relkind='v' and relname !~ '^pg_')"}, + {"INDEX", "(relkind='i' and relname !~ '^pg_')"}, {"SEQUENCE", "(relkind='S' and relname !~ '^pg_')"}, {"SYSTEM TABLE", "(relkind='r' and relname ~ '^pg_')"}, {"SYSTEM INDEX", "(relkind='i' and relname ~ '^pg_')"} diff --git a/src/interfaces/jdbc/org/postgresql/jdbc2/PreparedStatement.java b/src/interfaces/jdbc/org/postgresql/jdbc2/PreparedStatement.java index 54c7a9b589..a0f1d0713c 100644 --- a/src/interfaces/jdbc/org/postgresql/jdbc2/PreparedStatement.java +++ b/src/interfaces/jdbc/org/postgresql/jdbc2/PreparedStatement.java @@ -356,6 +356,10 @@ public class PreparedStatement extends Statement implements java.sql.PreparedSta StringBuffer strBuf = new StringBuffer("'"); strBuf.append(df.format(x)).append('.').append(x.getNanos()/10000000).append("+00'"); set(parameterIndex, strBuf.toString()); + + // The above works, but so does the following. I'm leaving the above in, but this seems + // to be identical. Pays to read the docs ;-) + //set(parameterIndex,"'"+x.toString()+"'"); } /** diff --git a/src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java b/src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java index d88d3190b1..e221187680 100644 --- a/src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java +++ b/src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java @@ -131,7 +131,10 @@ public class ResultSet extends org.postgresql.ResultSet implements java.sql.Resu public void close() throws SQLException { //release resources held (memory for tuples) - rows.setSize(0); + if(rows!=null) { + rows.setSize(0); + rows=null; + } } /** @@ -157,16 +160,13 @@ public class ResultSet extends org.postgresql.ResultSet implements java.sql.Resu */ public String getString(int columnIndex) throws SQLException { - //byte[] bytes = getBytes(columnIndex); - // - //if (bytes == null) - //return null; - //return new String(bytes); if (columnIndex < 1 || columnIndex > fields.length) throw new PSQLException("postgresql.res.colrange"); + wasNullFlag = (this_row[columnIndex - 1] == null); if(wasNullFlag) return null; + String encoding = connection.getEncoding(); if (encoding == null) return new String(this_row[columnIndex - 1]); @@ -230,7 +230,7 @@ public class ResultSet extends org.postgresql.ResultSet implements java.sql.Resu */ public short getShort(int columnIndex) throws SQLException { - String s = getString(columnIndex); + String s = getFixedString(columnIndex); if (s != null) { @@ -253,7 +253,7 @@ public class ResultSet extends org.postgresql.ResultSet implements java.sql.Resu */ public int getInt(int columnIndex) throws SQLException { - String s = getString(columnIndex); + String s = getFixedString(columnIndex); if (s != null) { @@ -276,7 +276,7 @@ public class ResultSet extends org.postgresql.ResultSet implements java.sql.Resu */ public long getLong(int columnIndex) throws SQLException { - String s = getString(columnIndex); + String s = getFixedString(columnIndex); if (s != null) { @@ -299,7 +299,7 @@ public class ResultSet extends org.postgresql.ResultSet implements java.sql.Resu */ public float getFloat(int columnIndex) throws SQLException { - String s = getString(columnIndex); + String s = getFixedString(columnIndex); if (s != null) { @@ -322,7 +322,7 @@ public class ResultSet extends org.postgresql.ResultSet implements java.sql.Resu */ public double getDouble(int columnIndex) throws SQLException { - String s = getString(columnIndex); + String s = getFixedString(columnIndex); if (s != null) { @@ -348,14 +348,14 @@ public class ResultSet extends org.postgresql.ResultSet implements java.sql.Resu */ public BigDecimal getBigDecimal(int columnIndex, int scale) throws SQLException { - String s = getString(columnIndex); + String s = getFixedString(columnIndex); BigDecimal val; if (s != null) { - try - { + try + { val = new BigDecimal(s); } catch (NumberFormatException e) { throw new PSQLException ("postgresql.res.badbigdec",s); @@ -418,12 +418,8 @@ public class ResultSet extends org.postgresql.ResultSet implements java.sql.Resu String s = getString(columnIndex); if(s==null) return null; - SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd"); - try { - return new java.sql.Date(df.parse(s).getTime()); - } catch (ParseException e) { - throw new PSQLException("postgresql.res.baddate",new Integer(e.getErrorOffset()),s); - } + + return java.sql.Date.valueOf(s); } /** @@ -438,21 +434,10 @@ public class ResultSet extends org.postgresql.ResultSet implements java.sql.Resu { String s = getString(columnIndex); - if (s != null) - { - try - { - if (s.length() != 5 && s.length() != 8) - throw new NumberFormatException("Wrong Length!"); - int hr = Integer.parseInt(s.substring(0,2)); - int min = Integer.parseInt(s.substring(3,5)); - int sec = (s.length() == 5) ? 0 : Integer.parseInt(s.substring(6)); - return new Time(hr, min, sec); - } catch (NumberFormatException e) { - throw new PSQLException ("postgresql.res.badtime",s); - } - } - return null; // SQL NULL + if(s==null) + return null; // SQL NULL + + return java.sql.Time.valueOf(s); } /** @@ -945,11 +930,8 @@ public class ResultSet extends org.postgresql.ResultSet implements java.sql.Resu public java.math.BigDecimal getBigDecimal(int columnIndex) throws SQLException { - try { - return new BigDecimal(getDouble(columnIndex)); - } catch(NumberFormatException nfe) { - throw new PSQLException("postgresql.res.badbigdec",nfe.toString()); - } + // Now must call BigDecimal with a scale otherwise JBuilder barfs + return getBigDecimal(columnIndex,0); } public java.math.BigDecimal getBigDecimal(String columnName) throws SQLException diff --git a/src/interfaces/jdbc/org/postgresql/jdbc2/Statement.java b/src/interfaces/jdbc/org/postgresql/jdbc2/Statement.java index ec8632a574..4851b2d14e 100644 --- a/src/interfaces/jdbc/org/postgresql/jdbc2/Statement.java +++ b/src/interfaces/jdbc/org/postgresql/jdbc2/Statement.java @@ -281,6 +281,15 @@ public class Statement extends org.postgresql.Statement implements java.sql.Stat if(escapeProcessing) sql=connection.EscapeSQL(sql); + // New in 7.1, if we have a previous resultset then force it to close + // This brings us nearer to compliance, and helps memory management. + // Internal stuff will call ExecSQL directly, bypassing this. + if(result!=null) { + java.sql.ResultSet rs = getResultSet(); + if(rs!=null) + rs.close(); + } + // New in 7.1, pass Statement so that ExecSQL can customise to it result = connection.ExecSQL(sql,this); diff --git a/src/interfaces/jdbc/org/postgresql/test/JDBC2Tests.java b/src/interfaces/jdbc/org/postgresql/test/JDBC2Tests.java index c4ff4b29f6..6aac14b4b1 100644 --- a/src/interfaces/jdbc/org/postgresql/test/JDBC2Tests.java +++ b/src/interfaces/jdbc/org/postgresql/test/JDBC2Tests.java @@ -7,7 +7,7 @@ import org.postgresql.test.jdbc2.*; import java.sql.*; /** - * Executes all known tests for JDBC2 + * Executes all known tests for JDBC2 and includes some utility methods. */ public class JDBC2Tests extends TestSuite { /** @@ -59,6 +59,122 @@ public class JDBC2Tests extends TestSuite { } } + /** + * Helper - creates a test table for use by a test + */ + public static void createTable(Connection conn,String columns) { + try { + Statement st = conn.createStatement(); + + // Ignore the drop + try { + st.executeUpdate("drop table "+getTableName()); + } catch(SQLException se) { + } + + // Now create the table + st.executeUpdate("create table "+getTableName()+" ("+columns+")"); + + st.close(); + } catch(SQLException ex) { + TestCase.assert(ex.getMessage(),false); + } + } + + /** + * Variant used when more than one table is required + */ + public static void createTable(Connection conn,String id,String columns) { + try { + Statement st = conn.createStatement(); + + // Ignore the drop + try { + st.executeUpdate("drop table "+getTableName(id)); + } catch(SQLException se) { + } + + // Now create the table + st.executeUpdate("create table "+getTableName(id)+" ("+columns+")"); + + st.close(); + } catch(SQLException ex) { + TestCase.assert(ex.getMessage(),false); + } + } + + /** + * Helper - generates INSERT SQL - very simple + */ + public static String insert(String values) { + return insert(null,values); + } + public static String insert(String columns,String values) { + String s = "INSERT INTO "+getTableName(); + if(columns!=null) + s=s+" ("+columns+")"; + return s+" VALUES ("+values+")"; + } + + /** + * Helper - generates SELECT SQL - very simple + */ + public static String select(String columns) { + return select(columns,null,null); + } + public static String select(String columns,String where) { + return select(columns,where,null); + } + public static String select(String columns,String where,String other) { + String s = "SELECT "+columns+" FROM "+getTableName(); + if(where!=null) + s=s+" WHERE "+where; + if(other!=null) + s=s+" "+other; + return s; + } + + /** + * Helper - returns the test table's name + * This is defined by the tablename property. If not defined it defaults to + * jdbctest + */ + public static String getTableName() { + if(tablename==null) + tablename=System.getProperty("tablename","jdbctest"); + return tablename; + } + + /** + * As getTableName() but the id is a suffix. Used when more than one table is + * required in a test. + */ + public static String getTableName(String id) { + if(tablename==null) + tablename=System.getProperty("tablename","jdbctest"); + return tablename+"_"+id; + } + + /** + * Cache used by getTableName() [its used a lot!] + */ + private static String tablename; + + /** + * Helper to prefix a number with leading zeros - ugly but it works... + * @param v value to prefix + * @param l number of digits (0-10) + */ + public static String fix(int v,int l) { + String s = "0000000000".substring(0,l)+Integer.toString(v); + return s.substring(s.length()-l); + } + + /** + * Number of milliseconds in a day + */ + public static final long DAYMILLIS = 24*3600*1000; + /** * The main entry point for JUnit */ @@ -68,19 +184,24 @@ public class JDBC2Tests extends TestSuite { // // Add one line per class in our test cases. These should be in order of // complexity. + + // ANTTest should be first as it ensures that test parameters are + // being sent to the suite. It also initialises the database (if required) + // with some simple global tables (will make each testcase use its own later). // - // ie: ANTTest should be first as it ensures that test parameters are - // being sent to the suite. - // + suite.addTestSuite(ANTTest.class); // Basic Driver internals - suite.addTestSuite(ANTTest.class); suite.addTestSuite(DriverTest.class); suite.addTestSuite(ConnectionTest.class); + suite.addTestSuite(DatabaseMetaDataTest.class); // Connectivity/Protocols // ResultSet + suite.addTestSuite(DateTest.class); + suite.addTestSuite(TimeTest.class); + suite.addTestSuite(TimestampTest.class); // PreparedStatement @@ -88,7 +209,12 @@ public class JDBC2Tests extends TestSuite { // Fastpath/LargeObject + // Other misc tests, based on previous problems users have had or specific + // features some applications require. + suite.addTestSuite(JBuilderTest.class); + suite.addTestSuite(MiscTest.class); + // That's all folks return suite; } -} \ No newline at end of file +} diff --git a/src/interfaces/jdbc/org/postgresql/test/jdbc2/ConnectionTest.java b/src/interfaces/jdbc/org/postgresql/test/jdbc2/ConnectionTest.java index 6f268d3af7..58c5c7258f 100644 --- a/src/interfaces/jdbc/org/postgresql/test/jdbc2/ConnectionTest.java +++ b/src/interfaces/jdbc/org/postgresql/test/jdbc2/ConnectionTest.java @@ -10,7 +10,7 @@ import java.sql.*; * * PS: Do you know how difficult it is to type on a train? ;-) * - * $Id: ConnectionTest.java,v 1.1 2001/02/07 09:13:20 peter Exp $ + * $Id: ConnectionTest.java,v 1.2 2001/02/13 16:39:05 peter Exp $ */ public class ConnectionTest extends TestCase { @@ -127,7 +127,7 @@ public class ConnectionTest extends TestCase { } /** - * Simple test to see if isClosed works + * Simple test to see if isClosed works. */ public void testIsClosed() { try { @@ -146,4 +146,92 @@ public class ConnectionTest extends TestCase { } } + /** + * Test the warnings system + */ + public void testWarnings() { + try { + Connection con = JDBC2Tests.openDB(); + + String testStr = "This Is OuR TeSt message"; + + // The connection must be ours! + assert(con instanceof org.postgresql.Connection); + + // Clear any existing warnings + con.clearWarnings(); + + // Set the test warning + ((org.postgresql.Connection)con).addWarning(testStr); + + // Retrieve it + SQLWarning warning = con.getWarnings(); + assert(warning!=null); + assert(warning.getMessage().equals(testStr)); + + // Finally test clearWarnings() this time there must be something to delete + con.clearWarnings(); + assert(con.getWarnings()==null); + + JDBC2Tests.closeDB(con); + } catch(SQLException ex) { + assert(ex.getMessage(),false); + } + } + + /** + * Transaction Isolation Levels + */ + public void testTransactionIsolation() { + try { + Connection con = JDBC2Tests.openDB(); + + con.setAutoCommit(false); + + // These are the currently available ones + con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); + assert(con.getTransactionIsolation()==Connection.TRANSACTION_SERIALIZABLE); + + con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); + assert(con.getTransactionIsolation()==Connection.TRANSACTION_READ_COMMITTED); + + // Now turn on AutoCommit. Transaction Isolation doesn't work outside of + // a transaction, so they should return READ_COMMITTED at all times! + con.setAutoCommit(true); + con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); + assert(con.getTransactionIsolation()==Connection.TRANSACTION_READ_COMMITTED); + + con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); + assert(con.getTransactionIsolation()==Connection.TRANSACTION_READ_COMMITTED); + + JDBC2Tests.closeDB(con); + } catch(SQLException ex) { + assert(ex.getMessage(),false); + } + } + + /** + * JDBC2 Type mappings + */ + public void testTypeMaps() { + try { + Connection con = JDBC2Tests.openDB(); + + // preserve the current map + java.util.Map oldmap = con.getTypeMap(); + + // now change it for an empty one + java.util.Map newmap = new java.util.HashMap(); + con.setTypeMap(newmap); + assert(con.getTypeMap()==newmap); + + // restore the old one + con.setTypeMap(oldmap); + assert(con.getTypeMap()==oldmap); + + JDBC2Tests.closeDB(con); + } catch(SQLException ex) { + assert(ex.getMessage(),false); + } + } } \ No newline at end of file diff --git a/src/interfaces/jdbc/org/postgresql/test/jdbc2/DatabaseMetaDataTest.java b/src/interfaces/jdbc/org/postgresql/test/jdbc2/DatabaseMetaDataTest.java new file mode 100644 index 0000000000..ab56b70193 --- /dev/null +++ b/src/interfaces/jdbc/org/postgresql/test/jdbc2/DatabaseMetaDataTest.java @@ -0,0 +1,273 @@ +package org.postgresql.test.jdbc2; + +import org.postgresql.test.JDBC2Tests; +import junit.framework.TestCase; +import java.sql.*; + +/** + * TestCase to test the internal functionality of org.postgresql.jdbc2.DatabaseMetaData + * + * PS: Do you know how difficult it is to type on a train? ;-) + * + * $Id: DatabaseMetaDataTest.java,v 1.1 2001/02/13 16:39:05 peter Exp $ + */ + +public class DatabaseMetaDataTest extends TestCase { + + /** + * Constructor + */ + public DatabaseMetaDataTest(String name) { + super(name); + } + + /** + * The spec says this may return null, but we always do! + */ + public void testGetMetaData() { + try { + Connection con = JDBC2Tests.openDB(); + + DatabaseMetaData dbmd = con.getMetaData(); + assert(dbmd!=null); + + JDBC2Tests.closeDB(con); + } catch(SQLException ex) { + assert(ex.getMessage(),false); + } + } + + /** + * Test default capabilities + */ + public void testCapabilities() { + try { + Connection con = JDBC2Tests.openDB(); + + DatabaseMetaData dbmd = con.getMetaData(); + assert(dbmd!=null); + + assert(dbmd.allProceduresAreCallable()==true); + assert(dbmd.allTablesAreSelectable()==true); // not true all the time + + // This should always be false for postgresql (at least for 7.x) + assert(!dbmd.isReadOnly()); + + // does the backend support this yet? The protocol does... + assert(!dbmd.supportsMultipleResultSets()); + + // yes, as multiple backends can have transactions open + assert(dbmd.supportsMultipleTransactions()); + + assert(dbmd.supportsMinimumSQLGrammar()); + assert(!dbmd.supportsCoreSQLGrammar()); + assert(!dbmd.supportsExtendedSQLGrammar()); + assert(!dbmd.supportsANSI92EntryLevelSQL()); + assert(!dbmd.supportsANSI92IntermediateSQL()); + assert(!dbmd.supportsANSI92FullSQL()); + + assert(!dbmd.supportsIntegrityEnhancementFacility()); + + JDBC2Tests.closeDB(con); + } catch(SQLException ex) { + assert(ex.getMessage(),false); + } + } + + + public void testJoins() { + try { + Connection con = JDBC2Tests.openDB(); + + DatabaseMetaData dbmd = con.getMetaData(); + assert(dbmd!=null); + + assert(dbmd.supportsOuterJoins()); + assert(dbmd.supportsFullOuterJoins()); + assert(dbmd.supportsLimitedOuterJoins()); + + JDBC2Tests.closeDB(con); + } catch(SQLException ex) { + assert(ex.getMessage(),false); + } + } + + public void testCursors() { + try { + Connection con = JDBC2Tests.openDB(); + + DatabaseMetaData dbmd = con.getMetaData(); + assert(dbmd!=null); + + assert(!dbmd.supportsPositionedDelete()); + assert(!dbmd.supportsPositionedUpdate()); + + JDBC2Tests.closeDB(con); + } catch(SQLException ex) { + assert(ex.getMessage(),false); + } + } + + public void testNulls() { + try { + Connection con = JDBC2Tests.openDB(); + + DatabaseMetaData dbmd = con.getMetaData(); + assert(dbmd!=null); + + // these need double checking + assert(!dbmd.nullsAreSortedAtStart()); + assert(dbmd.nullsAreSortedAtEnd()); + assert(!dbmd.nullsAreSortedHigh()); + assert(!dbmd.nullsAreSortedLow()); + + assert(dbmd.nullPlusNonNullIsNull()); + + assert(dbmd.supportsNonNullableColumns()); + + JDBC2Tests.closeDB(con); + } catch(SQLException ex) { + assert(ex.getMessage(),false); + } + } + + public void testLocalFiles() { + try { + Connection con = JDBC2Tests.openDB(); + + DatabaseMetaData dbmd = con.getMetaData(); + assert(dbmd!=null); + + assert(!dbmd.usesLocalFilePerTable()); + assert(!dbmd.usesLocalFiles()); + + JDBC2Tests.closeDB(con); + } catch(SQLException ex) { + assert(ex.getMessage(),false); + } + } + + public void testIdentifiers() { + try { + Connection con = JDBC2Tests.openDB(); + + DatabaseMetaData dbmd = con.getMetaData(); + assert(dbmd!=null); + + assert(!dbmd.supportsMixedCaseIdentifiers()); // always false + assert(dbmd.supportsMixedCaseQuotedIdentifiers()); // always true + + assert(!dbmd.storesUpperCaseIdentifiers()); // always false + assert(dbmd.storesLowerCaseIdentifiers()); // always true + assert(!dbmd.storesUpperCaseQuotedIdentifiers()); // always false + assert(!dbmd.storesLowerCaseQuotedIdentifiers()); // always false + assert(!dbmd.storesMixedCaseQuotedIdentifiers()); // always false + + assert(dbmd.getIdentifierQuoteString().equals("\"")); + + + JDBC2Tests.closeDB(con); + } catch(SQLException ex) { + assert(ex.getMessage(),false); + } + } + + public void testTables() { + try { + Connection con = JDBC2Tests.openDB(); + + DatabaseMetaData dbmd = con.getMetaData(); + assert(dbmd!=null); + + // we can add columns + assert(dbmd.supportsAlterTableWithAddColumn()); + + // we can't drop columns (yet) + assert(!dbmd.supportsAlterTableWithDropColumn()); + + JDBC2Tests.closeDB(con); + } catch(SQLException ex) { + assert(ex.getMessage(),false); + } + } + + public void testSelect() { + try { + Connection con = JDBC2Tests.openDB(); + + DatabaseMetaData dbmd = con.getMetaData(); + assert(dbmd!=null); + + // yes we can?: SELECT col a FROM a; + assert(dbmd.supportsColumnAliasing()); + + // yes we can have expressions in ORDERBY + assert(dbmd.supportsExpressionsInOrderBy()); + + assert(!dbmd.supportsOrderByUnrelated()); + + assert(dbmd.supportsGroupBy()); + assert(dbmd.supportsGroupByUnrelated()); + assert(dbmd.supportsGroupByBeyondSelect()); // needs checking + + JDBC2Tests.closeDB(con); + } catch(SQLException ex) { + assert(ex.getMessage(),false); + } + } + + public void testDBParams() { + try { + Connection con = JDBC2Tests.openDB(); + + DatabaseMetaData dbmd = con.getMetaData(); + assert(dbmd!=null); + + assert(dbmd.getURL().equals(JDBC2Tests.getURL())); + assert(dbmd.getUserName().equals(JDBC2Tests.getUser())); + + JDBC2Tests.closeDB(con); + } catch(SQLException ex) { + assert(ex.getMessage(),false); + } + } + + public void testDbProductDetails() { + try { + Connection con = JDBC2Tests.openDB(); + assert(con instanceof org.postgresql.Connection); + org.postgresql.Connection pc = (org.postgresql.Connection) con; + + DatabaseMetaData dbmd = con.getMetaData(); + assert(dbmd!=null); + + assert(dbmd.getDatabaseProductName().equals("PostgreSQL")); + assert(dbmd.getDatabaseProductVersion().startsWith(Integer.toString(pc.this_driver.getMajorVersion())+"."+Integer.toString(pc.this_driver.getMinorVersion()))); + assert(dbmd.getDriverName().equals("PostgreSQL Native Driver")); + + JDBC2Tests.closeDB(con); + } catch(SQLException ex) { + assert(ex.getMessage(),false); + } + } + + public void testDriverVersioning() { + try { + Connection con = JDBC2Tests.openDB(); + assert(con instanceof org.postgresql.Connection); + org.postgresql.Connection pc = (org.postgresql.Connection) con; + + DatabaseMetaData dbmd = con.getMetaData(); + assert(dbmd!=null); + + assert(dbmd.getDriverVersion().equals(pc.this_driver.getVersion())); + assert(dbmd.getDriverMajorVersion()==pc.this_driver.getMajorVersion()); + assert(dbmd.getDriverMinorVersion()==pc.this_driver.getMinorVersion()); + + + JDBC2Tests.closeDB(con); + } catch(SQLException ex) { + assert(ex.getMessage(),false); + } + } +} \ No newline at end of file diff --git a/src/interfaces/jdbc/org/postgresql/test/jdbc2/DateTest.java b/src/interfaces/jdbc/org/postgresql/test/jdbc2/DateTest.java new file mode 100644 index 0000000000..5150cd7f41 --- /dev/null +++ b/src/interfaces/jdbc/org/postgresql/test/jdbc2/DateTest.java @@ -0,0 +1,126 @@ +package org.postgresql.test.jdbc2; + +import org.postgresql.test.JDBC2Tests; +import junit.framework.TestCase; +import java.sql.*; + +/** + * $Id: DateTest.java,v 1.1 2001/02/13 16:39:05 peter Exp $ + * + * Some simple tests based on problems reported by users. Hopefully these will + * help prevent previous problems from re-occuring ;-) + * + */ +public class DateTest extends TestCase { + + public DateTest(String name) { + super(name); + } + + /** + * Tests the time methods in ResultSet + */ + public void testGetDate() { + try { + Connection con = JDBC2Tests.openDB(); + + Statement st=con.createStatement(); + + JDBC2Tests.createTable(con,"dt date"); + + st.executeUpdate(JDBC2Tests.insert("'1950-02-07'")); + st.executeUpdate(JDBC2Tests.insert("'1970-06-02'")); + st.executeUpdate(JDBC2Tests.insert("'1999-08-11'")); + st.executeUpdate(JDBC2Tests.insert("'2001-02-13'")); + + // Fall through helper + checkTimeTest(con,st); + + st.close(); + + JDBC2Tests.closeDB(con); + } catch(Exception ex) { + assert(ex.getMessage(),false); + } + } + + /** + * Tests the time methods in PreparedStatement + */ + public void testSetDate() { + try { + Connection con = JDBC2Tests.openDB(); + + Statement st=con.createStatement(); + + JDBC2Tests.createTable(con,"dt date"); + + PreparedStatement ps = con.prepareStatement(JDBC2Tests.insert("?")); + + ps.setDate(1,getDate(1950,2,7)); + assert(!ps.execute()); // false as its an update! + + ps.setDate(1,getDate(1970,6,2)); + assert(!ps.execute()); // false as its an update! + + ps.setDate(1,getDate(1999,8,11)); + assert(!ps.execute()); // false as its an update! + + ps.setDate(1,getDate(2001,2,13)); + assert(!ps.execute()); // false as its an update! + + // Fall through helper + checkTimeTest(con,st); + + ps.close(); + st.close(); + + JDBC2Tests.closeDB(con); + } catch(Exception ex) { + assert(ex.getMessage(),false); + } + } + + /** + * Helper for the TimeTests. It tests what should be in the db + */ + private void checkTimeTest(Connection con,Statement st) throws SQLException { + ResultSet rs=null; + java.sql.Date t=null; + + rs=st.executeQuery(JDBC2Tests.select("dt")); + assert(rs!=null); + + assert(rs.next()); + t = rs.getDate(1); + assert(t!=null); + assert(t.equals(getDate(1950,2,7))); + + assert(rs.next()); + t = rs.getDate(1); + assert(t!=null); + assert(t.equals(getDate(1970,6,2))); + + assert(rs.next()); + t = rs.getDate(1); + assert(t!=null); + assert(t.equals(getDate(1999,8,11))); + + assert(rs.next()); + t = rs.getDate(1); + assert(t!=null); + assert(t.equals(getDate(2001,2,13))); + + assert(!rs.next()); + + rs.close(); + } + + /** + * Yes this is ugly, but it gets the test done ;-) + */ + private java.sql.Date getDate(int y,int m,int d) { + return java.sql.Date.valueOf(JDBC2Tests.fix(y,4)+"-"+JDBC2Tests.fix(m,2)+"-"+JDBC2Tests.fix(d,2)); + } + +} diff --git a/src/interfaces/jdbc/org/postgresql/test/jdbc2/JBuilderTest.java b/src/interfaces/jdbc/org/postgresql/test/jdbc2/JBuilderTest.java new file mode 100644 index 0000000000..986f61df38 --- /dev/null +++ b/src/interfaces/jdbc/org/postgresql/test/jdbc2/JBuilderTest.java @@ -0,0 +1,44 @@ +package org.postgresql.test.jdbc2; + +import org.postgresql.test.JDBC2Tests; +import junit.framework.TestCase; +import java.sql.*; +import java.math.BigDecimal; + +/** + * $Id: JBuilderTest.java,v 1.1 2001/02/13 16:39:05 peter Exp $ + * + * Some simple tests to check that the required components needed for JBuilder + * stay working + * + */ +public class JBuilderTest extends TestCase { + + public JBuilderTest(String name) { + super(name); + } + + /** + * This tests that Money types work. JDBCExplorer barfs if this fails. + */ + public void testMoney() { + try { + Connection con = JDBC2Tests.openDB(); + + Statement st=con.createStatement(); + ResultSet rs=st.executeQuery("select cost from test_c"); + assert(rs!=null); + + while(rs.next()){ + double bd = rs.getDouble(1); + } + + rs.close(); + st.close(); + + JDBC2Tests.closeDB(con); + } catch(Exception ex) { + assert(ex.getMessage(),false); + } + } +} diff --git a/src/interfaces/jdbc/org/postgresql/test/jdbc2/MiscTest.java b/src/interfaces/jdbc/org/postgresql/test/jdbc2/MiscTest.java new file mode 100644 index 0000000000..ecad2dc320 --- /dev/null +++ b/src/interfaces/jdbc/org/postgresql/test/jdbc2/MiscTest.java @@ -0,0 +1,47 @@ +package org.postgresql.test.jdbc2; + +import org.postgresql.test.JDBC2Tests; +import junit.framework.TestCase; +import java.sql.*; + +/** + * $Id: MiscTest.java,v 1.1 2001/02/13 16:39:05 peter Exp $ + * + * Some simple tests based on problems reported by users. Hopefully these will + * help prevent previous problems from re-occuring ;-) + * + */ +public class MiscTest extends TestCase { + + public MiscTest(String name) { + super(name); + } + + /** + * Some versions of the driver would return rs as a null? + * + * Sasha was having this problem. + * + * Added Feb 13 2001 + */ + public void testDatabaseSelectNullBug() { + try { + Connection con = JDBC2Tests.openDB(); + + Statement st=con.createStatement(); + ResultSet rs=st.executeQuery("select datname from pg_database"); + assert(rs!=null); + + while(rs.next()){ + String s = rs.getString(1); + } + + rs.close(); + st.close(); + + JDBC2Tests.closeDB(con); + } catch(Exception ex) { + assert(ex.getMessage(),false); + } + } +} diff --git a/src/interfaces/jdbc/org/postgresql/test/jdbc2/TimeTest.java b/src/interfaces/jdbc/org/postgresql/test/jdbc2/TimeTest.java new file mode 100644 index 0000000000..e48a35ce29 --- /dev/null +++ b/src/interfaces/jdbc/org/postgresql/test/jdbc2/TimeTest.java @@ -0,0 +1,123 @@ +package org.postgresql.test.jdbc2; + +import org.postgresql.test.JDBC2Tests; +import junit.framework.TestCase; +import java.sql.*; + +/** + * $Id: TimeTest.java,v 1.1 2001/02/13 16:39:05 peter Exp $ + * + * Some simple tests based on problems reported by users. Hopefully these will + * help prevent previous problems from re-occuring ;-) + * + */ +public class TimeTest extends TestCase { + + public TimeTest(String name) { + super(name); + } + + /** + * Tests the time methods in ResultSet + */ + public void testGetTime() { + try { + Connection con = JDBC2Tests.openDB(); + + Statement st=con.createStatement(); + + JDBC2Tests.createTable(con,"tm time"); + + st.executeUpdate(JDBC2Tests.insert("'01:02:03'")); + st.executeUpdate(JDBC2Tests.insert("'23:59:59'")); + + // Fall through helper + checkTimeTest(con,st); + + st.close(); + + JDBC2Tests.closeDB(con); + } catch(Exception ex) { + assert(ex.getMessage(),false); + } + } + + /** + * Tests the time methods in PreparedStatement + */ + public void testSetTime() { + try { + Connection con = JDBC2Tests.openDB(); + + Statement st=con.createStatement(); + + JDBC2Tests.createTable(con,"tm time"); + + PreparedStatement ps = con.prepareStatement(JDBC2Tests.insert("?")); + + ps.setTime(1,getTime(1,2,3)); + assert(!ps.execute()); // false as its an update! + + ps.setTime(1,getTime(23,59,59)); + assert(!ps.execute()); // false as its an update! + + // Fall through helper + checkTimeTest(con,st); + + ps.close(); + st.close(); + + JDBC2Tests.closeDB(con); + } catch(Exception ex) { + assert(ex.getMessage(),false); + } + } + + /** + * Helper for the TimeTests. It tests what should be in the db + */ + private void checkTimeTest(Connection con,Statement st) throws SQLException { + ResultSet rs=null; + Time t=null; + + rs=st.executeQuery(JDBC2Tests.select("tm")); + assert(rs!=null); + + assert(rs.next()); + t = rs.getTime(1); + assert(t!=null); + assert(getHours(t)==1); + assert(getMinutes(t)==2); + assert(getSeconds(t)==3); + + assert(rs.next()); + t = rs.getTime(1); + assert(t!=null); + assert(getHours(t)==23); + assert(getMinutes(t)==59); + assert(getSeconds(t)==59); + + assert(!rs.next()); + + rs.close(); + } + + /** + * These implement depreciated methods in java.sql.Time + */ + private static long getHours(Time t) { + return (t.getTime() % JDBC2Tests.DAYMILLIS)/3600000; + } + + private static long getMinutes(Time t) { + return ((t.getTime() % JDBC2Tests.DAYMILLIS)/60000)%60; + } + + private static long getSeconds(Time t) { + return ((t.getTime() % JDBC2Tests.DAYMILLIS)/1000)%60; + } + + private Time getTime(int h,int m,int s) { + return new Time(1000*(s+(m*60)+(h*3600))); + } +} diff --git a/src/interfaces/jdbc/org/postgresql/test/jdbc2/TimestampTest.java b/src/interfaces/jdbc/org/postgresql/test/jdbc2/TimestampTest.java new file mode 100644 index 0000000000..4e3022f098 --- /dev/null +++ b/src/interfaces/jdbc/org/postgresql/test/jdbc2/TimestampTest.java @@ -0,0 +1,136 @@ +package org.postgresql.test.jdbc2; + +import org.postgresql.test.JDBC2Tests; +import junit.framework.TestCase; +import java.sql.*; + +/** + * $Id: TimestampTest.java,v 1.1 2001/02/13 16:39:05 peter Exp $ + * + * This has been the most controversial pair of methods since 6.5 was released! + * + * From now on, any changes made to either getTimestamp or setTimestamp + * MUST PASS this TestCase!!! + * + */ +public class TimestampTest extends TestCase { + + public TimestampTest(String name) { + super(name); + } + + /** + * Tests the time methods in ResultSet + */ + public void testGetTimestamp() { + try { + Connection con = JDBC2Tests.openDB(); + + Statement st=con.createStatement(); + + JDBC2Tests.createTable(con,"ts timestamp"); + + st.executeUpdate(JDBC2Tests.insert("'1950-02-07 15:00:00'")); + + // Before you ask why 8:13:00 and not 7:13:00, this is a problem with the + // getTimestamp method in this TestCase. It's simple, brain-dead. It + // simply doesn't know about summer time. As this date is in June, it's + // summer (GMT wise). + // + // This case needs some work done on it. + // + st.executeUpdate(JDBC2Tests.insert("'"+getTimestamp(1970,6,2,8,13,0).toString()+"'")); + + //st.executeUpdate(JDBC2Tests.insert("'1950-02-07'")); + + // Fall through helper + checkTimeTest(con,st); + + st.close(); + + JDBC2Tests.closeDB(con); + } catch(Exception ex) { + assert(ex.getMessage(),false); + } + } + + /** + * Tests the time methods in PreparedStatement + */ + public void testSetTimestamp() { + try { + Connection con = JDBC2Tests.openDB(); + + Statement st=con.createStatement(); + + JDBC2Tests.createTable(con,"ts timestamp"); + + PreparedStatement ps = con.prepareStatement(JDBC2Tests.insert("?")); + + ps.setTimestamp(1,getTimestamp(1950,2,7,15,0,0)); + assert(!ps.execute()); // false as its an update! + + // Before you ask why 8:13:00 and not 7:13:00, this is a problem with the + // getTimestamp method in this TestCase. It's simple, brain-dead. It + // simply doesn't know about summer time. As this date is in June, it's + // summer (GMT wise). + // + // This case needs some work done on it. + // + ps.setTimestamp(1,getTimestamp(1970,6,2,7,13,0)); + assert(!ps.execute()); // false as its an update! + + // Fall through helper + checkTimeTest(con,st); + + ps.close(); + st.close(); + + JDBC2Tests.closeDB(con); + } catch(Exception ex) { + assert(ex.getMessage(),false); + } + } + + /** + * Helper for the TimeTests. It tests what should be in the db + */ + private void checkTimeTest(Connection con,Statement st) throws SQLException { + ResultSet rs=null; + java.sql.Timestamp t=null; + + rs=st.executeQuery(JDBC2Tests.select("ts")); + assert(rs!=null); + + assert(rs.next()); + t = rs.getTimestamp(1); + assert(t!=null); + assert(t.equals(getTimestamp(1950,2,7,15,0,0))); + + assert(rs.next()); + t = rs.getTimestamp(1); + assert(t!=null); + + assert(t.equals(getTimestamp(1970,6,2,7,13,0))); + + assert(!rs.next()); // end of table. Fail if more entries exist. + + rs.close(); + } + + /** + * These implement depreciated methods in java.sql.Time + */ + private static final long dayms = 24*3600*1000; + + /** + * Yes this is ugly, but it gets the test done ;-) + * + * Actually its buggy. We need a better solution to this, then the hack of adding 1 hour to + * entries in June above don't need setting. + */ + private java.sql.Timestamp getTimestamp(int y,int m,int d,int h,int mn,int se) { + return java.sql.Timestamp.valueOf(JDBC2Tests.fix(y,4)+"-"+JDBC2Tests.fix(m,2)+"-"+JDBC2Tests.fix(d,2)+" "+JDBC2Tests.fix(h,2)+":"+JDBC2Tests.fix(mn,2)+":"+JDBC2Tests.fix(se,2)+"."+JDBC2Tests.fix(0,9)); + } + +} diff --git a/src/interfaces/jdbc/org/postgresql/util/PGmoney.java b/src/interfaces/jdbc/org/postgresql/util/PGmoney.java index 99264345e1..ef71e15666 100644 --- a/src/interfaces/jdbc/org/postgresql/util/PGmoney.java +++ b/src/interfaces/jdbc/org/postgresql/util/PGmoney.java @@ -12,7 +12,7 @@ public class PGmoney extends PGobject implements Serializable,Cloneable * The value of the field */ public double val; - + /** * @param value of field */ @@ -20,7 +20,7 @@ public class PGmoney extends PGobject implements Serializable,Cloneable this(); val = value; } - + /** * This is called mainly from the other geometric types, when a * point is imbeded within their definition. @@ -32,7 +32,7 @@ public class PGmoney extends PGobject implements Serializable,Cloneable this(); setValue(value); } - + /** * Required by the driver */ @@ -40,7 +40,7 @@ public class PGmoney extends PGobject implements Serializable,Cloneable { setType("money"); } - + /** * @param s Definition of this point in PostgreSQL's syntax * @exception SQLException on conversion failure @@ -51,10 +51,12 @@ public class PGmoney extends PGobject implements Serializable,Cloneable String s1; boolean negative; - negative = (s.charAt(0) == '-') ; + negative = (s.charAt(0) == '(') ; - s1 = s.substring(negative ? 2 : 1); - + // Remove any () (for negative) & currency symbol + s1 = PGtokenizer.removePara(s).substring(1); + + // Strip out any , in currency int pos = s1.indexOf(','); while (pos != -1) { s1 = s1.substring(0,pos) + s1.substring(pos +1); @@ -68,7 +70,7 @@ public class PGmoney extends PGobject implements Serializable,Cloneable throw new PSQLException("postgresql.money",e); } } - + /** * @param obj Object to compare with * @return true if the two boxes are identical @@ -81,7 +83,7 @@ public class PGmoney extends PGobject implements Serializable,Cloneable } return false; } - + /** * This must be overidden to allow the object to be cloned */ @@ -89,7 +91,7 @@ public class PGmoney extends PGobject implements Serializable,Cloneable { return new PGmoney(val); } - + /** * @return the PGpoint in the syntax expected by org.postgresql */ -- 2.40.0