Attached is a patch that partially implements in/out parameters.
Known issues include batch update fails due to selects being used.
I'd like to know if there are any other issues before proceeding along
this path.
--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561
? callable.diff
Index: org/postgresql/jdbc2/AbstractJdbc2Statement.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.java,v
retrieving revision 1.72
diff -c -r1.72 AbstractJdbc2Statement.java
*** org/postgresql/jdbc2/AbstractJdbc2Statement.java 16 Feb 2005 18:28:45 -0000 1.72
--- org/postgresql/jdbc2/AbstractJdbc2Statement.java 22 Apr 2005 03:01:35 -0000
***************
*** 90,100 ****
// functionReturnType contains the user supplied value to check
// testReturn contains a modified version to make it easier to
// check the getXXX methods..
! private int functionReturnType;
! private int testReturn;
// returnTypeSet is true when a proper call to registerOutParameter has been made
private boolean returnTypeSet;
! protected Object callResult;
protected int maxfieldSize = 0;
public ResultSet createDriverResultSet(Field[] fields, Vector tuples)
--- 90,100 ----
// functionReturnType contains the user supplied value to check
// testReturn contains a modified version to make it easier to
// check the getXXX methods..
! private int []functionReturnType;
! private int []testReturn;
// returnTypeSet is true when a proper call to registerOutParameter has been made
private boolean returnTypeSet;
! protected Object []callResult;
protected int maxfieldSize = 0;
public ResultSet createDriverResultSet(Field[] fields, Vector tuples)
***************
*** 125,130 ****
--- 125,134 ----
this.preparedQuery = connection.getQueryExecutor().createParameterizedQuery(parsed_sql);
this.preparedParameters = preparedQuery.createParameterList();
+ this.testReturn = new int[preparedParameters.getParameterCount()+1];
+ this.functionReturnType = new int[preparedParameters.getParameterCount()+1];
+
+
resultsettype = rsType;
concurrency = rsConcurrency;
}
***************
*** 339,352 ****
if (!rs.next())
throw new PSQLException(GT.tr("A CallableStatement was executed with nothing returned."),
PSQLState.NO_DATA);
! callResult = rs.getObject(1);
! int columnType = rs.getMetaData().getColumnType(1);
! if (columnType != functionReturnType)
! throw new PSQLException (GT.tr("A CallableStatement function was executed and the return was of type
{0}however type {1} was registered.",
new Object[]{
"java.sql.Types=" + columnType, "java.sql.Types=" +
functionReturnType}),
PSQLState.DATA_TYPE_MISMATCH);
!
rs.close();
result = null;
return false;
--- 343,363 ----
if (!rs.next())
throw new PSQLException(GT.tr("A CallableStatement was executed with nothing returned."),
PSQLState.NO_DATA);
! // figure out how many columns
! int cols = rs.getMetaData().getColumnCount();
! callResult = new Object[cols];
!
! // move them into the result set
! for ( int i=0; i < cols; i++)
! {
! callResult[i] = rs.getObject(i+1);
! int columnType = rs.getMetaData().getColumnType(1);
! if (columnType != functionReturnType[i])
! throw new PSQLException (GT.tr("A CallableStatement function was executed and the return was of
type{0} however type {1} was registered.",
new Object[]{
"java.sql.Types=" + columnType, "java.sql.Types=" +
functionReturnType}),
PSQLState.DATA_TYPE_MISMATCH);
! }
rs.close();
result = null;
return false;
***************
*** 418,424 ****
}
private boolean isClosed = false;
!
/*
* getUpdateCount returns the current result as an update count,
* if the result is a ResultSet or there are no more results, -1
--- 429,435 ----
}
private boolean isClosed = false;
! private int lastIndex = 0;
/*
* getUpdateCount returns the current result as an update count,
* if the result is a ResultSet or there are no more results, -1
***************
*** 1700,1712 ****
// functionReturnType contains the user supplied value to check
// testReturn contains a modified version to make it easier to
// check the getXXX methods..
! functionReturnType = sqlType;
! testReturn = sqlType;
! if (functionReturnType == Types.CHAR ||
! functionReturnType == Types.LONGVARCHAR)
! testReturn = Types.VARCHAR;
! else if (functionReturnType == Types.FLOAT)
! testReturn = Types.REAL; // changes to streamline later error checking
returnTypeSet = true;
}
--- 1711,1723 ----
// functionReturnType contains the user supplied value to check
// testReturn contains a modified version to make it easier to
// check the getXXX methods..
! functionReturnType[parameterIndex-1] = sqlType;
! testReturn[parameterIndex-1] = sqlType;
! if (functionReturnType[parameterIndex-1] == Types.CHAR ||
! functionReturnType[parameterIndex-1] == Types.LONGVARCHAR)
! testReturn[parameterIndex-1] = Types.VARCHAR;
! else if (functionReturnType[parameterIndex-1] == Types.FLOAT)
! testReturn[parameterIndex-1] = Types.REAL; // changes to streamline later error checking
returnTypeSet = true;
}
***************
*** 1741,1747 ****
public boolean wasNull() throws SQLException
{
// check to see if the last access threw an exception
! return (callResult == null);
}
/*
--- 1752,1758 ----
public boolean wasNull() throws SQLException
{
// check to see if the last access threw an exception
! return (callResult[lastIndex-1] == null);
}
/*
***************
*** 1756,1762 ****
{
checkClosed();
checkIndex (parameterIndex, Types.VARCHAR, "String");
! return (String)callResult;
}
--- 1767,1773 ----
{
checkClosed();
checkIndex (parameterIndex, Types.VARCHAR, "String");
! return (String)callResult[parameterIndex-1];
}
***************
*** 1773,1779 ****
checkIndex (parameterIndex, Types.BIT, "Boolean");
if (callResult == null)
return false;
! return ((Boolean)callResult).booleanValue ();
}
/*
--- 1784,1790 ----
checkIndex (parameterIndex, Types.BIT, "Boolean");
if (callResult == null)
return false;
! return ((Boolean)callResult[parameterIndex-1]).booleanValue ();
}
/*
***************
*** 1807,1813 ****
checkIndex (parameterIndex, Types.SMALLINT, "Short");
if (callResult == null)
return 0;
! return (short)((Short)callResult).intValue ();
}
--- 1818,1824 ----
checkIndex (parameterIndex, Types.SMALLINT, "Short");
if (callResult == null)
return 0;
! return (short)((Short)callResult[parameterIndex-1]).intValue ();
}
***************
*** 1824,1830 ****
checkIndex (parameterIndex, Types.INTEGER, "Int");
if (callResult == null)
return 0;
! return ((Integer)callResult).intValue ();
}
/*
--- 1835,1841 ----
checkIndex (parameterIndex, Types.INTEGER, "Int");
if (callResult == null)
return 0;
! return ((Integer)callResult[parameterIndex-1]).intValue ();
}
/*
***************
*** 1840,1846 ****
checkIndex (parameterIndex, Types.BIGINT, "Long");
if (callResult == null)
return 0;
! return ((Long)callResult).longValue ();
}
/*
--- 1851,1857 ----
checkIndex (parameterIndex, Types.BIGINT, "Long");
if (callResult == null)
return 0;
! return ((Long)callResult[parameterIndex-1]).longValue ();
}
/*
***************
*** 1856,1862 ****
checkIndex (parameterIndex, Types.REAL, "Float");
if (callResult == null)
return 0;
! return ((Float)callResult).floatValue ();
}
/*
--- 1867,1873 ----
checkIndex (parameterIndex, Types.REAL, "Float");
if (callResult == null)
return 0;
! return ((Float)callResult[parameterIndex-1]).floatValue ();
}
/*
***************
*** 1872,1878 ****
checkIndex (parameterIndex, Types.DOUBLE, "Double");
if (callResult == null)
return 0;
! return ((Double)callResult).doubleValue ();
}
/*
--- 1883,1889 ----
checkIndex (parameterIndex, Types.DOUBLE, "Double");
if (callResult == null)
return 0;
! return ((Double)callResult[parameterIndex-1]).doubleValue ();
}
/*
***************
*** 1891,1897 ****
{
checkClosed();
checkIndex (parameterIndex, Types.NUMERIC, "BigDecimal");
! return ((BigDecimal)callResult);
}
/*
--- 1902,1908 ----
{
checkClosed();
checkIndex (parameterIndex, Types.NUMERIC, "BigDecimal");
! return ((BigDecimal)callResult[parameterIndex-1]);
}
/*
***************
*** 1906,1912 ****
{
checkClosed();
checkIndex (parameterIndex, Types.VARBINARY, Types.BINARY, "Bytes");
! return ((byte [])callResult);
}
--- 1917,1923 ----
{
checkClosed();
checkIndex (parameterIndex, Types.VARBINARY, Types.BINARY, "Bytes");
! return ((byte [])callResult[parameterIndex-1]);
}
***************
*** 1921,1927 ****
{
checkClosed();
checkIndex (parameterIndex, Types.DATE, "Date");
! return (java.sql.Date)callResult;
}
/*
--- 1932,1938 ----
{
checkClosed();
checkIndex (parameterIndex, Types.DATE, "Date");
! return (java.sql.Date)callResult[parameterIndex-1];
}
/*
***************
*** 1935,1941 ****
{
checkClosed();
checkIndex (parameterIndex, Types.TIME, "Time");
! return (java.sql.Time)callResult;
}
/*
--- 1946,1952 ----
{
checkClosed();
checkIndex (parameterIndex, Types.TIME, "Time");
! return (java.sql.Time)callResult[parameterIndex-1];
}
/*
***************
*** 1950,1956 ****
{
checkClosed();
checkIndex (parameterIndex, Types.TIMESTAMP, "Timestamp");
! return (java.sql.Timestamp)callResult;
}
// getObject returns a Java object for the parameter.
--- 1961,1967 ----
{
checkClosed();
checkIndex (parameterIndex, Types.TIMESTAMP, "Timestamp");
! return (java.sql.Timestamp)callResult[parameterIndex-1];
}
// getObject returns a Java object for the parameter.
***************
*** 1978,1984 ****
{
checkClosed();
checkIndex (parameterIndex);
! return callResult;
}
/*
--- 1989,1995 ----
{
checkClosed();
checkIndex (parameterIndex);
! return callResult[parameterIndex-1];
}
/*
***************
*** 2129,2134 ****
--- 2140,2146 ----
case 5: // Should be at 'call ' either at start of string or after ?=
if ((ch == 'c' || ch == 'C') && i + 4 <= len && p_sql.substring(i, i + 4).equalsIgnoreCase("call"))
{
+ isFunction=true;
i += 4;
++state;
}
***************
*** 2243,2249 ****
throws SQLException
{
checkIndex (parameterIndex);
! if (type1 != this.testReturn && type2 != this.testReturn)
throw new PSQLException(GT.tr("Parameter of type {0} was registered, but call to get{1} (sqltype={2}) was
made.",
new Object[]{"java.sql.Types=" + testReturn,
getName,
--- 2255,2261 ----
throws SQLException
{
checkIndex (parameterIndex);
! if (type1 != this.testReturn[parameterIndex-1] && type2 != this.testReturn[parameterIndex-1])
throw new PSQLException(GT.tr("Parameter of type {0} was registered, but call to get{1} (sqltype={2}) was
made.",
new Object[]{"java.sql.Types=" + testReturn,
getName,
***************
*** 2257,2263 ****
throws SQLException
{
checkIndex (parameterIndex);
! if (type != this.testReturn)
throw new PSQLException(GT.tr("Parameter of type {0} was registered, but call to get{1} (sqltype={2}) was
made.",
new Object[]{"java.sql.Types=" + testReturn,
getName,
--- 2269,2275 ----
throws SQLException
{
checkIndex (parameterIndex);
! if (type != this.testReturn[parameterIndex-1])
throw new PSQLException(GT.tr("Parameter of type {0} was registered, but call to get{1} (sqltype={2}) was
made.",
new Object[]{"java.sql.Types=" + testReturn,
getName,
***************
*** 2273,2280 ****
--- 2285,2295 ----
{
if (!isFunction)
throw new PSQLException(GT.tr("A CallableStatement was declared, but no call to registerOutParameter(1,
<sometype>) was made."), PSQLState.STATEMENT_NOT_ALLOWED_IN_FUNCTION_CALL);
+ lastIndex = parameterIndex;
+ /*
if (parameterIndex != 1)
throw new PSQLException (GT.tr("PostgreSQL only supports a single OUT function return value at index
1."),PSQLState.STATEMENT_NOT_ALLOWED_IN_FUNCTION_CALL);
+ */
}
public void setPrepareThreshold(int newThreshold) throws SQLException {
***************
*** 2772,2785 ****
{
checkClosed();
checkIndex(i, Types.ARRAY, "Array");
! return (Array)callResult;
}
public java.math.BigDecimal getBigDecimal(int parameterIndex) throws SQLException
{
checkClosed();
checkIndex (parameterIndex, Types.NUMERIC, "BigDecimal");
! return ((BigDecimal)callResult);
}
public Blob getBlob(int i) throws SQLException
--- 2787,2800 ----
{
checkClosed();
checkIndex(i, Types.ARRAY, "Array");
! return (Array)callResult[i-1];
}
public java.math.BigDecimal getBigDecimal(int parameterIndex) throws SQLException
{
checkClosed();
checkIndex (parameterIndex, Types.NUMERIC, "BigDecimal");
! return ((BigDecimal)callResult[parameterIndex-1]);
}
public Blob getBlob(int i) throws SQLException
Index: org/postgresql/test/jdbc2/CallableStmtTest.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/test/jdbc2/CallableStmtTest.java,v
retrieving revision 1.13
diff -c -r1.13 CallableStmtTest.java
*** org/postgresql/test/jdbc2/CallableStmtTest.java 10 Feb 2005 19:53:17 -0000 1.13
--- org/postgresql/test/jdbc2/CallableStmtTest.java 22 Apr 2005 03:01:35 -0000
***************
*** 25,30 ****
--- 25,38 ----
public CallableStmtTest (String name)
{
super(name);
+ try
+ {
+ Class.forName("org.postgresql.Driver");
+ }
+ catch (Exception ex )
+ {
+
+ }
}
protected void setUp() throws Exception
***************
*** 43,48 ****
--- 51,64 ----
stmt.execute ("CREATE OR REPLACE FUNCTION testspg__getNumeric (numeric) " +
"RETURNS numeric AS ' DECLARE inString alias for $1; " +
"begin return 42; end; ' LANGUAGE 'plpgsql';");
+ stmt.execute("create temp table numeric_tab (MAX_VAL NUMERIC(30,15), MIN_VAL NUMERIC(30,15), NULL_VAL
NUMERIC(30,15)NULL)");
+ stmt.execute("insert into numeric_tab values ( 999999999999999,0.000000000000001, null)");
+ stmt.execute("create type Numeric_Proc_RetType as(it1 numeric(30,15),it2 numeric(30,15),it3
numeric(30,15));");
+ boolean ret = stmt.execute("create function "
+ + "Numeric_Proc(numeric(30,15),numeric(30,15),numeric(30,15)) returns
Numeric_Proc_RetTypeas "
+ + "'declare work_ret record; begin select * into work_ret from Numeric_Tab; return
work_ret;end;' "
+ + "language 'plpgsql';");
+
stmt.execute ("CREATE OR REPLACE FUNCTION testspg__getNumericWithoutArg() " +
"RETURNS numeric AS ' " +
"begin return 42; end; ' LANGUAGE 'plpgsql';");
***************
*** 58,63 ****
--- 74,81 ----
stmt.execute ("drop FUNCTION testspg__getDouble (float);");
stmt.execute ("drop FUNCTION testspg__getInt (int);");
stmt.execute ("drop FUNCTION testspg__getNumeric (numeric);");
+ stmt.execute("drop function Numeric_Proc(numeric, numeric, numeric)");
+ stmt.execute("drop type Numeric_Proc_RetType");
stmt.execute ("drop FUNCTION testspg__getNumericWithoutArg ();");
stmt.execute ("DROP FUNCTION getarray();");
stmt.execute ("DROP FUNCTION raisenotice();");
***************
*** 71,76 ****
--- 89,126 ----
//testGetString ();
//}
+
+ public void testNumeric() throws Throwable
+ {
+ CallableStatement call = con.prepareCall( "{ call Numeric_Proc(?,?,?) }" ) ;
+
+ call.registerOutParameter(1,Types.NUMERIC);
+ call.registerOutParameter(2,Types.NUMERIC);
+ call.registerOutParameter(3,Types.NUMERIC);
+
+ call.setBigDecimal(2,new java.math.BigDecimal(1));
+ call.setBigDecimal(3,new java.math.BigDecimal(2));
+ call.setBigDecimal(4,new java.math.BigDecimal(3));
+
+ call.execute();
+ java.math.BigDecimal ret = call.getBigDecimal(1);
+ assertTrue ("correct return from getNumeric () should be 999999999999999.000000000000000 but returned " +
ret.toString(),
+ ret.equals (new java.math.BigDecimal("999999999999999.000000000000000")));
+
+ ret=call.getBigDecimal(2);
+ assertTrue ("correct return from getNumeric ()",
+ ret.equals (new java.math.BigDecimal("0.000000000000001")));
+ try
+ {
+ ret = call.getBigDecimal(3);
+ }catch(NullPointerException ex)
+ {
+ assertTrue("This should be null",call.wasNull());
+ }
+
+
+ }
+
public void testGetDouble () throws Throwable
{
CallableStatement call = con.prepareCall (func + pkgName + "getDouble (?) }");
Index: org/postgresql/test/jdbc3/TypesTest.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/test/jdbc3/TypesTest.java,v
retrieving revision 1.6
diff -c -r1.6 TypesTest.java
*** org/postgresql/test/jdbc3/TypesTest.java 11 Jan 2005 08:25:49 -0000 1.6
--- org/postgresql/test/jdbc3/TypesTest.java 22 Apr 2005 03:01:35 -0000
***************
*** 19,24 ****
--- 19,32 ----
public TypesTest(String name) {
super(name);
+ try
+ {
+ Class.forName("org.postgresql.Driver");
+ }
+ catch (Exception ex )
+ {
+
+ }
}
protected void setUp() throws SQLException {
***************
*** 64,68 ****
--- 72,84 ----
assertEquals(true, cs.getBoolean(1));
cs.close();
}
+ public void testUnknownType() throws SQLException {
+ Statement stmt = _conn.createStatement();
+
+ ResultSet rs = stmt.executeQuery("select 'foo' as icon1, 'foo2' as icon2 ");
+ assertTrue(rs.next());
+ assertFalse(rs.getString("icon1").equalsIgnoreCase("foo1"));
+ assertFalse(rs.getString("icon2").equalsIgnoreCase("foo2"));
+ }
}