Thanks Paul for all of the clarifications
Actually what I was saying was this:
I obviously didn't assume that sticking a semi-colon between statements
creates a transaction. I was leaving out the transaction/table lock
statements but I meant to illustrate that for instance:
LOCK TABLES Orders WRITE;
SELECT @a:=MAX(Number) FROM Orders;
INSERT
INTO Orders (...)
VALUES (..., @a+1);
UNLOCK TABLES;
Traditionally this is executed in one statement.
So your translation is:
try
{
Statement s = conn.createStatement ();
s.execute ("LOCK TABLES Orders WRITE");
s.executeQuery ("SELECT @a:=MAX(Number) FROM Orders");
s.executeUpdate ("INSERT INTO Orders (...) VALUES (..., @a+1);");
s.execute ("UNLOCK TABLES");
s.close ();
}
catch (SQLException e)
{
}
Would work as expected? (Ignoring the obvious invalidity of my Insert
statement).
The lock would occur on the statements and the insert would get the value of
the select?
Thanks a lot for your help this should be a winner.
-Allon
On 3/19/02 5:40 PM, "Paul DuBois" <[EMAIL PROTECTED]> wrote:
> 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!
>> ----------------------------------------------------------------------------
>
----------------------------------------------------------------------------
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
