Example usage for java.sql Connection prepareCall

List of usage examples for java.sql Connection prepareCall

Introduction

In this page you can find the example usage for java.sql Connection prepareCall.

Prototype

CallableStatement prepareCall(String sql) throws SQLException;

Source Link

Document

Creates a CallableStatement object for calling database stored procedures.

Usage

From source file:net.sourceforge.msscodefactory.cffreeswitch.v2_1.CFFswOracle.CFFswOracleSecAppTable.java

public void createSecApp(CFFswAuthorization Authorization, CFFswSecAppBuff Buff) {
    final String S_ProcName = "createSecApp";
    if (!schema.isTransactionOpen()) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName,
                "Transaction not open");
    }//from w ww  . ja v  a 2s .c o m
    ResultSet resultSet = null;
    CallableStatement stmtCreateByPKey = null;
    try {
        long ClusterId = Buff.getRequiredClusterId();
        String JEEMountName = Buff.getRequiredJEEMountName();
        Connection cnx = schema.getCnx();
        stmtCreateByPKey = cnx.prepareCall("begin " + schema.getLowerDbSchemaName()
                + ".crt_secapp( ?, ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + " ); 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++, "SAPP");
        stmtCreateByPKey.setLong(argIdx++, ClusterId);
        stmtCreateByPKey.setString(argIdx++, JEEMountName);
        stmtCreateByPKey.execute();
        resultSet = (ResultSet) stmtCreateByPKey.getObject(1);
        if (resultSet == null) {
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "crt_secapp() did not return a result set");
        }
        try {
            if (resultSet.next()) {
                CFFswSecAppBuff createdBuff = unpackSecAppResultSetToBuff(resultSet);
                if (resultSet.next()) {
                    resultSet.last();
                    throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                            "Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
                }
                Buff.setRequiredClusterId(createdBuff.getRequiredClusterId());
                Buff.setRequiredSecAppId(createdBuff.getRequiredSecAppId());
                Buff.setRequiredJEEMountName(createdBuff.getRequiredJEEMountName());
                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_secapp() 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.cfasterisk.v2_2.CFAstOracle.CFAstOracleSysClusterTable.java

public CFAstSysClusterBuff[] readAllBuff(CFAstAuthorization Authorization) {
    final String S_ProcName = "readAllBuff";
    if (!schema.isTransactionOpen()) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName,
                "Transaction not open");
    }/*  w  w w  .  j a  v  a2  s.com*/
    ResultSet resultSet = null;
    Connection cnx = schema.getCnx();
    CallableStatement stmtReadAllBuff = null;
    try {
        CFAstSysClusterBuff buff = null;
        List<CFAstSysClusterBuff> buffList = new LinkedList<CFAstSysClusterBuff>();
        stmtReadAllBuff = cnx.prepareCall(
                "begin " + schema.getLowerDbSchemaName() + ".rd_sysclusall( ?, ?, ?, ?, ?, ? ) ); end;");
        int argIdx = 1;
        stmtReadAllBuff.registerOutParameter(argIdx++, OracleTypes.CURSOR);
        stmtReadAllBuff.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtReadAllBuff.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtReadAllBuff.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtReadAllBuff.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtReadAllBuff.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtReadAllBuff.execute();
        resultSet = (ResultSet) stmtReadAllBuff.getObject(1);
        if (resultSet != null) {
            try {
                while (resultSet.next()) {
                    buff = unpackSysClusterResultSetToBuff(resultSet);
                    buffList.add(buff);
                }
            } catch (SQLException e) {
                // Oracle may return an invalid resultSet if the rowset is empty
            }
        }
        int idx = 0;
        CFAstSysClusterBuff[] retBuff = new CFAstSysClusterBuff[buffList.size()];
        Iterator<CFAstSysClusterBuff> iter = buffList.iterator();
        while (iter.hasNext()) {
            retBuff[idx++] = iter.next();
        }
        return (retBuff);
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
        if (stmtReadAllBuff != null) {
            try {
                stmtReadAllBuff.close();
            } catch (SQLException e) {
            }
            stmtReadAllBuff = null;
        }
    }
}

From source file:com.intuit.it.billing.data.BillingDAOImpl.java

