java.sql.CallableStatement Java Examples

The following examples show how to use java.sql.CallableStatement. 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: SQLExecutor.java    From quetzal with 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 #2
Source File: Tds9Test.java    From jTDS with 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 File: OracleTableMetaDataProvider.java    From spring-analysis-note with 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 #4
Source File: BigDecimalHandler.java    From gemfirexd-oss with 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 #5
Source File: ProcedureTest.java    From gemfirexd-oss with 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 #6
Source File: JdbcLiveTest.java    From tutorials with 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 #7
Source File: DataDirectOracle9Dialect.java    From cacheonix-core with 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 #8
Source File: ProcedureTest.java    From gemfirexd-oss with 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 #9
Source File: CallableStatementWrapper.java    From Komondor with GNU General Public License v3.0 5 votes vote down vote up
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;
}
 
Example #10
Source File: SpliceAdminIT.java    From spliceengine with 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 File: CallableStatementWrapper.java    From r-course with 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 #12
Source File: CallableStatementWrapper.java    From FoxTelem with 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 File: CallableStatementWrapper.java    From lams with 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 #14
Source File: JDBCPrepareCallInterceptor.java    From skywalking with 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 #15
Source File: GfxdCallbacksTest.java    From gemfirexd-oss with 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 #16
Source File: GeneratedColumnsHelper.java    From spliceengine with 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 #17
Source File: CallableStatementTest.java    From jTDS with 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 #18
Source File: CallableStatementWrapper.java    From r-course with 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 #19
Source File: VacuumIT.java    From spliceengine with GNU Affero General Public License v3.0 5 votes vote down vote up
@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);
    }
}
 
Example #20
Source File: CallableStatementWrapper.java    From r-course with MIT License 5 votes vote down vote up
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;
}
 
Example #21
Source File: CachingLogicalConnection40.java    From gemfirexd-oss with 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 File: CallableStatementWrapper.java    From Komondor with GNU General Public License v3.0 5 votes vote down vote up
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;
}
 
Example #23
Source File: SqlStatisticsIT.java    From spliceengine with 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 File: ProcedureJdbcTemplate.java    From opscenter with Apache License 2.0 5 votes vote down vote up
/**
 * 执行存储过程
 * @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;
		}
	});
}
 
Example #25
Source File: CallableStatementWrapper.java    From lams with GNU General Public License v2.0 5 votes vote down vote up
@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;
}
 
Example #26
Source File: ProcedureTest.java    From spliceengine with 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 #27
Source File: CallableStatementWrapper.java    From lams with 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 #28
Source File: JDBC4CallableStatementWrapper.java    From Komondor with 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);
    }
}
 
Example #29
Source File: CallableStatementWrapper.java    From Komondor with 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 #30
Source File: DAProcedures.java    From gemfirexd-oss with 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;
}