Java Code Examples for java.sql.CallableStatement#setLong()

The following examples show how to use java.sql.CallableStatement#setLong() . These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
/**
 * Test the stored procedure SYSIBM.BLOBGETLENGTH
 *
 * @throws SQLException
 */
public void testBlobTruncateSP() throws SQLException {
    CallableStatement cs = prepareCall
        ("CALL SYSIBM.BLOBTRUNCATE(?,?)");
    cs.setInt(1, 1);
    cs.setLong(2, 10L);
    cs.execute();
    cs.close();

    cs  = prepareCall
        ("? = CALL SYSIBM.BLOBGETLENGTH(?)");
    cs.registerOutParameter(1, java.sql.Types.BIGINT);
    cs.setInt(2, 1);
    cs.executeUpdate();
    //compare the actual length of the test string and the returned length.
    assertEquals("Error SYSIBM.BLOBGETLENGTH returns " +
        "the wrong value for the length of the Blob", 10L
        , cs.getLong(1));
    cs.close();
 }
 
Example 2
/**
 * Setup the test.
 * @throws a SQLException.
 */
public void setUp() throws Exception {
    int locator = 0;
    getConnection().setAutoCommit(false);
    CallableStatement cs  = prepareCall
        ("? = CALL SYSIBM.CLOBCREATELOCATOR()");
    cs.registerOutParameter(1, java.sql.Types.INTEGER);
    cs.executeUpdate();
    locator = cs.getInt(1);
    cs.close();
    cs  = prepareCall("CALL SYSIBM.CLOBSETSTRING(?,?,?,?)");
    cs.setInt(1, locator);
    cs.setInt(2, 1);
    cs.setLong(3, testStrLength);
    cs.setString(4, testStr);
    cs.execute();
    cs.close();
}
 
Example 3
/**
 * Test the stored procedure SYSIBM.CLOBGETSUBSTRING
 *
 * @throws an SQLException.
 */
public void testGetSubStringSP() throws SQLException {
    CallableStatement cs  = prepareCall("? = CALL " +
        "SYSIBM.CLOBGETSUBSTRING(?,?,?)");
    cs.registerOutParameter(1, java.sql.Types.VARCHAR);
    cs.setInt(2, 1);
    cs.setLong(3, 1);
    //get sub-string of length 10 from the clob.
    cs.setInt(4, 10);
    cs.executeUpdate();
    String retVal = cs.getString(1);
    //compare the string that is returned to the sub-string obtained directly
    //from the test string. If found to be equal the stored procedure
    //returns valid values.
    if (testStr.substring(0, 10).compareTo(retVal) != 0) {
        fail("Error SYSIBM.CLOBGETSUBSTRING returns the wrong string");
    }
    cs.close();
}
 
Example 4
/**
 * Test the stored procedure SYSIBM.BLOBGETLENGTH
 *
 * @throws SQLException
 */
public void testBlobTruncateSP() throws SQLException {
    CallableStatement cs = prepareCall
        ("CALL SYSIBM.BLOBTRUNCATE(?,?)");
    cs.setInt(1, 1);
    cs.setLong(2, 10L);
    cs.execute();
    cs.close();

    cs  = prepareCall
        ("? = CALL SYSIBM.BLOBGETLENGTH(?)");
    cs.registerOutParameter(1, java.sql.Types.BIGINT);
    cs.setInt(2, 1);
    cs.executeUpdate();
    //compare the actual length of the test string and the returned length.
    assertEquals("Error SYSIBM.BLOBGETLENGTH returns " +
        "the wrong value for the length of the Blob", 10L
        , cs.getLong(1));
    cs.close();
 }
 
Example 5
Source Project: gemfirexd-oss   File: CallableTest.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Calls a SQL procedure that takes INOUT parameters of various types.
 * @throws SQLException 
 */
public void testManyTypesInoutProc() throws SQLException
{
    CallableStatement cs = prepareCall
        ("call MANY_TYPES_INOUT_PROC(?,?,?,?,?,?,?,?,?,?,?,?)");

    cs.registerOutParameter (2, java.sql.Types.SMALLINT);
    cs.registerOutParameter (4, java.sql.Types.INTEGER);
    cs.registerOutParameter (6, java.sql.Types.BIGINT);
    cs.registerOutParameter (8, java.sql.Types.REAL);
    cs.registerOutParameter (10, java.sql.Types.DOUBLE);
    cs.registerOutParameter (12, java.sql.Types.TIME);

    cs.setShort(1, (short)6);
    cs.setShort(2, (short)9);
    cs.setInt(3, 6);
    cs.setInt(4, 9);
    cs.setLong(5, (long)99999);
    cs.setLong(6, (long)88888888);
    cs.setFloat(7, (float)6.123453);
    cs.setFloat(8, (float)77777);
    cs.setDouble(9, (double)6.123453);
    cs.setDouble(10, (double)8888888888888.01234);
    cs.setTime(11, Time.valueOf("11:06:03"));
    cs.setTime(12, Time.valueOf("10:05:02"));

    cs.execute();

    assertEquals("Short: Sum of 6 + 9", 15, cs.getShort(2));
    assertEquals("Int: Sum of 6 + 9", 15, cs.getInt(4));
    assertEquals("Long: Sum of 99999 + 88888888", 88988887, cs.getLong(6));
    assertEquals("Float: Sum of 6.123453 and 77777" , (float) 77783.123453,
        cs.getFloat(8), .000001);
    assertEquals("Double: Sum of Sum of 6.987654 and 8888888888888.01234",
        8.888888888894135e12, cs.getDouble(10), .000001);
    assertEquals("Time: changed to", Time.valueOf("11:06:03"), 
        cs.getTime(12));

}
 
Example 6
Source Project: gemfirexd-oss   File: CallableTest.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Calls a SQL procedure that takes numeric IN and OUT parameters.
 * Excluded from JSR169/j2ME, which doesn't support get/set BigDecimal yet.
 * @throws SQLException 
 */
public void xtestNumericTypesInAndOutProc() throws SQLException
{
    CallableStatement cs = prepareCall
        ("call NUMERIC_TYPES_IN_AND_OUT_PROC(?,?,?,?,?,?,?,?,?,?,?,?)");

    cs.setShort(1, (short) 3);
    cs.setInt(2, 4);
    cs.setLong(3, 5);
    cs.setFloat(4, (float) 6.0);
    cs.setDouble(5, 7.0);
    cs.setBigDecimal(6, new BigDecimal("88.88"));

    cs.registerOutParameter (7, java.sql.Types.SMALLINT);
    cs.registerOutParameter (8, java.sql.Types.INTEGER);
    cs.registerOutParameter (9, java.sql.Types.BIGINT);
    cs.registerOutParameter (10, java.sql.Types.REAL);
    cs.registerOutParameter (11, java.sql.Types.DOUBLE);
    cs.registerOutParameter (12, java.sql.Types.DECIMAL);

    cs.execute();

    assertEquals("OUT short", (short) 3, cs.getShort(7));
    assertEquals("OUT int"  , 4, cs.getInt(8));
    assertEquals("OUT long" , 5, cs.getLong(9));
    assertEquals("OUT float" , (float) 6.0, cs.getFloat(10), .0001);
    assertEquals("OUT double" , 7.0, cs.getDouble(11), .0001);
    assertDecimalSameValue("OUT decimal", "88.88", cs.getBigDecimal(12));
}
 
Example 7
/**
 * Calls a SQL procedure that takes INOUT parameters of various types.
 * @throws SQLException 
 */
public void testManyTypesInoutProc() throws SQLException
{
    CallableStatement cs = prepareCall
        ("call MANY_TYPES_INOUT_PROC(?,?,?,?,?,?,?,?,?,?,?,?)");

    cs.registerOutParameter (2, java.sql.Types.SMALLINT);
    cs.registerOutParameter (4, java.sql.Types.INTEGER);
    cs.registerOutParameter (6, java.sql.Types.BIGINT);
    cs.registerOutParameter (8, java.sql.Types.REAL);
    cs.registerOutParameter (10, java.sql.Types.DOUBLE);
    cs.registerOutParameter (12, java.sql.Types.TIME);

    cs.setShort(1, (short)6);
    cs.setShort(2, (short)9);
    cs.setInt(3, 6);
    cs.setInt(4, 9);
    cs.setLong(5, (long)99999);
    cs.setLong(6, (long)88888888);
    cs.setFloat(7, (float)6.123453);
    cs.setFloat(8, (float)77777);
    cs.setDouble(9, (double)6.123453);
    cs.setDouble(10, (double)8888888888888.01234);
    cs.setTime(11, Time.valueOf("11:06:03"));
    cs.setTime(12, Time.valueOf("10:05:02"));

    cs.execute();

    assertEquals("Short: Sum of 6 + 9", 15, cs.getShort(2));
    assertEquals("Int: Sum of 6 + 9", 15, cs.getInt(4));
    assertEquals("Long: Sum of 99999 + 88888888", 88988887, cs.getLong(6));
    assertEquals("Float: Sum of 6.123453 and 77777" , (float) 77783.123453,
        cs.getFloat(8), .000001);
    assertEquals("Double: Sum of Sum of 6.987654 and 8888888888888.01234",
        8.888888888894135e12, cs.getDouble(10), .000001);
    assertEquals("Time: changed to", Time.valueOf("11:06:03"), 
        cs.getTime(12));

}
 
Example 8
@Test(expected = SQLException.class)
public void testMultiHostWithException() throws SQLException {
    when(mysqlCallableStatement.executeQuery()).thenThrow(new SQLException());
    try {
        CallableStatement preparedStatement = multiHostConnection.prepareCall("SELECT * FROM test WHERE a = ? OR b = ? OR c=? OR d = ? OR e=?");
        preparedStatement.setBigDecimal(1, new BigDecimal(10000));
        preparedStatement.setBlob(2, inputStream);
        preparedStatement.setBlob(3, inputStream, 1000000L);
        preparedStatement.setByte(3, (byte) 1);
        preparedStatement.setBytes(4, bytesParam);
        preparedStatement.setLong(5, 100L);

        ResultSet resultSet = preparedStatement.executeQuery();

        preparedStatement.close();
    } finally {
        verify(mysqlCallableStatement).executeQuery();
        verify(mysqlCallableStatement, times(0)).close();
        verify(mysqlCallableStatement).setBigDecimal(anyInt(), any(BigDecimal.class));
        verify(mysqlCallableStatement).setBlob(anyInt(), any(InputStream.class));
        verify(mysqlCallableStatement).setBlob(anyInt(), any(InputStream.class), anyLong());
        verify(mysqlCallableStatement).setByte(anyInt(), anyByte());
        assertThat(segmentStorage.getTraceSegments().size(), is(1));
        TraceSegment traceSegment = segmentStorage.getTraceSegments().get(0);
        List<AbstractTracingSpan> spans = SegmentHelper.getSpans(traceSegment);
        assertThat(spans.size(), is(1));
        assertDBSpan(spans.get(0), "Mysql/JDBI/CallableStatement/executeQuery", "SELECT * FROM test WHERE a = ? OR b = ? OR c=? OR d = ? OR e=?");
        List<LogDataEntity> logs = SpanHelper.getLogs(spans.get(0));
        Assert.assertThat(logs.size(), is(1));
        assertDBSpanLog(logs.get(0));
    }
}
 
Example 9
Source Project: gemfirexd-oss   File: CallableTest.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Calls a SQL procedure that takes INOUT parameters of various types.
 * @throws SQLException 
 */
public void testManyTypesInoutProc() throws SQLException
{
    CallableStatement cs = prepareCall
        ("call MANY_TYPES_INOUT_PROC(?,?,?,?,?,?,?,?,?,?,?,?)");

    cs.registerOutParameter (2, java.sql.Types.SMALLINT);
    cs.registerOutParameter (4, java.sql.Types.INTEGER);
    cs.registerOutParameter (6, java.sql.Types.BIGINT);
    cs.registerOutParameter (8, java.sql.Types.REAL);
    cs.registerOutParameter (10, java.sql.Types.DOUBLE);
    cs.registerOutParameter (12, java.sql.Types.TIME);

    cs.setShort(1, (short)6);
    cs.setShort(2, (short)9);
    cs.setInt(3, 6);
    cs.setInt(4, 9);
    cs.setLong(5, (long)99999);
    cs.setLong(6, (long)88888888);
    cs.setFloat(7, (float)6.123453);
    cs.setFloat(8, (float)77777);
    cs.setDouble(9, (double)6.123453);
    cs.setDouble(10, (double)8888888888888.01234);
    cs.setTime(11, Time.valueOf("11:06:03"));
    cs.setTime(12, Time.valueOf("10:05:02"));

    cs.execute();

    assertEquals("Short: Sum of 6 + 9", 15, cs.getShort(2));
    assertEquals("Int: Sum of 6 + 9", 15, cs.getInt(4));
    assertEquals("Long: Sum of 99999 + 88888888", 88988887, cs.getLong(6));
    assertEquals("Float: Sum of 6.123453 and 77777" , (float) 77783.123453,
        cs.getFloat(8), .000001);
    assertEquals("Double: Sum of Sum of 6.987654 and 8888888888888.01234",
        8.888888888894135e12, cs.getDouble(10), .000001);
    assertEquals("Time: changed to", Time.valueOf("11:06:03"), 
        cs.getTime(12));

}
 
