List of usage examples for java.sql CallableStatement getFloat
float getFloat(String parameterName) throws SQLException;
FLOAT parameter as a float in the Java programming language. From source file:com.mobilewallet.common.dao.RegisterDAO.java
public Object[] registerUser(String email, String fname, String lname, String dob, String gender, String pwd, String imei, String accounts, String country, String handsetModel, String androidVer, String emulator, String gcmId, String androidId, String refCode, String ip, String fbId) { Object[] obj = null;/*from w w w . j av a 2s . co m*/ Connection con = null; CallableStatement cstmt = null; try { con = ds.getConnection(); cstmt = con.prepareCall("{call REGISTER_USER(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}"); cstmt.setString(1, email); cstmt.setString(2, fname); cstmt.setString(3, lname); cstmt.setString(4, dob); cstmt.setString(5, gender); cstmt.setString(6, pwd); cstmt.setString(7, imei); cstmt.setString(8, accounts); cstmt.setString(9, country); cstmt.setString(10, handsetModel); cstmt.setString(11, androidVer); cstmt.setString(12, emulator); cstmt.setString(13, gcmId); cstmt.setString(14, androidId); cstmt.setString(15, refCode); cstmt.setString(16, ip); cstmt.setString(17, fbId); cstmt.registerOutParameter(18, java.sql.Types.INTEGER); cstmt.registerOutParameter(19, java.sql.Types.VARCHAR); cstmt.registerOutParameter(20, java.sql.Types.INTEGER); cstmt.registerOutParameter(21, java.sql.Types.INTEGER); cstmt.execute(); obj = new Object[4]; obj[0] = cstmt.getInt(18);//rvalue obj[1] = cstmt.getString(19);//user ref code obj[2] = cstmt.getFloat(20);//balance obj[3] = cstmt.getLong(21);//user id } catch (Exception ex) { ex.printStackTrace(); } finally { try { if (cstmt != null) { cstmt.close(); } } catch (Exception ex) { } try { if (con != null) { con.close(); } } catch (Exception ex) { } } return obj; }
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 ww w . ja v a2s. c o m 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:com.oracle.tutorial.jdbc.StoredProcedureJavaDBSample.java
public void runStoredProcedures(String coffeeNameArg, double maximumPercentageArg, double newPriceArg) throws SQLException { CallableStatement cs = null; try {/* w w w.ja v a 2s . co m*/ System.out.println("\nCalling the stored procedure GET_SUPPLIER_OF_COFFEE"); cs = this.con.prepareCall("{call GET_SUPPLIER_OF_COFFEE(?, ?)}"); cs.setString(1, coffeeNameArg); cs.registerOutParameter(2, Types.VARCHAR); cs.execute(); 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.setDouble(2, maximumPercentageArg); cs.registerOutParameter(3, Types.DOUBLE); cs.setDouble(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:com.netspective.axiom.sql.StoredProcedureParameter.java
/** * Extract the OUT parameter values from the callable statment and * assign them to the value of the parameter. *//*from w w w. ja v a2 s.c o m*/ public void extract(ConnectionContext cc, CallableStatement stmt) throws SQLException { if (getType().getValueIndex() == StoredProcedureParameter.Type.IN) return; int index = this.getIndex(); QueryParameterType paramType = getSqlType(); int jdbcType = paramType.getJdbcType(); String identifier = paramType.getIdentifier(); // result sets are special if (identifier.equals(QueryParameterType.RESULTSET_IDENTIFIER)) { ResultSet rs = (ResultSet) stmt.getObject(index); QueryResultSet qrs = new QueryResultSet(getParent().getProcedure(), cc, rs); value.getValue(cc).setValue(qrs); return; } switch (jdbcType) { case Types.VARCHAR: value.getValue(cc).setTextValue(stmt.getString(index)); break; case Types.INTEGER: value.getValue(cc).setValue(new Integer(stmt.getInt(index))); break; case Types.DOUBLE: value.getValue(cc).setValue(new Double(stmt.getDouble(index))); break; case Types.CLOB: Clob clob = stmt.getClob(index); value.getValue(cc).setTextValue(clob.getSubString(1, (int) clob.length())); break; case java.sql.Types.ARRAY: Array array = stmt.getArray(index); value.getValue(cc).setValue(array); break; case java.sql.Types.BIGINT: long bigint = stmt.getLong(index); value.getValue(cc).setValue(new Long(bigint)); break; case java.sql.Types.BINARY: value.getValue(cc).setTextValue(new String(stmt.getBytes(index))); break; case java.sql.Types.BIT: boolean bit = stmt.getBoolean(index); value.getValue(cc).setValue(new Boolean(bit)); case java.sql.Types.BLOB: value.getValue(cc).setValue(stmt.getBlob(index)); break; case java.sql.Types.CHAR: value.getValue(cc).setTextValue(stmt.getString(index)); break; case java.sql.Types.DATE: value.getValue(cc).setValue(stmt.getDate(index)); break; case java.sql.Types.DECIMAL: value.getValue(cc).setValue(stmt.getBigDecimal(index)); break; case java.sql.Types.DISTINCT: value.getValue(cc).setValue(stmt.getObject(index)); break; case java.sql.Types.FLOAT: value.getValue(cc).setValue(new Float(stmt.getFloat(index))); break; case java.sql.Types.JAVA_OBJECT: value.getValue(cc).setValue(stmt.getObject(index)); break; case java.sql.Types.LONGVARBINARY: value.getValue(cc).setTextValue(new String(stmt.getBytes(index))); break; case java.sql.Types.LONGVARCHAR: value.getValue(cc).setTextValue(stmt.getString(index)); break; //case java.sql.Types.NULL: // value.getValue(cc).setValue(null); // break; case java.sql.Types.NUMERIC: value.getValue(cc).setValue(stmt.getBigDecimal(index)); break; case java.sql.Types.OTHER: value.getValue(cc).setValue(stmt.getObject(index)); break; case java.sql.Types.REAL: value.getValue(cc).setValue(new Float(stmt.getFloat(index))); break; //case java.sql.Types.REF: // Ref ref = stmt.getRef(index); // break; case java.sql.Types.SMALLINT: short sh = stmt.getShort(index); value.getValue(cc).setValue(new Short(sh)); break; case java.sql.Types.STRUCT: value.getValue(cc).setValue(stmt.getObject(index)); break; case java.sql.Types.TIME: value.getValue(cc).setValue(stmt.getTime(index)); break; case java.sql.Types.TIMESTAMP: value.getValue(cc).setValue(stmt.getTimestamp(index)); break; case java.sql.Types.TINYINT: byte b = stmt.getByte(index); value.getValue(cc).setValue(new Byte(b)); break; case java.sql.Types.VARBINARY: value.getValue(cc).setValue(stmt.getBytes(index)); break; default: throw new RuntimeException( "Unknown JDBC Type set for stored procedure parameter '" + this.getName() + "'."); } }
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 v a2 s . 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; }
From source file:org.wso2.carbon.dataservices.core.description.query.SQLQuery.java
private ParamValue getOutparameterValue(CallableStatement cs, String type, int ordinal) throws DataServiceFault { try {/*from w w w . j a v a 2 s .c om*/ Object elementValue; if (type.equals(DBConstants.DataTypes.STRING)) { elementValue = cs.getString(ordinal); return new ParamValue(elementValue == null ? null : elementValue.toString()); } else if (type.equals(DBConstants.DataTypes.DOUBLE)) { elementValue = cs.getDouble(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Double) elementValue)); } else if (type.equals(DBConstants.DataTypes.BIGINT)) { elementValue = cs.getLong(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Long) elementValue)); } else if (type.equals(DBConstants.DataTypes.INTEGER)) { elementValue = cs.getInt(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Integer) elementValue)); } else if (type.equals(DBConstants.DataTypes.TIME)) { elementValue = cs.getTime(ordinal); return new ParamValue(elementValue == null ? null : this.convertToTimeString((Time) elementValue)); } else if (type.equals(DBConstants.DataTypes.DATE)) { elementValue = cs.getDate(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Date) elementValue)); } else if (type.equals(DBConstants.DataTypes.TIMESTAMP)) { elementValue = cs.getTimestamp(ordinal, calendar); return new ParamValue( elementValue == null ? null : this.convertToTimestampString((Timestamp) elementValue)); } else if (type.equals(DBConstants.DataTypes.BLOB)) { elementValue = cs.getBlob(ordinal); return new ParamValue(elementValue == null ? null : this.getBase64StringFromInputStream(((Blob) elementValue).getBinaryStream())); } else if (type.equals(DBConstants.DataTypes.CLOB)) { elementValue = cs.getClob(ordinal); return new ParamValue(elementValue == null ? null : deriveValueFromClob((Clob) elementValue)); } else if (type.equals(DBConstants.DataTypes.STRUCT)) { elementValue = cs.getObject(ordinal); return new ParamValue(elementValue == null ? null : (Struct) elementValue); } else if (type.equals(DBConstants.DataTypes.ARRAY)) { Array dataArray = cs.getArray(ordinal); ParamValue paramValue = new ParamValue(ParamValue.PARAM_VALUE_ARRAY); if (dataArray != null) { this.processSQLArray(dataArray, paramValue); } return paramValue; } else if (type.equals(DBConstants.DataTypes.NUMERIC)) { elementValue = cs.getBigDecimal(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((BigDecimal) elementValue)); } else if (type.equals(DBConstants.DataTypes.BIT)) { elementValue = cs.getBoolean(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Boolean) elementValue)); } else if (type.equals(DBConstants.DataTypes.TINYINT)) { elementValue = cs.getByte(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Byte) elementValue)); } else if (type.equals(DBConstants.DataTypes.SMALLINT)) { elementValue = cs.getShort(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Short) elementValue)); } else if (type.equals(DBConstants.DataTypes.REAL)) { elementValue = cs.getFloat(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Float) elementValue)); } else if (type.equals(DBConstants.DataTypes.BINARY)) { elementValue = cs.getBlob(ordinal); return new ParamValue(elementValue == null ? null : this.getBase64StringFromInputStream(((Blob) elementValue).getBinaryStream())); } else { throw new DataServiceFault("Unsupported data type: " + type); } } catch (SQLException e) { throw new DataServiceFault(e, "Error in getting sql output parameter values."); } }