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

The following examples show how to use java.sql.CallableStatement#registerOutParameter() . 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 File: BlobStoredProcedureTest.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up
/**
 * 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 2
Source File: UseCase1Client.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up
private void useCase1MatchWorkload() throws SQLException {
  SharedCounters counters = UseCase1Blackboard.getInstance().getSharedCounters();
  int throttleMs = UseCase1Prms.getWorkloadThrottleMs();
  while (true) {
    // check with traffic cop
    terminateIfNeeded(counters);
    pauseIfNeeded(counters);

    // initialize the match stored procedure call
    CallableStatement callableStmt = this.connection.prepareCall(MATCH_CALL);
    callableStmt.registerOutParameter(3, Types.INTEGER);

    // run a workload batch
    long batchEnd = System.currentTimeMillis() + this.batchSeconds * 1000;
    while (System.currentTimeMillis() < batchEnd) {
      if (throttleMs != 0) { // throttle was set so choose each time at random
        MasterController.sleepForMs(UseCase1Prms.getWorkloadThrottleMs());
      }
      int index = this.rng.nextInt(0, clientAccounts.size() - 1);
      String clientAccount = clientAccounts.get(index);
      doSingleMatchWithOnePrimaryKey(callableStmt, clientAccount);
    }
    Log.getLogWriter().info("Completed a workload batch");
  }
}
 
Example 3
Source File: BlobStoredProcedureTest.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up
/**
 * 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 4
Source File: ProcedureImpl.java    From tephra with MIT License 6 votes vote down vote up
@Override
public SqlTable query(String dataSource, String sql, Object[] args) {
    try {
        long time = System.currentTimeMillis();
        CallableStatement pstmt = newPreparedStatement(dataSource, Mode.Read, sql);
        setArgs(pstmt, args);
        int index = (validator.isEmpty(args) ? 0 : args.length) + 1;
        pstmt.registerOutParameter(index, Types.REF_CURSOR);
        pstmt.execute();
        SqlTable sqlTable = query((ResultSet) pstmt.getObject(index));
        pstmt.close();

        if (logger.isDebugEnable())
            logger.debug("执行SQL[{}:{}:{}:{}]检索操作。", dataSource, sql, converter.toString(args), System.currentTimeMillis() - time);

        return sqlTable;
    } catch (SQLException e) {
        logger.warn(e, "执行SQL[{}:{}:{}]检索时发生异常!", dataSource, sql, converter.toString(args));

        throw new RuntimeException(e);
    }
}
 
Example 5
Source File: CallableTest.java    From spliceengine with GNU Affero General Public License v3.0 5 votes vote down vote up
/**
 * Calls a SQL function that takes no input parameter and returns one 
 * output.
 * @throws SQLException 
 */
public void testNoInOneOutFunc() throws SQLException
{
    CallableStatement cs = prepareCall
        ("? = call NO_IN_ONE_OUT_FUNC()");
    cs.registerOutParameter (1, java.sql.Types.INTEGER);
    cs.execute();
    assertEquals("NO_IN_ONE_OUT_FUNC output value", 55, cs.getInt(1));

}
 
Example 6
Source File: CallableTest.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
/**
 * Calls a SQL function that takes no input parameter and returns one 
 * output.
 * @throws SQLException 
 */
public void testNoInOneOutFunc() throws SQLException
{
    CallableStatement cs = prepareCall
        ("? = call NO_IN_ONE_OUT_FUNC()");
    cs.registerOutParameter (1, java.sql.Types.INTEGER);
    cs.execute();
    assertEquals("NO_IN_ONE_OUT_FUNC output value", 55, cs.getInt(1));

}
 
Example 7
Source File: ClobStoredProcedureTest.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
/**
 * 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();
}
 
Example 8
Source File: PostgreSQLNativeViewCreator.java    From binnavi with Apache License 2.0 5 votes vote down vote up
/**
 * Creates the native call graph view of a module.
 * 
 * @param connection Connection to the database.
 * @param moduleId ID of the BinNavi module where the view is created.
 * 
 * @return The ID of the created view.
 * 
 * @throws SQLException Thrown if creating the view failed.
 */
