/*
* Copyright 2004-2011 H2 Group. Multiple-Licensed under the H2 License,
* Version 1.0, and under the Eclipse Public License, Version 1.0
* (http://h2database.com/html/license.html).
* Initial Developer: H2 Group
*/
package org.h2.test.jdbc;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import org.h2.constant.ErrorCode;
import org.h2.test.TestBase;
import org.h2.tools.SimpleResultSet;
/**
* Tests for the CallableStatement class.
*/
public class TestCallableStatement extends TestBase {
/**
* Run just this test.
*
* @param a ignored
*/
public static void main(String... a) throws Exception {
TestBase.createCaller().init().test();
}
public void test() throws SQLException {
deleteDb("callableStatement");
Connection conn = getConnection("callableStatement");
testCallWithResultSet(conn);
testCallWithResult(conn);
testPrepare(conn);
conn.close();
deleteDb("callableStatement");
}
private void testCallWithResultSet(Connection conn) throws SQLException {
CallableStatement call;
ResultSet rs;
call = conn.prepareCall("select 10");
call.execute();
rs = call.getResultSet();
rs.next();
assertEquals(10, rs.getInt(1));
call.executeUpdate();
rs = call.getResultSet();
rs.next();
assertEquals(10, rs.getInt(1));
}
private void testCallWithResult(Connection conn) throws SQLException {
CallableStatement call;
for (String s : new String[]{"{?= call abs(?)}", " { ? = call abs(?)}", " {? = call abs(?)}"}) {
call = conn.prepareCall(s);
call.setInt(2, -3);
call.registerOutParameter(1, Types.INTEGER);
call.execute();
assertEquals(3, call.getInt(1));
call.executeUpdate();
assertEquals(3, call.getInt(1));
}
}
private void testPrepare(Connection conn) throws SQLException {
Statement stat = conn.createStatement();
CallableStatement call;
ResultSet rs;
stat.execute("CREATE TABLE TEST(ID INT, NAME VARCHAR)");
call = conn.prepareCall("INSERT INTO TEST VALUES(?, ?)");
call.setInt(1, 1);
call.setString(2, "Hello");
call.execute();
call = conn.prepareCall("SELECT * FROM TEST",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
rs = call.executeQuery();
rs.next();
assertEquals(1, rs.getInt(1));
assertEquals("Hello", rs.getString(2));
assertFalse(rs.next());
call = conn.prepareCall("SELECT * FROM TEST",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY,
ResultSet.HOLD_CURSORS_OVER_COMMIT);
rs = call.executeQuery();
rs.next();
assertEquals(1, rs.getInt(1));
assertEquals("Hello", rs.getString(2));
assertFalse(rs.next());
stat.execute("CREATE ALIAS testCall FOR \"" + getClass().getName() + ".testCall\"");
call = conn.prepareCall("{CALL testCall(?,?,?)}");
call.setInt("A", 100);
call.setString(2, "abc");
long t = System.currentTimeMillis();
call.setTimestamp("C", new Timestamp(t));
call.registerOutParameter(1, Types.INTEGER);
call.registerOutParameter("B", Types.VARCHAR);
call.executeUpdate();
try {
call.getTimestamp("C");
fail("not registered out parameter accessible");
} catch (SQLException e) {
// expected exception
}
call.registerOutParameter(3, Types.TIMESTAMP);
call.executeUpdate();
assertEquals(t + 1, call.getTimestamp(3).getTime());
assertEquals(200, call.getInt("A"));
assertEquals("ABC", call.getString("B"));
try {
call.getString(100);
fail("incorrect parameter index value");
} catch (SQLException e) {
// expected exception
}
try {
call.getString(0);
fail("incorrect parameter index value");
} catch (SQLException e) {
// expected exception
}
try {
call.getBoolean("X");
fail("incorrect parameter name value");
} catch (SQLException e) {
// expected exception
}
// test for exceptions after closing
call.close();
assertThrows(ErrorCode.OBJECT_CLOSED, call).
executeUpdate();
assertThrows(ErrorCode.OBJECT_CLOSED, call).
registerOutParameter(1, Types.INTEGER);
assertThrows(ErrorCode.OBJECT_CLOSED, call).
getString("X");
}
/**
* This method is called via reflection from the database.
*
* @param conn the connection
* @param a the value a
* @param b the value b
* @param c the value c
* @return a result set
*/
public static ResultSet testCall(Connection conn, int a, String b, Timestamp c) throws SQLException {
SimpleResultSet rs = new SimpleResultSet();
rs.addColumn("A", Types.INTEGER, 0, 0);
rs.addColumn("B", Types.VARCHAR, 0, 0);
rs.addColumn("C", Types.TIMESTAMP, 0, 0);
if ("jdbc:columnlist:connection".equals(conn.getMetaData().getURL())) {
return rs;
}
rs.addRow(a * 2, b.toUpperCase(), new Timestamp(c.getTime() + 1));
return rs;
}
}