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

/**
 * 写MartifHeader节点内容
 * @param hContent
 *            整个节点的内容
 * @param hIdAttr
 *            MartifHeader节点的ID属性;
 * @return
 * @throws SQLException
 */
public int insertBMartifHeader(String hContent, String hIdAttr) throws SQLException {
	CallableStatement stmt = null;
	try {
		String sql = dbConfig.getOperateDbSQL("insert-bmartifheader");
		stmt = conn.prepareCall(sql);
		stmt.setString(1, hIdAttr);
		stmt.setString(2, hContent);
		stmt.registerOutParameter(3, Types.INTEGER);
		stmt.execute();
		return stmt.getInt(3);
	} finally {
		if (stmt != null) {
			stmt.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);

   }
 

/**
 * Update the static information about an existing run
 *
 * @param runId
 * @param runName
 * @param osName
 * @param productName
 * @param versionName
 * @param buildName
 * @param userNote
 * @param hostName
 * @param closeConnection
 * @throws DatabaseAccessException
 */
public void updateRun( int runId, String runName, String osName, String productName, String versionName,
                       String buildName, String userNote, String hostName, boolean closeConnection )
                                                                                                     throws DatabaseAccessException {

    final String errMsg = "Unable to update run with name '" + runName + "' and id " + runId;

    // then start the run
    final int indexRowsUpdate = 9;

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_update_run(?, ?, ?, ?, ?, ?, ?, ?, ?) }");
        callableStatement.setString(1, runId + "");
        callableStatement.setString(2, productName);
        callableStatement.setString(3, versionName);
        callableStatement.setString(4, buildName);
        callableStatement.setString(5, runName);
        callableStatement.setString(6, osName);
        callableStatement.setString(7, userNote);
        callableStatement.setString(8, hostName);
        callableStatement.registerOutParameter(indexRowsUpdate, Types.INTEGER);

        callableStatement.execute();
        if (callableStatement.getInt(indexRowsUpdate) != 1) {
            throw new DatabaseAccessException(errMsg);
        }
    } catch (Exception e) {
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        if (closeConnection) {
            DbUtils.close(connection, callableStatement);
        } else {
            DbUtils.closeStatement(callableStatement);
        }
    }
}
 

/**
 * Creates the native flow graph views of a module.
 * 
 * @param connection Connection to the database.
 * @param moduleId ID of the BinNavi module where the views are created.
 * 
 * @return The ID of the first created view.
 * 
 * @throws SQLException Thrown if creating the flow graph views failed.
 */
public static int createNativeFlowgraphViews(final CConnection connection, final int moduleId)
    throws SQLException {
  Preconditions.checkNotNull(connection, "IE01816: connection argument can not be null");
  final String query = "{ ? = call create_native_flowgraph_views(?) }";
  final CallableStatement call = connection.getConnection().prepareCall(query);
  call.registerOutParameter(1, Types.INTEGER);
  call.setInt(2, moduleId);
  call.execute();
  return call.getInt(1);
}
 

/**
 * Appends a local comment to a text node.
 *
 * @param provider the provider to access the database.
 * @param textNode The text node where to add the comment.
 * @param commentText The comment text of the comment.
 * @param userId the user id of the currently active user.
 *
 * @return The id of the comment generated by the database.
 *
 * @throws CouldntSaveDataException if the comment could not be saved to the database.
 */
public static Integer appendTextNodeComment(final SQLProvider provider,
    final INaviTextNode textNode, final String commentText, final Integer userId)
    throws CouldntSaveDataException {

  Preconditions.checkNotNull(provider, "IE02461: provider argument can not be null");
  Preconditions.checkNotNull(textNode, "IE02462: textNode argument can not be null");
  Preconditions.checkNotNull(commentText, "IE02463: commentText argument can not be null");
  Preconditions.checkNotNull(userId, "IE02464: userId argument can not be null");

  final String function = " { ? = call append_text_node_comment(?, ?, ?) } ";

  try {
    final CallableStatement appendCommentFunction =
        provider.getConnection().getConnection().prepareCall(function);

    try {
      appendCommentFunction.registerOutParameter(1, Types.INTEGER);
      appendCommentFunction.setInt(2, textNode.getId());
      appendCommentFunction.setInt(3, userId);
      appendCommentFunction.setString(4, commentText);

      appendCommentFunction.execute();
      final Integer commentId = appendCommentFunction.getInt(1);
      if (appendCommentFunction.wasNull()) {
        throw new CouldntSaveDataException("Error: Got an comment id of null from the database");
      }
      return commentId;
    } finally {
      appendCommentFunction.close();
    }
  } catch (final SQLException exception) {
    throw new CouldntSaveDataException(exception);
  }
}
 

