• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5800
  • Last Modified:

WHERE clause in SQL statement used in JSP page

Hi All,

  Well, I am much closer, but I can not seem to get my "WHERE" clause to work. Jakarta-Tomcat seems to complain about a missing operator. I think I need to add quotes, but every combination I try gives my syntax errors. What could be wrong with my WHERE statement?


<%@include file="myobjects.jsp"%>
<%@ page import="java.sql.*" %>

<%
String sDefinition = "";
String sLabelString = "";

sLabelString = request.getParameter("currlabel").trim();

//Load the JDBC-ODBC bridge driver
try {
     Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
} catch( ClassNotFoundException ee) {  
     ee.printStackTrace();
}
 
//open a data source name by means of the jdbcodbcdriver.
// ODBC data source name
String dsn = "jdbc:odbc:paysyshelpmenu";
String user = "admin";
String password = "";

// Connect to the database
Connection con = DriverManager.getConnection(dsn, user, password);

// Shut off autocommit
con.setAutoCommit(false);
       

//executing a query on the opened connection.
Statement stmt; // SQL statement object
String query;   // SQL select string
ResultSet rs;   // SQL query results
boolean more;   // "more rows found" switch      
String v1;       // Temporary storage results  
String whereClause = "WHERE ScreenID = " + myobj.getScreenId() + " AND 3270Literal = " + sLabelString + "";

System.out.println("Screen ID = " + myobj.getScreenId());
System.out.println("Label = " + sLabelString);
System.out.println("Where Clause = " + whereClause);

Vector results = new Vector( 10 );

query = "SELECT Definition FROM Employees " + whereClause;
System.out.println("Query = " + query);        


//try {
       stmt = con.createStatement();
       rs = stmt.executeQuery(query);
   
       // Check to see if any rows were read
       more = rs.next();
       if (!more) {

            System.out.println("No rows found.");
            return;

        }

        // Loop through the rows retrieved from the query
        while (more) {

         v1 = "Definition: " + rs.getString("Definition");
         sDefinition = rs.getString("Definition");

            System.out.println( v1 );
            System.out.println("");    

            results.addElement( v1 + "\n");
            more = rs.next();

        }

        rs.close();
     stmt.close();

//} catch (SQLException e) {
//     System.out.println("" + results.size() + " results where found.");
//}


%>

<html>
<title>Whats This</title>
<body>
<p> <%=sDefinition %> </p>
</body>
</html>
0
DoughBoy
Asked:
DoughBoy
  • 6
  • 5
  • 2
  • +1
1 Solution
 
kotanCommented:
In sql statement, string is located between ' (single quote)

String whereClause = "WHERE ScreenID = " + myobj.getScreenId() + " AND 3270Literal = " + sLabelString
+ "";

-->

String whereClause = "WHERE ScreenID = '" + myobj.getScreenId() + "' AND 3270Literal = '" + sLabelString
+ "'";
0
 
objectsCommented:
Using PreparedStatement and placeholders avoids all the problems with quoting and formating etc. Let me know if you'd like more info.
0
 
DoughBoyAuthor Commented:
Hi Kotan,
Thanks, but I tried that and it did not work.

Objects, I could use all the advice you can offer. Do you have some sample code?

