Java Code Examples for java.sql.CallableStatement

@Test
public void whenCallProcedure_thenCorrect() {

    try {
        String preparedSql = "{call insertEmployee(?,?,?,?)}";
        CallableStatement cstmt = con.prepareCall(preparedSql);
        cstmt.setString(2, "ana");
        cstmt.setString(3, "tester");
        cstmt.setDouble(4, 2000);
        cstmt.registerOutParameter(1, Types.INTEGER);
        cstmt.execute();
        int new_id = cstmt.getInt(1);
        assertTrue(new_id > 0);
    } catch (SQLException exc) {
        LOG.error("Procedure incorrect or does not exist!");
    }
}
 

/**
 * SQL 2005 allows varbinary(max) as the output parameter of a stored
 * procedure. Test this functionality now.
 */
public void testVarbinaryMaxOutput() throws Exception
{
   if( supportsTDS9() )
   {
      Statement stmt = con.createStatement();
      stmt.execute( "CREATE PROC #sp_test @in varbinary(max), @out varbinary(max) output as set @out = @in" );
      StringBuffer buf = new StringBuffer( 5000 );
      buf.append( '<' );
      for( int i = 0; i < 8000; i++ )
      {
         buf.append( 'X' );
      }
      buf.append( '>' );
      CallableStatement cstmt = con.prepareCall( "{call #sp_test(?,?)}" );
      cstmt.setBytes( 1, buf.toString().getBytes() );
      cstmt.registerOutParameter( 2, Types.LONGVARBINARY );
      cstmt.execute();
      assertTrue( buf.toString().equals( new String( cstmt.getBytes( 2 ) ) ) );
      cstmt.close();
      stmt.close();
   }
}
 

/** This method is a wrapper for the CallableStatement method getBigDecimal(int parameterIndex).
 * The wrapper method needs the parameterType as an input since ParameterMetaData is not available in JSR169.
 * 
 * @param cs CallableStatement 
 * @param parameterIndex Parameter Index
 * @param parameterType Parameter Type
 * @return String value of getXXX(parameterIndex)method on the CallableStatement
 * @throws SQLException
 */
public static String getBigDecimalString(CallableStatement cs, int parameterIndex, int parameterType) throws SQLException{
	String bigDecimalString = null;
	
	switch(representation){
		case BIGDECIMAL_REPRESENTATION:
			//Call toString() only for non-null values, else return null
			if(cs.getBigDecimal(parameterIndex) != null)
				bigDecimalString = cs.getBigDecimal(parameterIndex).toString();
			break;
		case STRING_REPRESENTATION:
			bigDecimalString = cs.getString(parameterIndex);
			if((bigDecimalString != null) && !canConvertToDecimal(parameterType))
				throw new SQLException("Invalid data conversion. Method not called.");
			break;
		default:	
			new Exception("Failed: Invalid Big Decimal representation").printStackTrace();
	}
	return bigDecimalString;
}
 

@Nullable
private static String lookupDefaultSchema(DatabaseMetaData databaseMetaData) {
	try {
		CallableStatement cstmt = null;
		try {
			Connection con = databaseMetaData.getConnection();
			if (con == null) {
				logger.debug("Cannot check default schema - no Connection from DatabaseMetaData");
				return null;
			}
			cstmt = con.prepareCall("{? = call sys_context('USERENV', 'CURRENT_SCHEMA')}");
			cstmt.registerOutParameter(1, Types.VARCHAR);
			cstmt.execute();
			return cstmt.getString(1);
		}
		finally {
			if (cstmt != null) {
				cstmt.close();
			}
		}
	}
	catch (SQLException ex) {
		logger.debug("Exception encountered during default schema lookup", ex);
		return null;
	}
}
 

public static boolean executeCall(Connection conn, String sql,
						Object... params) {
	
	CallableStatement stmt = null;
	
	try {
		stmt = conn.prepareCall(sql);
		int i = 1;
		for (Object o : params) {
			stmt.setObject(i, o);
			i++;
		}
		
		return stmt.execute();
	} catch (SQLException e) {
		throw new SQLExceptionWrapper(e);
	} finally {
		closeSQLObjects(stmt, null);
	}
}
 

private void closeCurrentGetMoreResults(CallableStatement cs, ResultSet[] allRS) throws SQLException {
    cs.execute();

    for (int i = 0; i < 5; i++)
    {
        allRS[i] = cs.getResultSet();
        assertSame(cs, allRS[i].getStatement());
        allRS[i].next();
        assertEquals(2+i, allRS[i].getInt(1));

        if (i < 4)
            assertTrue(cs.getMoreResults(Statement.CLOSE_CURRENT_RESULT));
        else
            assertFalse(cs.getMoreResults(Statement.CLOSE_CURRENT_RESULT));
    }

    // verify resultSets are closed
    for (int i = 0; i < 5; i++)
        JDBC.assertClosed(allRS[i]);
}
 

public void testCallableStatementExec2() throws Exception {
    CallableStatement cstmt = con.prepareCall("EXEC sp_who");

    ResultSet rs = cstmt.executeQuery();
    dump( rs,SILENT );

    rs.close();
    cstmt.close();
}
 

public static void addLoader(String schemaName, String tableName,
    String functionStr, String initInfoStr) throws SQLException {
  Connection conn = getConnection();
  CallableStatement cs = conn
      .prepareCall("CALL SYS.ATTACH_LOADER(?,?,?,?)");
  cs.setString(1, schemaName);
  cs.setString(2, tableName);
  cs.setString(3, functionStr);
  cs.setString(4, initInfoStr);
  cs.execute();
}
 

public void testChangeIsoLevelCallableStatementJDBC() throws SQLException {
    Connection c = getConnection();
    CallableStatement cs = prepareCall("CALL SET_ISOLATION_JDBC(?)");
    for (int i = 0; i < 4; ++i) {
        cs.setInt(1, isoLevels[i].getIsoLevel());
        cs.execute();
        verifyCachedIsolation(c);
    }
    cs.close();
}
 

@Test
public void testGetLoggers() throws Exception {
    CallableStatement cs = methodWatcher.prepareCall("call SYSCS_UTIL.SYSCS_GET_LOGGERS()");
    ResultSet rs = cs.executeQuery();
    TestUtils.FormattedResult fr = TestUtils.FormattedResult.ResultFactory.convert("call SYSCS_UTIL.SYSCS_GET_LOGGERS()", rs);
    System.out.println(fr.toString());
    assertTrue(fr.size() >= 80);
    DbUtils.closeQuietly(rs);
}
 

@Override
public Object afterMethod(EnhancedInstance objInst, Method method, Object[] allArguments, Class<?>[] argumentsTypes,
    Object ret) throws Throwable {
    if (objInst.getSkyWalkingDynamicField() == null) {
        return ret;
    }
    return new SWCallableStatement((Connection) objInst, (CallableStatement) ret, (ConnectionInfo) objInst.getSkyWalkingDynamicField(), (String) allArguments[0]);
}
 

@Override
public void setClob(String parameterName, Reader reader, long length) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            ((CallableStatement) this.wrappedStmt).setClob(parameterName, reader, length);
        } else {
            throw SQLError.createSQLException(Messages.getString("Statement.AlreadyClosed"), MysqlErrorNumbers.SQL_STATE_GENERAL_ERROR,
                    this.exceptionInterceptor);
        }
    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }
}
 

public void setURL(String parameterName, URL val) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            ((CallableStatement) this.wrappedStmt).setURL(parameterName, val);
        } else {
            throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);
        }
    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }
}
 

public Ref getRef(int parameterIndex) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            return ((CallableStatement) this.wrappedStmt).getRef(parameterIndex);
        }
        throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);

    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }

    return null;
}
 

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

}
 

/**
 * Helper method for testing getExportedKeys - calls the ODBC procedure
 * @throws SQLException
 */
private ResultSet getExportedKeysODBC(
        String catalog, String schema, String table) throws SQLException
{
    CallableStatement cs = prepareCall("CALL SYSIBM.SQLFOREIGNKEYS(" +
        "?, ?, ?, null, null, null, 'EXPORTEDKEY=1;DATATYPE=''ODBC''')");
    cs.setString(1, catalog);
    cs.setString(2, schema);
    cs.setString(3, table);
    cs.execute();
    return cs.getResultSet();
}
 

private void checkNotPreparedOrCallable(String s) throws SQLException {
  if (this instanceof PreparedStatement
      || this instanceof CallableStatement) {
    throw AvaticaConnection.HELPER.createException("Cannot call " + s
        + " on prepared or callable statement");
  }
}
 

private void registerOutParameters(CallableStatement statement, StatementParameters parameters) throws Exception {
	for (StatementParameter parameter: parameters.values()) {
		if(parameter.isOutParameter()) {
			if (parameter.getName() == null || parameter.isTSQLParameter())
				statement.registerOutParameter(parameter.getIndex(), parameter.getSqlType());
			else
				statement.registerOutParameter(parameter.getName(), parameter.getSqlType());
		}
	}
}
 

public ResultSet getResultSet(CallableStatement ps) throws SQLException {
	boolean isResultSet = ps.execute();
	// This assumes you will want to ignore any update counts
	while (!isResultSet && ps.getUpdateCount() != -1) {
	    isResultSet = ps.getMoreResults();
	}
	ResultSet rs = ps.getResultSet();
	// You may still have other ResultSets or update counts left to process here
	// but you can't do it now or the ResultSet you just got will be closed
	return rs;
}
 

public URL getURL(int parameterIndex) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            return ((CallableStatement) this.wrappedStmt).getURL(parameterIndex);
        }
        throw SQLError.createSQLException(Messages.getString("Statement.AlreadyClosed"), MysqlErrorNumbers.SQL_STATE_GENERAL_ERROR,
                this.exceptionInterceptor);

    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }

    return null;
}
 

public synchronized CallableStatement prepareCall(String sql,
                                                  int resultSetType,
                                                  int resultSetConcurrency,
                                                  int resultSetHoldability)
        throws SQLException {
    checkForNullPhysicalConnection();
    return cacheInteractor.prepareCall(
            sql, resultSetType, resultSetConcurrency, resultSetHoldability);
}
 

/**
 * Tests that <code>CallableStatement.executeQuery()</code>
 * succeeds when one result set is returned from a stored
 * procedure.
 * @exception SQLException if a database error occurs
 */
public void testExecuteQueryWithOneDynamicResultSet_callable()
    throws SQLException
{
    CallableStatement cs =
        prepareCall("CALL RETRIEVE_DYNAMIC_RESULTS(?)");
    cs.setInt(1, 1);
    ResultSet rs = cs.executeQuery();
    assertNotNull("executeQuery() returned null.", rs);
    assertSame(cs, rs.getStatement());
    JDBC.assertDrainResultsHasData(rs);
}
 

private int getResultSetCountFromShowIndexes(String schemaName, String tableName) throws Exception {
	if (schemaName == null) {
		schemaName = "null";
	} else {
		schemaName = "'" + schemaName + "'";
	}
	if (tableName == null) {
		tableName = "null";
	} else {
		tableName = "'" + tableName + "'";
	}
    CallableStatement cs = methodWatcher.prepareCall(format("call SYSIBM.SQLSTATISTICS(null, %s, %s, 1, 1, null)", schemaName, tableName), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = cs.executeQuery();
    int count = 0;
    LOG.trace(format("Show Indexes Args: schema = %s, table = %s", schemaName, tableName));
    while (rs.next()) {
        String schema = rs.getString("TABLE_SCHEM");
        String table = rs.getString("TABLE_NAME");
        String index = rs.getString("INDEX_NAME");
        String column = rs.getString("COLUMN_NAME");
        int position = rs.getInt("ORDINAL_POSITION");
        LOG.trace(format("Show Indexes Results: schema = %s, table = %s, index = %s, column = %s, position = %s", schema, table, index, column, position));
        count++;
    }
    LOG.trace(format("Show Indexes Results: count = %s", count));
    DbUtils.closeQuietly(rs);
    return count;
}
 

public String getString(String parameterName) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            return ((CallableStatement) this.wrappedStmt).getString(parameterName);
        }
        throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);

    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }
    return null;
}
 

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;
}
 

public void setClob(String parameterName, Reader reader) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            ((CallableStatement) this.wrappedStmt).setClob(parameterName, reader);
        } else {
            throw SQLError.createSQLException(Messages.getString("Statement.AlreadyClosed"), MysqlErrorNumbers.SQL_STATE_GENERAL_ERROR,
                    this.exceptionInterceptor);
        }
    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }
}
 

public void setCharacterStream(String parameterName, Reader reader, int length) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            ((CallableStatement) this.wrappedStmt).setCharacterStream(parameterName, reader, length);
        } else {
            throw SQLError.createSQLException(Messages.getString("Statement.AlreadyClosed"), MysqlErrorNumbers.SQL_STATE_GENERAL_ERROR,
                    this.exceptionInterceptor);
        }
    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }
}
 

/**
 * Prepare a callable statement and report its sql text.
 */
protected CallableStatement   chattyPrepareCall( Connection conn, String text )
    throws SQLException
{
    println( "Preparing callable statement:\n\t" + text );
    
    return conn.prepareCall( text );
}
 

/**
 * Tests that <code>CallableStatement.executeQuery()</code> fails
 * when no result sets are returned.
 * @exception SQLException if a database error occurs
 */
public void testExecuteQueryWithNoDynamicResultSets_callable()
    throws SQLException
{
    CallableStatement cs =
        prepareCall("CALL RETRIEVE_DYNAMIC_RESULTS(?)");
    cs.setInt(1, 0);
    try {
        cs.executeQuery();
        fail("executeQuery() didn't fail.");
    } catch (SQLException sqle) {
        assertNoResultSetFromExecuteQuery(sqle);
    }
}
 

public void setNClob(String parameterName, Reader reader, long length) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            ((CallableStatement) this.wrappedStmt).setNClob(parameterName, reader, length);
        } else {
            throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);
        }
    } catch (SQLException sqlEx) {
        checkAndFireConnectionError(sqlEx);
    }
}