The moose likes JDBC and Relational Databases and the fly likes Oracle jdbc + null value for primitive type Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Oracle jdbc + null value for primitive type" Watch "Oracle jdbc + null value for primitive type" New topic

Oracle jdbc + null value for primitive type

Ryan Crum

Joined: Jan 11, 2002
Posts: 1
I would like to insert 'null' into a numeric column in Oracle (the column does allow null fields) however doing this in a pain free manor has proven difficult so far. I have tried the following:
PreparedStatement ps ....
// The setObject(...) doesnt work, "Invalid column type" error is returned
ps.setObject( idx2col, null );
The following code works:
ps.setNull( idx2col, Types.INTEGER );
The problem with the above code is that it requires that I know the type of the field the object will be going into. I do not know this information because the function that executes this PreparedStatement is very general. Sometimes the column that will be set to null is a number, sometimes its a string or date...
Does anyone have suggestions as to how a null can be inserted into the column without knowing the type (Using Oracle + their thin jdbc driver)?
Stanley Tan
Ranch Hand

Joined: May 17, 2001
Posts: 243
Hi. I just went through a similar problem. It's really annoying to know that it works for everyone else. Anyway, I've got the same setup as you (Oracle, thin driver). What I did what to use a Statement instead of a PreparedStatement and then just append the query on.
Amit Da

Joined: Mar 07, 2003
Posts: 16
From a preparedStatement one can easily know the jdbc type of any parameter.(refer to jdk1.4 javadoc of PreparedStatement). prepStmt.getParameterMetaData returns ParameterMetaData which has getParameterType(int param) giving jdbc type. Now pstmt.setNull(idx, jdbcType) can be called with ease
Bear Bibeault
Author and ninkuma

Joined: Jan 10, 2002
Posts: 63867


We're pleased to have you here with us on the Ranch, but there are a few rules that need to be followed, and one is that proper names are required. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.

In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.

Forum Bartender

[Asking smart questions] [About Bear] [Books by Bear]
I agree. Here's the link:
subject: Oracle jdbc + null value for primitive type
It's not a secret anymore!