tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Cox, Charlie" <c...@cincom.com>
Subject RE: Confusing Problem with SQL Query in Tomcat
Date Tue, 27 Mar 2001 13:50:24 GMT
Some data types(char) are fixed length on some databases. I'm not familiar
with postgres, but you may want to try 'where upper(rtrim(name)) = upper(?)'
to trim any trailing spaces. I have run into cases where in a char(10)
field,'ABC' is not equal to 'ABC       ', which is what is actually stored.
Other data types, like varchar, only store 'ABC' and thus would work.

Also, rtrim() may slow down your query so you may want to right pad the
string you are passing in to be the correct length if you want to use a
char() type.


Charlie

-----Original Message-----
From: Hunter Hillegas [mailto:lists@lastonepicked.com]
Sent: Tuesday, March 27, 2001 12:48 AM
To: Tomcat User List; Joe Laffey
Subject: Re: Confusing Problem with SQL Query in Tomcat


> From: Joe Laffey <joe@laffeycomputer.com>
> Reply-To: tomcat-user@jakarta.apache.org
> Date: Mon, 26 Mar 2001 23:38:48 -0600 (CST)
> To: Tomcat User List <tomcat-user@jakarta.apache.org>
> Cc: Hunter Hillegas <lists@lastonepicked.com>
> Subject: Re: Confusing Problem with SQL Query in Tomcat
> 
>>             pst = con.prepareStatement("SELECT * FROM artist_info WHERE
>> upper(name) = upper(?)");
> 
> Does Postgres have any equalsIgnoresCase support? If so, might be faster
> than this.

I'll have to check on this. I don't know.

> 
>>             pst.clearParameters();
> 
> 
> No need for clearParameters(). The setString will override any old
> parameters (if their were any).

Can't hurt though, can it?

> 
> 
>>             pst.setString(1, artistName);
>>             rs = pst.executeQuery();
>> 
>>             while (rs.next()) {
>>                 out.println("Is the inputted name: " + artistName + "
equal
>> to: " + rs.getString("name") + "?<BR>"); //DEBUG
>>                 counter++; //DEBUG
>> 
> 
> [snip]
> 
> 
> My guess is that it has something to do with the
> rs.getString(java.lang.String) call. Try rs.getString(int) instead to
> narrow it down. (The int is the column number. I would highly suggest
> doing something like "SELECT name, column2, column3, FROM <table> WHERE
> <whereclause>" instead of doing "SELECT * ...". If all you want is the
> name then only SELECT that.

You think? The counter isn't getting incremented at all, suggesting that
rs.next() is evaluating to false, thus suggesting the query is returning 0
rows...

Do column numbers start at 0 or 1? I will try changing the query and using
the column number...

> 
> You also might not want to call rs.getString() on the same column twice.
> Use a temp var instead. (The Sun docs say not to do this for "maximum
> portability." The docs also say to read columns from left to right, which
> is why I suggested SELECTing actual columns in a know order instead of
> using *.)
> 
> If you get this to work please let us know how.

Mime
View raw message