Question
cftransaction in a jdbc connection
Hello,
I'm kind of new to the environment, so please let me know if this question has already been answered elsewhere.
We're experiencing a fairly complicated problem with our database connection drivers. Here's what's happened so far:
Some time ago, we were interested in trying out different database drivers to see if there was an improvement in the performance using one driver or another. Up until that time we had been using the SQL Native Client driver defined in the Data Sources tool on our server. We set up new DSNs using the SQL Server driver instead, and things seemed to be working fine until a week or two ago. It's possible that the change in behavior is due to the fact that we upgraded from MX 7.0.1 to 7.0.2 and switched our JVM in order to address the new daylight savings time and we just haven't noticed the problem until now. The problem occurs with cfquery blocks that use subqueries and then have another query within the cfquery block, for example:
<cfquery>
INSERT INTO TodaysAppointments (colNames....)
SELECT (colNames)
FROM Appointments
WHERE Appointments.date = #today#;
SELECT @@IDENTITY;
</cfquery>
This type of query no longer returns any results. Upon further examination, it turned out that any cfquery block that had a subquery in an insert statement followed by any select statement wouldn't return any of the records from the select statement.
This poses a problem for us because we use this kind of query in a lot of places. We tried using the SQL Native Client driver and surprise, it works just fine.
Exploring the issue a bit further, we tried setting up a JDBC connection to our database to see whether it suffered from the same problem. The JDBC connection returned the SELECT statement results just fine, but suddenly any page that used a cftransaction with explicit actions (like commit or rollback) started failing.
The error code that we got said that the database couldn't process a commit or rollback command when it was in autocommit mode. After a little research, I learned that autocommit mode is the default for database connections, and it means that each SQL statement in a transaction/connection is automatically committed to the database before the next SQL statement is executed. Running in this mode seems to defeat the purpose of cftransactions.
The transactions work fine with the other drivers. Is there something special going on with the JDBC driver so that cftransactions aren't notifying the database to switch out of autocommit mode? Is there somewhere where I can set my database connections not to use autocommit mode?
I tried setting the database to use Implicit_Transactions = true, but I still got the error, so it seems like the problem is on the app server side.
If we can't find a solution, then we can always switch back to the SQL Native Client driver, but I had seen some other posts that said that the JDBC connection might be a better choice because it handles connection pools better.
Can anyone shed some light on this? We're using ColdFusion 7.0.2, JVM 1.4.2_11, OS is Windows Server 2003, and the DB is SQL Server 2005. I followed the instructions in livedocs for setting up a JDBC connection using the CF admin pages. We are not deploying our app as a jar.
Thanks.
I'm kind of new to the environment, so please let me know if this question has already been answered elsewhere.
We're experiencing a fairly complicated problem with our database connection drivers. Here's what's happened so far:
Some time ago, we were interested in trying out different database drivers to see if there was an improvement in the performance using one driver or another. Up until that time we had been using the SQL Native Client driver defined in the Data Sources tool on our server. We set up new DSNs using the SQL Server driver instead, and things seemed to be working fine until a week or two ago. It's possible that the change in behavior is due to the fact that we upgraded from MX 7.0.1 to 7.0.2 and switched our JVM in order to address the new daylight savings time and we just haven't noticed the problem until now. The problem occurs with cfquery blocks that use subqueries and then have another query within the cfquery block, for example:
<cfquery>
INSERT INTO TodaysAppointments (colNames....)
SELECT (colNames)
FROM Appointments
WHERE Appointments.date = #today#;
SELECT @@IDENTITY;
</cfquery>
This type of query no longer returns any results. Upon further examination, it turned out that any cfquery block that had a subquery in an insert statement followed by any select statement wouldn't return any of the records from the select statement.
This poses a problem for us because we use this kind of query in a lot of places. We tried using the SQL Native Client driver and surprise, it works just fine.
Exploring the issue a bit further, we tried setting up a JDBC connection to our database to see whether it suffered from the same problem. The JDBC connection returned the SELECT statement results just fine, but suddenly any page that used a cftransaction with explicit actions (like commit or rollback) started failing.
The error code that we got said that the database couldn't process a commit or rollback command when it was in autocommit mode. After a little research, I learned that autocommit mode is the default for database connections, and it means that each SQL statement in a transaction/connection is automatically committed to the database before the next SQL statement is executed. Running in this mode seems to defeat the purpose of cftransactions.
The transactions work fine with the other drivers. Is there something special going on with the JDBC driver so that cftransactions aren't notifying the database to switch out of autocommit mode? Is there somewhere where I can set my database connections not to use autocommit mode?
I tried setting the database to use Implicit_Transactions = true, but I still got the error, so it seems like the problem is on the app server side.
If we can't find a solution, then we can always switch back to the SQL Native Client driver, but I had seen some other posts that said that the JDBC connection might be a better choice because it handles connection pools better.
Can anyone shed some light on this? We're using ColdFusion 7.0.2, JVM 1.4.2_11, OS is Windows Server 2003, and the DB is SQL Server 2005. I followed the instructions in livedocs for setting up a JDBC connection using the CF admin pages. We are not deploying our app as a jar.
Thanks.
AdChoices