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

The following examples show how to use java.sql.CallableStatement#setInt() . 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
Source Project: binnavi   File: PostgreSQLTypeFunctions.java    License: Apache License 2.0 7 votes vote down vote up
/**
 * 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);
  }
}
 
Example 2
/**
 * 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();
}
 
Example 3
/**
 * 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();
}
 
Example 4
/**
 * 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);

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

   }
 
Example 7
/**
 * 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 8
/**
 * 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();
}
 
Example 9
/**
 * 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);
    }
}
 
Example 10
@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));
}
 
Example 11
/**
 * 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);
        }
    }
}
 
Example 12
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);
}
 
Example 13
@Override
protected CallableStatement getCallableStatement(Connection conn)
    throws SQLException {
  CallableStatement cs = conn.prepareCall(" call " + procName + " (?) ");
  cs.setInt(1, SqlUtilityHelper.tid());
  return cs;
}
 
Example 14
Source Project: gemfirexd-oss   File: ProcedureTest.java    License: Apache License 2.0 5 votes vote down vote up
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());           
}
 
Example 15
/**
 * 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();
}
 
Example 16
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();
}
 
Example 17
Source Project: gemfirexd-oss   File: ProcedureTest.java    License: Apache License 2.0 4 votes vote down vote up
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);
  }
           
}
 
Example 18
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);
        }
    }
}
 
Example 19
Source Project: gemfirexd-oss   File: ProcedureTest.java    License: Apache License 2.0 4 votes vote down vote up
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);
    }

  }
 
Example 20
Source Project: r-course   File: MetaDataRegressionTest.java    License: MIT License 4 votes vote down vote up
/**
 * 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;
}