Example usage for java.sql CallableStatement setNull

List of usage examples for java.sql CallableStatement setNull

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

Introduction

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

Prototype

void setNull(String parameterName, int sqlType) throws SQLException;

Source Link

Document

Sets the designated parameter to SQL NULL.

Usage

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
 *//*w w w . j ava  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(")");

    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 a2s .  c om*/
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;
}

From source file:org.executequery.databasemediators.spi.DefaultStatementExecutor.java

/** <p>Executes the specified procedure.
 *
 *  @param  the SQL procedure to execute
 *  @return the query result/*from   w  w w  . j ava  2 s .c om*/
 */
public SqlStatementResult execute(DatabaseExecutable databaseExecutable) throws SQLException {

    if (!prepared()) {

        return statementResult;
    }

    ProcedureParameter[] param = databaseExecutable.getParametersArray();
    Arrays.sort(param, new ProcedureParameterSorter());

    String procQuery = null;
    boolean hasOut = false;
    boolean hasParameters = (param != null && param.length > 0);

    List<ProcedureParameter> outs = null;
    List<ProcedureParameter> ins = null;

    if (hasParameters) {

        // split the params into ins and outs
        outs = new ArrayList<ProcedureParameter>();
        ins = new ArrayList<ProcedureParameter>();

        int type = -1;
        for (int i = 0; i < param.length; i++) {
            type = param[i].getType();
            if (type == DatabaseMetaData.procedureColumnIn || type == DatabaseMetaData.procedureColumnInOut) {

                // add to the ins list
                ins.add(param[i]);

            } else if (type == DatabaseMetaData.procedureColumnOut
                    || type == DatabaseMetaData.procedureColumnResult
                    || type == DatabaseMetaData.procedureColumnReturn
                    || type == DatabaseMetaData.procedureColumnUnknown
                    || type == DatabaseMetaData.procedureColumnInOut) {

                // add to the outs list
                outs.add(param[i]);

            }
        }

        char QUESTION_MARK = '?';
        String COMMA = ", ";

        // init the string buffer
        StringBuilder sb = new StringBuilder("{ ");
        if (!outs.isEmpty()) {

            // build the out params place holders
            for (int i = 0, n = outs.size(); i < n; i++) {

                sb.append(QUESTION_MARK);

                if (i < n - 1) {

                    sb.append(COMMA);
                }

            }

            sb.append(" = ");
        }

        sb.append(" call ");

        if (databaseExecutable.supportCatalogOrSchemaInFunctionOrProcedureCalls()) {

            String namePrefix = null;
            if (databaseExecutable.supportCatalogInFunctionOrProcedureCalls()) {

                namePrefix = databaseExecutable.getCatalogName();

            }
            if (databaseExecutable.supportSchemaInFunctionOrProcedureCalls()) {

                namePrefix = databaseExecutable.getSchemaName();

            }

            if (namePrefix != null) {

                sb.append(namePrefix).append('.');
            }
        }

        sb.append(databaseExecutable.getName()).append("( ");

        // build the ins params place holders
        for (int i = 0, n = ins.size(); i < n; i++) {
            sb.append(QUESTION_MARK);
            if (i < n - 1) {
                sb.append(COMMA);
            }
        }

        sb.append(" ) }");

        // determine if we have out params
        hasOut = !(outs.isEmpty());
        procQuery = sb.toString();
    } else {
        StringBuilder sb = new StringBuilder();
        sb.append("{ call ");

        if (databaseExecutable.getSchemaName() != null) {
            sb.append(databaseExecutable.getSchemaName()).append('.');
        }

        sb.append(databaseExecutable.getName()).append("( ) }");

        procQuery = sb.toString();
    }

    //Log.debug(procQuery);

    // null value literal
    String NULL = "null";

    // clear any warnings
    conn.clearWarnings();

    Log.info("Executing: " + procQuery);

    CallableStatement cstmnt = null;
    try {
        // prepare the statement
        cstmnt = conn.prepareCall(procQuery);
        stmnt = cstmnt;
    } catch (SQLException e) {
        handleException(e);
        statementResult.setSqlException(e);
        return statementResult;
    }

    // check if we are passing parameters
    if (hasParameters) {
        // the parameter index counter
        int index = 1;

        // the java.sql.Type value
        int dataType = -1;

        // the parameter input value
        String value = null;

        // register the out params
        for (int i = 0, n = outs.size(); i < n; i++) {
            //Log.debug("setting out at index: " + index);
            cstmnt.registerOutParameter(index, outs.get(i).getDataType());
            index++;
        }

        try {

            // register the in params
            for (int i = 0, n = ins.size(); i < n; i++) {

                ProcedureParameter procedureParameter = ins.get(i);
                value = procedureParameter.getValue();
                dataType = procedureParameter.getDataType();

                // try infer a type if OTHER
                if (dataType == Types.OTHER) {

                    // checking only for bit/bool for now

                    if (isTrueFalse(value)) {

                        dataType = Types.BOOLEAN;

                    } else if (isBit(value)) {

                        dataType = Types.BIT;
                        value = value.substring(2, value.length() - 1);
                    }

                }

                if (MiscUtils.isNull(value) || value.equalsIgnoreCase(NULL)) {

                    cstmnt.setNull(index, dataType);

                } else {

                    switch (dataType) {

                    case Types.TINYINT:
                        byte _byte = Byte.valueOf(value).byteValue();
                        cstmnt.setShort(index, _byte);
                        break;

                    case Types.SMALLINT:
                        short _short = Short.valueOf(value).shortValue();
                        cstmnt.setShort(index, _short);
                        break;

                    case Types.LONGVARCHAR:
                    case Types.CHAR:
                    case Types.VARCHAR:
                        cstmnt.setString(index, value);
                        break;

                    case Types.BIT:
                    case Types.BOOLEAN:

                        boolean _boolean = false;
                        if (NumberUtils.isNumber(value)) {

                            int number = Integer.valueOf(value);
                            if (number > 0) {

                                _boolean = true;
                            }

                        } else {

                            _boolean = Boolean.valueOf(value).booleanValue();
                        }

                        cstmnt.setBoolean(index, _boolean);
                        break;

                    case Types.BIGINT:
                        long _long = Long.valueOf(value).longValue();
                        cstmnt.setLong(index, _long);
                        break;

                    case Types.INTEGER:
                        int _int = Integer.valueOf(value).intValue();
                        cstmnt.setInt(index, _int);
                        break;

                    case Types.REAL:
                        float _float = Float.valueOf(value).floatValue();
                        cstmnt.setFloat(index, _float);
                        break;

                    case Types.NUMERIC:
                    case Types.DECIMAL:
                        cstmnt.setBigDecimal(index, new BigDecimal(value));
                        break;
                    /*
                                      case Types.DATE:
                                      case Types.TIMESTAMP:
                                      case Types.TIME:
                                        cstmnt.setTimestamp(index, new Timestamp( BigDecimal(value));
                    */
                    case Types.FLOAT:
                    case Types.DOUBLE:
                        double _double = Double.valueOf(value).doubleValue();
                        cstmnt.setDouble(index, _double);
                        break;

                    default:
                        cstmnt.setObject(index, value);

                    }

                }

                // increment the index
                index++;
            }

        } catch (Exception e) {

            statementResult.setOtherErrorMessage(e.getClass().getName() + ": " + e.getMessage());
            return statementResult;
        }

    }

    /*
    test creating function for postgres:
            
    CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false)
    RETURNS text
    AS
    $$
     SELECT CASE
        WHEN $3 THEN UPPER($1 || ' ' || $2)
        ELSE LOWER($1 || ' ' || $2)
        END;
    $$
    LANGUAGE SQL IMMUTABLE STRICT;
    */

    try {
        cstmnt.clearWarnings();
        boolean hasResultSet = cstmnt.execute();
        Map<String, Object> results = new HashMap<String, Object>();

        if (hasOut) {
            // incrementing index
            int index = 1;

            // return value from each registered out
            String returnValue = null;

            for (int i = 0; i < param.length; i++) {

                int type = param[i].getType();
                int dataType = param[i].getDataType();

                if (type == DatabaseMetaData.procedureColumnOut
                        || type == DatabaseMetaData.procedureColumnResult
                        || type == DatabaseMetaData.procedureColumnReturn
                        || type == DatabaseMetaData.procedureColumnUnknown
                        || type == DatabaseMetaData.procedureColumnInOut) {

                    switch (dataType) {

                    case Types.TINYINT:
                        returnValue = Byte.toString(cstmnt.getByte(index));
                        break;

                    case Types.SMALLINT:
                        returnValue = Short.toString(cstmnt.getShort(index));
                        break;

                    case Types.LONGVARCHAR:
                    case Types.CHAR:
                    case Types.VARCHAR:
                        returnValue = cstmnt.getString(index);
                        break;

                    case Types.BIT:
                    case Types.BOOLEAN:
                        returnValue = Boolean.toString(cstmnt.getBoolean(index));
                        break;

                    case Types.INTEGER:
                        returnValue = Integer.toString(cstmnt.getInt(index));
                        break;

                    case Types.BIGINT:
                        returnValue = Long.toString(cstmnt.getLong(index));
                        break;

                    case Types.REAL:
                        returnValue = Float.toString(cstmnt.getFloat(index));
                        break;

                    case Types.NUMERIC:
                    case Types.DECIMAL:
                        returnValue = cstmnt.getBigDecimal(index).toString();
                        break;

                    case Types.DATE:
                    case Types.TIME:
                    case Types.TIMESTAMP:
                        returnValue = cstmnt.getDate(index).toString();
                        break;

                    case Types.FLOAT:
                    case Types.DOUBLE:
                        returnValue = Double.toString(cstmnt.getDouble(index));
                        break;

                    }

                    if (returnValue == null) {
                        returnValue = "NULL";
                    }

                    results.put(param[i].getName(), returnValue);
                    index++;
                }

            }

        }

        if (!hasResultSet) {

            statementResult.setUpdateCount(cstmnt.getUpdateCount());

        } else {

            statementResult.setResultSet(cstmnt.getResultSet());
        }

        useCount++;
        statementResult.setOtherResult(results);

    } catch (SQLException e) {

        statementResult.setSqlException(e);

    } catch (Exception e) {

        statementResult.setMessage(e.getMessage());
    }

    return statementResult;
}

