Example usage for java.sql CallableStatement setTimestamp

List of usage examples for java.sql CallableStatement setTimestamp

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

Introduction

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

Prototype

void setTimestamp(String parameterName, java.sql.Timestamp x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given java.sql.Timestamp value.

Usage

From source file:jongo.jdbc.JDBCExecutor.java

/**
 * Utility method which registers in a CallableStatement object the different {@link jongo.jdbc.StoredProcedureParam}
 * instances in the given list. Returns a List of {@link jongo.jdbc.StoredProcedureParam} with all the OUT parameters
 * registered in the CallableStatement/*from www  . j  av a2s .  c  o  m*/
 * @param cs the CallableStatement object where the parameters are registered.
 * @param params a list of {@link jongo.jdbc.StoredProcedureParam}
 * @return a list of OUT {@link jongo.jdbc.StoredProcedureParam} 
 * @throws SQLException if we fail to register any of the parameters in the CallableStatement
 */
private static List<StoredProcedureParam> addParameters(final CallableStatement cs,
        final List<StoredProcedureParam> params) throws SQLException {
    List<StoredProcedureParam> outParams = new ArrayList<StoredProcedureParam>();
    int i = 1;
    for (StoredProcedureParam p : params) {
        final Integer sqlType = p.getType();
        if (p.isOutParameter()) {
            l.debug("Adding OUT parameter " + p.toString());
            cs.registerOutParameter(i++, sqlType);
            outParams.add(p);
        } else {
            l.debug("Adding IN parameter " + p.toString());
            switch (sqlType) {
            case Types.BIGINT:
            case Types.INTEGER:
            case Types.TINYINT:
                //                    case Types.NUMERIC:
                cs.setInt(i++, Integer.valueOf(p.getValue()));
                break;
            case Types.DATE:
                cs.setDate(i++, (Date) JongoUtils.parseValue(p.getValue()));
                break;
            case Types.TIME:
                cs.setTime(i++, (Time) JongoUtils.parseValue(p.getValue()));
                break;
            case Types.TIMESTAMP:
                cs.setTimestamp(i++, (Timestamp) JongoUtils.parseValue(p.getValue()));
                break;
            case Types.DECIMAL:
                cs.setBigDecimal(i++, (BigDecimal) JongoUtils.parseValue(p.getValue()));
                break;
            case Types.DOUBLE:
                cs.setDouble(i++, Double.valueOf(p.getValue()));
                break;
            case Types.FLOAT:
                cs.setLong(i++, Long.valueOf(p.getValue()));
                break;
            default:
                cs.setString(i++, p.getValue());
                break;
            }
        }
    }
    return outParams;
}

From source file:it.greenvulcano.gvesb.datahandling.dbo.DBOCallSP.java

/**
 * @param cs/* ww w .  j  av a2 s .c  o m*/
 * @param ts
 * @throws SQLException
 */
private void setTimestamp(CallableStatement cs, Timestamp ts) throws SQLException {
    if (useName) {
        cs.setTimestamp(currName, ts);
    } else {
        cs.setTimestamp(colIdx, ts);
    }
}

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 a 2  s.  co m*/
 *        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:nl.nn.adapterframework.jdbc.JdbcQuerySenderBase.java

protected String executePackageQuery(Connection connection, PreparedStatement statement, String message)
        throws SenderException, JdbcException, IOException, JMSException {
    Object[] paramArray = new Object[10];
    String callMessage = fillParamArray(paramArray, message);
    ResultSet resultset = null;//from   ww w  .  j  ava2s.c o m
    try {
        CallableStatement pstmt = connection.prepareCall(callMessage);
        if (getMaxRows() > 0) {
            pstmt.setMaxRows(getMaxRows() + (getStartRow() > 1 ? getStartRow() - 1 : 0));
        }
        int var = 1;
        for (int i = 0; i < paramArray.length; i++) {
            if (paramArray[i] instanceof Timestamp) {
                pstmt.setTimestamp(var, (Timestamp) paramArray[i]);
                var++;
            }
            if (paramArray[i] instanceof java.sql.Date) {
                pstmt.setDate(var, (java.sql.Date) paramArray[i]);
                var++;
            }
            if (paramArray[i] instanceof String) {
                pstmt.setString(var, (String) paramArray[i]);
                var++;
            }
            if (paramArray[i] instanceof Integer) {
                int x = Integer.parseInt(paramArray[i].toString());
                pstmt.setInt(var, x);
                var++;
            }
            if (paramArray[i] instanceof Float) {
                float x = Float.parseFloat(paramArray[i].toString());
                pstmt.setFloat(var, x);
                var++;
            }
        }
        if (message.indexOf('?') != -1) {
            pstmt.registerOutParameter(var, Types.CLOB); // make sure enough space is available for result...
        }
        if ("xml".equalsIgnoreCase(getPackageContent())) {
            log.debug(getLogPrefix() + "executing a package SQL command");
            pstmt.executeUpdate();
            String pUitvoer = pstmt.getString(var);
            return pUitvoer;
        }
        log.debug(getLogPrefix() + "executing a package SQL command");
        int numRowsAffected = pstmt.executeUpdate();
        if (StringUtils.isNotEmpty(getResultQuery())) {
            Statement resStmt = null;
            try {
                resStmt = connection.createStatement();
                log.debug("obtaining result from [" + getResultQuery() + "]");
                ResultSet rs = resStmt.executeQuery(getResultQuery());
                return getResult(rs);
            } finally {
                if (resStmt != null) {
                    resStmt.close();
                }
            }
        }
        if (getColumnsReturnedList() != null) {
            return getResult(getReturnedColumns(getColumnsReturnedList(), statement));
        }
        if (isScalar()) {
            return numRowsAffected + "";
        }
        return "<result><rowsupdated>" + numRowsAffected + "</rowsupdated></result>";
    } catch (SQLException sqle) {
        throw new SenderException(getLogPrefix() + "got exception executing a package SQL command", sqle);
    } finally {
        try {
            if (resultset != null) {
                resultset.close();
            }
        } catch (SQLException e) {
            log.warn(new SenderException(getLogPrefix() + "got exception closing resultset", e));
        }
    }
}

From source file:org.brucalipto.sqlutil.DB2SQLManager.java

/**
 * Method useful for using STORED PROCEDURE
 * @param spib The {@link SPInputBean} bean containing data to execute the stored procedure
 * @return The {@link SPOutputBean} containing returned values
 *//*from   ww w.jav a  2  s  . com*/
public SPOutputBean executeSP(final SPInputBean spib) throws SQLException {
    Connection conn = null;
    CallableStatement call = null;
    ResultSet resultSet = null;
    final String procedureName = spib.spName;

    SPParameter[] inputParameters = spib.inputParams;
    int[] outputParameters = spib.outputParams;

    final int inputParametersSize = inputParameters.length;
    final int outputParametersSize = outputParameters.length;

    final StringBuffer spName = new StringBuffer("call ").append(procedureName).append('(');
    int totalParameters = inputParametersSize + outputParametersSize;
    for (int i = 0; i < totalParameters; i++) {
        if (i != totalParameters - 1) {
            spName.append("?,");
        } else {
            spName.append('?');
        }
    }
    spName.append(")");

    try {
        if (this.dataSource != null) {
            conn = this.dataSource.getConnection();
        } else {
            conn = this.connection;
        }
        call = conn.prepareCall(spName.toString());
        for (int i = 0; i < inputParametersSize; i++) {
            final SPParameter inputParam = inputParameters[i];
            final int sqlType = inputParam.sqlType;
            final Object inputParamValue = inputParam.value;
            log.debug((i + 1) + ") Setting input value: " + inputParam);
            if (inputParamValue == null) {
                call.setNull(i + 1, sqlType);
                continue;
            }
            switch (sqlType) {
            case Types.VARCHAR:
                call.setString(i + 1, (String) inputParamValue);
                break;
            case Types.INTEGER:
                if (inputParamValue instanceof Integer) {
                    call.setInt(i + 1, ((Integer) inputParamValue).intValue());
                } else if (inputParamValue instanceof Long) {
                    call.setLong(i + 1, ((Long) inputParamValue).longValue());
                }
                break;
            case Types.DATE:
                call.setDate(i + 1, (Date) inputParamValue);
                break;
            case Types.BOOLEAN:
                call.setBoolean(i + 1, ((Boolean) inputParamValue).booleanValue());
                break;
            case Types.CHAR:
                call.setString(i + 1, ((Character) inputParamValue).toString());
                break;
            case Types.DOUBLE:
                call.setDouble(i + 1, ((Double) inputParamValue).doubleValue());
                break;
            case Types.FLOAT:
                call.setFloat(i + 1, ((Float) inputParamValue).floatValue());
                break;
            case Types.TIMESTAMP:
                call.setTimestamp(i + 1, (Timestamp) inputParamValue);
                break;
            default:
                call.setObject(i + 1, inputParamValue);
                break;
            }
        }

        for (int i = 0; i < outputParametersSize; i++) {
            int sqlType = outputParameters[i];
            log.debug((i + 1) + ") Registering output type 'Types."
                    + SQLUtilTypes.SQL_TYPES.get(Integer.valueOf("" + sqlType)) + "'");
            call.registerOutParameter(inputParametersSize + i + 1, sqlType);
        }

        log.debug("Going to call: '" + procedureName + "'");
        long elapsedTime = System.currentTimeMillis();
        boolean hasResultSet = call.execute();
        log.debug("SP '" + procedureName + "' executed in " + (System.currentTimeMillis() - elapsedTime)
                + "millis");
        if (hasResultSet) {
            log.debug("This SP is going to return also a resultSet");
        }

        final SPOutputBean output = new SPOutputBean();
        for (int i = 0; i < outputParametersSize; i++) {
            int sqlType = outputParameters[i];
            log.debug((i + 1) + ") Getting output type 'Types."
                    + SQLUtilTypes.SQL_TYPES.get(Integer.valueOf("" + sqlType)) + "'");
            final Object spResult = call.getObject(inputParametersSize + i + 1);
            SPParameter outParam = new SPParameter(sqlType, spResult);
            output.addResult(outParam);
        }
        if (hasResultSet) {
            RowSetDynaClass rowSetDynaClass = new RowSetDynaClass(call.getResultSet(), false);
            if (log.isDebugEnabled()) {
                log.debug("Going to return a RowSetDynaClass with following properties:");
                DynaProperty[] properties = rowSetDynaClass.getDynaProperties();
                for (int i = 0; i < properties.length; i++) {
                    log.debug("Name: '" + properties[i].getName() + "'; Type: '"
                            + properties[i].getType().getName() + "'");
                }
            }
            SPParameter outParam = new SPParameter(Types.JAVA_OBJECT, rowSetDynaClass);
            output.addResult(outParam);
        }
        return output;
    } finally {
        closeResources(resultSet, call, conn);
    }
}

From source file:org.brucalipto.sqlutil.OracleSQLManager.java

/**
* Method useful for using STORED PROCEDURE
* @param spib The {@link SPInputBean} bean containing data to execute the stored procedure
* @return The {@link SPOutputBean} containing returned values
*///from   w  ww . j  a  v a 2 s .  c  o m
public SPOutputBean executeSP(final SPInputBean spib) throws SQLException {
    Connection conn = null;
    CallableStatement call = null;
    ResultSet resultSet = null;
    final String procedureName = spib.spName;

    SPParameter[] inputParameters = spib.inputParams;
    int[] outputParameters = spib.outputParams;

    final int inputParametersSize = inputParameters.length;
    final int outputParametersSize = outputParameters.length;

    final StringBuffer spName = new StringBuffer("{ call ").append(procedureName).append('(');
    int totalParameters = inputParametersSize + outputParametersSize;
    for (int i = 0; i < totalParameters; i++) {
        if (i != totalParameters - 1) {
            spName.append("?,");
        } else {
            spName.append('?');
        }
    }
    spName.append(") }");
    log.debug("Going to call: '" + spName + "'");

    try {
        conn = this.dataSource.getConnection();
        call = conn.prepareCall(spName.toString());
        for (int i = 0; i < inputParametersSize; i++) {
            final SPParameter inputParam = inputParameters[i];
            final int sqlType = inputParam.sqlType;
            final Object inputParamValue = inputParam.value;
            log.debug((i + 1) + ") Setting input value 'Types."
                    + SQLUtilTypes.SQL_TYPES.get(Integer.valueOf("" + sqlType)) + "'-'" + inputParamValue
                    + "'");
            if (inputParamValue == null) {
                call.setNull(i + 1, sqlType);
                continue;
            }
            switch (sqlType) {
            case Types.VARCHAR:
                call.setString(i + 1, (String) inputParamValue);
                break;
            case Types.INTEGER:
                if (inputParamValue instanceof Integer) {
                    call.setInt(i + 1, ((Integer) inputParamValue).intValue());
                } else if (inputParamValue instanceof Long) {
                    call.setLong(i + 1, ((Long) inputParamValue).longValue());
                }
                break;
            case Types.DATE:
                call.setDate(i + 1, (Date) inputParamValue);
                break;
            case Types.BOOLEAN:
                call.setBoolean(i + 1, ((Boolean) inputParamValue).booleanValue());
                break;
            case Types.CHAR:
                call.setString(i + 1, ((Character) inputParamValue).toString());
                break;
            case Types.DOUBLE:
                call.setDouble(i + 1, ((Double) inputParamValue).doubleValue());
                break;
            case Types.FLOAT:
                call.setFloat(i + 1, ((Float) inputParamValue).floatValue());
                break;
            case Types.TIMESTAMP:
                call.setTimestamp(i + 1, (Timestamp) inputParamValue);
                break;
            default:
                call.setObject(i + 1, inputParamValue);
                break;
            }
        }

        for (int i = 0; i < outputParametersSize; i++) {
            int sqlType = outputParameters[i];
            log.debug((i + 1) + ") Registering output type 'Types."
                    + SQLUtilTypes.SQL_TYPES.get(Integer.valueOf("" + sqlType)) + "'");
            call.registerOutParameter(inputParametersSize + i + 1, sqlType);
        }

        call.execute();

        final SPOutputBean output = new SPOutputBean();
        for (int i = 0; i < outputParametersSize; i++) {
            int sqlType = outputParameters[i];
            log.debug((i + 1) + ") Getting output type 'Types."
                    + SQLUtilTypes.SQL_TYPES.get(Integer.valueOf("" + sqlType)) + "'");
            final Object spResult = call.getObject(inputParametersSize + i + 1);
            SPParameter outParam = null;
            if (sqlType == SQLUtilTypes.CURSOR) {
                resultSet = (ResultSet) spResult;
                RowSetDynaClass rowSetDynaClass = new RowSetDynaClass(resultSet, false);
                if (log.isDebugEnabled()) {
                    log.debug("Going to return a RowSetDynaClass with following properties:");
                    DynaProperty[] properties = rowSetDynaClass.getDynaProperties();
                    for (int j = 0; j < properties.length; j++) {
                        log.debug("Name: '" + properties[j].getName() + "'; Type: '"
                                + properties[j].getType().getName() + "'");
                    }
                }
                outParam = new SPParameter(sqlType, rowSetDynaClass);
            } else {
                outParam = new SPParameter(sqlType, spResult);
            }
            output.addResult(outParam);
        }

        return output;
    } catch (SQLException sqle) {
        log.error("Caught SQLException", sqle);
    } finally {
        closeResources(resultSet, call, conn);
    }

    return null;
}