Thursday, November 08, 2007

Did Adobe cripple the Oracle Thin Driver in Cold Fusion

Adobe bundles the Data Direct drivers with the developer and enterprise versions of Cold Fusion. The oracle version works really well, but somehow it doesn't justify paying $10k just to have working oracle support.

Now this has been this way for a long time historically speaking, but it does support JDBC. Sooooooo you can use the oracle thin drivers (ojdbc4.jar, ojdbc5.jar and ojdbc6.jar the last two being the 11g versions for java 1.5 and 1.6).

But some basic things it just don't work.

for example
"INSERT INTO zac.test (REGIONS_R_id)
select REGIONS_R_id FROM zac.test"

works in sqlplus, via plain old java, via the data direct drivers but don't via JDBC thru Cold fusion 8.

This is a simple un-bound query, but it fails with

java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java :91)
oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
at oracle.jdbc.driver.T4CTTIoer.processError (T4CTTIoer.java:455)
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)
oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java :183)
oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:936)
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
oracle.jdbc.driver.OracleStatement.executeInternal (OracleStatement.java:1770)
oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:4975)
oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:314)
coldfusion.server.j2ee.sql.JRunStatement.execute (JRunStatement.java:348)

No I know that there are some subtle differences in the way Oracle's JDBC works, but this is a plain old query.

Pass SQL to the driver, prepare the statement and execute. Of course the sheer beauty of CF is that this is all handled beneath the covers. It should just work :(

UPDATE: I have passed on basic jdbc and cf test cases to Adam from Adobe at CFCAMP Melbourne and they are going to have a look. The Coldfsion Wishlist apparently is a channel to send cf bug reports

2 comments:

ArmchairDeity said...

I'm a little late to the party here, but lemme comment anyway...

No, Adobe isn't in the habit of crippling software from other vendors. It's been a while since I've done this, but my guess is that the Oracle driver is complaining that you didn't terminate the query with a ; like you have to do in SQL Developer, also an Oracle tool.

Let me know if that doesn't work?

Zac Spitzer said...

the problem was confirmed and rectified in the last cumulative update for cf 8...