Hi
we have the following behaviour when passing parameters from java code. The sql
statement is something like (executed in oracle mode):
SELECT
*
FROM
TABLE1 b
inner join TABLE2 a on (b.e_key=a.key)
WHERE
b.D_START=to_DATE(?, 'dd/mm/yyyy') AND
b.column1=to_number(?) AND
b.e_key=to_number(?)
The java code with parameter passing is
PreparedStatement st= cn.prepareStatement(sqlString);
st.setString(1,"26/05/2004");
st.setString(2,"1");
st.setString(3,"200027");
We get, on maxdb 7.5.01.00, the following error
com.sap.dbtech.jdbc.exceptions.DatabaseException: [-3016]: Invalid numeric constant
Note that all colums involved in the query (except b.D_START) are "fixed(9)".
At the first time we thought the problem was due to "to_number" function, so we tried
not to use it. The query is now:
SELECT
*
FROM
TABLE1 b
inner join TABLE2 a on (b.e_key=a.key)
WHERE
b.D_START=to_DATE(?, 'dd/mm/yyyy') AND
b.column1=? AND
b.e_key=?
and it does work!.
Unfortunatly we need "to_number", because we use the same sql on a oracle db. So we
mustn't remove it.
The strange thing is that excluding the last row from query (" b.e_key=to_number(?)")
and obviuosly not passing the third parameter, everything works fine. So it seems that
"to_number" confilcts with join condition, which refers to the same column.
Thanks in advance
Fabio
