• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

PreparedStatement problem; Doesn't happen with plain SQL

 
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 254
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can you post the new code with '?' for the prepared statement?
 
William Nelson
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
reply
    Bookmark Topic Watch Topic
  • New Topic