Thursday, December 30, 2010

Wake Up, Geek Culture. Time to Die | Wired

Fast-forward to now: Boba Fett’s helmet emblazoned on sleeveless T-shirts worn by gym douches hefting dumbbells. The Glee kids performing the songs from The Rocky Horror Picture Show. And Toad the Wet Sprocket, a band that took its name from a Monty Python riff, joining the permanent soundtrack of a night out at Bennigan’s. Our below-the-topsoil passions have been rudely dug up and displayed in the noonday sun. The Lord of the Rings used to be ours and only ours simply because of the sheer goddamn thickness of the books. Twenty years later, the entire cast and crew would be trooping onstage at the Oscars to collect their statuettes, and replicas of the One Ring would be sold as bling.

Absolutely brilliant analysis of the mainstreaming of geek culture and the effect of what Oswalt calls "ETEWAF" (Everything That Ever Was - Available Forever). It's kind of along the lines of my Moon Pie post ( from a few months ago, only much more eloquently and thoroughly presented, on a much larger scale.
This was a great thing to read as we move into a new year.

Wednesday, December 22, 2010

451 CAOS Theory » Big business better use open source

Thus, to say that an organization avoids or bans open source software today is tantamount to saying that organization does not save money, does not do things efficiently and is not progressive. There may be those who continue to believe that the use of open source is still relegated to geeky development or IT operations teams, or that it is limited to test and dev projects, but it has already made inroads into production. Whether the leadership of big business knows it or not may be another matter.

This seems to be a theme in articles covering free software as the year comes to a close. Looking back on 2010 it does seem that free software finally lost most of its "geek sheik" image and is simply an important part of most IT organizations.

Monday, December 13, 2010

CFML and Oracle Stored Procedures: Experimentation and Solutions

I love a challenge. Most of the time when someone tells me something can't be done, I have to prove it to myself even if these attempts are frequently exercises in futility. And even if said something actually cannot be done, I always feel like I learn a ton during the process.

Such was the case when I was working with a coworker recently on getting data back from some Oracle stored procedures into either Open BlueDragon or Railo. Before I proceed, let me state very clearly that I hardly ever touch Oracle so if I'm off-base on any of this I'm happy for an Oracle expert to educate me. In my defense, I will say I did quite a bit of searching on this topic, and more hours of experimentation than I care to admit, so I wasn't just screaming "Oracle sucks!" and not actually trying to intelligently and logically get things to work. Also bear in mind that in this situation we didn't write the storedprocs and we do not have the ability to alter them. (Necessity is the mother of invention and all that.)

Back to the task at hand. The storedprocs in question return Oracle REF CURSORs, which is basically Oracle's way of thumbing their ever-expanding nose at the world by doing things differently than everyone else simply because they want to. (Oracle fans, if there's a legitimate explanation for this utter nonsense I'm all ears, because I certainly couldn't find one.)

If your first thought is, "But this just works on Adobe ColdFusion!" you are in fact correct. This is because Adobe CF ships with DataDirect drivers that handle getting an Oracle REF CURSOR into a format that can be used by CF. Because REF CURSORs ain't your plain old Java ResultSets like the entire rest of the known database universe uses, my assumption is that there's some translation that goes on either in the driver, or on the CF side, or both, to make this work. But in other CFML engines if you're using the plain old JDBC drivers--yes, even Oracle's own JDBC drivers--this doesn't "just work."

Let's assume it's entirely in the driver. I could either purchase the DataDirect drivers (expensive, and silly to have to do this for this one seemingly small thing), or I could use the ones that ship with CF. Well, a little birdie told me (ahem) that if you try to use the ones that ship with CF anywhere but with CF you'll get a licensing error when trying to run queries. So that solution's out.

You might also think you could simply call the storedproc from within a normal CFQUERY tag as opposed to using CFSTOREDPROC and an out parameter. Good thought, but even if you get the syntax figured out, since the storedprocs in question are looking for an out parameter to be passed into the storedproc (figure that one out), there's no real way to get the data returned from the storedproc into a variable within the SQL statement that you can actually access. I spent a lot of time on this and got the storedproc itself executing fine, but getting at the data was where I spent the bulk of my experimentation time, and I finally gave up.

So where does that leave us? Long and short of it is that without using the DataDirect drivers (even if that would in fact work), there is no way that I could come up with that would allow calling an Oracle storedproc that returns a REF CURSOR from OpenBD or Railo that would work.

At that point do we throw up our hands in despair and state that it simply isn't possible? Certainly not! We expand our thought process, put all options on the table, and forge ahead because we must not let technology, particularly of the Oracle variety, defeat us!

Since I was at the point where there was no way to have OpenBD or Railo deal with what the Oracle storedproc was returning (and believe me I'm happy to be proven wrong here), I decided the most expedient route would be to write the database access piece in Java and have CFML call that Java object. Once I'm in Java I can work with the Oracle and JDBC datatypes directly and have more control over what I will return to the CFML engine once I get the REF CURSOR back from Oracle.
The question then becomes if it's better to convert the REF CURSOR into a native CFML query object on the Java side, or to return something like a standard Java ResultSet to CFML and use the ResultSet directly. There are numerous other options as well, particularly once we're dealing with this in Java and have the freedom to do pretty much whatever we want.

I won't go into the gory details, but in one of my early attempts I ran into a problem where I couldn't access the ResultSet because by the time the Oracle delegate class was delegated to the Tomcat delegate class, I had to hack around a bit and get the underlying delegate on the Oracle side just to display the data. Neat problem to solve, but not exactly the most straight-forward solution.

I then decided to try creating a query object native to the CFML engine from the Java ResultSet. This worked well, and is certainly a valid approach, particularly if you know you're going to deploy to a specific CFML engine. If you want to build something that will work on any CFML engine things get more complicated because your Java methods have to return a specific datatype, so you can't have a single method in your Java class that will return the various underlying CFML query object types to the respective engines.

But as a wise man once said, all problems in computer science can be solved by another level of indirection (except, of course, the problem of too many levels of indirection). So I created the Java class to talk to Oracle and return the REF CURSOR, and then wrapped that puppy in a CFC that has conditional logic to return a CFML query object native to the engine on which it's running (i.e. either OpenBD or Railo). Works great!

The only outstanding issue at this point that makes me a hair uncomfortable is that in order for all this to work, I can't explicitly close my callable statements, ResultSets, and datasource connections on the Java side. If I close those items in Java and then return the ResultSet to the CFML engine, then I can't actually iterate over the ResultSet since it's closed.

I could of course close the ResultSet from CFML (I wouldn't have access to the callable statement or the database connection directly), but I'm not nearly as concerned about the ResultSet as I am about the database connection. At least I'm using the Tomcat datasource connection pooling so that should mitigate this concern a bit. In theory Tomcat and the JVM will handle closing this stuff when it's no longer in use, but I need to do a bit of load testing to be sure it's going to work well under load.
There's also some attributes in the Tomcat <Resource> XML definition that you can leverage to alleviate potential problems. Specificaly the maxActive, maxIdle, removeAbandoned, and removeAbandonedTimeout attributes can help keep things cleaned up since I'm in situation where I can't clean things up manually.

As far as the database connection pooling goes, thus far we've seen problems on Windows with Tomcat 6.0.26. It works for a while but then starts throwing database connection pooling errors and all the connections time out. Drop this all on Red Hat Enterprise Linux with Tomcat 7, and things are rock solid. Go figure.

I'm happy to share the Java and CFC code if anyone's interested, and I'm really curious to hear how others have solved this issue because I'm still not convinced what I came up with is the best solution, even though it's certainly working well. So far, anyway (knock on wood).