Solved

How to create PreparedStatements thats return multiple ResultSet

Posted on 2004-09-22
7
341 Views
Last Modified: 2010-03-31
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!
0
Comment
Question by:fredand44
  • 4
  • 2
7 Comments
 
LVL 14

Expert Comment

by:sudhakar_koundinya
Comment Utility
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
            }
        }
    }
0
 
LVL 14

Accepted Solution

by:
sudhakar_koundinya earned 60 total points
Comment Utility
0
 
LVL 1

Expert Comment

by:helloexpert
Comment Utility
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. :-)
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:fredand44
Comment Utility
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
0
 

Author Comment

by:fredand44
Comment Utility
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();
}
}
0
 

Author Comment

by:fredand44
Comment Utility
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
0
 

Author Comment

by:fredand44
Comment Utility
I really can not figure out how to split points??

Sorry!

Fredrik
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
java continue statement 10 70
array6 challenfge 6 62
strDist challenge 35 84
Groovy:unable to resolve class error 2 27
This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now