Example usage for java.sql CallableStatement 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:Main.java

public static void main(String[] args) throws Exception {

    CallableStatement cs = conn.prepareCall("{call StoreProcedureName}");
    ResultSet rs = cs.executeQuery();

    while (rs.next()) {
        System.out.println("Defunct user: " + rs.getString("user"));
    }//w ww .java  2  s.c om
}

From source file:CallableStmt.java

public static void main(String args[]) throws Exception {
    String storedProc = "create procedure SHOW_ORDERS_BY_STATE @State CHAR (2) as "
            + "select c.Last_Name+', '+c.First_Name AS Name,o.Order_Number "
            + "from CUSTOMERS c, ORDERS o where c.Customer_Number = o.Customer_Number "
            + "AND c.State = @State order by c.Last_Name;";

    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con = DriverManager.getConnection("jdbc:odbc:Customers");
    Statement stmt = con.createStatement();
    stmt.executeUpdate(storedProc);//from ww  w  . j  a v  a 2  s  .  c o m
    CallableStatement cs = con.prepareCall("{call SHOW_ORDERS_BY_STATE(?)}");
    cs.setString(1, "NJ");
    ResultSet rs = cs.executeQuery();
    while (rs.next()) {
        String name = rs.getString("Name");
        int orderNo = rs.getInt("Order_Number");
        System.out.println(name + ": " + orderNo);
    }
}

From source file:Main.java

public static void main(String[] argv) throws Exception {
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

    Connection con = DriverManager.getConnection("jdbc:sqlserver://MYSERVER;databaseName=MYDATABASE", "USERID",
            "PASSWORD");

    CallableStatement proc_stmt = con.prepareCall("{ call generateID(?) }");

    proc_stmt.setString(1, "employee");
    ResultSet rs = proc_stmt.executeQuery();

    if (rs.next()) {
        int employeeId = rs.getInt(1);
        System.out.println("Generated employeeId: " + employeeId);
    } else {//from  www.  j a v a  2 s  . c  o  m
        System.out.println("Stored procedure couldn't generate new Id");
    }
}

From source file:Test.java

public static void main(String[] args) throws Exception {
    Connection conn = DriverManager.getConnection("...", "username", "password");
    String query = "{CALL GETDATE(?,?)}";
    CallableStatement callableStatement = (CallableStatement) conn.prepareCall(query);

    callableStatement.setInt(1, 123);//w  w w  .  ja  v  a  2  s.com
    callableStatement.registerOutParameter(1, Types.DATE);
    callableStatement.executeQuery();

    Date date = callableStatement.getObject(2, Date.class);

}

From source file:Main.java

public static void main(String[] argv) throws Exception {
    Connection con = null;/* www.j av a 2s  .co  m*/
    CallableStatement proc_stmt = null;

    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    con = DriverManager.getConnection("jdbc:sqlserver://MYSERVER;databaseName=MYDATABASE", "USERID",
            "PASSWORD");
    proc_stmt = con.prepareCall("{ call someStoredProc() }");

    proc_stmt.executeQuery();

}

From source file:Main.java

public static int storedProcWithResultSet() throws Exception {
    Connection conn = null;/*from   w  w  w. java 2  s.  c o m*/
    CallableStatement cs = conn.prepareCall("{? = call proc (?,?,?,?,?,?,?)}");

    // register input parameters
    cs.setString(2, "");
    cs.setString(3, "");
    cs.setString(4, "123");
    // regsiter ouput parameters
    cs.registerOutParameter(5, java.sql.Types.CHAR);
    cs.registerOutParameter(6, java.sql.Types.CHAR);
    cs.registerOutParameter(7, java.sql.Types.CHAR);

    // Procedure execution
    ResultSet rs = cs.executeQuery();

    ResultSetMetaData rsmd = rs.getMetaData();
    int nbCol = rsmd.getColumnCount();
    while (rs.next()) {
        for (int i = 1; i <= nbCol; i++) {
            System.out.println(rs.getString(i));
            System.out.println(rs.getString(i));
        }
    }
    // OUTPUT parameters
    System.out.println("return code of Stored procedure = : " + cs.getInt(1));
    for (int i = 5; i <= 7; i++)
        System.out.println("parameter " + i + " : " + cs.getString(i));
    return cs.getInt(1);
}