Example 10
/**
 * Setup the test.
 * @throws UnsupportedEncodingException 
 * @throws a SQLException.
 */
protected void setUp() throws SQLException, UnsupportedEncodingException {
	 //Byte array obatined from the string
	byte [] strBytes = testStr.getBytes("US-ASCII");

    //initialize the locator to a default value.
    int locator = -1;
    //set auto commit to false for the connection
    getConnection().setAutoCommit(false);
    //call the stored procedure to return the created locator.
    CallableStatement cs  = prepareCall
        ("? = CALL SYSIBM.BLOBCREATELOCATOR()");
    cs.registerOutParameter(1, java.sql.Types.INTEGER);
    cs.executeUpdate();
    locator = cs.getInt(1);
    cs.close();
    //use this new locator to test the SETBYTES function
    //by inserting the new bytes and testing whether it has
    //been inserted properly.

    //Insert the new substring.
    cs  = prepareCall("CALL SYSIBM.BLOBSETBYTES(?,?,?,?)");
    cs.setInt(1, locator);
    cs.setLong(2, 1L);
    cs.setInt(3, (int)testStrLength);
    cs.setBytes(4, strBytes);
    cs.execute();
    cs.close();
}
 
Example 11
/**
 * Tests the SYSIBM.CLOBGETPOSITIONFROMLOCATOR stored procedure.
 *
 * @throws SQLException.
 */
public void testClobGetPositionFromLocatorSP() throws SQLException {
    int locator = 0;

    String newStr = "simple";

    CallableStatement cs  = prepareCall
        ("? = CALL SYSIBM.CLOBCREATELOCATOR()");
    cs.registerOutParameter(1, java.sql.Types.INTEGER);
    cs.executeUpdate();
    locator = cs.getInt(1);
    cs.close();

    cs  = prepareCall("CALL SYSIBM.CLOBSETSTRING(?,?,?,?)");
    cs.setInt(1, locator);
    cs.setInt(2, 1);
    cs.setLong(3, newStr.length());
    cs.setString(4, newStr);
    cs.execute();

    cs.close();
    cs  = prepareCall
        ("? = CALL SYSIBM.CLOBGETPOSITIONFROMLOCATOR(?,?,?)");
    cs.registerOutParameter(1, java.sql.Types.BIGINT);
    cs.setInt(2, 1);
    //find the position of the bytes corresponding to
    //the String simple in the test string.
    cs.setInt(3, locator);
    cs.setLong(4, 1L);
    cs.executeUpdate();
    //check to see that the returned position and the expected position
    //of the substring simple in the string are matching.
    assertEquals("Error SYSIBM.CLOBGETPOSITIONFROMLOCATOR returns " +
        "the wrong value for the position of the Clob", 8, cs.getLong(1));
    cs.close();
}
 
Example 12
Source Project: binnavi   File: PostgreSQLTypeFunctions.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Creates a new type instance reference in the database.
 *
 * @param connection The connection to the database.
 * @param moduleId The id of the module that contains the reference.
 * @param address The address of the operand referencing the type instance.
 * @param position The position of the operand within the instruction.
 * @param expressionId The id of the corresponding expression.
 * @param typeInstanceId The id of the referred type instance.
 * @throws CouldntSaveDataException Thrown if the type instance reference could not be written to
 *         the database.
 */
public static void createTypeInstanceReference(final Connection connection,
    final int moduleId,
    final long address,
    final int position,
    final int expressionId,
    final int typeInstanceId) throws CouldntSaveDataException {

  Preconditions.checkNotNull(connection, "Error: connection argument can not be null");
  Preconditions.checkArgument(moduleId > 0, "Error: module id must be greater than zero");
  Preconditions.checkArgument(position >= 0, "Error: position must be larger or equal to zero");
  Preconditions.checkArgument(expressionId > 0,
      "Error: expression id must be larger or equal to zero");
  Preconditions.checkArgument(typeInstanceId >= 0,
      "Error: type instance id must be larger or equal to zero");

  final String query = " { call create_expression_type_instance(?, ?, ?, ?, ?) } ";
  try {
    final CallableStatement procedure = connection.prepareCall(query);
    try {
      procedure.setInt(1, moduleId);
      procedure.setLong(2, address);
      procedure.setInt(3, position);
      procedure.setInt(4, expressionId);
      procedure.setInt(5, typeInstanceId);
      procedure.execute();
    } finally {
      procedure.close();
    }
  } catch (final SQLException exception) {
    throw new CouldntSaveDataException(exception);
  }
}
 
Example 13
/**
 * Tests fix for BUG#87704 (26771560) - THE STREAM GETS THE RESULT SET ?THE DRIVER SIDE GET WRONG ABOUT GETLONG().
 * 
 * @throws Exception
 *             if an error occurs.
 */
public void testBug87704() throws Exception {
    if (!serverSupportsStoredProcedures()) {
        return;
    }

    createProcedure("testBug87704",
            "(IN PARAMIN BIGINT, OUT PARAM_OUT_LONG BIGINT, OUT PARAM_OUT_STR VARCHAR(100))\nBEGIN\nSET PARAM_OUT_LONG = PARAMIN + 100000;\nSET PARAM_OUT_STR = concat('STR' ,PARAM_OUT_LONG);end\n");

    final Properties props = new Properties();
    props.setProperty("useSSL", "false");
    props.setProperty("useServerPrepStmts", "true");
    props.setProperty("cachePrepStmts", "true");
    props.setProperty("prepStmtCacheSize", "500");
    props.setProperty("prepStmtCacheSqlLimit", "2048");
    props.setProperty("useOldAliasMetadataBehavior", "true");
    props.setProperty("rewriteBatchedStatements", "true");
    props.setProperty("useCursorFetch", "true");
    props.setProperty("defaultFetchSize", "100");

    Connection con = getConnectionWithProps(props);

    CallableStatement callableStatement = null;
    try {
        callableStatement = con.prepareCall("call testBug87704(?,?,?)");
        callableStatement.setLong(1, 30214567L);
        callableStatement.registerOutParameter(2, Types.BIGINT);
        callableStatement.registerOutParameter(3, Types.VARCHAR);
        callableStatement.execute();
        System.out.println(callableStatement.getLong(2));
        System.out.println(callableStatement.getString(3));

        assertEquals(30314567L, callableStatement.getLong(2));
        assertEquals("STR30314567", callableStatement.getString(3));

    } finally {
        if (callableStatement != null) {
            callableStatement.close();
        }
        if (con != null) {
            con.close();
        }
    }
}
 
Example 14
/**
 * Tests fix for BUG#87704 (26771560) - THE STREAM GETS THE RESULT SET ?THE DRIVER SIDE GET WRONG ABOUT GETLONG().
 * 
 * @throws Exception
 *             if an error occurs.
 */
public void testBug87704() throws Exception {
    if (!serverSupportsStoredProcedures()) {
        return;
    }

    createProcedure("testBug87704",
            "(IN PARAMIN BIGINT, OUT PARAM_OUT_LONG BIGINT, OUT PARAM_OUT_STR VARCHAR(100))\nBEGIN\nSET PARAM_OUT_LONG = PARAMIN + 100000;\nSET PARAM_OUT_STR = concat('STR' ,PARAM_OUT_LONG);end\n");

    final Properties props = new Properties();
    props.setProperty("useSSL", "false");
    props.setProperty("allowPublicKeyRetrieval", "true");
    props.setProperty("useServerPrepStmts", "true");
    props.setProperty("cachePrepStmts", "true");
    props.setProperty("prepStmtCacheSize", "500");
    props.setProperty("prepStmtCacheSqlLimit", "2048");
    props.setProperty("useOldAliasMetadataBehavior", "true");
    props.setProperty("rewriteBatchedStatements", "true");
    props.setProperty("useCursorFetch", "true");
    props.setProperty("defaultFetchSize", "100");

    Connection con = getConnectionWithProps(props);

    CallableStatement callableStatement = null;
    try {
        callableStatement = con.prepareCall("call testBug87704(?,?,?)");
        callableStatement.setLong(1, 30214567L);
        callableStatement.registerOutParameter(2, Types.BIGINT);
        callableStatement.registerOutParameter(3, Types.VARCHAR);
        callableStatement.execute();
        System.out.println(callableStatement.getLong(2));
        System.out.println(callableStatement.getString(3));

        assertEquals(30314567L, callableStatement.getLong(2));
        assertEquals("STR30314567", callableStatement.getString(3));

    } finally {
        if (callableStatement != null) {
            callableStatement.close();
        }
        if (con != null) {
            con.close();
        }
    }
}
 
Example 15
/**
 * Tests the stored procedure SYSIBM.BLOBSETBYTES
 * @throws UnsupportedEncodingException 
 *
 * @throws SQLException.
 */
public void testBlobSetBytes() throws SQLException, UnsupportedEncodingException {
    String newString = "123456789012345";
    byte [] newBytes = newString.getBytes("US-ASCII");
    //initialize the locator to a default value.
    int locator = -1;
    //call the stored procedure to return the created locator.
    CallableStatement cs  = prepareCall
        ("? = CALL SYSIBM.BLOBCREATELOCATOR()");
    cs.registerOutParameter(1, java.sql.Types.INTEGER);
    cs.executeUpdate();
    locator = cs.getInt(1);
    cs.close();

    //use this new locator to test the SETBYTES function
    //by inserting the new bytes and testing whether it has
    //been inserted properly.

    //Insert the new substring.
    cs  = prepareCall("CALL SYSIBM.BLOBSETBYTES(?,?,?,?)");
    cs.setInt(1, locator);
    cs.setLong(2, 1L);
    cs.setInt(3, newString.length());
    cs.setBytes(4, newBytes);
    cs.execute();
    cs.close();

    //check the new locator to see if the value has been inserted correctly.
    cs  = prepareCall("? = CALL " +
        "SYSIBM.BLOBGETBYTES(?,?,?)");
    cs.registerOutParameter(1, java.sql.Types.VARBINARY);
    cs.setInt(2, locator);
    cs.setLong(3, 1);
    cs.setInt(4, newString.length());
    cs.executeUpdate();
    byte [] retVal = cs.getBytes(1);
    //compare the new bytes and the bytes returned by the stored
    //procedure to see of they are the same.
    for (int i=0;i<newString.length();i++){
        assertEquals
            ("The Stored procedure SYSIBM.BLOBGETBYTES " +
            "returns the wrong bytes"
            , newBytes[i], retVal[i]);
    }
    cs.close();
}
 
Example 16
/**
 * Tests the stored procedure SYSIBM.CLOBSETSTRING
 *
 * @throws SQLException.
 */
public void testClobSetStringSP() throws SQLException {
    String newString = "123456789012345";
    //initialize the locator to a default value.
    int locator = -1;
    //call the stored procedure to return the created locator.
    CallableStatement cs  = prepareCall
        ("? = CALL SYSIBM.CLOBCREATELOCATOR()");
    cs.registerOutParameter(1, java.sql.Types.INTEGER);
    cs.executeUpdate();
    locator = cs.getInt(1);
    cs.close();

    //use this new locator to test the SETSUBSTRING function
    //by inserting a new sub string and testing whether it has
    //been inserted properly.

    //Insert the new substring.
    cs  = prepareCall("CALL SYSIBM.CLOBSETSTRING(?,?,?,?)");
    cs.setInt(1, locator);
    cs.setInt(2, 1);
    cs.setLong(3, newString.length());
    cs.setString(4, newString);
    cs.execute();
    cs.close();

    //check the new locator to see if the value has been inserted correctly.
    cs  = prepareCall("? = CALL " +
        "SYSIBM.CLOBGETSUBSTRING(?,?,?)");
    cs.registerOutParameter(1, java.sql.Types.VARCHAR);
    cs.setInt(2, locator);
    cs.setLong(3, 1);
    //get sub-string of length 10 from the clob.
    cs.setInt(4, newString.length());
    cs.executeUpdate();
    String retVal = cs.getString(1);
    //compare the new string and the string returned by the stored
    //procedure to see of they are the same.
    if (newString.compareTo(retVal) != 0)
        fail("SYSIBM.CLOBSETSTRING does not insert the right value");
    cs.close();
}
 
Example 17
/**
 * Utility method which registers in a CallableStatement object the different {@link amforeas.jdbc.StoredProcedureParam}
 * instances in the given list. Returns a List of {@link amforeas.jdbc.StoredProcedureParam} with all the OUT parameters
 * registered in the CallableStatement
 * @param cs the CallableStatement object where the parameters are registered.
 * @param params a list of {@link amforeas.jdbc.StoredProcedureParam}
 * @return a list of OUT {@link amforeas.jdbc.StoredProcedureParam} 
 * @throws SQLException if we fail to register any of the parameters in the CallableStatement
 * @throws AmforeasBadRequestException 
 */
private List<StoredProcedureParam> addParameters (final CallableStatement cs, final List<StoredProcedureParam> params) throws SQLException, AmforeasBadRequestException {
    final List<StoredProcedureParam> outParams = new ArrayList<StoredProcedureParam>();
    int i = 1;
    for (StoredProcedureParam p : params) {
        final Integer sqlType = p.getSqlType();
        if (p.isOutParameter()) {
            l.debug("Adding OUT parameter " + p.toString());
            cs.registerOutParameter(i++, sqlType);
            outParams.add(p);
        } else {
            l.debug("Adding IN parameter " + p.toString());
            switch (sqlType) {
                case Types.BIGINT:
                case Types.INTEGER:
                case Types.TINYINT:
                    // case Types.NUMERIC:
                    cs.setInt(i++, Integer.valueOf(p.getValue()));
                    break;
                case Types.DATE:
                    cs.setDate(i++, (Date) AmforeasUtils.parseValue(p.getValue()));
                    break;
                case Types.TIME:
                    cs.setTime(i++, (Time) AmforeasUtils.parseValue(p.getValue()));
                    break;
                case Types.TIMESTAMP:
                    cs.setTimestamp(i++, (Timestamp) AmforeasUtils.parseValue(p.getValue()));
                    break;
                case Types.DECIMAL:
                    cs.setBigDecimal(i++, (BigDecimal) AmforeasUtils.parseValue(p.getValue()));
                    break;
                case Types.DOUBLE:
                    cs.setDouble(i++, Double.valueOf(p.getValue()));
                    break;
                case Types.FLOAT:
                    cs.setLong(i++, Long.valueOf(p.getValue()));
                    break;
                default:
                    cs.setString(i++, p.getValue());
                    break;
            }
        }
    }
    return outParams;
}
 
Example 18
/**
 * Tests fix for BUG#87704 (26771560) - THE STREAM GETS THE RESULT SET ?THE DRIVER SIDE GET WRONG ABOUT GETLONG().
 * 
 * @throws Exception
 *             if an error occurs.
 */
public void testBug87704() throws Exception {
    createProcedure("testBug87704",
            "(IN PARAMIN BIGINT, OUT PARAM_OUT_LONG BIGINT, OUT PARAM_OUT_STR VARCHAR(100))\nBEGIN\nSET PARAM_OUT_LONG = PARAMIN + 100000;\nSET PARAM_OUT_STR = concat('STR' ,PARAM_OUT_LONG);end\n");

    final Properties props = new Properties();
    props.setProperty(PropertyKey.useSSL.getKeyName(), "false");
    props.setProperty(PropertyKey.allowPublicKeyRetrieval.getKeyName(), "true");
    props.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), "true");
    props.setProperty(PropertyKey.cachePrepStmts.getKeyName(), "true");
    props.setProperty(PropertyKey.prepStmtCacheSize.getKeyName(), "500");
    props.setProperty(PropertyKey.prepStmtCacheSqlLimit.getKeyName(), "2048");
    props.setProperty(PropertyKey.useOldAliasMetadataBehavior.getKeyName(), "true");
    props.setProperty(PropertyKey.rewriteBatchedStatements.getKeyName(), "true");
    props.setProperty(PropertyKey.useCursorFetch.getKeyName(), "true");
    props.setProperty(PropertyKey.defaultFetchSize.getKeyName(), "100");

    Connection con = getConnectionWithProps(props);

    CallableStatement callableStatement = null;
    try {
        callableStatement = con.prepareCall("call testBug87704(?,?,?)");
        callableStatement.setLong(1, 30214567L);
        callableStatement.registerOutParameter(2, Types.BIGINT);
        callableStatement.registerOutParameter(3, Types.VARCHAR);
        callableStatement.execute();
        System.out.println(callableStatement.getLong(2));
        System.out.println(callableStatement.getString(3));

        assertEquals(30314567L, callableStatement.getLong(2));
        assertEquals("STR30314567", callableStatement.getString(3));

    } finally {
        if (callableStatement != null) {
            callableStatement.close();
        }
        if (con != null) {
            con.close();
        }
    }
}
 