public static int createNativeCallgraphView(final CConnection connection, final int moduleId)
    throws SQLException {
  Preconditions.checkNotNull(connection, "IE00706: connection argument can not be null");
  final String query = "{ ? = call create_native_call_graph_view(?) }";
  final CallableStatement call = connection.getConnection().prepareCall(query);
  call.registerOutParameter(1, Types.INTEGER);
  call.setInt(2, moduleId);
  call.execute();
  return call.getInt(1);
}
 
Example 9
Source File: DAProcedures.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
protected static ResultSet[] callProcedureByCidRangePortfolio(Connection conn, String sql, 
    int cid1, int cid2, int sid, int tid, int[] data) throws SQLException { 
  ResultSet[] rs = new ResultSet[4];
  CallableStatement cs = null;
  cs = conn.prepareCall(sql);
  Log.getLogWriter().info(sql + " with cid1: " + cid1 + " and with cid2: " + cid2  +
      " with sid: " + sid + " and with tid: " + tid );
  cs.setInt(1, cid1);
  cs.setInt(2, cid2);
  cs.setInt(3, sid);
  cs.setInt(4, tid);
  cs.registerOutParameter(5, Types.INTEGER);
  cs.execute();
  data[0] = new Integer(cs.getInt(5));

  rs[0] = cs.getResultSet();
  int i=1;
  while (cs.getMoreResults(Statement.KEEP_CURRENT_RESULT)) {
    Log.getLogWriter().info("has more results");
    rs[i] = cs.getResultSet();
    i++;
  }
  if (rs == null) Log.getLogWriter().info("could not get result sets in callProcedureByCidRangePortfolio");
  
  SQLWarning warning = cs.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
  return rs;
}
 
Example 10
Source File: BatchUpdateTest.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
public void testCallableStatementWithOutputParamBatch()
throws SQLException {

    println("Negative Callable Statement: " +
        "callable statement with output parameters in the batch");
    Statement s = createStatement();

    s.execute("CREATE PROCEDURE " +
        "takesString(OUT P1 VARCHAR(40), IN P2 INT) " +
        "EXTERNAL NAME '" + this.getClass().getName() + ".takesString'" +
    " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");

    CallableStatement cs = prepareCall("call takesString(?,?)");
    cs.registerOutParameter(1, Types.CHAR);
    cs.setInt(2, Types.INTEGER);
    try
    {
        cs.addBatch();
        if (usingEmbedded())
            fail("Expected to see error XJ04C");
        else if (usingDerbyNetClient()) {
            executeBatchCallableStatement(cs);
        }
    }
    catch (SQLException sqle)
    {
        // Check to be sure the exception is callback related
        assertSQLState("XJ04C", sqle);
    }

    cs.close();
    s.execute("drop procedure takesString");
    s.close();
    rollback();
}
 
Example 11
Source File: CallableStatementRegressionTest.java    From FoxTelem with GNU General Public License v3.0 5 votes vote down vote up
/**
 * Tests fix for BUG#60816 - Cannot pass NULL to an INOUT procedure parameter
 * 
 * @throws Exception
 */
public void testBug60816() throws Exception {
    createProcedure("test60816_1", "(INOUT x INTEGER)\nBEGIN\nSET x = x + 1;\nEND");
    createProcedure("test60816_2", "(x INTEGER, OUT y INTEGER)\nBEGIN\nSET y = x + 1;\nEND");
    createProcedure("test60816_3", "(INOUT x INTEGER)\nBEGIN\nSET x = 10;\nEND");

    CallableStatement call = this.conn.prepareCall("{ call test60816_1(?) }");
    call.setInt(1, 1);
    call.registerOutParameter(1, Types.INTEGER);
    call.execute();
    assertEquals(2, call.getInt(1));

    call = this.conn.prepareCall("{ call test60816_2(?, ?) }");
    call.setInt(1, 1);
    call.registerOutParameter(2, Types.INTEGER);
    call.execute();
    assertEquals(2, call.getInt(2));

    call = this.conn.prepareCall("{ call test60816_2(?, ?) }");
    call.setNull(1, Types.INTEGER);
    call.registerOutParameter(2, Types.INTEGER);
    call.execute();
    assertEquals(0, call.getInt(2));
    assertTrue(call.wasNull());

    call = this.conn.prepareCall("{ call test60816_1(?) }");
    call.setNull(1, Types.INTEGER);
    call.registerOutParameter(1, Types.INTEGER);
    call.execute();
    assertEquals(0, call.getInt(1));
    assertTrue(call.wasNull());

    call = this.conn.prepareCall("{ call test60816_3(?) }");
    call.setNull(1, Types.INTEGER);
    call.registerOutParameter(1, Types.INTEGER);
    call.execute();
    assertEquals(10, call.getInt(1));

}
 
