You actually *can* run multiple statements in a cfquery if you change a setting in the datasource. By default MySQL does not let you, this is a security precaution to protect against SQL injection. This may help: http://www.petefreitag.com/item/357.cfm
Also note that you can only use CFTRANSACTION with innodb tables, not MyISAM tables. I don't know if it will throw an error or not, last time I tried it did not, but there is no transaction going on when you use these tags with MyISAM tables. -Ryan Barney Boisvert wrote: > 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:320589 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
