Problems with PreparedStatement
Posted by: Fredrik Andersson
Date: September 28, 2004 11:01AM

/*
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();
}
}

Options: ReplyQuote


Subject
Written By
Posted
Problems with PreparedStatement
September 28, 2004 11:01AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.