List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:March 20 2002 2:54am
Subject:Re: No support for multiple statements via JDBC?
View as plain text  
At 18:37 -0800 3/19/02, Allon Bendavid wrote:
>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.

I'm not sure what that is supposed to mean.

>
>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).

It should.  I'm not sure that you need to lock the tables, though, if
you're using them within a transaction.  That in itself should ensure the
atomicity of your operation.

I'm not certain why you don't just use an AUTO_INCREMENT column for this,
though.  If the reason is that you already have an AUTO_INCREMENT column
elsewhere in the table (and thus cannot create another one), a different
approache you could try is to use another table that maintains the
order number sequence.

CREATE TABLE fake_sequence (i INT UNSIGNED NOT NULL);
INSERT INTO fake_sequence SET i = 0;

That creates the table and initializes the counter.  To generate the
next sequence, do this:

UPDATE fake_sequence SET i = LAST_INSERT_ID(i+1);

Then to get the value of the value you just generated, do this:

SELECT LAST_INSERT_ID();

This has the important property of AUTO_INCREMENT values that the SELECT
will return the value *you* just generated, even if other clients generate
sequence number in between your UPDATE and your SELECT.

I guess you could to this, even:

UPDATE fake_sequence SET i = @a := LAST_INSERT_ID(i+1);

Then @a would have the value you want.



>
>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

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