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

2 files changed, 117 insertions, 1 deletions

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);
+	}
+			
+}