Networked In-Memory Databases With ColdFusion
Posted By : todd sharp Posted At : January 28, 2011 11:31 AM Posted In: ColdFusion
12
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):
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:
<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.
nsc.logConnections(false);
Otherwise Derby will make a log entry for every single hit to the DB which could quickly fill up your log files.
Should you call nsc.logConnections(false) before or after you start the server ?
Glad to see it works with ORM - I was gonna try that myself :) Probably a really nice way to just hit the ground running with an ORM app!
THANKS!
waiting for you!
http://www.onlinecoachfactoryoutlets.com coach outlet
http://www.onlinecoachfactoryoutlets.com coach outlet online
http://www.onlinecoachfactoryoutlets.com coach factory outlet
http://www.onlinecoachfactoryoutlets.com coach outlet store
O(∩_∩)O~
http://www.monstersbeatsbydre.org/ monster beats
http://www.monstersbeatsbydre.org/ beats by monster
http://www.monstersbeatsbydre.org/ monster beats by dre
http://www.monstersbeatsbydre.org/ monster headphone
http://www.abercrombieoutletonlines.org/ abercrombie & fitch outlet
http://www.abercrombieoutletonlines.org/ abercrombie and fitch outlet
http://www.abercrombieoutletonlines.org/ abercrombie outlet
http://www.jetsfansshop.com New York Jets Jerseys
http://www.bearsprostore.com
http://www.yankeesmlbjerseys.com
http://www.birthdaybless.com/ birthday party organisers delhi
http://www.prestigepackersmovers.com/ professional packers and movers
http://www.maxfortrelocation.com/movers-packers-pu... Movers and packers pune