This week's giveaway is in the Spring forum.
We're giving away four copies of Learn Spring Security (video course) and have Eugen Paraschiv on-line!
See this thread for details.
Win a copy of Learn Spring Security (video course) this week in the Spring forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Passing NULL to the database

 
Arjun Anand
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi guys,
I'm having a java application which uses a Stored Procedure in Oracle to insert values in a table. I'm trying to pass null to a float column. But instead of null it gets stored as 0. Can anyone throw light in this issue as to how to pass null as a value to the Stored Proc.
Thanks,
Arjun
 
shilpa kulkarni
Ranch Hand
Posts: 87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
USE :
CallableStatement cst = connection.prepareCall(.....);
cst.setNull(parameterIndex, sqlType);

see PreapredStatement.setNull() :-
public void setNull(int parameterIndex, int sqlType)
throws SQLException
Sets the designated parameter to SQL NULL.
Note: You must specify the parameter's SQL type.
Parameters:
parameterIndex - the first parameter is 1, the second is 2, ...
sqlType - the SQL type code defined in java.sql.Types
Throws:
SQLException - if a database access error occurs
 
Arjun Anand
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Originally posted by shilpa kulkarni:
USE :
CallableStatement cst = connection.prepareCall(.....);
cst.setNull(parameterIndex, sqlType);

see PreapredStatement.setNull() :-
public void setNull(int parameterIndex, int sqlType)
throws SQLException
Sets the designated parameter to SQL NULL.
Note: You must specify the parameter's SQL type.
Parameters:
parameterIndex - the first parameter is 1, the second is 2, ...
sqlType - the SQL type code defined in java.sql.Types
Throws:
SQLException - if a database access error occurs

I used the aforesaid method to set null. But it throwed an SQLException saying "column type error" or something of that sort which i could only perceive as the datatype didn't match. Hence i used the setFloat method(since the column is a float) and passed null as parameter. know what happened. It got stored as 0.

Thanks,
Arjun

 
shilpa kulkarni
Ranch Hand
Posts: 87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Try using the same type as has been specified in the stored procedure.
For eg. if procedure is defined as :
PROCEDURE ins(p_order_id IN VARCHAR2,
p_item_id IN VARCHAR2,
p_description IN VARCHAR2,
p_price IN NUMBER,
p_quantity IN NUMBER) IS......
When setting the null value for price use :
cst.setNull(4, java.sql.Types.NUMERIC);
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic