Using An In-Memory Database With ColdFusion (Out Of The Box)
Posted By : todd sharp Posted At : January 27, 2011 1:45 PM Posted In: ColdFusion
37
I've been struggling with how to implement a particular piece of logic for one of my apps lately. The app in question requires that I notify a user if another user has made a recent change to some piece of data. My first thought was to use websockets to notify the user when the data has changed. Unfortunately the corporate firewall blocks websockets so that solution won't quite work for this application. My next thought was to set up a timer with JavaScript and hit the server every 60 seconds to check for changes - poor mans polling if you will. This works out fine for my needs so it's the route I'll end up going.
The next step, however, is where things can get tricky. I could easily query the database server and find out the data that I need to, but that could get quite expensive (especially the more data elements that you need to keep track of). I could easily stuff the important data into an array or struct in the application scope when another user changes it, but retrieval then becomes another task (how do you easily find the records that another user is concerned with). At this point I started thinking about queries. It's easy to manually create a query object with ColdFusion and retrieve records via query of queries, but inserting and deleting from that query object is much more complicated (as it should be since query objects aren't meant to replace databases). I could easily create a database table strictly for logging to insert/delete/query the messages, but again we're talking tons of database hits.
What would be really cool is if you could create and utilize a mostly full featured dbms like Apache Derby directly within RAM, right? Well it turns out you actually can. With ColdFusion. Out of the box.
Read that again. An actual database in memory that supports inserts, updates, deletes and reads. Turns out that the version of Apache Derby that ships with CF has had this kind of support for quite a while. The baddest-ass part of this is how stupid easy it actually is to do.
The first step is to create the DB. I'm choosing to store the connection object in the application scope, which is probably a good place for it.
application.dm = createObject('java', 'java.sql.DriverManager');
application.conn = application.dm.getConnection('jdbc:derby:memory:test;create=true');
}
The important part to remember when creating the connection is adding "memory:SCHEMANAME" to the JDBC connection string. Next it's just a matter of creating a table:
CREATE TABLE testTable (
testID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
test VARCHAR(50)
)
</cfsavecontent>
<cfscript>
createTbl = application.conn.prepareStatement(createSQL);
try{
createTbl.execute();
}
catch(Any E){
//table probably already exists...
}
</cfscript>
At this point you're off to the friggin races. Here's an insert:
INSERT INTO testTable (test) VALUES ('foo')
</cfsavecontent>
<cfscript>
ins = application.conn.prepareStatement(insertSQL, application.conn.createStatement().RETURN_GENERATED_KEYS);
ins.executeUpdate();
</cfscript>
Running a select statement works the same way:
select *
from testTable
</cfsavecontent>
<cfscript>
sel = application.conn.prepareStatement(selectSQL);
results = sel.executeQuery();
writeDump(results);
</cfscript>
You may notice that when you dump out the results of the select that you get a java.sql.ResultSet object. No problem, just do this:
writeDump(cfquery);
So this feature gives me the ability to create a disposable table when my application starts and do inserts, updates, deletes and selects from that table as the needs of my application dictate. How badass is that? The potential uses for this feature are pretty limitless. And it all works straight up out of the box with ColdFusion.
It's a one page Ajax application so that really wouldn't work for this app.
into an existing app without having to make changes to the existing database.
Just need to make sure during onSessionEnd() that you clear that a specific
user's records get wiped in case they abandon before save.
Could also be used to remember form data during a user's session
and would make managing a shopping cart easier too.
This is a great find!
as far as 2 different servers i'd imagine it would act just like a normal apache derby db. jdbc supports transactions and locking i believe...
[Runs to see if this works on a shared host]
http://www.carehart.org/resourcelists/derby_for_cf...
Specifically the section on 'Can Derby be used in a Cluster?'. I'm going to look into those projects that Charlie mentions to see how feasible it would be to take advantage of something like that.
How would you do parameterization with this?
stmt = application.conn.prepareStatement("insert into tbl (foo) values(?)");
stmt.setInt(1);
//or
stmt.setString('foo');
@Robert:
I quickly tried that at first (I left the folder blank) but it seems CF does a bit of messing with the connection string and it goofed it up. Understandable, but there still might be a way to do it. I'll mess with it again at some point.
http://twitpic.com/3tzk6f
And it WORKED. So I can now use standard <cfquery> syntax on an in-memory database:
<cftry>
<cfquery name="createTbl" datasource="memory">
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">
insert into testTable
(test)
values(
<cfqueryparam value="todd" cfsqltype="cf_sql_varchar" />
)
</cfquery>
<cfquery name="select" datasource="memory">
select *
from testTable
</cfquery>
<cfdump var="#select#">
I've included the driver in the STAR stack...(http://aphatak.blogspot.com/2010/08/star-stack.htm... AND/OR http://aphatak.blogspot.com/2010/03/new-web-stack-...).
If your datasets are simple and not that huge why not just use Coldfusion's QueryNew() functions to create in memory tables. You could do relational querys. The pain would be to setup tables but you could have that functionality encap'ed in functions...They have very good performance too!
It's a neat experiment though.
Both returned the following:
No suitable driver found for jdbc:derby:memory:test;create=true
This doesn't explain why it failed for you on CF9 tho.
I got the same error on my CF 9 box (it's running 9,0,0,251028).
@Todd:
I tried the 'other' datasource like you showed but I get an error:
Connection verification failed for data source: memdb
java.sql.SQLException: Timed out trying to establish connection
The root cause was that: java.sql.SQLException: Timed out trying to establish connection
I'm able to create a normal derby datasource so something else must be up.
All:
I'm trying to play around with using an in-memory database with the Derby Network Server which will provide support for connections from multiple JVMs (IE: a cluster). I got it working, but I had to manually start the Derby Server from command line. That's pretty lame, so I'm looking at my options...
Couldn't you use cfexecute in an onServer start to kick off the Derby Server?
It just doesn't seem like a properly configured table schema would have any performance issues handing what you're doing. If the performance really is that bad, then I think I'd be looking into things like memcache/ehCache--both of which can play nicely w/in a cluster.
Like I said, this interesting and has some potential use cases, but I'm for the use case described in this blog, I'd try not to get too clever since this should be something you can solve with a pretty straightforward audit log table.
Check out my latest post: http://cfsilence.com/blog/client/index.cfm/2011/1/...
I'll agree that the use case I described might not be the best use case for this. I'm still considering my options (my first problem is the app server is on CF 8...). Still - I'll argue that in-memory databases definitely have potential when it comes to super fast I/O on 'disposable' data.
Fusion Reactor does not seem to like this type of JDBC string: jdbc:derby:memory:test;create=true
Hopefully I can find a solution online.
Thanks!
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