patch to add support for callable statements to the jdbc driver. The patch was submitted by Paul Bethe pmbethe@yahoo.com

6 files changed, 396 insertions, 42 deletions

diff --git a/src/interfaces/jdbc/org/postgresql/errors.properties b/src/interfaces/jdbc/org/postgresql/errors.properties
index c2e529ee999..26ff2f724b2 100644
--- a/src/interfaces/jdbc/org/postgresql/errors.properties
+++ b/src/interfaces/jdbc/org/postgresql/errors.properties
@@ -83,3 +83,11 @@ postgresql.updateable.afterlastdelete:After end of result set. Can not call dele
 postgresql.updateable.notoninsertrow:Not on insert row.
 postgresql.updateable.inputstream:Input Stream is null.
 postgresql.updateable.ioerror:Input Stream Error.
+postgresql.call.noreturntype:A CallableStatement Function was declared but no call to 'registerOutParameter (1, <some_type>)' was made.
+postgresql.call.noinout:PostgreSQL only supports function return value [@ 1] (no OUT or INOUT arguments)
+postgresql.call.procasfunc:This Statement [{0}] defines a procedure call (needs ?= call <stmt> to be considered a function.
+postgresql.call.malformed:Malformed stmt [{0}] usage : {1}
+postgresql.call.funcover:Cannot execute Query a call to setXXX (1, ..) was made where argument 1 is the return value of a function.
+postgresql.call.wrongget:Parameter of type {0} was registered but call to get{1} (sqltype={2}) was made.
+postgresql.call.noreturnval:A CallableStatement Function was executed with nothing returned.
+postgresql.call.wrongrtntype:A CallableStatement Function was executed and the return was of type ({0}) however type={1} was registered.
diff --git a/src/interfaces/jdbc/org/postgresql/jdbc2/CallableStatement.java b/src/interfaces/jdbc/org/postgresql/jdbc2/CallableStatement.java
index 2f316234c23..4aa03483253 100644
--- a/src/interfaces/jdbc/org/postgresql/jdbc2/CallableStatement.java
+++ b/src/interfaces/jdbc/org/postgresql/jdbc2/CallableStatement.java
@@ -7,7 +7,7 @@ package org.postgresql.jdbc2;
 
 import java.sql.*;
 import java.math.*;
-
+import org.postgresql.util.*;
 /*
  * CallableStatement is used to execute SQL stored procedures.
  *
@@ -37,6 +37,7 @@ import java.math.*;
  *
  * @see Connection#prepareCall
  * @see ResultSet
+ * @author Paul Bethe (implementer)
  */
 
 public class CallableStatement extends org.postgresql.jdbc2.PreparedStatement implements java.sql.CallableStatement
@@ -46,9 +47,74 @@ public class CallableStatement extends org.postgresql.jdbc2.PreparedStatement im
 	 */
 	public CallableStatement(Connection c, String q) throws SQLException
 	{
-		super(c, q);
+		super(c, q); // don't parse yet..
 	}
 
+
+	/**
+	 * allows this class to tweak the standard JDBC call !see Usage
+	 * -> and replace with the pgsql function syntax
+	 * ie. select <function ([params])> as RESULT;
+	 */
+
+	protected void parseSqlStmt () throws SQLException {
+		modifyJdbcCall ();
+		super.parseSqlStmt ();
+	}
+	/** 
+	 * this method will turn a string of the form
+	 * {? = call <some_function> (?, [?,..]) }
+	 * into the PostgreSQL format which is 
+	 * select <some_function> (?, [?, ...]) as result
+	 * 
+	 */
+	private void modifyJdbcCall () throws SQLException {
+		// syntax checking is not complete only a few basics :(
+		originalSql = sql; // save for error msgs..
+		int index = sql.indexOf ("="); // is implied func or proc?
+		boolean isValid = true;
+		if (index != -1) {
+			isFunction = true;
+			isValid = sql.indexOf ("?") < index; // ? before =			
+		}
+		sql = sql.trim ();
+		if (sql.startsWith ("{") && sql.endsWith ("}")) {
+			sql = sql.substring (1, sql.length() -1);
+		} else isValid = false;
+		index = sql.indexOf ("call"); 
+		if (index == -1 || !isValid)
+			throw new PSQLException ("postgresql.call.malformed", 
+									 new Object[]{sql, JDBC_SYNTAX});
+		sql = sql.replace ('{', ' '); // replace these characters
+		sql = sql.replace ('}', ' ');
+		sql = sql.replace (';', ' ');
+		
+		// this removes the 'call' string and also puts a hidden '?'
+		// at the front of the line for functions, this will
+		// allow the registerOutParameter to work correctly
+                // because in the source sql there was one more ? for the return
+                // value that is not needed by the postgres syntax.  But to make 
+                // sure that the parameter numbers are the same as in the original
+                // sql we add a dummy parameter in this case
+		sql = (isFunction ? "?" : "") + sql.substring (index + 4);
+		
+		sql = "select " + sql + " as " + RESULT_COLUMN + ";";	  
+	}
+
+	// internals 
+	static final String JDBC_SYNTAX = "{[? =] call <some_function> ([? [,?]*]) }";
+	static final String RESULT_COLUMN = "result";
+	String originalSql = "";
+	boolean isFunction;
+	// functionReturnType contains the user supplied value to check
+	// testReturn contains a modified version to make it easier to 
+	// check the getXXX methods..
+	int functionReturnType;
+	int testReturn;
+	// returnTypeSet is true when a proper call to registerOutParameter has been made
+	boolean returnTypeSet;
+	Object result;
+
 	/*
 	 * Before executing a stored procedure call you must explicitly
 	 * call registerOutParameter to register the java.sql.Type of each
@@ -58,6 +124,8 @@ public class CallableStatement extends org.postgresql.jdbc2.PreparedStatement im
 	 * the getXXX method whose Java type XXX corresponds to the
 	 * parameter's registered SQL type.
 	 *
+	 * ONLY 1 RETURN PARAMETER if {?= call ..} syntax is used
+	 *
 	 * @param parameterIndex the first parameter is 1, the second is 2,...
 	 * @param sqlType SQL type code defined by java.sql.Types; for
 	 * parameters of type Numeric or Decimal use the version of
@@ -65,7 +133,55 @@ public class CallableStatement extends org.postgresql.jdbc2.PreparedStatement im
 	 * @exception SQLException if a database-access error occurs.
 	 */
 	public void registerOutParameter(int parameterIndex, int sqlType) throws SQLException
-		{}
+		{
+			if (parameterIndex != 1)
+				throw new PSQLException ("postgresql.call.noinout");
+			if (!isFunction)
+				throw new PSQLException ("postgresql.call.procasfunc", originalSql);
+			
+			// 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;
+		}
+
+	/**
+	 * allow calls to execute update
+	 * @return 1 if succesful call otherwise 0
+	 */
+	public int executeUpdate() throws SQLException
+	{
+		java.sql.ResultSet rs = super.executeQuery (compileQuery());	
+		if (isFunction) {
+			if (!rs.next ())
+				throw new PSQLException ("postgresql.call.noreturnval");
+			result = rs.getObject(1);
+			int columnType = rs.getMetaData().getColumnType(1);
+			if (columnType != functionReturnType) 
+				throw new PSQLException ("postgresql.call.wrongrtntype",
+										 new Object[]{
+					getSqlTypeName (columnType), getSqlTypeName (functionReturnType) }); 
+		}
+		rs.close ();
+		return 1;
+	}
+
+
+	/**
+	 * allow calls to execute update
+	 * @return true if succesful
+	 */
+	public boolean execute() throws SQLException
+	{
+		return (executeUpdate() == 1);
+	}
 
 	/*
 	 * You must also specify the scale for numeric/decimal types:
@@ -82,12 +198,40 @@ public class CallableStatement extends org.postgresql.jdbc2.PreparedStatement im
 	 */
 	public void registerOutParameter(int parameterIndex, int sqlType,
 									 int scale) throws SQLException
-		{}
+		{
+			registerOutParameter (parameterIndex, sqlType); // ignore for now..
+		}
 
-	// Old api?
-	//public boolean isNull(int parameterIndex) throws SQLException {
-	//return true;
-	//}
+	/*
+	 * override this method to check for set @ 1 when declared function..
+	 *
+	 * @param paramIndex the index into the inString
+	 * @param s a string to be stored
+	 * @exception SQLException if something goes wrong
+	 */
+	protected void set(int paramIndex, String s) throws SQLException
+	{
+		if (paramIndex == 1 && isFunction) // need to registerOut instead
+			throw new PSQLException ("postgresql.call.funcover");
+		super.set (paramIndex, s); // else set as usual..
+	}
+
+		/*
+	 * Helper - this compiles the SQL query from the various parameters
+	 * This is identical to toString() except it throws an exception if a
+	 * parameter is unused.
+	 */
+	protected synchronized String compileQuery()
+	throws SQLException
+	{
+		if (isFunction && !returnTypeSet)
+			throw new PSQLException("postgresql.call.noreturntype");
+		if (isFunction) { // set entry 1 to dummy entry..
+			inStrings[0] = ""; // dummy entry which ensured that no one overrode
+			// and calls to setXXX (2,..) really went to first arg in a function call..
+		}
+		return super.compileQuery ();
+	}
 
 	/*
 	 * An OUT parameter may have the value of SQL NULL; wasNull
@@ -101,14 +245,9 @@ public class CallableStatement extends org.postgresql.jdbc2.PreparedStatement im
 	public boolean wasNull() throws SQLException
 	{
 		// check to see if the last access threw an exception
-		return false; // fake it for now
+		return (result == null);
 	}
 
-	// Old api?
-	//public String getChar(int parameterIndex) throws SQLException {
-	//return null;
-	//}
-
 	/*
 	 * Get the value of a CHAR, VARCHAR, or LONGVARCHAR parameter as a
 	 * Java String.
@@ -119,7 +258,8 @@ public class CallableStatement extends org.postgresql.jdbc2.PreparedStatement im
 	 */
 	public String getString(int parameterIndex) throws SQLException
 	{
-		return null;
+		checkIndex (parameterIndex, Types.VARCHAR, "String");
+		return (String)result;
 	}
 	//public String getVarChar(int parameterIndex) throws SQLException {
 	//	 return null;
@@ -138,7 +278,9 @@ public class CallableStatement extends org.postgresql.jdbc2.PreparedStatement im
 	 */
 	public boolean getBoolean(int parameterIndex) throws SQLException
 	{
-		return false;
+		checkIndex (parameterIndex, Types.BIT, "Boolean");
+		if (result == null) return false;
+		return ((Boolean)result).booleanValue ();
 	}
 
 	/*
@@ -150,7 +292,9 @@ public class CallableStatement extends org.postgresql.jdbc2.PreparedStatement im
 	 */
 	public byte getByte(int parameterIndex) throws SQLException
 	{
-		return 0;
+		checkIndex (parameterIndex, Types.TINYINT, "Byte");
+		if (result == null) return 0;
+		return (byte)((Integer)result).intValue ();
 	}
 
 	/*
@@ -162,8 +306,11 @@ public class CallableStatement extends org.postgresql.jdbc2.PreparedStatement im
 	 */
 	public short getShort(int parameterIndex) throws SQLException
 	{
-		return 0;
+		checkIndex (parameterIndex, Types.SMALLINT, "Short");
+		if (result == null) return 0;
+		return (short)((Integer)result).intValue ();
 	}
+		
 
 	/*
 	 * Get the value of an INTEGER parameter as a Java int.
@@ -174,7 +321,9 @@ public class CallableStatement extends org.postgresql.jdbc2.PreparedStatement im
 	 */
 	public int getInt(int parameterIndex) throws SQLException
 	{
-		return 0;
+		checkIndex (parameterIndex, Types.INTEGER, "Int");
+		if (result == null) return 0;
+		return ((Integer)result).intValue ();
 	}
 
 	/*
@@ -186,7 +335,9 @@ public class CallableStatement extends org.postgresql.jdbc2.PreparedStatement im
 	 */
 	public long getLong(int parameterIndex) throws SQLException
 	{
-		return 0;
+		checkIndex (parameterIndex, Types.BIGINT, "Long");
+		if (result == null) return 0;
+		return ((Long)result).longValue ();
 	}
 
 	/*
@@ -198,7 +349,9 @@ public class CallableStatement extends org.postgresql.jdbc2.PreparedStatement im
 	 */
 	public float getFloat(int parameterIndex) throws SQLException
 	{
-		return (float) 0.0;
+		checkIndex (parameterIndex, Types.REAL, "Float");
+		if (result == null) return 0;
+		return ((Float)result).floatValue ();
 	}
 
 	/*
@@ -210,7 +363,9 @@ public class CallableStatement extends org.postgresql.jdbc2.PreparedStatement im
 	 */
 	public double getDouble(int parameterIndex) throws SQLException
 	{
-		return 0.0;
+		checkIndex (parameterIndex, Types.DOUBLE, "Double");
+		if (result == null) return 0;
+		return ((Double)result).doubleValue ();
 	}
 
 	/*
@@ -227,7 +382,8 @@ public class CallableStatement extends org.postgresql.jdbc2.PreparedStatement im
 	public BigDecimal getBigDecimal(int parameterIndex, int scale)
 	throws SQLException
 	{
-		return null;
+		checkIndex (parameterIndex, Types.NUMERIC, "BigDecimal");
+		return ((BigDecimal)result);
 	}
 
 	/*
@@ -240,7 +396,8 @@ public class CallableStatement extends org.postgresql.jdbc2.PreparedStatement im
 	 */
 	public byte[] getBytes(int parameterIndex) throws SQLException
 	{
-		return null;
+		checkIndex (parameterIndex, Types.VARBINARY, "Bytes");
+		return ((byte [])result);
 	}
 
 	// New API (JPM) (getLongVarBinary)
@@ -257,7 +414,8 @@ public class CallableStatement extends org.postgresql.jdbc2.PreparedStatement im
 	 */
 	public java.sql.Date getDate(int parameterIndex) throws SQLException
 	{
-		return null;
+		checkIndex (parameterIndex, Types.DATE, "Date");
+		return (java.sql.Date)result;
 	}
 
 	/*
@@ -269,7 +427,8 @@ public class CallableStatement extends org.postgresql.jdbc2.PreparedStatement im
 	 */
 	public java.sql.Time getTime(int parameterIndex) throws SQLException
 	{
-		return null;
+		checkIndex (parameterIndex, Types.TIME, "Time");
+		return (java.sql.Time)result;
 	}
 
 	/*
@@ -282,7 +441,8 @@ public class CallableStatement extends org.postgresql.jdbc2.PreparedStatement im
 	public java.sql.Timestamp getTimestamp(int parameterIndex)
 	throws SQLException
 	{
-		return null;
+		checkIndex (parameterIndex, Types.TIMESTAMP, "Timestamp");
+		return (java.sql.Timestamp)result;
 	}
 
 	//----------------------------------------------------------------------
@@ -317,7 +477,8 @@ public class CallableStatement extends org.postgresql.jdbc2.PreparedStatement im
 	public Object getObject(int parameterIndex)
 	throws SQLException
 	{
-		return null;
+		checkIndex (parameterIndex);		
+		return result;
 	}
 
 	// ** JDBC 2 Extensions **
@@ -327,9 +488,10 @@ public class CallableStatement extends org.postgresql.jdbc2.PreparedStatement im
 		throw org.postgresql.Driver.notImplemented();
 	}
 
-	public java.math.BigDecimal getBigDecimal(int i) throws SQLException
+	public java.math.BigDecimal getBigDecimal(int parameterIndex) throws SQLException
 	{
-		throw org.postgresql.Driver.notImplemented();
+		checkIndex (parameterIndex, Types.NUMERIC, "BigDecimal");
+		return ((BigDecimal)result);
 	}
 
 	public Blob getBlob(int i) throws SQLException
@@ -367,10 +529,76 @@ public class CallableStatement extends org.postgresql.jdbc2.PreparedStatement im
 		throw org.postgresql.Driver.notImplemented();
 	}
 
+	// no custom types allowed yet..
 	public void registerOutParameter(int parameterIndex, int sqlType, String typeName) throws SQLException
 	{
 		throw org.postgresql.Driver.notImplemented();
 	}
 
+
+
+	/** helperfunction for the getXXX calls to check isFunction and index == 1
+	 */
+	private void checkIndex (int parameterIndex, int type, String getName) 
+		throws SQLException {
+		checkIndex (parameterIndex);
+		if (type != this.testReturn) 
+			throw new PSQLException("postgresql.call.wrongget",
+									new Object[]{getSqlTypeName (testReturn),
+													 getName,
+													 getSqlTypeName (type)});
+	}
+	/** helperfunction for the getXXX calls to check isFunction and index == 1
+	 * @param parameterIndex index of getXXX (index)
+	 * check to make sure is a function and index == 1
+	 */
+	private void checkIndex (int parameterIndex) throws SQLException {
+		if (!isFunction)
+			throw new PSQLException("postgresql.call.noreturntype");
+		if (parameterIndex != 1)
+			throw new PSQLException("postgresql.call.noinout");
+	}
+		
+	/** helper function for creating msg with type names
+	 * @param sqlType a java.sql.Types.XX constant
+	 * @return String which is the name of the constant..
+	 */
+	private static String getSqlTypeName (int sqlType) {
+		switch (sqlType)
+			{
+			case Types.BIT:
+				return "BIT";
+			case Types.SMALLINT:
+				return "SMALLINT";
+			case Types.INTEGER:
+				return "INTEGER";
+			case Types.BIGINT:
+				return "BIGINT";
+			case Types.NUMERIC:
+				return "NUMERIC";
+			case Types.REAL:
+				return "REAL";
+			case Types.DOUBLE:
+				return "DOUBLE";
+			case Types.FLOAT:
+				return "FLOAT";
+			case Types.CHAR:
+				return "CHAR";
+			case Types.VARCHAR:
+				return "VARCHAR";
+			case Types.DATE:
+				return "DATE";
+			case Types.TIME:
+				return "TIME";
+			case Types.TIMESTAMP:
+				return "TIMESTAMP";
+			case Types.BINARY:
+				return "BINARY";
+			case Types.VARBINARY:
+				return "VARBINARY";
+			default:
+				return "UNKNOWN";
+			}
+	}
 }
 
diff --git a/src/interfaces/jdbc/org/postgresql/jdbc2/Connection.java b/src/interfaces/jdbc/org/postgresql/jdbc2/Connection.java
index 86b1a4fc79c..9ab3cced8ed 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.19 2002/06/11 02:55:16 barry Exp $
+ * $Id: Connection.java,v 1.20 2002/06/24 06:16:27 barry Exp $
  *
  * A Connection represents a session with a specific database.	Within the
  * context of a Connection, SQL statements are executed and results are
@@ -135,11 +135,10 @@ public class Connection extends org.postgresql.Connection implements java.sql.Co
 
         public java.sql.CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException
         {
-                throw new PSQLException("postgresql.con.call");
-                //CallableStatement s = new CallableStatement(this,sql);
-                //s.setResultSetType(resultSetType);
-                //s.setResultSetConcurrency(resultSetConcurrency);
-                //return s;
+			CallableStatement s = new CallableStatement(this,sql);
+			s.setResultSetType(resultSetType);
+			s.setResultSetConcurrency(resultSetConcurrency);
+			return s;
         }
 
         /*
diff --git a/src/interfaces/jdbc/org/postgresql/jdbc2/PreparedStatement.java b/src/interfaces/jdbc/org/postgresql/jdbc2/PreparedStatement.java
index d0c07718bdb..eac3acf3aca 100644
--- a/src/interfaces/jdbc/org/postgresql/jdbc2/PreparedStatement.java
+++ b/src/interfaces/jdbc/org/postgresql/jdbc2/PreparedStatement.java
@@ -58,13 +58,16 @@ public class PreparedStatement extends Statement implements java.sql.PreparedSta
 	{
 		super(connection);
 
+		this.sql = sql;
+		this.connection = connection;
+		parseSqlStmt (); // this allows Callable stmt to override
+	}
+
+	protected void parseSqlStmt () throws SQLException {
 		Vector v = new Vector();
 		boolean inQuotes = false;
 		int lastParmEnd = 0, i;
 
-		this.sql = sql;
-		this.connection = connection;
-
 		for (i = 0; i < sql.length(); ++i)
 		{
 			int c = sql.charAt(i);
@@ -118,7 +121,7 @@ public class PreparedStatement extends Statement implements java.sql.PreparedSta
 	 * This is identical to toString() except it throws an exception if a
 	 * parameter is unused.
 	 */
-	private synchronized String compileQuery()
+	protected synchronized String compileQuery()
 	throws SQLException
 	{
 		sbuf.setLength(0);
@@ -818,7 +821,7 @@ public class PreparedStatement extends Statement implements java.sql.PreparedSta
 	 * @param s a string to be stored
 	 * @exception SQLException if something goes wrong
 	 */
-	private void set(int paramIndex, String s) throws SQLException
+	protected void set(int paramIndex, String s) throws SQLException
 	{
 		if (paramIndex < 1 || paramIndex > inStrings.length)
 			throw new PSQLException("postgresql.prep.range");
diff --git a/src/interfaces/jdbc/org/postgresql/test/JDBC2Tests.java b/src/interfaces/jdbc/org/postgresql/test/JDBC2Tests.java
index bacad690281..06c594ec551 100644
--- a/src/interfaces/jdbc/org/postgresql/test/JDBC2Tests.java
+++ b/src/interfaces/jdbc/org/postgresql/test/JDBC2Tests.java
@@ -226,8 +226,9 @@ public class JDBC2Tests extends TestSuite
 
 		// Fastpath/LargeObject
 		suite.addTestSuite(BlobTest.class);
-    suite.addTestSuite( UpdateableResultTest.class );
+		suite.addTestSuite( UpdateableResultTest.class );
 
+		suite.addTestSuite( CallableStmtTest.class );
 		// That's all folks
 		return suite;
 	}
diff --git a/src/interfaces/jdbc/org/postgresql/test/jdbc2/CallableStmtTest.java b/src/interfaces/jdbc/org/postgresql/test/jdbc2/CallableStmtTest.java
new file mode 100644
index 00000000000..5c83d8cdf2a
--- /dev/null
+++ b/src/interfaces/jdbc/org/postgresql/test/jdbc2/CallableStmtTest.java
@@ -0,0 +1,115 @@
+package org.postgresql.test.jdbc2;
+
+import org.postgresql.test.JDBC2Tests;
+import junit.framework.TestCase;
+import java.io.*;
+import java.sql.*;
+
+/*
+ * CallableStatement tests.
+ * @author Paul Bethe
+ */
+public class CallableStmtTest extends TestCase
+{
+	private Connection con;
+
+	public CallableStmtTest (String name)
+	{
+		super(name);
+	}
+
+	protected void setUp() throws Exception
+	{
+		con = JDBC2Tests.openDB();
+		Statement stmt = con.createStatement ();
+		stmt.execute ("CREATE OR REPLACE FUNCTION testspg__getString (varchar) " + 
+					  "RETURNS varchar AS ' DECLARE inString alias for $1; begin "+
+					  "return ''bob''; end; ' LANGUAGE 'plpgsql';");
+		stmt.execute ("CREATE OR REPLACE FUNCTION testspg__getDouble (float) " + 
+					  "RETURNS float AS ' DECLARE inString alias for $1; begin " +
+					  "return 42.42; end; ' LANGUAGE 'plpgsql';");
+		stmt.execute ("CREATE OR REPLACE FUNCTION testspg__getInt (int) RETURNS int " +
+					  " AS 'DECLARE	inString alias for $1; begin " + 
+					  "return 42; end;' LANGUAGE 'plpgsql';"); 
+		stmt.execute ("CREATE OR REPLACE FUNCTION testspg__getNumeric (numeric) " + 
+					  "RETURNS numeric AS ' DECLARE	inString alias for $1; " + 
+					  "begin	return 42; end; ' LANGUAGE 'plpgsql';");
+		stmt.close ();
+	}
+
+	protected void tearDown() throws Exception
+	{
+		Statement stmt = con.createStatement ();
+		stmt.execute ("drop FUNCTION testspg__getString (varchar);");
+		stmt.execute ("drop FUNCTION testspg__getDouble (float);");
+		stmt.execute ("drop FUNCTION testspg__getInt (int);");
+		stmt.execute ("drop FUNCTION testspg__getNumeric (numeric);");
+		JDBC2Tests.closeDB(con);
+	}
+
+
+	final String func = "{ ? = call ";
+	final String pkgName = "testspg__";
+	//	protected void runTest () throws Throwable {
+	//testGetString ();
+	//}
+
+	public void testGetDouble () throws Throwable {
+		// System.out.println ("Testing CallableStmt Types.DOUBLE");
+		CallableStatement call = con.prepareCall (func + pkgName + "getDouble (?) }");
+		call.setDouble (2, (double)3.04);
+		call.registerOutParameter (1, Types.DOUBLE);
+		call.execute ();
+		double result = call.getDouble (1);
+		assertTrue ("correct return from getString ()", result == 42.42);
+	}
+
+	public void testGetInt () throws Throwable {	
+		// System.out.println ("Testing CallableStmt Types.INTEGER");
+		CallableStatement call = con.prepareCall (func + pkgName + "getInt (?) }");
+		call.setInt (2, 4);
+		call.registerOutParameter (1, Types.INTEGER);
+		call.execute ();
+		int result = call.getInt (1);
+		assertTrue ("correct return from getString ()", result == 42);
+	}
+
+	public void testGetNumeric () throws Throwable {	
+		// System.out.println ("Testing CallableStmt Types.NUMERIC");
+		CallableStatement call = con.prepareCall (func + pkgName + "getNumeric (?) }");
+		call.setBigDecimal (2, new java.math.BigDecimal(4));
+		call.registerOutParameter (1, Types.NUMERIC);
+		call.execute ();
+		java.math.BigDecimal result = call.getBigDecimal (1);
+		assertTrue ("correct return from getString ()", 
+					result.equals (new java.math.BigDecimal(42)));
+	}
+
+	public void testGetString () throws Throwable {	
+		// System.out.println ("Testing CallableStmt Types.VARCHAR");
+		CallableStatement call = con.prepareCall (func + pkgName + "getString (?) }");
+		call.setString (2, "foo");
+		call.registerOutParameter (1, Types.VARCHAR);
+		call.execute ();
+		String result = call.getString (1);
+		assertTrue ("correct return from getString ()", result.equals ("bob"));
+
+	}
+
+	public void testBadStmt () throws Throwable {
+		tryOneBadStmt ("{ ?= " + pkgName + "getString (?) }");
+		tryOneBadStmt ("{ ?= call getString (?) ");
+		tryOneBadStmt ("{ = ? call getString (?); }");
+	}
+
+	protected void tryOneBadStmt (String sql) throws Throwable {
+		boolean wasCaught = false;
+		try {
+			CallableStatement call = con.prepareCall (sql);
+		} catch (SQLException e) {
+			wasCaught = true; // good -> this statement was missing something
+		}
+		assertTrue ("bad statment ('"+sql+"')was not caught", wasCaught);
+	}
+			
+}