From source file:recite18th.library.Db.java

public static String[][] getCallableDataSet(String sql) {
    try {//w w  w .  ja  v  a 2 s .  c om
        CallableStatement cstmt = getCon().prepareCall(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        return processDataSetResultSet(cstmt.executeQuery());
    } catch (SQLException ex) {
        Logger.getLogger(Db.class.getName()).log(Level.SEVERE, null, ex);
        return null;
    }
}

From source file:org.owasp.dependencycheck.data.nvdcve.ConnectionFactory.java

/**
 * Uses the provided connection to check the specified schema version within the database.
 *
 * @param conn the database connection object
 * @throws DatabaseException thrown if the schema version is not compatible with this version of dependency-check
 *//*ww w . j a  v a2 s . c o m*/
private static void ensureSchemaVersion(Connection conn) throws DatabaseException {
    ResultSet rs = null;
    CallableStatement cs = null;
    try {
        //TODO convert this to use DatabaseProperties
        cs = conn.prepareCall("SELECT value FROM properties WHERE id = 'version'");
        rs = cs.executeQuery();
        if (rs.next()) {
            final DependencyVersion appDbVersion = DependencyVersionUtil.parseVersion(DB_SCHEMA_VERSION);
            final DependencyVersion db = DependencyVersionUtil.parseVersion(rs.getString(1));
            if (appDbVersion.compareTo(db) > 0) {
                LOGGER.debug("Current Schema: {}", DB_SCHEMA_VERSION);
                LOGGER.debug("DB Schema: {}", rs.getString(1));
                updateSchema(conn, appDbVersion, db);
                if (++callDepth < 10) {
                    ensureSchemaVersion(conn);
                }
            }
        } else {
            throw new DatabaseException("Database schema is missing");
        }
    } catch (SQLException ex) {
        LOGGER.debug("", ex);
        throw new DatabaseException("Unable to check the database schema version");
    } finally {
        DBUtils.closeResultSet(rs);
        DBUtils.closeStatement(cs);
    }
}

From source file:com.splicemachine.derby.utils.TimestampAdminIT.java

/**
 * Tests SYSCS_GET_TIMESTAMP_GENERATOR_INFO system procedure.
 *//*from w  w w .  j a v a 2 s .  c  o m*/
@Test
public void testGetTimestampGeneratorInfo() throws Exception {
    String template = "call SYSCS_UTIL.SYSCS_GET_TIMESTAMP_GENERATOR_INFO()";
    CallableStatement cs = methodWatcher.prepareCall(template);
    ResultSet rs = cs.executeQuery();
    int rowCount = 0;
    while (rs.next()) {
        rowCount++;
        long num = rs.getLong(1);
        Assert.assertTrue("Unexpected number of timestamps", num > 0);
    }
    Assert.assertTrue(rowCount == 1);
    DbUtils.closeQuietly(rs);
}

From source file:com.splicemachine.derby.utils.TimestampAdminIT.java

/**
 * Tests SYSCS_GET_TIMESTAMP_REQUEST_INFO system procedure.
 *//*from   w  w  w . ja va 2s  .c o  m*/
@Test
public void testGetTimestampRequestInfo() throws Exception {
    String template = "call SYSCS_UTIL.SYSCS_GET_TIMESTAMP_REQUEST_INFO()";
    CallableStatement cs = methodWatcher.prepareCall(template);
    ResultSet rs = cs.executeQuery();
    int rowCount = 0;
    while (rs.next()) {
        rowCount++;
        long num = rs.getLong(2);
        Assert.assertTrue("Unexpected number of requests", num > 0);
    }
    Assert.assertTrue(rowCount > 0);
    DbUtils.closeQuietly(rs);
}