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

/**
 * Increments the offsets of all members which have an offset > startOffset.
 *
 * @param connection The connection to the database.
 * @param members The ids of the members whose updates should be updated.
 * @param delta The value that is added to all member offsets.
 * @param implicitDelta The delta that is added to the implicitly update member offsets.
 * @param implicitlyUpdatedMembers
 * @param module The module that contains the members.
 * @throws CouldntSaveDataException Thrown if the member offsets could not be updated.
 */
public static void updateMemberOffsets(final Connection connection,
    final List<Integer> members,
    final int delta,
    final List<Integer> implicitlyUpdatedMembers,
    final int implicitDelta,
    final INaviModule module) throws CouldntSaveDataException {
  try {
    final CallableStatement statement =
        connection.prepareCall("{ call update_member_offsets(?, ?, ?, ?, ?) }");
    try {
      statement.setInt(1, module.getConfiguration().getId());
      statement.setArray(2, connection.createArrayOf("int4", members.toArray()));
      statement.setInt(3, delta);
      statement.setArray(4, connection.createArrayOf("int4", implicitlyUpdatedMembers.toArray()));
      statement.setInt(5, implicitDelta);
      statement.execute();
    } finally {
      statement.close();
    }
  } catch (final SQLException exception) {
    throw new CouldntSaveDataException(exception);
  }
}
 

/**
 * Helper method for testing getBestRowIdentifier - calls the ODBC procedure
 * @throws SQLException
 */
private ResultSet getBestRowIdentifierODBC(String catalog, String schema,
    String table, int scope, boolean nullable) throws SQLException
{
    CallableStatement cs = prepareCall(
        "CALL SYSIBM.SQLSPECIALCOLUMNS(1, ?, ?, ?, ?, ?, " +
    "'DATATYPE=''ODBC''')");
    cs.setString(1, catalog);
    cs.setString(2, schema);
    cs.setString(3, table);
    cs.setInt(4, scope);
    cs.setBoolean(5, nullable);

    cs.execute();
    return cs.getResultSet();
}
 

/**
 * Tests the SYSIBM.BLOBGETPOSITIONFROMBYTES stored procedure.
 *
 * @throws SQLException.
 */
public void testBlobGetPositionFromBytesSP() throws Exception {
    CallableStatement cs  = prepareCall
        ("? = CALL SYSIBM.BLOBGETPOSITIONFROMBYTES(?,?,?)");
    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.setBytes(3, (new String("simple")).getBytes("US-ASCII"));
    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.BLOBGETPOSITIONFROMBYTES returns " +
        "the wrong value for the position of the Blob", 8, cs.getLong(1));
    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();
}
 

public static void testMySqlStoredProcedure(Connection connection) throws SQLException {
CallableStatement callableStatement = connection.prepareCall("{ call demoSp(?, ?, ?) }");
callableStatement.registerOutParameter(2, Types.INTEGER);
callableStatement.registerOutParameter(3, Types.INTEGER);
callableStatement.setString(1, "test");
callableStatement.setInt(2, 12);
ResultSet rs = callableStatement.executeQuery();

while (rs.next()) {
    System.out.println(rs.getString(1));
}

int out2 = callableStatement.getInt(2);
int out3 = callableStatement.getInt(3);

callableStatement.close();

System.out.println();
System.out.println("out2: " + out2);
System.out.println("out3: " + out3);

   }
 

/**
 * Helper method for testing getBestRowIdentifier - calls the ODBC procedure
 * @throws SQLException
 */
private ResultSet getBestRowIdentifierODBC(String catalog, String schema,
    String table, int scope, boolean nullable) throws SQLException
{
    CallableStatement cs = prepareCall(
        "CALL SYSIBM.SQLSPECIALCOLUMNS(1, ?, ?, ?, ?, ?, " +
    "'DATATYPE=''ODBC''')");
    cs.setString(1, catalog);
    cs.setString(2, schema);
    cs.setString(3, table);
    cs.setInt(4, scope);
    cs.setBoolean(5, nullable);

    cs.execute();
    return cs.getResultSet();
}
 

/**
 * replace with a hacked jar file, emc.class modified to 
 be an invalid class (no signing on this jar).
 * @throws MalformedURLException 
 */
public void testInvalidJar() throws SQLException, MalformedURLException
{
    replaceJar("dcl_emc2l.jar", "EMC.MAIL_APP");
    
    try {
        CallableStatement cs = prepareCall("CALL EMC.ADDCONTACT(?, ?)");
        cs.setInt(1, 999);
        cs.setString(2, "[email protected]");
        cs.executeUpdate();
        cs.close();
        fail("procedure call worked on invalid jar");
    } catch (SQLException e) {
        assertSQLState("Class load should fail due to invalid jar", "42X51", e);

    }        
}
 

/**
 * Tests the SYSIBM.BLOBRELEASELOCATOR stored procedure.
 *
 * @throws SQLException
 */
public void testBlobReleaseLocatorSP() throws SQLException {
    CallableStatement cs  = prepareCall
        ("CALL SYSIBM.BLOBRELEASELOCATOR(?)");
    cs.setInt(1, 1);
    cs.execute();
    cs.close();

    //once the locator has been released the BLOBGETLENGTH on that
    //locator value will throw an SQLException. This assures that
    //the locator has been properly released.

    cs  = prepareCall("? = CALL SYSIBM.BLOBGETLENGTH(?)");
    cs.registerOutParameter(1, java.sql.Types.BIGINT);
    cs.setInt(2, 1);
    try {
        cs.executeUpdate();
    } catch(SQLException sqle) {
        //on expected lines. The test was successful.
        return;
    }
    //The exception was not thrown. The test has failed here.
    fail("Error the locator was not released by SYSIBM.BLOBRELEASELOCATOR");
    cs.close();
}
 

private void callProcedure(CallableStatement cStmt, Connection c) throws SQLException {
    cStmt = c.prepareCall("{CALL testbug61203pr(?,?,?)}");
    cStmt.setFloat(1, 2);
    cStmt.setInt(2, 1);
    cStmt.setInt(3, 1);
    cStmt.registerOutParameter(1, Types.INTEGER);
    cStmt.execute();
    assertEquals(2f, cStmt.getInt(1), .001);
}
 

/**
 * Update meta info about an existing run
 *
 * @param runId
 * @param metaKey
 * @param metaValue
 * @param closeConnection
 * @throws DatabaseAccessException
 */
public void addRunMetainfo(
                            int runId,
                            String metaKey,
                            String metaValue,
                            boolean closeConnection ) throws DatabaseAccessException {

    final String errMsg = "Unable to add run meta info '" + metaKey + "=" + metaValue
                          + "' to run with id " + runId;

    final int indexRowsInserted = 4;

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_add_run_metainfo(?, ?, ?, ?) }");
        callableStatement.setInt(1, runId);
        callableStatement.setString(2, metaKey);
        callableStatement.setString(3, metaValue);
        callableStatement.registerOutParameter(indexRowsInserted, Types.INTEGER);

        callableStatement.execute();
        if (callableStatement.getInt(indexRowsInserted) != 1) {
            throw new DatabaseAccessException(errMsg);
        }

    } catch (Exception e) {
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        if (closeConnection) {
            DbUtils.close(connection, callableStatement);
        } else {
            DbUtils.closeStatement(callableStatement);
        }
    }
}
 

@Test
public void testIntToString() throws Exception {
    CallableStatement call = getConnection().prepareCall("{ ? = call testspg__intToString (?) }");
    call.setInt(2, 42);
    call.registerOutParameter (1, Types.VARCHAR);
    call.execute ();
    assertEquals("bob42", call.getString(1));
}
 

/**
 * Tests that <code>CallableStatement.executeUpdate()</code> fails
 * when a result set is returned from a stored procedure.
 * @exception SQLException if a database error occurs
 */
public void testExecuteUpdateWithOneDynamicResultSet_callable()
    throws SQLException
{
    CallableStatement cs =
        prepareCall("CALL RETRIEVE_DYNAMIC_RESULTS(?)");
    cs.setInt(1, 1);
    try {
        cs.executeUpdate();
        fail("executeUpdate() didn't fail.");
    } catch (SQLException sqle) {
        assertResultsFromExecuteUpdate(sqle);
    }
}
 

@Override
protected CallableStatement getCallableStatement(Connection conn)
    throws SQLException {
  CallableStatement cs = conn.prepareCall(" call " + procName + " (?) ");
  cs.setInt(1, SqlUtilityHelper.tid());
  return cs;
}
 

public void testDataAwareProcedureWithOutgoingResultSetsOnAll()
throws SQLException {

  setup();
  CallableStatement cs = prepareCall("CALL RETRIEVE_OUTGOING_RESULTS(?) ON ALL");
  int number=2;
  cs.setInt(1, number);   
  cs.execute();
  
  String[][] results=new String[2][10];
  results[0][0]="1";
  for(int i=0; i<10; i++) {
    results[1][i]=i+"String"+i;
  }  
  
  int[] numRows={0,9};
  
  int rsIndex=-1;
  do {
    ++rsIndex;
    int rowIndex=0;
    ResultSet rs = cs.getResultSet();
    ResultSetMetaData metaData = rs.getMetaData();
    int rowCount = metaData.getColumnCount();
    while (rs.next()) {
      String row="";
      for (int i = 1; i <=rowCount; ++i) {
        Object value = rs.getObject(i);
        row+=value.toString();          
      }
      if(rsIndex>1 || rowIndex>numRows[rsIndex]) {
        fail("the result is not correct!");
      }
      if(!row.equals(results[rsIndex][rowIndex])) {
        fail("the result is not correct!");
      }
      ++rowIndex;
    }
  } while (cs.getMoreResults());           
}
 

/**
 * Tests the stored procedure SYSIBM.CLOBGETLENGTH.
 *
 * @throws SQLException
 */
public void testClobGetLengthSP() throws SQLException {
    CallableStatement 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", testStrLength, cs.getLong(1));
    cs.close();
}
 

public void testExecuteOnServerGroupProcedureCall() throws Exception {
  startServerVMs(3, 0, "sg1");
  startServerVMs(1, 0, "SG2");
  //startClientVMs(1, 0, null);

  int clientPort = startNetworkServer(2, null, null);
  Connection conn = TestUtil.getNetConnection(clientPort, null, null);

  serverSQLExecute(1, "CREATE PROCEDURE SERVER_GROUP_PROC(number INT) "
      + "LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME '"
      + ProcedureTestDUnit.class.getName() + ".serverGroupProc' "
      + "DYNAMIC RESULT SETS 4");
  CallableStatement cs = conn.prepareCall(
      "CALL SERVER_GROUP_PROC(?) ON SERVER GROUPS (sg1)");
  cs.setInt(1, 3);
  cs.execute();

  int rsIndex = -1;
  do {
    ++rsIndex;
    int rowIndex = 0;
    ResultSet rs = cs.getResultSet();
    ResultSetMetaData metaData = rs.getMetaData();
    int rowCount = metaData.getColumnCount();
    while (rs.next()) {
      String row = "";
      for (int i = 1; i <= rowCount; ++i) {
        Object value = rs.getObject(i);
        row += value.toString();
        assertTrue(row, row.equalsIgnoreCase("sg1"));
      }
      getLogWriter().info(
          "testExecuteQueryWithDataAwareProcedureCall row=" + row
              + " resultset index=" + rsIndex + " rowIndex=" + rowIndex);
    }
  } while (cs.getMoreResults());
  conn.close();
}
 

