aspose file tools*
The moose likes JDBC and the fly likes Passing NULL to the database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Passing NULL to the database" Watch "Passing NULL to the database" New topic
Author

Passing NULL to the database

Arjun Anand
Greenhorn

Joined: May 24, 2001
Posts: 25
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


Arjun
shilpa kulkarni
Ranch Hand

Joined: Jun 07, 2000
Posts: 87
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

Joined: May 24, 2001
Posts: 25
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

Joined: Jun 07, 2000
Posts: 87
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);
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Passing NULL to the database