Showing posts with label oracle. Show all posts
Showing posts with label oracle. Show all posts

Friday, September 9, 2011

JDBC 'commit' even for SELECT

You probably knew this, but a 'commit' can even be necessary for SELECT calls that don't change any data.

After calling JDBC Connection.setAutoCommit(true)), every JDBC call, be it SELECT, INSERT, UPDATE, DELETE starts a transaction that needs to be ended with a commit (or rollback).

This might be obvious for a sequence of INSERT or UPDATE calls that change data, and you want a final commit() to perform an atomic update.

With a SELECT, the update is necessary to avoid the following situation:
  1. Calling SELECT to fetch something. That starts a Transaction!
  2. Now somebody else changes the data.
  3. You call SELECT again to get the 'latest', but since you're still in the original Transaction, you get the same old data as in step 1.
If you have other code that changes the data and happens to call commit() before you reach step 3, all will look OK, but to be sure that you end the original reading transaction and always get the 'latest' data in a later read, it has to be done like this:
  1. SELECT, commit()
  2. Now the data might change.
  3. If you later need to fetch the 'latest', again SELECT and commit()

Tuesday, August 16, 2011

Oracle JDBC Version Woes

Ran into a strange Oracle error today in a Tomcat program.
The code ran fine in two other Tomcat instances, but failed in a third one.
The error happened to be
ORA-03115: unsupported network datatype or representation
which basically means: There is a version mismatch between the database and the JDBC library.

Reason
That Tomcat instance had a copy of ojdbc14.jar in its lib/ subdirectory that was used instead of the ojdbc14.jar included in the application's *.war file.

Unfortunately it is hard to tell the version of an ojdbc14.jar from the outside.

Possible Solutions
  1. Unzip the ojdbc14.jar and check its manifest file.
  2. Get the version programmatically
The latter can for example be displayed on some 'internal' web page of your application:

Connection connection = ...;

DatabaseMetaData md = connection.getMetaData();

out.append(...md.getDriverName() + " " + md.getDriverVersion() ...);


That way it is possible at runtime to check which version you are actually using.

Moral
Don't put *.jar files in the Tomcat lib/ directory. Include them in the *.war file.
Yes, this way you might load multiple copies, one for each *.war, but that beats later wating time to debug an app that suddenly stops working because some Tomcat instance has the wrong *.jar in its lib/ directory.