Win a copy of Svelte and Sapper in Action this week in the JavaScript forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

how to insert null in numeric field

 
Ranch Hand
Posts: 624
9
BSD Java
  • Mark post as helpful
  • send pies
  • 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
  • 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
  • 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
  • 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: 2658
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
  • 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
  • Quote
  • Report post to moderator
sorry,i made a mistake
 
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • 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: 2658
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
  • 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
  • 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: 2658
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
  • 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
  • Quote
  • Report post to moderator
That works perfect.

Thanks for the help.
 
He baked a muffin that stole my car! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic