Example usage for java.sql CallableStatement registerOutParameter

List of usage examples for java.sql CallableStatement registerOutParameter

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. CallableStatement
  6. registerOutParameter

Introduction

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

Prototype

default void registerOutParameter(String parameterName, SQLType sqlType) throws SQLException

Source Link

Document

Registers the OUT parameter named parameterName to the JDBC type sqlType .

Usage

From source file:com.mobilewallet.common.dao.ForgotPasswordDAO.java

public int resetPassword(String uuid, String userId, String password, String ip) {
    Connection connection = null;
    CallableStatement cstmt = null;
    int rvalue = -1;
    try {//from w  w w . j  a  va2s .c  om
        connection = dataSource.getConnection();
        cstmt = connection.prepareCall("{call wp_reset_pwd(?,?,?,?,?)}");
        cstmt.setString(1, userId);
        cstmt.setString(2, uuid);
        cstmt.setString(3, password);
        cstmt.setString(4, ip);
        cstmt.registerOutParameter(5, java.sql.Types.INTEGER);

        cstmt.execute();

        rvalue = cstmt.getInt(5);

    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {

        try {
            if (cstmt != null) {
                cstmt.close();
            }
        } catch (Exception ex) {

        }
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (Exception ex) {

        }
    }
    return rvalue;
}

From source file:org.apache.lucene.store.jdbc.JdbcDirectory.java

/**
 * Delets all the file entries that are marked to be deleted, and they were marked
 * "delta" time ago (base on database time, if possible by dialect).
 *//*from  w  ww. j a  v  a 2s.co  m*/
public void deleteMarkDeleted(long delta) throws IOException {
    long currentTime = System.currentTimeMillis();
    if (dialect.supportsCurrentTimestampSelection()) {
        String timestampSelectString = dialect.getCurrentTimestampSelectString();
        if (dialect.isCurrentTimestampSelectStringCallable()) {
            currentTime = ((Long) jdbcTemplate.executeCallable(timestampSelectString,
                    new JdbcTemplate.CallableStatementCallback() {
                        public void fillCallableStatement(CallableStatement cs) throws Exception {
                            cs.registerOutParameter(1, java.sql.Types.TIMESTAMP);
                        }

                        public Object readCallableData(CallableStatement cs) throws Exception {
                            Timestamp timestamp = cs.getTimestamp(1);
                            return new Long(timestamp.getTime());
                        }
                    })).longValue();
        } else {
            currentTime = ((Long) jdbcTemplate.executeSelect(timestampSelectString,
                    new JdbcTemplate.ExecuteSelectCallback() {
                        public void fillPrepareStatement(PreparedStatement ps) throws Exception {
                            // nothing to do here
                        }

                        public Object execute(ResultSet rs) throws Exception {
                            rs.next();
                            Timestamp timestamp = rs.getTimestamp(1);
                            return new Long(timestamp.getTime());
                        }
                    })).longValue();
        }
    }
    final long deleteBefore = currentTime - delta;
    jdbcTemplate.executeUpdate(table.sqlDeletaMarkDeleteByDelta(),
            new JdbcTemplate.PrepateStatementAwareCallback() {
                public void fillPrepareStatement(PreparedStatement ps) throws Exception {
                    ps.setBoolean(1, true);
                    ps.setTimestamp(2, new Timestamp(deleteBefore));
                }
            });
}

From source file:com.mobilewallet.common.dao.ForgotPasswordDAO.java

public Object[] getResetPasswordLink(String email, String uuid, String ip) {
    Connection connection = null;
    CallableStatement cstmt = null;
    Object[] obj = null;/*from   w ww  . j ava2s.  c om*/
    int rvalue = -1;
    long userId = 0;
    try {
        connection = dataSource.getConnection();
        cstmt = connection.prepareCall("{call wp_forgot_pwd_reset_link(?,?,?,?,?)}");
        cstmt.setString(1, email);
        cstmt.setString(2, uuid);
        cstmt.setString(3, ip);
        cstmt.registerOutParameter(4, java.sql.Types.INTEGER);
        cstmt.registerOutParameter(5, java.sql.Types.INTEGER);

        cstmt.execute();

        rvalue = cstmt.getInt(4);
        userId = cstmt.getLong(5);

        obj = new Object[2];
        obj[0] = rvalue;
        obj[1] = userId;
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {

        try {
            if (cstmt != null) {
                cstmt.close();
            }
        } catch (Exception ex) {

        }
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (Exception ex) {

        }
    }
    return obj;
}

From source file:de.awtools.grooocle.varray.VarrayTest.java

@Test
public void testOraclesVarray() throws Exception {
    CallableStatement cs = null;
    try {/*from ww w  .  ja  va 2s . co m*/
        String arrayElements[] = { "Test3", "Test4", "Test5" };
        ArrayDescriptor desc = ArrayDescriptor.createDescriptor("T_STRING_VARRAY", conn);
        ARRAY newArray = new ARRAY(desc, conn, arrayElements);

        String spCall = "{ call call_me(?, ?) }";
        cs = conn.prepareCall(spCall);
        cs.setArray(1, newArray);
        cs.registerOutParameter(2, java.sql.Types.INTEGER);

        cs.execute();
        assertEquals(3, cs.getInt(2));
    } finally {
        if (cs != null) {
            cs.close();
        }
    }

}

From source file:com.mobilewallet.credits.dao.CreditsDAO.java

public int updateCredits(long userId, String isCorrect, int position) {
    int updated = 0;
    Connection connection = null;
    CallableStatement cstmt = null;
    try {/* www . j a va 2s. c  o  m*/
        connection = dataSource.getConnection();
        cstmt = connection.prepareCall("{call update_credits_proc(?,?,?,?)}");
        cstmt.setLong(1, userId);
        cstmt.setString(2, isCorrect);
        cstmt.setInt(3, position);
        cstmt.registerOutParameter(4, java.sql.Types.INTEGER);
        cstmt.execute();

        updated = cstmt.getInt(4);
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {

        try {
            if (cstmt != null) {
                cstmt.close();
            }
        } catch (Exception ex) {

        }
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (Exception ex) {

        }
    }
    return updated;
}

From source file:com.oracle.tutorial.jdbc.StoredProcedureJavaDBSample.java

public void runStoredProcedures(String coffeeNameArg, double maximumPercentageArg, double newPriceArg)
        throws SQLException {
    CallableStatement cs = null;

    try {//w  ww .  ja va  2 s. c  o  m

        System.out.println("\nCalling the stored procedure GET_SUPPLIER_OF_COFFEE");
        cs = this.con.prepareCall("{call GET_SUPPLIER_OF_COFFEE(?, ?)}");
        cs.setString(1, coffeeNameArg);
        cs.registerOutParameter(2, Types.VARCHAR);
        cs.execute();

        String supplierName = cs.getString(2);

        if (supplierName != null) {
            System.out.println("\nSupplier of the coffee " + coffeeNameArg + ": " + supplierName);
        } else {
            System.out.println("\nUnable to find the coffee " + coffeeNameArg);
        }

        System.out.println("\nCalling the procedure SHOW_SUPPLIERS");
        cs = this.con.prepareCall("{call SHOW_SUPPLIERS()}");
        ResultSet rs = cs.executeQuery();

        while (rs.next()) {
            String supplier = rs.getString("SUP_NAME");
            String coffee = rs.getString("COF_NAME");
            System.out.println(supplier + ": " + coffee);
        }

        System.out.println("\nContents of COFFEES table before calling RAISE_PRICE:");
        CoffeesTable.viewTable(this.con);

        System.out.println("\nCalling the procedure RAISE_PRICE");
        cs = this.con.prepareCall("{call RAISE_PRICE(?,?,?)}");
        cs.setString(1, coffeeNameArg);
        cs.setDouble(2, maximumPercentageArg);
        cs.registerOutParameter(3, Types.DOUBLE);
        cs.setDouble(3, newPriceArg);

        cs.execute();

        System.out.println("\nValue of newPrice after calling RAISE_PRICE: " + cs.getFloat(3));

        System.out.println("\nContents of COFFEES table after calling RAISE_PRICE:");
        CoffeesTable.viewTable(this.con);

    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (cs != null) {
            cs.close();
        }
    }
}

From source file:com.mobilewallet.common.dao.ReferralIncentiveDAO.java

public Object[] addReferralIncetive(long userId, String refCode, String imei, String ip) {
    int added = 0;
    Connection connection = null;
    CallableStatement cstmt = null;
    String gcmId = null;/*from   w  ww  .  java  2 s  . co  m*/
    Object[] obj = null;
    try {
        connection = dataSource.getConnection();
        cstmt = connection.prepareCall("{call ADD_REFERRAL_CREDIT(?,?,?,?,?,?,?)}");
        cstmt.setLong(1, userId);
        cstmt.setString(2, refCode);
        cstmt.setString(3, imei);
        cstmt.setString(4, ip);
        cstmt.registerOutParameter(5, java.sql.Types.INTEGER);
        cstmt.registerOutParameter(6, java.sql.Types.VARCHAR);
        cstmt.registerOutParameter(7, java.sql.Types.VARCHAR);

        cstmt.execute();

        added = cstmt.getInt(5);
        gcmId = cstmt.getString(6);
        obj = new Object[3];
        obj[0] = added;
        obj[1] = gcmId;
        obj[2] = cstmt.getString(7);
    } catch (Exception ex) {
        ex.printStackTrace();
        log.error("Error in add referral incentive dao : " + ex.getMessage() + ", USER ID " + userId
                + ", refCode : " + refCode + ", imei : " + imei + ", IP : " + ip);
    } finally {
        try {
            if (cstmt != null) {
                cstmt.close();
            }
        } catch (Exception ex) {

        }
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (Exception ex) {

        }
    }

    return obj;
}

From source file:com.mobilewallet.admin.dao.QuestionDAO.java

public int approveQuestion(long userId, long q_id, String is_admin_approved) {
    Connection connection = null;
    CallableStatement pstmt = null;
    ResultSet rs = null;/*from  w  ww .  j  a  va2 s .c o  m*/
    int approved = 0;
    try {
        connection = dataSource.getConnection();
        pstmt = connection.prepareCall("{call approve_question(?,?,?,?)}");
        pstmt.setLong(1, userId);
        pstmt.setLong(2, q_id);
        pstmt.setString(3, is_admin_approved);

        pstmt.registerOutParameter(4, java.sql.Types.INTEGER);
        pstmt.execute();
        approved = pstmt.getInt(4);
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {

        try {
            if (rs != null) {
                rs.close();
            }
        } catch (Exception ex) {

        }
        try {
            if (pstmt != null) {
                pstmt.close();
            }
        } catch (Exception ex) {

        }
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (Exception ex) {

        }
    }
    return approved;
}

From source file:com.rosy.bill.dao.hibernate.SimpleHibernateDao.java

/**
 * ?String/*from w  ww .  ja  v  a  2 s . c o 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:edu.harvard.i2b2.crc.dao.setfinder.QueryInstanceSpringDao.java

/**
 * Update query instance message//from   w  w  w.  j a va  2  s  .c  om
 * 
 * @param queryInstanceId
 * @param message
 * @param appendMessageFlag
 * @return 
 */
public void updateMessage(String queryInstanceId, String message, boolean appendMessageFlag)
        throws I2B2DAOException {

    String messageUpdate = "";
    if (appendMessageFlag) {
        String concatOperator = "";
        if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE)) {
            concatOperator = "||";
            messageUpdate = " MESSAGE = nvl(MESSAGE,'') " + concatOperator + " ? ";
        } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)) {
            concatOperator = "||";
            messageUpdate = " MESSAGE = ? ";

        } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) {
            // Cast(notes as nvarchar(4000))
            //messageUpdate = " message.write (?, NULL, 0) ";

            Connection conn = null;
            try {
                conn = getDataSource().getConnection();
                CallableStatement callStmt = conn
                        .prepareCall("{call " + getDbSchemaName() + "UPDATE_QUERYINSTANCE_MESSAGE(?,?,?)}");
                callStmt.setString(1, message);
                callStmt.setString(2, queryInstanceId);
                callStmt.registerOutParameter(3, java.sql.Types.VARCHAR);
                // callStmt.setString(2, tempPatientMappingTableName);
                callStmt.execute();
                this.getSQLServerProcedureError(dataSourceLookup.getServerType(), callStmt, 3);
            } catch (SQLException sqlEx) {
                sqlEx.printStackTrace();
                throw new I2B2DAOException("SQLException occured" + sqlEx.getMessage(), sqlEx);
            } catch (Exception ex) {
                ex.printStackTrace();
                throw new I2B2DAOException("Exception occured" + ex.getMessage(), ex);
            } finally {
                if (conn != null) {
                    try {
                        conn.close();
                    } catch (SQLException sqlEx) {
                        sqlEx.printStackTrace();
                        log.error("Error while closing connection", sqlEx);
                    }
                }
            }
            return;
            //////

        }

    } else {
        messageUpdate = " MESSAGE = ?";
    }
    String sql = "UPDATE " + getDbSchemaName() + "QT_QUERY_INSTANCE set " + messageUpdate
            + " where query_instance_id = ? ";
    jdbcTemplate.update(sql, new Object[] {

            (message == null) ? "" : message, Integer.parseInt(queryInstanceId) });

}