/**
 * getAllocationsList/*from w  ww .jav a 2  s. c o  m*/
 * 
 * To be used in a caching method where we are pulling all of the allocations at once.  The way we can do this
 * is to merge a date range based set of billing history records with a date range set of allocations.
 * <p/>
 * <p/>
 * <b>DATABASE PROCEDURE:</b>
 *  
 * @code
 *     FUNCTION fn_get_allocations(
 *           customer IN VARCHAR2,
 *           start_date IN DATE,
 *           end_date   IN DATE )
 *        RETURN ref_cursor;
 * @endcode
 * <p/>
 * <b>DATABASE RESULT SET:</b>
 * <ul>
 *    <li>ALLOCATION_DATE,</li>
 *    <li>ALLOCATION_T, </li>
 *    <li>ALLOCATION_AMT,</li>
 *    <li>AR_ITEM_NO, </li>
 *    <li>BILL_ITEM_NO, </li>
 *    <li>ITEM_DESCRIPTION, </li>
 *    <li>ITEM_CODE,</li> 
 *    <li>AR_ITEM_DATE, </li>
 *    <li>BILL_ITEM_DATE </li>
 *    <li>LICENSE</li>
 *  </ul>
 *  
 * @param customer  :  The Customer.accountNo account number of the customer who's allocations we need
 * @param startDate : The starting date of the allocation - to be merged with a billing history record set
 * @param endDate  :  The ending date of the allocation - to be merged with a billing history record set
 * 
 * @return A list of Allocation objects. 
 * 
 */
@Override
public List<Allocation> getAllocationsList(String customer, Date startDate, Date endDate) throws JSONException {

    List<Allocation> allocs = new ArrayList<Allocation>();

    java.sql.Date sqlStartDate = new java.sql.Date(startDate.getTime());
    java.sql.Date sqlEndDate = new java.sql.Date(endDate.getTime());

    String query = "begin ? := billing_inquiry.fn_get_allocations( ?, ?, ? ); end;";

    Connection conn = null;
    ResultSet rs = null;

    // DB Connection
    try {
        conn = this.getConnection();
    } catch (SQLException e) {
        throw JSONException.sqlError(e);
    } catch (NamingException e) {
        throw JSONException.namingError(e.toString());
    }

    try {
        CallableStatement stmt = conn.prepareCall(query);
        stmt.registerOutParameter(1, OracleTypes.CURSOR);
        stmt.setString(2, customer);
        stmt.setDate(3, sqlStartDate);
        stmt.setDate(4, sqlEndDate);

        stmt.execute();
        rs = (ResultSet) stmt.getObject(1);

        while (rs.next()) {

            Allocation a = new Allocation();
            a.setAllocatedFromItem(rs.getString("AR_ITEM_NO"));
            a.setAllocatedToItem(rs.getString("BILL_ITEM_NO"));
            a.setAllocationAmount(rs.getBigDecimal("ALLOCATION_AMT"));
            a.setAllocationDate(rs.getTimestamp("ALLOCATION_DATE"));
            a.setItemCode(rs.getString("ITEM_CODE"));
            a.setItemDescription(rs.getString("ITEM_DESCRIPTION"));
            a.setLicense(rs.getString("LICENSE"));
            allocs.add(a);
        }
        conn.close();
        rs.close();

    } catch (SQLException e) {
        throw JSONException.sqlError(e);
    }

    if (allocs == null || allocs.isEmpty()) {
        throw JSONException.noDataFound("Null set returned - no data found");
    }

    return allocs;
}

From source file:com.intuit.it.billing.data.BillingDAOImpl.java

/**
 * PaymentInfoCheck/*from   w  w  w.j  a  v  a2s  .co  m*/
 * <p/>
 * <p/>
 * <b>DATABASE PROCEDURE:</b>
 *  
 * @code
 *FUNCTION fn_get_payinfo_check
 * (
 *    item_no      IN VARCHAR2
 *  )
 *  RETURN ref_cursor;
 * @endcode
 * <p/>
 * <b>DATABASE RESULT SET:</b>
 * <ul>
 *    <li>BILLING_PROFILE_ID,</li>
 *    <li>BDOM,</li>
 *    <li>PAYINFO_NAME,</li>
 *    <li>NET_TERMS,</li>
 *    <li>CHECK_NO,</li>
 *  </ul>
 *  
 * @param itemNo - The item number (e.g. P1-111)of the item we want payment info for
 * 
 * @return A single Payment Info record
 * 
 */
@Override
public PaymentInfoCheck getPayinfoCheck(String itemNo) throws JSONException {

    String query = "begin ? := billing_inquiry.fn_get_payinfo_check( ? ); end;"; // TODO: configure strings

    ResultSet rs = null;
    Connection conn = null;
    PaymentInfoCheck c = null;

    // DB Connection
    try {
        conn = this.getConnection();
    } catch (SQLException e) {
        throw JSONException.sqlError(e);
    } catch (NamingException e) {
        throw JSONException.namingError(e.toString());
    }

    try {

        CallableStatement stmt = conn.prepareCall(query);
        stmt.registerOutParameter(1, OracleTypes.CURSOR);
        stmt.setString(2, itemNo);

        stmt.execute();
        rs = (ResultSet) stmt.getObject(1);

        while (rs.next()) {

            c = new PaymentInfoCheck();
            c.setBillingProfile(rs.getString("BILLING_PROFILE_ID")); // TODO: configure strings
            c.setProfileBdom(rs.getInt("BDOM"));
            c.setCustomerName(rs.getString("PAYINFO_NAME"));
            c.setNetTerms(rs.getString("NET_TERMS"));
            c.setCheckNumber(rs.getString("CHECK_NO"));
        }

        conn.close();
        rs.close();

    } catch (SQLException e) {
        throw JSONException.sqlError(e);
    }

    if (c == null) {
        throw JSONException.noDataFound("Null set returned - no data found");
    }

    return c;
}

From source file:net.sourceforge.msscodefactory.cfacc.v2_0.CFAccOracle.CFAccOracleSecUserTable.java

public CFAccSecUserBuff[] readAllBuff(CFAccAuthorization Authorization) {
    final String S_ProcName = "readAllBuff";
    if (!schema.isTransactionOpen()) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName,
                "Transaction not open");
    }/*ww  w .  j  a  va  2 s .  c  om*/
    ResultSet resultSet = null;
    Connection cnx = schema.getCnx();
    CallableStatement stmtReadAllBuff = null;
    try {
        CFAccSecUserBuff buff = null;
        List<CFAccSecUserBuff> buffList = new LinkedList<CFAccSecUserBuff>();
        stmtReadAllBuff = cnx.prepareCall(
                "begin " + schema.getLowerSchemaDbName() + ".rd_secuserall( ?, ?, ?, ?, ?, ? ) ); end;");
        int argIdx = 1;
        stmtReadAllBuff.registerOutParameter(argIdx++, OracleTypes.CURSOR);
        stmtReadAllBuff.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtReadAllBuff.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtReadAllBuff.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtReadAllBuff.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtReadAllBuff.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtReadAllBuff.execute();
        resultSet = (ResultSet) stmtReadAllBuff.getObject(1);
        if (resultSet != null) {
            try {
                while (resultSet.next()) {
                    buff = unpackSecUserResultSetToBuff(resultSet);
                    buffList.add(buff);
                }
            } catch (SQLException e) {
                // Oracle may return an invalid resultSet if the rowset is empty
            }
        }
        int idx = 0;
        CFAccSecUserBuff[] retBuff = new CFAccSecUserBuff[buffList.size()];
        Iterator<CFAccSecUserBuff> iter = buffList.iterator();
        while (iter.hasNext()) {
            retBuff[idx++] = iter.next();
        }
        return (retBuff);
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
        if (stmtReadAllBuff != null) {
            try {
                stmtReadAllBuff.close();
            } catch (SQLException e) {
            }
            stmtReadAllBuff = null;
        }
    }
}

From source file:net.sourceforge.msscodefactory.cfasterisk.v2_0.CFAstOracle.CFAstOracleSecUserTable.java

public CFAstSecUserBuff[] readAllBuff(CFAstAuthorization Authorization) {
    final String S_ProcName = "readAllBuff";
    if (!schema.isTransactionOpen()) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName,
                "Transaction not open");
    }/*from w  w  w  .  j a va2s.co  m*/
    ResultSet resultSet = null;
    Connection cnx = schema.getCnx();
    CallableStatement stmtReadAllBuff = null;
    try {
        CFAstSecUserBuff buff = null;
        List<CFAstSecUserBuff> buffList = new LinkedList<CFAstSecUserBuff>();
        stmtReadAllBuff = cnx.prepareCall(
                "begin " + schema.getLowerSchemaDbName() + ".rd_secuserall( ?, ?, ?, ?, ?, ? ) ); end;");
        int argIdx = 1;
        stmtReadAllBuff.registerOutParameter(argIdx++, OracleTypes.CURSOR);
        stmtReadAllBuff.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtReadAllBuff.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtReadAllBuff.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtReadAllBuff.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtReadAllBuff.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtReadAllBuff.execute();
        resultSet = (ResultSet) stmtReadAllBuff.getObject(1);
        if (resultSet != null) {
            try {
                while (resultSet.next()) {
                    buff = unpackSecUserResultSetToBuff(resultSet);
                    buffList.add(buff);
                }
            } catch (SQLException e) {
                // Oracle may return an invalid resultSet if the rowset is empty
            }
        }
        int idx = 0;
        CFAstSecUserBuff[] retBuff = new CFAstSecUserBuff[buffList.size()];
        Iterator<CFAstSecUserBuff> iter = buffList.iterator();
        while (iter.hasNext()) {
            retBuff[idx++] = iter.next();
        }
        return (retBuff);
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
        if (stmtReadAllBuff != null) {
            try {
                stmtReadAllBuff.close();
            } catch (SQLException e) {
            }
            stmtReadAllBuff = null;
        }
    }
}