From source file:org.xsystem.sql2.dml.DmlCommand.java

void setParms(CallableStatement cs, List<DmlParams> paramsSpec, Map<String, Object> values)
        throws SQLException {
    int i = 0;/*w ww  . j  a  va2s  .  c  om*/
    Connection con = cs.getConnection();
    AbstactNativeHelper nativeHelper = nativeHelperFactory(con);
    for (DmlParams row : paramsSpec) {
        String paramName = row.getName();
        Integer jdbcType = row.getJdbcType();
        String objectType = row.getObjectType();
        Object value = values.get(paramName);
        boolean in = row.isIn();
        boolean out = row.isOut();
        i = i + 1;
        if (in) {
            if (namedParams) {
                if (jdbcType == Types.OTHER) {
                    cs.setObject(paramName, value);
                } else {
                    value = setValue(value, jdbcType, objectType, con, nativeHelper);
                    if (value != null) {
                        value = convert(value, jdbcType);
                        cs.setObject(paramName, value, jdbcType);
                    } else {
                        cs.setNull(paramName, jdbcType);
                    }
                }
            } else {
                if (jdbcType == Types.OTHER) {
                    cs.setObject(i, value);
                } else {
                    if (jdbcType == JdbcTypeMapping.json) {
                        nativeHelper.setJSONPARAM(cs, i, value);
                    } else {
                        value = setValue(value, jdbcType, objectType, con, nativeHelper);

                        if (value != null) {
                            value = convert(value, jdbcType);
                            cs.setObject(i, value, jdbcType);
                        } else {
                            cs.setNull(i, jdbcType);
                        }
                    }
                }
            }
        }
        if (out) {
            if (namedParams) {

                if (jdbcType == Types.ARRAY || jdbcType == Types.STRUCT) {
                    cs.registerOutParameter(paramName, jdbcType, objectType);
                } else {
                    cs.registerOutParameter(paramName, jdbcType);
                }

            } else {
                if (jdbcType == Types.ARRAY || jdbcType == Types.STRUCT) {
                    cs.registerOutParameter(i, jdbcType, objectType);
                } else {
                    cs.registerOutParameter(i, jdbcType);
                }
            }
        }
    }
}

From source file:pingpong.db.DBAccess.java

/**
 * ResultSet?  ?  ? ./*from   ww  w. j  a  v  a2  s .c om*/
 * @param sql
 * @param args
 * @return procedure?  rtn_cod, rtn_msg   String[]
 */
public String[] executeProcedure(String sql, Object args[]) throws SQLException {
    String result[] = { "", "" };
    Connection local_con = null;
    CallableStatement cstmt = null;
    ResultSet rs = null;
    int idxParam = (args == null) ? 0 : args.length;

    log.debug(sql);
    if (args != null) {
        for (int i = 0; i < args.length; i++) {
            log.debug("args" + i + ":" + args[i]);
        }
    }

    try {
        if (m_con != null) {
            cstmt = m_con.prepareCall(sql);
        } else {
            local_con = getConnection();
            cstmt = local_con.prepareCall(sql);
        }
        if (args != null) {

            for (int i = 0; i < args.length; i++) {
                if (args[i] instanceof Null) {
                    cstmt.setNull(i + 1, ((Null) args[i]).type);
                } else {
                    cstmt.setObject(i + 1, args[i]);
                }
            }
        }
        cstmt.setString(idxParam + 1, "");
        cstmt.setString(idxParam + 2, "");
        cstmt.registerOutParameter(idxParam + 1, Types.VARCHAR);
        cstmt.registerOutParameter(idxParam + 2, Types.VARCHAR);
        cstmt.execute();
        result[0] = cstmt.getString(idxParam + 1);
        result[1] = cstmt.getString(idxParam + 2);
    } catch (SQLException e) {
        state = e.getErrorCode();
        sqlState = e.getSQLState();
        message = e.getMessage();
        throw e;
    } catch (Exception e) {
        message = e.getMessage();
        e.printStackTrace();
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
            }
        }
        if (cstmt != null) {
            try {
                cstmt.close();
            } catch (Exception e) {
            }
        }
        if (local_con != null) {
            try {
                local_con.close();
            } catch (Exception e) {
            }
        }
    }
    return result;
}

