Yesterday I posted about creating an in-memory database with ColdFusion and Apache Derby. It was a pretty cool proof of concept, but in practice it is a bit limited because it was using the Apache Derby Embedded database which limits you to a single JVM connection at a time (meaning not even a GUI interface can connect once CF grabs ahold of it). In simple applications this might not be an issue, but I wanted to see if I could get things working with the Network Server version of Derby.
Turns out it's just as easy, and in fact it can get a whole lot easier to work with by creating a ColdFusion DSN for the in-memory database. Having a proper DSN will let us use <cfquery> and <cfqueryparam> and work with the in-memory DB as we would any standard file system DB. The first step is to set up a DSN. Choose a name for the DSN and select 'other' as your driver type. On the next screen you'll simply craft the connection string and enter the proper class names as shown below:
You'll notice that ColdFusion will be unable to verify the DSN when you save it. That's because the Derby database isn't running yet.
You can easily start the database from the command line (see the Derby docs for the NetworkServiceControl), but wouldn't it be much easier to handle it programatically via code? Of course it would! Here's how you'd do that (imagine this code residing in onApplicationStart):
<cfscript>
ip = [127,0,0,1];
ba = javaCast('byte[]', ip);
inetAddress = createObject('java', 'java.net.Inet4Address').getByAddress(ba);
nsc = createObject('java', 'org.apache.derby.drda.NetworkServerControl').init(inetAddress, '1527');
writeDump(nsc);
try{
writeDump(nsc.ping());
}
catch(Any e){
//ping unsuccessful - start the server
nsc.start(javacast('null', ''));
}
writeDump(nsc.getCurrentProperties());
//uncomment to shutdown
//nsc.shutdown();
</cfscript>
And now you can do things like this:
<cftry>
<cfquery name="create" datasource="memory_network_server">
CREATE TABLE testTable (
testID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
test VARCHAR(50)
)
</cfquery>
<cfcatch type="any">
<!--- table exists --->
</cfcatch>
</cftry>
<cfquery name="insert" datasource="memory_network_server">
insert into testTable (test)
values(<cfqueryparam value="foo" cfsqltype="cf_sql_varchar" />)
</cfquery>
<cfquery name="select" datasource="memory_network_server">
select *
from testTable
</cfquery>
<cfdump var="#select#">
I haven't tested it yet, but in theory any other application in a cluster should be able to create a DSN pointing at the server that launches the database and interact with it just as you would a standard database. You should theoretically also be able to connect up with an IDE or GUI query management tool to make management and table creation even easier (that's my next test).
I should mention that in-memory databases will only work with ColdFusion 9.0.1 because the version of Apache Derby that supports in-memory databases didn't ship with ColdFusion until that updater. I may take a look at using JavaLoader to make things work with older versions of CF at some point in the future.
