One of the great benefits of using a framework (in the general sense) is the freedom in portability it often gives you. Rather than writing your own Ajax routines which would need to handle browser incompatibilities, you can rely on
Prototype/
Scriptaculous,
AjaxCFC,
Spry, or a seemingly infinite number of Ajax frameworks available. We see the same phenomenon in our use of ColdFusion, which uses Java to be cross-platform. And, you can get the benefit for databases by using a framework like
Transfer or
Reactor or ActiveRecord in Ruby (all also have a slew of other benefits). In general, we're simply seeing an abstracting away of the differences between low-level things so we can think at a much higher level than "oh crap, what if they're using Lynx!" (Ok, I'm doubting any of those Ajax frameworks fully support Lynx =), but you get the picture)
I can honestly say I've never had to build an application that needed to be portable between databases, aside from between different versions by the same vendor (although there was once where it might have been nice, since I used the apparently impossible to find in hosting combination of Microsoft SQL Server and Java). My guess is that unless you're building software which is meant to be deployed on different databases (such as a framework, library, or software you plan to sell over and over again which you won't be hosting), you probably haven't needed to do so either... Or at least, not often enough to let it impact your coding.
So today, I started my quest to turn cfrails into a DB-independent framework (as of today, March 30, 2007 it works only with Microsoft SQL Server). Since one of my goals is to limit the amount of configuration the programmer has to provide to get up and running, simply telling cfrails your database metadata (instead of having it look in the database itself) was out of the question (XML or otherwise). I've looked at using CF's
getMetaData(query_result) function in the past, but that doesn't have near the amount of data that would be useful for me.
I figured my best bet would be to drop down into Java and use its
DatabaseMetaData interface. This way, I could let Java take care of the differences between DB implementations for me. Sure enough, looking over
the docs, it had everything I needed. I went ahead and whipped up some code to use it and test it before trying to put it into Coldfusion. If you're interested, here's most of what I needed (only using the jdbc/odbc bridge). If not, feel free to skip this code:
// at the top of the file, you'll need to import java.sql.*
// and of course, define your class as you normally would.
Connection connection=null;
String driverPrefixURL = "jdbc:odbc:";
String dataSource = "ODBC Datasource Name";
String username = "user";
String password = "password";
String catalog = "which database";
String schema = "dbo";
String tableYouWantColumnsFor = "sometable";
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
connection = DriverManager.getConnection(driverPrefixURL+dataSource, username, password);
DatabaseMetaData dbmeta = connection.getMetaData();
ResultSet cols = dbmeta.getColumns(catalog,schema,tableYouWantColumnsFor,"%");
while (cols.next())
{
// this is all going to be unformatted as-is
System.out.print(cols.getString("COLUMN_NAME") + " ");
System.out.print(cols.getString("DATA_TYPE") + " ");
System.out.print(cols.getString("TYPE_NAME") + " ");
System.out.print(cols.getString("COLUMN_SIZE") + " ");
System.out.print(cols.getString("IS_NULLABLE") + " ");
// no clue why these two lines are throwing errors
//System.out.print(cols.getString("COLUMN_DEF") + " ");
//System.out.print(cols.getString("ORDINAL_POSITION") + " ");
System.out.println(); // skip to the next line
}
connection.close();
}
catch (Exception e)
{
e.printStackTrace();
// probably should be in finally block, but didn't worry about it as this was just a test
try
{
connection.close();
}
catch (Exception ex)
{
System.out.println("Could not close connection - probably wasn't open.");
}
}
(if you need more info, see the docs I linked above -- this is just a small sampling of the metadata available for columns in a table)
Sweet! I didn't really need
ORDINAL_POSITION since it already ordered the results by it, and
COLUMN_DEF (the column's default value) wasn't
all that important (well, I could figure out a way to get that later, anyway). But, when I went to put it into ColdFusion, I realized - I have no clue how I might get the information I need to connect- forget about the fact that I'm not sure the average developer (CF or otherwise) will know what a catalog or schema is, or which one they need to access if they do. Who in the world isn't going to have to go through some work to figure out what driver they need and what the connection URL is?
So, I needed a way to let Java know these things through CF. I thought I could get at least some of that information from the CFAdminAPI (or whatever the proper case and spelling is), but I didn't really like that option. I couldn't think of any other option that would allow me to use Java to get the metadata, so I decided I'd have a look at the codebases for Transfer ORM, Reactor, and
DataMgr and see how those smart guys did it. I only browsed, but I didn't find anything useful for me trying to use Java to do the trick. So, it was back to writing a DB-specific implementation for each database I wanted to support.
Luckily for me, even though my design doesn't approach the modularity and complexity of Reactor or Transfer (yet... I'm a big fan of
YAGNI and haven't had any reason to break out of the simple design I currently have and Transfer is downright confusing for me to quickly browse it and figure out what's going on), it is quite flexible enough for me to do the different implementations and integrate them with ease. To do so, I only need to refactor the current routine that gets the MSSQL Server metadata, and provide a way to figure out which database type it is looking in (each of these only has to happen once). After that, all that remains is to write a query to get the metadata and convert its output (for each implementation) to the interface the rest of my code expects. Not hard by any means, but it would have been cool for maintenance purposes and greater overall simplicity if I could have let Java handle it.
So now that you've gotten this far in this boring post with no conclusion, have you used Java to get the DB metadata in CF (or even outside of CF)? Have you found a way to do it without making the programmer specify the connection URL and driver?
Hey! Why don't you make your life easier and subscribe to the full post
or short blurb RSS feed? I'm so confident you'll love my smelly pasta plate
wisdom that I'm offering a no-strings-attached, lifetime money back guarantee!
Leave a comment
A Java-based database abstraction sounds good in theory, but I don't think it will prove to be worthwhile. The differences in SQL code may prove to be more subtle than expected.
More significantly, however, the information needed to connect to a database via Java is not as easy to get as specifying the datasource name and database type as is done in many of the solutions you mentioned.
I suspect that there is good reason why many of us independently decided against a Java based solution for database abstraction.
Posted by
Steve Bryant
on Mar 30, 2007 at 05:26 PM UTC - 6 hrs
Personally I think you're reinventing the wheel here by writing your own DB abstraction layer. I think your time could be better used by going down the same path that MG has and integrating Reactor and Transfer into cfrails.
Posted by
tony petruzzi
on Mar 31, 2007 at 09:27 AM UTC - 6 hrs
"A Java-based database abstraction sounds good in theory, but I don't think it will prove to be worthwhile. The differences in SQL code may prove to be more subtle than expected."
I disagree here. I mean, in general, the end user shouldn't be able to tell (plus, they are still free to write their own SQL, should they desire - and that may not be independent). But, from /my/ point of view - I'd rather let Sun (or whomever is your Java vendor) handle the differences than handle them myself =).
Also, I certainly learned it is not near as easy to connect in Java as in CF =)... (well, I knew this, but it had been so long since I've had to do it because of reusing old components, that I had forgotten).
And about the "good reason why many of us independently decided against a Java based solution for database abstraction." I think you are right on there =)
Posted by
Sam
on Mar 31, 2007 at 09:51 AM UTC - 6 hrs
@Tony- funny enough, as I discussed yesterday at
http://www.codeodor.com/index.cfm/2007/3/30/Revisi... it's not so much "wasting" as "wasted." All the time-consuming stuff is done, and most of it was the very first code I wrote for it back in October of last year =).
At first, I didn't want to use them because I wanted to add extra metadata (though at the time, I guess I didn't realize I still could have built that on top of them).
Since one of my goals is to have near zero configuration time spent by the programmer before getting started (right now, it only requires datasource, username, password and SQL adapter), I didn't like the XML config files, but I'm pretty confident I could automatically generate something to take care of that.
If I had it to do all over again, I probably would use one or both of them (in fact, I am seriously considering migrating to them in the future - keeping my own interface as a facade, or adding support for their interfaces).
Posted by
Sam
on Mar 31, 2007 at 10:09 AM UTC - 6 hrs
PS- I certainly won't be writing my own Ajax framwork! =)
Posted by
Sam
on Mar 31, 2007 at 10:13 AM UTC - 6 hrs
Im a bit late to this but I figured you might still be looking for a solution.
Take a look at CFCCreator.... not the code it generates or the looks or anything else ;) Its something I messed around with a while ago.
You can find it in the "Tools" pod on my website.
Specifically, look in table.cfc loadtablemetadata() and the jdbcwrapper.cfc. NOTE: I had mixed luck using the getPrimaryKey() from Java which is why I assumed primary keys were unique. However, drivers might be more consistent now.
It might lead you in the right direction.
Francis "Scotty" Scott
Posted by
Scotty
on Apr 22, 2007 at 06:06 PM UTC - 6 hrs
Scotty, I appreciate it. I'll definitely have a look when I revisit this before I finish the transformation.
Thanks again!
Posted by
Sam
on Apr 22, 2007 at 06:43 PM UTC - 6 hrs
