[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql-java
Subject:    RE: Multiple Queries
From:       Paul DuBois <paul () mysql ! com>
Date:       2003-10-16 16:41:47
[Download RAW message or body]

At 9:02 +0100 10/16/03, Keith Hatton wrote:
>There was a similar post on the list a week or two ago - check the 
>archives. Basically you can't do that in a single JDBC statement. Of 
>course, you can perform several statements in one database 
>transaction - just use Connection.setAutoCommit(false) before you 
>begin.
>
>I don't know what makes people *think* this should work - is it in 
>the MySQL documentation somewhere? Just because you can run 
>anonymous PL/SQL blocks this way in Oracle doesn't mean you can do 
>the same thing in another DBMS.

People seem to believe this should be possible because you can enter
queries that way in the mysql client program.

But the reason it's possible in mysql is that mysql parses the input to
determine where each query ends, based on the statement terminators. It
then sends each query in turn to the server and displays the results.

When using an API like JDBC, you don't send multiple queries in a single
string. Just one query per string.  That's also why you don't terminate
queries with a statement terminator like ';' in your own programs.

>
>Keith
>
>
>
>-----Original Message-----
>From: Slawek Jarosz [mailto:slawek_jarosz@engineer.com]
>Sent: 15 October 2003 22:31
>To: java@lists.mysql.com
>Subject: Multiple Queries
>
>
>Hi, I'm having trouble with concatenating a number of queries into 1
>transaction block.  The following
>
>    db.createStatement.executeUpdate("BEGIN;DELETE FROM
>table1;DELETE FROM table2;COMMIT;")
>
>reports back
>
>java.sql.SQLException: Syntax error or access violation: You have an
>error in your SQL syntax near ';DELETE FROM table1;DELETE FROM
>table2;COMMIT;'
>
>Any ideas?  Thanks.
>
>--
>__________________________________________________________
>Sign-up for your own personalized E-mail at Mail.com
>http://www.mail.com/?sr=signup
>
>CareerBuilder.com has over 400,000 jobs. Be smarter about your job search
>http://corp.mail.com/careers
>
>
>--
>MySQL Java Mailing List
>For list archives: http://lists.mysql.com/java
>To unsubscribe:    http://lists.mysql.com/java?unsub=khatton@axiomsystems.com
>
>
>--
>MySQL Java Mailing List
>For list archives: http://lists.mysql.com/java
>To unsubscribe:    http://lists.mysql.com/java?unsub=paul@mysql.com


-- 
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

Are you MySQL certified?  http://www.mysql.com/certification/


-- 
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe:    http://lists.mysql.com/java?unsub=mysql-java@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]