InnoDB are the only transactional tables in MySQL, but CF doesn't know about the backing table, and the latter is what we care about. In order for transactions to work correctly, the client has to ensure that all queries within the transaction execute on a single DB connection, and that no other queries are executed on that same connection. Since connections are managed by CF's connection pool, using the CFTRANSACTION tag ensures that "i own the connection" behaviour, regardless of whether the backing DB is actually transaction-aware. That's the part that is essential for a LAST_INSERT_ID() query to execute correctly, not an actual transaction.
And I know about the MySQL server setting for multiple statement support, but I'm of the opinion that you should never use it. :) Aside from the potential SQL injection vulnerabilities, I find that grouping statements in CFQUERY tags (as you can do with MSSQL out of the box), usually ends up in code that is harder to follow, because the eye naturally uses the CFQUERY tags to delimit blocks, not CFQUERY tags AND internally contained semicolons. cheers, barneyb On Tue, Mar 17, 2009 at 10:57 AM, Ryan Stille <[email protected]> wrote: > > 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:320593 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
