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   Author: eugenp   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   Author: milesibastos   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   Author: gemxd   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   Author: Vip-Augus   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   Author: Quetzal-RDF   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   Author: gemxd   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
Source Project: jTDS   Author: milesibastos   File: CallableStatementTest.java    License: GNU Lesser General Public License v2.1 5 votes vote down vote up
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   Author: gemxd   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   Author: gemxd   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
Source Project: spliceengine   Author: splicemachine   File: SpliceAdminIT.java    License: GNU Affero General Public License v3.0 5 votes vote down vote up
@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   Author: apache   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
Source Project: FoxTelem   Author: ac2cz   File: CallableStatementWrapper.java    License: GNU General Public License v3.0 5 votes vote down vote up
@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   Author: joanby   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   Author: joanby   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   Author: gemxd   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   Author: gemxd   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   Author: apache   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   Author: ctripcorp   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
Source Project: cacheonix-core   Author: cacheonix   File: DB2Dialect.java    License: GNU Lesser General Public License v2.1 5 votes vote down vote up
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
Source Project: lams   Author: lamsfoundation   File: CallableStatementWrapper.java    License: GNU General Public License v2.0 5 votes vote down vote up
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   Author: gemxd   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   Author: gemxd   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
Source Project: spliceengine   Author: splicemachine   File: SqlStatisticsIT.java    License: GNU Affero General Public License v3.0 5 votes vote down vote up
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
Source Project: Komondor   Author: wn-upf   File: CallableStatementWrapper.java    License: GNU General Public License v3.0 5 votes vote down vote up
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   Author: gemxd   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
Source Project: lams   Author: lamsfoundation   File: CallableStatementWrapper.java    License: GNU General Public License v2.0 5 votes vote down vote up
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
Source Project: lams   Author: lamsfoundation   File: CallableStatementWrapper.java    License: GNU General Public License v2.0 5 votes vote down vote up
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
Source Project: spliceengine   Author: splicemachine   File: GeneratedColumnsHelper.java    License: GNU Affero General Public License v3.0 5 votes vote down vote up
/**
 * 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
Source Project: spliceengine   Author: splicemachine   File: ProcedureTest.java    License: GNU Affero General Public License v3.0 5 votes vote down vote up
/**
 * 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
Source Project: Komondor   Author: wn-upf   File: JDBC4CallableStatementWrapper.java    License: GNU General Public License v3.0 5 votes vote down vote up
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);
    }
}