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:
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.
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.
Joined: Oct 24, 2008
Thanks for the response. Data type is char, except for SERVICE_ID which is varchar. That is not the problem field, however.
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.
Joined: Nov 20, 2008
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?