File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

PreparedStatement problem; Doesn't happen with plain SQL

 
William Nelson
Greenhorn
Posts: 7
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 30
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 7
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 254
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you post the new code with '?' for the prepared statement?
 
William Nelson
Greenhorn
Posts: 7
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 254
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic