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

The following examples show how to use java.sql.CallableStatement#setObject() . 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
/**
 * Helper method for *SetObject* tests.
 * Insert data into the given PreparedStatement, or any of its subclasses, with the following structure:
 * 1 - `id` INT
 * 2 - `ot1` VARCHAR
 * 3 - `ot2` BLOB
 * 4 - `odt1` VARCHAR
 * 5 - `odt2` BLOB
 * 
 * @param prepStmt
 * @return the row count of inserted records.
 * @throws Exception
 */
private int insertTestDataOffsetDTTypes(PreparedStatement prepStmt) throws Exception {
    prepStmt.setInt(1, 1);
    prepStmt.setObject(2, this.testOffsetTime, JDBCType.VARCHAR);
    prepStmt.setObject(3, this.testOffsetTime);
    prepStmt.setObject(4, this.testOffsetDateTime, JDBCType.VARCHAR);
    prepStmt.setObject(5, this.testOffsetDateTime);
    assertEquals(1, prepStmt.executeUpdate());

    if (prepStmt instanceof CallableStatement) {
        CallableStatement cstmt = (CallableStatement) prepStmt;

        cstmt.setInt("id", 2);
        cstmt.setObject("ot1", this.testOffsetTime, JDBCType.VARCHAR);
        cstmt.setObject("ot2", this.testOffsetTime);
        cstmt.setObject("odt1", this.testOffsetDateTime, JDBCType.VARCHAR);
        cstmt.setObject("odt2", this.testOffsetDateTime);
        assertEquals(1, cstmt.executeUpdate());

        return 2;
    }

    return 1;
}
 
Example 2
/**
 * Helper method for *SetObject* tests.
 * Insert data into the given PreparedStatement, or any of its subclasses, with the following structure:
 * 1 - `id` INT
 * 2 - `ot1` VARCHAR
 * 3 - `ot2` BLOB
 * 4 - `odt1` VARCHAR
 * 5 - `odt2` BLOB
 * 
 * @param pstmt
 * @return the row count of inserted records.
 * @throws Exception
 */
private int insertTestDataOffsetDTTypes(PreparedStatement pstmt) throws Exception {
    pstmt.setInt(1, 1);
    pstmt.setObject(2, testOffsetTime, JDBCType.VARCHAR);
    pstmt.setObject(3, testOffsetTime);
    pstmt.setObject(4, testOffsetDateTime, JDBCType.VARCHAR);
    pstmt.setObject(5, testOffsetDateTime);
    assertEquals(1, pstmt.executeUpdate());

    if (pstmt instanceof CallableStatement) {
        CallableStatement cstmt = (CallableStatement) pstmt;

        cstmt.setInt("id", 2);
        cstmt.setObject("ot1", testOffsetTime, JDBCType.VARCHAR);
        cstmt.setObject("ot2", testOffsetTime);
        cstmt.setObject("odt1", testOffsetDateTime, JDBCType.VARCHAR);
        cstmt.setObject("odt2", testOffsetDateTime);
        assertEquals(1, cstmt.executeUpdate());

        return 2;
    }

    return 1;
}
 
Example 3
Source Project: gemfirexd-oss   File: UDTTest.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * <p>
 * Verify that you can use UDTs as output parameters in database procedures.
 * </p>
 */
public void test_17_outputParameters() throws Exception
{
    Connection conn = getConnection();

    goodStatement( conn, "create type intArray_17 external name 'org.apache.derbyTesting.functionTests.tests.lang.IntArray' language java\n" );
    goodStatement
        ( conn,
          "create procedure changeIntArray_17\n" +
          "( in newSize int, inout oldIntArray intArray_17 )\n" +
          "language java parameter style java no sql\n" +
          "external name 'org.apache.derbyTesting.functionTests.tests.lang.UDTTest.changeIntArray'\n" );
    
    CallableStatement cs = chattyPrepareCall( conn, "call changeIntArray_17( ?, ? )" );
    cs.registerOutParameter( 2, java.sql.Types.JAVA_OBJECT );
    cs.setInt( 1, 2 );
    cs.setObject( 2,  new IntArray( new int[ 5 ] ) );
    cs.execute();
    Object obj = cs.getObject( 2 );
    cs.close();

    assertEquals( "[ 0, 0 ]", obj.toString() );
}
 
Example 4
/**
 * <p>
 * Verify that you can use UDTs as output parameters in database procedures.
 * </p>
 */
public void test_17_outputParameters() throws Exception
{
    Connection conn = getConnection();

    goodStatement( conn, "create type intArray_17 external name 'com.splicemachine.dbTesting.functionTests.tests.lang.IntArray' language java\n" );
    goodStatement
        ( conn,
          "create procedure changeIntArray_17\n" +
          "( in newSize int, inout oldIntArray intArray_17 )\n" +
          "language java parameter style java no sql\n" +
          "external name 'com.splicemachine.dbTesting.functionTests.tests.lang.UDTTest.changeIntArray'\n" );
    
    CallableStatement cs = chattyPrepareCall( conn, "call changeIntArray_17( ?, ? )" );
    cs.registerOutParameter( 2, java.sql.Types.JAVA_OBJECT );
    cs.setInt( 1, 2 );
    cs.setObject( 2,  new IntArray( new int[ 5 ] ) );
    cs.execute();
    Object obj = cs.getObject( 2 );
    cs.close();

    assertEquals( "[ 0, 0 ]", obj.toString() );
}
 
Example 5
/**
 * This function deletes a local instruction comment associated with the given
 * instruction in the given code node from the database.
 *
 * @param provider The provider to access the database.
 * @param codeNode The code node where the instruction is located.
 * @param instruction The instruction where the comment is deleted.
 * @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 deleteLocalInstructionComment(final SQLProvider provider,
    final INaviCodeNode codeNode, final INaviInstruction instruction, final Integer commentId,
    final Integer userId) throws CouldntDeleteException {

  Preconditions.checkNotNull(codeNode, "IE02432: codeNode argument can not be null");
  Preconditions.checkNotNull(provider, "IE02433: provider argument can not be null");
  Preconditions.checkNotNull(instruction, "IE02434: instruction argument can not be null");
  Preconditions.checkNotNull(commentId, "IE02435: comment argument can not be null");
  Preconditions.checkNotNull(userId, "IE02436: userId argument can not be null");

  final String function = " { ? = call delete_local_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.setInt(3, codeNode.getId());
      deleteCommentStatement.setObject(4, instruction.getAddress().toBigInteger(), Types.BIGINT);
      deleteCommentStatement.setInt(5, commentId);
      deleteCommentStatement.setInt(6, 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);
  }
}
 
Example 6
Source Project: orcas   File: WrapperExecuteStatement.java    License: Apache License 2.0 5 votes vote down vote up
protected void useCallableStatement( CallableStatement pCallableStatement ) throws SQLException
{
  if( _parameters != null )
  {
    for( int i = 0; i < _parameters.size(); i++ )
    {
      pCallableStatement.setObject( i + 1, _parameters.get( i ) );
    }
  }

  pCallableStatement.executeUpdate();
}
 
Example 7
/**
        * test ParameterMetaData for Java procedures with CHAR parameters
 *
 * @exception SQLException if error occurs
        */
 public void testParameterMetadataWithCHARParameters () throws SQLException {

	Statement stmt = createStatement();
      		stmt.execute("CREATE PROCEDURE PMDC(IN pmdI_1 CHAR(10), IN pmdI_2 VARCHAR(25), INOUT pmdI_3 CHAR(19), OUT pmdI_4 VARCHAR(32)) language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.jdbcapi.ParameterMetaDataJdbc30Test.dummyString'");
     		CallableStatement cs = prepareCall("CALL PMDC(?, ?, ?, ?)");
	// parameters 1 and 2 are input only	
	cs.setString(1, "TEST0");
	cs.setString(2, "TEST1");
	// parameter 3 is input and output
               Object x = new String("TEST");
               cs.setObject(3,x, Types.CHAR);
               cs.registerOutParameter(3,Types.CHAR);
               //parameter 4 is output only
               cs.registerOutParameter(4,Types.CHAR);
		//verify the meta data for the parameters
              	ParameterMetaData paramMetaData = cs.getParameterMetaData();
	assertEquals("Unexpected parameter count", 4, paramMetaData.getParameterCount());

	//expected values to be stored in a 2dim. array
               String parameterMetaDataArray0 [][] = {
               //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
               {"PARAMETER_NULLABLE", "false", "10", "0", "1", "CHAR", "java.lang.String", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "false", "25", "0", "12", "VARCHAR", "java.lang.String", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "false", "19", "0", "1", "CHAR", "java.lang.String", "PARAMETER_MODE_IN_OUT"},
               {"PARAMETER_NULLABLE", "false", "32", "0", "12", "VARCHAR", "java.lang.String", "PARAMETER_MODE_OUT"}};

	testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);

      		cs.close();
      		stmt.execute("DROP PROCEDURE PMDC");
           stmt.close();
}
 
Example 8
Source Project: gemfirexd-oss   File: CallableTest.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Calls a SQL procedure that updates a long varbinary column.
 * Uses DriverManager, so this test requires JDBC 2 DriverManager support.
 * @throws SQLException 
 */
public void xtestUpdateLongBinaryProc() throws SQLException
{
    // Insert a row with an initial value that will be updated later.
    Statement stmt = createStatement();
    stmt.executeUpdate(
        "INSERT INTO LONGVARBINARY_TABLE VALUES(X'010305')");

    // Build up a byte array that will replace the initial value.
    int bytearrsize = 50;
    byte[] bytearr=new byte[bytearrsize];
    String sbyteval=null;
    for (int count=0;count<bytearrsize;count++)
    {
        sbyteval=Integer.toString(count%255);
        bytearr[count]=Byte.parseByte(sbyteval);
    }

    // Update the value in the database.
    CallableStatement cstmt = prepareCall(
        "CALL UPDATE_LONGVARBINARY_PROC(?)");
    cstmt.setObject(1,bytearr,java.sql.Types.LONGVARBINARY);
    cstmt.executeUpdate();
    
    // Retrieve the updated value and verify it's correct.
    ResultSet rs = stmt.executeQuery(
        "SELECT LVBC FROM LONGVARBINARY_TABLE");
    assertNotNull("SELECT from LONGVARBINARY_TABLE", rs);

    while (rs.next())
    {
        byte[] retvalue = (byte[]) rs.getObject(1);
        assertTrue(Arrays.equals(bytearr, retvalue));
    }
}
 
Example 9
/**
        * test ParameterMetaData for Java procedures with INTEGER parameters 
 * 
 * @exception SQLException if error occurs
        */
public void testParameterMetadataWithINTParameters () throws SQLException {

	Statement stmt = createStatement();
      		stmt.execute("CREATE PROCEDURE PMDI(IN pmdI_1 INTEGER, IN pmdI_2 INTEGER, INOUT pmdI_3 INTEGER, OUT pmdI_4 INTEGER) language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.jdbcapi.ParameterMetaDataJdbc30Test.dummyint'");
     		CallableStatement cs = prepareCall("CALL PMDI(?, ?, ?, ?)");

	// parameters 1 and 2 are input only
              	cs.setInt(1,1);
       	cs.setInt(2,1);
	// parameter 3 is input and output
               Object x = new Integer(1);
               cs.setObject(3,x, Types.INTEGER);
               cs.registerOutParameter(3,Types.INTEGER);
              	//parameter 4 is output only
              	cs.registerOutParameter(4,Types.INTEGER);

              	//verify the meta data for the parameters
              	ParameterMetaData paramMetaData = cs.getParameterMetaData();
              	assertEquals("Unexpected parameter count", 4, paramMetaData.getParameterCount());

	//expected values to be stored in a 2dim. array
               String parameterMetaDataArray0 [][] = {
               //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
               {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN_OUT"},
               {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_OUT"}};

               testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);
		
      		cs.close();
      		stmt.execute("DROP PROCEDURE PMDI");
           stmt.close();
}
 
Example 10
/**
        * test ParameterMetaData for Java procedures with CHAR parameters
 *
 * @exception SQLException if error occurs
        */
 public void testParameterMetadataWithCHARParameters () throws SQLException {

	Statement stmt = createStatement();
      		stmt.execute("CREATE PROCEDURE PMDC(IN pmdI_1 CHAR(10), IN pmdI_2 VARCHAR(25), INOUT pmdI_3 CHAR(19), OUT pmdI_4 VARCHAR(32)) language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.jdbcapi.ParameterMetaDataJdbc30Test.dummyString'");
     		CallableStatement cs = prepareCall("CALL PMDC(?, ?, ?, ?)");
	// parameters 1 and 2 are input only	
	cs.setString(1, "TEST0");
	cs.setString(2, "TEST1");
	// parameter 3 is input and output
               Object x = new String("TEST");
               cs.setObject(3,x, Types.CHAR);
               cs.registerOutParameter(3,Types.CHAR);
               //parameter 4 is output only
               cs.registerOutParameter(4,Types.CHAR);
		//verify the meta data for the parameters
              	ParameterMetaData paramMetaData = cs.getParameterMetaData();
	assertEquals("Unexpected parameter count", 4, paramMetaData.getParameterCount());

	//expected values to be stored in a 2dim. array
               String parameterMetaDataArray0 [][] = {
               //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
               {"PARAMETER_NULLABLE", "false", "10", "0", "1", "CHAR", "java.lang.String", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "false", "25", "0", "12", "VARCHAR", "java.lang.String", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "false", "19", "0", "1", "CHAR", "java.lang.String", "PARAMETER_MODE_IN_OUT"},
               {"PARAMETER_NULLABLE", "false", "32", "0", "12", "VARCHAR", "java.lang.String", "PARAMETER_MODE_OUT"}};

	testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);

      		cs.close();
      		stmt.execute("DROP PROCEDURE PMDC");
           stmt.close();
}
 
Example 11
/**
        * test ParameterMetaData for Java procedures with CHAR parameters
 *
 * @exception SQLException if error occurs
        */
 public void testParameterMetadataWithCHARParameters () throws SQLException {

	Statement stmt = createStatement();
      		stmt.execute("CREATE PROCEDURE PMDC(IN pmdI_1 CHAR(10), IN pmdI_2 VARCHAR(25), INOUT pmdI_3 CHAR(19), OUT pmdI_4 VARCHAR(32)) language java parameter style java external name 'com.splicemachine.dbTesting.functionTests.tests.jdbcapi.ParameterMetaDataJdbc30Test.dummyString'");
     		CallableStatement cs = prepareCall("CALL PMDC(?, ?, ?, ?)");
	// parameters 1 and 2 are input only	
	cs.setString(1, "TEST0");
	cs.setString(2, "TEST1");
	// parameter 3 is input and output
               Object x = new String("TEST");
               cs.setObject(3,x, Types.CHAR);
               cs.registerOutParameter(3,Types.CHAR);
               //parameter 4 is output only
               cs.registerOutParameter(4,Types.CHAR);
		//verify the meta data for the parameters
              	ParameterMetaData paramMetaData = cs.getParameterMetaData();
	assertEquals("Unexpected parameter count", 4, paramMetaData.getParameterCount());

	//expected values to be stored in a 2dim. array
               String parameterMetaDataArray0 [][] = {
               //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
               {"PARAMETER_NULLABLE", "false", "10", "0", "1", "CHAR", "java.lang.String", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "false", "25", "0", "12", "VARCHAR", "java.lang.String", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "false", "19", "0", "1", "CHAR", "java.lang.String", "PARAMETER_MODE_IN_OUT"},
               {"PARAMETER_NULLABLE", "false", "32", "0", "12", "VARCHAR", "java.lang.String", "PARAMETER_MODE_OUT"}};

	testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);

      		cs.close();
      		stmt.execute("DROP PROCEDURE PMDC");
           stmt.close();
}
 
Example 12
/**
        * test ParameterMetaData for Java procedures with INTEGER parameters 
 * 
 * @exception SQLException if error occurs
        */
