• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to insert null in numeric field

 
Tapas Chand
Ranch Hand
Posts: 516
8
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
 
Sebastian Janisch
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.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate 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.
 
wang lei
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);
 
Jan Cumps
Bartender
Posts: 2588
11
C++ Linux Netbeans IDE
  • 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
 
Fabio Piergentili
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: 2588
11
C++ Linux Netbeans IDE
  • 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: 2588
11
C++ Linux Netbeans IDE
  • 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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic