Link to home
Start Free TrialLog in
Avatar of fredand44
fredand44

asked on

How to create PreparedStatements thats return multiple ResultSet

Hello!

I belive that PreparedStatements can return multiple ResultSet since they got the method:
preparedStatement.getMoreResults()
...that I belive jumps to the next ResultSet.

But when I try to create a PreparedStatement with a question that should give back more then one ResultSet like and rub with my mysql:

connection.prepareStatement("SELECT * FROM tbl_test; SELECT * FROM tbl_users;");

I just get a exception like there is something wrong with the SQL-statement.

Has any one tried this?

I also have an other problem, that may depend on the same as above, when I create PreparedStatement with more than one questions like above but which only should give back one ResultSet:

connection.prepareStatement("INSERT INTO tbl_test (text1, text2) VALUES(?, ?); SELECT LAST_INSERT_ID() FROM tbl_test;");

The exception is:
java.sql.SQLException: Syntax error or access violation: You have an error in your SQL syntax near';SELECT LAST_INSERT_ID() FROM tbl_test; ' at line 1


So perhaps it something wrong how I create the SQL-statements but the strange thing is that it works fine from the commandprompt for mysql. Like if I just type:
INSERT INTO tbl_test (text1, text2) VALUES(?, ?); SELECT LAST_INSERT_ID() FROM tbl_test;

...that works fine in.

So if any one got a solution for this it would be great!

Best regards
Fredrik

BTW 30 points is all I got, sorry!
Avatar of sudhakar_koundinya
sudhakar_koundinya

try {
        // Disable auto-commit
        connection.setAutoCommit(false);
   
        // Create a prepared statement
        String sql = "INSERT INTO my_table VALUES(?)";
        PreparedStatement pstmt = connection.prepareStatement(sql);
   
        // Insert 10 rows of data
        for (int i=0; i<10; i++) {
            pstmt.setString(1, ""+i);
            pstmt.addBatch();
        }
   
        // Execute the batch
        int [] updateCounts = pstmt.executeBatch();
   
        // All statements were successfully executed.
        // updateCounts contains one element for each batched statement.
        // updateCounts[i] contains the number of rows affected by that statement.
        processUpdateCounts(updateCounts);
   
        // Since there were no errors, commit
        connection.commit();
    } catch (BatchUpdateException e) {
        // Not all of the statements were successfully executed
        int[] updateCounts = e.getUpdateCounts();
   
        // Some databases will continue to execute after one fails.
        // If so, updateCounts.length will equal the number of batched statements.
        // If not, updateCounts.length will equal the number of successfully executed statements
        processUpdateCounts(updateCounts);
   
        // Either commit the successfully executed statements or rollback the entire batch
        connection.rollback();
    } catch (SQLException e) {
    }
   
    public static void processUpdateCounts(int[] updateCounts) {
        for (int i=0; i<updateCounts.length; i++) {
            if (updateCounts[i] >= 0) {
                // Successfully executed; the number represents number of affected rows
            } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {
                // Successfully executed; number of affected rows not available
            } else if (updateCounts[i] == Statement.EXECUTE_FAILED) {
                // Failed to execute
            }
        }
    }
ASKER CERTIFIED SOLUTION
Avatar of sudhakar_koundinya
sudhakar_koundinya

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
why take all the trouble?
Wouldn't it be simple and more readable/maintainable if you run them separately?    

Even if u combine two queries, the database will have to do the same amount of work as it would have if they were separate queries.

The only advantage I see here is that you are saving one extra call to the database. This would help u save some time if your databae is in a remote slow network....

Unless you have such network latency issues, I would suggest that you run the queries in two separate prepared statements...
this  way the code will be simpler to understand and those who support that application in the future will be thankful to you. :-)
Avatar of fredand44

ASKER

Hello!

Yes, you got a point there. But I really would like to know how to do this, and be able to save any extra calls to the database.

/Fredrik
Hello Amigos!

I'm still struggling with mysql and PreparedStatements that I would like to be able to execute multiple select. Below is a question, with full test source that should be able to test shoot, that a wrote in a forum at mysql.org. But I never got any answers. So if any one would like to test run the code below an d se if you could get it to work.

Best regards
Fredrik

/*
Hello!

I got a problem with mysql and PreparedStatement. I would like to create a PreparedStatement that will return more then one ResultSet. I think that this is possible with other databases. I have heard that this is possible with Sybase for eg.

The problem seems to be at this line (given you have a database with a table called test):
PreparedStatement preparedStatement = connection.prepareStatement("select * from test where id = 1; select * from test where id = 2;");

With Sybase you should be able to do this but you should separate the 2 select-statement with a new-line. I have not tried it my self but I have heard that this should work.

But with mysql I get this exception:
java.sql.SQLException: Syntax error or access violation message from server: "You have an error in your SQL syntax near '; select * from test where id = 2' at line 1"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2247)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1586)
at MySQLPreparedStatementTester.<init>(MySQLPreparedStatementTester.java:31)
at MySQLPreparedStatementTester.main(MySQLPreparedStatementTester.java:51)

At the Java API for PreparedStatement at method execute it says:
"Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement. SOME PREPARED STATEMENTS RETURN MULTIPLE RESULTS; the execute method handles these complex statements as well as the simpler form of statements handled by the methods executeQuery and executeUpdate."

So my guess is that this should be possible.

Below is a testcode, but remeber that you need a table to test on.

So if any one could help me out with this it would be great. Else I guess that there is a bug in the jdbc.

Best regards
Fredrik Andersson
*/


import java.sql.*;

public class MySQLPreparedStatementTester
{
public MySQLPreparedStatementTester()
{

try
{
Class.forName("com.mysql.jdbc.Driver");

Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/albinoni?user=root&password=pass");

PreparedStatement preparedStatement = connection.prepareStatement("select * from test where id = 1; select * from test where id = 2;");

preparedStatement.execute();

ResultSet resultSet = preparedStatement.getResultSet();

while ( resultSet.next() )
{
System.out.println( resultSet.getString(2) );

}

preparedStatement.getMoreResults();

resultSet = preparedStatement.getResultSet();

while ( resultSet.next() )
{
System.out.println( resultSet.getString(2) );

}

preparedStatement.close();
connection.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}


public static void main( String args[] )
{
MySQLPreparedStatementTester mySQLPreparedStatementTester = new MySQLPreparedStatementTester();
}
}
Hello!

I got this reply at www.mysql.org

MySQL-4.1 is the first version of MySQL to support multiple statements, however it doesn't support multiple statements per prepared statement. You'll have to use 'normal' statements to accomplish this.

You'll also have to enable this feature on the JDBC side (as well as use Connector/J 3.1.x), as it isn't enabled by default, because multiple-query support is a SQL-injection security vulnerability if not used correctly by the end-user. You can do this by setting 'allowMultiQueries=true' in your JDBC-url

So I guess it doesnt work
/Fredrik
I really can not figure out how to split points??

Sorry!

Fredrik