Solved

WHERE clause in SQL statement used in JSP page

Posted on 2002-05-29
14
5,126 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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
In this article, I will show you HOW TO: Perform a Physical to Virtual (P2V) Conversion the easy way from a computer backup (image).
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now