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