Solved

WHERE clause in SQL statement used in JSP page

Posted on 2002-05-29
14
5,194 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
  • 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
Gigs: Get Your Project Delivered by an Expert

Select from 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.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
java beans and EJBs 5 192
Handle form fields in spring mvc controller 1 64
Suggestion on WebSite Template Sites 6 101
listing all functions in JavaScript 19 200
Troubleshooting common task sequence error codes
When you’re making plans to join the modern business race, you should analyze various details that may affect your results. Nowadays, millions of businesses are trying to grow into established and appreciated professional enterprises.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

786 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