This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes how to insert null in numeric field Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to insert null in numeric field" Watch "how to insert null in numeric field" New topic
Author

how to insert null in numeric field

Tapas Chand
Greenhorn

Joined: Dec 29, 2009
Posts: 23
hello,
i am using preparestatement to insert muliple rows into database.
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
Ranch Hand

Joined: Feb 23, 2009
Posts: 1183
You can't. setInt takes an int, which is a primitive and must represent a legal value. Only reference variables can be assigned null.


JDBCSupport - An easy to use, light-weight JDBC framework -
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
wang lei
Greenhorn

Joined: Mar 09, 2010
Posts: 12
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
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

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


OCUP UML fundamental and ITIL foundation
youtube channel
wang lei
Greenhorn

Joined: Mar 09, 2010
Posts: 12
sorry,i made a mistake
Fabio Piergentili
Ranch Hand

Joined: Sep 04, 2009
Posts: 57
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
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

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
Ranch Hand

Joined: Sep 04, 2009
Posts: 57
Sorry my mistake.

When I get the values via 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
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

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
Ranch Hand

Joined: Sep 04, 2009
Posts: 57
That works perfect.

Thanks for the help.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: how to insert null in numeric field
 
Similar Threads
difficulty in inserting value into oracle "long" datatype column
how to batch insert and update?
database and xml
Syntax error converting datetime from character string.
JDBC VRE 2.0 BatchUpdate