preparedstatement return null
Hi,
I am having some problem with the result that is returned after executing a preparedstatement. The preparedstatement works fine as long as there is no null value in the result but if there is a null column once, all subsequent queries using the same preparedstatement will return a null for that column even when that column does contain data.
I am using an Oracle DB and the Oracle JDBC driver. Does anyone know why this is happens?
Thanks
[478 byte] By [
brainofjt] at [2007-9-26 1:58:56]
can you Plz post your code ?
JDBC drivers are kinda finicky about null. Are you calling ResultSet.waNull() to verify that the stored value actually IS an SQL NULL?
I don't get the same results as you stated. I use Oracle with a classes12.zip and have no problems(besides the fact that it returns a java null...which is a different problem) retrieving values after encountering a null.
post the code so we can help you resolve your problem.
Jamie
hi!
suppose u have 2 fields in the table.and u want to insert the values through JDBC then u have toi use
like
PreparedStatement pst=connection.prepareStatement("insert into table values(?,?)");
pst.setString(1,"aaa");
pst.setString(2,"bbb");
int n=pst.executeUpdate();
check the value of n,if it is 1 then the statement is execute successfully otherwise not.
if u do like this..
PreparedStatement pst=connection.prepareStatement("insert into table values(?)");
i.e if u didn't provide values for all the fields in insert statement the prepared statement doesn't execute.
Hope this helps u
goodluck
My code is something like this..
String sql = "SELECT COL1, COL2, COL3, COL4 FROM TABLE1 WHERE COL5 = ? ";
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, data1);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
var1 = rs.getString(1);
var2 = rs.getString(2);
var3 = rs.getString(3);
var4 = rs.getString(4);
}
This works ok as long as any of the COL does not return a null, if it happens, all subsequent queries will result in that COL returning a null value even if that COL does contain a value.
Hope this will make my problem clearer
String sql = "SELECT nvl(COL1, ' '), nvl(COL2,' '), nvl(COL3, ' '), nvl(COL4, ' ') FROM TABLE1 WHERE COL5 = ? ";
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, data1);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
var1 = rs.getString(1);
var2 = rs.getString(2);
var3 = rs.getString(3);
var4 = rs.getString(4);
}
This query will return one single space if the coloum value is null. I hope this will solve the prblm.
tomym at 2007-6-29 3:17:24 >
I don't quite understand? There is no loop, so it looks like the code executes once then moves on. How does the code get executed more than once? The code that you have shown should not be giving you problems.Jamie
public class DB{
private static String col1;
//ditto for the other columns 2 to 4
private static PreparedStatement ps;
private static final String sql = "SELECT COL1, COL2, COL3, COL4 FROM TABLE1 WHERE COL5 = ? ";
public Transaction() {
//initialise JDBC Driver
//get Connection object con
ps = con.prepareStatement(sql);
}
public static String getCOL1(){
return col1;
}
//ditto for the other columns 2 to 4
public static void queryDB(String data1) {
String var1 = "";
String var2 = "";
String var3 = "";
String var4 = "";
ps.setString(1, data1);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
var1 = rs.getString(1);
var2 = rs.getString(2);
var3 = rs.getString(3);
var4 = rs.getString(4);
}
}
The loop occurs when the method queryDB is being called.
The problem does not suface as long as the col a null value, but once there is a null value, all subsequent querys using the preparedstatement will result in that col returning a null value even if the col in the database does contain a data.
