Example usage for java.sql CallableStatement executeQuery

List of usage examples for java.sql CallableStatement executeQuery

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

Introduction

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

Prototype

ResultSet executeQuery() throws SQLException;

Source Link

Document

Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.

Usage

From source file:pingpong.db.DBAccess.java

public TResultSet executeProcedure1(String sql, Object args[]) throws SQLException {
    Connection local_con = null;/*  www.  j a  v a  2  s  .com*/
    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:com.wabacus.system.dataset.sqldataset.GetDataSetByStoreProcedure.java

private ResultSet doGetResultSet(ReportRequest rrequest, ReportBean rbean, ReportDataSetValueBean datasetbean,
        Object typeObj, StringBuffer systemParamsBuf) {
    log.debug(systemParamsBuf.toString());
    String procedure = datasetbean.getValue();
    if (rbean.getInterceptor() != null) {
        Object obj = rbean.getInterceptor().beforeLoadData(rrequest, rbean, typeObj, procedure);
        if (obj == null)
            return null;
        if (obj instanceof List || obj instanceof ResultSet) {
            throw new WabacusRuntimeException("" + rbean.getPath()
                    + "????ResultSetList");
        }//ww  w  .j a v  a  2s.c om
        if (!(obj instanceof String)) {
            throw new WabacusRuntimeException(
                    "" + rbean.getPath() + "??"
                            + obj.getClass().getName() + "??");
        }
        procedure = (String) obj;
    }
    if (Config.show_sql) {
        log.info("Execute sql: " + procedure);
    }
    CallableStatement cstmt = null;
    try {
        cstmt = rrequest.getConnection(datasetbean.getDatasource()).prepareCall(procedure);
        AbsDatabaseType dbtype = rrequest.getDbType(datasetbean.getDatasource());
        VarcharType varcharObj = (VarcharType) Config.getInstance().getDataTypeByClass(VarcharType.class);
        int idx = 1;
        if (datasetbean.getLstStoreProcedureParams() != null
                && datasetbean.getLstStoreProcedureParams().size() > 0) {
            ConditionBean cbeanTmp;
            for (String paramTmp : datasetbean.getLstStoreProcedureParams()) {
                if (WabacusAssistant.getInstance().isGetRequestContextValue(paramTmp)) {//request/session?
                    varcharObj.setPreparedStatementValue(idx,
                            WabacusAssistant.getInstance().getRequestContextStringValue(rrequest, paramTmp, ""),
                            cstmt, dbtype);
                } else if (Tools.isDefineKey("condition", paramTmp)) {
                    cbeanTmp = rbean.getSbean()
                            .getConditionBeanByName(Tools.getRealKeyByDefine("condition", paramTmp));
                    if (cbeanTmp.getIterator() > 1 || cbeanTmp.getCcolumnsbean() != null
                            || cbeanTmp.getCvaluesbean() != null) {
                        varcharObj.setPreparedStatementValue(idx, cbeanTmp.getConditionValueForSP(rrequest),
                                cstmt, dbtype);
                    } else {
                        cbeanTmp.getDatatypeObj().setPreparedStatementValue(idx,
                                cbeanTmp.getConditionValueForSP(rrequest), cstmt, dbtype);
                    }
                } else {
                    varcharObj.setPreparedStatementValue(idx, paramTmp, cstmt, dbtype);
                }
                idx++;
            }
        }
        cstmt.setString(idx++, systemParamsBuf.toString());

        if (dbtype instanceof Oracle) {
            cstmt.registerOutParameter(idx, OracleTypes.CURSOR);
        }
        rrequest.addUsedStatement(cstmt);
        cstmt.executeQuery();
        ResultSet rs = null;
        if (dbtype instanceof Oracle) {
            rs = (ResultSet) cstmt.getObject(idx);
        } else {
            rs = cstmt.getResultSet();
        }
        return rs;

    } catch (SQLException e) {
        throw new WabacusRuntimeException(
                "??" + rbean.getPath() + "?SQL" + procedure + "", e);
    }
}

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

/**
 * Get AlarmMetricAlarm by alarm id/*  w  w  w.  j a v  a 2  s  . com*/
 * 
 * @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;
}

From source file:pingpong.db.DBAccess.java

/**
 * ResultSet?  ?  ? .//from  w ww.ja v a2s.c  o  m
 * @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:shnakkydoodle.measuring.provider.MetricsProviderSQLServer.java

/**
 * Gets metric data//from ww  w  . j  a  va2s  .c om
 * 
 * @param metricNamespace
 * @param metricName
 * @param startDate
 * @param endDate
 * @return ArrayList<MetricData>
 */
@Override
public ArrayList<MetricData> getMetricData(String metricNamespace, String metricName, Date startDate,
        Date endDate) {
    Connection conn = null;
    CallableStatement stmt = null;
    ResultSet rs = null;
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    // create a container for the data
    ArrayList<MetricData> data = new ArrayList<MetricData>();

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

        stmt = conn.prepareCall("uspMetricData_GetMetricData(?,?,?,?)");
        stmt.setString(1, metricNamespace);
        stmt.setString(2, metricName);

        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);

        rs = stmt.executeQuery();

        while (rs.next()) {

            MetricUnit metricUnit = MetricUnit.None;

            MetricData dataitem = new MetricData();
            dataitem.setMetricId(rs.getInt("MetricDataId"));
            dataitem.setUnit(metricUnit.findByValue(rs.getInt("MetricUnitId")));
            dataitem.setMetricNamespace(rs.getString("MetricNamespace"));
            dataitem.setMetricName(rs.getString("MetricName"));
            dataitem.setValue(rs.getDouble("Value"));
            dataitem.setData(rs.getString("Data"));
            dataitem.setTimestamp(sdf.parse(rs.getString("DateCreated")));
            data.add(dataitem);
        }
    } 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 data;
}

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

/**
 * Get list of MetricSlaAlarmStatus(es) by MetricSlaId
 * //from  ww w  . ja v  a 2 s  .  co m
 * @param metricSlaId
 * @param startDate
 * @param endDate
 * @return ArrayList<MetricSlaAlarmStatus>
 */
@Override
public ArrayList<MetricSlaAlarmStatus> getMetricSlaAlarmStatus(Integer metricSlaId, Date startDate,
        Date endDate) {
    Connection conn = null;
    CallableStatement stmt = null;
    ResultSet rs = null;
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    AlarmState alarmState = AlarmState.NONE;

    // create a container for the data
    ArrayList<MetricSlaAlarmStatus> metricSlaAlarmStatusList = new ArrayList<MetricSlaAlarmStatus>();

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

        stmt = conn.prepareCall("uspMetricSlaAlarmStatus_GetBySlaId(?,?,?)");
        stmt.setInt(1, metricSlaId);

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

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

        rs = stmt.executeQuery();

        while (rs.next()) {
            MetricSLA metricSla = new MetricSLA();
            metricSla = getMetricSla(rs.getInt("MetricSlaId"), null);

            MetricSlaAlarmStatus dataitem = new MetricSlaAlarmStatus();
            dataitem.setMetricSlaAlarmStatusId(rs.getInt("MetricSlaAlarmStatusId"));
            dataitem.setMetricSLA(metricSla);
            dataitem.setAlarmState(alarmState.findByValue(rs.getInt("AlarmStateId")));
            dataitem.setTimestamp(sdf.parse(rs.getString("DateCreated")));

            metricSlaAlarmStatusList.add(dataitem);
        }
    } 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 metricSlaAlarmStatusList;
}

From source file:pingpong.db.DBAccess.java

