Example usage for java.sql CallableStatement executeUpdate

List of usage examples for java.sql CallableStatement executeUpdate

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

Introduction

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

Prototype

int executeUpdate() throws SQLException;

Source Link

Document

Executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.

Usage

From source file:com.MainFiles.Functions.java

public String generateUniqueReferenceNumber() throws SQLException {
    String strReferenceNumber = "";
    Connection dbConnection = connections.getDBConnection(ECSERVER, ECDB, ECUSER, ECPASSWORD);
    try {/*from www .j a  v  a 2s . c  om*/
        NumberFormat formatter = NumberFormat.getCurrencyInstance(new Locale("en", "tz"));
        String strStoredProcedure = "{call SP_GET_AGENCY_NEXT_SEQ(?)}";
        CallableStatement callableStatement = dbConnection.prepareCall(strStoredProcedure);
        callableStatement.registerOutParameter(1, java.sql.Types.VARCHAR);
        callableStatement.executeUpdate();
        String response = callableStatement.getString(1);
        strReferenceNumber = "POS" + response;
    } catch (Exception ex) {
        this.log("INFO :: Error on generateUniqueReferenceNumber " + ex.getMessage() + "\n"
                + this.StackTraceWriter(ex), "ERROR");
    } finally {
        dbConnection.close();
    }
    return strReferenceNumber;
}

From source file:com.MainFiles.Functions.java

public String fn_getAgentEODTransactions(String strAgentID, String strTerminalID, String intID)
        throws SQLException {
    String strResponse = "";
    String SQL, sqlQuery = "";
    String strTotalcredit = "";
    String strdate = "";
    double amount = 0;
    String strStatementPrinting = "";
    String strTotaldebits = "";
    int i = 1;/*from   ww  w  .j  av  a 2  s.  c  om*/
    Connection dbConnection = connections.getDBConnection(ECSERVER, ECDB, ECUSER, ECPASSWORD);
    try {
        NumberFormat formatter = NumberFormat.getCurrencyInstance(new Locale("en", "tz"));
        String strStoredProcedure = "{call SP_DAILY_TRANSACTIONS(?,?)}";
        CallableStatement callableStatement = dbConnection.prepareCall(strStoredProcedure);

        callableStatement.setString(1, strTerminalID);
        callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
        callableStatement.executeUpdate();

        String strStatus = callableStatement.getString(2);

        if (strStatus.equals("1")) {
            String query = "select transactiontype,transactioncount,amount from tbagentsummary where serialnumber='"
                    + strTerminalID + "'";
            ResultSet rsrecordQuery = connections.ExecuteQueryReturnString(ECSERVER, query, ECPASSWORD, ECUSER,
                    ECDB);

            while (rsrecordQuery.next()) {
                String strAmount = NumberFormat.getNumberInstance(Locale.US)
                        .format(Double.parseDouble(rsrecordQuery.getString("AMOUNT").toString()));
                strStatementPrinting += " ";
                strStatementPrinting += PadSpaces(15, rsrecordQuery.getString("TRANSACTIONTYPE").toString(),
                        "RIGHT");
                strStatementPrinting += PadSpaces(4, rsrecordQuery.getString("TRANSACTIONCOUNT").toString(),
                        "LEFT");
                strStatementPrinting += PadSpaces(12, strAmount.replace("TZS", ""), "LEFT");
                strStatementPrinting += "#";
            }

        }
        String StrMerchantFloatAccount = fn_getAgentAccountNumber(strAgentID);
        strResponse += "AGENT ID:    " + strAgentID + "#";
        strResponse += "TRAN NUM:    " + intID + "#";
        strResponse += "ACCOUNT NUM: " + StrMerchantFloatAccount + "#";
        strResponse += "--------------------------------" + "#";
        strResponse += "                                " + "#";
        strResponse += "       EOD SUMMARY REPORT       " + "#";
        strResponse += "                                " + "#";
        strResponse += "    TRANS.       COUNT    AMOUNT" + "#";
        strResponse += "                                " + "#";
        strResponse += strStatementPrinting;
        strResponse += "                                " + "#";
        strResponse += "                                " + "#";

        return strResponse;
    } catch (Exception ex) {
        this.log("INFO :: Error on fn_getAgentEODTransactions " + ex.getMessage() + "\n"
                + this.StackTraceWriter(ex), "ERROR");
    } finally {
        dbConnection.close();
    }

    return null;
}

From source file:org.wso2.carbon.dataservices.core.description.query.SQLQuery.java

