Storedproc and cross compatible code between Lucee and ACF

dev
1

Long time CF developer here with something I’d thought I had solved at one point in my career - but can’t for the life of me figure out how (or if) I ever did it.

I am writing a base of code that I’d like to be compatible across both Lucee and ACF. I’m using Coldbox and Testbox to help me test it as I go.

The big problem I am running into right now is calling stored procedures from cfscript. The syntax for both languages is so different! So I’ve tried putting in some server engine detection code around the “storedproc” calls - but the code won’t parse. I get errors on the bits that are specific to the other engine.

Here’s what I was hoping would work.

	local.CFMLEngine = new coldbox.system.core.util.CFMLEngine();

	if(CFMLEngine.getEngine() eq CFMLEngine.LUCEE){
		storedproc procedure='sp_mystoredproc' datasource="somedb" {
 			procparam type="in" cfsqltype="cf_sql_varchar" value=arguments.requestStruct.userID;
			procresult resultset=1 name='local.q';
		};
	} else {
		//create a new storedproc service
		spService = new storedproc();
		spService.setDatasource("somedb");
		spService.setProcedure("sp_mystoredproc");
		spService.addParam(cfsqltype="cf_sql_varchar", type="in",value=arguments.requestStruct.userID);
		spService.addProcResult(name="rs1",resultset=1);
		result = spService.execute();
		local.q = result.getProcResultSets().rs1;
	};

But as expected each engine chokes on the code it doesn’t understand.

Any pointers or suggestions? I’m certainly not the only person to be running into this.

2

Lucee shouldn’t have any issue compiling the second half of the if, even though it won’t run, but Adobe will hate the Lucee syntax in the first half of the if and won’t compile it.

You have two options. The first is to convert them both to use the newest ACF ugly function-but-not-really-a-function syntax. Lucee supports this in addition the Lucee syntax you showed above.

cfstoredproc( procedure='sp_mystoredproc', datasource="somedb" ){
 	cfprocparam( type="in", cfsqltype="cf_sql_varchar", value=arguments.requestStruct.userID );
	cfprocresult( resultset=1, name='local.q' );
};

Your second option is to implement the adapter design pattern and break out the logic into two separate CFCs, one for each engine and then only create the CFC you need based on the engine. As long as you DON’T HAVE ORM SCANNING YOUR ENTIRE DRIVE ROOT FOR PERSISTENT CFCS this will work because the CFML won’t get compiled unless it’s used.

if(CFMLEngine.getEngine() eq CFMLEngine.LUCEE){
  new LuceeProcImpl().call( arguments );
} else {

  new AdobeProcImpl().call( arguments );
}

Ok, I lied-- you have a third option too. If you look hard enough on the internet (try the old Railo google group) you can find a shim CFC that someone created that basically mimics the Adobe CFC version of stored proc. You can add it to your custom tags folder I think and it will get picked up.

1 Like
3

Thanks Brad. That’s exactly what I was looking for.

I’ve tested option #1 - use the new dog ugly syntax. I don’t know how that permutation slipped through my tests. That format works for me on both ACF 11, ACF 2016 and Lucee 5. I haven’t tested Lucee 4 or older ACF yet. They are less important.

Hopefully, this thread will show up in someone else’s google search.

(Especially if they are looking for keywords like : cross platform, cfml, cfscript, adobe, lucee, acf,
same code base, both types of server, and hasn’t someone already done this.)