List of usage examples for java.sql CallableStatement getString
String getString(String parameterName) throws SQLException;
CHAR, VARCHAR, or LONGVARCHAR parameter as a String in the Java programming language. From source file:com.MainFiles.Functions.java
public String fn_getAgentEODTransactions(String strAgentID, String strTerminalID, String intID) throws SQLException { String strResponse = ""; String SQL, sqlQuery = ""; String strTotalcredit = ""; String strdate = ""; double amount = 0; String strStatementPrinting = ""; String strTotaldebits = ""; int i = 1;//from ww w . java 2s .com Connection dbConnection = connections.getDBConnection(ECSERVER, ECDB, ECUSER, ECPASSWORD); try { NumberFormat formatter = NumberFormat.getCurrencyInstance(new Locale("en", "tz")); String strStoredProcedure = "{call SP_DAILY_TRANSACTIONS(?,?)}"; CallableStatement callableStatement = dbConnection.prepareCall(strStoredProcedure); callableStatement.setString(1, strTerminalID); callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR); callableStatement.executeUpdate(); String strStatus = callableStatement.getString(2); if (strStatus.equals("1")) { String query = "select transactiontype,transactioncount,amount from tbagentsummary where serialnumber='" + strTerminalID + "'"; ResultSet rsrecordQuery = connections.ExecuteQueryReturnString(ECSERVER, query, ECPASSWORD, ECUSER, ECDB); while (rsrecordQuery.next()) { String strAmount = NumberFormat.getNumberInstance(Locale.US) .format(Double.parseDouble(rsrecordQuery.getString("AMOUNT").toString())); strStatementPrinting += " "; strStatementPrinting += PadSpaces(15, rsrecordQuery.getString("TRANSACTIONTYPE").toString(), "RIGHT"); strStatementPrinting += PadSpaces(4, rsrecordQuery.getString("TRANSACTIONCOUNT").toString(), "LEFT"); strStatementPrinting += PadSpaces(12, strAmount.replace("TZS", ""), "LEFT"); strStatementPrinting += "#"; } } String StrMerchantFloatAccount = fn_getAgentAccountNumber(strAgentID); strResponse += "AGENT ID: " + strAgentID + "#"; strResponse += "TRAN NUM: " + intID + "#"; strResponse += "ACCOUNT NUM: " + StrMerchantFloatAccount + "#"; strResponse += "--------------------------------" + "#"; strResponse += " " + "#"; strResponse += " EOD SUMMARY REPORT " + "#"; strResponse += " " + "#"; strResponse += " TRANS. COUNT AMOUNT" + "#"; strResponse += " " + "#"; strResponse += strStatementPrinting; strResponse += " " + "#"; strResponse += " " + "#"; return strResponse; } catch (Exception ex) { this.log("INFO :: Error on fn_getAgentEODTransactions " + ex.getMessage() + "\n" + this.StackTraceWriter(ex), "ERROR"); } finally { dbConnection.close(); } return null; }
From source file:tds.dll.mysql.CommonDLL.java
public MultiDataResultSet _UpdateOpportunityAccommodations_SP_Mysql(SQLConnection connection, UUID oppKey, int segment, String accoms, int isStarted, Boolean approved, Boolean restoreRTS, _Ref<String> error, int debug) throws ReturnStatusException { List<SingleDataResultSet> resultsets = new ArrayList<SingleDataResultSet>(); try {// w ww . j a v a2 s. com String oppKeyStr = oppKey.toString().replaceAll("-", ""); oppKeyStr = String.format("0x%s", oppKeyStr); final String SQL_QUERY = "{call _UpdateOpportunityAccommodations (" + oppKeyStr + ",?,?,?,?,?,?,?)}"; CallableStatement callableStatement = connection.prepareCall(SQL_QUERY); callableStatement.setInt(1, segment); callableStatement.setString(2, accoms); callableStatement.setInt(3, isStarted); callableStatement.setBoolean(4, approved); callableStatement.setBoolean(5, restoreRTS); callableStatement.registerOutParameter(6, java.sql.Types.VARCHAR); callableStatement.setInt(7, debug); callableStatement.executeUpdate(); error.set(callableStatement.getString(6)); } catch (SQLException e) { throw new ReturnStatusException(e); } return new MultiDataResultSet(resultsets); }
From source file:us.daveread.basicquery.BasicQuery.java
/** * Takes a callable statement and retrieves the out parameters, displaying * them in the message area of the GUI.//from w w w . ja v a2 s . c o m * * @param stmt * CallableStatement just executed * @param params * List of parameters used in the callable statement * @return List */ private List<Object> getOutParams(CallableStatement stmt, List<StatementParameter> params) { List<Object> values; int paramIndex; StatementParameter param; paramIndex = 0; values = new ArrayList<Object>(); try { for (paramIndex = 0; paramIndex < params.size(); ++paramIndex) { param = (StatementParameter) params.get(paramIndex); if (param.getType() == StatementParameter.OUT) { messageOut(Resources.getString("msgOutParam") + " ", STYLE_SUBTLE, false); switch (param.getDataType()) { case java.sql.Types.VARCHAR: messageOut(Resources.getString("msgParamString", paramIndex + "") + " ", STYLE_SUBTLE, false); messageOut(stmt.getString(paramIndex + 1), STYLE_BOLD, true); values.add(stmt.getString(paramIndex + 1)); break; case java.sql.Types.INTEGER: messageOut(Resources.getString("msgParamInteger", paramIndex + "") + " ", STYLE_SUBTLE, false); messageOut("" + stmt.getInt(paramIndex + 1), STYLE_BOLD, true); values.add(new Integer(stmt.getInt(paramIndex + 1))); break; default: messageOut(Resources.getString("msgParamDefault", paramIndex + "") + " ", STYLE_SUBTLE, false); messageOut("" + stmt.getObject(paramIndex + 1), STYLE_BOLD, true); values.add(stmt.getObject(paramIndex + 1)); break; } } } } catch (Throwable any) { LOGGER.error("Failed to read output parameter at index (" + paramIndex + ")", any); messageOut(Resources.getString("errFailReadingOutParam", paramIndex + "", any.getMessage())); } return values; }
From source file:gov.nih.nci.cadsr.sentinel.database.DBAlertOracle.java
/** * Insert a DE, DEC or VD into the user reserved CSI to be monitored. * * @param idseq_ the database id of the AC to be monitored. * @param user_ the user id for the reserved CSI * @return the id of the CSI if successful, null if a problem. *//*ww w . j ava 2 s . c o m*/ public String insertAC(String idseq_, String user_) { String user = user_.toUpperCase(); CallableStatement stmt = null; String csi = null; try { stmt = _conn.prepareCall("begin SBREXT_CDE_CURATOR_PKG.ADD_TO_SENTINEL_CS(?,?,?); end;"); stmt.registerOutParameter(3, java.sql.Types.VARCHAR); stmt.setString(2, user); stmt.setString(1, idseq_); stmt.execute(); csi = stmt.getString(3); _needCommit = true; } catch (SQLException ex) { // Ooops... _errorCode = DBAlertUtil.getSQLErrorCode(ex); _errorMsg = _errorCode + ": " + ex.toString(); _logger.error(_errorMsg); } finally { closeCursors(stmt, null); } return csi; }
From source file:gov.nih.nci.cadsr.sentinel.database.DBAlertOracle.java
/** * Insert the Report details for the Alert definition into the database and * retrieve the new report id.// w w w .j av a2 s . co m * * @param rec_ * The Alert definition to be stored in the database. * @return 0 if successful, otherwise the database error code. * @throws java.sql.SQLException * On error with rollback(). */ private int insertReport(AlertRec rec_) throws SQLException { // Add the Report record. String insert = "begin insert into sbrext.sn_report_view_ext " + "(al_idseq, comments, include_property_ind, style, send, acknowledge_ind, assoc_lvl_num, created_by) " + "values (?, ?, ?, ?, ?, ?, ?, ?) return rep_idseq into ?; end;"; CallableStatement pstmt = null; int rc = 0; try { pstmt = _conn.prepareCall(insert); pstmt.setString(1, rec_.getAlertRecNum()); pstmt.setString(2, rec_.getIntro(false)); pstmt.setString(3, rec_.getIncPropSectString()); pstmt.setString(4, rec_.getReportStyleString()); pstmt.setString(5, rec_.getReportEmptyString()); pstmt.setString(6, rec_.getReportAckString()); pstmt.setInt(7, rec_.getIAssocLvl()); pstmt.setString(8, _user); pstmt.registerOutParameter(9, Types.CHAR); pstmt.executeUpdate(); // We need the record id to populate the foreign keys for other // tables. rec_.setReportRecNum(pstmt.getString(9)); } catch (SQLException ex) { // Ooops... rec_.setAlertRecNum(null); _conn.rollback(); _errorCode = DBAlertUtil.getSQLErrorCode(ex); _errorMsg = _errorCode + ": " + insert + "\n\n" + ex.toString(); _logger.error(_errorMsg); rc = _errorCode; } finally { closeCursors(pstmt, null); } return rc; }
From source file:gov.nih.nci.cadsr.sentinel.database.DBAlertOracle.java
/** * Insert the properties for the Alert definition and retrieve the new * database generated ID for this Alert. * * @param rec_/*w ww.j a va 2 s.c o m*/ * The Alert to be stored in the database. * @return 0 if successful, otherwise the database error code. * @throws java.sql.SQLException * On error with rollback(). */ private int insertProperties(AlertRec rec_) throws SQLException { // Define the SQL insert. Remember date_created, date_modified, creator // and modifier are controlled // by triggers. Also (as of 10/21/2004) after the insert the // date_modified is still set by the insert // trigger. String insert = "begin insert into sbrext.sn_alert_view_ext " + "(name, auto_freq_unit, al_status, begin_date, end_date, status_reason, auto_freq_value, created_by) " + "values (?, ?, ?, ?, ?, ?, ?, ?) return al_idseq into ?; end;"; CallableStatement pstmt = null; int rc = 0; cleanRec(rec_); try { // Set all the SQL arguments. pstmt = _conn.prepareCall(insert); pstmt.setString(1, rec_.getName()); pstmt.setString(2, rec_.getFreqString()); pstmt.setString(3, rec_.getActiveString()); pstmt.setTimestamp(4, rec_.getStart()); pstmt.setTimestamp(5, rec_.getEnd()); pstmt.setString(6, rec_.getInactiveReason(false)); pstmt.setInt(7, rec_.getDay()); pstmt.setString(8, _user); pstmt.registerOutParameter(9, Types.CHAR); // Insert the new record and flag a commit for later. pstmt.executeUpdate(); // We need the record id to populate the foreign keys for other // tables. rec_.setAlertRecNum(pstmt.getString(9)); } catch (SQLException ex) { // Ooops... rec_.setAlertRecNum(null); _conn.rollback(); _errorCode = DBAlertUtil.getSQLErrorCode(ex); _errorMsg = _errorCode + ": " + insert + "\n\n" + ex.toString(); _logger.error(_errorMsg); rc = _errorCode; } finally { closeCursors(pstmt, null); } return rc; }
From source file:org.executequery.databasemediators.spi.DefaultStatementExecutor.java
/** <p>Executes the specified procedure. * * @param the SQL procedure to execute * @return the query result/*from www .ja v a2 s .com*/ */ 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:tds.dll.mysql.StudentDLL.java
public void _IsOpportunityBlocked_SP_Mysql(SQLConnection connection, String clientname, Long testee, String testID, Integer maxopps, _Ref<String> reasonBlockedRef, Integer sessionType) throws ReturnStatusException { try {/*from w w w . j a v a 2 s . c o m*/ /*String sessionIdStr = sessionId.toString ().replaceAll ("-", ""); sessionIdStr = String.format ("0x%s", sessionIdStr);*/ final String SQL_QUERY = "{call _isopportunityblocked(?,?,?,?,?,?,?)}"; CallableStatement callableStatement = connection.prepareCall(SQL_QUERY); callableStatement.setString(1, clientname); callableStatement.setLong(2, testee); callableStatement.setString(3, testID); callableStatement.setInt(4, maxopps); callableStatement.registerOutParameter(5, java.sql.Types.VARCHAR); callableStatement.setInt(6, sessionType); callableStatement.setInt(7, 0); callableStatement.executeUpdate(); reasonBlockedRef.set(callableStatement.getString(5)); } catch (SQLException e) { throw new ReturnStatusException(e); } }
From source file:tds.dll.mysql.StudentDLL.java
public void _CanOpenTestOpportunity_SP_Mysql(SQLConnection connection, String clientname, Long testee, String testkey, UUID sessionId, Integer maxOpportunities, _Ref<Boolean> newRef, _Ref<Integer> numberRef, _Ref<String> reasonRef) throws ReturnStatusException { try {//from www.j a v a2 s .c o m String sessionIdStr = sessionId.toString().replaceAll("-", ""); sessionIdStr = String.format("0x%s", sessionIdStr); final String SQL_QUERY = "{call _canopentestopportunity(?,?,?," + sessionIdStr + ",?,?,?,?,?)}"; CallableStatement callableStatement = connection.prepareCall(SQL_QUERY); callableStatement.setString(1, clientname); callableStatement.setLong(2, testee); callableStatement.setString(3, testkey); callableStatement.setInt(4, maxOpportunities); callableStatement.registerOutParameter(5, java.sql.Types.BOOLEAN); callableStatement.registerOutParameter(6, java.sql.Types.INTEGER); callableStatement.registerOutParameter(7, java.sql.Types.VARCHAR); callableStatement.setInt(8, 0); callableStatement.executeUpdate(); newRef.set(callableStatement.getBoolean(5)); numberRef.set(callableStatement.getInt(6)); reasonRef.set(callableStatement.getString(7)); } catch (SQLException e) { throw new ReturnStatusException(e); } }