private Object processPreStoredProcQuery(InternalParamCollection params, int queryLevel)
        throws DataServiceFault {
    boolean isError = false;
    CallableStatement stmt = null;
    ResultSet rs = null;//from  w w w. j a v  a 2  s .c o  m
    try {
        Connection conn = this.createConnection(queryLevel);
        stmt = (CallableStatement) this.createProcessedPreparedStatement(SQLQuery.DS_QUERY_TYPE_STORED_PROC,
                params, conn);
        /* check if this is a batch request */
        if (this.isJDBCFirstBatchRequest()) {
            this.setBatchPreparedStatement(stmt);
            /* add this to cleanup this query after batch request */
            BatchDataServiceRequest.addParticipant(this);
        }
        if (!this.hasResult() || (this.hasResult() && this.isReturnGeneratedKeys())
                || (this.hasResult() && this.isReturnUpdatedRowCount())) {
            /* if we are in the middle of a batch request, don't execute it */
            if (this.isJDBCBatchRequest()) {
                /* if this is the last one, execute the full batch */
                if (this.isJDBCLastBatchRequest()) {
                    stmt.executeBatch();
                }
            } else {
                stmt.executeUpdate();
            }
        } else {
            /*
             * check if all the result elements are out params; if so, no
             * result set
             */
            if (this.isResultOnlyOutParams()) {
                stmt.execute();
                /* if there's a ref cursor, get the result set */
                if (this.hasRefCursor()) {
                    rs = (ResultSet) stmt.getObject(getCurrentRefCursorOrdinal());
                }
            } else {
                rs = this.getFirstRSOfStoredProc(stmt);
            }
        }
        return new QueryResultInfo(stmt, rs);
    } catch (Exception e) {
        log.error(e.getMessage(), e);
        isError = true;
        throw new DataServiceFault(e, FaultCodes.DATABASE_ERROR,
                "Error in 'SQLQuery.processStoredProcQuery': " + e.getMessage());
    } finally {
        if (isError) {
            this.releaseResources(rs, this.isStatementClosable(isError) ? stmt : null);
        }
    }
}

From source file:tds.dll.mysql.CommonDLL.java

public MultiDataResultSet _UpdateOpportunityAccommodations_SP_Mysql(SQLConnection connection, UUID oppKey,
        int segment, String accoms, int isStarted, Boolean approved, Boolean restoreRTS, _Ref<String> error,
        int debug) throws ReturnStatusException {
    List<SingleDataResultSet> resultsets = new ArrayList<SingleDataResultSet>();
    try {//from  w w  w .  j av a  2  s  .co m

        String oppKeyStr = oppKey.toString().replaceAll("-", "");
        oppKeyStr = String.format("0x%s", oppKeyStr);
        final String SQL_QUERY = "{call _UpdateOpportunityAccommodations (" + oppKeyStr + ",?,?,?,?,?,?,?)}";
        CallableStatement callableStatement = connection.prepareCall(SQL_QUERY);
        callableStatement.setInt(1, segment);
        callableStatement.setString(2, accoms);
        callableStatement.setInt(3, isStarted);
        callableStatement.setBoolean(4, approved);
        callableStatement.setBoolean(5, restoreRTS);
        callableStatement.registerOutParameter(6, java.sql.Types.VARCHAR);
        callableStatement.setInt(7, debug);

        callableStatement.executeUpdate();

        error.set(callableStatement.getString(6));

    } catch (SQLException e) {
        throw new ReturnStatusException(e);
    }
    return new MultiDataResultSet(resultsets);
}

From source file:gov.nih.nci.cadsr.sentinel.database.DBAlertOracle.java

/**
 * Insert the Report details for the Alert definition into the database and
 * retrieve the new report id.//from   w w  w . j a  v a 2  s  .c o  m
 *
 * @param rec_
 *        The Alert definition to be stored in the database.
 * @return 0 if successful, otherwise the database error code.
 * @throws java.sql.SQLException
 *         On error with rollback().
 */
private int insertReport(AlertRec rec_) throws SQLException {
    // Add the Report record.
    String insert = "begin insert into sbrext.sn_report_view_ext "
            + "(al_idseq, comments, include_property_ind, style, send, acknowledge_ind, assoc_lvl_num, created_by) "
            + "values (?, ?, ?, ?, ?, ?, ?, ?) return rep_idseq into ?; end;";

    CallableStatement pstmt = null;
    int rc = 0;
    try {
        pstmt = _conn.prepareCall(insert);
        pstmt.setString(1, rec_.getAlertRecNum());
        pstmt.setString(2, rec_.getIntro(false));
        pstmt.setString(3, rec_.getIncPropSectString());
        pstmt.setString(4, rec_.getReportStyleString());
        pstmt.setString(5, rec_.getReportEmptyString());
        pstmt.setString(6, rec_.getReportAckString());
        pstmt.setInt(7, rec_.getIAssocLvl());
        pstmt.setString(8, _user);
        pstmt.registerOutParameter(9, Types.CHAR);
        pstmt.executeUpdate();

        // We need the record id to populate the foreign keys for other
        // tables.
        rec_.setReportRecNum(pstmt.getString(9));
    } catch (SQLException ex) {
        // Ooops...
        rec_.setAlertRecNum(null);
        _conn.rollback();
        _errorCode = DBAlertUtil.getSQLErrorCode(ex);
        _errorMsg = _errorCode + ": " + insert + "\n\n" + ex.toString();
        _logger.error(_errorMsg);
        rc = _errorCode;
    } finally {
        closeCursors(pstmt, null);
    }
    return rc;
}

From source file:gov.nih.nci.cadsr.sentinel.database.DBAlertOracle.java

