Jdbc check for capability - savepoint release

Question

I have generic jdbc code that works with all kinds of databases. I have some apis that work with transactions and savepoints.

The problem is that some databases require you to release the savepoint manually

 conn.releaseSavepoint(savepoint1) 

and some just throw an exeption when you do a manual release (most notably Oracle).

How can I check at run-time if the db has automatic savepoint release or if I need to do it manually. And pls, I have some nice apis. I don't want to catch the exception, it's ugly and error-prone in this case. The exception thrown is java.sql.SqlException, not something like "FeatureUnsupportedException". So if there is a real error and I foolishly catch it then I could fail at the whole transaction thing altogether.

Thanks

Tallou

You've already said you don't want to hear this, but I think catching the exception is your best option – Fulcher

Javadoc says that it throws SQLFeatureNotSupportedException if not supported. Now, if the implementations comply with the API, that's another story. – Blaise

@Blaise You're right! Oracle decided to wrap all of their db exceptions in SqlException. But if I catch it, check the "cause" field. If it's SQLFeatureNotSupportedException work it, else throw it back !!! Thank you, you gave me the best idea so far. – Tallou

@Blaise Sadly, even cause is wrapped in an SQLException. For now, I will skip manual releasing altogether. But I don't like it ... – Tallou

Answer 1

In Oracle's 10g documentation, I could spot the fact that this particular JDBC / ojdbc method is not supported:

Note: As of Oracle Database 10g, releaseSavepoint and oracleReleaseSavepoint are not supported. If you call either of the methods, then SQLException is thrown with the message "Unsupported feature".

In 11g, the documentation seems to claim otherwise:

You remove a savepoint using the Connection.releaseSavepoint(Savepoint svpt) method.

But I can easily reproduce your issue also in 11g.

Lighten

I've just tested on Oracle 19, SQLFeatureNotSupportedException is thrown when releaseSavepoint is called after Connection.rollback(Savepoint) because the rollback releases the savepoint, which is expected according to Oracle documentation: "When a transaction is committed or rolled back, all savepoints created in that transaction are automatically released and become invalid". However, after reading the Java documentation, I don't expect this exception because the feature is supported, I expect an SQLException: "the given Savepoint object is not a valid savepoint in the current transaction". – Ogawa

Answer 2

If you can pass a DB vendor parameter into your class then you can simply map this behaviour for each vendor.

Exhaust