Coldfusion datasource isolation level (tomcat resource?)

[Steven6282]

Hello all,

I'm hoping someone can help me with a problem I'm having.  I know some of the problems I'm having are due to poor application design, but this was all stuff done before I started working on the application and changing it is not likely any time soon.  That said... we are currently running ColdFusion 9 on a fairly large application.  We never upgraded to 10 because of a problem with being able to set the isolation level on datasource connections to the database.  Since CF 2016 released recently, I decided to play with this some again.  We are running an informix database and so have their jdbc drivers, and I have them added to the class path.  All of that works fine.

I can add the datasource directly in the coldfusion administrator, and it works, accept for the isolation level.  The way the application was designed and written it requires the default isolation level to be set to dirty read (read uncommitted) on some of the datasources.  In CF9 we can configure this in jrun-resources.xml by adding <isolation-level>READ_UNCOMMITTED</isolation-level> to the datasource.  There is no way to do this in the coldfusion administrator that I can find, and haven't found anyway to add it in the neo-datasources.xml file.

So I found out about Tomcat JNDI datasources. I was able to get a working datasource by adding a resource container in context.xml and a reference to it in web.xml, then add it through the coldfusion administrator as a J2EE datasource pointed at java:comp/env/jdbc/datasource.  It supports defaultTransactionIsolation="READ_UNCOMMITTED" as a paramter on the resource so it resolves that issue.  Initially this is fine, as long as I add the username and password to the Resource in context.xml.  This brings the 2nd problem, as much as I hate that this is done, our current set up does a user based authentication on every query.  So every cfquery tag has username  and password attributes, accept for a couple of datasources that do datasource based authentication.  The Tomcat JNDI datasource is apparently based on a BasicDataSource factory from Tomcat, which errors with the username and password being specified on the query tag (or even in coldfusions administrator).  It says that it's not supported by BasicDataSource.  I found another org.apache.jdbc.pool.DataSourceFactory, but if I try to specify that as the factory on the Resource, the coldfusion errors with unable to find the datasource when try to add or modify it through the administrator.

The last part of the problem is an IBM problem.  There is supposed to be an Informix environment setting IFX_ISOLATION_LEVEL, but it seems to ignore that environment variable if specified on the jdbc url.

Is there a way to do what we need to accomplish in CF10+ (have a datasource with a default isolation level specified and allow authentication from the query tags).

Any advice or help on ways to resolve this situation would be greatly appreciated.

[Steven6282 · Accepted solution]

So, I revisited this problem recently because we really need to get upgraded off of CF9 and finally had a breakthrough with this problem.

I'm posting my solution here if anyone else comes across this problem.  I was on the right track back in April 2016, but there were a few things that needed to be done differently to make it work.

First, I do have to use the alternate data source factory on the JNDI resources "org.apache.tomcat.jdbc.pool.DataSourceFactory".  However, when using this DataSource factory, for some reason it only finds the Informix JDBC driver if it is in Tomcat's lib directory.  No idea why the default one can find it with it being added to the Class path in CF and this one can't but that's the way it is apparently lol.

Second, once that is done there is another attribute I found reading through apache's tomcat documentation.  alternateUsernameAllowed="true", This setting allows per query authentication.  I really hate that the software I work on authenticates this way, but it does, and using this setting allows it to continue to do so without having to change thousands of queries and configure and entirely new user management system right now.

Finally, I discovered that after doing this, I have access to the defaultTransactionIsolation, however, a READ_UNCOMMITTED in a production environment is bad and it doesn't support READ_LASTCOMMITTED, what we really need.  But then I also discovered configuring the datasource in this manner fixes the problems I was having with Informix's environment variable IFX_ISOLATION_LEVEL being ignored.  So I'm able to set the isolation level to last committed in the URL now.  Since this datasource is managed by Tomcat instead of Coldfusion, I can only assume that Coldfusion has been the culpret responsible for breaking the URL environment variable all this time.  This environment variable does require an explicit connection to the database, which we have the URL configured to do, but Coldfusion must be changing the URL in some way behind the scenes that breaks that explicit connection and causes the variable to be ignored.  I say this because other variables that don't require an explicit database connection do work when configured under Coldfusion (like IFX_LOCK_MODE_WAIT).

All said and done this is an example of the resource in my Context.xml:

<Resource name="jdbc/dbName" global="globalName" auth="Container" type="javax.sql.DataSource"

                maxWait="30000" driverClassName="com.informix.jdbc.IfxDriver"

                factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"

                alternateUsernameAllowed="true"

                username="defaultUser" password="defaultPassword"

                url="jdbc:informix-sqli://server:port/database:IFX_ISOLATION_LEVEL=5;IFX_NETBUF_SIZE=16384;JDBCTEMP=/coldfusion/;IFX_LOCK_MODE_WAIT=30;"/>

I saw some guides talking about configuring a web resource in web.xml as well, but I did not find that step necessary.

Anyway, hopefully this might save someone else the headache of figuring this out someday!