Example usage for java.sql CallableStatement execute

List of usage examples for java.sql CallableStatement execute

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

Introduction

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

Prototype

boolean execute() throws SQLException;

Source Link

Document

Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement.

Usage

From source file:com.splicemachine.derby.utils.SpliceAdminIT.java

@Test
public void testGetSetLogLevel() throws Exception {
    String logger = "com.splicemachine.derby.iapi.sql.execute.SpliceOperationContext";
    String origLevel = "FRED";
    String newLogLevel = "INFO";
    CallableStatement cs = methodWatcher.prepareCall("call SYSCS_UTIL.SYSCS_GET_LOGGER_LEVEL(?)");
    cs.setString(1, logger);/*from   w ww . j av  a  2  s .c o  m*/
    ResultSet rs = cs.executeQuery();
    while (rs.next()) {
        origLevel = rs.getString(1);
    }

    try {
        cs = methodWatcher.prepareCall("call SYSCS_UTIL.SYSCS_SET_LOGGER_LEVEL(?,?)");
        cs.setString(1, logger);
        cs.setString(2, newLogLevel);
        cs.execute();

        cs = methodWatcher.prepareCall("call SYSCS_UTIL.SYSCS_GET_LOGGER_LEVEL(?)");
        cs.setString(1, logger);
        rs = cs.executeQuery();
        String currentLogLevel = "FRED";
        while (rs.next()) {
            currentLogLevel = rs.getString(1);
        }
        Assert.assertNotEquals("FRED", currentLogLevel);
        Assert.assertEquals(newLogLevel, currentLogLevel);
    } finally {
        // reset to orig value
        cs = methodWatcher.prepareCall("call SYSCS_UTIL.SYSCS_SET_LOGGER_LEVEL(?,?)");
        cs.setString(1, logger);
        cs.setString(2, origLevel);
        cs.execute();
    }

    DbUtils.closeQuietly(rs);

}

From source file:com.mobilewallet.users.dao.UserDAO.java

public int updateProfile(long userId, String mCode, String mobileNumber, String dob, String gender,
        String occupation, String income) {
    Connection connection = null;
    CallableStatement pstmt = null;
    ResultSet rs = null;/*  w w w.  ja v  a2  s . c  o  m*/
    int updated = 0;
    try {
        connection = dataSource.getConnection();
        pstmt = connection.prepareCall("{call UPDATE_PROFILE(?,?,?,?,?,?,?,?)}");
        pstmt.setLong(1, userId);
        pstmt.setString(2, mCode);
        pstmt.setString(3, mobileNumber);
        pstmt.setString(4, dob);
        pstmt.setString(5, gender);
        pstmt.setString(6, occupation);
        pstmt.setString(7, income);
        pstmt.registerOutParameter(8, java.sql.Types.INTEGER);
        pstmt.execute();
        updated = pstmt.getInt(8);
    } 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 updated;
}

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 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:Statement.Statement.java

private void load() {
    //Display dong Fix va Other Cost
    try {/*from ww  w.j a v a  2 s  . c o  m*/
        PreparedStatement st = cnn
                .prepareStatement("select Fixed,Other from Expense where ShopID = ? and Date = ?");
        st.setString(1, code);
        st.setString(2, date);
        ResultSet rs = st.executeQuery();

        while (rs.next()) {
            txtFix.setText(customFormat("VND ###,###,###", rs.getInt(1)));
            fix = rs.getInt(1);
            txtOther.setText(customFormat("VND ###,###,###", rs.getInt(2)));
            other = rs.getInt(2);
        }
    } catch (SQLException e) {
        System.err.println(e.getMessage());
    }
    //Display dong Revenue
    String query = "{call Revenue_Shop_Date(?,?,?)}";
    try {
        CallableStatement cst = cnn.prepareCall(query);
        cst.setString(1, code);
        cst.setString(2, date);
        cst.registerOutParameter(3, INTEGER);
        cst.execute();

        txtRev.setText(customFormat("VND ###,###,###", cst.getInt(3)));
        rev = cst.getInt(3);
    } catch (Exception e) {
    }
    //Display dong Profit
    cost = fix + other;
    profit = rev - cost;
    txtProfit.setText(customFormat("VND ###,###,###", profit));

    //Display comment
    try {

        PreparedStatement st1 = cnn.prepareStatement("select Goal from Shop where ShopID = ?");
        st1.setString(1, code);
        ResultSet rs1 = st1.executeQuery();

        while (rs1.next()) {
            int goal = rs1.getInt(1);
            if (rev >= goal) {
                comment.setText("Congrats! The Shop has achieved the goal");
            } else {
                comment.setText("The Shop has failed the goal");
            }
        }

    } catch (SQLException e) {
        System.err.println(e.getMessage());
    }
}

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

/**
 * @see com.cws.esolutions.security.dao.reference.interfaces.ISecurityReferenceDAO#obtainApprovedServers()
 *//*from  w ww  .j  av a  2s. c om*/
public synchronized List<String> obtainApprovedServers() throws SQLException {
    final String methodName = ISecurityReferenceDAO.CNAME + "#obtainApprovedServers() throws SQLException";

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

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<String> securityList = null;

    try {
        sqlConn = dataSource.getConnection();

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

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

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

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

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

            if (resultSet.next()) {
                resultSet.beforeFirst();

                securityList = new ArrayList<String>();

                while (resultSet.next()) {
                    if (DEBUG) {
                        DEBUGGER.debug(resultSet.getString(1));
                    }

                    // check if column is null
                    securityList.add(resultSet.getString(1));
                }

                if (DEBUG) {
                    DEBUGGER.debug("securityList: {}", securityList);
                }
            }
        }
    } 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 securityList;
}

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

/**
 * @see com.cws.esolutions.security.dao.reference.interfaces.ISecurityReferenceDAO#listAvailableServices()
 *///from www  .ja v  a 2s  .com
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;
}

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

/**
 * @see com.cws.esolutions.security.dao.reference.interfaces.ISecurityReferenceDAO#obtainSecurityQuestionList()
 */// ww  w  . ja  v a2  s.  c o m
public synchronized List<String> obtainSecurityQuestionList() throws SQLException {
    final String methodName = ISecurityReferenceDAO.CNAME + "#obtainSecurityQuestionList() throws SQLException";

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

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<String> questionList = null;

    try {
        sqlConn = dataSource.getConnection();

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

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

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

        if (stmt.execute()) {
            resultSet = stmt.getResultSet();
            resultSet.last();
            int iRowCount = resultSet.getRow();

            if (iRowCount == 0) {
                throw new SQLException("No security questions are currently configured.");
            }

            resultSet.first();
            ResultSetMetaData resultData = resultSet.getMetaData();

            int iColumns = resultData.getColumnCount();

            questionList = new ArrayList<String>();

            for (int x = 1; x < iColumns + 1; x++) {
                if (DEBUG) {
                    DEBUGGER.debug("resultSet.getObject: {}", resultSet.getObject(resultData.getColumnName(x)));
                }

                // check if column is null
                resultSet.getObject(resultData.getColumnName(x));

                // if the column was null, insert n/a, otherwise, insert the column's contents
                questionList.add((String) (resultSet.wasNull() ? "N/A"
                        : resultSet.getObject(resultData.getColumnName(x))));
            }
        }
    } 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 questionList;
}

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

public void registerJarFile(String jarPathName) throws SQLException {
    CallableStatement cs2 = null;
    CallableStatement cs2a = null;
    CallableStatement cs3 = null;

    String query2 = "CALL sqlj.install_jar('" + jarPathName + "','" + this.schema + ".JDBCTutorial',0)";
    String query2a = "CALL sqlj.replace_jar('" + jarPathName + "','" + this.schema + ".JDBCTutorial')";
    String query3 = "CALL syscs_util.syscs_set_database_property('derby.database.classpath','" + this.schema
            + ".JDBCTutorial')";

    try {// ww w .  ja v  a 2 s .  c  om
        System.out.println("Calling " + query2);
        cs2 = con.prepareCall(query2);
        cs2.execute();
    } catch (SQLException e2) {
        JDBCTutorialUtilities.printSQLException(e2);
    } finally {
        if (cs2 != null) {
            cs2.close();
        }
        try {
            System.out.println("Calling " + query2a);
            cs2a = con.prepareCall(query2a);
            cs2a.execute();
        } catch (SQLException e2a) {
            JDBCTutorialUtilities.printSQLException(e2a);
        } finally {
            if (cs2a != null) {
                cs2a.close();
            }
        }
    }
    try {
        System.out.println("Calling " + query3);
        cs3 = con.prepareCall(query3);
        cs3.execute();
    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (cs3 != null) {
            cs3.close();
        }
    }

}

From source file:com.cimmyt.model.dao.impl.AbstractDAO.java

public void callStoreProcedureAddFieldToTemplate(int idTemplateParams, int studytemplateid) {
    SessionImpl session = (SessionImpl) getHibernateTemplate().getSessionFactory().openSession();

    CallableStatement callableStatement = null;
    try {//from   w  ww  . j a  v  a  2 s  .  c o  m
        System.out.println("Session : " + session);
        callableStatement = session.connection().prepareCall("Call sp_add_fields_template_result(?,?)");
        System.out.println("Callable stament : " + callableStatement);
        callableStatement.setInt(1, idTemplateParams);
        callableStatement.setInt(2, studytemplateid);
        callableStatement.execute();
    } catch (HibernateException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}

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

/**
 * @see com.cws.us.pws.dao.interfaces.ICareersReferenceDAO#getCareerList(String) throws SQLException
 *//*w  ww. j a  va 2s .c  o m*/
@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;
}