On Mon, 26 Mar 2001, Hunter Hillegas wrote:
> I have a strange problem with my Servlet app running in Tomcat 3.2.1 on
> Linux.
>
> The method posted below takes a String and uses it in conjunction with a
> PreparedStatement to create a query. I've added debugging code to the method
> to print output to the screen to help me but I can't figure it out.
>
> Basically the query is returning nothing.
>
> When I run the query from the SQL command line (PostgreSQL 7.1) it returns
> one row.
>
> When I substitute the actual String in the query instead of using setString,
> it doesn't work. I'm kind of baffled...
>
> The counter I have set for debugging returns 0 and the output of the String
> that's brought into the method matches the one I use in the query that runs
> properly on the command line. Any help would be appreciated.
>
> I do all sorts of DB access in other parts of my app and it works just fine.
>
> Hunter
>
> Here is the method:
>
> private boolean artistExists (HttpServletRequest request,
> HttpServletResponse response, String artistName) throws IOException,
> ServletException {
> Connection con = null;
> PreparedStatement pst = null;
> ResultSet rs = null;
> boolean artistExists = false;
>
> //DEBUG
> response.setContentType("text/html");
> PrintWriter out = response.getWriter();
> out.println("Input Artist Name: " + artistName + "<BR>");
> int counter = 0;
> //END DEBUG
>
> try {
> con = myBroker.getConnection();
>
> pst = con.prepareStatement("SELECT * FROM artist_info WHERE
> upper(name) = upper(?)");
Does Postgres have any equalsIgnoresCase support? If so, might be faster
than this.
> pst.clearParameters();
No need for clearParameters(). The setString will override any old
parameters (if their were any).
> 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 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.
Joe Laffey
LAFFEY Computer Imaging
St. Louis, MO
----------------------
Need to do multi-file string replacement in Un*x, but don't want to mess
with sed? Try rpl. It's a free text replacement utility with source.
http://www.laffeycomputer.com/rpl.html -- Check it out!
------------------------------------------------------------------------