List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:March 20 2002 1:40am
Subject:Re: No support for multiple statements via JDBC?
View as plain text  
At 16:45 -0800 3/19/02, Allon Bendavid wrote:
>Hi All-
>
>We are using the mm driver for MySQL and multiple statements in one
>connection do not seem to be supported:

No.  Multiple statements in one *string* are not supported, in the MM.MySQL
or any other API.  The client-server protocol supports sending only one
statement to the server at a time.

Multiple statements per connection are supported.

>
>I.e.
>
>SELECT @a:=MAX(Number) FROM Orders;insert into orders (Number) values (@a+1)
>
>
>The driver throws a syntax error.  You can do either one of these statements
>on their own, and you can have a semicolon in the statement, but you cannot
>combine the statements.

Putting a semicolon in the statement is wrong in any case.  It may work
by coincidence, but you'll certainly find that trying that in other APIs
will cause big problems.  Semicolons are a convention of the mysql client
program; don't carry them into your own programming.

>
>Are we missing something?
>
>How would you do a transaction or table lock without multi statement
>support?

Huh?

I'm curious why you'd think that the ability to stick a semicolon between
two statements would give you a transaction.  That's certainly not true
in mysql.

If you want to issue multiple statements with the same connection, then
just invoke execute(), executeQuery(), or executeUpdate() as necessary
while your connection is active.  If you want them within a transaction,
use your connection object to set the autocommit mode, and the commit()
and rollback() methods.

try
{
     conn.setAutoCommit (false);
     try
     {
         Statement s = conn.createStatement ();
         // move some money from one person to the other
         s.executeUpdate ("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'");
         s.executeUpdate ("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'");
         s.close ();
         conn.commit ();
     }
     catch (SQLException e)
     {
         System.err.println ("Transaction failed, rolling back.");
         Cookbook.printErrorMessage (e);
         // empty exception handler in case rollback fails
         try { conn.rollback (); } catch (Exception e2) { }
     }
     conn.setAutoCommit (true);
}
catch (Exception e)
{
     System.err.println ("Cannot perform transaction");
     Cookbook.printErrorMessage (e);
}


>
>-Allon
>
>
>----------------------------------------------------------------------------
>Allon Bendavid                                          Imacination Software
>allon@stripped                            http://www.imacination.com/
>805-650-8153
>----------------------------------------------------------------------------
>Visit Imacination and start selling on the Web today with Ch-Ching!
>----------------------------------------------------------------------------

Thread
No support for multiple statements via JDBC?Allon Bendavid20 Mar
  • Re: No support for multiple statements via JDBC?Paul DuBois20 Mar
    • Re: No support for multiple statements via JDBC?Allon Bendavid20 Mar
      • Re: No support for multiple statements via JDBC?Paul DuBois20 Mar
  • Re: No support for multiple statements via JDBC?Anvar Hussain K.M.20 Mar
    • Re: No support for multiple statements via JDBC?Allon Bendavid20 Mar