List of usage examples for java.sql CallableStatement setFloat
void setFloat(String parameterName, float x) throws SQLException;
float value. From source file:it.greenvulcano.gvesb.datahandling.dbo.DBOCallSP.java
/** * @param cs// ww w . j av a 2s . c o m * @param num * @throws SQLException */ private void setFloat(CallableStatement cs, float num) throws SQLException { if (useName) { cs.setFloat(currName, num); } else { cs.setFloat(colIdx, num); } }
From source file:com.oracle.tutorial.jdbc.StoredProcedureMySQLSample.java
public void runStoredProcedures(String coffeeNameArg, float maximumPercentageArg, float newPriceArg) throws SQLException { CallableStatement cs = null; try {//from w w w. j a va 2 s . c om System.out.println("\nCalling the procedure GET_SUPPLIER_OF_COFFEE"); cs = this.con.prepareCall("{call GET_SUPPLIER_OF_COFFEE(?, ?)}"); cs.setString(1, coffeeNameArg); cs.registerOutParameter(2, Types.VARCHAR); cs.executeQuery(); String supplierName = cs.getString(2); if (supplierName != null) { System.out.println("\nSupplier of the coffee " + coffeeNameArg + ": " + supplierName); } else { System.out.println("\nUnable to find the coffee " + coffeeNameArg); } System.out.println("\nCalling the procedure SHOW_SUPPLIERS"); cs = this.con.prepareCall("{call SHOW_SUPPLIERS}"); ResultSet rs = cs.executeQuery(); while (rs.next()) { String supplier = rs.getString("SUP_NAME"); String coffee = rs.getString("COF_NAME"); System.out.println(supplier + ": " + coffee); } System.out.println("\nContents of COFFEES table before calling RAISE_PRICE:"); CoffeesTable.viewTable(this.con); System.out.println("\nCalling the procedure RAISE_PRICE"); cs = this.con.prepareCall("{call RAISE_PRICE(?,?,?)}"); cs.setString(1, coffeeNameArg); cs.setFloat(2, maximumPercentageArg); cs.registerOutParameter(3, Types.NUMERIC); cs.setFloat(3, newPriceArg); cs.execute(); System.out.println("\nValue of newPrice after calling RAISE_PRICE: " + cs.getFloat(3)); System.out.println("\nContents of COFFEES table after calling RAISE_PRICE:"); CoffeesTable.viewTable(this.con); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (cs != null) { cs.close(); } } }
From source file:nl.nn.adapterframework.jdbc.JdbcQuerySenderBase.java
protected String executePackageQuery(Connection connection, PreparedStatement statement, String message) throws SenderException, JdbcException, IOException, JMSException { Object[] paramArray = new Object[10]; String callMessage = fillParamArray(paramArray, message); ResultSet resultset = null;/*ww w . ja v a2 s . c om*/ try { CallableStatement pstmt = connection.prepareCall(callMessage); if (getMaxRows() > 0) { pstmt.setMaxRows(getMaxRows() + (getStartRow() > 1 ? getStartRow() - 1 : 0)); } int var = 1; for (int i = 0; i < paramArray.length; i++) { if (paramArray[i] instanceof Timestamp) { pstmt.setTimestamp(var, (Timestamp) paramArray[i]); var++; } if (paramArray[i] instanceof java.sql.Date) { pstmt.setDate(var, (java.sql.Date) paramArray[i]); var++; } if (paramArray[i] instanceof String) { pstmt.setString(var, (String) paramArray[i]); var++; } if (paramArray[i] instanceof Integer) { int x = Integer.parseInt(paramArray[i].toString()); pstmt.setInt(var, x); var++; } if (paramArray[i] instanceof Float) { float x = Float.parseFloat(paramArray[i].toString()); pstmt.setFloat(var, x); var++; } } if (message.indexOf('?') != -1) { pstmt.registerOutParameter(var, Types.CLOB); // make sure enough space is available for result... } if ("xml".equalsIgnoreCase(getPackageContent())) { log.debug(getLogPrefix() + "executing a package SQL command"); pstmt.executeUpdate(); String pUitvoer = pstmt.getString(var); return pUitvoer; } log.debug(getLogPrefix() + "executing a package SQL command"); int numRowsAffected = pstmt.executeUpdate(); if (StringUtils.isNotEmpty(getResultQuery())) { Statement resStmt = null; try { resStmt = connection.createStatement(); log.debug("obtaining result from [" + getResultQuery() + "]"); ResultSet rs = resStmt.executeQuery(getResultQuery()); return getResult(rs); } finally { if (resStmt != null) { resStmt.close(); } } } if (getColumnsReturnedList() != null) { return getResult(getReturnedColumns(getColumnsReturnedList(), statement)); } if (isScalar()) { return numRowsAffected + ""; } return "<result><rowsupdated>" + numRowsAffected + "</rowsupdated></result>"; } catch (SQLException sqle) { throw new SenderException(getLogPrefix() + "got exception executing a package SQL command", sqle); } finally { try { if (resultset != null) { resultset.close(); } } catch (SQLException e) { log.warn(new SenderException(getLogPrefix() + "got exception closing resultset", e)); } } }
From source file:org.brucalipto.sqlutil.DB2SQLManager.java
/** * Method useful for using STORED PROCEDURE * @param spib The {@link SPInputBean} bean containing data to execute the stored procedure * @return The {@link SPOutputBean} containing returned values *//*from www . ja va 2 s .com*/ public SPOutputBean executeSP(final SPInputBean spib) throws SQLException { Connection conn = null; CallableStatement call = null; ResultSet resultSet = null; final String procedureName = spib.spName; SPParameter[] inputParameters = spib.inputParams; int[] outputParameters = spib.outputParams; final int inputParametersSize = inputParameters.length; final int outputParametersSize = outputParameters.length; final StringBuffer spName = new StringBuffer("call ").append(procedureName).append('('); int totalParameters = inputParametersSize + outputParametersSize; for (int i = 0; i < totalParameters; i++) { if (i != totalParameters - 1) { spName.append("?,"); } else { spName.append('?'); } } spName.append(")"); try { if (this.dataSource != null) { conn = this.dataSource.getConnection(); } else { conn = this.connection; } call = conn.prepareCall(spName.toString()); for (int i = 0; i < inputParametersSize; i++) { final SPParameter inputParam = inputParameters[i]; final int sqlType = inputParam.sqlType; final Object inputParamValue = inputParam.value; log.debug((i + 1) + ") Setting input value: " + inputParam); if (inputParamValue == null) { call.setNull(i + 1, sqlType); continue; } switch (sqlType) { case Types.VARCHAR: call.setString(i + 1, (String) inputParamValue); break; case Types.INTEGER: if (inputParamValue instanceof Integer) { call.setInt(i + 1, ((Integer) inputParamValue).intValue()); } else if (inputParamValue instanceof Long) { call.setLong(i + 1, ((Long) inputParamValue).longValue()); } break; case Types.DATE: call.setDate(i + 1, (Date) inputParamValue); break; case Types.BOOLEAN: call.setBoolean(i + 1, ((Boolean) inputParamValue).booleanValue()); break; case Types.CHAR: call.setString(i + 1, ((Character) inputParamValue).toString()); break; case Types.DOUBLE: call.setDouble(i + 1, ((Double) inputParamValue).doubleValue()); break; case Types.FLOAT: call.setFloat(i + 1, ((Float) inputParamValue).floatValue()); break; case Types.TIMESTAMP: call.setTimestamp(i + 1, (Timestamp) inputParamValue); break; default: call.setObject(i + 1, inputParamValue); break; } } for (int i = 0; i < outputParametersSize; i++) { int sqlType = outputParameters[i]; log.debug((i + 1) + ") Registering output type 'Types." + SQLUtilTypes.SQL_TYPES.get(Integer.valueOf("" + sqlType)) + "'"); call.registerOutParameter(inputParametersSize + i + 1, sqlType); } log.debug("Going to call: '" + procedureName + "'"); long elapsedTime = System.currentTimeMillis(); boolean hasResultSet = call.execute(); log.debug("SP '" + procedureName + "' executed in " + (System.currentTimeMillis() - elapsedTime) + "millis"); if (hasResultSet) { log.debug("This SP is going to return also a resultSet"); } final SPOutputBean output = new SPOutputBean(); for (int i = 0; i < outputParametersSize; i++) { int sqlType = outputParameters[i]; log.debug((i + 1) + ") Getting output type 'Types." + SQLUtilTypes.SQL_TYPES.get(Integer.valueOf("" + sqlType)) + "'"); final Object spResult = call.getObject(inputParametersSize + i + 1); SPParameter outParam = new SPParameter(sqlType, spResult); output.addResult(outParam); } if (hasResultSet) { RowSetDynaClass rowSetDynaClass = new RowSetDynaClass(call.getResultSet(), false); if (log.isDebugEnabled()) { log.debug("Going to return a RowSetDynaClass with following properties:"); DynaProperty[] properties = rowSetDynaClass.getDynaProperties(); for (int i = 0; i < properties.length; i++) { log.debug("Name: '" + properties[i].getName() + "'; Type: '" + properties[i].getType().getName() + "'"); } } SPParameter outParam = new SPParameter(Types.JAVA_OBJECT, rowSetDynaClass); output.addResult(outParam); } return output; } finally { closeResources(resultSet, call, conn); } }
From source file:org.brucalipto.sqlutil.OracleSQLManager.java
/** * Method useful for using STORED PROCEDURE * @param spib The {@link SPInputBean} bean containing data to execute the stored procedure * @return The {@link SPOutputBean} containing returned values */// ww w . j ava 2s . co m public SPOutputBean executeSP(final SPInputBean spib) throws SQLException { Connection conn = null; CallableStatement call = null; ResultSet resultSet = null; final String procedureName = spib.spName; SPParameter[] inputParameters = spib.inputParams; int[] outputParameters = spib.outputParams; final int inputParametersSize = inputParameters.length; final int outputParametersSize = outputParameters.length; final StringBuffer spName = new StringBuffer("{ call ").append(procedureName).append('('); int totalParameters = inputParametersSize + outputParametersSize; for (int i = 0; i < totalParameters; i++) { if (i != totalParameters - 1) { spName.append("?,"); } else { spName.append('?'); } } spName.append(") }"); log.debug("Going to call: '" + spName + "'"); try { conn = this.dataSource.getConnection(); call = conn.prepareCall(spName.toString()); for (int i = 0; i < inputParametersSize; i++) { final SPParameter inputParam = inputParameters[i]; final int sqlType = inputParam.sqlType; final Object inputParamValue = inputParam.value; log.debug((i + 1) + ") Setting input value 'Types." + SQLUtilTypes.SQL_TYPES.get(Integer.valueOf("" + sqlType)) + "'-'" + inputParamValue + "'"); if (inputParamValue == null) { call.setNull(i + 1, sqlType); continue; } switch (sqlType) { case Types.VARCHAR: call.setString(i + 1, (String) inputParamValue); break; case Types.INTEGER: if (inputParamValue instanceof Integer) { call.setInt(i + 1, ((Integer) inputParamValue).intValue()); } else if (inputParamValue instanceof Long) { call.setLong(i + 1, ((Long) inputParamValue).longValue()); } break; case Types.DATE: call.setDate(i + 1, (Date) inputParamValue); break; case Types.BOOLEAN: call.setBoolean(i + 1, ((Boolean) inputParamValue).booleanValue()); break; case Types.CHAR: call.setString(i + 1, ((Character) inputParamValue).toString()); break; case Types.DOUBLE: call.setDouble(i + 1, ((Double) inputParamValue).doubleValue()); break; case Types.FLOAT: call.setFloat(i + 1, ((Float) inputParamValue).floatValue()); break; case Types.TIMESTAMP: call.setTimestamp(i + 1, (Timestamp) inputParamValue); break; default: call.setObject(i + 1, inputParamValue); break; } } for (int i = 0; i < outputParametersSize; i++) { int sqlType = outputParameters[i]; log.debug((i + 1) + ") Registering output type 'Types." + SQLUtilTypes.SQL_TYPES.get(Integer.valueOf("" + sqlType)) + "'"); call.registerOutParameter(inputParametersSize + i + 1, sqlType); } call.execute(); final SPOutputBean output = new SPOutputBean(); for (int i = 0; i < outputParametersSize; i++) { int sqlType = outputParameters[i]; log.debug((i + 1) + ") Getting output type 'Types." + SQLUtilTypes.SQL_TYPES.get(Integer.valueOf("" + sqlType)) + "'"); final Object spResult = call.getObject(inputParametersSize + i + 1); SPParameter outParam = null; if (sqlType == SQLUtilTypes.CURSOR) { resultSet = (ResultSet) spResult; RowSetDynaClass rowSetDynaClass = new RowSetDynaClass(resultSet, false); if (log.isDebugEnabled()) { log.debug("Going to return a RowSetDynaClass with following properties:"); DynaProperty[] properties = rowSetDynaClass.getDynaProperties(); for (int j = 0; j < properties.length; j++) { log.debug("Name: '" + properties[j].getName() + "'; Type: '" + properties[j].getType().getName() + "'"); } } outParam = new SPParameter(sqlType, rowSetDynaClass); } else { outParam = new SPParameter(sqlType, spResult); } output.addResult(outParam); } return output; } catch (SQLException sqle) { log.error("Caught SQLException", sqle); } finally { closeResources(resultSet, call, conn); } return null; }
From source file:org.executequery.databasemediators.spi.DefaultStatementExecutor.java
/** <p>Executes the specified procedure. * * @param the SQL procedure to execute * @return the query result//w w w. j a va2s. c om */ public SqlStatementResult execute(DatabaseExecutable databaseExecutable) throws SQLException { if (!prepared()) { return statementResult; } ProcedureParameter[] param = databaseExecutable.getParametersArray(); Arrays.sort(param, new ProcedureParameterSorter()); String procQuery = null; boolean hasOut = false; boolean hasParameters = (param != null && param.length > 0); List<ProcedureParameter> outs = null; List<ProcedureParameter> ins = null; if (hasParameters) { // split the params into ins and outs outs = new ArrayList<ProcedureParameter>(); ins = new ArrayList<ProcedureParameter>(); int type = -1; for (int i = 0; i < param.length; i++) { type = param[i].getType(); if (type == DatabaseMetaData.procedureColumnIn || type == DatabaseMetaData.procedureColumnInOut) { // add to the ins list ins.add(param[i]); } else if (type == DatabaseMetaData.procedureColumnOut || type == DatabaseMetaData.procedureColumnResult || type == DatabaseMetaData.procedureColumnReturn || type == DatabaseMetaData.procedureColumnUnknown || type == DatabaseMetaData.procedureColumnInOut) { // add to the outs list outs.add(param[i]); } } char QUESTION_MARK = '?'; String COMMA = ", "; // init the string buffer StringBuilder sb = new StringBuilder("{ "); if (!outs.isEmpty()) { // build the out params place holders for (int i = 0, n = outs.size(); i < n; i++) { sb.append(QUESTION_MARK); if (i < n - 1) { sb.append(COMMA); } } sb.append(" = "); } sb.append(" call "); if (databaseExecutable.supportCatalogOrSchemaInFunctionOrProcedureCalls()) { String namePrefix = null; if (databaseExecutable.supportCatalogInFunctionOrProcedureCalls()) { namePrefix = databaseExecutable.getCatalogName(); } if (databaseExecutable.supportSchemaInFunctionOrProcedureCalls()) { namePrefix = databaseExecutable.getSchemaName(); } if (namePrefix != null) { sb.append(namePrefix).append('.'); } } sb.append(databaseExecutable.getName()).append("( "); // build the ins params place holders for (int i = 0, n = ins.size(); i < n; i++) { sb.append(QUESTION_MARK); if (i < n - 1) { sb.append(COMMA); } } sb.append(" ) }"); // determine if we have out params hasOut = !(outs.isEmpty()); procQuery = sb.toString(); } else { StringBuilder sb = new StringBuilder(); sb.append("{ call "); if (databaseExecutable.getSchemaName() != null) { sb.append(databaseExecutable.getSchemaName()).append('.'); } sb.append(databaseExecutable.getName()).append("( ) }"); procQuery = sb.toString(); } //Log.debug(procQuery); // null value literal String NULL = "null"; // clear any warnings conn.clearWarnings(); Log.info("Executing: " + procQuery); CallableStatement cstmnt = null; try { // prepare the statement cstmnt = conn.prepareCall(procQuery); stmnt = cstmnt; } catch (SQLException e) { handleException(e); statementResult.setSqlException(e); return statementResult; } // check if we are passing parameters if (hasParameters) { // the parameter index counter int index = 1; // the java.sql.Type value int dataType = -1; // the parameter input value String value = null; // register the out params for (int i = 0, n = outs.size(); i < n; i++) { //Log.debug("setting out at index: " + index); cstmnt.registerOutParameter(index, outs.get(i).getDataType()); index++; } try { // register the in params for (int i = 0, n = ins.size(); i < n; i++) { ProcedureParameter procedureParameter = ins.get(i); value = procedureParameter.getValue(); dataType = procedureParameter.getDataType(); // try infer a type if OTHER if (dataType == Types.OTHER) { // checking only for bit/bool for now if (isTrueFalse(value)) { dataType = Types.BOOLEAN; } else if (isBit(value)) { dataType = Types.BIT; value = value.substring(2, value.length() - 1); } } if (MiscUtils.isNull(value) || value.equalsIgnoreCase(NULL)) { cstmnt.setNull(index, dataType); } else { switch (dataType) { case Types.TINYINT: byte _byte = Byte.valueOf(value).byteValue(); cstmnt.setShort(index, _byte); break; case Types.SMALLINT: short _short = Short.valueOf(value).shortValue(); cstmnt.setShort(index, _short); break; case Types.LONGVARCHAR: case Types.CHAR: case Types.VARCHAR: cstmnt.setString(index, value); break; case Types.BIT: case Types.BOOLEAN: boolean _boolean = false; if (NumberUtils.isNumber(value)) { int number = Integer.valueOf(value); if (number > 0) { _boolean = true; } } else { _boolean = Boolean.valueOf(value).booleanValue(); } cstmnt.setBoolean(index, _boolean); break; case Types.BIGINT: long _long = Long.valueOf(value).longValue(); cstmnt.setLong(index, _long); break; case Types.INTEGER: int _int = Integer.valueOf(value).intValue(); cstmnt.setInt(index, _int); break; case Types.REAL: float _float = Float.valueOf(value).floatValue(); cstmnt.setFloat(index, _float); break; case Types.NUMERIC: case Types.DECIMAL: cstmnt.setBigDecimal(index, new BigDecimal(value)); break; /* case Types.DATE: case Types.TIMESTAMP: case Types.TIME: cstmnt.setTimestamp(index, new Timestamp( BigDecimal(value)); */ case Types.FLOAT: case Types.DOUBLE: double _double = Double.valueOf(value).doubleValue(); cstmnt.setDouble(index, _double); break; default: cstmnt.setObject(index, value); } } // increment the index index++; } } catch (Exception e) { statementResult.setOtherErrorMessage(e.getClass().getName() + ": " + e.getMessage()); return statementResult; } } /* test creating function for postgres: CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false) RETURNS text AS $$ SELECT CASE WHEN $3 THEN UPPER($1 || ' ' || $2) ELSE LOWER($1 || ' ' || $2) END; $$ LANGUAGE SQL IMMUTABLE STRICT; */ try { cstmnt.clearWarnings(); boolean hasResultSet = cstmnt.execute(); Map<String, Object> results = new HashMap<String, Object>(); if (hasOut) { // incrementing index int index = 1; // return value from each registered out String returnValue = null; for (int i = 0; i < param.length; i++) { int type = param[i].getType(); int dataType = param[i].getDataType(); if (type == DatabaseMetaData.procedureColumnOut || type == DatabaseMetaData.procedureColumnResult || type == DatabaseMetaData.procedureColumnReturn || type == DatabaseMetaData.procedureColumnUnknown || type == DatabaseMetaData.procedureColumnInOut) { switch (dataType) { case Types.TINYINT: returnValue = Byte.toString(cstmnt.getByte(index)); break; case Types.SMALLINT: returnValue = Short.toString(cstmnt.getShort(index)); break; case Types.LONGVARCHAR: case Types.CHAR: case Types.VARCHAR: returnValue = cstmnt.getString(index); break; case Types.BIT: case Types.BOOLEAN: returnValue = Boolean.toString(cstmnt.getBoolean(index)); break; case Types.INTEGER: returnValue = Integer.toString(cstmnt.getInt(index)); break; case Types.BIGINT: returnValue = Long.toString(cstmnt.getLong(index)); break; case Types.REAL: returnValue = Float.toString(cstmnt.getFloat(index)); break; case Types.NUMERIC: case Types.DECIMAL: returnValue = cstmnt.getBigDecimal(index).toString(); break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: returnValue = cstmnt.getDate(index).toString(); break; case Types.FLOAT: case Types.DOUBLE: returnValue = Double.toString(cstmnt.getDouble(index)); break; } if (returnValue == null) { returnValue = "NULL"; } results.put(param[i].getName(), returnValue); index++; } } } if (!hasResultSet) { statementResult.setUpdateCount(cstmnt.getUpdateCount()); } else { statementResult.setResultSet(cstmnt.getResultSet()); } useCount++; statementResult.setOtherResult(results); } catch (SQLException e) { statementResult.setSqlException(e); } catch (Exception e) { statementResult.setMessage(e.getMessage()); } return statementResult; }