/**
 * Insert the properties for the Alert definition and retrieve the new
 * database generated ID for this Alert.
 *
 * @param rec_/*from w  w w  .  j a  v a2 s  . c om*/
 *        The Alert to be stored in the database.
 * @return 0 if successful, otherwise the database error code.
 * @throws java.sql.SQLException
 *         On error with rollback().
 */
private int insertProperties(AlertRec rec_) throws SQLException {
    // Define the SQL insert. Remember date_created, date_modified, creator
    // and modifier are controlled
    // by triggers. Also (as of 10/21/2004) after the insert the
    // date_modified is still set by the insert
    // trigger.
    String insert = "begin insert into sbrext.sn_alert_view_ext "
            + "(name, auto_freq_unit, al_status, begin_date, end_date, status_reason, auto_freq_value, created_by) "
            + "values (?, ?, ?, ?, ?, ?, ?, ?) return al_idseq into ?; end;";

    CallableStatement pstmt = null;
    int rc = 0;
    cleanRec(rec_);
    try {
        // Set all the SQL arguments.
        pstmt = _conn.prepareCall(insert);
        pstmt.setString(1, rec_.getName());
        pstmt.setString(2, rec_.getFreqString());
        pstmt.setString(3, rec_.getActiveString());
        pstmt.setTimestamp(4, rec_.getStart());
        pstmt.setTimestamp(5, rec_.getEnd());
        pstmt.setString(6, rec_.getInactiveReason(false));
        pstmt.setInt(7, rec_.getDay());
        pstmt.setString(8, _user);
        pstmt.registerOutParameter(9, Types.CHAR);

        // Insert the new record and flag a commit for later.
        pstmt.executeUpdate();

        // We need the record id to populate the foreign keys for other
        // tables.
        rec_.setAlertRecNum(pstmt.getString(9));
    } catch (SQLException ex) {
        // Ooops...
        rec_.setAlertRecNum(null);
        _conn.rollback();
        _errorCode = DBAlertUtil.getSQLErrorCode(ex);
        _errorMsg = _errorCode + ": " + insert + "\n\n" + ex.toString();
        _logger.error(_errorMsg);
        rc = _errorCode;
    } finally {
        closeCursors(pstmt, null);
    }
    return rc;
}

From source file:tds.dll.mysql.StudentDLL.java

public void _IsOpportunityBlocked_SP_Mysql(SQLConnection connection, String clientname, Long testee,
        String testID, Integer maxopps, _Ref<String> reasonBlockedRef, Integer sessionType)
        throws ReturnStatusException {

    try {/*from  w ww. j av a2  s.c  o  m*/

        /*String sessionIdStr = sessionId.toString ().replaceAll ("-", "");
        sessionIdStr = String.format ("0x%s", sessionIdStr);*/
        final String SQL_QUERY = "{call _isopportunityblocked(?,?,?,?,?,?,?)}";
        CallableStatement callableStatement = connection.prepareCall(SQL_QUERY);
        callableStatement.setString(1, clientname);
        callableStatement.setLong(2, testee);
        callableStatement.setString(3, testID);
        callableStatement.setInt(4, maxopps);
        callableStatement.registerOutParameter(5, java.sql.Types.VARCHAR);
        callableStatement.setInt(6, sessionType);
        callableStatement.setInt(7, 0);

        callableStatement.executeUpdate();

        reasonBlockedRef.set(callableStatement.getString(5));

    } catch (SQLException e) {
        throw new ReturnStatusException(e);
    }

}

From source file:tds.dll.mysql.StudentDLL.java

public void _CanOpenTestOpportunity_SP_Mysql(SQLConnection connection, String clientname, Long testee,
        String testkey, UUID sessionId, Integer maxOpportunities, _Ref<Boolean> newRef, _Ref<Integer> numberRef,
        _Ref<String> reasonRef) throws ReturnStatusException {
    try {//from w  w  w  .  j  av a  2 s . com

        String sessionIdStr = sessionId.toString().replaceAll("-", "");
        sessionIdStr = String.format("0x%s", sessionIdStr);
        final String SQL_QUERY = "{call _canopentestopportunity(?,?,?," + sessionIdStr + ",?,?,?,?,?)}";
        CallableStatement callableStatement = connection.prepareCall(SQL_QUERY);
        callableStatement.setString(1, clientname);
        callableStatement.setLong(2, testee);
        callableStatement.setString(3, testkey);
        callableStatement.setInt(4, maxOpportunities);
        callableStatement.registerOutParameter(5, java.sql.Types.BOOLEAN);
        callableStatement.registerOutParameter(6, java.sql.Types.INTEGER);
        callableStatement.registerOutParameter(7, java.sql.Types.VARCHAR);
        callableStatement.setInt(8, 0);

        callableStatement.executeUpdate();

        newRef.set(callableStatement.getBoolean(5));
        numberRef.set(callableStatement.getInt(6));
        reasonRef.set(callableStatement.getString(7));

    } catch (SQLException e) {
        throw new ReturnStatusException(e);
    }

}