Thanks
DoughBoy
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
cheekycjCommented:
I agree with objects.  Use PreparedStatement.  Not only does it remove issues with formatting for SQL it also is a performance enhancement (supposedly - though I haven't done any perf testing or profiling on it)

Also you don't want to use rs.next() to check if there are any rows. rs.next() moves the cursor to the next row, a better approach is to use a counter.

Here is your basic PreparedStatement setup:

try {
String sDefinition = "";
String sLabelString = "";

sLabelString = request.getParameter("currlabel").trim();

//Load the JDBC-ODBC bridge driver
try {
    Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
} catch( ClassNotFoundException ee) {  
    ee.printStackTrace();
}
 
//open a data source name by means of the jdbcodbcdriver.
// ODBC data source name
String dsn = "jdbc:odbc:paysyshelpmenu";
String user = "admin";
String password = "";
   // Connect to the database
  Connection con = DriverManager.getConnection(dsn, user, password);

  // Shut off autocommit
  con.setAutoCommit(false);

  String query = "SELECT Definition FROM Employees WHERE ScreenID = ? AND 3270Literal = ?";
  PreparedStatement ps = con.prepareStatement(query);
  ResultSet rs = null;
  ps.setLong(1, myobj.getScreenId());
  ps.setString(2, sLabelString);
  rs = ps.executeQuery();
  int count = 0;
  while (rs.next()) {
        v1 = "Definition: " + rs.getString("Definition");
        sDefinition = rs.getString("Definition");

        System.out.println( v1 );
        System.out.println("");    

        results.addElement( v1 + "\n");

  }
  if (count == 0)
      System.out.println("No results found");
  rs.close();
  ps.close();
  con.close();
} catch (Exception e) {e.printStackTrace();}

HTH,
CJ
0
 
DoughBoyAuthor Commented:
Thanks for your suggestion, CJ.

This is the error I receive:

....

Root cause:
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (m
issing operator) in query expression 'ScreenID = Pa_RaM000 AND 3270Literal = Zip Code'.
....
0
 
cheekycjCommented:
Oh I didn't know that Screen ID was a String.

change the following line:
ps.setLong(1, myobj.getScreenId());

to

ps.setString(1, myobj.getScreenId());

CJ
0
 
DoughBoyAuthor Commented:
Still same error...

Thanks, I saw that and changed it too. What is wierd is that it looks like myobj.getScreenId() is being applied twice in the query string. And sLabelString is not even used.
0
 
cheekycjCommented:
can you post your current code and exact stack trace java outputs.

CJ
0
 
DoughBoyAuthor Commented:
code:

<%@include file="myobj.jsp"%>
<%@ page import="java.io.*" %>
<%@ page import="java.sql.*" %>

<%
String sDefinition = "";
String sLabelString = "";

sLabelString = request.getParameter("currlabel").trim();
System.out.println ("Label String: " + sLabelString);

//Load the JDBC-ODBC bridge driver
try {
      Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
} catch( ClassNotFoundException ee) {  
      ee.printStackTrace();
}
 
//open a data source name by means of the jdbcodbcdriver.
// ODBC data source name
String dsn = "jdbc:odbc:helpmenu";
String user = "admin";
String password = "";

// Connect to the database
Connection con = DriverManager.getConnection(dsn, user, password);

// Shut off autocommit
con.setAutoCommit(false);
       
String query = "SELECT Definition FROM GUIHelpEnglish WHERE ScreenID = ? AND 3270Literal = ?";
PreparedStatement ps = con.prepareStatement(query);

System.out.println(query);

ResultSet rs = null;
ps.setString(1, myobj.getScreenId());
//ps.setString(1, "SCREEN01");
ps.setString(2, sLabelString);
//ps.setString(2, "PLAN");

rs = ps.executeQuery();

//try {
 int count = 0;
 while (rs.next()) {
       sDefinition = rs.getString("Definition");

       System.out.println(sDefinition);    

 }
if (count == 0) {
     System.out.println("No results found");
}
 
rs.close();
ps.close();
con.close();

//} catch (SQLException e) {
//}


%>

<html>
<title>Whats This</title>
<body>
<p> <%=sDefinition %> </p>
</body>
</html>


browser message: (NOTICE THE STRINGS CHANGE TO SOME STRANGE VALUE - I thought the screen ID was sent twice, but both strings get messed up. I don't know where Pa_RaM000 came from. The values get passed correctly from previous pages. And Why would single quotes be wrapped around the entire ps?)

Error: 500
Location: /mydemo/my.jsp
Internal Servlet Error:

javax.servlet.ServletException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'ScreenID = Pa_RaM000 AND 3270Literal = Pa_RaM001'.
      at org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:459)
      at _0002fwhatsthis_0002ejspwhatsthis_jsp_8._jspService(_0002fwhatsthis_0002ejspwhatsthis_jsp_8.java:203)
      at com.sabratec.servlet.GXAbstractJspPageServlet.service(Unknown Source)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
      at org.apache.jasper.servlet.JspServlet$JspCountedServlet.service(JspServlet.java:130)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
      at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:282)
      at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:429)
      at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:500)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
      at org.apache.tomcat.core.ServletWrapper.doService(ServletWrapper.java:405)
      at org.apache.tomcat.core.Handler.service(Handler.java:287)
      at org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372)
      at org.apache.tomcat.core.ContextManager.internalService(ContextManager.java:812)
      at org.apache.tomcat.core.ContextManager.service(ContextManager.java:758)
      at org.apache.tomcat.service.http.HttpConnectionHandler.processConnection(HttpConnectionHandler.java:213)
      at org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:416)
      at org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:501)
      at java.lang.Thread.run(Thread.java:484)
Root cause:
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'ScreenID = Pa_RaM000 AND 3270Literal = Pa_RaM001'.
      at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6031)
      at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:6188)
      at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:2533)
      at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:217)
      at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeQuery(JdbcOdbcPreparedStatement.java:92)
      at _0002fwhatsthis_0002ejspwhatsthis_jsp_8._jspService(_0002fwhatsthis_0002ejspwhatsthis_jsp_8.java:164)
      at com.sabratec.servlet.GXAbstractJspPageServlet.service(Unknown Source)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
      at org.apache.jasper.servlet.JspServlet$JspCountedServlet.service(JspServlet.java:130)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
      at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:282)
      at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:429)
      at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:500)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
      at org.apache.tomcat.core.ServletWrapper.doService(ServletWrapper.java:405)
      at org.apache.tomcat.core.Handler.service(Handler.java:287)
      at org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372)
      at org.apache.tomcat.core.ContextManager.internalService(ContextManager.java:812)
      at org.apache.tomcat.core.ContextManager.service(ContextManager.java:758)
      at org.apache.tomcat.service.http.HttpConnectionHandler.processConnection(HttpConnectionHandler.java:213)
      at org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:416)
      at org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:501)
      at java.lang.Thread.run(Thread.java:484)



Errors in TOMCAT CONSOLE:
Label String: PLAN
SELECT Definition FROM Employees WHERE ScreenID = ? AND 3270Literal = ?
2002-05-30 13:55:12 - Ctx( /mydemo ): Exception in: R( /mydemo +
 /my.jsp + null) - javax.servlet.ServletException: [Microsoft][ODBC Micro
soft Access Driver] Syntax error (missing operator) in query expression 'ScreenI
D = Pa_RaM000 AND 3270Literal = Pa_RaM001'.
        at org.apache.jasper.runtime.PageContextImpl.handlePageException(PageCon
textImpl.java:459)
        at _0002fmy_0002ejspwhatsthis_jsp_8._jspService(_0002fmy_0
002ejspmy_jsp_8.java:203)
 
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
        at org.apache.jasper.servlet.JspServlet$JspCountedServlet.service(JspSer
vlet.java:130)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
        at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspSer
vlet.java:282)
        at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:4
29)
        at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:500)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
        at org.apache.tomcat.core.ServletWrapper.doService(ServletWrapper.java:4
05)
        at org.apache.tomcat.core.Handler.service(Handler.java:287)
        at org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372
)
        at org.apache.tomcat.core.ContextManager.internalService(ContextManager.
java:812)
        at org.apache.tomcat.core.ContextManager.service(ContextManager.java:758
)
        at org.apache.tomcat.service.http.HttpConnectionHandler.processConnectio
n(HttpConnectionHandler.java:213)
        at org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:
416)
        at org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java
:501)
        at java.lang.Thread.run(Thread.java:484)
Root cause:
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (m
issing operator) in query expression 'ScreenID = Pa_RaM000 AND 3270Literal = Pa_
RaM001'.
        at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6031)
        at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:6188)
        at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:2533)
        at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedState
ment.java:217)
        at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeQuery(JdbcOdbcPrepared
Statement.java:92)
        at _0002fmy_0002ejspmy_jsp_8._jspService(_0002fmy_0
002ejspmy_jsp_8.java:164)

        at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
        at org.apache.jasper.servlet.JspServlet$JspCountedServlet.service(JspSer
vlet.java:130)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
        at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspSer
vlet.java:282)
        at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:4
29)
        at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:500)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
        at org.apache.tomcat.core.ServletWrapper.doService(ServletWrapper.java:4
05)
        at org.apache.tomcat.core.Handler.service(Handler.java:287)
        at org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372
)
        at org.apache.tomcat.core.ContextManager.internalService(ContextManager.
java:812)
        at org.apache.tomcat.core.ContextManager.service(ContextManager.java:758
)
        at org.apache.tomcat.service.http.HttpConnectionHandler.processConnectio
n(HttpConnectionHandler.java:213)
        at org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:
416)
        at org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java
:501)
        at java.lang.Thread.run(Thread.java:484)


0
 
cheekycjCommented:
When you used your string constants:

ps.setString(1, "SCREEN01");
ps.setString(2, "PLAN");

did it work?

also replace your System.out.println(query) with:

System.out.println(query + ":" + myobj.getScreenId() + "," + sLabelString);

What gets outputted?

CJ


0
 
cheekycjCommented:
also add this AFTER the ps.setString() statements.

System.out.println(ps.toString());

CJ
0
 
cheekycjCommented:
Another Option to try if you just want to get it to work and don't care about the benefits of prepared statements is this:


<%@include file="myobj.jsp"%>
<%@ page import="java.io.*" %>
<%@ page import="java.sql.*" %>

<%
String sDefinition = "";
String sLabelString = "";

sLabelString = request.getParameter("currlabel").trim();
System.out.println ("Label String: " + sLabelString);

//Load the JDBC-ODBC bridge driver
try {
     Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
} catch( ClassNotFoundException ee) {  
     ee.printStackTrace();
}
 
//open a data source name by means of the jdbcodbcdriver.
// ODBC data source name
String dsn = "jdbc:odbc:helpmenu";
String user = "admin";
String password = "";

// Connect to the database
Connection con = DriverManager.getConnection(dsn, user, password);

// Shut off autocommit
con.setAutoCommit(false);
     
Statement stmt = con.createStatement();

System.out.println(query + ":" + myobj.getScreenId() + "," + sLabelString);

ResultSet rs = stmt.executeQuery("SELECT Definition FROM GUIHelpEnglish " +
                                 "WHERE ScreenID = '" + myobj.getScreenId() +
                                 "' AND 3270Literal = '" + sLabelString + "';");
//try {
int count = 0;
while (rs.next()) {
      sDefinition = rs.getString("Definition");

      System.out.println(sDefinition);    

}
if (count == 0) {
    System.out.println("No results found");
}

rs.close();
con.close();

//} catch (SQLException e) {
//}


%>

<html>
<title>Whats This</title>
<body>
<p> <%=sDefinition %> </p>
</body>
</html>

CJ
0
 
kotanCommented:
Check for field names and types in the original database. Make sure all your usages are correct.

What is the type of ScreenID and 3270Literal?
0
 
DoughBoyAuthor Commented:
Ah, my friend. Thank you. "3270Literal" was a bad name. It needed a character to start the table name.
Good call.
Thanks to all of you for your help. I learned a lot.
DoughBoy
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now