From source file:com.intuit.it.billing.data.BillingDAOImpl.java

/**
 * getCustomerInfo/*www . j a  v a2 s.  co m*/
 * <p/>
 * <p/>
 * <b>DATABASE PROCEDURE:</b>
 *  
 * @code
 *  FUNCTION fn_get_customer
 *  (
 *    customer     IN VARCHAR2,
 *    bill_item_no IN VARCHAR2,
 *    order_no     IN VARCHAR2,
 *    pson         IN VARCHAR2
 *  )
 *  RETURN ref_cursor;
 * @endcode
 * <p/>
 * <b>DATABASE RESULT SET:</b>
 * <ul>
 *    <li>ACCOUNT_NO,</li>
 *    <li>FIRST_NAME,</li>
 *    <li>LAST_NAME,</li>
 *    <li>COMPANY,</li>
 *    <li>PHONE,</li>
 *    <li>ADDRESS,</li>
 *    <li>CITY,</li>
  *    <li>STATE,</li>
 *    <li>ZIP,</li>
 *    <li>COUNTRY </li>
 *  </ul>
 *  
 * @param customer - The Customer.accountNo of the customer we want to find
 * @param billNo -  A bill number (e.g. B1-1111) OR bill line item number (e.g. B1-111,5) of the customer we want to find  - remove everything after the comma
 * @param orderNo - An order number (e.g. 200000011111) OR order line item number (e.g. 200000011111,5) of the customer we want to find - remove everything after the comma
 * @param pson - A BRM trans_id number (Paymenttech merchant number) of the payment event
 * 
 * @return A  Customer object
 * 
 */
@Override
public Customer getCustomerInfo(String customer, String billNo, String orderNo, String pson)
        throws JSONException {

    Customer c = null;

    String query = "begin ? := billing_inquiry.fn_get_customer( ?, ?, ?, ? ); end;";

    Connection conn = null;
    ResultSet rs = null;

    // DB Connection
    try {
        conn = this.getConnection();
    } catch (SQLException e) {
        throw JSONException.sqlError(e);
    } catch (NamingException e) {
        throw JSONException.namingError(e.toString());
    }

    try {

        CallableStatement stmt = conn.prepareCall(query);
        stmt.registerOutParameter(1, OracleTypes.CURSOR);
        stmt.setString(2, customer);
        stmt.setString(3, billNo);
        stmt.setString(4, orderNo);
        stmt.setString(5, pson);

        stmt.execute();
        rs = (ResultSet) stmt.getObject(1);

        while (rs.next()) {

            c = new Customer();
            c.setAccountNo(rs.getString("ACCOUNT_NO"));
            c.setFirstName(rs.getString("FIRST_NAME"));
            c.setLastName(rs.getString("LAST_NAME"));
            c.setCompany(rs.getString("COMPANY"));
            c.setPhone(rs.getString("PHONE"));
            c.setAddress(rs.getString("ADDRESS"));
            c.setCity(rs.getString("CITY"));
            c.setState(rs.getString("STATE"));
            c.setZip(rs.getString("ZIP"));
            c.setCountry(rs.getString("COUNTRY"));
            break;
        }
        conn.close();
        rs.close();

    } catch (SQLException e) {
        throw JSONException.sqlError(e);
    }

    if (c == null) {
        throw JSONException.noDataFound("Null set returned - no data found");
    }

    return c;
}

From source file:net.sourceforge.msscodefactory.cfasterisk.v2_1.CFAstOracle.CFAstOracleSecDeviceTable.java

