Bug 24372

Summary: parameter replacement fails in sql:query when replacing in ORDER BY
Product: Taglibs Reporter: Leo Davis <ldavis>
Component: Standard TaglibAssignee: Tomcat Developers Mailing List <dev>
Status: RESOLVED INVALID    
Severity: normal    
Priority: P3    
Version: 1.0.2   
Target Milestone: ---   
Hardware: PC   
OS: All   

Description Leo Davis 2003-11-04 04:55:15 UTC
I'm using JSTL 1.0.4, despite what the version says.
Running on W2k, Tomcat 4.1.27, JDK 1.4.2_02.

My problem is this:

  <sql:query var="accList">
    SELECT * FROM ACCOUNT ORDER BY ACCOUNT_NAME ?
    <sql:param value="ASC" />
  </sql:query>

The error message Tomcat gives me is:

org.apache.jasper.JasperException: 
    SELECT * FROM ACCOUNT
    ORDER BY ACCOUNT_NAME ?
    
  : [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression 'ACCOUNT_NAME Pa_RaM000'.
	at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:254)
	at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
	at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)

etc.

root cause

javax.servlet.ServletException: 
    SELECT * FROM ACCOUNT
    ORDER BY ACCOUNT_NAME ?
    
  : [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression 'ACCOUNT_NAME Pa_RaM000'.
	at
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:531)
	at org.apache.jsp.index_jsp._jspService(index_jsp.java:1198)
	at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:137)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
	at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:210)
	at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
	at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)

etc.

Hardcoding "ASC" or "DESC" in the query and removing the parameter will work ok.
Comment 1 Pierre Delisle 2003-12-12 00:20:09 UTC
Parameter markers in a SQL statement can only be used where "column"
values are normally expected, as is defined in java.sql.PreparedStatement.

For example:
      UPDATE EMPLOYEES SET SALARY = ? WHERE ID = ?

In your example, you are trying to set how the ordering will
be done on the ORDER BY statement. This is not a valid
way to use a parameter marker.

You could rewrite your code as follows to get the expected
result (assuming "orderDirection" is a scoped attribute that
holds the value "ASC" or "DESC").

  <sql:query var="accList">
    SELECT * FROM ACCOUNT ORDER BY ACCOUNT_NAME <c:out value="${orderDirection}"/>
  </sql:query>
Comment 2 Leo Davis 2003-12-12 03:23:44 UTC
Your example was exactly how I got the effect I was looking for.

Since the JSTL mechanism relies on java.sql.PreparedStatment, I agree that this
is an invalid report.