/*------------------------------------------------------------------------- * * Copyright (c) 2005-2008, PostgreSQL Global Development Group * * IDENTIFICATION * $PostgreSQL: pgjdbc/org/postgresql/test/jdbc3/Jdbc3CallableStatementTest.java,v 1.6 2008/01/08 06:56:31 jurka Exp $ * *------------------------------------------------------------------------- */ package org.postgresql.test.jdbc3; import java.math.BigDecimal; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; import org.postgresql.test.TestUtil; import org.postgresql.util.PSQLState; import junit.framework.TestCase; /** * @author davec * */ public class Jdbc3CallableStatementTest extends TestCase { Connection con; /* (non-Javadoc) * @see junit.framework.TestCase#setUp() */ protected void setUp() throws Exception { con = TestUtil.openDB(); Statement stmt = con.createStatement (); 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 OR REPLACE FUNCTION myiofunc(a INOUT int, b OUT int) AS 'BEGIN b := a; a := 1; END;' LANGUAGE plpgsql"); stmt.execute("CREATE OR REPLACE FUNCTION myif(a INOUT int, b IN int) AS 'BEGIN a := b; END;' LANGUAGE plpgsql"); stmt.execute("create or replace function " + "Numeric_Proc( OUT IMAX NUMERIC(30,15), OUT IMIN NUMERIC(30,15), OUT INUL NUMERIC(30,15)) as " + "'begin " + "select max_val into imax from numeric_tab;" + "select min_val into imin from numeric_tab;" + "select null_val into inul from numeric_tab;" + " end;' " + "language 'plpgsql';"); stmt.execute( "CREATE OR REPLACE FUNCTION test_somein_someout(" + "pa IN int4," + "pb OUT varchar," + "pc OUT int8)" + " AS " + "'begin " + "pb := ''out'';" + "pc := pa + 1;" + "end;'" + "LANGUAGE 'plpgsql' VOLATILE;" ); stmt.execute("CREATE OR REPLACE FUNCTION test_allinout(" + "pa INOUT int4," + "pb INOUT varchar," + "pc INOUT int8)" + " AS " + "'begin " + "pa := pa + 1;" + "pb := ''foo out'';" + "pc := pa + 1;" + "end;'" + "LANGUAGE 'plpgsql' VOLATILE;" ); } /* (non-Javadoc) * @see junit.framework.TestCase#tearDown() */ protected void tearDown() throws Exception { Statement stmt = con.createStatement(); stmt.execute("drop function Numeric_Proc(out decimal, out decimal, out decimal)"); stmt.execute("drop function test_somein_someout(int4)"); stmt.execute("drop function test_allinout( inout int4, inout varchar, inout int8)"); stmt.execute("drop function myiofunc(a INOUT int, b OUT int) "); stmt.execute("drop function myif(a INOUT int, b IN int)"); stmt.close(); TestUtil.closeDB(con); } public void testSomeInOut() throws Throwable { CallableStatement call = con.prepareCall( "{ call test_somein_someout(?,?,?) }" ) ; call.registerOutParameter(2,Types.VARCHAR); call.registerOutParameter(3,Types.BIGINT); call.setInt(1,20); call.execute(); } public void testNotEnoughParameters() throws Throwable { CallableStatement cs = con.prepareCall("{call myiofunc(?,?)}"); cs.setInt(1,2); cs.registerOutParameter(2,Types.INTEGER); try { cs.execute(); fail("Should throw an exception "); } catch( SQLException ex) { assertTrue(ex.getSQLState().equalsIgnoreCase(PSQLState.SYNTAX_ERROR.getState())); } } public void testTooManyParameters() throws Throwable { CallableStatement cs = con.prepareCall("{call myif(?,?)}"); try { cs.setInt(1,1); cs.setInt(2,2); cs.registerOutParameter(1,Types.INTEGER); cs.registerOutParameter(2,Types.INTEGER); cs.execute(); fail("should throw an exception"); } catch( SQLException ex ) { assertTrue(ex.getSQLState().equalsIgnoreCase(PSQLState.SYNTAX_ERROR.getState())); } } public void testAllInOut() throws Throwable { CallableStatement call = con.prepareCall( "{ call test_allinout(?,?,?) }" ) ; call.registerOutParameter(1,Types.INTEGER); call.registerOutParameter(2,Types.VARCHAR); call.registerOutParameter(3,Types.BIGINT); call.setInt(1,20); call.setString(2,"hi"); call.setInt(3,123); call.execute(); call.getInt(1); call.getString(2); call.getLong(3); } public void testNumeric() throws Throwable { CallableStatement call = con.prepareCall( "{ call Numeric_Proc(?,?,?) }" ) ; call.registerOutParameter(1,Types.NUMERIC,15); call.registerOutParameter(2,Types.NUMERIC,15); call.registerOutParameter(3,Types.NUMERIC,15); call.executeUpdate(); 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 testGetObjectDecimal() throws Throwable { try { Statement stmt = con.createStatement(); stmt.execute("create temp table decimal_tab ( max_val numeric(30,15), min_val numeric(30,15), nul_val numeric(30,15) )"); stmt.execute("insert into decimal_tab values (999999999999999.000000000000000,0.000000000000001,null)"); boolean ret = stmt.execute("create or replace function " + "decimal_proc( OUT pmax numeric, OUT pmin numeric, OUT nval numeric) as " + "'begin " + "select max_val into pmax from decimal_tab;" + "select min_val into pmin from decimal_tab;" + "select nul_val into nval from decimal_tab;" + " end;' " + "language 'plpgsql';"); } catch (Exception ex) { fail ( ex.getMessage()); throw ex; } try { CallableStatement cstmt = con.prepareCall("{ call decimal_proc(?,?,?) }"); cstmt.registerOutParameter(1, Types.DECIMAL); cstmt.registerOutParameter(2, Types.DECIMAL ); cstmt.registerOutParameter(3, Types.DECIMAL ); cstmt.executeUpdate(); BigDecimal val = (BigDecimal)cstmt.getObject(1); assertTrue( val.compareTo(new BigDecimal("999999999999999.000000000000000")) == 0 ); val = ( BigDecimal )cstmt.getObject(2); assertTrue( val.compareTo( new BigDecimal( "0.000000000000001")) == 0); val = ( BigDecimal )cstmt.getObject(3); assertTrue( val == null ); } catch ( Exception ex ) { fail(ex.getMessage()); } finally { try { Statement dstmt = con.createStatement(); dstmt.execute("drop function decimal_proc()"); } catch (Exception ex){} } } public void testVarcharBool() throws Throwable { try { Statement stmt = con.createStatement(); stmt.execute("create temp table vartab( max_val text, min_val text)"); stmt.execute("insert into vartab values ('a','b')"); boolean ret = stmt.execute("create or replace function " + "updatevarchar( in imax text, in imin text) returns int as " + "'begin " + "update vartab set max_val = imax;" + "update vartab set min_val = imin;" + "return 0;" + " end;' " + "language 'plpgsql';"); stmt.close(); } catch (Exception ex) { fail ( ex.getMessage()); throw ex; } try { String str = Boolean.TRUE.toString(); CallableStatement cstmt = con.prepareCall("{ call updatevarchar(?,?) }"); cstmt.setObject(1,Boolean.TRUE, Types.VARCHAR); cstmt.setObject(2,Boolean.FALSE, Types.VARCHAR); cstmt.executeUpdate(); cstmt.close(); ResultSet rs = con.createStatement().executeQuery("select * from vartab"); assertTrue(rs.next()); assertTrue( rs.getString(1).equals(Boolean.TRUE.toString()) ); assertTrue( rs.getString(2).equals(Boolean.FALSE.toString()) ); rs.close(); } catch ( Exception ex ) { fail(ex.getMessage()); } finally { try { Statement dstmt = con.createStatement(); dstmt.execute("drop function updatevarchar(text,text)"); } catch (Exception ex){} } } public void testInOut() throws Throwable { try { Statement stmt = con.createStatement(); stmt.execute(createBitTab); stmt.execute(insertBitTab); boolean ret = stmt.execute("create or replace function " + "insert_bit( inout IMAX boolean, inout IMIN boolean, inout INUL boolean) as " + "'begin " + "insert into bit_tab values( imax, imin, inul);" + "select max_val into imax from bit_tab;" + "select min_val into imin from bit_tab;" + "select null_val into inul from bit_tab;" + " end;' " + "language 'plpgsql';"); } catch (Exception ex) { fail ( ex.getMessage()); throw ex; } try { CallableStatement cstmt = con.prepareCall("{ call insert_bit(?,?,?) }"); cstmt.setObject(1,"true", Types.BIT); cstmt.setObject(2,"false", Types.BIT); cstmt.setNull(3,Types.BIT); cstmt.registerOutParameter(1, Types.BIT); cstmt.registerOutParameter(2, Types.BIT); cstmt.registerOutParameter(3, Types.BIT); cstmt.executeUpdate(); assertTrue( cstmt.getBoolean(1) == true ); assertTrue( cstmt.getBoolean(2) == false ); cstmt.getBoolean(3); assertTrue(cstmt.wasNull()); } finally { try { Statement dstmt = con.createStatement(); dstmt.execute("drop function insert_bit(boolean, boolean, boolean)"); } catch (Exception ex){} } } private final String createBitTab = "create temp table bit_tab ( max_val boolean, min_val boolean, null_val boolean )"; private final String insertBitTab = "insert into bit_tab values (true,false,null)"; public void testSetObjectBit() throws Throwable { try { Statement stmt = con.createStatement(); stmt.execute(createBitTab); stmt.execute(insertBitTab); boolean ret = stmt.execute("create or replace function " + "update_bit( in IMAX boolean, in IMIN boolean, in INUL boolean) returns int as " + "'begin " + "update bit_tab set max_val = imax;" + "update bit_tab set min_val = imin;" + "update bit_tab set min_val = inul;" + " return 0;" + " end;' " + "language 'plpgsql';"); } catch (Exception ex) { fail ( ex.getMessage()); throw ex; } try { CallableStatement cstmt = con.prepareCall("{ call update_bit(?,?,?) }"); cstmt.setObject(1,"true", Types.BIT); cstmt.setObject(2,"false", Types.BIT); cstmt.setNull(3,Types.BIT); cstmt.executeUpdate(); cstmt.close(); ResultSet rs = con.createStatement().executeQuery("select * from bit_tab"); assertTrue( rs.next() ); assertTrue( rs.getBoolean(1) == true ); assertTrue( rs.getBoolean(2) == false ); rs.getBoolean(3); assertTrue( rs.wasNull() ); } catch ( Exception ex ) { fail(ex.getMessage()); } finally { try { Statement dstmt = con.createStatement(); dstmt.execute("drop function update_bit(boolean, boolean, boolean)"); } catch (Exception ex){} } } public void testGetObjectLongVarchar() throws Throwable { try { Statement stmt = con.createStatement(); stmt.execute("create temp table longvarchar_tab ( t text, null_val text )"); stmt.execute("insert into longvarchar_tab values ('testdata',null)"); boolean ret = stmt.execute("create or replace function " + "longvarchar_proc( OUT pcn text, OUT nval text) as " + "'begin " + "select t into pcn from longvarchar_tab;" + "select null_val into nval from longvarchar_tab;" + " end;' " + "language 'plpgsql';"); ret = stmt.execute("create or replace function " + "lvarchar_in_name( IN pcn text) returns int as " + "'begin " + "update longvarchar_tab set t=pcn;" + "return 0;" + " end;' " + "language 'plpgsql';"); } catch (Exception ex) { fail ( ex.getMessage()); throw ex; } try { CallableStatement cstmt = con.prepareCall("{ call longvarchar_proc(?,?) }"); cstmt.registerOutParameter(1, Types.LONGVARCHAR); cstmt.registerOutParameter(2, Types.LONGVARCHAR ); cstmt.executeUpdate(); String val = (String)cstmt.getObject(1); assertTrue( val.equals("testdata") ); val = ( String )cstmt.getObject(2); assertTrue( val == null ); cstmt.close(); cstmt = con.prepareCall("{ call lvarchar_in_name(?) }"); String maxFloat = "3.4E38"; cstmt.setObject( 1, new Float(maxFloat), Types.LONGVARCHAR); cstmt.executeUpdate(); cstmt.close(); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("select * from longvarchar_tab"); assertTrue(rs.next()); String rval = (String)rs.getObject(1); assertEquals( rval.trim(), maxFloat.trim()); } catch ( Exception ex ) { fail(ex.getMessage()); } finally { try { Statement dstmt = con.createStatement(); dstmt.execute("drop function longvarchar_proc()"); dstmt.execute("drop function lvarchar_in_name(text)"); } catch (Exception ex){} } } public void testGetBytes01() throws Throwable { byte [] testdata = "TestData".getBytes(); try { Statement stmt = con.createStatement(); stmt.execute("create temp table varbinary_tab ( vbinary bytea, null_val bytea )"); boolean ret = stmt.execute("create or replace function " + "varbinary_proc( OUT pcn bytea, OUT nval bytea) as " + "'begin " + "select vbinary into pcn from varbinary_tab;" + "select null_val into nval from varbinary_tab;" + " end;' " + "language 'plpgsql';"); stmt.close(); PreparedStatement pstmt = con.prepareStatement("insert into varbinary_tab values (?,?)"); pstmt.setBytes( 1, testdata); pstmt.setBytes( 2, null ); pstmt.executeUpdate(); pstmt.close(); } catch (Exception ex) { fail ( ex.getMessage()); throw ex; } try { CallableStatement cstmt = con.prepareCall("{ call varbinary_proc(?,?) }"); cstmt.registerOutParameter(1, Types.VARBINARY); cstmt.registerOutParameter(2, Types.VARBINARY ); cstmt.executeUpdate(); byte [] retval = cstmt.getBytes(1); for( int i = 0; i < testdata.length; i++) { assertTrue( testdata[i] == retval[i] ); } retval = cstmt.getBytes(2); assertTrue( retval == null ); } catch ( Exception ex ) { fail(ex.getMessage()); } finally { try { Statement dstmt = con.createStatement(); dstmt.execute("drop function varbinary_proc()"); } catch (Exception ex){} } } private final String createDecimalTab = "create temp table decimal_tab ( max_val float, min_val float, null_val float )"; private final String insertDecimalTab = "insert into decimal_tab values (1.0E125,1.0E-130,null)"; private final String createFloatProc = "create or replace function " + "float_proc( OUT IMAX float, OUT IMIN float, OUT INUL float) as " + "'begin " + "select max_val into imax from decimal_tab;" + "select min_val into imin from decimal_tab;" + "select null_val into inul from decimal_tab;" + " end;' " + "language 'plpgsql';"; private final String createUpdateFloat = "create or replace function " + "updatefloat_proc ( IN maxparm float, IN minparm float ) returns int as " + "'begin " + "update decimal_tab set max_val=maxparm;" + "update decimal_tab set min_val=minparm;" + "return 0;" + " end;' " + "language 'plpgsql';"; private final String createRealTab = "create temp table real_tab ( max_val float(25), min_val float(25), null_val float(25) )"; private final String insertRealTab = "insert into real_tab values (1.0E37,1.0E-37, null)"; private final String dropFloatProc = "drop function float_proc()"; private final String createUpdateReal = "create or replace function " + "update_real_proc ( IN maxparm float(25), IN minparm float(25) ) returns int as " + "'begin " + "update real_tab set max_val=maxparm;" + "update real_tab set min_val=minparm;" + "return 0;" + " end;' " + "language 'plpgsql';"; private final String dropUpdateReal = "drop function update_real_proc(float, float)"; private final double [] doubleValues = {1.0E125, 1.0E-130}; private final float [] realValues = {(float)1.0E37,(float)1.0E-37}; private final int []intValues = {2147483647,-2147483648}; public void testUpdateReal() throws Throwable { try { Statement stmt = con.createStatement(); stmt.execute(createRealTab); boolean ret = stmt.execute(createUpdateReal); stmt.execute(insertRealTab); stmt.close(); } catch (Exception ex) { fail ( ex.getMessage()); throw ex; } try { CallableStatement cstmt = con.prepareCall("{ call update_real_proc(?,?) }"); BigDecimal val = new BigDecimal( intValues[0] ); float x = val.floatValue(); cstmt.setObject( 1, val, Types.REAL ); val = new BigDecimal( intValues[1]); cstmt.setObject( 2, val, Types.REAL ); cstmt.executeUpdate(); cstmt.close(); ResultSet rs = con.createStatement().executeQuery("select * from real_tab"); assertTrue ( rs.next() ); Float oVal = new Float( intValues[0]); Float rVal = new Float(rs.getObject(1).toString()); assertTrue ( oVal.equals(rVal) ); oVal = new Float( intValues[1] ); rVal = new Float(rs.getObject(2).toString()); assertTrue ( oVal.equals(rVal) ); rs.close(); } catch ( Exception ex ) { fail(ex.getMessage()); } finally { try { Statement dstmt = con.createStatement(); dstmt.execute(dropUpdateReal); dstmt.close(); } catch (Exception ex){} } } public void testUpdateDecimal() throws Throwable { try { Statement stmt = con.createStatement(); stmt.execute(createDecimalTab); boolean ret = stmt.execute(createUpdateFloat); stmt.close(); PreparedStatement pstmt = con.prepareStatement("insert into decimal_tab values (?,?)"); // note these are reversed on purpose pstmt.setDouble(1, doubleValues[1]); pstmt.setDouble(2, doubleValues[0]); pstmt.executeUpdate(); pstmt.close(); } catch (Exception ex) { fail ( ex.getMessage()); throw ex; } try { CallableStatement cstmt = con.prepareCall("{ call updatefloat_proc(?,?) }"); cstmt.setDouble(1, doubleValues[0]); cstmt.setDouble(2, doubleValues[1]); cstmt.executeUpdate(); cstmt.close(); ResultSet rs = con.createStatement().executeQuery("select * from decimal_tab"); assertTrue ( rs.next() ); assertTrue ( rs.getDouble(1) == doubleValues[0]); assertTrue ( rs.getDouble(2) == doubleValues[1]); rs.close(); } catch ( Exception ex ) { fail(ex.getMessage()); } finally { try { Statement dstmt = con.createStatement(); dstmt.execute("drop function updatefloat_proc(float, float)"); } catch (Exception ex){} } } public void testGetBytes02() throws Throwable { byte [] testdata = "TestData".getBytes(); try { Statement stmt = con.createStatement(); stmt.execute("create temp table longvarbinary_tab ( vbinary bytea, null_val bytea )"); boolean ret = stmt.execute("create or replace function " + "longvarbinary_proc( OUT pcn bytea, OUT nval bytea) as " + "'begin " + "select vbinary into pcn from longvarbinary_tab;" + "select null_val into nval from longvarbinary_tab;" + " end;' " + "language 'plpgsql';"); stmt.close(); PreparedStatement pstmt = con.prepareStatement("insert into longvarbinary_tab values (?,?)"); pstmt.setBytes( 1, testdata); pstmt.setBytes( 2, null ); pstmt.executeUpdate(); pstmt.close(); } catch (Exception ex) { fail ( ex.getMessage()); throw ex; } try { CallableStatement cstmt = con.prepareCall("{ call longvarbinary_proc(?,?) }"); cstmt.registerOutParameter(1, Types.LONGVARBINARY); cstmt.registerOutParameter(2, Types.LONGVARBINARY ); cstmt.executeUpdate(); byte [] retval = cstmt.getBytes(1); for( int i = 0; i < testdata.length; i++) { assertTrue( testdata[i] == retval[i] ); } retval = cstmt.getBytes(2); assertTrue( retval == null ); } catch ( Exception ex ) { fail(ex.getMessage()); } finally { try { Statement dstmt = con.createStatement(); dstmt.execute("drop function longvarbinary_proc()"); } catch (Exception ex){} } } public void testGetObjectFloat() throws Throwable { try { Statement stmt = con.createStatement(); stmt.execute( createDecimalTab ); stmt.execute( insertDecimalTab ); boolean ret = stmt.execute(createFloatProc); } catch (Exception ex) { fail ( ex.getMessage()); throw ex; } try { CallableStatement cstmt = con.prepareCall("{ call float_proc(?,?,?) }"); cstmt.registerOutParameter(1,java.sql.Types.FLOAT); cstmt.registerOutParameter(2,java.sql.Types.FLOAT); cstmt.registerOutParameter(3,java.sql.Types.FLOAT); cstmt.executeUpdate(); Double val = (Double)cstmt.getObject(1); assertTrue( val.doubleValue() == doubleValues[0] ); val = (Double)cstmt.getObject(2); assertTrue( val.doubleValue() == doubleValues[1]); val = (Double)cstmt.getObject(3); assertTrue( cstmt.wasNull() ); } catch ( Exception ex ) { fail(ex.getMessage()); } finally { try { Statement dstmt = con.createStatement(); dstmt.execute(dropFloatProc); } catch (Exception ex){} } } public void testGetDouble01() throws Throwable { try { Statement stmt = con.createStatement(); stmt.execute("create temp table d_tab ( max_val float, min_val float, null_val float )"); stmt.execute("insert into d_tab values (1.0E125,1.0E-130,null)"); boolean ret = stmt.execute("create or replace function " + "double_proc( OUT IMAX float, OUT IMIN float, OUT INUL float) as " + "'begin " + "select max_val into imax from d_tab;" + "select min_val into imin from d_tab;" + "select null_val into inul from d_tab;" + " end;' " + "language 'plpgsql';"); } catch (Exception ex) { fail ( ex.getMessage()); throw ex; } try { CallableStatement cstmt = con.prepareCall("{ call double_proc(?,?,?) }"); cstmt.registerOutParameter(1,java.sql.Types.DOUBLE); cstmt.registerOutParameter(2,java.sql.Types.DOUBLE); cstmt.registerOutParameter(3,java.sql.Types.DOUBLE); cstmt.executeUpdate(); assertTrue( cstmt.getDouble(1) == 1.0E125 ); assertTrue( cstmt.getDouble(2) == 1.0E-130); cstmt.getDouble(3); assertTrue( cstmt.wasNull() ); } catch ( Exception ex ) { fail(ex.getMessage()); } finally { try { Statement dstmt = con.createStatement(); dstmt.execute("drop function double_proc()"); } catch (Exception ex){} } } public void testGetDoubleAsReal() throws Throwable { try { Statement stmt = con.createStatement(); stmt.execute("create temp table d_tab ( max_val float, min_val float, null_val float )"); stmt.execute("insert into d_tab values (3.4E38,1.4E-45,null)"); boolean ret = stmt.execute("create or replace function " + "double_proc( OUT IMAX float, OUT IMIN float, OUT INUL float) as " + "'begin " + "select max_val into imax from d_tab;" + "select min_val into imin from d_tab;" + "select null_val into inul from d_tab;" + " end;' " + "language 'plpgsql';"); } catch (Exception ex) { fail ( ex.getMessage()); throw ex; } try { CallableStatement cstmt = con.prepareCall("{ call double_proc(?,?,?) }"); cstmt.registerOutParameter(1,java.sql.Types.REAL); cstmt.registerOutParameter(2,java.sql.Types.REAL); cstmt.registerOutParameter(3,java.sql.Types.REAL); cstmt.executeUpdate(); assertTrue( cstmt.getFloat(1) == 3.4E38f); assertTrue( cstmt.getFloat(2) == 1.4E-45f); cstmt.getFloat(3); assertTrue( cstmt.wasNull() ); } catch ( Exception ex ) { fail(ex.getMessage()); } finally { try { Statement dstmt = con.createStatement(); dstmt.execute("drop function double_proc()"); } catch (Exception ex){} } } public void testGetShort01() throws Throwable { try { Statement stmt = con.createStatement(); stmt.execute("create temp table short_tab ( max_val int2, min_val int2, null_val int2 )"); stmt.execute("insert into short_tab values (32767,-32768,null)"); boolean ret = stmt.execute("create or replace function " + "short_proc( OUT IMAX int2, OUT IMIN int2, OUT INUL int2) as " + "'begin " + "select max_val into imax from short_tab;" + "select min_val into imin from short_tab;" + "select null_val into inul from short_tab;" + " end;' " + "language 'plpgsql';"); } catch (Exception ex) { fail ( ex.getMessage()); throw ex; } try { CallableStatement cstmt = con.prepareCall("{ call short_proc(?,?,?) }"); cstmt.registerOutParameter(1,java.sql.Types.SMALLINT); cstmt.registerOutParameter(2,java.sql.Types.SMALLINT); cstmt.registerOutParameter(3,java.sql.Types.SMALLINT); cstmt.executeUpdate(); assertTrue ( cstmt.getShort(1) == 32767); assertTrue ( cstmt.getShort(2) == -32768); cstmt.getShort(3); assertTrue( cstmt.wasNull() ); } catch ( Exception ex ) { fail(ex.getMessage()); } finally { try { Statement dstmt = con.createStatement(); dstmt.execute("drop function short_proc()"); } catch (Exception ex){} } } public void testGetInt01() throws Throwable { try { Statement stmt = con.createStatement(); stmt.execute("create temp table i_tab ( max_val int, min_val int, null_val int )"); stmt.execute("insert into i_tab values (2147483647,-2147483648,null)"); boolean ret = stmt.execute("create or replace function " + "int_proc( OUT IMAX int, OUT IMIN int, OUT INUL int) as " + "'begin " + "select max_val into imax from i_tab;" + "select min_val into imin from i_tab;" + "select null_val into inul from i_tab;" + " end;' " + "language 'plpgsql';"); } catch (Exception ex) { fail ( ex.getMessage()); throw ex; } try { CallableStatement cstmt = con.prepareCall("{ call int_proc(?,?,?) }"); cstmt.registerOutParameter(1,java.sql.Types.INTEGER); cstmt.registerOutParameter(2,java.sql.Types.INTEGER); cstmt.registerOutParameter(3,java.sql.Types.INTEGER); cstmt.executeUpdate(); assertTrue( cstmt.getInt(1) == 2147483647); assertTrue( cstmt.getInt(2) == -2147483648); cstmt.getInt(3); assertTrue( cstmt.wasNull() ); } catch ( Exception ex ) { fail(ex.getMessage()); } finally { try { Statement dstmt = con.createStatement(); dstmt.execute("drop function int_proc()"); } catch (Exception ex){} } } public void testGetLong01() throws Throwable { try { Statement stmt = con.createStatement(); stmt.execute("create temp table l_tab ( max_val int8, min_val int8, null_val int8 )"); stmt.execute("insert into l_tab values (9223372036854775807,-9223372036854775808,null)"); boolean ret = stmt.execute("create or replace function " + "bigint_proc( OUT IMAX int8, OUT IMIN int8, OUT INUL int8) as " + "'begin " + "select max_val into imax from l_tab;" + "select min_val into imin from l_tab;" + "select null_val into inul from l_tab;" + " end;' " + "language 'plpgsql';"); } catch (Exception ex) { fail ( ex.getMessage()); throw ex; } try { CallableStatement cstmt = con.prepareCall("{ call bigint_proc(?,?,?) }"); cstmt.registerOutParameter(1,java.sql.Types.BIGINT); cstmt.registerOutParameter(2,java.sql.Types.BIGINT); cstmt.registerOutParameter(3,java.sql.Types.BIGINT); cstmt.executeUpdate(); assertTrue(cstmt.getLong( 1 ) == 9223372036854775807l ); assertTrue( cstmt.getLong(2) == -9223372036854775808l ); cstmt.getLong(3); assertTrue( cstmt.wasNull() ); } catch ( Exception ex ) { fail(ex.getMessage()); } finally { try { Statement dstmt = con.createStatement(); dstmt.execute("drop function bigint_proc()"); } catch (Exception ex){} } } public void testGetBoolean01() throws Throwable { try { Statement stmt = con.createStatement(); stmt.execute(createBitTab); stmt.execute(insertBitTab); boolean ret = stmt.execute("create or replace function " + "bit_proc( OUT IMAX boolean, OUT IMIN boolean, OUT INUL boolean) as " + "'begin " + "select max_val into imax from bit_tab;" + "select min_val into imin from bit_tab;" + "select null_val into inul from bit_tab;" + " end;' " + "language 'plpgsql';"); } catch (Exception ex) { fail ( ex.getMessage()); throw ex; } try { CallableStatement cstmt = con.prepareCall("{ call bit_proc(?,?,?) }"); cstmt.registerOutParameter(1,java.sql.Types.BIT); cstmt.registerOutParameter(2,java.sql.Types.BIT); cstmt.registerOutParameter(3,java.sql.Types.BIT); cstmt.executeUpdate(); assertTrue(cstmt.getBoolean( 1 ) ); assertTrue( cstmt.getBoolean(2) == false ); cstmt.getBoolean(3); assertTrue( cstmt.wasNull() ); } catch ( Exception ex ) { fail(ex.getMessage()); } finally { try { Statement dstmt = con.createStatement(); dstmt.execute("drop function bit_proc()"); } catch (Exception ex){} } } public void testGetByte01() throws Throwable { try { Statement stmt = con.createStatement(); stmt.execute("create temp table byte_tab ( max_val int2, min_val int2, null_val int2 )"); stmt.execute("insert into byte_tab values (127,-128,null)"); boolean ret = stmt.execute("create or replace function " + "byte_proc( OUT IMAX int2, OUT IMIN int2, OUT INUL int2) as " + "'begin " + "select max_val into imax from byte_tab;" + "select min_val into imin from byte_tab;" + "select null_val into inul from byte_tab;" + " end;' " + "language 'plpgsql';"); } catch (Exception ex) { fail ( ex.getMessage()); throw ex; } try { CallableStatement cstmt = con.prepareCall("{ call byte_proc(?,?,?) }"); cstmt.registerOutParameter(1,java.sql.Types.TINYINT); cstmt.registerOutParameter(2,java.sql.Types.TINYINT); cstmt.registerOutParameter(3,java.sql.Types.TINYINT); cstmt.executeUpdate(); assertTrue(cstmt.getByte( 1 ) == 127 ); assertTrue( cstmt.getByte(2) == -128 ); cstmt.getByte(3); assertTrue( cstmt.wasNull() ); } catch ( Exception ex ) { fail(ex.getMessage()); } finally { try { Statement dstmt = con.createStatement(); dstmt.execute("drop function byte_proc()"); } catch (Exception ex){} } } public void testMultipleOutExecutions() throws SQLException { CallableStatement cs = con.prepareCall("{call myiofunc(?, ?)}"); for (int i=0; i<10; i++) { cs.registerOutParameter(1, Types.INTEGER); cs.registerOutParameter(2, Types.INTEGER); cs.setInt(1, i); cs.execute(); assertEquals(1, cs.getInt(1)); assertEquals(i, cs.getInt(2)); cs.clearParameters(); } } }