java.sql.CallableStatement Java Examples

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

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

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

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

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

@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!");
    }
}
 

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

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

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

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

    return 0;
}
 

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

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

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

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

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

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

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

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

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

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

@Test
public void testVacuumDoesNotDeleteConcurrentCreatedTable() throws Exception {
    Connection connection = spliceClassWatcher.getOrCreateConnection();
    connection.createStatement().execute(String.format("drop table %s.b if exists", CLASS_NAME));
    connection.commit();

    boolean autoCommit = connection.getAutoCommit();
    connection.setAutoCommit(false);
    try {
        try (ResultSet rs = connection.createStatement().executeQuery("select * from sys.systables")) {
            assertTrue(rs.next());
        }

        try (Connection connection2 = spliceClassWatcher.createConnection()) {
            connection2.createStatement().execute(String.format("create table %s.b (i int)", CLASS_NAME));
            long[] conglomerates = SpliceAdmin.getConglomNumbers(connection2, CLASS_NAME, "B");


            try (Admin admin = ConnectionFactory.createConnection(new Configuration()).getAdmin()) {
                try (CallableStatement callableStatement = connection.prepareCall("call SYSCS_UTIL.VACUUM()")) {
                    callableStatement.execute();
                }
                for (long congId : conglomerates) {
                    // make sure the table exists in HBase and hasn't been dropped by VACUUM
                    admin.getTableDescriptor(TableName.valueOf("splice:" + congId));
                }
            }
        }
    } finally {
        connection.commit();
        connection.setAutoCommit(autoCommit);
    }
}
 

public Object getObject(int parameterIndex, Map<String, Class<?>> typeMap) throws SQLException {
    try {
        if (this.wrappedStmt != null) {
            return ((CallableStatement) this.wrappedStmt).getObject(parameterIndex, typeMap);
        }
        throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.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);
}
 

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

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

    return null;
}
 

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

/**
 * 执行存储过程
 * @param psc
 * @param rsStatus
 * @param outParam
 * @return T
 * @throws DataAccessException
 */
@Nullable
private <T> T execProcedure(CallableStatementCreator csc,boolean rsStatus,String[] outParam)	throws DataAccessException {
	return namedParameterJdbcTemplate.getJdbcTemplate().execute(csc, new CallableStatementCallback<T>() {
		@SuppressWarnings("unchecked")
		@Override
		public T doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
			Map<String,Object> result = new HashMap<String, Object>();
			Map<String,Object> message = new HashMap<String, Object>();
			ResultSet rs = null;
			boolean sqlExecuteStatus = false;
			int outParamLen = outParam.length;
			try {
				if(rsStatus) {
					rs = cs.executeQuery();
					result.put("rows", createRows(cs, rs));
					sqlExecuteStatus = true;
				}else {
					sqlExecuteStatus = cs.execute();
				}
				for (int i = 0; i <  outParamLen; i++) {
					message.put(outParam[i], cs.getString(outParam[i]));
				}
				message.put("sqlExecuteStatus", sqlExecuteStatus);
				result.put("message", message);
			} catch (IOException e) {
			}finally {
				JdbcUtils.closeResultSet(rs);
			}
			return (T) result;
		}
	});
}
 

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

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

    return null;
}
 

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

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