List of usage examples for java.sql CallableStatement setBigDecimal
void setBigDecimal(String parameterName, BigDecimal x) throws SQLException;
java.math.BigDecimal value. From source file:jongo.jdbc.JDBCExecutor.java
/** * Utility method which registers in a CallableStatement object the different {@link jongo.jdbc.StoredProcedureParam} * instances in the given list. Returns a List of {@link jongo.jdbc.StoredProcedureParam} with all the OUT parameters * registered in the CallableStatement/*from w w w.j ava 2 s.c om*/ * @param cs the CallableStatement object where the parameters are registered. * @param params a list of {@link jongo.jdbc.StoredProcedureParam} * @return a list of OUT {@link jongo.jdbc.StoredProcedureParam} * @throws SQLException if we fail to register any of the parameters in the CallableStatement */ private static List<StoredProcedureParam> addParameters(final CallableStatement cs, final List<StoredProcedureParam> params) throws SQLException { List<StoredProcedureParam> outParams = new ArrayList<StoredProcedureParam>(); int i = 1; for (StoredProcedureParam p : params) { final Integer sqlType = p.getType(); if (p.isOutParameter()) { l.debug("Adding OUT parameter " + p.toString()); cs.registerOutParameter(i++, sqlType); outParams.add(p); } else { l.debug("Adding IN parameter " + p.toString()); switch (sqlType) { case Types.BIGINT: case Types.INTEGER: case Types.TINYINT: // case Types.NUMERIC: cs.setInt(i++, Integer.valueOf(p.getValue())); break; case Types.DATE: cs.setDate(i++, (Date) JongoUtils.parseValue(p.getValue())); break; case Types.TIME: cs.setTime(i++, (Time) JongoUtils.parseValue(p.getValue())); break; case Types.TIMESTAMP: cs.setTimestamp(i++, (Timestamp) JongoUtils.parseValue(p.getValue())); break; case Types.DECIMAL: cs.setBigDecimal(i++, (BigDecimal) JongoUtils.parseValue(p.getValue())); break; case Types.DOUBLE: cs.setDouble(i++, Double.valueOf(p.getValue())); break; case Types.FLOAT: cs.setLong(i++, Long.valueOf(p.getValue())); break; default: cs.setString(i++, p.getValue()); break; } } } return outParams; }
From source file:it.greenvulcano.gvesb.datahandling.dbo.DBOCallSP.java
private void setBigDecimal(CallableStatement cs, BigDecimal num) throws SQLException { if (useName) { cs.setBigDecimal(currName, num); } else {/* w w w .j a va 2 s .com*/ cs.setBigDecimal(colIdx, num); } }
From source file:net.sourceforge.msscodefactory.cfacc.v2_0.CFAccOracle.CFAccOracleAccountTable.java
public void createAccount(CFAccAuthorization Authorization, CFAccAccountBuff Buff) { final String S_ProcName = "createAccount"; if (!schema.isTransactionOpen()) { throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName, "Transaction not open"); }// ww w . j a v a 2s . c o m ResultSet resultSet = null; CallableStatement stmtCreateByPKey = null; try { long TenantId = Buff.getRequiredTenantId(); String AccountCode = Buff.getRequiredAccountCode(); String Description = Buff.getRequiredDescription(); short CurrencyId = Buff.getRequiredCurrencyId(); BigDecimal Balance = Buff.getRequiredBalance(); Long RollupTenantId = Buff.getOptionalRollupTenantId(); Long RollupAccountId = Buff.getOptionalRollupAccountId(); Connection cnx = schema.getCnx(); stmtCreateByPKey = cnx.prepareCall( "begin " + schema.getLowerSchemaDbName() + ".crt_acct( ?, ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + " ); end;"); int argIdx = 1; stmtCreateByPKey.registerOutParameter(argIdx++, OracleTypes.CURSOR); stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtCreateByPKey.setString(argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString()); stmtCreateByPKey.setString(argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString()); stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId()); stmtCreateByPKey.setString(argIdx++, "ACCT"); stmtCreateByPKey.setLong(argIdx++, TenantId); stmtCreateByPKey.setString(argIdx++, AccountCode); stmtCreateByPKey.setString(argIdx++, Description); stmtCreateByPKey.setShort(argIdx++, CurrencyId); stmtCreateByPKey.setBigDecimal(argIdx++, Balance); if (RollupTenantId != null) { stmtCreateByPKey.setLong(argIdx++, RollupTenantId.longValue()); } else { stmtCreateByPKey.setNull(argIdx++, java.sql.Types.BIGINT); } if (RollupAccountId != null) { stmtCreateByPKey.setLong(argIdx++, RollupAccountId.longValue()); } else { stmtCreateByPKey.setNull(argIdx++, java.sql.Types.BIGINT); } stmtCreateByPKey.execute(); resultSet = (ResultSet) stmtCreateByPKey.getObject(1); if (resultSet == null) { throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "crt_acct() did not return a result set"); } try { if (resultSet.next()) { CFAccAccountBuff createdBuff = unpackAccountResultSetToBuff(resultSet); if (resultSet.next()) { resultSet.last(); throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "Did not expect multi-record response, " + resultSet.getRow() + " rows selected"); } Buff.setRequiredTenantId(createdBuff.getRequiredTenantId()); Buff.setRequiredId(createdBuff.getRequiredId()); Buff.setRequiredAccountCode(createdBuff.getRequiredAccountCode()); Buff.setRequiredDescription(createdBuff.getRequiredDescription()); Buff.setRequiredCurrencyId(createdBuff.getRequiredCurrencyId()); Buff.setRequiredBalance(createdBuff.getRequiredBalance()); Buff.setOptionalRollupTenantId(createdBuff.getOptionalRollupTenantId()); Buff.setOptionalRollupAccountId(createdBuff.getOptionalRollupAccountId()); Buff.setRequiredRevision(createdBuff.getRequiredRevision()); Buff.setCreatedByUserId(createdBuff.getCreatedByUserId()); Buff.setCreatedAt(createdBuff.getCreatedAt()); Buff.setUpdatedByUserId(createdBuff.getUpdatedByUserId()); Buff.setUpdatedAt(createdBuff.getUpdatedAt()); } else { throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "Expected a single-record response, " + resultSet.getRow() + " rows selected"); } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "crt_acct() did not return a valid result set"); } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } resultSet = null; } if (stmtCreateByPKey != null) { try { stmtCreateByPKey.close(); } catch (SQLException e) { } stmtCreateByPKey = null; } } }
From source file:net.sourceforge.msscodefactory.cfacc.v2_0.CFAccOracle.CFAccOracleAccountTable.java
public void updateAccount(CFAccAuthorization Authorization, CFAccAccountBuff Buff) { final String S_ProcName = "updateAccount"; ResultSet resultSet = null;//w w w . ja v a 2 s.co m Connection cnx = schema.getCnx(); CallableStatement stmtUpdateByPKey = null; List<CFAccAccountBuff> buffList = new LinkedList<CFAccAccountBuff>(); try { long TenantId = Buff.getRequiredTenantId(); long Id = Buff.getRequiredId(); String AccountCode = Buff.getRequiredAccountCode(); String Description = Buff.getRequiredDescription(); short CurrencyId = Buff.getRequiredCurrencyId(); BigDecimal Balance = Buff.getRequiredBalance(); Long RollupTenantId = Buff.getOptionalRollupTenantId(); Long RollupAccountId = Buff.getOptionalRollupAccountId(); int Revision = Buff.getRequiredRevision(); stmtUpdateByPKey = cnx.prepareCall("begin " + schema.getLowerSchemaDbName() + ".upd_acct( ?, ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "? ); end;"); int argIdx = 1; stmtUpdateByPKey.registerOutParameter(argIdx++, OracleTypes.CURSOR); stmtUpdateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtUpdateByPKey.setString(argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString()); stmtUpdateByPKey.setString(argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString()); stmtUpdateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtUpdateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId()); stmtUpdateByPKey.setString(argIdx++, "ACCT"); stmtUpdateByPKey.setLong(argIdx++, TenantId); stmtUpdateByPKey.setLong(argIdx++, Id); stmtUpdateByPKey.setString(argIdx++, AccountCode); stmtUpdateByPKey.setString(argIdx++, Description); stmtUpdateByPKey.setShort(argIdx++, CurrencyId); stmtUpdateByPKey.setBigDecimal(argIdx++, Balance); if (RollupTenantId != null) { stmtUpdateByPKey.setLong(argIdx++, RollupTenantId.longValue()); } else { stmtUpdateByPKey.setNull(argIdx++, java.sql.Types.BIGINT); } if (RollupAccountId != null) { stmtUpdateByPKey.setLong(argIdx++, RollupAccountId.longValue()); } else { stmtUpdateByPKey.setNull(argIdx++, java.sql.Types.BIGINT); } stmtUpdateByPKey.setInt(argIdx++, Revision); stmtUpdateByPKey.execute(); resultSet = (ResultSet) stmtUpdateByPKey.getObject(1); if (resultSet != null) { try { if (resultSet.next()) { CFAccAccountBuff updatedBuff = unpackAccountResultSetToBuff(resultSet); if (resultSet.next()) { resultSet.last(); throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "Did not expect multi-record response, " + resultSet.getRow() + " rows selected"); } Buff.setRequiredAccountCode(updatedBuff.getRequiredAccountCode()); Buff.setRequiredDescription(updatedBuff.getRequiredDescription()); Buff.setRequiredCurrencyId(updatedBuff.getRequiredCurrencyId()); Buff.setRequiredBalance(updatedBuff.getRequiredBalance()); Buff.setOptionalRollupTenantId(updatedBuff.getOptionalRollupTenantId()); Buff.setOptionalRollupAccountId(updatedBuff.getOptionalRollupAccountId()); Buff.setRequiredRevision(updatedBuff.getRequiredRevision()); } else { throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "Expected a single-record response, " + resultSet.getRow() + " rows selected"); } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "upd_acct() did not return a valid result cursor"); } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } resultSet = null; } } } else { throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "upd_acct() did not return a result cursor"); } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } resultSet = null; } if (stmtUpdateByPKey != null) { try { stmtUpdateByPKey.close(); } catch (SQLException e) { } stmtUpdateByPKey = null; } } }
From source file:net.sourceforge.msscodefactory.cfacc.v2_0.CFAccOracle.CFAccOracleAccountEntryTable.java
public void createAccountEntry(CFAccAuthorization Authorization, CFAccAccountEntryBuff Buff) { final String S_ProcName = "createAccountEntry"; if (!schema.isTransactionOpen()) { throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName, "Transaction not open"); }// www . j ava 2 s. c o m ResultSet resultSet = null; CallableStatement stmtCreateByPKey = null; try { long TenantId = Buff.getRequiredTenantId(); long AccountId = Buff.getRequiredAccountId(); UUID SplitEntryId = Buff.getOptionalSplitEntryId(); Calendar EntryStamp = Buff.getRequiredEntryStamp(); String Description = Buff.getRequiredDescription(); Long TransferTenantId = Buff.getOptionalTransferTenantId(); Long TransferAccountId = Buff.getOptionalTransferAccountId(); Short DebitCurrencyId = Buff.getOptionalDebitCurrencyId(); BigDecimal Debit = Buff.getOptionalDebit(); Short CreditCurrencyId = Buff.getOptionalCreditCurrencyId(); BigDecimal Credit = Buff.getOptionalCredit(); short ConvertedCurrencyId = Buff.getRequiredConvertedCurrencyId(); BigDecimal ConvertedAmount = Buff.getRequiredConvertedAmount(); short BalanceCurrencyId = Buff.getRequiredBalanceCurrencyId(); BigDecimal Balance = Buff.getRequiredBalance(); UUID EntryId = UUID.randomUUID(); Connection cnx = schema.getCnx(); stmtCreateByPKey = cnx.prepareCall("begin " + schema.getLowerSchemaDbName() + ".crt_ac_entry( ?, ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "to_timestamp( ?, 'YYYY-MM-DD HH24:MI:SS' )" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + " ); end;"); int argIdx = 1; stmtCreateByPKey.registerOutParameter(argIdx++, OracleTypes.CURSOR); stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtCreateByPKey.setString(argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString()); stmtCreateByPKey.setString(argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString()); stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId()); stmtCreateByPKey.setString(argIdx++, "ACNY"); stmtCreateByPKey.setLong(argIdx++, TenantId); stmtCreateByPKey.setLong(argIdx++, AccountId); stmtCreateByPKey.setString(argIdx++, EntryId.toString()); if (SplitEntryId != null) { stmtCreateByPKey.setString(argIdx++, SplitEntryId.toString()); } else { stmtCreateByPKey.setNull(argIdx++, java.sql.Types.VARCHAR); } stmtCreateByPKey.setString(argIdx++, CFAccOracleSchema.getTimestampString(EntryStamp)); stmtCreateByPKey.setString(argIdx++, Description); if (TransferTenantId != null) { stmtCreateByPKey.setLong(argIdx++, TransferTenantId.longValue()); } else { stmtCreateByPKey.setNull(argIdx++, java.sql.Types.BIGINT); } if (TransferAccountId != null) { stmtCreateByPKey.setLong(argIdx++, TransferAccountId.longValue()); } else { stmtCreateByPKey.setNull(argIdx++, java.sql.Types.BIGINT); } if (DebitCurrencyId != null) { stmtCreateByPKey.setShort(argIdx++, DebitCurrencyId.shortValue()); } else { stmtCreateByPKey.setNull(argIdx++, java.sql.Types.SMALLINT); } if (Debit != null) { stmtCreateByPKey.setBigDecimal(argIdx++, Debit); } else { stmtCreateByPKey.setNull(argIdx++, java.sql.Types.NUMERIC); } if (CreditCurrencyId != null) { stmtCreateByPKey.setShort(argIdx++, CreditCurrencyId.shortValue()); } else { stmtCreateByPKey.setNull(argIdx++, java.sql.Types.SMALLINT); } if (Credit != null) { stmtCreateByPKey.setBigDecimal(argIdx++, Credit); } else { stmtCreateByPKey.setNull(argIdx++, java.sql.Types.NUMERIC); } stmtCreateByPKey.setShort(argIdx++, ConvertedCurrencyId); stmtCreateByPKey.setBigDecimal(argIdx++, ConvertedAmount); stmtCreateByPKey.setShort(argIdx++, BalanceCurrencyId); stmtCreateByPKey.setBigDecimal(argIdx++, Balance); stmtCreateByPKey.execute(); resultSet = (ResultSet) stmtCreateByPKey.getObject(1); if (resultSet == null) { throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "crt_ac_entry() did not return a result set"); } try { if (resultSet.next()) { CFAccAccountEntryBuff createdBuff = unpackAccountEntryResultSetToBuff(resultSet); if (resultSet.next()) { resultSet.last(); throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "Did not expect multi-record response, " + resultSet.getRow() + " rows selected"); } Buff.setRequiredTenantId(createdBuff.getRequiredTenantId()); Buff.setRequiredAccountId(createdBuff.getRequiredAccountId()); Buff.setRequiredEntryId(createdBuff.getRequiredEntryId()); Buff.setOptionalSplitEntryId(createdBuff.getOptionalSplitEntryId()); Buff.setRequiredEntryStamp(createdBuff.getRequiredEntryStamp()); Buff.setRequiredDescription(createdBuff.getRequiredDescription()); Buff.setOptionalTransferTenantId(createdBuff.getOptionalTransferTenantId()); Buff.setOptionalTransferAccountId(createdBuff.getOptionalTransferAccountId()); Buff.setOptionalDebitCurrencyId(createdBuff.getOptionalDebitCurrencyId()); Buff.setOptionalDebit(createdBuff.getOptionalDebit()); Buff.setOptionalCreditCurrencyId(createdBuff.getOptionalCreditCurrencyId()); Buff.setOptionalCredit(createdBuff.getOptionalCredit()); Buff.setRequiredConvertedCurrencyId(createdBuff.getRequiredConvertedCurrencyId()); Buff.setRequiredConvertedAmount(createdBuff.getRequiredConvertedAmount()); Buff.setRequiredBalanceCurrencyId(createdBuff.getRequiredBalanceCurrencyId()); Buff.setRequiredBalance(createdBuff.getRequiredBalance()); Buff.setRequiredRevision(createdBuff.getRequiredRevision()); Buff.setCreatedByUserId(createdBuff.getCreatedByUserId()); Buff.setCreatedAt(createdBuff.getCreatedAt()); Buff.setUpdatedByUserId(createdBuff.getUpdatedByUserId()); Buff.setUpdatedAt(createdBuff.getUpdatedAt()); } else { throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "Expected a single-record response, " + resultSet.getRow() + " rows selected"); } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "crt_ac_entry() did not return a valid result set"); } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } resultSet = null; } if (stmtCreateByPKey != null) { try { stmtCreateByPKey.close(); } catch (SQLException e) { } stmtCreateByPKey = null; } } }
From source file:net.sourceforge.msscodefactory.cfacc.v2_0.CFAccOracle.CFAccOracleAccountEntryTable.java
public void updateAccountEntry(CFAccAuthorization Authorization, CFAccAccountEntryBuff Buff) { final String S_ProcName = "updateAccountEntry"; ResultSet resultSet = null;/*from ww w. j a va 2s . co m*/ Connection cnx = schema.getCnx(); CallableStatement stmtUpdateByPKey = null; List<CFAccAccountEntryBuff> buffList = new LinkedList<CFAccAccountEntryBuff>(); try { long TenantId = Buff.getRequiredTenantId(); long AccountId = Buff.getRequiredAccountId(); UUID EntryId = Buff.getRequiredEntryId(); UUID SplitEntryId = Buff.getOptionalSplitEntryId(); Calendar EntryStamp = Buff.getRequiredEntryStamp(); String Description = Buff.getRequiredDescription(); Long TransferTenantId = Buff.getOptionalTransferTenantId(); Long TransferAccountId = Buff.getOptionalTransferAccountId(); Short DebitCurrencyId = Buff.getOptionalDebitCurrencyId(); BigDecimal Debit = Buff.getOptionalDebit(); Short CreditCurrencyId = Buff.getOptionalCreditCurrencyId(); BigDecimal Credit = Buff.getOptionalCredit(); short ConvertedCurrencyId = Buff.getRequiredConvertedCurrencyId(); BigDecimal ConvertedAmount = Buff.getRequiredConvertedAmount(); short BalanceCurrencyId = Buff.getRequiredBalanceCurrencyId(); BigDecimal Balance = Buff.getRequiredBalance(); int Revision = Buff.getRequiredRevision(); stmtUpdateByPKey = cnx.prepareCall("begin " + schema.getLowerSchemaDbName() + ".upd_ac_entry( ?, ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "to_timestamp( ?, 'YYYY-MM-DD HH24:MI:SS' )" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "? ); end;"); int argIdx = 1; stmtUpdateByPKey.registerOutParameter(argIdx++, OracleTypes.CURSOR); stmtUpdateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtUpdateByPKey.setString(argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString()); stmtUpdateByPKey.setString(argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString()); stmtUpdateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtUpdateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId()); stmtUpdateByPKey.setString(argIdx++, "ACNY"); stmtUpdateByPKey.setLong(argIdx++, TenantId); stmtUpdateByPKey.setLong(argIdx++, AccountId); stmtUpdateByPKey.setString(argIdx++, EntryId.toString()); if (SplitEntryId != null) { stmtUpdateByPKey.setString(argIdx++, SplitEntryId.toString()); } else { stmtUpdateByPKey.setNull(argIdx++, java.sql.Types.VARCHAR); } stmtUpdateByPKey.setString(argIdx++, CFAccOracleSchema.getTimestampString(EntryStamp)); stmtUpdateByPKey.setString(argIdx++, Description); if (TransferTenantId != null) { stmtUpdateByPKey.setLong(argIdx++, TransferTenantId.longValue()); } else { stmtUpdateByPKey.setNull(argIdx++, java.sql.Types.BIGINT); } if (TransferAccountId != null) { stmtUpdateByPKey.setLong(argIdx++, TransferAccountId.longValue()); } else { stmtUpdateByPKey.setNull(argIdx++, java.sql.Types.BIGINT); } if (DebitCurrencyId != null) { stmtUpdateByPKey.setShort(argIdx++, DebitCurrencyId.shortValue()); } else { stmtUpdateByPKey.setNull(argIdx++, java.sql.Types.SMALLINT); } if (Debit != null) { stmtUpdateByPKey.setBigDecimal(argIdx++, Debit); } else { stmtUpdateByPKey.setNull(argIdx++, java.sql.Types.NUMERIC); } if (CreditCurrencyId != null) { stmtUpdateByPKey.setShort(argIdx++, CreditCurrencyId.shortValue()); } else { stmtUpdateByPKey.setNull(argIdx++, java.sql.Types.SMALLINT); } if (Credit != null) { stmtUpdateByPKey.setBigDecimal(argIdx++, Credit); } else { stmtUpdateByPKey.setNull(argIdx++, java.sql.Types.NUMERIC); } stmtUpdateByPKey.setShort(argIdx++, ConvertedCurrencyId); stmtUpdateByPKey.setBigDecimal(argIdx++, ConvertedAmount); stmtUpdateByPKey.setShort(argIdx++, BalanceCurrencyId); stmtUpdateByPKey.setBigDecimal(argIdx++, Balance); stmtUpdateByPKey.setInt(argIdx++, Revision); stmtUpdateByPKey.execute(); resultSet = (ResultSet) stmtUpdateByPKey.getObject(1); if (resultSet != null) { try { if (resultSet.next()) { CFAccAccountEntryBuff updatedBuff = unpackAccountEntryResultSetToBuff(resultSet); if (resultSet.next()) { resultSet.last(); throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "Did not expect multi-record response, " + resultSet.getRow() + " rows selected"); } Buff.setOptionalSplitEntryId(updatedBuff.getOptionalSplitEntryId()); Buff.setRequiredEntryStamp(updatedBuff.getRequiredEntryStamp()); Buff.setRequiredDescription(updatedBuff.getRequiredDescription()); Buff.setOptionalTransferTenantId(updatedBuff.getOptionalTransferTenantId()); Buff.setOptionalTransferAccountId(updatedBuff.getOptionalTransferAccountId()); Buff.setOptionalDebitCurrencyId(updatedBuff.getOptionalDebitCurrencyId()); Buff.setOptionalDebit(updatedBuff.getOptionalDebit()); Buff.setOptionalCreditCurrencyId(updatedBuff.getOptionalCreditCurrencyId()); Buff.setOptionalCredit(updatedBuff.getOptionalCredit()); Buff.setRequiredConvertedCurrencyId(updatedBuff.getRequiredConvertedCurrencyId()); Buff.setRequiredConvertedAmount(updatedBuff.getRequiredConvertedAmount()); Buff.setRequiredBalanceCurrencyId(updatedBuff.getRequiredBalanceCurrencyId()); Buff.setRequiredBalance(updatedBuff.getRequiredBalance()); Buff.setRequiredRevision(updatedBuff.getRequiredRevision()); } else { throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "Expected a single-record response, " + resultSet.getRow() + " rows selected"); } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "upd_ac_entry() did not return a valid result cursor"); } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } resultSet = null; } } } else { throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "upd_ac_entry() did not return a result cursor"); } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } resultSet = null; } if (stmtUpdateByPKey != null) { try { stmtUpdateByPKey.close(); } catch (SQLException e) { } stmtUpdateByPKey = 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//from w w w. j ava2s .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; }