tomcat-taglibs-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kris Schneider <k...@dotech.com>
Subject Re: JSP and JSTL "sql:param" and "order by" with Oracle
Date Fri, 07 Apr 2006 13:44:51 GMT
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 <mailto:kris@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


Mime
View raw message