aspose file tools*
The moose likes JDBC and the fly likes PreparedStatement problem;  Doesn't happen with plain SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "PreparedStatement problem;  Doesn Watch "PreparedStatement problem;  Doesn New topic
Author

PreparedStatement problem; Doesn't happen with plain SQL

William Nelson
Greenhorn

Joined: Oct 24, 2008
Posts: 7
Hi, I have spent a day or more trying to figure out the answer to this problem. I wanted to see if anyone has had a similar problem. When I execute this code, I get a result set that contains records (this is just test code to get it working, so no looping through RS):

StringBuffer sb = new StringBuffer();
sb.append("SELECT ");
sb.append("A.CRL_REC_ID, A.CRL_REC_TYPE, ");
sb.append("A.CRL_REC_LENGTH,A.CRL_RECORD_TYPE FROM CP_RECORD_LAYOUT A,");
sb.append("CP_REQUEST B WHERE A.CRL_REC_ID = B.CR_REC_ID ");
sb.append("AND B.CR_SERVICE_ID = 'IS' ");
sb.append("AND B.CR_INVOCATION_MODE = 'I' ");
sb.append("AND B.CR_CALLER_ID = 'AL' ");
sb.append("AND B.CR_VENDOR_ID = 'C' ");
sb.append("ORDER BY B.CR_REC_SEQ");

java.sql.Connection conn = aConnection.getConnection();
java.sql.PreparedStatement ps = conn.prepareStatement(sb.toString());
java.sql.ResultSet myRs = ps.executeQuery();
myRs.next();
System.out.println("Result: " + myRs.getString("crl_rec_id"));
// prints out the first record id of 1.

However, when I swap out the hard-coded values with question marks, and then set the parameters with the following code, no records are returned in the result set:

ps.setString(1, "IS");
ps.setString(2, "I");
ps.setString(3, "AL");
ps.setString(4, "C");

The strange thing is that the problem line is the third parameter of "AL". If I set the other three by ps.setString(x, "XX") and hard-code the "AL" in the query string (as in "AND B.CR_CALLER_ID = 'AL'"), it returns records. I am stumped because this shouldn't be a problem, and that particular piece of code is no different than the others.

Data types for the columns are of char type. Database is Oracle 10g.

Thanks,
William
Gerbrand van Dieijen
Ranch Hand

Joined: Nov 28, 2008
Posts: 30
What is the type of the CR_CALLER_ID ? The column might be of another type in the database, like an enumeration. When using plain sql, the database will do the conversion, but when using a prepared statement the database might not.
William Nelson
Greenhorn

Joined: Oct 24, 2008
Posts: 7
Thanks for the response. Data type is char, except for SERVICE_ID which is varchar. That is not the problem field, however.
Duc Vo
Ranch Hand

Joined: Nov 20, 2008
Posts: 254
Can you post the new code with '?' for the prepared statement?


“Everything should be as simple as it is, but not simpler.” Albert Einstein
William Nelson
Greenhorn

Joined: Oct 24, 2008
Posts: 7
Sure, this is what it looks like when I use the prepared statement:

StringBuffer sb = new StringBuffer();
sb.append("SELECT ");
sb.append("A.CRL_REC_ID, A.CRL_REC_TYPE, ");
sb.append("A.CRL_REC_LENGTH,A.CRL_RECORD_TYPE FROM CP_RECORD_LAYOUT A,");
sb.append("CP_REQUEST B WHERE A.CRL_REC_ID = B.CR_REC_ID ");
sb.append("AND B.CR_SERVICE_ID = ? ");
sb.append("AND B.CR_INVOCATION_MODE = ? ");
sb.append("AND B.CR_CALLER_ID = ? ");
sb.append("AND B.CR_VENDOR_ID = ? ");
sb.append("ORDER BY B.CR_REC_SEQ");

java.sql.Connection conn = aConnection.getConnection();
java.sql.PreparedStatement ps = conn.prepareStatement(sb.toString());

ps.setString(1, "IS");
ps.setString(2, "I");
ps.setString(3, "AL");
ps.setString(4, "C");

java.sql.ResultSet myRs = ps.executeQuery();
myRs.next();
System.out.println("Result: " + myRs.getString("crl_rec_id"));

When I do this, it does not return any records. But, like I said, if I code the value "AL" into the query string it will return a value. I'm currently checking with my DBA to see what the query looks like when it reaches Oracle.
Duc Vo
Ranch Hand

Joined: Nov 20, 2008
Posts: 254
Can you try to print out the ParameterMetaData from the method ps.getParameterMetaData()?
What we need is the order, parameterClassName, and parameterTypeName.

Also, if possible, can you check the database server log to see what query has been sent?
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: PreparedStatement problem; Doesn't happen with plain SQL