how to insert null in numeric field

[Tapas Chand]

hello,
i am using preparestatement to insert muliple rows into database.

String query = "insert into table_temp(col1,col2) values(?,?)";
pstmt = con.prepareStatement(query);
pstmt.setString(1,"a");
pstmt.setInt(2,100);
pstmt.addBatch();
pstmt.setString(1,"b");
pstmt.setInt(2,null);
pstmt.addBatch();
int[] cnt = pstmt.executeBatch();

My problem is that in the 2nd addBatch block i dont want to insert any value into the col2, which is a numeric field.
And i dont want to do it in a different query.
How to do that.
Please help

[Sebastian Janisch]

You can't. setInt takes an int, which is a primitive and must represent a legal value. Only reference variables can be assigned null.

[Paul Sturrock]

To correct what Sebastian says, you can, but NULL is a distinct SQL type, so you can't do it with that particular method. You need to use setNull rather than setInt.

[wang lei]

en,the method void setNull() is correct.

now i look in the api docs so that i find some useful message,

pstmt.setNull(2,java.sql.Types.NULL);

[Jan Cumps]

wang lei wrote:...

pstmt.setNull(2,java.sql.Types.NULL);

It should be java.sql.Types.INTEGER if you are dealing with an integer.
Regards, Jan

[wang lei]

sorry,i made a mistake

[Fabio Piergentili]

I am having a problem pertaining to this subject.

What if the table entry is an integer but a foreign key to another table? setNull sets an integer to 0 and not null so the update fails as 0 is not a valid reference to another table. Is their a way to pass it the null sql type ...... set column=null ....... not ......set column=0....?

Thanks

[Jan Cumps]

Fabio Piergentili wrote: setNull sets an integer to 0 and not null

Can you check?
java.sql.CallableStatement#setNull(java.lang.String, int) javadoc tells different.

[Fabio Piergentili]

Sorry my mistake.

When I get the values via

  rs.getInt(column)  

it returns 0 if column is null. I was not aware of that. So my object was storing 0. I had to put in a check and not populate the valriable in my object if 0 was returned. This works ok for me as the column in question is a foreign key and a 0 value is never valid. I would not know how to handle this if 0 was a valid value. Any suggestions?

Thank You

[Jan Cumps]

getInt() can't return a null value, because null is not an option for the int type.
jdbc api has another mechanism to check for nulls:

You have to check java.sql.ResultSet#wasNull() after you performed the getInt() call.
Only then you know whether the column was null.

[Fabio Piergentili]

That works perfect.

Thanks for the help.