Example usage for java.sql Connection prepareCall

List of usage examples for java.sql Connection prepareCall

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Connection
  6. 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:com.aw.core.dao.DAOSql.java

protected void dbmsOutputPrint(Connection conn, StringBuffer buf) throws java.sql.SQLException {
    String getLineSql = "begin dbms_output.get_line(?,?); end;";
    CallableStatement stmt = conn.prepareCall(getLineSql);
    boolean hasMore = true;
    stmt.registerOutParameter(1, Types.VARCHAR);
    stmt.registerOutParameter(2, Types.INTEGER);
    while (hasMore) {
        boolean status = stmt.execute();
        hasMore = (stmt.getInt(2) == 0);
        if (hasMore) {
            buf.append(stmt.getString(1)).append("\n");
        }//from  www  .ja  v a 2s.  c om
    }
    stmt.close();
}

From source file:edu.harvard.i2b2.crc.dao.setfinder.QueryInstanceSpringDao.java

/**
 * Update query instance message// w  w  w.j  av  a 2s  . c  o m
 * 
 * @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) });

}

From source file:com.taobao.diamond.server.service.HangingModePersistService.java

/**
 * Returns a callable statement//from w w  w  .  ja va2s .  c  om
 * 
 * @param conn
 *            Connection to use to create statement
 * @return cs A callable statement
 */
public CallableStatement createCallableStatement(Connection conn) {
    StringBuffer storedProcName = new StringBuffer("call ");
    storedProcName.append(storedProc + "(");
    // set output parameters
    storedProcName.append("?");
    storedProcName.append(")");

    CallableStatement cs = null;
    try {
        // set the first parameter is OracleTyep.CURSOR for oracel stored
        // procedure
        cs = conn.prepareCall(storedProcName.toString());
        cs.setInt(1, sleepTime);
    } catch (SQLException e) {
        throw new RuntimeException("createCallableStatement method Error : SQLException " + e.getMessage());
    }
    return cs;
}

From source file:com.netspective.axiom.sql.StoredProcedure.java

/**
 * NOTE: When using the batch update facility, a CallableStatement object can call only stored
 * procedures that take input parameters or no parameters at all. Further, the stored procedure
 * must return an update count. The CallableStatement.executeBatch method
 * (inherited from PreparedStatement) will throw a BatchUpdateException if the stored procedure
 * returns anything other than an update count or takes OUT or INOUT parameters.
 *///from ww  w .  j a  v a 2s  . com
protected int[] batchExecute(ConnectionContext cc) throws SQLException, NamingException {
    // TODO: This method NEEDS to be tested!
    Connection conn;
    CallableStatement stmt;

    conn = cc.getConnection();
    String sql = StringUtils.strip(getSqlText(cc));

    stmt = conn.prepareCall(sql);
    // TODO: parameters must do addBatch() calles!!!
    if (parameters != null)
        parameters.apply(cc, stmt);

    return stmt.executeBatch();

}

From source file:com.cws.esolutions.security.dao.userauth.impl.SQLAuthenticator.java

/**
 * @see com.cws.esolutions.security.dao.userauth.interfaces.Authenticator#verifySecurityData(java.lang.String, java.lang.String, java.util.List)
 *//*from   ww w. j  av a2s.  c  om*/
public synchronized boolean verifySecurityData(final String userId, final String userGuid,
        final List<String> attributes) throws AuthenticatorException {
    final String methodName = SQLAuthenticator.CNAME
            + "#verifySecurityData(final String userId, final String userGuid, final List<String> attributes) throws AuthenticatorException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("Value: {}", userId);
        DEBUGGER.debug("Value: {}", userGuid);
    }

    Connection sqlConn = null;
    CallableStatement stmt = null;

    try {
        sqlConn = SQLAuthenticator.dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);

        stmt = sqlConn.prepareCall("{CALL verifySecurityQuestions(?, ?, ?, ?)}");
        stmt.setString(1, userGuid); // guid
        stmt.setString(2, userId);
        stmt.setString(3, attributes.get(0)); // username
        stmt.setString(4, attributes.get(1)); // username

        if (DEBUG) {
            DEBUGGER.debug("Statement: {}", stmt.toString());
        }

        return stmt.execute();
    } catch (SQLException sqx) {
        throw new AuthenticatorException(sqx.getMessage(), sqx);
    } finally {
        try {
            if (stmt != null) {
                stmt.close();
            }

            if (!(sqlConn == null) && (!(sqlConn.isClosed()))) {
                sqlConn.close();
            }
        } catch (SQLException sqx) {
            throw new AuthenticatorException(sqx.getMessage(), sqx);
        }
    }
}

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

@SuppressWarnings("deprecation")
public String callProc(final String proc, final List<Object> paramList, final int outIndex, final int outType) {
    String result = null;//from  w  w  w.  j  a  va2s. c om
    java.sql.Connection conn = null;
    java.sql.CallableStatement cstmt = null;
    //Session session = this.getSession();
    try {

        conn = this.getSession().connection();
        conn.setAutoCommit(false);
        cstmt = conn.prepareCall(proc);
        for (int i = 0; paramList != null && i < paramList.size(); i++) {
            if (i + 1 == outIndex) {
                //cstmt.setInt(i + 1,
                //      (Integer.parseInt(paramList.get(i).toString())));
                cstmt.setString(i + 1, paramList.get(i).toString());
            } else {
                cstmt.setInt(i + 1, Integer.valueOf(paramList.get(i).toString()));
            }
        }
        cstmt.registerOutParameter(outIndex, outType);
        cstmt.execute();
        result = cstmt.getString(outIndex);
        conn.commit();
        //session.flush();
        //session.clear();
    } catch (Exception ex) {
        try {
            conn.rollback();
        } catch (SQLException e1) {
            logger.error("[" + proc + "]?" + e1.getMessage());
            e1.printStackTrace();
        }
        ex.printStackTrace();
    } finally {
        if (cstmt != null) {
            try {
                cstmt.close();
            } catch (Exception ex) {
            }
        }
    }
    return result;
}

From source file:com.cws.us.pws.dao.impl.CareersReferenceDAOImpl.java

/**
 * @see com.cws.us.pws.dao.interfaces.ICareersReferenceDAO#getCareerData(String, String) throws SQLException
 *//* w  w w.j  a v a 2 s. c  o  m*/
@Override
public List<Object> getCareerData(final String reqId, final String lang) throws SQLException {
    final String methodName = ICareersReferenceDAO.CNAME
            + "#getCareerData(final int reqId, final String lang) throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("Value: {}", reqId);
        DEBUGGER.debug("Value: {}", lang);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    List<Object> results = null;
    CallableStatement stmt = null;

    try {
        sqlConn = this.dataSource.getConnection();

        if (DEBUG) {
            DEBUGGER.debug("Connection: {}", sqlConn);
        }

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain connection to application datasource");
        }

        stmt = sqlConn.prepareCall("{ CALL getCareerData(?, ?) }");
        stmt.setString(1, reqId);
        stmt.setString(2, lang);

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (!(stmt.execute())) {
            throw new SQLException("PreparedStatement is null. Cannot execute.");
        }

        resultSet = stmt.getResultSet();

        if (DEBUG) {
            DEBUGGER.debug("ResultSet: {}", resultSet);
        }

        if (resultSet.next()) {
            resultSet.beforeFirst();
            results = new ArrayList<Object>();

            while (resultSet.next()) {
                results.add(resultSet.getString(1)); // REQ_ID
                results.add(resultSet.getDate(2)); // POST_DATE
                results.add(resultSet.getDate(3)); // UNPOST_DATE
                results.add(resultSet.getString(4)); // JOB_TITLE
                results.add(resultSet.getString(5)); // JOB_SHORT_DESC
                results.add(resultSet.getString(6)); // JOB_DESCRIPTION
            }

            if (DEBUG) {
                DEBUGGER.debug("results: {}", results);
            }
        }
    } catch (SQLException sqx) {
        ERROR_RECORDER.error(sqx.getMessage(), sqx);

        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (resultSet != null) {
            resultSet.close();
        }

        if (stmt != null) {
            stmt.close();
        }

        if ((sqlConn != null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    if (DEBUG) {
        DEBUGGER.debug("results: {}", results);
    }

    return results;
}

From source file:com.cws.us.pws.dao.impl.CareersReferenceDAOImpl.java

/**
 * @see com.cws.us.pws.dao.interfaces.ICareersReferenceDAO#getCareerList(String) throws SQLException
 *///  ww w  . j  av  a  2  s  .c om
@Override
public List<Object[]> getCareerList(final String lang) throws SQLException {
    final String methodName = ICareersReferenceDAO.CNAME
            + "#getCareerList(final String lang) throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("Value: {}", lang);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<Object[]> results = null;

    try {
        sqlConn = this.dataSource.getConnection();

        if (DEBUG) {
            DEBUGGER.debug("Connection: {}", sqlConn);
        }

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain connection to application datasource");
        }

        stmt = sqlConn.prepareCall("{ CALL getCareersList(?) }");
        stmt.setString(1, lang);

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (!(stmt.execute())) {
            throw new SQLException("PreparedStatement is null. Cannot execute.");
        }

        resultSet = stmt.getResultSet();

        if (DEBUG) {
            DEBUGGER.debug("ResultSet: {}", resultSet);
        }

        if (resultSet.next()) {
            resultSet.beforeFirst();
            results = new ArrayList<Object[]>();

            while (resultSet.next()) {
                Object[] data = new Object[] { resultSet.getString(1), // REQ_ID
                        resultSet.getString(2), // POST_DATE
                        resultSet.getString(3), // UNPOST_DATE
                        resultSet.getString(4), // JOB_TITLE
                        resultSet.getBigDecimal(5), // JOB_SHORT_DESC
                        resultSet.getString(6), // JOB_DESCRIPTION
                };

                results.add(data);
            }

            if (DEBUG) {
                DEBUGGER.debug("results: {}", results);
            }
        }
    } catch (SQLException sqx) {
        ERROR_RECORDER.error(sqx.getMessage(), sqx);

        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (resultSet != null) {
            resultSet.close();
        }

        if (stmt != null) {
            stmt.close();
        }

        if ((sqlConn != null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    if (DEBUG) {
        DEBUGGER.debug("results: {}", results);
    }

    return results;
}

From source file:com.gnadenheimer.mg.utils.Utils.java

public Boolean exectueBackUp(String backupDirectory) {
    try {//w  w w.ja v a 2s .co  m
        //SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss");
        //String backupfile = backupDirectory + "\\BackUp_" + sdf.format(new LocalDateTime());
        String backupfile = backupDirectory + "\\BackUp_"
                + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd-HH-mm-ss"));
        Connection conn = getDatabaseConnection();

        try (CallableStatement cs = conn.prepareCall("CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)")) {
            cs.setString(1, backupfile);
            cs.execute();
            cs.close();
        } catch (Exception ex) {
            LOGGER.error(Thread.currentThread().getStackTrace()[1].getMethodName(), ex);
            JOptionPane.showMessageDialog(null,
                    Thread.currentThread().getStackTrace()[1].getMethodName() + " - " + ex.getMessage());
        }
        JOptionPane.showMessageDialog(null, "BackUp guardado con exito en: " + backupfile);
        return true;
    } catch (Exception ex) {
        LOGGER.error(Thread.currentThread().getStackTrace()[1].getMethodName(), ex);
        JOptionPane.showMessageDialog(null,
                Thread.currentThread().getStackTrace()[1].getMethodName() + " - " + ex.getMessage());
        return false;
    }
}

From source file:com.cws.esolutions.security.dao.reference.impl.SecurityReferenceDAOImpl.java

/**
 * @see com.cws.esolutions.security.dao.reference.interfaces.ISecurityReferenceDAO#listAvailableServices()
 *//*from   w w w.ja v a  2s .  c  o  m*/
public synchronized Map<String, String> listAvailableServices() throws SQLException {
    final String methodName = ISecurityReferenceDAO.CNAME + "#listAvailableServices() throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    Map<String, String> serviceMap = null;

    try {
        sqlConn = dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);
        stmt = sqlConn.prepareCall("{CALL retrAvailableServices()}");

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (stmt.execute()) {
            resultSet = stmt.getResultSet();

            if (DEBUG) {
                DEBUGGER.debug("ResultSet: {}", resultSet);
            }

            if (resultSet.next()) {
                resultSet.beforeFirst();
                serviceMap = new HashMap<String, String>();

                while (resultSet.next()) {
                    serviceMap.put(resultSet.getString(1), resultSet.getString(2));
                }

                if (DEBUG) {
                    DEBUGGER.debug("Map<String, String>: {}", serviceMap);
                }
            }
        }
    } catch (SQLException sqx) {
        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (resultSet != null) {
            resultSet.close();
        }

        if (stmt != null) {
            stmt.close();
        }

        if ((sqlConn != null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    return serviceMap;
}