public void testParameterMetadataWithINTParameters () throws SQLException {

	Statement stmt = createStatement();
      		stmt.execute("CREATE PROCEDURE PMDI(IN pmdI_1 INTEGER, IN pmdI_2 INTEGER, INOUT pmdI_3 INTEGER, OUT pmdI_4 INTEGER) language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.jdbcapi.ParameterMetaDataJdbc30Test.dummyint'");
     		CallableStatement cs = prepareCall("CALL PMDI(?, ?, ?, ?)");

	// parameters 1 and 2 are input only
              	cs.setInt(1,1);
       	cs.setInt(2,1);
	// parameter 3 is input and output
               Object x = new Integer(1);
               cs.setObject(3,x, Types.INTEGER);
               cs.registerOutParameter(3,Types.INTEGER);
              	//parameter 4 is output only
              	cs.registerOutParameter(4,Types.INTEGER);

              	//verify the meta data for the parameters
              	ParameterMetaData paramMetaData = cs.getParameterMetaData();
              	assertEquals("Unexpected parameter count", 4, paramMetaData.getParameterCount());

	//expected values to be stored in a 2dim. array
               String parameterMetaDataArray0 [][] = {
               //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
               {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN_OUT"},
               {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_OUT"}};

               testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);
		
      		cs.close();
      		stmt.execute("DROP PROCEDURE PMDI");
           stmt.close();
}
 
Example 13
Source Project: fixflow   File: SqlCommandExtend.java    License: Apache License 2.0 4 votes vote down vote up
/**
 * 执行存储过程(更新,查询数据[简单查询、非纪录集],返回输出参数[非纪录集])
 * 
 * @param procName
 *            存储过程名称
 * @param parameters
 *            参数对象数组
 * @param os
 *            输出参数对象数组
 * @return 输出参数对象数组
 * @throws Exception
 */
public Object[] executeProcedureUpdate(String procName, Object[] parameters)
		throws Exception {
	logger.info("------------------------------------------------------------------------------------------------------");
	logger.info(" Run --> executeProcedureUpdate ##############   正在执行 存储过程: " + procName +"   ##############");
	CallableStatement cs = null;
	Object []returnVal = null;
	try {
	// 获取 存储过程 调用全名
	String fullPCallName = getProcedureCallName(procName,parameters.length);
	logger.info(" Run --> executeProcedureUpdate #   存储过程命令: " + fullPCallName +"   #");
	//获取存储过程参数信息
	ArrayList<HashMap<Object, Object>> p_Call_Info_List = getProcedureInfo(procName);
	//获取连接
	//getConnection();
	//创建 存储过程 执行对象
	cs = con.prepareCall(fullPCallName);
	//数组下标
	int index = 1;
	//输出参数下标 纪录
       ArrayList<Integer> outPutIndexList = new ArrayList<Integer>();
       logger.info(" Run --> executeProcedureUpdate #   参数个数是: " + parameters.length +"   #");
	for(HashMap<Object,Object> tempHash:p_Call_Info_List)
	{
		if("0".equals(tempHash.get("WAY")))
	    {
			//设置输入参数
			cs.setObject(index, parameters[index-1]);
			logger.info(" Run --> executeProcedureUpdate #   输入 Input: 编号:" + index +" 值: "+parameters[index-1]+" 类型: "+parameters[index-1].getClass()+"   #");
		}
		else
		{
			//注册输出参数
			cs.registerOutParameter(index, getDataType(tempHash.get("TYPENAME").toString()));
			//纪录输出参数的下标
			outPutIndexList.add(index);
			logger.info(" Run --> executeProcedureUpdate #   输出 OutPut: 编号:" + index +" 值: "+parameters[index-1]+" 类型: "+parameters[index-1].getClass()+"   #");
		}
		index++;
	}
	logger.info(" Run --> executeProcedureUpdate #   参数设置完毕,正在执行中 ... :   #");
	
	//-------------------- 执行 -----------------
	if(!cs.execute())
	{
		returnVal = new Object[outPutIndexList.size()];
		logger.info(" Run --> executeProcedureUpdate #   执行成功! :   #");
		//取输 出参数的 返回值
		for(int i = 0 ;i<outPutIndexList.size();i++)
		{
			returnVal[i] = cs.getObject(outPutIndexList.get(i));
			logger.info(" Run --> executeProcedureUpdate #   返回值 "+(i+1)+" "+returnVal[i]+"   #");
		}
		//con.commit();//提交
	}
	} catch (Exception e) {
		logger.info(" Run --> executeProcedureUpdate #   执行失败!事务回滚中... :   #");
		//con.rollback();
		throw e;
	} 
	logger.info("------------------------------------------------------------------------------------------------------");
	return returnVal;
}
 
Example 14
Source Project: binnavi   File: PostgreSQLEdgeFunctions.java    License: Apache License 2.0 4 votes vote down vote up
/**
 * 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);
  }
}
 
Example 15
Source Project: ureport   File: ProcedureUtils.java    License: Apache License 2.0 4 votes vote down vote up
private static StatementWrapper buildProcedureCallableStatement(String sql,Map<String, Object> pmap,Connection conn){
	try {
		Map<String,Object> paramMap=new LinkedHashMap<String,Object>();
		int leftParnPos=sql.indexOf("(");
		int rightParnPos=sql.indexOf(")");
		String paramStr="";
		if(leftParnPos>-1 && rightParnPos>-1){
			paramStr=sql.substring(leftParnPos+1,rightParnPos);				
		}
		int oracleCursorIndex=-1,paramIndex=0;
		String[] str=paramStr.split(",");
		for(String param:str){
			paramIndex++;
			param=param.trim();
			if(param.toLowerCase().equals("oracle")){
				sql=sql.replaceFirst(param, "?");
				oracleCursorIndex=paramIndex;
				continue;
			}else if(!param.startsWith(":")){
				continue;
			}
			sql=sql.replaceFirst(param, "?");
			String paramName=param.substring(1,param.length());
			Object paramValue=pmap.get(paramName);
			paramMap.put(paramName, (paramValue==null ? "" : paramValue));
		}
		String procedure="{"+sql+"}";
		CallableStatement cs= conn.prepareCall(procedure);
		int index=1;
		for(String name:paramMap.keySet()){
			Object value=paramMap.get(name);
			if(value instanceof String){
				cs.setString(index,(String)value);									
			}else if(value instanceof Date){
				Date date=(Date)value;
				cs.setDate(index, new java.sql.Date(date.getTime()));
			}else if(value instanceof Integer){
				cs.setInt(index, (Integer)value);
			}else if(value instanceof Float){
				cs.setFloat(index, (Float)value);
			}else if(value instanceof Double){
				cs.setDouble(index, (Double)value);
			}else{
				cs.setObject(index, value);
			}
			index++;
		}
		if(oracleCursorIndex>-1){
			cs.registerOutParameter(oracleCursorIndex, -10);
		}
		return new StatementWrapper(cs,oracleCursorIndex);
	} catch (SQLException e) {
		throw new ReportException(e);
	}
}
 
Example 16
Source Project: gemfirexd-oss   File: UseCase1Client.java    License: Apache License 2.0 4 votes vote down vote up
private void doSingleMatchWithOnePrimaryKey(CallableStatement callableStmt,
                                            String clientAccount)
throws SQLException {
  long start = this.useCase1stats.startMatch();

  // set up inBackOfficeMsg with one client account

  Map<String, String> tableMap = new HashMap<String, String>();
  tableMap.put(CLIENT_ACCOUNT, clientAccount);

  Map<String,Map<String,String>> inBackOfficeMsg = new HashMap<String,Map<String,String>>();
  inBackOfficeMsg.put(SECL_BO_DATA_STATUS_HIST, tableMap);

  // set up singlePrimaryMatchingKeySet with one MatchingInfo

  Set<MatchingInfo> singlePrimaryMatchingKeySet = new HashSet<MatchingInfo>();
  MatchingInfo mi = new MatchingInfo();
  mi.setBackOfficeCode(IPAY);
  mi.setKeyName(CLIENT_ACCOUNT);
  mi.setMatchingPriority(1);
  mi.setChnDataTable(SECT_CHANNEL_DATA);
  mi.setBoDataTable(SECL_BO_DATA_STATUS_HIST);
  mi.setBoOnBoardTimestamp(new Timestamp(new Date().getTime()));
  mi.setKeyOnBoardTimestamp(new Timestamp(new Date().getTime()));
  singlePrimaryMatchingKeySet.add(mi);

  SortedMap<Integer,Set<MatchingInfo>> inMatchingKeyMap = new TreeMap<Integer,Set<MatchingInfo>>();
  inMatchingKeyMap.put(Integer.valueOf(1), singlePrimaryMatchingKeySet);

  if (Log.getLogWriter().fineEnabled()) {
    Log.getLogWriter().fine("Match Task matching CLIENT_ACCOUNT=" + clientAccount);
  }

  callableStmt.setObject(1, inBackOfficeMsg);
  callableStmt.setObject(2, inMatchingKeyMap);
  callableStmt.execute();

  int errorStateValue = callableStmt.getInt(3);
  ResultSet rs = callableStmt.getResultSet();
  int countOfRows = 0;
  while (rs.next()) {
    countOfRows++;
    if (Log.getLogWriter().fineEnabled()) {
      Log.getLogWriter().fine("Match Task got" +
                              " rs.getString(1) " + rs.getString(1));
    }
  }
  rs.close();
  rs = null;
  if (Log.getLogWriter().fineEnabled()) {
    Log.getLogWriter().fine("Match Task got" +
                            " countOfRows=" + countOfRows +
                            " errorStateValue=" + errorStateValue);
  }
  this.useCase1stats.endMatch(start, countOfRows, errorStateValue);
}
 
Example 17
/**
    	* Testing a callable statement by calling a SQL procedure with 
* IN parameters, OUT parameters and IN_OUT parameters.
       *
* @exception SQLException if error occurs
    	*/
public void testCallableStatement () throws SQLException {

       	Statement stmt = createStatement();

       	stmt.executeUpdate("create procedure dummyint( " + 
			   "in a integer, in b integer, " + 
			   "out c integer, inout d integer) " + 
			   "language java external name "+ 
			   "'org.apache.derbyTesting.functionTests." +
			   "tests.jdbcapi.ParameterMetaDataJdbc30Test.dummyint' " +
			   "parameter style java");
      		CallableStatement cs = prepareCall("CALL dummyint(?,?,?,?)");
	// parameters 1 and 2 are input only
     		cs.setInt(1,1);
     		cs.setInt(2,1);
     		//parameter 3 is output only
     		cs.registerOutParameter(3,Types.INTEGER);
     		// parameter 4 is input and output
     		Object x = new Integer(1);
     		cs.setObject(4,x, Types.INTEGER);
     		cs.registerOutParameter(4,Types.INTEGER);

     		//verify the meta data for the parameters
     		ParameterMetaData paramMetaData = cs.getParameterMetaData();
	assertEquals("Unexpected parameter count", 4, paramMetaData.getParameterCount());

	//expected values to be stored in a 2dim. array	
	String [][] parameterMetaDataArray0 = {
               //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
	{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
	{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
	{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_OUT"},
	{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN_OUT"}};
		
	testParameterMetaData(paramMetaData, parameterMetaDataArray0);

     		cs.execute();

	/*
	/* bug 4450 - parameter meta data info for the return parameter was giving
     		/* null pointer exception. In the past, we didn't need to keep the return
     		/* parameter info for callable statement execution and hence we never
     		/* generated the meta data for it. To fix the problem, at the parsing time,
     		/* I set a flag if the call statement is of ? = form. If so, the first
     		/* parameter is a return parameter and save it's meta data rather than
     		/* discarding it.
 	 */	

     		cs = prepareCall("? = call RDB(?)");
     		paramMetaData = cs.getParameterMetaData();
	assertEquals("Unexpected parameter count", 2, paramMetaData.getParameterCount());

	//expected values to be stored in a 2dim. array
               String parameterMetaDataArray1 [][] = {
               //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
               {"PARAMETER_NULLABLE", "true", "127", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_OUT"},
               {"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"}};

               testParameterMetaData(paramMetaData, parameterMetaDataArray1);

	stmt.close();
	cs.close();
}
 
Example 18
/**
 * This function appends a local instruction comment to the list of local
 * instruction comments associated with the given instruction residing in the
 * given code node to the database.
 *
 * @param provider The provider to access the database.
 * @param codeNode The code node in which the instruction resides.
 * @param instruction The instruction to which the comment is associated.
 * @param commentText The text of the comment to be appended.
 * @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 stored in the
 *         database.
 */
public static int appendLocalInstructionComment(final SQLProvider provider,
    final INaviCodeNode codeNode, final INaviInstruction instruction, final String commentText,
    final Integer userId) throws CouldntSaveDataException {

  Preconditions.checkNotNull(provider, "IE02423: provider argument can not be null");
  Preconditions.checkNotNull(codeNode, "IE02424: codeNode argument can not be null");
  Preconditions.checkNotNull(instruction, "IE02425: instruction argument can not be null");
  Preconditions.checkNotNull(commentText, "IE02426: comment argument can not be null");
  Preconditions.checkNotNull(userId, "IE02427: userId argument can not be null");

  final CConnection connection = provider.getConnection();

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

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

    try {

      appendCommentFunction.registerOutParameter(1, Types.INTEGER);
      appendCommentFunction.setInt(2, instruction.getModule().getConfiguration().getId());
      appendCommentFunction.setInt(3, codeNode.getId());
      appendCommentFunction.setObject(4, instruction.getAddress().toBigInteger(), Types.BIGINT);
      appendCommentFunction.setInt(5, userId);
      appendCommentFunction.setString(6, commentText);

      appendCommentFunction.execute();

      final int 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);
  }
}
 
Example 19
/**
        * test ParameterMetaData for Java procedures with some literal parameters
 *
 * @exception SQLException if error occurs
        */
public void testParameterMetadataWithLITERALParameters () throws SQLException {

     		CallableStatement cs = prepareCall("CALL PMDD(32.4, ?, ?, ?)");
	// parameters 2 is input only
               cs.setBigDecimal(1,new BigDecimal("1"));;
               // parameter 3 is input and output
               Object x = new BigDecimal(1.1);
               cs.setObject(2,x, Types.DECIMAL);
               cs.registerOutParameter(2,Types.DECIMAL);
               //parameter 4 is output only
               cs.registerOutParameter(3,Types.DECIMAL);

	//verify the meta data for the parameters
               ParameterMetaData paramMetaData = cs.getParameterMetaData();
               assertEquals("Unexpected parameter count", 3, paramMetaData.getParameterCount());

	//expected values to be stored in a 2dim. array
               String parameterMetaDataArray0 [][] = {
               //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
               {"PARAMETER_NULLABLE", "true", "4", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "true", "9", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN_OUT"},
               {"PARAMETER_NULLABLE", "true", "10", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_OUT"}};

               testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray0);

         	cs.close();

     		cs = prepareCall("CALL PMDD(32.4, 47.9, ?, ?)");
	// parameter 3 is input and output
               Object y = new BigDecimal(1.1);
               cs.setObject(1,y, Types.DECIMAL);
               cs.registerOutParameter(1,Types.DECIMAL);
               //parameter 4 is output only
               cs.registerOutParameter(2,Types.DECIMAL);
	paramMetaData = cs.getParameterMetaData();
               assertEquals("Unexpected parameter count", 2, paramMetaData.getParameterCount());

	//expected values to be stored in a 2dim. array
               String parameterMetaDataArray1 [][] = {
               //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
               {"PARAMETER_NULLABLE", "true", "9", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN_OUT"},
               {"PARAMETER_NULLABLE", "true", "10", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_OUT"}};

               testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray1);

         	cs.close();

     		cs = prepareCall("CALL PMDD(?, 38.2, ?, ?)");
	// parameters 1 is input only
               cs.setBigDecimal(1,new BigDecimal("1"));;
               // parameter 3 is input and output
               Object z = new BigDecimal(1.1);
               cs.setObject(2,z, Types.DECIMAL);
               cs.registerOutParameter(2,Types.DECIMAL);
               //parameter 4 is output only
               cs.registerOutParameter(3,Types.DECIMAL);

	//verify the meta data for the parameters
               paramMetaData = cs.getParameterMetaData();
	assertEquals("Unexpected parameter count", 3, paramMetaData.getParameterCount());

	//expected values to be stored in a 2dim. array
               String parameterMetaDataArray2 [][] = {
               //isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
               {"PARAMETER_NULLABLE", "true", "5", "3", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN"},
               {"PARAMETER_NULLABLE", "true", "9", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN_OUT"},
               {"PARAMETER_NULLABLE", "true", "10", "2", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_OUT"}};

               testParameterMetaData(cs.getParameterMetaData(), parameterMetaDataArray2);

         	cs.close();
}
 
Example 20
Source Project: gemfirexd-oss   File: BatchUpdateTest.java    License: Apache License 2.0 4 votes vote down vote up
public void testCallableStatementBatch() throws SQLException {

        println("Positive Callable Statement: " +
            "statement testing callable statement batch");
        CallableStatement cs = prepareCall("insert into t1 values(?)");

        cs.setInt(1, 1);
        cs.addBatch();
        cs.setInt(1,2);
        cs.addBatch();
        executeBatchCallableStatement(cs);

        cleanUpCallableStatement(cs, "t1");

        /* For 'beetle' bug 2813 - setDate/setTime/setTimestamp
         * calls on callableStatement throws ClassNotFoundException
         * verify setXXXX() works with Date, Time and Timestamp
         * on CallableStatement.
         */
        cs = prepareCall("insert into datetab values(?)");

        cs.setDate(1, Date.valueOf("1990-05-05"));
        cs.addBatch();
        cs.setDate(1,Date.valueOf("1990-06-06"));
        cs.addBatch();

        executeBatchCallableStatement(cs);

        cleanUpCallableStatement(cs, "datetab");

        cs = prepareCall("insert into timetab values(?)");

        cs.setTime(1, Time.valueOf("11:11:11"));
        cs.addBatch();
        cs.setTime(1, Time.valueOf("12:12:12"));
        cs.addBatch();
        executeBatchCallableStatement(cs);

        cleanUpCallableStatement(cs, "timestamptab");

        cs = prepareCall("insert into timestamptab values(?)");

        cs.setTimestamp(1, Timestamp.valueOf("1990-05-05 11:11:11.1"));
        cs.addBatch();
        cs.setTimestamp(1, Timestamp.valueOf("1992-07-07 12:12:12.2"));
        cs.addBatch();
        executeBatchCallableStatement(cs);

        cleanUpCallableStatement(cs, "timestamptab");

        // Try with a user type
        cs = prepareCall("insert into usertypetab values(?)");

        cs.setObject(1, Date.valueOf("1990-05-05"));
        cs.addBatch();
        cs.setObject(1,Date.valueOf("1990-06-06"));
        cs.addBatch();
        executeBatchCallableStatement(cs);

        cleanUpCallableStatement(cs, "usertypetab");
    }