JSP and JSTL "sql:param" and "order by" with Oracle

[Francis <ff...@met.wallonie.be>]

Impossible to sort a select in sql:query with an "order by ?"  and 
sql:param with Oracle.

<sql:query var="stmt" dataSource="${ ig42 }" >
SELECT id_agent, nom, prenom, no_ulis, no_bureau, prof_telephone,      
serv_principal FROM perso.per_v_agent_opencms
WHERE NVL( perso.per_v_agent_opencms.date_fin_contrat , 
TO_DATE('01/01/2100','DD/MM/YYYY')) > SYSDATE
ORDER BY ?
 <sql:param value="${truc}" />
</sql:query>

I try with truc=nom or truc=2.
The var truc is tested with c:out.
With  "ORDER BY nom" or "ORDER BY 2" in the select the query is in order
Another test with  a where clause "nom like ?" and <sql:param 
value="${truc}%" /> is working with correct value of truc.
The JSP page is working but without sort.
If the text in the var truc is "xxx" it's also working without error and 
sort.

Why?

Francis


---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org

Re: JSP and JSTL "sql:param" and "order by" with Oracle

[Kris Schneider <kr...@dotech.com>]

One of the things to try in cases like this is the equivalent Java code. It 
turns out that doesn't seem to work either, which means JSP/JSTL isn't the 
issue. I tried the following on our own database with various Oracle 
drivers (9.2.0.5 and 10.2.0.1 to name a couple):

import java.io.*;
import java.sql.*;
import java.util.*;

public class ParamTest {

     private static final String DRIVER_CLASSNAME = "oracle.jdbc.OracleDriver";
     private static final String DB_URL = "jdbc:oracle:thin:@...";
     private static final String DB_USER = "...";
     private static final String DB_PASSWORD = "...";

     private static final Properties DB_PROPS = new Properties();
     static {
         DB_PROPS.setProperty("user", DB_USER);
         DB_PROPS.setProperty("password", DB_PASSWORD);
     }

     private static final String SQL = "select owner, table_name from 
all_tables order by ?";
     // try this as "owner", "table_name" or "xxx"
     // can also try an Integer instead
     private static final String ORDER_BY = "owner";

     public static void main(String[] args) throws Exception {
         Class driverClass = Class.forName(DRIVER_CLASSNAME);
         Driver driver = (Driver)driverClass.newInstance();
         Connection conn = null;
         PreparedStatement stmt = null;
         ResultSet rs = null;

         try {
             conn = driver.connect(DB_URL, DB_PROPS);
             stmt = conn.prepareCall(SQL);
             stmt.setObject(1, ORDER_BY);
             rs = stmt.executeQuery();
             while (rs.next()) {
                 System.out.println("Owner: " + rs.getObject(1) + ", Table 
name: " + rs.getObject(2));
             }
         } catch (SQLException exc) {
             exc.printStackTrace();
         } finally {
             if (rs != null) {
                 try {
                     rs.close();
                 } catch (SQLException exc) {
                     exc.printStackTrace();
                 }
             }
             if (stmt != null) {
                 try {
                     stmt.close();
                 } catch (SQLException exc) {
                     exc.printStackTrace();
                 }
             }
             if (conn != null) {
                 try {
                     conn.close();
                 } catch (SQLException exc) {
                     exc.printStackTrace();
                 }
             }
         }
     }
}

Interestingly, if you try something similar with either the jTDS or 
Microsoft SQL Server drivers (against an SQL Server database), you get an 
error trying to use a param for "order by".

Francis wrote:
> Impossible to sort a select in sql:query with an "order by ?"  and 
> sql:param with Oracle.
> 
> <sql:query var="stmt" dataSource="${ ig42 }" >
> SELECT id_agent, nom, prenom, no_ulis, no_bureau, prof_telephone,      
> serv_principal FROM perso.per_v_agent_opencms
> WHERE NVL( perso.per_v_agent_opencms.date_fin_contrat , 
> TO_DATE('01/01/2100','DD/MM/YYYY')) > SYSDATE
> ORDER BY ?
> <sql:param value="${truc}" />
> </sql:query>
> 
> I try with truc=nom or truc=2.
> The var truc is tested with c:out.
> With  "ORDER BY nom" or "ORDER BY 2" in the select the query is in order
> Another test with  a where clause "nom like ?" and <sql:param 
> value="${truc}%" /> is working with correct value of truc.
> The JSP page is working but without sort.
> If the text in the var truc is "xxx" it's also working without error and 
> sort.
> 
> Why?
> 
> Francis

-- 
Kris Schneider <ma...@dotech.com>
D.O.Tech       <http://www.dotech.com/>

---------------------------------------------------------------------
To unsubscribe, e-mail: taglibs-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: taglibs-user-help@jakarta.apache.org