public void deleteSecDevice(CFAstAuthorization Authorization, CFAstSecDeviceBuff Buff) {
    final String S_ProcName = "deleteSecDevice";
    Connection cnx = schema.getCnx();
    CallableStatement stmtDeleteByPKey = null;
    try {// w ww.  ja  va2s  .co  m
        UUID SecUserId = Buff.getRequiredSecUserId();
        String DevName = Buff.getRequiredDevName();
        stmtDeleteByPKey = cnx.prepareCall("begin " + schema.getLowerDbSchemaName()
                + ".dl_secdev( ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?" + " ); end;");
        int argIdx = 1;
        stmtDeleteByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtDeleteByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtDeleteByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtDeleteByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtDeleteByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtDeleteByPKey.setString(argIdx++, SecUserId.toString());
        stmtDeleteByPKey.setString(argIdx++, DevName);
        stmtDeleteByPKey.setInt(argIdx++, Buff.getRequiredRevision());
        ;
        stmtDeleteByPKey.execute();
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (stmtDeleteByPKey != null) {
            try {
                stmtDeleteByPKey.close();
            } catch (SQLException e) {
            }
            stmtDeleteByPKey = null;
        }
    }
}

From source file:net.sourceforge.msscodefactory.cffreeswitch.v2_1.CFFswOracle.CFFswOracleSecDeviceTable.java

public void deleteSecDevice(CFFswAuthorization Authorization, CFFswSecDeviceBuff Buff) {
    final String S_ProcName = "deleteSecDevice";
    Connection cnx = schema.getCnx();
    CallableStatement stmtDeleteByPKey = null;
    try {//from   w w w  . j  a v  a  2s  .co m
        UUID SecUserId = Buff.getRequiredSecUserId();
        String DevName = Buff.getRequiredDevName();
        stmtDeleteByPKey = cnx.prepareCall("begin " + schema.getLowerDbSchemaName()
                + ".dl_secdev( ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?" + " ); end;");
        int argIdx = 1;
        stmtDeleteByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtDeleteByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtDeleteByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtDeleteByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtDeleteByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtDeleteByPKey.setString(argIdx++, SecUserId.toString());
        stmtDeleteByPKey.setString(argIdx++, DevName);
        stmtDeleteByPKey.setInt(argIdx++, Buff.getRequiredRevision());
        ;
        stmtDeleteByPKey.execute();
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (stmtDeleteByPKey != null) {
            try {
                stmtDeleteByPKey.close();
            } catch (SQLException e) {
            }
            stmtDeleteByPKey = null;
        }
    }
}

From source file:net.sourceforge.msscodefactory.cfasterisk.v2_4.CFAsteriskOracle.CFAsteriskOracleISOCountryCurrencyTable.java

public void createISOCountryCurrency(CFSecurityAuthorization Authorization,
        CFSecurityISOCountryCurrencyBuff Buff) {
    final String S_ProcName = "createISOCountryCurrency";
    if (!schema.isTransactionOpen()) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName,
                "Transaction not open");
    }/*  w w w . j  a  v a2 s .c  o  m*/
    ResultSet resultSet = null;
    CallableStatement stmtCreateByPKey = null;
    try {
        short ISOCountryId = Buff.getRequiredISOCountryId();
        short ISOCurrencyId = Buff.getRequiredISOCurrencyId();
        Connection cnx = schema.getCnx();
        stmtCreateByPKey = cnx.prepareCall("begin " + schema.getLowerDbSchemaName()
                + ".crt_iso_cntryccy( ?, ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + " ); 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++, "ICCY");
        stmtCreateByPKey.setShort(argIdx++, ISOCountryId);
        stmtCreateByPKey.setShort(argIdx++, ISOCurrencyId);
        stmtCreateByPKey.execute();
        resultSet = (ResultSet) stmtCreateByPKey.getObject(1);
        if (resultSet == null) {
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "crt_iso_cntryccy() did not return a result set");
        }
        try {
            if (resultSet.next()) {
                CFSecurityISOCountryCurrencyBuff createdBuff = unpackISOCountryCurrencyResultSetToBuff(
                        resultSet);
                if (resultSet.next()) {
                    resultSet.last();
                    throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                            "Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
                }
                Buff.setRequiredISOCountryId(createdBuff.getRequiredISOCountryId());
                Buff.setRequiredISOCurrencyId(createdBuff.getRequiredISOCurrencyId());
                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_iso_cntryccy() 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;
        }
    }
}