Examples with CallableStatement java.sql.CallableStatement used on opensource projects

Example 96 with CallableStatement

use of java.sql.CallableStatement in project jdbc-shards by wplatform.

the class CallableStatementTestCase method testCallWithResultSet.

private void testCallWithResultSet(Connection conn) throws SQLException {
    CallableStatement call;
    ResultSet rs;
    call = conn.prepareCall("select 10 as a");
    call.execute();
    rs = call.getResultSet();
    rs.next();
    assertEquals(10, rs.getInt(1));
}

Example 97 with CallableStatement

use of java.sql.CallableStatement in project jdbc-shards by wplatform.

the class CallableStatementTestCase method testCallWithResult.

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

Example 98 with CallableStatement

use of java.sql.CallableStatement in project jdbc-shards by wplatform.

the class CallableStatementTestCase method testUnsupportedOperations.

private void testUnsupportedOperations(Connection conn) throws SQLException {
    CallableStatement call;
    call = conn.prepareCall("select 10 as a");
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, call).getURL(1);
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, call).getObject(1, Collections.<String, Class<?>>emptyMap());
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, call).getRef(1);
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, call).getRowId(1);
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, call).getSQLXML(1);
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, call).getURL("a");
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, call).getObject("a", Collections.<String, Class<?>>emptyMap());
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, call).getRef("a");
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, call).getRowId("a");
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, call).getSQLXML("a");
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, call).setURL(1, (URL) null);
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, call).setRef(1, (Ref) null);
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, call).setRowId(1, (RowId) null);
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, call).setSQLXML(1, (SQLXML) null);
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, call).setURL("a", (URL) null);
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, call).setRowId("a", (RowId) null);
    assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, call).setSQLXML("a", (SQLXML) null);
}

Example 99 with CallableStatement

use of java.sql.CallableStatement in project jdbc-shards by wplatform.

the class CallableStatementTestCase method testGetters.

private void testGetters(Connection conn) throws SQLException {
    CallableStatement call;
    call = conn.prepareCall("{?=call ?}");
    call.setLong(2, 1);
    call.registerOutParameter(1, Types.BIGINT);
    call.execute();
    assertEquals(1, call.getLong(1));
    assertEquals(1, call.getByte(1));
    assertEquals(1, ((Long) call.getObject(1)).longValue());
    assertFalse(call.wasNull());
    call.setFloat(2, 1.1f);
    call.registerOutParameter(1, Types.REAL);
    call.execute();
    assertEquals(1.1f, call.getFloat(1));
    call.setDouble(2, Math.PI);
    call.registerOutParameter(1, Types.DOUBLE);
    call.execute();
    assertEquals(Math.PI, call.getDouble(1));
    call.setBytes(2, new byte[11]);
    call.registerOutParameter(1, Types.BINARY);
    call.execute();
    assertEquals(11, call.getBytes(1).length);
    assertEquals(11, call.getBlob(1).length());
    call.setDate(2, java.sql.Date.valueOf("2000-01-01"));
    call.registerOutParameter(1, Types.DATE);
    call.execute();
    assertEquals("2000-01-01", call.getDate(1).toString());
    call.setTime(2, java.sql.Time.valueOf("01:02:03"));
    call.registerOutParameter(1, Types.TIME);
    call.execute();
    assertEquals("01:02:03", call.getTime(1).toString());
    call.setTimestamp(2, java.sql.Timestamp.valueOf("2001-02-03 04:05:06.789"));
    call.registerOutParameter(1, Types.TIMESTAMP);
    call.execute();
    assertEquals("2001-02-03 04:05:06.789", call.getTimestamp(1).toString());
    call.setBoolean(2, true);
    call.registerOutParameter(1, Types.BIT);
    call.execute();
    assertEquals(true, call.getBoolean(1));
    call.setShort(2, (short) 123);
    call.registerOutParameter(1, Types.SMALLINT);
    call.execute();
    assertEquals(123, call.getShort(1));
    call.setBigDecimal(2, BigDecimal.TEN);
    call.registerOutParameter(1, Types.DECIMAL);
    call.execute();
    assertEquals("10", call.getBigDecimal(1).toString());
}

Example 100 with CallableStatement

use of java.sql.CallableStatement in project jdbc-shards by wplatform.

the class CallableStatementTestCase method testPrepare.

private void testPrepare(Connection conn) throws Exception {
    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", 50);
    call.setString("B", "abc");
    long t = System.currentTimeMillis();
    call.setTimestamp("C", new Timestamp(t));
    call.setTimestamp("D", Timestamp.valueOf("2001-02-03 10:20:30.0"));
    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.registerOutParameter(4, Types.TIMESTAMP);
    call.executeUpdate();
    assertEquals(t + 1, call.getTimestamp(3).getTime());
    assertEquals(t + 1, call.getTimestamp("C").getTime());
    assertEquals("2001-02-03 10:20:30.0", call.getTimestamp(4).toString());
    assertEquals("2001-02-03 10:20:30.0", call.getTimestamp("D").toString());
    assertEquals("10:20:30", call.getTime(4).toString());
    assertEquals("10:20:30", call.getTime("D").toString());
    assertEquals("2001-02-03", call.getDate(4).toString());
    assertEquals("2001-02-03", call.getDate("D").toString());
    assertEquals(100, call.getInt(1));
    assertEquals(100, call.getInt("A"));
    assertEquals(100, call.getLong(1));
    assertEquals(100, call.getLong("A"));
    assertEquals("100", call.getBigDecimal(1).toString());
    assertEquals("100", call.getBigDecimal("A").toString());
    assertEquals(100, call.getFloat(1));
    assertEquals(100, call.getFloat("A"));
    assertEquals(100, call.getDouble(1));
    assertEquals(100, call.getDouble("A"));
    assertEquals(100, call.getByte(1));
    assertEquals(100, call.getByte("A"));
    assertEquals(100, call.getShort(1));
    assertEquals(100, call.getShort("A"));
    assertTrue(call.getBoolean(1));
    assertTrue(call.getBoolean("A"));
    assertEquals("ABC", call.getString(2));
    Reader r = call.getCharacterStream(2);
    assertEquals("ABC", IOUtils.readStringAndClose(r, -1));
    r = call.getNCharacterStream(2);
    assertEquals("ABC", IOUtils.readStringAndClose(r, -1));
    assertEquals("ABC", call.getString("B"));
    assertEquals("ABC", call.getNString(2));
    assertEquals("ABC", call.getNString("B"));
    assertEquals("ABC", call.getClob(2).getSubString(1, 3));
    assertEquals("ABC", call.getClob("B").getSubString(1, 3));
    assertEquals("ABC", call.getNClob(2).getSubString(1, 3));
    assertEquals("ABC", call.getNClob("B").getSubString(1, 3));
    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
    }
    call.setCharacterStream("B", new StringReader("xyz"));
    call.executeUpdate();
    assertEquals("XYZ", call.getString("B"));
    call.setCharacterStream("B", new StringReader("xyz-"), 3);
    call.executeUpdate();
    assertEquals("XYZ", call.getString("B"));
    call.setCharacterStream("B", new StringReader("xyz-"), 3L);
    call.executeUpdate();
    assertEquals("XYZ", call.getString("B"));
    call.setAsciiStream("B", new ByteArrayInputStream("xyz".getBytes("UTF-8")));
    call.executeUpdate();
    assertEquals("XYZ", call.getString("B"));
    call.setAsciiStream("B", new ByteArrayInputStream("xyz-".getBytes("UTF-8")), 3);
    call.executeUpdate();
    assertEquals("XYZ", call.getString("B"));
    call.setAsciiStream("B", new ByteArrayInputStream("xyz-".getBytes("UTF-8")), 3L);
    call.executeUpdate();
    assertEquals("XYZ", call.getString("B"));
    call.setClob("B", new StringReader("xyz"));
    call.executeUpdate();
    assertEquals("XYZ", call.getString("B"));
    call.setClob("B", new StringReader("xyz-"), 3);
    call.executeUpdate();
    assertEquals("XYZ", call.getString("B"));
    call.setNClob("B", new StringReader("xyz"));
    call.executeUpdate();
    assertEquals("XYZ", call.getString("B"));
    call.setNClob("B", new StringReader("xyz-"), 3);
    call.executeUpdate();
    assertEquals("XYZ", call.getString("B"));
    call.setString("B", "xyz");
    call.executeUpdate();
    assertEquals("XYZ", call.getString("B"));
    call.setNString("B", "xyz");
    call.executeUpdate();
    assertEquals("XYZ", call.getString("B"));
    // 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");
}