Example 12
Source File: BlobStoredProcedureTest.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
/**
 * 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();
}
 
Example 13
Source File: CallableStatementRegressionTest.java    From Komondor with GNU General Public License v3.0 4 votes vote down vote up
/**
 * Tests fix for BUG#15464 - INOUT parameter does not store IN value.
 * 
 * @throws Exception
 *             if the test fails
 */

public void testBug15464() throws Exception {
    if (!serverSupportsStoredProcedures()) {
        return;
    }

    createProcedure("testInOutParam",
            "(IN p1 VARCHAR(255), INOUT p2 INT)\nbegin\n DECLARE z INT;\n" + "SET z = p2 + 1;\nSET p2 = z;\nSELECT p1;\nSELECT CONCAT('zyxw', p1);\nend\n");

    CallableStatement storedProc = null;

    storedProc = this.conn.prepareCall("{call testInOutParam(?, ?)}");

    storedProc.setString(1, "abcd");
    storedProc.setInt(2, 4);
    storedProc.registerOutParameter(2, Types.INTEGER);

    storedProc.execute();

    assertEquals(5, storedProc.getInt(2));
}
 
Example 14
Source File: CallableStatementTest.java    From jTDS with GNU Lesser General Public License v2.1 4 votes vote down vote up
/**
 * Test for bug [983432] Prepared call doesn't work with jTDS 0.8
 */
public void testCallableRegisterOutParameter4()
   throws Exception
{
   // cleanup remains from last run
   dropProcedure( "rop4" );
   dropType( "T_INTEGER" );

   CallableStatement cstmt = con.prepareCall( "{call sp_addtype T_INTEGER, int, 'NULL'}" );
   Statement stmt = con.createStatement();

   try
   {
      cstmt.execute();
      cstmt.close();

      stmt.execute( "create procedure rop4 @data T_INTEGER OUTPUT as\r\n " + "begin\r\n" + "set @data = 1\r\n" + "end" );
      stmt.close();

      cstmt = con.prepareCall( "{call rop4(?)}" );

      cstmt.registerOutParameter( 1, Types.VARCHAR );
      cstmt.execute();

      assertEquals( cstmt.getInt( 1 ), 1 );
      assertTrue( !cstmt.wasNull() );
      cstmt.close();

      cstmt = con.prepareCall( "rop4 ?" );

      cstmt.registerOutParameter( 1, Types.VARCHAR );
      cstmt.execute();

      assertEquals( cstmt.getInt( 1 ), 1 );
      assertTrue( !cstmt.wasNull() );
      cstmt.close();
   }
   finally
   {
      // cleanup
      dropProcedure( "rop4" );
      dropType( "T_INTEGER" );
   }
}
 
