Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Prepared Statement and DEFAULT identifier

843854Apr 15 2004 — edited May 16 2004
Does anyone know how to pass the identifier DEFAULT to a PreparedStatement?

I am processing a text file to import into a database table (Oracle 9i) via JDBC. The user is able to configure what will happen if the data in the file doesn't match the types required by the database and so I do a quick check that the data is of the right type before it gets to the database. The user can choose to have the invalid value replaced with the default associated with that field (as configured on the database table). Eg. Table setup is
CREATE TABLE "TEST1" (
"TESTFIELD1" VARCHAR2(40 byte),
"TESTFIELD2" VARCHAR2(40 byte) NOT NULL,
"TESTFIELD3" NUMBER(30, 10) DEFAULT 777)

sample data of 'abc', 'def', 'www' should therefore end up as 'abc', 'def', 777

The problem I have is that I'm using a PreparedStatement to do the INSERTs (there could potentially be thousands of them).

The following SQL is what I'm trying to emulate...
INSERT INTO table (field1, field2, field3) VALUES ('abc', 'def', DEFAULT);

BUT, I am using a PreparedStatment via JDBC with bind variables ...
pstmt = con.prepareStatement("INSERT INTO table (field1, field2, field3) VALUES (?, ?, ?)");
ps.setString(1, "abc");
ps.setString(2, "def");
ps.setsomething(3, something); <- what goes here?

I have no idea what to put in the "something" bits so that DEFAULT is passed through. I obviously can't use setString(3, "DEFAULT"); as I'll just get an Oracle type mismatch error.

I realise that I could just leave out the 3rd variable and the default would be inserted automatically - but because some of the rows in the input file may have a valid value for field3 I don't always want the default value. Using the straight SQL is not an option due to the performance (or lack thereof).

Any ideas would be greatly appreciated! Thanks!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 13 2004
Added on Apr 15 2004
8 comments
588 views