It doesn't work because you can't run multiple statements in a single query. If you use two CFQUERY tags (wrapped in a CFTRANSACTION to ensure connection affinity) it'll work fine. MySQL Front is splitting the single query into two distinct queries on the semicolon, sending each to the server independently, and then giving you back the two results. I.e. it's a client-side optimization, not server functionality.
cheers, barneyb On Tue, Mar 17, 2009 at 10:42 AM, Chad Gray <[email protected]> wrote: > > I am trying to use the MySQL function LAST_INSERT_ID() in a CFquery tag and > get this error. > > Error Executing Database Query. > You have an error in your SQL syntax; check the manual that corresponds to > your MySQL server version for the right syntax to use near '; SELECT > LAST_INSERT_ID() AS UserID' at line 4 > > INSERT users (Foo, Foo1, Foo2) > VALUES ('test', 'test', 'test'); > SELECT LAST_INSERT_ID() AS UserID; > > If I run this query in MySQL Front it works fine. It returns the last > UserID. If I run this code inside of CFQuery I get the error. > > Any ideas why it is not working in a CFQUery tag? > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320588 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
