Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

WHERE clause in SQL statement used in JSP page

Posted on 2002-05-29
14
Medium Priority
?
5,572 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
Industry Leaders: 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 150 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Files go missing when using DFS (Distributed File System) Replication and how to recover them and fix it.
Without even knowing it, most of us are using web applications on a daily basis.  In fact, Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We generally confuse these web applications to…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

722 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