public void testCallProcedureWithInAndOutParameter()
   throws SQLException  {
  
  setup();
  int number=2;
  CallableStatement cs = prepareCall("CALL PROCEDURE_INOUT_PARAMETERS(?, ?, ?)");
  cs.setInt(1, number);
  cs.registerOutParameter(2, java.sql.Types.VARCHAR);
  cs.setString(2, "INOUT_PARAMETER");
  cs.registerOutParameter(3, java.sql.Types.INTEGER);
  cs.execute();
  
  String[][] results=new String[2][1];
  results[0][0]="1";
  results[1][0]="1";
           
  int rsIndex=-1;
  do {
    ++rsIndex;
    int rowIndex=0;
    ResultSet rs = cs.getResultSet();
    ResultSetMetaData metaData = rs.getMetaData();
    int rowCount = metaData.getColumnCount();
    while (rs.next()) {
      String row="";
      for (int i = 1; i <=rowCount; ++i) {
        Object value = rs.getObject(i);
        row+=value.toString();          
      }
      if(rsIndex>1 || rowIndex>1) {
        fail("the result is not correct!");
      }
      if(!row.equals(results[rsIndex][rowIndex])) {
        fail("the result is not correct!");
      }
      ++rowIndex;
    }
  } while (cs.getMoreResults());    
  
  String outValue=cs.getString(2);
  String outParameter="INOUT_PARAMETER"+"Modified";
  if(!outValue.equals(outParameter)) {
    fail("the out parameter is supposed to "+outParameter+" but "+outValue);
  }
  
  int parameter3=cs.getInt(3);    
  if(parameter3!=number) {
    fail("the out parameter is supposed to "+number+" but "+parameter3);
  }
           
}
 

public int startLoadQueue(
                           String name,
                           int sequence,
                           String hostsList,
                           String threadingPattern,
                           int numberThreads,
                           String machine,
                           long timestamp,
                           int testcaseId,
                           boolean closeConnection ) throws DatabaseAccessException {

    if (testcaseId < 1) {
        log.getLog4jLogger()
           .warn("Load queue '" + name
                 + "' will not be registered because there is no database connection!");
        return -1;
    }

    timestamp = inUTC(timestamp);

    final String errMsg = "Unable to start load queue with name " + name;

    // create a new load queue
    final int indexRowsInserted = 9;
    final int indexLoadQueueId = 10;

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_start_loadqueue(?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }");
        callableStatement.setInt(1, testcaseId);
        callableStatement.setString(2, name);
        callableStatement.setInt(3, sequence);
        callableStatement.setString(4, hostsList);
        callableStatement.setString(5, threadingPattern);
        callableStatement.setInt(6, numberThreads);
        callableStatement.setString(7, machine);
        callableStatement.setTimestamp(8, new Timestamp(timestamp));
        callableStatement.registerOutParameter(indexRowsInserted, Types.INTEGER);
        callableStatement.registerOutParameter(indexLoadQueueId, Types.INTEGER);

        callableStatement.execute();

        if (callableStatement.getInt(indexRowsInserted) != 1) {
            throw new DatabaseAccessException(errMsg);
        } else {
            if (callableStatement.getInt(indexLoadQueueId) == 0) {
                throw new DatabaseAccessException(errMsg + " - load queue id returned was 0");
            }
        }

        // get the result
        return callableStatement.getInt(indexLoadQueueId);

    } catch (Exception e) {
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        if (closeConnection) {
            DbUtils.close(connection, callableStatement);
        } else {
            DbUtils.closeStatement(callableStatement);
        }
    }
}
 

public void testDataAwareProcedureWithInAndOutParameter() throws SQLException {

    setup();
    int number = 2;
    CallableStatement cs = prepareCall("CALL PROCEDURE_INOUT_PARAMETERS(?, ?, ?) ON TABLE EMP.PARTITIONTESTTABLE WHERE SECONDID=4 AND THIRDID='3'");
    cs.setInt(1, number);
    cs.registerOutParameter(2, java.sql.Types.VARCHAR);
    cs.setString(2, "INOUT_PARAMETER");
    cs.registerOutParameter(3, java.sql.Types.INTEGER);
    cs.execute();

    String[][] results = new String[2][1];
    results[0][0] = "1";
    results[1][0] = "1";

    int rsIndex = -1;
    do {
      ++rsIndex;
      int rowIndex = 0;
      ResultSet rs = cs.getResultSet();
      ResultSetMetaData metaData = rs.getMetaData();
      int rowCount = metaData.getColumnCount();
      while (rs.next()) {
        String row = "";
        for (int i = 1; i <= rowCount; ++i) {
          Object value = rs.getObject(i);
          row += value.toString();
        }
        if (rsIndex > 1 || rowIndex > 1) {
          fail("the result is not correct!");
        }
        if (!row.equals(results[rsIndex][rowIndex])) {
          fail("the result is not correct!");
        }
        ++rowIndex;
      }
    } while (cs.getMoreResults());

    String outValue = cs.getString(2);
    String outParameter = "INOUT_PARAMETER" + "Modified";
    if (!outValue.equals(outParameter)) {
      fail("the out parameter is supposed to " + outParameter + " but "
          + outValue);
    }

    int parameter3 = cs.getInt(3);
    if (parameter3 != number) {
      fail("the out parameter is supposed to " + number + " but " + parameter3);
    }

  }
 

/**
 * Tests fix for BUG#61150 - First call to SP
 * fails with "No Database Selected"
 * The workaround introduced in DatabaseMetaData.getCallStmtParameterTypes
 * to fix the bug in server where SHOW CREATE PROCEDURE was not respecting
 * lower-case table names is misbehaving when connection is not attached to
 * database and on non-casesensitive OS.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug61150() throws Exception {
    NonRegisteringDriver driver = new NonRegisteringDriver();
    Properties oldProps = driver.parseURL(BaseTestCase.dbUrl, null);

    String host = driver.host(oldProps);
    int port = driver.port(oldProps);
    StringBuilder newUrlToTestNoDB = new StringBuilder("jdbc:mysql://");
    if (host != null) {
        newUrlToTestNoDB.append(host);
    }
    newUrlToTestNoDB.append(":").append(port).append("/");

    Statement savedSt = this.stmt;

    Properties props = getHostFreePropertiesFromTestsuiteUrl();
    props.remove(NonRegisteringDriver.DBNAME_PROPERTY_KEY);
    Connection conn1 = DriverManager.getConnection(newUrlToTestNoDB.toString(), props);

    this.stmt = conn1.createStatement();
    createDatabase("TST1");
    createProcedure("TST1.PROC", "(x int, out y int)\nbegin\ndeclare z int;\nset z = x+1, y = z;\nend\n");

    CallableStatement cStmt = null;
    cStmt = conn1.prepareCall("{call `TST1`.`PROC`(?, ?)}");
    cStmt.setInt(1, 5);
    cStmt.registerOutParameter(2, Types.INTEGER);

    cStmt.execute();
    assertEquals(6, cStmt.getInt(2));
    cStmt.clearParameters();
    cStmt.close();

    conn1.setCatalog("TST1");
    cStmt = null;
    cStmt = conn1.prepareCall("{call TST1.PROC(?, ?)}");
    cStmt.setInt(1, 5);
    cStmt.registerOutParameter(2, Types.INTEGER);

    cStmt.execute();
    assertEquals(6, cStmt.getInt(2));
    cStmt.clearParameters();
    cStmt.close();

    conn1.setCatalog("mysql");
    cStmt = null;
    cStmt = conn1.prepareCall("{call `TST1`.`PROC`(?, ?)}");
    cStmt.setInt(1, 5);
    cStmt.registerOutParameter(2, Types.INTEGER);

    cStmt.execute();
    assertEquals(6, cStmt.getInt(2));
    cStmt.clearParameters();
    cStmt.close();

    this.stmt = savedSt;
}