Re: JDBC PreparedStatements ?

I'll venture a wild guess that the column "theName" is defined as CHAR and not as VARCHAR2. If that's the case, you've encountered the difference in the comparison semantics of CHAR and VARCHAR2 expressions. The differences are covered somewhere in the Oracle documentation, but I don't recall which manual, but it's probably Concepts or SQL Reference or PL/SQL reference.

I'll skip the details here, and summarize by saying that when the arguments of a comparison are of different lengths, the behavior is different, a VARCHAR comparison blank pads the shorter string, a CHAR comparison does _not_ blank pad.

To fix this, modify the WHERE clause so that the expressions on both sides of the comparison are of equal length, like this:

... WHERE theName = RPAD( ? ,30)

replacing 30 with the actual length of "theName" (of course.)

Some developers avoid the use of the "equal to" comparison and use a LIKE comparison operator instead. There are several drawbacks to this approach 1) you must concatenate the % sign to the end of the argument string 2) the characters _ and % have special meaning, and 3) you have the potential of pulling rows that do not exactly match.

Another approach is to "trim" the trailing characters from the CHAR expression, like this:

... WHERE RTRIM(theName) = RTRIM(?)

The downside of this approach is that this construct will prevent Oracle from using an index range scan on "theName".

The difference in comparison semantics is just one of the benefits of using VARCHAR2 datatype in place of CHAR.

HTH "Bob Morris" <ram_at_cs.umb.edu> wrote in message news:39BB8270.8235CBDF_at_cs.umb.edu...
> I can't get a PeparedStatement query to return a result set with any
> rows in it. The snippet below shows me that the database is ok and has
> data, that a ResultSet with column names is returned, but that there are
> no rows in the ResultSet.
>
> I'm using Oracle8i 1.1.5, jdk 1.2, and the Oracle8i jdbc thin driver
> supplied with 1.1.5. What am I doing wrong?
>
> Thanks, especially for email replies to ram_at_cs.umb.edu
>
> --Bob Morris
>
> //initialize driver and make connection omitted
> //...
>
> String qs=
> "SELECT theNumber,theAddr FROM PHONEBOOK WHERE theName=";
>
> //try an ordinary Statement; it works
> Statement stmt=dbConn.createStatement();
> R= stmt.executeQuery(qs+"'Robert Morris'");
> R.next();
> System.out.println("1: "+R.getString(1));
>
>
> //try a PreparedStatement;
> // it has metadata but says"There are no rows"
> PreparedStatement dbPrepStmt=
> dbConn.prepareStatement(qs+"?");
>
> dbPrepStmt.setString(1,"Robert Morris");
> dbPrepStmt.execute();
>
> R = dbPrepStmt.getResultSet();
>
> //show some metadata. it works
> ResultSetMetaData rsmd = R.getMetaData();
> for(int i=0;i<rsmd.getColumnCount();i++)
> System.out.println (rsmd.getColumnLabel(i+1));
>
> // print stuff if there is any
> if (!R.next())
> System.out.println("There are no rows");
> else {
> try {
> System.out.println("NumRows:"+ R.getRow());
> System.out.println("Result:"+ R.getString(1));
> }
> catch(Exception E){E.printStackTrace();}
> }
Received on Thu Sep 14 2000 - 04:00:24 CEST