Example usage for java.sql CallableStatement getObject

Introduction

In this page you can find the example usage for java.sql CallableStatement getObject.

Prototype

Object getObject(String parameterName) throws SQLException;

Source Link

Document

Retrieves the value of a parameter as an Object in the Java programming language.

Usage

From source file:Main.java

public static void main(String[] args) throws Exception {
    // DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    final Connection c = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system",
            "manager");
    String plsql = " declare " + "    p_id varchar2(20) := null; " + "    l_rc sys_refcursor;" + " begin "
            + "    p_id := ?; " + "    ? := 'input parameter was = ' || p_id;" + "    open l_rc for "
            + "        select 1 id, 'abc' name from dual; " + "    ? := l_rc;" + " end;";

    CallableStatement cs = c.prepareCall(plsql);
    cs.setString(1, "12345");
    cs.registerOutParameter(2, Types.VARCHAR);
    //  cs.registerOutParameter(3, OracleTypes.CURSOR);

    cs.execute();/*  w ww.j  a v a 2s.  c o m*/

    System.out.println("Result = " + cs.getObject(2));

    ResultSet cursorResultSet = (ResultSet) cs.getObject(3);
    while (cursorResultSet.next()) {
        System.out.println(cursorResultSet.getInt(1) + " " + cursorResultSet.getString(2));
    }
    cs.close();
    c.close();
}

---

From source file:com.jaspersoft.jrx.query.PlSqlQueryExecuter.java

public JRDataSource createDatasource() throws JRException {
    JRDataSource dataSource = null;//from  www. jav a  2  s  . c om

    createStatement();

    if (statement != null) {
        try {
            Integer reportMaxCount = (Integer) getParameterValue(JRParameter.REPORT_MAX_COUNT);
            if (reportMaxCount != null) {
                statement.setMaxRows(reportMaxCount.intValue());
            }

            if (isStoredProcedure) {
                CallableStatement cstmt = (CallableStatement) statement;
                cstmt.execute();
                if (cursorParameter > 0) {
                    resultSet = (java.sql.ResultSet) cstmt.getObject(cursorParameter);
                }
            } else {
                resultSet = statement.executeQuery();
            }

            dataSource = new JRResultSetDataSource(resultSet);
        } catch (SQLException e) {
            throw new JRException("Error executing SQL statement for : " + dataset.getName(), e);
        }
    }

    return dataSource;
}

---

From source file:com.adaptris.jdbc.StoredProcedure.java

private void applyOutParameters(CallableStatement statement) throws SQLException {
    for (StoredProcedureParameter param : getParameters()) {
        if (param.getParameterType().equals(ParameterType.OUT)
                || param.getParameterType().equals(ParameterType.INOUT)) {
            if (!isEmpty(param.getName())) {
                param.setOutValue(statement.getObject(param.getName()));
                log.debug("Receiving 'OUT' parameter with name '" + param.getName() + "' and value '"
                        + param.getOutValue() + "'");
            } else {
                param.setOutValue(statement.getObject(param.getOrder()));
                log.debug("Receiving 'OUT' parameter with order '" + param.getOrder() + "' and value '"
                        + param.getOutValue() + "'");
            }//from  ww  w.  java2  s .c o  m
        }
    }
}

---

From source file:com.aw.core.dao.DAOSql.java

/**
 * Call example//  w ww. java 2  s  . c  o  m
 * execSqlFunction(sqlUpdSqldoActor, Types.NUMERIC, new Object[]{"dss"})
 */
public Object execSqlFunction(String sql, int returnSqlType, Object[] sqlParams) {
    try {
        CallableStatement cstmt = getHibernateConnection().prepareCall(sql);
        cstmt.registerOutParameter(1, returnSqlType);
        if (sqlParams != null)
            for (int i = 0; i < sqlParams.length; i++) {
                cstmt.setObject(i + 2, sqlParams[i]);
            }
        logger.debug("SQL Exec:" + buildSQL(sql, sqlParams));
        cstmt.execute();
        Object returnValue = cstmt.getObject(1);
        cstmt.close();
        return returnValue;
    } catch (SQLException e) {
        logger.error("SQL:" + buildSQL(sql, sqlParams), e);
        throw AWBusinessException.wrapUnhandledException(logger, e);
    }
}

---

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

/**
 * PaymentInfoEFT//  www  . j  a va2  s. c  o m
 * 
 * <p/>
 * <p/>
 * <b>DATABASE PROCEDURE:</b>
 *  
 * @code
 *FUNCTION fn_get_payinfo_eft
 * (
 *    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>BANK_ACCOUNT_NO,</li>
 *    <li>BANK_NUMBER,</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 PaymentInfoEFT getPayinfoEFT(String itemNo) throws JSONException {

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

    Connection conn = null;
    PaymentInfoEFT c = 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, itemNo);

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

        while (rs.next()) {

            c = new PaymentInfoEFT();
            c.setBillingProfile(rs.getString("BILLING_PROFILE_ID"));
            c.setProfileBdom(rs.getInt("BDOM"));
            c.setCustomerName(rs.getString("PAYINFO_NAME"));
            c.setBankAccountNumber(rs.getString("BANK_ACCOUNT_NO"));
            c.setBankNumber(rs.getString("BANK_NUMBER"));
        }

        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:com.intuit.it.billing.data.BillingDAOImpl.java

/**
 * PaymentInfoCheck//from w  w w.  j  a  va2 s . c o  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:com.intuit.it.billing.data.BillingDAOImpl.java

/**
  * @section dao_section class BillingDAOImpl
 * getAllocation//from  w  w w.  jav  a 2s  .co  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_item_allocations(
 *           item_no IN VARCHAR2 )
 *        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. 
 * @throws JSONException 
 * 
 * 
 * 
 */
@Override
public List<Allocation> getAllocation(String itemNo) throws JSONException {

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

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

    ResultSet rs = null;
    Connection conn = 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()) {

            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

/**
 * PaymentInfoCC/*from   ww  w  . jav a2 s.c o m*/
 * 
 * <p/>
 * <p/>
 * <b>DATABASE PROCEDURE:</b>
 *  
 * @code
 *FUNCTION fn_get_payinfo_cc
 * (
 *    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>CC_NUMBER,</li>
 *    <li>EXPIRE_MM,</li>
 *    <li>EXPIRE_YYYY,</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
 * 
 * TODO 
 * Decide how to determine the card type from the token
 * 
 */
@Override
public PaymentInfoCC getPayinfoCC(String itemNo) throws JSONException {
    // TODO Auto-generated method stub

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

    Connection conn = null;
    ResultSet rs = null;
    PaymentInfoCC 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 PaymentInfoCC();
            c.setBillingProfile(rs.getString("BILLING_PROFILE_ID"));
            c.setProfileBdom(rs.getInt("BDOM"));
            c.setCardholderName(rs.getString("PAYINFO_NAME"));
            c.setCcNumber(rs.getString("CC_NUMBER"));
            c.setExpiryMonth(rs.getString("EXPIRE_MM"));
            c.setExpiryYear(rs.getString("EXPIRE_YYYY"));
            c.setCardType("TODO");
        }

        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:com.rosy.bill.dao.hibernate.SimpleHibernateDao.java

/**
 * ?String/*from www  .  j a  v a 2 s  . co  m*/
 * @param execStr
 * @param params
 * @param outIndex
 * @return
 */
@SuppressWarnings("deprecation")
public String getNumByExecuteProc(String execStr, String[] params, int outIndex) {
    java.sql.CallableStatement cstmt = null;
    String rtn = null;
    try {
        cstmt = this.getSession().connection().prepareCall(execStr);
        cstmt.registerOutParameter(outIndex, oracle.jdbc.driver.OracleTypes.LONGVARCHAR);
        if (params != null && params.length > 0) {
            for (int i = 0; i < params.length; i++) {
                if (i != outIndex) {
                    cstmt.setString(i, params[i].toString());
                }
            }
        }
        cstmt.executeQuery();
        java.sql.ResultSet rs = (java.sql.ResultSet) cstmt.getObject(outIndex);
        if (rs != null) {
            rtn = rs.getString(0);
        }
    } catch (HibernateException e1) {
        e1.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return rtn;
}

---

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

/**
 * getAllocationsList//w ww. java  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;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »