problem with null in prepared statement
hi all,
i am getting some values from my jps page using request.getParameter("") and setting them in query in a servlet for fetching records from oracle db.
i have query something like this:
select product_id from product p, product_types pt where upper(pt.product_name) = nvl(?,upper(pt.product_name)).
if the user on jsp page doesn't input any product name then i m setting "null" explicitly.
but the real issue is how can i set ? to a null (not null string) (just like prepareStatement.setString (1,"abc");) because the query will return all the records if ? is set to null (not "null") and i want exactly that.
any hints?
regards
# 1
Try this :prepareStatement.setString (1,(String)null);let us know what it gives youhth
# 2
That will probably place the exact String 'null' in place of the ? instead of the SQL keyword NULL.
When the user does not provide the argument, then you need to use the setNull method of preparedStatement. You can't use setString to produce the SQL NULL keyword. It does not work that way, at least AFAIK.
# 3
java.sql
Interface PreparedStatement
public void setNull(int paramIndex,
int sqlType,
String typeName)
throws SQLException
Sets the designated parameter to SQL NULL. This version of the method setNull should be used for user-defined types and REF type parameters. Examples of user-defined types include: STRUCT, DISTINCT, JAVA_OBJECT, and named array types.
Note: To be portable, applications must give the SQL type code and the fully-qualified SQL type name when specifying a NULL user-defined or REF parameter. In the case of a user-defined type the name is the type name of the parameter itself. For a REF parameter, the name is the type name of the referenced type. If a JDBC driver does not need the type code or type name information, it may ignore it. Although it is intended for user-defined and Ref parameters, this method may be used to set a null parameter of any JDBC type. If the parameter does not have a user-defined or REF type, the given typeName is ignored.
Parameters:
paramIndex - the first parameter is 1, the second is 2, ...
sqlType - a value from java.sql.Types
typeName - the fully-qualified name of an SQL user-defined type; ignored if the parameter is not a user-defined type or REF
Throws:
SQLException - if a database access error occurs
Since:
1.2
# 4
String p=request.getParameter("value");
//Check for null value
if(!(p.equals(null)) || !(p.equals("")) || !(p==null) )
{
p=request.getParameter("value");
}
else
{
//if user doesnt enter any value from jsp then setting p to null
p=new String();
}
prepareStatement.setString (1,p);
just try this and give me the all dukes. :)
# 5
java_2006,
your solution will give stirng null but sql type null
cvasu,
what u told worked. but another approach is what masijade and mschan0 described above.
masijade and mschan0,
you are right. i checked that and it worked. i used something like this
prepareStatement.setNull(1, Types.VARCHAR)
it worked when column is of type varchar2
but this failed in case table's column is of type number(8). I tried other options like NUMERIC etc. but couldn't get through. it says problem in internal representation or something like that.
In my java code when i have a value then i write something like this:
prepareStatement.setLong (1, value)
but when i don't have the value then i need to pass a null. how do i do it?
need your help again?
# 6
tryprepareStatement.setNull(1, Types.INTEGER) //for Number(8)orprepareStatement.setNull(1, Types.DOUBLE) //for Number(8, 2) for examplelet us know if it works
# 7
I tried with prepareStatement.setNull (1, Types.INTEGER) and this is what i am getting :
Exception in thread "main" java.sql.SQLException: Fail to convert to internal representation
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:190)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:286)
at oracle.jdbc.driver.CharCommonAccessor.getLong(CharCommonAccessor.java:239)
at oracle.jdbc.driver.OracleResultSetImpl.getLong(OracleResultSetImpl.java:761)
at ManageDBConnection.main(ManageDBConnection.java:69)
I don't know what to do now. I am stuck due to this.
More help please...
regards
# 8
sorry if i misunderstand the question, why you want to put null in number, why don't you just put 0.
# 9
hi gain,try this:import oracle.jdbc.driver.OracleTypes;...pstmt.setNull(1, OracleTypes.INTEGER);BTW, could you post your table creation sql script plz ?
# 10
Abubakr,
putting 0 will check if any of the rows in table has brand_id=0 which will happen never and i want to fetch all the rows if the no brand_id is being supplied.
java
import oracle.jdbc.driver.OracleTypes;
...
pstmt.setNull(1, OracleTypes.INTEGER);
That didn't work either. it gave me :
Exception in thread "main" java.sql.SQLException: Fail to convert to internal representation
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:190)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:286)
at oracle.jdbc.driver.CharCommonAccessor.getLong(CharCommonAccessor.java:239)
at oracle.jdbc.driver.OracleResultSetImpl.getLong(OracleResultSetImpl.java:761)
at ManageDBConnection.main(ManageDBConnection.java:70)
By the way, this is the script for creating the table.I want to fetch all the records in the table if no brand_id is supplied in prepared statement.
CREATE TABLE product (
Product_Id NUMBER(8),
Is_supported CHAR(1),
Firmware_version NUMBER(3,2),
Software_product VARCHAR2(25),
Software_version NUMBER(3,2),
Software_operating_system VARCHAR2(25),
Product_type_Id NUMBER(8),
Brand_Id NUMBER(8),
CONSTRAINT product_Product_Id_pk PRIMARY KEY(Product_Id),
CONSTRAINT product_type_Id_fk FOREIGN KEY (Product_type_Id) REFERENCES product_types(Product_type_Id),
CONSTRAINT product_brand_Id_fk FOREIGN KEY (Brand_Id) REFERENCES brand(Brand_Id));
This problem is driving me nuts.
lets hope.....
# 11
Hi,
I think I've a solution : Use is null when needed.
PreparedStatement pstmt=null;
String sql = null;
if(p!=null && p.trim().length>0){
sql = "select * from product where brand_id = ?";
pstmt = connection.prepareStatement(sql);
pstmt.setInteger(1, Integer.parseInt(p)); //
}else{
sql = "select * from product where brand_id is null";
pstmt = connection.prepareStatement(sql);
}
....
Take a look at this: http://forum.java.sun.com/thread.jspa?threadID=540698&messageID=2685632
Let us know if it'll work !
hth
# 12
hello deepak,If your problem is related to JAVA just check for null and if the value is null replace it with 0. 2. If your problem is with database and you are using ORACLE just FIRE AN OUTER JOIN ON product and brand tables.Now pl allot your dukes. :))
# 13
sorry, can't award more than that coz my problem is still there.java, the solution u have suggested is not practical as i have many sub conditions in my sql queryand cvasu, i am looking into outer join concept and will let u know...
# 14
prepareStatement.setNull(1, java.sql.Types.INTEGER);Have you solved your problem yet?Try the above.Message was edited by: marsui
