Example usage for java.sql CallableStatement getUpdateCount

List of usage examples for java.sql CallableStatement getUpdateCount

Introduction

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

Prototype

int getUpdateCount() throws SQLException;

Source Link

Document

Retrieves the current result as an update count; if the result is a ResultSet object or there are no more results, -1 is returned.

Usage

From source file:Main.java

public static void main(String args[]) throws Exception {
    String URL = "jdbc:microsoft:sqlserver://yourServer:1433;databasename=pubs";
    String userName = "yourUser";
    String password = "yourPassword";

    Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
    Connection con = DriverManager.getConnection(URL, userName, password);
    CallableStatement callstmt = con
            .prepareCall("INSERT INTO myIdentTable (col2) VALUES (?);SELECT @@IDENTITY");
    callstmt.setString(1, "testInputBatch");
    callstmt.execute();/*from   ww  w .j  av a2  s  .c o m*/

    int iUpdCount = callstmt.getUpdateCount();
    boolean bMoreResults = true;
    ResultSet rs = null;
    int myIdentVal = -1; // to store the @@IDENTITY

    while (bMoreResults || iUpdCount != -1) {
        rs = callstmt.getResultSet();
        if (rs != null) {
            rs.next();
            myIdentVal = rs.getInt(1);
        }
        bMoreResults = callstmt.getMoreResults();
        iUpdCount = callstmt.getUpdateCount();
    }
    callstmt.close();
    con.close();
}

From source file:lib.JdbcTemplate.java

@Override
public Map<String, Object> call(CallableStatementCreator csc, List<SqlParameter> declaredParameters)
        throws DataAccessException {

    final List<SqlParameter> updateCountParameters = new ArrayList<SqlParameter>();
    final List<SqlParameter> resultSetParameters = new ArrayList<SqlParameter>();
    final List<SqlParameter> callParameters = new ArrayList<SqlParameter>();
    for (SqlParameter parameter : declaredParameters) {
        if (parameter.isResultsParameter()) {
            if (parameter instanceof SqlReturnResultSet) {
                resultSetParameters.add(parameter);
            } else {
                updateCountParameters.add(parameter);
            }/*from  w ww.  j ava2  s  . com*/
        } else {
            callParameters.add(parameter);
        }
    }
    return execute(csc, new CallableStatementCallback<Map<String, Object>>() {
        @Override
        public Map<String, Object> doInCallableStatement(CallableStatement cs) throws SQLException {
            boolean retVal = cs.execute();
            int updateCount = cs.getUpdateCount();
            if (logger.isDebugEnabled()) {
                logger.debug("CallableStatement.execute() returned '" + retVal + "'");
                logger.debug("CallableStatement.getUpdateCount() returned " + updateCount);
            }
            Map<String, Object> returnedResults = createResultsMap();
            if (retVal || updateCount != -1) {
                returnedResults.putAll(
                        extractReturnedResults(cs, updateCountParameters, resultSetParameters, updateCount));
            }
            returnedResults.putAll(extractOutputParameters(cs, callParameters));
            return returnedResults;
        }
    });
}

From source file:com.toxind.benchmark.thrid.ibatis.sqlmap.engine.execution.SqlExecutor.java

/**
 * Execute a stored procedure that updates data
 * /*  w w w.  j a v  a2 s . c  o  m*/
 * @param statementScope
 *            - the request scope
 * @param conn
 *            - the database connection
 * @param sql
 *            - the SQL to call the procedure
 * @param parameters
 *            - the parameters for the procedure
 * @return - the rows impacted by the procedure
 * @throws SQLException
 *             - if the procedure fails
 */
public int executeUpdateProcedure(StatementScope statementScope, Connection conn, String sql,
        Object[] parameters) throws SQLException {
    ErrorContext errorContext = statementScope.getErrorContext();
    errorContext.setActivity("executing update procedure");
    errorContext.setObjectId(sql);
    CallableStatement cs = null;
    setupResultObjectFactory(statementScope);
    int rows = 0;
    try {
        errorContext.setMoreInfo("Check the SQL Statement (preparation failed).");
        cs = prepareCall(statementScope.getSession(), conn, sql);
        setStatementTimeout(statementScope.getStatement(), cs);
        ParameterMap parameterMap = statementScope.getParameterMap();
        ParameterMapping[] mappings = parameterMap.getParameterMappings();
        errorContext.setMoreInfo("Check the output parameters (register output parameters failed).");
        registerOutputParameters(cs, mappings);
        errorContext.setMoreInfo("Check the parameters (set parameters failed).");
        parameterMap.setParameters(statementScope, cs, parameters);
        errorContext.setMoreInfo("Check the statement (update procedure failed).");
        cs.execute();
        rows = cs.getUpdateCount();
        errorContext.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
        retrieveOutputParameters(statementScope, cs, mappings, parameters, null);
    } finally {
        closeStatement(statementScope.getSession(), cs);
    }
    return rows;
}

From source file:com.jbrisbin.vpc.jobsched.sql.SqlMessageHandler.java

public SqlMessage handleMessage(final SqlMessage msg) throws Exception {
    log.debug("handling message: " + msg.toString());

    DataSource ds = appCtx.getBean(msg.getDatasource(), DataSource.class);
    JdbcTemplate tmpl = new JdbcTemplate(ds);

    String sql = msg.getSql();/*ww  w.  j  ava 2  s . co m*/
    CallableStatementCreator stmtCreator = null;
    CallableStatementCallback<SqlMessage> callback = null;
    if (sql.startsWith("plugin:")) {
        // Use a plugin to get the sql
        String pluginName = (sql.contains("?") ? sql.substring(7, sql.indexOf('?')) : sql.substring(7));
        final Plugin plugin = groovyPluginManager.getPlugin(pluginName);
        Map<String, Object> vars = new LinkedHashMap<String, Object>();
        vars.put("message", msg);
        vars.put("datasource", ds);
        vars.put("listen", groovyClosureFactory.createListenClosure(msg));
        vars.put("mapreduce", groovyClosureFactory.createMapReduceClosure(msg));
        plugin.setContext(vars);

        // Execute this plugin
        plugin.run();

        Object o = plugin.get("sql");
        if (null != o && o instanceof Closure) {
            sql = ((Closure) o).call(msg).toString();
        } else if (o instanceof String || o instanceof GString) {
            sql = o.toString();
        } else {
            throw new IllegalStateException("Can't convert " + String.valueOf(o) + " to SQL statement.");
        }
        msg.setSql(sql);

        o = plugin.get("statementCreator");
        if (null != o && o instanceof Closure) {
            stmtCreator = new CallableStatementCreator() {
                public CallableStatement createCallableStatement(Connection con) throws SQLException {
                    Object obj = ((Closure) plugin.get("statementCreator")).call(new Object[] { con, msg });
                    log.debug("from plugin statementCreator: " + String.valueOf(obj));
                    return (CallableStatement) obj;
                }
            };
        } else {
            throw new IllegalStateException("Can't convert " + String.valueOf(o)
                    + " to CallableStatementCreator. Define a closure named 'statementCreator' in your plugin.");
        }

        o = plugin.get("callback");
        if (null != o && o instanceof Closure) {
            callback = new CallableStatementCallback<SqlMessage>() {
                public SqlMessage doInCallableStatement(CallableStatement cs)
                        throws SQLException, DataAccessException {
                    Object obj = ((Closure) plugin.get("callback")).call(new Object[] { cs, msg });
                    log.debug("from plugin callback: " + String.valueOf(obj));
                    return (SqlMessage) obj;
                }
            };
        } else {
            throw new IllegalStateException("Can't convert " + String.valueOf(o)
                    + " to CallableStatementCallback. Define a closure named 'callback' in your plugin.");
        }
    } else {
        stmtCreator = new CallableStatementCreator() {
            public CallableStatement createCallableStatement(Connection connection) throws SQLException {
                CallableStatement stmt = connection.prepareCall(msg.getSql());
                List<Object> params = msg.getParams();
                if (null != params) {
                    int index = 1;
                    for (Object obj : params) {
                        stmt.setObject(index++, obj);
                    }
                }
                return stmt;
            }
        };
        callback = new CallableStatementCallback<SqlMessage>() {
            public SqlMessage doInCallableStatement(CallableStatement callableStatement)
                    throws SQLException, DataAccessException {
                if (null == msg.getResults().getData()) {
                    msg.getResults().setData(new ArrayList<List<Object>>());
                }
                if (callableStatement.execute()) {
                    ResultSet results = callableStatement.getResultSet();

                    // Pull out column names
                    ResultSetMetaData meta = results.getMetaData();
                    String[] columns = new String[meta.getColumnCount()];
                    for (int i = 1; i <= meta.getColumnCount(); i++) {
                        columns[i - 1] = meta.getColumnName(i);
                    }
                    msg.getResults().getColumnNames().addAll(Arrays.asList(columns));

                    int total = 0;
                    while (results.next()) {
                        List<Object> row = new ArrayList<Object>(columns.length);
                        for (int i = 1; i <= columns.length; i++) {
                            row.add(results.getObject(i));
                        }
                        msg.getResults().getData().add(row);
                        total++;
                    }
                    msg.getResults().setTotalRows(total);

                } else {
                    msg.getResults().getColumnNames().add("updateCount");
                    msg.getResults().setTotalRows(1);
                    List<Object> updCnt = new ArrayList<Object>(1);
                    updCnt.add(callableStatement.getUpdateCount());
                    msg.getResults().getData().add(updCnt);
                }
                return msg;
            }
        };
    }
    try {
        tmpl.setExceptionTranslator(appCtx.getBean(SQLExceptionTranslator.class));
    } catch (NoSuchBeanDefinitionException notfound) {
        // IGNORED
    }

    if (null != stmtCreator && null != callback) {
        try {
            tmpl.execute(stmtCreator, callback);
        } catch (Throwable t) {
            log.error(t.getMessage(), t);
            List<String> errors = new ArrayList<String>();
            errors.add(t.getMessage());
            Throwable cause = t.getCause();
            if (null != cause) {
                do {
                    errors.add(cause.getMessage());
                } while (null != (cause = cause.getCause()));
            }
            msg.getResults().setErrors(errors);
        }
    } else {
        log.warn("CallableStatementCreator and/or CallableStatementCallback where empty. "
                + "Make sure your plugin provides these under 'statementCreator' and 'callback' respectively.");
    }
    return msg;
}

From source file:lib.JdbcTemplate.java

/**
 * Extract returned ResultSets from the completed stored procedure.
 * @param cs JDBC wrapper for the stored procedure
 * @param updateCountParameters Parameter list of declared update count parameters for the stored procedure
 * @param resultSetParameters Parameter list of declared resultSet parameters for the stored procedure
 * @return Map that contains returned results
 *//*from  w  w  w  .j  av a  2s. co m*/
protected Map<String, Object> extractReturnedResults(CallableStatement cs,
        List<SqlParameter> updateCountParameters, List<SqlParameter> resultSetParameters, int updateCount)
        throws SQLException {

    Map<String, Object> returnedResults = new HashMap<String, Object>();
    int rsIndex = 0;
    int updateIndex = 0;
    boolean moreResults;
    if (!this.skipResultsProcessing) {
        do {
            if (updateCount == -1) {
                if (resultSetParameters != null && resultSetParameters.size() > rsIndex) {
                    SqlReturnResultSet declaredRsParam = (SqlReturnResultSet) resultSetParameters.get(rsIndex);
                    returnedResults.putAll(processResultSet(cs.getResultSet(), declaredRsParam));
                    rsIndex++;
                } else {
                    if (!this.skipUndeclaredResults) {
                        String rsName = RETURN_RESULT_SET_PREFIX + (rsIndex + 1);
                        SqlReturnResultSet undeclaredRsParam = new SqlReturnResultSet(rsName,
                                new ColumnMapRowMapper());
                        if (logger.isDebugEnabled()) {
                            logger.debug("Added default SqlReturnResultSet parameter named '" + rsName + "'");
                        }
                        returnedResults.putAll(processResultSet(cs.getResultSet(), undeclaredRsParam));
                        rsIndex++;
                    }
                }
            } else {
                if (updateCountParameters != null && updateCountParameters.size() > updateIndex) {
                    SqlReturnUpdateCount ucParam = (SqlReturnUpdateCount) updateCountParameters
                            .get(updateIndex);
                    String declaredUcName = ucParam.getName();
                    returnedResults.put(declaredUcName, updateCount);
                    updateIndex++;
                } else {
                    if (!this.skipUndeclaredResults) {
                        String undeclaredName = RETURN_UPDATE_COUNT_PREFIX + (updateIndex + 1);
                        if (logger.isDebugEnabled()) {
                            logger.debug("Added default SqlReturnUpdateCount parameter named '" + undeclaredName
                                    + "'");
                        }
                        returnedResults.put(undeclaredName, updateCount);
                        updateIndex++;
                    }
                }
            }
            moreResults = cs.getMoreResults();
            updateCount = cs.getUpdateCount();
            if (logger.isDebugEnabled()) {
                logger.debug("CallableStatement.getUpdateCount() returned " + updateCount);
            }
        } while (moreResults || updateCount != -1);
    }
    return returnedResults;
}

From source file:org.castor.cpa.persistence.sql.keygen.SequenceDuringKeyGenerator.java

/**
 * {@inheritDoc}//  w ww  . j  av a 2  s .  c o m
 */
public Object executeStatement(final Database database, final CastorConnection conn, final Identity identity,
        final ProposedEntity entity) throws PersistenceException {
    CastorStatement stmt = conn.createStatement();
    CallableStatement cstmt = null;
    try {
        SQLColumnInfo[] ids = _engine.getColumnInfoForIdentities();
        stmt.prepareStatement(_insert);
        String statement = stmt.toString();

        statement += " RETURNING ";
        statement += _factory.quoteName(ids[0].getName());
        statement += " INTO ?";
        statement = "{call " + statement + "}";

        stmt.setStatement(conn.getConnection().prepareCall(statement));

        if (LOG.isTraceEnabled()) {
            LOG.trace(Messages.format("jdo.creating", _engineType, stmt.toString()));
        }

        bindFields(entity, stmt);

        if (LOG.isTraceEnabled()) {
            LOG.trace(Messages.format("jdo.creating", _engineType, stmt.toString()));
        }

        // generate key during INSERT.
        cstmt = (CallableStatement) stmt.getStatement();

        int sqlType = ids[0].getSqlType();
        cstmt.registerOutParameter(stmt.getParameterSize() + 1, sqlType);

        if (LOG.isDebugEnabled()) {
            LOG.debug(Messages.format("jdo.creating", _engineType, cstmt.toString()));
        }

        cstmt.execute();

        // first skip all results "for maximum portability"
        // as proposed in CallableStatement javadocs.
        while (cstmt.getMoreResults() || (cstmt.getUpdateCount() != -1)) {
            // no code to execute
        }

        // identity is returned in the last parameter.
        // workaround for INTEGER type in Oracle getObject returns BigDecimal.
        Object temp;
        if (sqlType == java.sql.Types.INTEGER) {
            temp = new Integer(cstmt.getInt(stmt.getParameterSize() + 1));
        } else {
            temp = cstmt.getObject(stmt.getParameterSize() + 1);
        }
        return new Identity(ids[0].toJava(temp));
    } catch (SQLException except) {
        LOG.fatal(Messages.format("jdo.storeFatal", _engineType, stmt.toString()), except);
        throw new PersistenceException(Messages.format("persist.nested", except), except);
    } finally {
        //close statement
        try {
            if (cstmt != null) {
                cstmt.close();
            }
        } catch (SQLException e) {
            LOG.warn("Problem closing JDBC statement", e);
        }
        try {
            stmt.close();
        } catch (SQLException e) {
            LOG.warn("Problem closing JDBC statement", e);
        }
    }
}

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  ww  w . j ava  2 s.  c o  m*/
 */
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.executequery.databasemediators.spi.DefaultStatementExecutor.java

/** <p>Executes the specified procedure and returns
 *  a <code>ResultSet</code> object from this query.
 *  <p>If an exception occurs, null is returned and
 *  the relevant error message, if available, assigned
 *  to this object for retrieval./*from  w  ww.  j a  v a 2 s  . co  m*/
 *
 *  @param  the SQL procedure to execute
 *  @return the query result
 */
private SqlStatementResult executeProcedure(String query) throws SQLException {

    if (!prepared()) {

        return statementResult;
    }

    //Log.debug("query " + query);

    String execString = "EXECUTE ";
    String callString = "CALL ";

    int nameIndex = -1;
    int index = query.toUpperCase().indexOf(execString);

    // check if EXECUTE was entered
    if (index != -1) {

        nameIndex = execString.length();

    } else { // must be CALL

        nameIndex = callString.length();
    }

    String procedureName = null;

    // check for input brackets
    boolean possibleParams = false;
    index = query.indexOf("(", nameIndex);
    if (index != -1) {

        possibleParams = true;
        procedureName = query.substring(nameIndex, index);

    } else {

        procedureName = query.substring(nameIndex);
    }

    String prefix = prefixFromName(procedureName);
    procedureName = suffixFromName(procedureName);

    DatabaseHost host = new DatabaseObjectFactoryImpl().createDatabaseHost(databaseConnection);

    if (prefix == null) {

        prefix = host.getDefaultNamePrefix();
    }

    DatabaseExecutable procedure = host.getDatabaseSource(prefix).getProcedure(procedureName);
    if (procedure == null) { // hedge

        procedure = host.getDatabaseSource(prefix).getFunction(procedureName);
    }

    if (procedure != null) {

        if (possibleParams) {

            String params = query.substring(index + 1, query.indexOf(")"));

            if (!MiscUtils.isNull(params)) {

                // check that the proc accepts params
                //                    if (!procedure.hasParameters()) {
                //                    
                //                        statementResult.setSqlException(new SQLException("Procedure call was invalid"));
                //                        return statementResult;
                //                    }

                int paramIndex = 0;
                ProcedureParameter[] parameters = procedure.getParametersArray();

                // extract the parameters
                StringTokenizer st = new StringTokenizer(params, ",");

                // no defined params from the meta data but params supplied ??
                // attempt to execute as supplied and bubble up db error if an issue
                if (parameters.length == 0) {

                    parameters = new ProcedureParameter[st.countTokens()];
                    for (int i = 0, n = st.countTokens(); i < n; i++) {

                        procedure.addParameter("UNKNOWN", DatabaseMetaData.procedureColumnIn, Types.OTHER,
                                "OTHER", -1);
                    }

                    parameters = procedure.getParametersArray();
                }

                while (st.hasMoreTokens()) {

                    String value = st.nextToken().trim();

                    // check applicable param
                    for (int i = paramIndex; i < parameters.length; i++) {
                        paramIndex++;

                        int type = parameters[i].getType();
                        if (type == DatabaseMetaData.procedureColumnIn
                                || type == DatabaseMetaData.procedureColumnInOut) {

                            // check the data type and remove quotes if char
                            int dataType = parameters[i].getDataType();
                            if (dataType == Types.CHAR || dataType == Types.VARCHAR
                                    || dataType == Types.LONGVARCHAR) {

                                if (value.indexOf("'") != -1) {
                                    // assuming quotes at start and end
                                    value = value.substring(1, value.length() - 1);
                                }

                            }

                            parameters[i].setValue(value);
                            break;
                        }
                    }

                }

            }
        }

        // execute the procedure
        return execute(procedure);

    } else {

        // just run it...

        CallableStatement cstmnt = null;
        try {

            cstmnt = conn.prepareCall(query);
            boolean hasResultSet = cstmnt.execute();

            if (!hasResultSet) {

                statementResult.setUpdateCount(cstmnt.getUpdateCount());

            } else {

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

        } catch (SQLException e) {

            handleException(e);
            statementResult.setSqlException(e);
        }

        return statementResult;
        /*
                
        statementResult.setSqlException(
            new SQLException("Procedure or Function name specified is invalid"));
                
        return statementResult;
                
        */
    }

}

From source file:org.opoo.oqs.spring.SpringQuery.java

protected Object doCall() throws QueryException {
    final PreparedStatementSetter pss = new ArgTypePreparedStatementSetter(valueArray(), typeArray());
    final ResultSetExtractor rse = createResultSetExtractor(createListResultSetHandler());
    return jdbcTemplate.execute(getSql(), new CallableStatementCallback() {
        public Object doInCallableStatement(CallableStatement callableStatement)
                throws SQLException, DataAccessException {

            if (getQueryTimeout() > 0) {
                callableStatement.setQueryTimeout(getQueryTimeout());
            }/*  w  w w .  jav  a 2  s .  c  om*/

            pss.setValues(callableStatement);

            boolean retVal = callableStatement.execute();
            int updateCount = callableStatement.getUpdateCount();
            if (log.isDebugEnabled()) {
                log.debug("CallableStatement.execute() returned '" + retVal + "'");
                log.debug("CallableStatement.getUpdateCount() returned " + updateCount);
            }

            ResultSet rs = callableStatement.getResultSet();
            try {
                if (rs != null && rse != null) {
                    return rse.extractData(rs);
                }
            } finally {
                JdbcUtils.closeResultSet(rs);
            }

            if (updateCount > 0) {
                return new Integer(updateCount);
            }
            return null;
        }
    });
}

From source file:org.springframework.jdbc.core.JdbcTemplate.java

public Map call(CallableStatementCreator csc, final List declaredParameters) throws DataAccessException {
    return (Map) execute(csc, new CallableStatementCallback() {
        public Object doInCallableStatement(CallableStatement cs) throws SQLException {
            boolean retVal = cs.execute();
            int updateCount = cs.getUpdateCount();
            if (logger.isDebugEnabled()) {
                logger.debug("CallableStatement.execute returned [" + retVal + "]");
                logger.debug("CallableStatement.getUpdateCount returned [" + updateCount + "]");
            }/*from  ww  w  .  j  av a  2s.c  o  m*/
            Map returnedResults = new HashMap();
            if (retVal || updateCount != -1) {
                returnedResults.putAll(extractReturnedResultSets(cs, declaredParameters, updateCount));
            }
            returnedResults.putAll(extractOutputParameters(cs, declaredParameters));
            return returnedResults;
        }
    });
}