From source file:pingpong.db.DBAccess.java

public TResultSet executeProcedure1(String sql, Object args[]) throws SQLException {
    Connection local_con = null;//from w w  w  .ja v a2 s . co  m
    CallableStatement cstmt = null;
    ResultSet rs = null;
    TResultSet ds = null;

    sql = sql.replace(")", ",?)"); //
    log.debug("sql repalce:" + sql + "len:" + args.length);

    int idxParam = (args == null) ? 0 : args.length + 1;
    for (int i = 0; i < args.length; i++)
        log.debug("arg " + i + " : " + args[i]);
    try {
        if (m_con != null) {
            cstmt = m_con.prepareCall(sql);
        } else {
            local_con = getConnection();
            cstmt = local_con.prepareCall(sql);
        }
        if (args != null) {
            for (int i = 0; i < args.length; i++) {
                //log.debug("###########i: "+i +"/"+args[i]+"/"+args.length);
                if (args[i] instanceof Null) {
                    cstmt.setNull(i + 1, ((Null) args[i]).type);
                } else {
                    cstmt.setObject(i + 1, args[i]);
                }

            }

            // For Oracle Procedure return Cursor
            //            cstmt.registerOutParameter(idxParam, OracleTypes.CURSOR);  //            
        }

        // For Oracle Procedure
        //          cstmt.executeQuery();          
        //          rs = (ResultSet) cstmt.getObject(idxParam);
        rs = cstmt.executeQuery();

        return makeTResultSet(rs, 0, -1);
    } catch (SQLException e) {
        state = e.getErrorCode();
        sqlState = e.getSQLState();
        message = e.getMessage();
        throw e;
    } catch (Exception e) {
        message = e.getMessage();
        e.printStackTrace();
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
            }
        }
        if (cstmt != null) {
            try {
                cstmt.close();
            } catch (Exception e) {
            }
        }
        if (local_con != null) {
            try {
                local_con.close();
            } catch (Exception e) {
            }
        }
    }
    return ds;
}

From source file:pingpong.db.DBAccess.java

/**
 * ResultSet?  ?  ? .//from w  w w.  j av a  2 s .  com
 * @param args
 *       -  2? arguments   .
 * @return TResultSet? 
 */
public TResultSet executeProcedure2(String sql, Object args[]) throws SQLException {
    //      String result[] = {"", ""};
    Connection local_con = null;
    CallableStatement cstmt = null;
    ResultSet rs = null;
    TResultSet ds = null;

    //   sql.replace("?)", "?,?)");
    int idxParam = (args == null) ? 0 : args.length;
    try {
        if (m_con != null) {
            cstmt = m_con.prepareCall(sql);
        } else {
            local_con = getConnection();
            cstmt = local_con.prepareCall(sql);
        }
        if (args != null) {
            for (int i = 0; i < args.length - 2; i++) {
                if (args[i] instanceof Null) {
                    log.debug("1###########i:" + i + args[i]);

                    cstmt.setNull(i + 1, ((Null) args[i]).type);
                } else {
                    cstmt.setObject(i + 1, args[i]);
                    log.debug("2###########i:" + i + args[i]);
                }
            }
            cstmt.setString(idxParam - 1, "");
            cstmt.setString(idxParam, "");
            cstmt.registerOutParameter(idxParam - 1, Types.VARCHAR);
            cstmt.registerOutParameter(idxParam, Types.VARCHAR);
            //   cstmt.registerOutParameter(idxParam, OracleTypes.CURSOR);   
            log.debug("###########sql:" + sql + "\nargs:" + args + " len:" + args.length);
            log.debug("###########sql:" + sql + "\nargs:" + args + " len:" + args.length);
        }
        rs = cstmt.executeQuery();
        ds = new TResultSet(rs);
        args[idxParam - 2] = cstmt.getString(idxParam - 1);
        args[idxParam - 1] = cstmt.getString(idxParam);
    } catch (SQLException e) {
        state = e.getErrorCode();
        sqlState = e.getSQLState();
        message = e.getMessage();
        throw e;
    } catch (Exception e) {
        message = e.getMessage();
        e.printStackTrace();
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
            }
        }
        if (cstmt != null) {
            try {
                cstmt.close();
            } catch (Exception e) {
            }
        }
        if (local_con != null) {
            try {
                local_con.close();
            } catch (Exception e) {
            }
        }
    }
    return ds;
}

From source file:pingpong.db.DBAccess.java

public TResultSet executeProcedure3(String sql, Object args[]) throws SQLException {
    Connection local_con = null;//from  w  w  w  .j  a va 2s.c om
    CallableStatement cstmt = null;
    ResultSet rs = null;
    TResultSet ds = null;

    sql = sql.replace("?)", "?,?)"); //
    log.debug("sql repalce:" + sql);
    int idxParam = (args == null) ? 0 : args.length + 1;
    try {
        if (m_con != null) {
            cstmt = m_con.prepareCall(sql);
        } else {
            local_con = getConnection();
            cstmt = local_con.prepareCall(sql);
        }
        if (args != null) {
            for (int i = 0; i < args.length - 2; i++) {
                log.debug("###########i: " + i + "/" + args[i] + "/" + args.length);
                if (args[i] instanceof Null) {
                    cstmt.setNull(i + 1, ((Null) args[i]).type);
                } else {
                    cstmt.setObject(i + 1, args[i]);
                }
            }

            cstmt.setString(idxParam - 2, "");
            cstmt.setString(idxParam - 1, "");
            cstmt.registerOutParameter(idxParam - 2, Types.VARCHAR);
            cstmt.registerOutParameter(idxParam - 1, Types.VARCHAR);
            // For Oracle
            //            cstmt.registerOutParameter(idxParam, OracleTypes.CURSOR);  //

            log.debug("###########sql:" + sql + "\nargs:" + args + " len:" + args.length);
        }

        //OracleCallableStatement ? getCursor() method  REF CURSOR 
        //JDBC ResultSet variable ? .         
        //          cstmt.executeQuery();          
        //          rs = (ResultSet) cstmt.getObject(idxParam);
        rs = cstmt.executeQuery();
        ds = new TResultSet(rs);

        args[idxParam - 3] = cstmt.getString(idxParam - 2); //call by refenene  000
        args[idxParam - 2] = cstmt.getString(idxParam - 1); //O.K
        log.debug(cstmt.getString(idxParam - 2) + "," + cstmt.getString(idxParam - 1));
    } catch (SQLException e) {
        state = e.getErrorCode();
        sqlState = e.getSQLState();
        message = e.getMessage();
        throw e;
    } catch (Exception e) {
        message = e.getMessage();
        e.printStackTrace();
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
            }
        }
        if (cstmt != null) {
            try {
                cstmt.close();
            } catch (Exception e) {
            }
        }
        if (local_con != null) {
            try {
                local_con.close();
            } catch (Exception e) {
            }
        }
    }
    return ds;
}