/**
 * This function deletes a global instruction comment associated with the
 * given instruction from the database.
 *
 * @param provider The provider used to access the database.
 * @param instruction The instruction to which the comment is associated.
 * @param commentId The comment id of the comment to be deleted.
 * @param userId The id of the currently active user.
 *
 * @throws CouldntDeleteException if the comment could not be deleted from the
 *         database.
 */
public static void deleteGlobalInstructionComment(final SQLProvider provider,
    final INaviInstruction instruction, final Integer commentId, final Integer userId)
        throws CouldntDeleteException {

  Preconditions.checkNotNull(provider, "IE02428: provider argument can not be null");
  Preconditions.checkNotNull(instruction, "IE02429: instruction argument can not be null");
  Preconditions.checkNotNull(commentId, "IE02430: comment argument can not be null");
  Preconditions.checkNotNull(userId, "IE02431: userId argument can not be null");

  final String function = " { ? = call delete_global_instruction_comment(?, ?, ?, ?) } ";

  try {

    final CallableStatement deleteCommentStatement =
        provider.getConnection().getConnection().prepareCall(function);

    try {
      deleteCommentStatement.registerOutParameter(1, Types.INTEGER);
      deleteCommentStatement.setInt(2, instruction.getModule().getConfiguration().getId());
      deleteCommentStatement.setObject(3, instruction.getAddress().toBigInteger(), Types.BIGINT);
      deleteCommentStatement.setInt(4, commentId);
      deleteCommentStatement.setInt(5, userId);

      deleteCommentStatement.execute();

      deleteCommentStatement.getInt(1);
      if (deleteCommentStatement.wasNull()) {
        throw new IllegalArgumentException(
            "Error: The comment id returned from the database was null.");
      }

    } finally {
      deleteCommentStatement.close();
    }

  } catch (final SQLException exception) {
    throw new CouldntDeleteException(exception);
  }
}
 

public Object getResult(CallableStatement cs, int columnIndex) throws SQLException {

            int i = cs.getInt(columnIndex);
            if (cs.wasNull()) {
                return null;
            }
            else {
                return new Integer(i);
            }
        }
 

@Override
public E getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
	int i = cs.getInt(columnIndex);
	if (cs.wasNull()) {
		return null;
	} else {
		try {
			return enums[i];
		} catch (Exception ex) {
			throw new IllegalArgumentException("Cannot convert " + i + " to " + type.getSimpleName() + " by ordinal value.", ex);
		}
	}
}
 

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

public void testDataAwareProcedureWithoutResultSets()
throws SQLException {
  
  setup();
  CallableStatement cs = prepareCall("CALL PROCEDURE_WITHOUT_RESULTSET(?, ?) ON TABLE EMP.PARTITIONTESTTABLE WHERE SECONDID=4 AND THIRDID='3'");
  cs.registerOutParameter(2, java.sql.Types.VARCHAR, 20);
  int number=2;
  String name="INOUTPARAMETER";
  cs.setInt(1, number);
  cs.setString(2, name);
 
  
  cs.execute();
  
  ResultSet rs=cs.getResultSet();
  if(rs!=null || cs.getMoreResults()) {
    fail("no dynamic result set for the procedure!");
  }
  
  ParameterMetaData pmd=cs.getParameterMetaData();  
  int numParameters=pmd.getParameterCount();
  assertTrue(" the number of parameter is 2", numParameters==2);
  try {
    cs.getInt(1);
    fail("the in parameteter cannot be read!");
  } catch (Exception e) {
    
  }
  Object parameter2=cs.getObject(2);    
  assertTrue("the second inout parameter is "+name+number, parameter2.equals(name+number));
    
  

}
 

public void testDataAwareProcedureWithoutResultSetsUsingGlobalIndex()
throws SQLException {
  
  setup();
  CallableStatement cs = prepareCall("CALL PROCEDURE_WITHOUT_RESULTSET(?, ?) ON TABLE EMP.PARTITIONTESTTABLE1 WHERE SECONDID=4 AND THIRDID='3'");
  cs.registerOutParameter(2, java.sql.Types.VARCHAR, 20);
  int number=2;
  String name="INOUTPARAMETER";
  cs.setInt(1, number);
  cs.setString(2, name);
 
  
  cs.execute();
  
  ResultSet rs=cs.getResultSet();
  if(rs!=null || cs.getMoreResults()) {
    fail("no dynamic result set for the procedure!");
  }
  
  ParameterMetaData pmd=cs.getParameterMetaData();  
  int numParameters=pmd.getParameterCount();
  assertTrue(" the number of parameter is 2", numParameters==2);
  try {
    cs.getInt(1);
    fail("the in parameteter cannot be read!");
  } catch (Exception e) {
    
  }
  Object parameter2=cs.getObject(2);    
  assertTrue("the second inout parameter is "+name+number, parameter2.equals(name+number));
    
  

}
 

/**
 * Insert a new run in the database
 *
 * @param runName
 *            name of the run
 * @param osName
 *            name of the OS
 * @param productName
 *            name of the product
 * @param versionName
 *            version of the product
 * @param buildName
 *            build version
 * @param timestamp
 * @param hostName
 *            name/IP of the machine , from which the run was started
 * @return
 */
public int startRun(
                     String runName,
                     String osName,
                     String productName,
                     String versionName,
                     String buildName,
                     long timestamp,
                     String hostName,
                     boolean closeConnection ) throws DatabaseAccessException {

    timestamp = inUTC(timestamp);

    // then start the run
    final int indexRowsInserted = 8;
    final int indexRunId = 9;

    String errMsg = "Unable to insert run with name " + runName;

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_start_run(?, ?, ?, ?, ?, ?, ?, ? ,?) }");
        callableStatement.setString(1, productName);
        callableStatement.setString(2, versionName);
        callableStatement.setString(3, buildName);
        callableStatement.setString(4, runName);
        callableStatement.setString(5, osName);
        callableStatement.setTimestamp(6, new Timestamp(timestamp));
        callableStatement.setString(7, hostName);
        callableStatement.registerOutParameter(indexRowsInserted, Types.INTEGER);
        callableStatement.registerOutParameter(indexRunId, Types.INTEGER);

        callableStatement.execute();
        if (callableStatement.getInt(indexRowsInserted) == 1) {

            // check if the run ID is correct
            if (callableStatement.getInt(indexRunId) == 0) {
                throw new DatabaseAccessException(errMsg
                                                  + " - run ID returned was 0");
            }
        } else {
            throw new DatabaseAccessException(errMsg);
        }

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

    } catch (Exception e) {
        String procedureName = "sp_start_run";
        List<Object> argValues = new ArrayList<Object>();
        argValues.add(procedureName);
        argValues.add(versionName);
        argValues.add(buildName);
        argValues.add(runName);
        argValues.add(osName);
        argValues.add(timestamp);
        argValues.add(hostName);

        errMsg += " using the following statement: "
                  + constructStoredProcedureArgumentsMap(procedureName, argValues);
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        if (closeConnection) {
            DbUtils.close(connection, callableStatement);
        } else {
            DbUtils.closeStatement(callableStatement);
        }
    }
}
 

public static Object getValue(final CallableStatement cs, final int index, Class<?> requiredType) throws SQLException {
    Object value = null;
    boolean wasNullCheck = false;
    if (requiredType == null) {
        return cs.getObject(index);
    }
    requiredType = primitiveToWrapper(requiredType);
    // Explicitly extract typed value, as far as possible.
    if (String.class.equals(requiredType)) {
        value = cs.getString(index);
    } else if (Integer.class.equals(requiredType)) {
        value = cs.getInt(index);
        wasNullCheck = true;
    } else if (Double.class.equals(requiredType)) {
        value = cs.getDouble(index);
        wasNullCheck = true;
    } else if (Boolean.class.equals(requiredType)) {
        value = cs.getBoolean(index) ? Boolean.TRUE : Boolean.FALSE;
        wasNullCheck = true;
    } else if (java.sql.Date.class.equals(requiredType)) {
        value = cs.getDate(index);
    } else if (java.sql.Time.class.equals(requiredType)) {
        value = cs.getTime(index);
    } else if (java.sql.Timestamp.class.equals(requiredType)) {
        value = cs.getTimestamp(index);
    } else if (java.util.Date.class.equals(requiredType)) {
        value = new java.util.Date(cs.getTimestamp(index).getTime());
    } else if (Byte.class.equals(requiredType)) {
        value = cs.getByte(index);
        wasNullCheck = true;
    } else if (Short.class.equals(requiredType)) {
        value = cs.getShort(index);
        wasNullCheck = true;
    } else if (Long.class.equals(requiredType)) {
        value = cs.getLong(index);
        wasNullCheck = true;
    } else if (Float.class.equals(requiredType)) {
        value = cs.getFloat(index);
        wasNullCheck = true;
    } else if (Number.class.equals(requiredType)) {
        value = cs.getDouble(index);
        wasNullCheck = true;
    } else if (byte[].class.equals(requiredType)) {
        value = cs.getBytes(index);
    } else if (java.math.BigDecimal.class.equals(requiredType)) {
        value = cs.getBigDecimal(index);
    } else if (java.sql.Blob.class.equals(requiredType)) {
        value = cs.getBlob(index);
    } else if (java.sql.Clob.class.equals(requiredType)) {
        value = cs.getClob(index);
    } else if (java.net.URL.class.equals(requiredType)) {
        value = cs.getURL(index);
    } else {
        // Some unknown type desired -> rely on getObject.
        value = cs.getObject(index);
    }
    // Perform was-null check if demanded (for results that the JDBC driver returns as primitives).
    if (wasNullCheck && value != null && cs.wasNull()) {
        value = null;
    }
    return value;
}
 

@Override
public ArticleTypeEnum getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
    int code = cs.getInt(columnIndex);
    return ArticleTypeEnum.find(code);
}
 

/**
 * This function deletes a global edge comment from the database.
 *
 * @param provider The provider to access the database.
 * @param edge The edge to which the comment is associated.
 * @param commentId The comment id of the comment to be deleted.
 * @param userId The user id of the currently active user.
 *
 * @throws CouldntDeleteException if the comment could not be deleted from the database.
 */
public static void deleteGlobalEdgeComment(final AbstractSQLProvider provider,
    final INaviEdge edge, final Integer commentId, final Integer userId)
    throws CouldntDeleteException {

  Preconditions.checkNotNull(provider, "IE00505: provider argument can not be null");
  Preconditions.checkNotNull(edge, "IE00506: codeNode argument can not be null");
  Preconditions.checkNotNull(commentId, "IE00507: comment argument can not be null");
  Preconditions.checkNotNull(userId, "IE00508: userId argument can not be null");

  final String function = " { ? = call delete_global_edge_comment(?, ?, ?, ?, ?, ?) } ";

  try {
    final CallableStatement deleteCommentFunction =
        provider.getConnection().getConnection().prepareCall(function);

    try {
      deleteCommentFunction.registerOutParameter(1, Types.INTEGER);
      deleteCommentFunction.setInt(2, getModuleId(edge.getSource()));
      deleteCommentFunction.setInt(3, getModuleId(edge.getTarget()));
      deleteCommentFunction.setObject(
          4, ((INaviCodeNode) edge.getSource()).getAddress().toBigInteger(), Types.BIGINT);
      deleteCommentFunction.setObject(
          5, ((INaviCodeNode) edge.getTarget()).getAddress().toBigInteger(), Types.BIGINT);
      deleteCommentFunction.setInt(6, commentId);
      deleteCommentFunction.setInt(7, userId);

      deleteCommentFunction.execute();

      deleteCommentFunction.getInt(1);
      if (deleteCommentFunction.wasNull()) {
        throw new IllegalArgumentException(
            "Error: the comment id returned from the database was null");
      }
    } finally {
      deleteCommentFunction.close();
    }

  } catch (SQLException | MaybeNullException exception) {
    throw new CouldntDeleteException(exception);
  }
}
 

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

public CheckpointInfo startCheckpoint( String name, long startTimestamp, String transferRateUnit, int loadQueueId,
                                       boolean closeConnection ) throws DatabaseAccessException {

    final String errMsg = "Unable to start checkpoint '" + name + "' in load queue " + loadQueueId;

    startTimestamp = inUTC(startTimestamp);

    final int indexCheckpointSummaryId = 5;
    final int indexCheckpointId = 6;

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_start_checkpoint(?, ?, ?, ?, ?, ?) }");
        callableStatement.setInt(1, loadQueueId);
        callableStatement.setString(2, name);
        callableStatement.setInt(3, checkpointLogLevel.toInt());
        callableStatement.setString(4, transferRateUnit);
        callableStatement.registerOutParameter(indexCheckpointSummaryId, Types.INTEGER);
        callableStatement.registerOutParameter(indexCheckpointId, Types.BIGINT);

        callableStatement.execute();

        // we always update the checkpoint summary table
        if (callableStatement.getInt(indexCheckpointSummaryId) == 0) {
            throw new DatabaseAccessException(errMsg + " - checkpoint summary ID returned was 0");
        }

        // we update the checkpoint table only in FULL mode
        if (checkpointLogLevel == CheckpointLogLevel.FULL && callableStatement.getInt(indexCheckpointId) == 0) {
            throw new DatabaseAccessException(errMsg + " - checkpoint ID returned was 0");
        }

        int checkpointSummaryId = callableStatement.getInt(indexCheckpointSummaryId);
        long checkpointId = callableStatement.getLong(indexCheckpointId);

        return new CheckpointInfo(name, checkpointSummaryId, checkpointId, startTimestamp);

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

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

public void testOutParamsNoBodies() throws Exception {
    CallableStatement storedProc = null;

    Properties props = new Properties();
    props.setProperty(PropertyKey.noAccessToProcedureBodies.getKeyName(), "true");

    Connection spConn = getConnectionWithProps(props);

    createProcedure("testOutParam", "(x int, out y int)\nbegin\ndeclare z int;\nset z = x+1, y = z;\nend\n");

    storedProc = spConn.prepareCall("{call testOutParam(?, ?)}");

    storedProc.setInt(1, 5);
    storedProc.registerOutParameter(2, Types.INTEGER);

    storedProc.execute();

    int indexedOutParamToTest = storedProc.getInt(2);

    assertTrue("Output value not returned correctly", indexedOutParamToTest == 6);

    storedProc.clearParameters();
    storedProc.setInt(1, 32);
    storedProc.registerOutParameter(2, Types.INTEGER);

    storedProc.execute();

    indexedOutParamToTest = storedProc.getInt(2);

    assertTrue("Output value not returned correctly", indexedOutParamToTest == 33);
}
 

public void testOutParamsNoBodies() throws Exception {
    if (versionMeetsMinimum(5, 0)) {
        CallableStatement storedProc = null;

        Properties props = new Properties();
        props.setProperty("noAccessToProcedureBodies", "true");

        Connection spConn = getConnectionWithProps(props);

        createProcedure("testOutParam", "(x int, out y int)\nbegin\ndeclare z int;\nset z = x+1, y = z;\nend\n");

        storedProc = spConn.prepareCall("{call testOutParam(?, ?)}");

        storedProc.setInt(1, 5);
        storedProc.registerOutParameter(2, Types.INTEGER);

        storedProc.execute();

        int indexedOutParamToTest = storedProc.getInt(2);

        assertTrue("Output value not returned correctly", indexedOutParamToTest == 6);

        storedProc.clearParameters();
        storedProc.setInt(1, 32);
        storedProc.registerOutParameter(2, Types.INTEGER);

        storedProc.execute();

        indexedOutParamToTest = storedProc.getInt(2);

        assertTrue("Output value not returned correctly", indexedOutParamToTest == 33);
    }
}