Example 19
/**
 * Tests the stored procedure SYSIBM.CLOBSETSTRING
 *
 * @throws SQLException.
 */
public void testClobSetStringSP() throws SQLException {
    String newString = "123456789012345";
    //initialize the locator to a default value.
    int locator = -1;
    //call the stored procedure to return the created locator.
    CallableStatement cs  = prepareCall
        ("? = CALL SYSIBM.CLOBCREATELOCATOR()");
    cs.registerOutParameter(1, java.sql.Types.INTEGER);
    cs.executeUpdate();
    locator = cs.getInt(1);
    cs.close();

    //use this new locator to test the SETSUBSTRING function
    //by inserting a new sub string and testing whether it has
    //been inserted properly.

    //Insert the new substring.
    cs  = prepareCall("CALL SYSIBM.CLOBSETSTRING(?,?,?,?)");
    cs.setInt(1, locator);
    cs.setInt(2, 1);
    cs.setLong(3, newString.length());
    cs.setString(4, newString);
    cs.execute();
    cs.close();

    //check the new locator to see if the value has been inserted correctly.
    cs  = prepareCall("? = CALL " +
        "SYSIBM.CLOBGETSUBSTRING(?,?,?)");
    cs.registerOutParameter(1, java.sql.Types.VARCHAR);
    cs.setInt(2, locator);
    cs.setLong(3, 1);
    //get sub-string of length 10 from the clob.
    cs.setInt(4, newString.length());
    cs.executeUpdate();
    String retVal = cs.getString(1);
    //compare the new string and the string returned by the stored
    //procedure to see of they are the same.
    if (newString.compareTo(retVal) != 0)
        fail("SYSIBM.CLOBSETSTRING does not insert the right value");
    cs.close();
}
 
Example 20
/**
 * Test the stored procedure SYSIBM.CLOBGETLENGTH
 *
 * @throws SQLException
 */
public void testClobTruncateSP() throws SQLException {

   //----------TO BE ENABLED LATER------------------------------
   //This code needs to be enabled once the set methods on the
   //Clob interface are implemented. Until that time keep checking
   //for a not implemented exception being thrown.
   /*
    CallableStatement cs = prepareCall
        ("CALL SYSIBM.CLOBTRUNCATE(?,?)");
    cs.setInt(1, 1);
    cs.setLong(2, 10L);
    cs.execute();
    cs.close();

    cs  = prepareCall
        ("? = CALL SYSIBM.CLOBGETLENGTH(?)");
    cs.registerOutParameter(1, java.sql.Types.BIGINT);
    cs.setInt(2, 1);
    cs.executeUpdate();
    //compare the actual length of the test string and the returned length.
    assertEquals("Error SYSIBM.CLOBGETLENGTH returns " +
        "the wrong value for the length of the Clob", 10
        , cs.getLong(1));
    cs.close();
    */
    //----------TO BE ENABLED LATER------------------------------

    CallableStatement cs = prepareCall
        ("CALL SYSIBM.CLOBTRUNCATE(?,?)");
    cs.setInt(1, 1);
    cs.setLong(2, 10L);
    try {
        cs.execute();
    }
    catch(SQLException sqle) {
        //expected Unsupported SQLException
        //The CLOBTRUNCATE is not supported but contains
        //temporary code that shall be removed when
        //the method is enabled.
    }
    cs.close();
}