CODEWITHSUNDEEP
javasql-serverjdbc
Bgreen
Bgreen

Reputation: 27

Multiple ResultSets, different queries, same database SQLSERVER

Using JDBC driver, how can I use multiple result sets from different queries without continually opening and closing the connections, as it stand im extracting w.e I need and passing it to another method. Each time opening a new conn,statement and resultset

public static void OppQuery() {
    Connection conn = null;
    Statement stmt = null;
    ResultSet result_set = null;
    try {
        Class.forName(JDBC_DRIVER);
        conn = DriverManager.getConnection(DB_URL);
        stmt = conn.createStatement();

        String emailInfoQuery = "FROM WHERE";
}


public static void addQuery(String latestRenewalId) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet result_set = null;
    try {
        Class.forName(JDBC_DRIVER);
        conn = DriverManager.getConnection(DB_URL);
        stmt = conn.createStatement();
        String new_AddressLine1, new_AddressLine2;
        new_AddressLine1=new_AddressLine2="";
        int new_ChannelOption= 0;
}

I tried to use multiple result sets in one method but it kept throwing exceptions saying the result set was closed. I don't have much experience with SqlServver so any guidance would help :-)

Upvotes: 1

Views: 6001

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123549

Further to Mark's answer, since the Microsoft SQL Server JDBC driver seems to create "Holdable" ResultSet objects by default, the following test code does work when AutoCommit is enabled:

import java.sql.*;

public class SqlServerTest {

    public static void main(String[] args) {
        try {
            String connectionUrl =
                    "jdbc:sqlserver://localhost:52865;" +
                    "databaseName=myDb;" +
                    "integratedSecurity=true";
            Connection con = DriverManager.getConnection(connectionUrl);
            System.out.println(String.format("con.getAutoCommit returned %s", con.getAutoCommit()));

            Statement st1 = con.createStatement();
            ResultSet rs1 = st1.executeQuery("SELECT id FROM odds");
            rs1.next();
            System.out.println(String.format("value from rs1: %d", rs1.getInt(1)));

            Statement st2 = con.createStatement();
            ResultSet rs2 = st2.executeQuery("SELECT id FROM evens");
            rs2.next();
            System.out.println(String.format("value from rs2: %d", rs2.getInt(1)));

            rs1.next();
            System.out.println(String.format("value from rs1: %d", rs1.getInt(1)));

            rs2.next();
            System.out.println(String.format("value from rs2: %d", rs2.getInt(1)));

            rs2.close();
            rs1.close();
            con.close();
        } catch(Exception e) {
            e.printStackTrace();
        }
    }

}

For test data in tables named [odds]...

id  description
--  -----------
 1  one
 3  three
 5  five

...and [evens]...

id  description
--  -----------
 2  two
 4  four
 6  six

...the console output is:

con.getAutoCommit returned true
value from rs1: 1
value from rs2: 2
value from rs1: 3
value from rs2: 4

Upvotes: 0

Mark Rotteveel
Mark Rotteveel

Reputation: 109002

In JDBC, when the connection is in auto-commit mode (which is the default), any execution of a statement will commit the previous transaction and close any open result sets on the same connection (assuming the holdability is ResultSet.CLOSE_CURSORS_AT_COMMIT, which actually isn't the default for SQL Server). When you need to have multiple result sets open, you need to have auto-commit disabled (or use ResultSet.HOLD_CURSORS_OVER_COMMIT) and you need to use multiple Statement objects. JDBC requires that a new execute on the same Statement object closes any other open result sets from that same statement.

So

  1. Disable auto-commit (Connection.setAutoCommit(false))
  2. Use multiple Statement objects to open the ResultSets

Upvotes: 2

Related Questions