From source file:shnakkydoodle.measuring.provider.MetricsProviderSQLServer.java

/**
 * Delete Metric data - maintenance cleanup function
 * /* w  ww.j a  v a  2  s. c o  m*/
 * @param metricNamespace
 * @param startDate
 * @param endDate
 */
@Override
public void deleteMetricData(String metricNamespace, Date startDate, Date endDate) {
    Connection conn = null;
    CallableStatement stmt = null;
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    try {
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        conn = DriverManager.getConnection(this.host + ";user=" + this.username + ";password=" + this.password);

        // Delete metric data
        stmt = conn.prepareCall("uspMetricData_Delete(?,?,?,?)");
        stmt.setString(1, metricNamespace);
        stmt.setNull(2, Types.NULL);

        if (startDate != null)
            stmt.setString(3, sdf.format(startDate));
        else
            stmt.setString(3, null);

        if (endDate != null)
            stmt.setString(4, sdf.format(endDate));
        else
            stmt.setString(4, null);

        stmt.execute();
    } catch (SQLException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } finally {
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(conn);
    }
}

From source file:shnakkydoodle.measuring.provider.MetricsProviderSQLServer.java

/**
 * Get AlarmMetricAlarm by alarm id//from  ww  w .  j av a2 s  . co m
 * 
 * @param alarmMetricAlarmId
 * @param alarmName
 * @return AlarmMetricAlarm
 */
@Override
public AlarmMetricAlarm getMetricAlarm(Integer alarmMetricAlarmId, String alarmName) {
    Connection conn = null;
    CallableStatement stmt = null;
    ResultSet rs = null;

    AlarmMetricAlarm alarmMetricAlarm = null;
    AlarmComparisonOperator alarmComparisonOperator = AlarmComparisonOperator.None;
    MetricStatistic metricStatistic = MetricStatistic.None;
    MetricUnit metricUnit = MetricUnit.None;
    AlarmState alarmState = AlarmState.NONE;

    try {
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        conn = DriverManager.getConnection(this.host + ";user=" + this.username + ";password=" + this.password);

        stmt = conn.prepareCall("uspMetricAlarm_Get(?,?)");

        if (alarmMetricAlarmId == null)
            stmt.setNull(1, Types.NULL);
        else
            stmt.setInt(1, alarmMetricAlarmId);

        stmt.setString(2, alarmName);

        rs = stmt.executeQuery();

        while (rs.next()) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            alarmMetricAlarm = new AlarmMetricAlarm();
            alarmMetricAlarm.setAlarmMetricAlarmId(rs.getInt("MetricAlarmId"));
            alarmMetricAlarm.setComparisonOperator(
                    alarmComparisonOperator.findByValue(rs.getInt("AlarmComparisonOperatorId")));
            alarmMetricAlarm.setMetricSatistic(metricStatistic.findByValue(rs.getInt("MetricStatisticId")));
            alarmMetricAlarm.setMetricUnit(metricUnit.findByValue(rs.getInt("MetricUnitId")));
            alarmMetricAlarm.setAlarmName(rs.getString("Name"));
            alarmMetricAlarm.setAlarmDescription(rs.getString("Description"));
            alarmMetricAlarm.setMetricNamespace(rs.getString("MetricNamespace"));
            alarmMetricAlarm.setMetricName(rs.getString("MetricName"));
            alarmMetricAlarm.setThreshold(rs.getDouble("Threshold"));
            alarmMetricAlarm.setStateReason(rs.getString("StateReason"));
            alarmMetricAlarm.setStateReasonData(rs.getString("StateReasonData"));
            alarmMetricAlarm.setStateValue(alarmState.findByValue(rs.getInt("AlarmStateId")));
            alarmMetricAlarm.setDateModified(sdf.parse(rs.getString("DateModified")));
            alarmMetricAlarm.setDateCreated(sdf.parse(rs.getString("DateCreated")));
        }
    } catch (SQLException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } catch (ParseException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } finally {
        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(conn);
    }

    return alarmMetricAlarm;
}