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

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

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

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

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

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

}
 

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

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

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

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

}
 

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

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

}
 

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

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

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

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

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

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

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

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

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