Example 15
Source File: SQLServerDbWriteAccess.java    From ats-framework with Apache License 2.0 4 votes vote down vote up
/**
 * 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.setInt(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);
        }
    }
}
 
Example 16
Source File: ProcedureTest.java    From gemfirexd-oss with 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 17
Source File: QueryTimeOutDUnit.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
public void testQueryTimeOutStoredProc_2() throws Exception {
  startVMs(1, 2);
  Connection cxn = TestUtil.getConnection() ;
  Statement stmt = cxn.createStatement();
  
  // create a procedure
  stmt.execute("CREATE PROCEDURE myProc2 " 
      + "(IN timOutOnCallableStmt INTEGER, "
      + " OUT count INTEGER)" 
      + "LANGUAGE JAVA PARAMETER STYLE JAVA "
      + "READS SQL DATA " + "DYNAMIC RESULT SETS 2 " + "EXTERNAL NAME '"
      + QueryTimeOutDUnit.class.getName() + ".myProc2'");
  
  stmt.execute("create table MyTable(x int, y int) partition by column(x)");
  stmt.execute("insert into MyTable values (1, 1), (2, 2), (3, 3), " +
          "(4, 4), (5, 5), (6, 6), (7, 7)");
  
  // set a large timeout on outer callable stmt so that it does not timeout
  int timOutOnCallableStmt = 10; // seconds
  CallableStatement callableStmt = cxn
       // first param to myProc2- timeOut to be set on callable stmt
      // CALL myProc2(10, ?) ON TABLE MyTable 
      .prepareCall("{CALL myProc2(" + timOutOnCallableStmt +  
              ", ?) ON TABLE MyTable}");
  callableStmt.registerOutParameter(1, Types.INTEGER);

  callableStmt.setQueryTimeout(timOutOnCallableStmt);
  
  // suspend the execution to allow query get timed out
  CacheSerializableRunnable csr = new CacheSerializableRunnable(
      "_testTimeOut_") {
    @Override
    public void run2() {
      GemFireXDQueryObserver old = GemFireXDQueryObserverHolder
          .setInstance(new GemFireXDQueryObserverAdapter() {
            @Override
            public void onGetNextRowCoreOfBulkTableScan(
                com.pivotal.gemfirexd.internal.iapi.sql.ResultSet resultSet) {
              try {
                Thread.sleep(1500);
              } catch (InterruptedException e) {
              }
            }
          });
    }
  };
  // clientExecute(1, csr);
  serverExecute(1, csr);
  serverExecute(2, csr);
  
  addExpectedException(new int[] { 1 }, new int[] { 1, 2 },
      SQLException.class);
  try {
    callableStmt.execute();
    fail("This test should have thrown exception "
        + "due to query timeout (exception state XCL52)");
  } catch (SQLException se) {
    if (!se.getSQLState().equals("XCL52")) {
      throw se;
    }
    assertEquals(10, callableStmt.getQueryTimeout());
  } finally {
    removeExpectedException(new int[] { 1 }, new int[] { 1, 2 },
        SQLException.class);
  }
}
 
Example 18
Source File: ParameterMappingTest.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
/**
 * Verify correct mapping of clobs.
 */
public void testClobMapping() throws Exception
{
    Connection conn = getConnection();
    PreparedStatement ps;
    CallableStatement cs;
    Clob outVal;

    //
    // Clob input parameter
    //
    ps = chattyPrepare
        (
         conn,
         "create procedure clobIn\n" +
         "( in c clob, out result varchar( 100 ) )\n" +
         "language java\n" +
         "parameter style java\n" +
         "no sql\n" +
         "external name '" + getClass().getName() + ".clobIn'\n"
         );
    ps.execute();
    ps.close();

    cs = chattyPrepareCall( conn, "call clobIn( cast( 'def' as clob ), ? )" );
    cs.registerOutParameter( 1, Types.VARCHAR );
    cs.execute();
    assertEquals( "def", cs.getString( 1 ) );
    cs.close();

    cs = chattyPrepareCall( conn, "call clobIn( ?, ? )" );
    cs.setClob( 1, new StringColumnVTI.SimpleClob( "ghi" ) );
    cs.registerOutParameter( 2, Types.VARCHAR );
    cs.execute();
    assertEquals( "ghi", cs.getString( 2 ) );
    cs.close();

    //
    // Clob output parameter
    //
    ps = chattyPrepare
        (
         conn,
         "create procedure clobOut\n" +
         "( out c clob )\n" +
         "language java\n" +
         "parameter style java\n" +
         "no sql\n" +
         "external name '" + getClass().getName() + ".clobOut'\n"
         );
    ps.execute();
    ps.close();
    
    cs = chattyPrepareCall( conn, "call clobOut( ? )" );
    cs.registerOutParameter( 1, Types.CLOB );
    cs.execute();
    outVal = cs.getClob( 1 );
    assertEquals( "abc", outVal.getSubString( 1L, (int) outVal.length() ) );
    cs.close();

    //
    // Clob inout parameter
    //
    ps = chattyPrepare
        (
         conn,
         "create procedure clobInOut\n" +
         "( inout c clob )\n" +
         "language java\n" +
         "parameter style java\n" +
         "no sql\n" +
         "external name '" + getClass().getName() + ".clobInOut'\n"
         );
    ps.execute();
    ps.close();
    
    cs = chattyPrepareCall( conn, "call clobInOut( ? )" );
    cs.setClob( 1, new StringColumnVTI.SimpleClob( "ghi" ) );
    cs.registerOutParameter( 1, Types.CLOB );
    cs.execute();
    outVal = cs.getClob( 1 );
    assertEquals( "ihg", outVal.getSubString( 1L, (int) outVal.length() ) );

    Clob inValue = makeBigClob();
    cs.setClob( 1, inValue );
    cs.execute();
    Clob outValue = cs.getClob( 1 );
    compareClobs( inValue, outValue );
    
    cs.close();
}
 
Example 19
Source File: CallableStatementRegressionTest.java    From Komondor with GNU General Public License v3.0 4 votes vote down vote up
public void testBug43576() throws Exception {
    createTable("TMIX91P",
            "(F01SMALLINT         SMALLINT NOT NULL, F02INTEGER          INTEGER,F03REAL             REAL,"
                    + "F04FLOAT            FLOAT,F05NUMERIC31X4      NUMERIC(31,4), F06NUMERIC16X16     NUMERIC(16,16), F07CHAR_10          CHAR(10),"
                    + " F08VARCHAR_10       VARCHAR(10), F09CHAR_20          CHAR(20), F10VARCHAR_20       VARCHAR(20), F11DATE         DATE,"
                    + " F12DATETIME         DATETIME, PRIMARY KEY (F01SMALLINT))");

    this.stmt.executeUpdate("INSERT INTO TMIX91P VALUES (1,1,1234567.12,1234567.12,111111111111111111111111111.1111,.111111111111111,'1234567890',"
            + "'1234567890','CHAR20CHAR20','VARCHAR20ABCD','2001-01-01','2001-01-01 01:01:01.111')");

    this.stmt.executeUpdate("INSERT INTO TMIX91P VALUES (7,1,1234567.12,1234567.12,22222222222.0001,.99999999999,'1234567896','1234567896','CHAR20',"
            + "'VARCHAR20ABCD','2001-01-01','2001-01-01 01:01:01.111')");

    this.stmt.executeUpdate("INSERT INTO TMIX91P VALUES (12,12,1234567.12,1234567.12,111222333.4444,.1234567890,'2234567891','2234567891','CHAR20',"
            + "'VARCHAR20VARCHAR20','2001-01-01','2001-01-01 01:01:01.111')");

    createProcedure("MSQSPR100",
            "\n( p1_in  INTEGER , p2_in  CHAR(20), OUT p3_out INTEGER, OUT p4_out CHAR(11))\nBEGIN "
                    + "\n SELECT F01SMALLINT,F02INTEGER, F11DATE,F12DATETIME,F03REAL \n FROM TMIX91P WHERE F02INTEGER = p1_in; "
                    + "\n SELECT F02INTEGER,F07CHAR_10,F08VARCHAR_10,F09CHAR_20 \n FROM TMIX91P WHERE  F09CHAR_20 = p2_in ORDER BY F02INTEGER ; "
                    + "\n SET p3_out  = 144; \n SET p4_out  = 'CHARACTER11'; \n SELECT p3_out, p4_out; END");

    String sql = "{call MSQSPR100(1,'CHAR20',?,?)}";

    CallableStatement cs = this.conn.prepareCall(sql);

    cs.registerOutParameter(1, Types.INTEGER);
    cs.registerOutParameter(2, Types.CHAR);

    cs.execute();
    cs.close();

    createProcedure("bug43576_1", "(OUT nfact VARCHAR(100), IN ccuenta VARCHAR(100),\nOUT ffact VARCHAR(100),\nOUT fdoc VARCHAR(100))\nBEGIN"
            + "\nSET nfact = 'ncfact string';\nSET ffact = 'ffact string';\nSET fdoc = 'fdoc string';\nEND");

    createProcedure("bug43576_2", "(IN ccuent1 VARCHAR(100), IN ccuent2 VARCHAR(100),\nOUT nfact VARCHAR(100),\nOUT ffact VARCHAR(100),"
            + "\nOUT fdoc VARCHAR(100))\nBEGIN\nSET nfact = 'ncfact string';\nSET ffact = 'ffact string';\nSET fdoc = 'fdoc string';\nEND");

    Properties props = new Properties();
    props.put("jdbcCompliantTruncation", "true");
    props.put("useInformationSchema", "true");
    Connection conn1 = null;
    conn1 = getConnectionWithProps(props);
    try {
        CallableStatement callSt = conn1.prepareCall("{ call bug43576_1(?, ?, ?, ?) }");
        callSt.setString(2, "xxx");
        callSt.registerOutParameter(1, java.sql.Types.VARCHAR);
        callSt.registerOutParameter(3, java.sql.Types.VARCHAR);
        callSt.registerOutParameter(4, java.sql.Types.VARCHAR);
        callSt.execute();

        assertEquals("ncfact string", callSt.getString(1));
        assertEquals("ffact string", callSt.getString(3));
        assertEquals("fdoc string", callSt.getString(4));

        CallableStatement callSt2 = conn1.prepareCall("{ call bug43576_2(?, ?, ?, ?, ?) }");
        callSt2.setString(1, "xxx");
        callSt2.setString(2, "yyy");
        callSt2.registerOutParameter(3, java.sql.Types.VARCHAR);
        callSt2.registerOutParameter(4, java.sql.Types.VARCHAR);
        callSt2.registerOutParameter(5, java.sql.Types.VARCHAR);
        callSt2.execute();

        assertEquals("ncfact string", callSt2.getString(3));
        assertEquals("ffact string", callSt2.getString(4));
        assertEquals("fdoc string", callSt2.getString(5));

        CallableStatement callSt3 = conn1.prepareCall("{ call bug43576_2(?, 'yyy', ?, ?, ?) }");
        callSt3.setString(1, "xxx");
        // callSt3.setString(2, "yyy");
        callSt3.registerOutParameter(2, java.sql.Types.VARCHAR);
        callSt3.registerOutParameter(3, java.sql.Types.VARCHAR);
        callSt3.registerOutParameter(4, java.sql.Types.VARCHAR);
        callSt3.execute();

        assertEquals("ncfact string", callSt3.getString(2));
        assertEquals("ffact string", callSt3.getString(3));
        assertEquals("fdoc string", callSt3.getString(4));
    } finally {
        conn1.close();
    }
}
 
Example 20
Source File: CallableStatementRegressionTest.java    From FoxTelem with GNU General Public License v3.0 4 votes vote down vote up
/**
 * Tests fix for BUG#15464 - INOUT parameter does not store IN value.
 * 
 * @throws Exception
 *             if the test fails
 */

public void testBug15464() throws Exception {

    createProcedure("testInOutParam",
            "(IN p1 VARCHAR(255), INOUT p2 INT)\nbegin\n DECLARE z INT;\n" + "SET z = p2 + 1;\nSET p2 = z;\nSELECT p1;\nSELECT CONCAT('zyxw', p1);\nend\n");

    CallableStatement storedProc = null;

    storedProc = this.conn.prepareCall("{call testInOutParam(?, ?)}");

    storedProc.setString(1, "abcd");
    storedProc.setInt(2, 4);
    storedProc.registerOutParameter(2, Types.INTEGER);

    storedProc.execute();

    assertEquals(5, storedProc.getInt(2));
}