• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

how to insert null in numeric field

 
Ranch Hand
Posts: 624
9
BSD Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 1183
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You can't. setInt takes an int, which is a primitive and must represent a legal value. Only reference variables can be assigned null.
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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);
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
sorry,i made a mistake
 
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 57
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 57
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
That works perfect.

Thanks for the help.
 
I want my playground back. Here, I'll give you this tiny ad for it:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic