CODEWITHSUNDEEP
javaoracle-databasejdbcoracle10g
Johnco
Johnco

Reputation: 4150

Oracle driver REALLY compatible with JDBC4

I have an application that uses plain vanilla JDBC code to access different properties from a database. This is agnostic of the database underneath.

However one of the features it uses requires the driver to be compliant with JDBC4 (in particular, it's trying to determine if a column is autoincremented accessing the value for IS_AUTOINCREMENT on the result of a getColumns on the connection's metadata)

See: http://download.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getColumns%28java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String%29

The thing is, that even using Oracle's oracle.jdbc.OracleDriver contained in ojdbc6.jar, which claims to be JDBC 4 compliant, it is not returning the IS_AUTOINCREMENT column added to the specification in JDBC 4. This produces a SQLException for the unknown column, and makes the application unusable for Oracle dbs.

The sql query executed by Oracle's driver is as follows:

SELECT NULL AS table_cat, t.owner AS table_schem, t.table_name AS table_name, t.column_name AS column_name, DECODE (t.data_type, 'CHAR', 1, 'VARCHAR2', 12, 'NUMBER', 3, 'LONG', -1, 'DATE', 93, 'RAW', -3, 'LONG RAW', -4,
'BLOB', 2004, 'CLOB', 2005, 'BFILE', -13, 'FLOAT', 6, 'TIMESTAMP(6)', 93, 'TIMESTAMP(6) WITH TIME ZONE', -101, 'TIMESTAMP(6) WITH LOCAL TIME ZONE', -102, 'INTERVAL YEAR(2) TO MONTH', -103, 'INTERVAL DAY(2) TO SECOND(6)', -104, 'BINARY_FLOAT', 100, 'BINARY_DOUBLE', 101, 'XMLTYPE', 2007, 1111) AS data_type, t.data_type AS type_name, DECODE (t.data_precision, null, DECODE (t.data_type, 'CHAR', t.char_length,
'VARCHAR', t.char_length,
'VARCHAR2', t.char_length,
'NVARCHAR2', t.char_length,
'NCHAR', t.char_length,
'NUMBER', 0, t.data_length), t.data_precision) AS column_size, 0 AS buffer_length, DECODE (t.data_type, 'NUMBER', DECODE (t.data_precision,
null, -127,
t.data_scale),
t.data_scale) AS decimal_digits, 10 AS num_prec_radix, DECODE (t.nullable, 'N', 0, 1) AS nullable, NULL AS remarks, t.data_default AS column_def, 0 AS sql_data_type, 0 AS sql_datetime_sub, t.data_length AS char_octet_length, t.column_id AS ordinal_position, DECODE (t.nullable, 'N', 'NO', 'YES') AS is_nullable FROM all_tab_columns t WHERE t.owner LIKE :1 ESCAPE '/' AND t.table_name LIKE :2 ESCAPE '/' AND t.column_name LIKE :3 ESCAPE '/' ORDER BY table_schem, table_name, ordinal_position

Is there any driver / alternative / workaround to this?

Upvotes: 1

Views: 4095

Answers (1)

Bruno Gautier
Bruno Gautier

Reputation: 656

Well this is probably because the IS_AUTOINCREMENT attribute is not relevant for an Oracle database.

Of course, it would be easier if all JDBC drivers where really compliant with the specification, but this is generally not the case. As stated in the API specification, the Resultset.getString("IS_AUTOINCREMENT") should at least return an empty string when it cannot be determined whether the column is auto incremented or not.

If you want to emulate this behavior, I suggest you use the following piece of code:

String isAutoincrement = "";
try {
    isAutoincrement = rset.getString("IS_AUTOINCREMENT");
} catch (SQLException sqle) {
    log.warn("IS_AUTOINCREMENT attribute could not be retrieved", sqle);
}

If you intend to develop a database agnostic application, you should also consider using this tip for other attributes such as "REMARKS" or "COLUMN_DEF" for example. More generally, your code should be really defensive, because JDBC implementations are really different from one vendor to another.

Upvotes: 4

Related Questions