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
>[EMAIL PROTECTED] http://www.imacination.com/
>805-650-8153
>----------------------------------------------------------------------------
>Visit Imacination and start selling on the Web today with Ch-Ching!
>----------------------------------------------------------------------------
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
