/*-------------------------------------------------------------------------
*
* 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();
}
}
}