Thanks Anvar- But I guess you didn't really follow this thread. We started with the statement you are suggesting because we are porting from another datasource. As it turns out, you cannot do a Insert ... Select from the same table in MySQL.
It's supported in other databases, so I was surprised. The work around is to lock the table, do a select, then an insert then unlock the table. And, thanks to everyone, I got it working under JDBC. -Allon On 3/19/02 6:56 PM, "Anvar Hussain K.M." <[EMAIL PROTECTED]> wrote: > Hi Allon, > > Only one query can be sent to server at a time. But your particular case > can be handled with the single query > > Insert Into orders (Number) select Max(Number) FROM Orders; > > Anvar. > > At 04:45 PM 19/03/2002 -0800, you wrote: >> Hi All- >> >> We are using the mm driver for MySQL and multiple statements in one >> connection do not seem to be 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. >> >> Are we missing something? >> >> How would you do a transaction or table lock without multi statement >> support? >> >> -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 > > ---------------------------------------------------------------------------- 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
