Solved

WHERE clause in SQL statement used in JSP page

Posted on 2002-05-29
14
5,396 Views
Last Modified: 2008-03-17
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
Comment
Question by:DoughBoy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 6

Expert Comment

by:kotan
ID: 7043445
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
 
LVL 92

Expert Comment

by:objects
ID: 7043612
Using PreparedStatement and placeholders avoids all the problems with quoting and formating etc. Let me know if you'd like more info.
0
 

Author Comment

by:DoughBoy
ID: 7044692
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Expert Comment

by:cheekycj
ID: 7044699
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
 

Author Comment

by:DoughBoy
ID: 7044817
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
 
LVL 19

Expert Comment

by:cheekycj
ID: 7044822
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
 

Author Comment

by:DoughBoy
ID: 7044876
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
 
LVL 19

Expert Comment

by:cheekycj
ID: 7045190
can you post your current code and exact stack trace java outputs.

CJ
0
 

Author Comment

by:DoughBoy
ID: 7045220
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
 
LVL 19

Expert Comment

by:cheekycj
ID: 7045604
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
 
LVL 19

Expert Comment

by:cheekycj
ID: 7045621
also add this AFTER the ps.setString() statements.

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

CJ
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 7045635
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
 
LVL 6

Accepted Solution

by:
kotan earned 50 total points
ID: 7045945
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
 

Author Comment

by:DoughBoy
ID: 7046153
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When the s#!t hits the fan, you don’t have time to look up who’s on call, draft emails, call collaborators, or send text messages. An instant chat window is definitely the way to go, especially one like HipChat. HipChat is a true business app. An…
The goal of this blog is to: > note what has impeded us from reaching effective life on-call > provide 3 steps to mastering life on-call > highlight what will be achieved with effective life on-call
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question