22. september 2004 - 19:35Der er
8 kommentarer og 1 løsning
How to create PreparedStatements with 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!
I lang tid har samarbejdsbranchen fokuseret på at forbedre enhedsfunktioner – bedre kameraer, klarere lyd og smartere software. Men den virkelige forvandling handler ikke om funktioner.
public boolean execute(String sql) throws SQLException
Executes the given SQL statement, which may return multiple results. In some (uncommon) situations, a single SQL statement may return multiple result sets and/or update counts. Normally you can ignore this unless you are (1) executing a stored procedure that you know may return multiple results or (2) you are dynamically executing an unknown SQL string.
The execute method executes an SQL statement and indicates the form of the first result. You must then use the methods getResultSet or getUpdateCount to retrieve the result, and getMoreResults to move to any subsequent result(s).
Yes...but I guess that I create the SQL-statements wrong, for eg: INSERT INTO tbl_test (text1, text2) VALUES(?, ?); SELECT LAST_INSERT_ID() FROM tbl_test;
Perhaps it is not possible to do this at all or I should seperate them with somethin else then ;
Again the line of swl-statement works fine like one row in mysql-commandprompt.
And like you wrote: Executes the given SQL statement, which may return multiple results... it looks like this should be possible.
I got this reply from MySQL so I guess it is just not possible. Thanks for your time.
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
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
