Java Code Examples for java.sql.CallableStatement

The following examples show how to use java.sql.CallableStatement. These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source Project: tutorials   Source File: JdbcLiveTest.java    License: MIT License 6 votes vote down vote up
@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!");
    }
}
 
Example 2
Source Project: jTDS   Source File: Tds9Test.java    License: GNU Lesser General Public License v2.1 6 votes vote down vote up
/**
 * 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();
   }
}
 
Example 3
Source Project: gemfirexd-oss   Source File: BigDecimalHandler.java    License: Apache License 2.0 6 votes vote down vote up
/** 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;
}
 
Example 4
Source Project: spring-analysis-note   Source File: OracleTableMetaDataProvider.java    License: MIT License 6 votes vote down vote up
@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;
	}
}
 
Example 5
Source Project: quetzal   Source File: SQLExecutor.java    License: Eclipse Public License 2.0 6 votes vote down vote up
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);
	}
}
 
Example 6
Source Project: gemfirexd-oss   Source File: ProcedureTest.java    License: Apache License 2.0 6 votes vote down vote up
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]);
}
 
Example 7
public void testCallableStatementExec2() throws Exception {
    CallableStatement cstmt = con.prepareCall("EXEC sp_who");

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

    rs.close();
    cstmt.close();
}
 
Example 8
Source Project: gemfirexd-oss   Source File: GfxdCallbacksTest.java    License: Apache License 2.0 5 votes vote down vote up
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();
}
 
Example 9
Source Project: gemfirexd-oss   Source File: CacheSessionDataTest.java    License: Apache License 2.0 5 votes vote down vote up
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();
}
 
Example 10
@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);
}
 
Example 11
Source Project: skywalking   Source File: JDBCPrepareCallInterceptor.java    License: Apache License 2.0 5 votes vote down vote up
@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]);
}
 
Example 12
@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);
    }
}
 
Example 13
Source Project: r-course   Source File: CallableStatementWrapper.java    License: MIT License 5 votes vote down vote up
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);
    }
}
 
Example 14
Source Project: r-course   Source File: CallableStatementWrapper.java    License: MIT License 5 votes vote down vote up
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;
}
 
Example 15
Source Project: gemfirexd-oss   Source File: ProcedureTestDUnit.java    License: Apache License 2.0 5 votes vote down vote up
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));
    
  

}
 
Example 16
Source Project: gemfirexd-oss   Source File: DatabaseMetaDataTest.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 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();
}
 
Example 17
Source Project: calcite-avatica   Source File: AvaticaStatement.java    License: Apache License 2.0 5 votes vote down vote up
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");
  }
}
 
Example 18
Source Project: dal   Source File: DalStatementCreator.java    License: Apache License 2.0 5 votes vote down vote up
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());
		}
	}
}
 
Example 19
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;
}
 
Example 20
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;
}
 
Example 21
Source Project: gemfirexd-oss   Source File: CachingLogicalConnection40.java    License: Apache License 2.0 5 votes vote down vote up
public synchronized CallableStatement prepareCall(String sql,
                                                  int resultSetType,
                                                  int resultSetConcurrency,
                                                  int resultSetHoldability)
        throws SQLException {
    checkForNullPhysicalConnection();
    return cacheInteractor.prepareCall(
            sql, resultSetType, resultSetConcurrency, resultSetHoldability);
}
 
Example 22
Source Project: gemfirexd-oss   Source File: ProcedureTest.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 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);
}
 
Example 23
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;
}
 
Example 24
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;
}
 
Example 25
Source Project: gemfirexd-oss   Source File: DAProcedures.java    License: 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 26
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);
    }
}
 
Example 27
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);
    }
}
 
Example 28
/**
 * 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 );
}
 
Example 29
/**
 * 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);
    }
}
 
Example 30
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);
    }
}