I fancy myself a pretty descent programmer. I'm good with Coldfusion, Java, and I'm not doing to bad with Ruby (haven't touched it in 2 months :( ). But every once and a while I come across something that I don't know, and feel like a complete idiot for not knowing it.
As you already know I've recently
become the manager of the IT Web Team at my day job. We've got a hugely successful hotel-booking web site that my department built and it's experienced some slight growing pains recently. One of those growing pains was the occurance of the following error:
"Open Cursors Maximum Exceeded"
Let me back-up a little bit. The web site is running in Coldfusion MX 6.1 and Oracle.
Now, I'm sure we all know what a database connection is, right? Your application server (in our case CFMX and the underlying JRun J2EE server) needs to talk to the database so it uses this thing called a "connection". Connections take a while to .... connect .... to the database so pimp app servers put a bunch of connections in a pool and when your app server needs one it dishes up a connection. The app server does it's database work and gives the connection back to the pool.
What I learned is that there is whole other level of voodoo magic know as "cursors".
Ok, some of you have probably fallen over laughing at me.
Piss off.
;)
Here is a quick explaination of a "cursor". Each time the app server gets a connection and executes a SQL statement a "cursor" in the connection is created.
Simple right?
Here is the kicker.
Oracle (and I'm guessing other RDBMS') have a limited number of cursors per connection, and when they are used up the connection is totally hosed. If you send a SELECT statement and then an INSERT statement, that's two cursors. My brain told that the app server should just kill the connection and make a new one for the pool. Apparently that's not how it works, at least not with the technology we're using. The connection just fills up with open cursors and the app server starts reporting the "Open Cursors Maximum Exceeded" error when it tries to use the connection.
[
UPDATE: See the comments at the bottom of this entry.]
Brilliant I thought. What kind of piece of crap is this?
Our first (naive) solution was to increase the cursor amount. That fixed it, for all of 1 day. :(
Then a couple of people
with a frigg'n clue sat me down and explained "bind variables".
If you are a super smart person you use bind variables in your SQL. In Coldfusion this means using the CFQUERYPARAM tag, and in Java this means (I think) creating a PreparedStatement. (If someone could verify this for me I'd appreciate it. I haven't looked into it yet. I always use PreparedStatements, I just didn't know about this wonderful benefit.)
What am I talking about?
Here is some SQL to illustrate, and then I'll get back into why you need to use bind variables.
Bad SQL in CF
INSERT INTO some_table (column_1) VALUES (#somevalue#)
Good SQL in CFINSERT INTO some_table (column_1) VALUES (<cfqueryparam value="#somevalue#" cfsqltype="CF_SQL_VARCHAR" />)
Why do you need to do this?
Every time the "Bad SQL" is executed in Coldfusion, a new cursor is created in the Oracle database connection.
The first time the "Good SQL" is executed in Coldfusion a new cursor is created. Then the next time the "Good SQL" is executed the cursor that was created the first time is reused. 1 SQL statement = 1 cursor that can be re-used for the life of the connection.
Like I said, every once and a while I come across something that I don't know, and feel like a complete idiot for not knowing it. :)
Oh, and if anyone out there is running CFMX 6.1 I HIGHLY recommend you upgrade to the latest updater, apply
this hotfix for the DB drivers, and if you're running in J2EE mode get the latest JRun updater. I've seen huge stability improvements after applying the patches.
