Examples with CallableStatement java.sql.CallableStatement used on opensource projects

Example 1 with CallableStatement

use of java.sql.CallableStatement in project groovy by apache.

the class Sql method callWithRows.

/**
     * Base internal method for call(), callWithRows(), and callWithAllRows() style of methods.
     * <p>
     * Performs a stored procedure call with the given parameters,
     * calling the closure once with all result objects,
     * and also returning the rows of the ResultSet(s) (if processResultSets is set to
     * Sql.FIRST_RESULT_SET, Sql.ALL_RESULT_SETS)
     * <p>
     * Main purpose of processResultSets param is to retain original call() method
     * performance when this is set to Sql.NO_RESULT_SETS
     * <p>
     * Resource handling is performed automatically where appropriate.
     *
     * @param sql     the sql statement
     * @param params  a list of parameters
     * @param processResultsSets the result sets to process, either Sql.NO_RESULT_SETS, Sql.FIRST_RESULT_SET, or Sql.ALL_RESULT_SETS
     * @param closure called once with all out parameter results
     * @return a list of GroovyRowResult objects
     * @throws SQLException if a database access error occurs
     * @see #callWithRows(String, List, Closure)
     */
protected List<List<GroovyRowResult>> callWithRows(String sql, List<Object> params, int processResultsSets, Closure closure) throws SQLException {
    Connection connection = createConnection();
    CallableStatement statement = null;
    List<GroovyResultSet> resultSetResources = new ArrayList<GroovyResultSet>();
    try {
        statement = getCallableStatement(connection, sql, params);
        boolean hasResultSet = statement.execute();
        List<Object> results = new ArrayList<Object>();
        int indx = 0;
        int inouts = 0;
        for (Object value : params) {
            if (value instanceof OutParameter) {
                if (value instanceof ResultSetOutParameter) {
                    GroovyResultSet resultSet = CallResultSet.getImpl(statement, indx);
                    resultSetResources.add(resultSet);
                    results.add(resultSet);
                } else {
                    Object o = statement.getObject(indx + 1);
                    if (o instanceof ResultSet) {
                        GroovyResultSet resultSet = new GroovyResultSetProxy((ResultSet) o).getImpl();
                        results.add(resultSet);
                        resultSetResources.add(resultSet);
                    } else {
                        results.add(o);
                    }
                }
                inouts++;
            }
            indx++;
        }
        closure.call(results.toArray(new Object[inouts]));
        List<List<GroovyRowResult>> resultSets = new ArrayList<List<GroovyRowResult>>();
        if (processResultsSets == NO_RESULT_SETS) {
            resultSets.add(new ArrayList<GroovyRowResult>());
            return resultSets;
        }
        //Check both hasResultSet and getMoreResults() because of differences in vendor behavior
        if (!hasResultSet) {
            hasResultSet = statement.getMoreResults();
        }
        while (hasResultSet && (processResultsSets != NO_RESULT_SETS)) {
            resultSets.add(asList(sql, statement.getResultSet()));
            if (processResultsSets == FIRST_RESULT_SET) {
                break;
            } else {
                hasResultSet = statement.getMoreResults();
            }
        }
        return resultSets;
    } catch (SQLException e) {
        LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage());
        throw e;
    } finally {
        for (GroovyResultSet rs : resultSetResources) {
            closeResources(null, null, rs);
        }
        closeResources(connection, statement);
    }
}

Example 2 with CallableStatement

use of java.sql.CallableStatement in project groovy by apache.

the class Sql method setObject.

/**
     * Strategy method allowing derived classes to handle types differently
     * such as for CLOBs etc.
     *
     * @param statement the statement of interest
     * @param i         the index of the object of interest
     * @param value     the new object value
     * @throws SQLException if a database access error occurs
     */
