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

Example 91 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 92 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");
}

Example 93 with CallableStatement

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

the class CallableStatementTestCase method testPreparedStatement.

private void testPreparedStatement(Connection conn) throws SQLException {
    // using a callable statement like a prepared statement
    CallableStatement call;
    call = conn.prepareCall("create table test(id int)");
    call.executeUpdate();
    call = conn.prepareCall("insert into test values(1), (2)");
    assertEquals(2, call.executeUpdate());
    call = conn.prepareCall("drop table test");
    call.executeUpdate();
}

Example 94 with CallableStatement

use of java.sql.CallableStatement in project druid by alibaba.

the class DBCPTest method test_dbcp.

public void test_dbcp() throws Exception {
    BasicDataSource dataSource = new BasicDataSource();
    dataSource.setDriverClassName(MockDriver.class.getName());
    dataSource.setUrl("jdbc:mock:xxx");
    dataSource.setMaxOpenPreparedStatements(100);
    dataSource.setPoolPreparedStatements(true);
    final String sql = "selelct 1";
    {
        Connection conn = dataSource.getConnection();
        CallableStatement stmt = conn.prepareCall(sql);
        stmt.close();
        conn.close();
    }
    {
        Connection conn = dataSource.getConnection();
        CallableStatement stmt = conn.prepareCall(sql);
        stmt.close();
        conn.close();
    }
}

Example 95 with CallableStatement

use of java.sql.CallableStatement in project hibernate-orm by hibernate.

the class PGGeometryTypeDescriptor method getBinder.

@Override
public <X> ValueBinder<X> getBinder(final JavaTypeDescriptor<X> javaTypeDescriptor) {
    return new BasicBinder<X>(javaTypeDescriptor, this) {

        @Override
        protected void doBind(PreparedStatement st, X value, int index, WrapperOptions options) throws SQLException {
            final WkbEncoder encoder = Wkb.newEncoder(Wkb.Dialect.POSTGIS_EWKB_1);
            final Geometry geometry = getJavaDescriptor().unwrap(value, Geometry.class, options);
            final byte[] bytes = encoder.encode(geometry, ByteOrder.NDR).toByteArray();
            st.setBytes(index, bytes);
        }

        @Override
        protected void doBind(CallableStatement st, X value, String name, WrapperOptions options) throws SQLException {
            final WkbEncoder encoder = Wkb.newEncoder(Wkb.Dialect.POSTGIS_EWKB_1);
            final Geometry geometry = getJavaDescriptor().unwrap(value, Geometry.class, options);
            final byte[] bytes = encoder.encode(geometry, ByteOrder.NDR).toByteArray();
            st.setBytes(name, bytes);
        }
    };
}