public TResultSet executeProcedure3(String sql, Object args[]) throws SQLException {
    Connection local_con = null;//  w ww. ja va2s  . c o  m
    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:weave.servlets.AdminService.java

/**
 * @param conn An active SQL connection used to test the query.
 * @param query SQL query which may contain '?' marks for parameters.
 * @param params Optional list of parameters to pass to the SQL query.  May be null.
 * @return The Weave dataType metadata value to use, based on the result of the SQL query.
 *//*from w  w w.j  ava2 s  .c  o m*/
private String testQueryAndGetDataType(Connection conn, String query, Object[] params) throws RemoteException {
    CallableStatement cstmt = null;
    Statement stmt = null;
    ResultSet rs = null;
    DataType dataType = null;
    try {
        String dbms = conn.getMetaData().getDatabaseProductName();
        if (!dbms.equalsIgnoreCase(SQLUtils.SQLSERVER) && !dbms.equalsIgnoreCase(SQLUtils.ORACLE))
            query += " LIMIT 1";

        if (params == null || params.length == 0) {
            // We have to use Statement when there are no parameters, because CallableStatement
            // will fail in Microsoft SQL Server with "Incorrect syntax near the keyword 'SELECT'".
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
        } else {
            cstmt = conn.prepareCall(query);
            for (int i = 0; i < params.length; i++)
                cstmt.setObject(i + 1, params[i]);
            rs = cstmt.executeQuery();
        }

        dataType = DataType.fromSQLType(rs.getMetaData().getColumnType(2));
    } catch (SQLException e) {
        throw new RemoteException("Unable to execute generated query:\n" + query, e);
    } finally {
        SQLUtils.cleanup(rs);
        SQLUtils.cleanup(cstmt);
        SQLUtils.cleanup(stmt);
    }

    return dataType.toString();
}

From source file:org.kawanfw.sql.servlet.sql.ServerCallableStatement.java

/**
 * Execute the passed SQL Statement and return: <br>
 * - The result set as a List of Maps for SELECT statements. <br>
 * - The return code for other statements
 * //from  w  w w  .j  a v  a 2  s.c  o  m
 * @param sqlOrder
 *            the qsql order
 * @param sqlParms
 *            the sql parameters
 * @param out
 *            the output stream where to write to result set output
 * 
 * 
 * @throws SQLException
 */
private void callStatement(OutputStream out) throws SQLException, IOException {

    String sqlOrder = callableStatementHolder.getSqlOrder();

    debug("callableStatementHolder: " + callableStatementHolder.getSqlOrder());
    debug("sqlOrder               : " + sqlOrder);

    CallableStatement callableStatement = null;
    callableStatement = connection.prepareCall(sqlOrder);

    Map<Integer, Integer> parameterTypes = null;
    Map<Integer, String> parameterStringValues = null;

    // Class to set all the statement parameters
    ServerCallableStatementParameters serverCallableStatementParameters = null;

    try {

        ServerSqlUtilCallable.setCallableStatementProperties(callableStatement, callableStatementHolder);

        parameterTypes = callableStatementHolder.getParameterTypes();
        parameterStringValues = callableStatementHolder.getParameterStringValues();

        debug("before serverCallableStatementParameters");

        serverCallableStatementParameters = new ServerCallableStatementParameters(username, fileConfigurator,
                callableStatement, callableStatementHolder);
        serverCallableStatementParameters.setParameters();

        // Throws a SQL exception if the order is not authorized:
        debug("before new SqlSecurityChecker()");
        boolean isAllowedAfterAnalysis = true;

        /*
         * boolean isAllowedAfterAnalysis = sqlConfigurator
         * .allowStatementAfterAnalysis(username, connection, sqlOrder,
         * serverPreparedStatementParameters .getParameterValues());
         */
        if (!isAllowedAfterAnalysis) {

            String ipAddress = request.getRemoteAddr();
            SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, username, connection, ipAddress,
                    sqlOrder, serverCallableStatementParameters.getParameterValues());

            String message = Tag.PRODUCT_SECURITY + " [" + "{Callable Statement not authorized}"
                    + "{sql order : " + sqlOrder + "}" + "{sql parms : " + parameterTypes + "}"
                    + "{sql values: " + parameterStringValues + "}]";

            throw new SecurityException(message);
        }

        isAllowedAfterAnalysis = SqlConfiguratorCall.allowResultSetGetMetaData(sqlConfigurator, username,
                connection);

        debug("before callableStatement.executeQuery() / execute");

        ServerSqlUtil.setMaxRowsToReturn(callableStatement, sqlConfigurator);

        ResultSet rs = null;
        boolean hasResultSet = false;

        if (isExecuteRaw) {
            hasResultSet = callableStatement.execute();
            if (hasResultSet) {
                rs = callableStatement.getResultSet();
            }
        } else {
            rs = callableStatement.executeQuery();
        }

        // 1) Update and send back the CallableStatementHolder:
        updateCallableStatementHolderValues(callableStatement);

        //out.write(TransferStatus.SEND_OK + CR_LF);
        ServerSqlManager.writeLine(out, TransferStatus.SEND_OK);

        String jsonString = CallableStatementHolderTransportJson.toJson(callableStatementHolder);
        boolean doEncryptCallableStatement = SqlConfiguratorCall.encryptResultSet(sqlConfigurator);

        if (doEncryptCallableStatement) {
            jsonString = JsonLineEncrypter.encrypt(jsonString, commonsConfigurator);
        }

        //out.write(jsonString + CR_LF);
        ServerSqlManager.writeLine(out, jsonString);

        // 2) Send back the result set:

        if (hasResultSet || !isExecuteRaw) {
            try {

                // Horrible hack because ResultSetWriter is in common CE/EE
                // code and
                // we can't modify it now (to be clean in next CE version):
                StatementHolder statementHolder = new StatementHolder();
                statementHolder.setHtmlEncodingOn(callableStatementHolder.isHtmlEncodingOn());

                ResultSetWriter resultSetWriter = new ResultSetWriter(request, out, commonsConfigurator,
                        fileConfigurator, sqlConfigurator, username, sqlOrder, statementHolder);

                resultSetWriter.write(rs);
            } finally {
                if (rs != null) {
                    rs.close();
                }
            }
        } else {
            // Write a line saying there is no Result Set!
            //out.write(CallableParms.NO_RESULT_SET + CR_LF);
            ServerSqlManager.writeLine(out, CallableParms.NO_RESULT_SET);
        }

    } catch (SQLException e) {
        ServerLogger.getLogger().log(Level.WARNING,
                Tag.PRODUCT_EXCEPTION_RAISED + CR_LF + "CallableStatement statement: " + sqlOrder + CR_LF
                        + "- sql order : " + sqlOrder + CR_LF + "- sql parms : " + parameterTypes + CR_LF
                        + "- sql values: " + parameterStringValues + CR_LF + "- exception : " + e.toString());
        throw e;
    } finally {
        // Close the ServerPreparedStatementParameters
        if (serverCallableStatementParameters != null) {
            serverCallableStatementParameters.close();
        }

        if (callableStatement != null) {
            callableStatement.close();
        }

        // Clean all
        parameterTypes = null;
        parameterStringValues = null;
        serverCallableStatementParameters = null;

    }
}

From source file:org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.java

protected TableModel parametrizeAndQuery(final DataRow parameters, final String translatedQuery,
        final String[] preparedParameterNames) throws SQLException {
    final boolean callableStatementQuery = isCallableStatementQuery(translatedQuery);
    final boolean callableStatementUsed = callableStatementQuery || isCallableStatement(translatedQuery);
    final Statement statement;
    if (preparedParameterNames.length == 0) {
        statement = getConnection(parameters).createStatement(getBestResultSetType(parameters),
                ResultSet.CONCUR_READ_ONLY);
    } else {//w  w w  . ja va  2s.  c om
        if (callableStatementUsed) {
            final CallableStatement pstmt = getConnection(parameters).prepareCall(translatedQuery,
                    getBestResultSetType(parameters), ResultSet.CONCUR_READ_ONLY);
            if (isCallableStatementQuery(translatedQuery)) {
                pstmt.registerOutParameter(1, Types.OTHER);
                parametrize(parameters, preparedParameterNames, pstmt, false, 1);
            } else {
                parametrize(parameters, preparedParameterNames, pstmt, false, 0);
            }
            statement = pstmt;
        } else {
            final PreparedStatement pstmt = getConnection(parameters).prepareStatement(translatedQuery,
                    getBestResultSetType(parameters), ResultSet.CONCUR_READ_ONLY);
            parametrize(parameters, preparedParameterNames, pstmt, isExpandArrays(), 0);
            statement = pstmt;
        }
    }

    final Object queryLimit = parameters.get(DataFactory.QUERY_LIMIT);
    try {
        if (queryLimit instanceof Number) {
            final Number i = (Number) queryLimit;
            final int max = i.intValue();
            if (max > 0) {
                statement.setMaxRows(max);
            }
        }
    } catch (SQLException sqle) {
        // this fails for MySQL as their driver is buggy. We will not add workarounds here, as
        // all drivers are buggy and this is a race we cannot win. Put pressure on the driver
        // manufacturer instead.
        logger.warn("Driver indicated error: Failed to set query-limit: " + queryLimit, sqle);
    }
    final Object queryTimeout = parameters.get(DataFactory.QUERY_TIMEOUT);
    try {
        if (queryTimeout instanceof Number) {
            final Number i = (Number) queryTimeout;
            final int seconds = i.intValue();
            if (seconds > 0) {
                statement.setQueryTimeout(seconds);
            }
        }
    } catch (SQLException sqle) {
        logger.warn("Driver indicated error: Failed to set query-timeout: " + queryTimeout, sqle);
    }

    // Track the currently running statement - just in case someone needs to cancel it
    final ResultSet res;
    try {
        currentRunningStatement = statement;
        if (preparedParameterNames.length == 0) {
            res = statement.executeQuery(translatedQuery);
        } else {
            final PreparedStatement pstmt = (PreparedStatement) statement;
            res = pstmt.executeQuery();
        }
    } finally {
        currentRunningStatement = null;
    }

    // equalsIgnore, as this is what the ResultSetTableModelFactory uses.
    final boolean simpleMode = "simple".equalsIgnoreCase(getConfiguration().getConfigProperty( //$NON-NLS-1$
            ResultSetTableModelFactory.RESULTSET_FACTORY_MODE)); //$NON-NLS-1$

    if (simpleMode) {
        return ResultSetTableModelFactory.getInstance().generateDefaultTableModel(res, columnNameMapping);
    }
    return ResultSetTableModelFactory.getInstance().createTableModel(res, columnNameMapping, true);
}