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:[EMAIL PROTECTED]>
D.O.Tech <http://www.dotech.com/>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