protected void setObject(PreparedStatement statement, int i, Object value) throws SQLException {
    if (value instanceof InParameter || value instanceof OutParameter) {
        if (value instanceof InParameter) {
            InParameter in = (InParameter) value;
            Object val = in.getValue();
            if (null == val) {
                statement.setNull(i, in.getType());
            } else {
                statement.setObject(i, val, in.getType());
            }
        }
        if (value instanceof OutParameter) {
            try {
                OutParameter out = (OutParameter) value;
                ((CallableStatement) statement).registerOutParameter(i, out.getType());
            } catch (ClassCastException e) {
                throw new SQLException("Cannot register out parameter.");
            }
        }
    } else {
        try {
            statement.setObject(i, value);
        } catch (SQLException e) {
            if (value == null) {
                SQLException se = new SQLException("Your JDBC driver may not support null arguments for setObject. Consider using Groovy's InParameter feature." + (e.getMessage() == null ? "" : " (CAUSE: " + e.getMessage() + ")"));
                se.setNextException(e);
                throw se;
            } else {
                throw e;
            }
        }
    }
}

Example 3 with CallableStatement

use of java.sql.CallableStatement in project hive by apache.

the class Commands method getConfInternal.

/**
   * Use call statement to retrieve the configurations for substitution and sql for the substitution.
   *
   * @param call
   * @return
   */
private BufferedRows getConfInternal(boolean call) {
    Statement stmnt = null;
    BufferedRows rows = null;
    try {
        boolean hasResults = false;
        DatabaseConnection dbconn = beeLine.getDatabaseConnection();
        Connection conn = null;
        if (dbconn != null)
            conn = dbconn.getConnection();
        if (conn != null) {
            if (call) {
                stmnt = conn.prepareCall("set");
                hasResults = ((CallableStatement) stmnt).execute();
            } else {
                stmnt = beeLine.createStatement();
                hasResults = stmnt.execute("set");
            }
        }
        if (hasResults) {
            ResultSet rs = stmnt.getResultSet();
            rows = new BufferedRows(beeLine, rs);
        }
    } catch (SQLException e) {
        beeLine.error(e);
    } finally {
        if (stmnt != null) {
            try {
                stmnt.close();
            } catch (SQLException e1) {
                beeLine.error(e1);
            }
        }
    }
    return rows;
}

Example 4 with CallableStatement

use of java.sql.CallableStatement in project hive by apache.

the class Commands method executeInternal.

// Return false only occurred error when execution the sql and the sql should follow the rules
// of beeline.
private boolean executeInternal(String sql, boolean call) {
    if (!beeLine.isBeeLine()) {
        sql = cliToBeelineCmd(sql);
    }
    if (sql == null || sql.length() == 0) {
        return true;
    }
    if (beeLine.isComment(sql)) {
        //skip this and rest cmds in the line
        return true;
    }
    // is source CMD
    if (isSourceCMD(sql)) {
        return sourceFile(sql);
    }
    if (sql.startsWith(BeeLine.COMMAND_PREFIX)) {
        return beeLine.execCommandWithPrefix(sql);
    }
    String prefix = call ? "call" : "sql";
    if (sql.startsWith(prefix)) {
        sql = sql.substring(prefix.length());
    }
    // batch statements?
    if (beeLine.getBatch() != null) {
        beeLine.getBatch().add(sql);
        return true;
    }
    if (!(beeLine.assertConnection())) {
        return false;
    }
    ClientHook hook = ClientCommandHookFactory.get().getHook(beeLine, sql);
    try {
        Statement stmnt = null;
        boolean hasResults;
        Thread logThread = null;
        try {
            long start = System.currentTimeMillis();
            if (call) {
                stmnt = beeLine.getDatabaseConnection().getConnection().prepareCall(sql);
                hasResults = ((CallableStatement) stmnt).execute();
            } else {
                stmnt = beeLine.createStatement();
                if (beeLine.getOpts().isSilent()) {
                    hasResults = stmnt.execute(sql);
                } else {
                    InPlaceUpdateStream.EventNotifier eventNotifier = new InPlaceUpdateStream.EventNotifier();
                    logThread = new Thread(createLogRunnable(stmnt, eventNotifier));
                    logThread.setDaemon(true);
                    logThread.start();
                    if (stmnt instanceof HiveStatement) {
                        HiveStatement hiveStatement = (HiveStatement) stmnt;
                        hiveStatement.setInPlaceUpdateStream(new BeelineInPlaceUpdateStream(beeLine.getErrorStream(), eventNotifier));
                    }
                    hasResults = stmnt.execute(sql);
                    logThread.interrupt();
                    logThread.join(DEFAULT_QUERY_PROGRESS_THREAD_TIMEOUT);
                }
            }
            beeLine.showWarnings();
            if (hasResults) {
                do {
                    ResultSet rs = stmnt.getResultSet();
                    try {
                        int count = beeLine.print(rs);
                        long end = System.currentTimeMillis();
                        beeLine.info(beeLine.loc("rows-selected", count) + " " + beeLine.locElapsedTime(end - start));
                    } finally {
                        if (logThread != null) {
                            logThread.join(DEFAULT_QUERY_PROGRESS_THREAD_TIMEOUT);
                            showRemainingLogsIfAny(stmnt);
                            logThread = null;
                        }
                        rs.close();
                    }
                } while (BeeLine.getMoreResults(stmnt));
            } else {
                int count = stmnt.getUpdateCount();
                long end = System.currentTimeMillis();
                beeLine.info(beeLine.loc("rows-affected", count) + " " + beeLine.locElapsedTime(end - start));
            }
        } finally {
            if (logThread != null) {
                if (!logThread.isInterrupted()) {
                    logThread.interrupt();
                }
                logThread.join(DEFAULT_QUERY_PROGRESS_THREAD_TIMEOUT);
                showRemainingLogsIfAny(stmnt);
            }
            if (stmnt != null) {
                stmnt.close();
            }
        }
    } catch (Exception e) {
        return beeLine.error(e);
    }
    beeLine.showWarnings();
    if (hook != null) {
        hook.postHook(beeLine);
    }
    return true;
}

Example 5 with CallableStatement

use of java.sql.CallableStatement in project tomcat by apache.

the class TestSlowQueryReport method testSlowSqlJmx.

@Test
public void testSlowSqlJmx() throws Exception {
    int count = 1;
    Connection con = this.datasource.getConnection();
    for (int i = 0; i < count; i++) {
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(superSlowSql);
        rs.close();
        st.close();
    }
    Map<String, SlowQueryReport.QueryStats> map = SlowQueryReport.getPoolStats(datasource.getPool().getName());
    Assert.assertNotNull(map);
    Assert.assertEquals(1, map.size());
    String key = map.keySet().iterator().next();
    SlowQueryReport.QueryStats stats = map.get(key);
    System.out.println("Stats:" + stats);
    ClientListener listener = new ClientListener();
    ConnectionPool pool = datasource.getPool();
    ManagementFactory.getPlatformMBeanServer().addNotificationListener(new SlowQueryReportJmx().getObjectName(SlowQueryReportJmx.class, pool.getName()), listener, null, null);
    for (int i = 0; i < count; i++) {
        PreparedStatement st = con.prepareStatement(superSlowSql);
        ResultSet rs = st.executeQuery();
        rs.close();
        st.close();
    }
    System.out.println("Stats:" + stats);
    for (int i = 0; i < count; i++) {
        CallableStatement st = con.prepareCall(superSlowSql);
        ResultSet rs = st.executeQuery();
        rs.close();
        st.close();
    }
    System.out.println("Stats:" + stats);
    Assert.assertEquals("Expecting to have received " + (2 * count) + " notifications.", 2 * count, listener.notificationCount.get());
    con.close();
    tearDown();
    //make sure we actually did clean up when the pool closed
    Assert.assertNull(SlowQueryReport.getPoolStats(pool.getName()));
}