I have an integration test for an API that utilizes the JDBC store in Cachebox. My test checks to make sure the API can properly cache and return data from the database.
However, the integration test always fails and returns an error when using an ACF engine (ACF 2018+):
General application error: Datasource myDsn verification failed.
Detail: The root cause was that: java.sql.SQLException: Usernames and Passwords for all the database tags within the cftransaction tag must be the same.
StackTrace: coldfusion.tagext.sql.QueryTag$DataSourceVerificationException: Datasource myDsn verification failed.
at coldfusion.tagext.sql.QueryTag.validate(QueryTag.java:634 undefined)
at coldfusion.tagext.sql.QueryTag.doValidate(QueryTag.java:775 undefined)
at coldfusion.tagext.sql.QueryUtils.executeQuery(QueryUtils.java:68 undefined)
at coldfusion.runtime.CFPage.QueryExecute(CFPage.java:12477 undefined)
at cfJDBCStore2ecfc1077390540$funcGET.runFunction(D:\...\coldbox\system\cache\store\JDBCStore.cfc:198 undefined)
...
I suspect this issue is the result of my BaseIntegrationSpec which wraps everything in a transaction…
/**
* This function is tagged as an around each handler. All the integration tests we build
* will be automatically rolled backed. No database corruption
*
* @aroundEach
*/
function wrapInTransaction( spec ) {
transaction action="begin" {
try {
arguments.spec.body();
} catch ( any e ){
rethrow;
} finally {
transaction action="rollback";
}
}
}
…and I know the JDBC store also uses transaction in the get() method:
/**
* Get an object from the store with metadata tracking, or null if not found
*
* @objectKey The key to retrieve
*/
function get( required objectKey ){
var normalizedID = getNormalizedID( arguments.objectKey );
transaction{
// select entry
var q = queryExecute(
"SELECT *
FROM #variables.table#
WHERE id = ?
",
[ normalizedID ],
{
datsource = variables.dsn,
username = variables.dsnUsername,
password = variables.dsnPassword
}
);
// Update stats if found
...
} // end transaction
The nested transaction seems to cause problems likely because there are other queries present in the request that don’t specify credentials identical to the JDBC store (yet they all use the same DSN). Any help or workarounds that someone could provide would be most welcome!
Edit:
If I manually edit JDBCStore.cfc and remove the credentials from each query so…
{
datsource = variables.dsn,
username = variables.dsnUsername,
password = variables.dsnPassword
}
…becomes…
{}
…everything works. However, I understand that JDBCStore needs the flexibility to be able to define a datasource.
Update: I filed a bug with Adobe in the meantime. Maybe if they get enough votes they will remove this limitation.