SQL query in JSP file pulling variable from VXML file

Question

Hi

I'm trying to get an SQL query to work within a JSP file. The JSP file is pulled by a VXML file here is my JSP file code:

<?xml version="1.0"?>
<%@ page import="java.util.*" %>
<%@ page import="java.sql.*" %>
<% boolean success = true; // Always optimistic
 String info = "";
 String schoolname = request.getParameter("schoolname");
 String informationtype = request.getParameter("informationtype");

 try {
        Class.forName("org.postgresql.Driver");
        String connectString = "jdbc:postgresql://localhost:5435/N0176359";
        String user = "****";
        String password = "*****";
        Connection conn = DriverManager.getConnection(connectString, user, password);
        Statement st = conn.createStatement();
  ResultSet rsvp = st.executeQuery("SELECT * FROM lincolnshire_school_information_new WHERE school_name=\'"+schoolname+"\'");
        rsvp.next();
       info = rsvp.getString(2);
 }catch (ClassNotFoundException e) {
         success = false; // something went wrong
 }
%>

As you can see I'm trying to insert the value of the variable declared as "schooname" into the end of the SQL query. However when I come to run the jsp file it doesn't work and I get an error "ResultSet not positioned properly". When I put a standard query in (without trying to make it value of the variable it works fine)

Hope that makes sense, and thank you for any help!

Answer 1

Looks OK to me, I suspect it is something to do with the parameter you're passing - I suggest the following debugging steps:

1) Can you print the value of the schoolname parameter and verify it is what you think it is?

2) Can you form the SQL statement in a variable before you execute it, and print that too?

You might also want to check the boolean return value of your rsvp.next() call. If it is false, then there were no rows returned by your query. (javadoc for ResultSet.next())

Answer 2

You're ignoring the outcome of ResultSet#next() before attempting to access the columns of the row. The ResultSet#next() returns true if there is a row, otherwise false. If you attempt to access a non-existing row, then you will get this kind of exception.

So, change the following two lines

    rsvp.next();
    info = rsvp.getString(2);

as follows:

    if (rsvp.next()) {
        info = rsvp.getString(2);
    }

This way the code won't anymore attempt to access a non-existing row. The next question would probably be: "why didn't it return a row"? The answer would be: "either the SQL query is wrong, or the row is actually not in the DB". This problem lies then outside the scope of Java/JSP/JDBC. Debug the SQL query (run a debugger, or just do a System.out.println() of it) and copypaste it unchanged into a DB admin tool and verify/execute it. My bet that those superflous backslashes are the root cause of the problem, or that the schoolname itself contains a character which needs to be escaped.

That said, this code doesn't belong in a JSP file. It belongs in a real Java class. Start learning servlets before it's too late. Also I would learn the JDBC once again based on the Sun tutorial, your JDBC code is namely leaking resources. You need to release (close) them after use in the finally block of the try block as you acquired them. Also I would use PreparedStatement instead of Statement to save your code from SQL injection attacks. The aforementioned Sun tutorial covers it as well.