Prev: CFHTTP with Client Certificate -- Help
Next: Connecting to Excel
From: ldbc on 24 Feb 2006 14:23
Does anyone know if cftransaction will work with mySQL 5.0.15?
When I force an error in my test code, it is caught but the rollback does not
take place. I've never had any issues with Oracle and cftransaction but this
is the first time I've tried it with mySQL and I'm thinking its just not
supported.

<cftransaction action="begin">

<cftry>
<cfquery name="qInsertRecord" datasource="#application.dsn#">
insert into studies(studyID,study)
values(1,'study1')
</cfquery>
<cfcatch>
<cftransaction action="rollback"/>
<cfabort>
</cfcatch>
</cftry>

<cftry>
<!--- intentionally insert a duplicate record to cause error and test
rollback --->
<cfquery name="qInsertRecord2" datasource="#application.dsn#">
insert into studies(studyID,study)
values(1,'study1')
</cfquery>
<cfcatch>
<cftransaction action="rollback"/>
error thrown
<cfabort>
</cfcatch>
</cftry>

<cftransaction action="commit"/>
</cftransaction>

From: Sojovi on 24 Feb 2006 14:37
What I know is the transaction using CFTRANSACTION is handled through the ODBC
driver, so if the driver supports transactions it should work. Maybe is not a
great help but you can search in the MySQL odbc driver about transactions
support.

From: GMina on 25 Feb 2006 14:49
if you are having problems with mysql, you can explicitly control transactions
at the database, as opposed to using cftransaction



<cftry>
<cfquery datasource="mysql">
BEGIN;
</cfquery>

...

<cfquery datasource="mysql">
COMMIT;
</cfquery>

<cfcatch type="any">
<cfquery datasource="mysql">
ROLLBACK;
</cfquery>
</cfcatch>
</cftry>

From: cf_menace on 27 Feb 2006 10:21
I'm using <cftransaction> extensively with MySQL 4.2x and it works. You may
need to download the latest JDBC driver from MySQL to make sure the command is
sent correctly to MySQL 5.0.15.

It could be that <cfabort> is stopping the rollback of the first query. Try
putting that code in a function which returns a boolean and see what happens.
You could add <cfdump var="#cfcatch#"> right before each <cfabort> to see what
error was thrown by the database.

Also, if autocommit is turned on, you may get an error when you get to the
line <cftransaction action="commit"/>.

From: ldbc on 27 Feb 2006 10:28
Turns out that I needed to alter the table type to InnoDB. It was MyISAM by default and this type does not support transactions. Everything works now.
 | 
Pages: 1
Prev: CFHTTP with Client Certificate -- Help
Next: Connecting to Excel