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