cftransaction in a jdbc connection

[nimblejack]

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.

[cf_dev2]

>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.

I remember hearing about that problem. I can't remember the cause but putting a SET NOCOUNT ON / OFF around the sql seemed to fix it.

<cfquery>
SET NOCOUNT ON

INSERT INTO TodaysAppointments (colNames....)
SELECT (colNames)
FROM Appointments
WHERE Appointments.date = #today#;

SELECT SCOPE_IDENTITY() AS TheNewId;

SET NOCOUNT OFF
</cfquery>

I can't help you with cftransaction issue. I ran a few tests with cftransaction and an explicit commit/rollback. It seems to work under 7.0.2. But I don't use cftransaction very often and my environment is slightly different.

[BKBK]

> 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?

Just a hunch, but worth a try. Go to the Data Sources page of the Coldfusion Administrator. Select the offending JDBC data source. Click on the button to Show Advanced Settings. Enter the line

autoCommit=false

in the field Connection String. Press the Submit button.

[nimblejack]

I tried adding that line, but it didn't do the trick. I checked the name/value pairs documentation in the JDBC site, and it didn't list autoCommit as one of the possible variables. SQLServerConnection object does have a setAutoCommit method, though.
I don't know how MX handles cftransaction, but I guess I could switch to using a BEGIN TRANSACTION statement in my query block, but then I can't see a way to do a cftry to check for errors and commit or rollback on that basis, because the commit/rollback would have to take place in a separate cfquery block and when the first query block closed, the connection would end and the transaction would be committed.
Any idea on anything else I can try?
Thanks for your time!

[Newsgroup_User]

nimblejack wrote:
> <cfquery>
>
> INSERT INTO TodaysAppointments (colNames....)
> SELECT (colNames)
> FROM Appointments
> WHERE Appointments.date = #today#;
>
> SELECT @@IDENTITY;
>
> </cfquery>

This isn't supposed to work. A cfquery tag can return only one
resultset. In this case, the INSERT statement generates an empty
resultset and the select statement generates a second non-empty
resultset. But only the first resultset is accessible from CF.


> This type of query no longer returns any results.

If it worked before, that was a fluke. You need the NOCOUNT solution
that was posted before to make this work reliably.

Jochem

--
Jochem van Dieten
Adobe Community Expert for ColdFusion

[nimblejack]

Thanks very much for your response. We'll adopt that policy into our coding. I'm guessing that the reason why it works with some drivers but not with others is that the result set that gets returned is handled differently be different drivers.

Do you have any insight into why transactions aren't working with our JDBC driver? I tried adding the name/value autoCommit pair to the connection string, but it didn't fix it.

Thanks.

[Newsgroup_User]

nimblejack wrote:
> Thanks very much for your response. We'll adopt that policy into our coding.
> I'm guessing that the reason why it works with some drivers but not with others
> is that the result set that gets returned is handled differently be different
> drivers.
>
> Do you have any insight into why transactions aren't working with our JDBC
> driver?

No.

Jochem


--
Jochem van Dieten
Adobe Community Expert for ColdFusion

[BKBK]

Other variations worth testing are autocommit=off, auto-commit=false , auto-commit=off, autoCommit(FALSE) and autoCommit(TRUE).

[BKBK]

What's the name of your JDBC driver?

[nimblejack]

Thanks for the suggestions above. Unfortunately, none of them worked.

The driver I'm using is sqljdbc_1.1/enu/sqljdbc.jar

Thanks

[Newsgroup_User]

nimblejack wrote:
> Thanks for the suggestions above. Unfortunately, none of them worked.
>
> The driver I'm using is sqljdbc_1.1/enu/sqljdbc.jar

FWIW, we ship that driver in every CF application we write, we run them
against MS SQL Server 2000 and MS SQL Server 2005 (Express) and we are
not experiencing your problems.

Jochem

--
Jochem van Dieten
Adobe Community Expert for ColdFusion

[BKBK]

Looks like the functionality we seek is not among the new SQL Server JDBC Driver's connection properties

[nimblejack]

Thanks for continuing to look at this.

I've been looking around the msdn site, and here's a description of when the autoCommit mode is used in T-SQL:

http://msdn2.microsoft.com/en-us/library/aa213069(sql.80).aspx

According to this site, the best way to take a connection out of autocommit mode is to either issue a BEGIN TRANSACTION SQL command, or set implicit transactions to true.
After a little more digging, I found this page: http://msdn2.microsoft.com/fr-fr/library/ms378922.aspx

It says that you can call setAutoComplete(false) for the SQLServerConnection object. Isn't this what CF should be doing when you start a cftransaction tag? Did something get missed with the JDBC drivers? I guess I'm thinking that maybe the cftransaction functionality isn't supported with the JDBC driver, because there isn't a predefined JDBC driver option in the datasources on the CF admin tool. You have to specify 'Other' as the driver type. This seems to imply to me that there's no check inside of CF to see whether it's a JDBC connection and if so issue these SQLServerConnection.setAutoComplete(false) statements.

Maybe the answer is that for now if I want to use a JDBC connection, then I'm going to have to move my transaction logic inside of the SQL statements?

Thanks.

[BKBK]

> Do you have any insight into why transactions aren't working with
> our JDBC driver?
...
> Isn't this what CF should be doing when you start a cftransaction tag?

I get transactions to work on a system that comprises

Windows 2000 Professional SP4
MS SQL Server 2005
Coldfusion MX7.0.2 + all hotfixes + sqljdbc_1.1 driver

My Coldfusion Administrator datasource settings are:

CF Data Source Name: mssqlDSN
JDBC URL: jdbc:sqlserver://localhost:1433;DatabaseName=myMSSQLDB
Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
Driver Name: mssql2005
User Name: sa
Password: my_admin_pw

The code assumes there is not yet a table called 'client'. As a result of the rollback transaction tag, you will get the message

There is no table called 'client'

The client table is duly created and populated when you delete the rollback transaction tag.

[BKBK]

Nimblejack:
> The driver I'm using is sqljdbc_1.1/enu/sqljdbc.jar

Jochem:
> ...we ship that driver...

Surely, come confusion there. The Microsoft SQL Server 2005 JDBC Driver is, well, Microsoft.

[Newsgroup_User]

BKBK wrote:
> Nimblejack:
> > The driver I'm using is sqljdbc_1.1/enu/sqljdbc.jar
>
> Jochem:
> > ...we ship that driver...
>
> Surely, come confusion there. The
> http://www.microsoft.com/downloads/details.aspx?FamilyId=6D483869-816A-44CB-9787
> -A866235EFC7C&displaylang=en] is, well, Microsoft.

They wrote it, we ship it. (As is allowed by section 3 "Transfer to a
third party" of the EULA.)

Jochem


--
Jochem van Dieten
Adobe Community Expert for ColdFusion

[BKBK]

we ship it.
Please expand.

[Newsgroup_User]

BKBK wrote:
> we ship it.
> Please expand.

We build applications. We package those applications as EAR files. We
put drivers in the EAR files (which include the MS SQL Server JDBC
drivers). We ship those EAR files to our customers.

Jochem

--
Jochem van Dieten
Adobe Community Expert for ColdFusion

[BKBK]

What confused me is why one would have to go to all the trouble of configuring the Microsoft SQL Server 2005 JDBC driver if the driver shipped with Coldfusion.