finder/sql - scalar functions
Krish.Venkat
Hi , I am running WLS7sp1, oracle 8.1.7 with the classes12.zip drivers
I have a finder method as follows:
<query>
<query-method>
<method-name>findDistinctDrivenFields1</method-name>
<method-params>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql>
<![CDATA[
SELECT o FROM DrivingDrivenMatrix AS o
WHERE
o.paramId = ?1 and
((o.drivingField = ?2 and
(o.drivingCondition = 'BETWEEN' and
(?4 BETWEEN {fn substring(o.drivingValue,1,{fn
locate(o.drivingValue, 'and') } - 1) } and
{fn substring(o.drivingValue,{fn
locate(o.drivingValue, 'and')} +3,{fn length(o.drivingValue)})}
)
)
))
]]>
</ejb-ql>
</query>
When ejbc runs ,
it throws the error:
[java] ERROR: Error from ejbc: Error while reading 'META-INF/weblogic-cmp-rdbms-jar.xml'.
The error was:
[java]
[java] weblogic.ejb20.cmp.rdbms.finders.IllegalExpressionException:
[java] While trying to process Query
[java] Method Name: findDistinctDrivenFields1
[java] Parameter Types: (java.lang.String, java.lang.String, java.lang.String,
java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String)
[java] EJB Query: ......
[java] Could not parse EJB QL expression: .....
[java] weblogic.ejb20.ejbc.EJBCException: antlr.TokenStreamRecognitionException:
unexpected char: {
[java] Error in 'BETWEEN' expression.
[java] Check the BETWEEN syntax: expr1 [NOT] BETWEEN expr2 AND expr3
[java] Check that no EJB QL keywords are being used as arguments: expr1, expr2
or expr3.
[java] Error in WHERE clause.
[java] Check that no EJB QL keywords are being used as:
[java] variable names.
I guess if I have use functions like substring, locate, length in my finder I
have to use it as
eg: {fn substring() }
which is what I am doing above.
If I do not put the fn keyword in the finder, at runtime I get the following error
java.sql.SQLException: Non supported SQL92 token at position: 757: fn
java.sql.SQLException: Non supported SQL92 token at position: 757: fn
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:210)
at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:829)
at oracle.jdbc.driver.OracleSql.handleToken(OracleSql.java:152)
at oracle.jdbc.driver.OracleSql.handleODBC(OracleSql.java:112)
at oracle.jdbc.driver.OracleSql.parse(OracleSql.java:64)
at oracle.jdbc.driver.OracleConnection.nativeSQL(OracleConnection.java:762)
at oracle.jdbc.driver.OracleStatement.expandSqlEscapes(OracleStatement.java:4790)
at oracle.jdbc.driver.OracleStatement.parseSqlKind(OracleStatement.java:4779)
at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java:209)
at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java:165)
at oracle.jdbc.driver.OracleConnection.privatePrepareStatement(OracleConnection.java:604)
at oracle.jdbc.driver.OracleConnection.prepareStatement(OracleConnection.java:485)
at weblogic.jdbc.jts.Connection.prepareStatement(Connection.java:138)
at weblogic.jdbc.rmi.internal.ConnectionImpl.prepareStatement(ConnectionImpl.java:139)
at weblogic.jdbc.rmi.SerialConnection.prepareStatement(SerialConnection.java:92)
at com.bofa.news.business.entity.drivingdrivenmatrix.DrivingDrivenMatrixBean_g8txe4__WebLogic_CMP_RDBMS.ejbFindDistinctDrivenFields1(DrivingDrivenMatrixBean_g8txe4__WebLogic_CMP_RDBMS.java:1105)
at java.lang.reflect.Method.invoke(Native Method)
at weblogic.ejb20.cmp.rdbms.RDBMSPersistenceManager.collectionFinder(RDBMSPersistenceManager.java:314)
at weblogic.ejb20.manager.BaseEntityManager.collectionFinder(BaseEntityManager.java:739)
at weblogic.ejb20.manager.BaseEntityManager.collectionFinder(BaseEntityManager.java:712)
at weblogic.ejb20.internal.EntityEJBLocalHome.finder(EntityEJBLocalHome.java:476)
at com.bofa.news.business.entity.drivingdrivenmatrix.DrivingDrivenMatrixBean_g8txe4_LocalHomeImpl.findDistinctDrivenFields1(DrivingDrivenMatrixBean_g8txe4_LocalHomeImpl.java:123)
whatz am I missing ?
Thanx,
Krish
thorick
Right, the JDBC escapes 'fn' are not supported in EJB QL or WebLogic QL.
What does the EJB QL query look like that does not have the 'fn' escapes ?
-thorick
Krish.Venkat
If I do not put the fn's in the finder explicitly, then at runtime I see the following
in the log-file
DRVR DBG1 SQL: "SELECT WL0.DRIVEN_FIELD, WL0.DRIVEN_VALUE, WL0.DRIVING_FIELD,
WL0.DRIVING_VALUE, WL0.PARAM_ID, WL0.DEPENDENCY, WL0.DRIVEN_SCREEN_ID, WL0.DRIVING_CONDITION,
WL0.LAST_UPDATE_TIME, WL0.LAST_USER_ID, WL0.MATRIX_DESC, WL0.RULE_DEFN, WL0.VERSION
FROM DRIVING_DRIVEN_MATRIX WL0 WHERE ( (((WL0.PARAM_ID = ?)) AND ( (((WL0.DRIVING_FIELD
= ?)) AND ( ( ( (((WL0.DRIVING_CONDITION = 'EQUAL')) AND ( ((? = WL0.DRIVING_VALUE)
) OR ((? = WL0.DRIVING_VALUE) ) ) ) OR (((WL0.DRIVING_CONDITION = 'NOTEQUAL'))
AND ( ((? <> WL0.DRIVING_VALUE) ) OR ((? <> WL0.DRIVING_VALUE) ) ) ) ) OR
(((WL0.DRIVING_CONDITION = 'LIKE')) AND ( ((WL0.DRIVING_VALUE LIKE ? ) ) OR
((WL0.DRIVING_VALUE LIKE ? ) ) ) ) ) OR (((WL0.DRIVING_CONDITION = 'BETWEEN'))
AND ( (((? >= { fn SUBSTRING( WL0.DRIVING_VALUE, 1, { fn LOCATE( WL0.DRIVING_VALUE,
'and' ) } - 1 ) } )) AND ((? <= { fn SUBSTRING( WL0.DRIVING_VALUE, { fn LOCATE(
WL0.DRIVING_VALUE, 'and' ) } + 3, { fn LENGTH( WL0.DRIVING_VALUE ) } ) } ))
) OR (((? >= { fn SUBSTRING( WL0.DRIVING_VALUE, 1, { fn LOCATE( WL0.DRIVING_VALUE,
'and' ) } - 1 ) } )) AND ((? <= { fn SUBSTRING( WL0.DRIVING_VALUE, { fn LOCATE(
WL0.DRIVING_VALUE, 'and' ) } + 3, { fn LENGTH( WL0.DRIVING_VALUE ) } ) } ))
) ) ) ) ) OR (((WL0.DRIVING_FIELD = ?)) AND ((WL0.DRIVING_VALUE = ?)) ) )) AND
( ((WL0.DRIVEN_SCREEN_ID = ?) ) OR ((WL0.DRIVEN_SCREEN_ID = ?) ) ) ) "
DRVR FUNC OracleStatement.OracleStatement(conn, batchValue=1, rowPrefetchValue=10)
DBAC FUNC DBDataSetImpl.DBDataSetImpl(conn, nrows=0 ,dataSetType= 0)
DRVR FUNC OraclePreparedStatement.OraclePreparedStatement(conn, sql, batchValue,
rowPrefetchValue)
DBAC FUNC DBDataSetImpl.DBDataSetImpl(conn, nrows=1 ,dataSetType= 1)
DRVR FUNC OracleConnection.nativeSQL(sql)
DRVR DBG1 Input SQL: "SELECT WL0.DRIVEN_FIELD, WL0.DRIVEN_VALUE, WL0.DRIVING_FIELD,
WL0.DRIVING_VALUE, WL0.PARAM_ID, WL0.DEPENDENCY, WL0.DRIVEN_SCREEN_ID, WL0.DRIVING_CONDITION,
WL0.LAST_UPDATE_TIME, WL0.LAST_USER_ID, WL0.MATRIX_DESC, WL0.RULE_DEFN, WL0.VERSION
FROM DRIVING_DRIVEN_MATRIX WL0 WHERE ( (((WL0.PARAM_ID = ?)) AND ( (((WL0.DRIVING_FIELD
= ?)) AND ( ( ( (((WL0.DRIVING_CONDITION = 'EQUAL')) AND ( ((? = WL0.DRIVING_VALUE)
) OR ((? = WL0.DRIVING_VALUE) ) ) ) OR (((WL0.DRIVING_CONDITION = 'NOTEQUAL'))
AND ( ((? <> WL0.DRIVING_VALUE) ) OR ((? <> WL0.DRIVING_VALUE) ) ) ) ) OR
(((WL0.DRIVING_CONDITION = 'LIKE')) AND ( ((WL0.DRIVING_VALUE LIKE ? ) ) OR
((WL0.DRIVING_VALUE LIKE ? ) ) ) ) ) OR (((WL0.DRIVING_CONDITION = 'BETWEEN'))
AND ( (((? >= { fn SUBSTRING( WL0.DRIVING_VALUE, 1, { fn LOCATE( WL0.DRIVING_VALUE,
'and' ) } - 1 ) } )) AND ((? <= { fn SUBSTRING( WL0.DRIVING_VALUE, { fn LOCATE(
WL0.DRIVING_VALUE, 'and' ) } + 3, { fn LENGTH( WL0.DRIVING_VALUE ) } ) } ))
) OR (((? >= { fn SUBSTRING( WL0.DRIVING_VALUE, 1, { fn LOCATE( WL0.DRIVING_VALUE,
'and' ) } - 1 ) } )) AND ((? <= { fn SUBSTRING( WL0.DRIVING_VALUE, { fn LOCATE(
WL0.DRIVING_VALUE, 'and' ) } + 3, { fn LENGTH( WL0.DRIVING_VALUE ) } ) } ))
) ) ) ) ) OR (((WL0.DRIVING_FIELD = ?)) AND ((WL0.DRIVING_VALUE = ?)) ) )) AND
( ((WL0.DRIVEN_SCREEN_ID = ?) ) OR ((WL0.DRIVEN_SCREEN_ID = ?) ) ) ) "
DRVR FUNC DBError.check_error(errNum=34, obj=757: fn)
DRVR FUNC DBError.throwSqlException(errNum=34, obj=757: fn)
DRVR FUNC DBError.findMessage(errNum=34, obj=757: fn)
DRVR FUNC DBError.throwSqlException(reason="Non supported SQL92 token at position:
757: fn", SQLState=null, vendorCode=17034)
SQLException: SQLState(null) vendor code(17034)
java.sql.SQLException: Non supported SQL92 token at position: 757: fn
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:210)
at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:829)
at oracle.jdbc.driver.OracleSql.handleToken(OracleSql.java:152)
at oracle.jdbc.driver.OracleSql.handleODBC(OracleSql.java:112)
at oracle.jdbc.driver.OracleSql.parse(OracleSql.java:64)
at oracle.jdbc.driver.OracleConnection.nativeSQL(OracleConnection.java:762)
at oracle.jdbc.driver.OracleStatement.expandSqlEscapes(OracleStatement.java:4790)
at oracle.jdbc.driver.OracleStatement.parseSqlKind(OracleStatement.java:4779)
at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java:209)
at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java:165)
at oracle.jdbc.driver.OracleConnection.privatePrepareStatement(OracleConnection.java:604)
at oracle.jdbc.driver.OracleConnection.prepareStatement(OracleConnection.java:485)
at weblogic.jdbc.jts.Connection.prepareStatement(Connection.java:138)
at weblogic.jdbc.rmi.internal.ConnectionImpl.prepareStatement(ConnectionImpl.java:139)
at weblogic.jdbc.rmi.SerialConnection.prepareStatement(SerialConnection.java:92)
at com.bofa.news.business.entity.drivingdrivenmatrix.DrivingDrivenMatrixBean_g8txe4__WebLogic_CMP_RDBMS.ejbFindDistinctDrivenFields1(DrivingDrivenMatrixBean_g8txe4__WebLogic_CMP_RDBMS.java:1105)
at java.lang.reflect.Method.invoke(Native Method)
at weblogic.ejb20.cmp.rdbms.RDBMSPersistenceManager.collectionFinder(RDBMSPersistenceManager.java:314)
at weblogic.ejb20.manager.BaseEntityManager.collectionFinder(BaseEntityManager.java:739)
Krish.Venkat
The specs for ejb20 at 11.4 has the following :
functions_returning_strings ::=CONCAT (string_expression, string_expression) |
SUBSTRING (string_expression, arithmetic_expression, arithmetic_expression)
functions_returning_numerics::=
LENGTH (string_expression) |
LOCATE (string_expression, string_expression[, arithmetic_expression]) |
ABS (arithmetic_expression) |
SQRT (arithmetic_expression)
So why is it not supported in EJB-QL ?
Thanx,
Krish
thorick
Hi,
The JDBC function 'fn' is not supported in EJB QL.
It would be good to see what the actual EJB QL is without the 'fn's
in it in addition to the generated SQL that resulted from it.
thanks
-thorick
KRISH.VENKAT
I already sent it out in the earlier post, but here it is again..
FINDER WITHOUT FN in the ejb-jar.xml
<![CDATA[SELECT o FROM DrivingDrivenMatrix AS o WHERE o.paramId = ?1 and ((o.drivingField
= ?2 and ((o.drivingCondition = 'EQUAL' and (?4 = o.drivingValue or ?3 = o.drivingValue))
or (o.drivingCondition = 'NOTEQUAL' and (?4 <> o.drivingValue or ?3 <> o.drivingValue))
or (o.drivingCondition = 'LIKE' and (o.drivingValue like ?4 or o.drivingValue
like ?3)) or (o.drivingCondition = 'BETWEEN' and (?4 BETWEEN substring(o.drivingValue,1,locate(o.drivingValue,
'and') - 1) and substring(o.drivingValue, locate(o.drivingValue, 'and') +3, length(o.drivingValue))))))
or (o.drivingField = ?7 and o.drivingValue = ?8) ) and ( o.drivenScreenId = ?5
or o.drivenScreenId = ?6)]]>
RUNTIME SQL QUERY IF NO FN IN FINDER(this is at runtime from the jdbc logfile)
DRVR DBG1 Input SQL: "SELECT WL0.DRIVEN_FIELD, WL0.DRIVEN_VALUE, WL0.DRIVING_FIELD,
WL0.DRIVING_VALUE, WL0.PARAM_ID, WL0.DEPENDENCY, WL0.DRIVEN_SCREEN_ID, WL0.DRIVING_CONDITION,
WL0.LAST_UPDATE_TIME, WL0.LAST_USER_ID, WL0.MATRIX_DESC, WL0.RULE_DEFN, WL0.VERSION
FROM DRIVING_DRIVEN_MATRIX WL0 WHERE ( (((WL0.PARAM_ID = ?)) AND ( (((WL0.DRIVING_FIELD
= ?)) AND ( ( ( (((WL0.DRIVING_CONDITION = 'EQUAL')) AND ( ((? = WL0.DRIVING_VALUE)
) OR ((? = WL0.DRIVING_VALUE) ) ) ) OR (((WL0.DRIVING_CONDITION = 'NOTEQUAL'))
AND ( ((? <> WL0.DRIVING_VALUE) ) OR ((? <> WL0.DRIVING_VALUE) ) ) ) ) OR
(((WL0.DRIVING_CONDITION = 'LIKE')) AND ( ((WL0.DRIVING_VALUE LIKE ? ) ) OR
((WL0.DRIVING_VALUE LIKE ? ) ) ) ) ) OR (((WL0.DRIVING_CONDITION = 'BETWEEN'))
AND ( (((? >= { fn SUBSTRING( WL0.DRIVING_VALUE, 1, { fn LOCATE( WL0.DRIVING_VALUE,
'and' ) } - 1 ) } )) AND ((? <= { fn SUBSTRING( WL0.DRIVING_VALUE, { fn LOCATE(
WL0.DRIVING_VALUE, 'and' ) } + 3, { fn LENGTH( WL0.DRIVING_VALUE ) } ) } ))
) OR (((? >= { fn SUBSTRING( WL0.DRIVING_VALUE, 1, { fn LOCATE( WL0.DRIVING_VALUE,
'and' ) } - 1 ) } )) AND ((? <= { fn SUBSTRING( WL0.DRIVING_VALUE, { fn LOCATE(
WL0.DRIVING_VALUE, 'and' ) } + 3, { fn LENGTH( WL0.DRIVING_VALUE ) } ) } ))
) ) ) ) ) OR (((WL0.DRIVING_FIELD = ?)) AND ((WL0.DRIVING_VALUE = ?)) ) )) AND
( ((WL0.DRIVEN_SCREEN_ID = ?) ) OR ((WL0.DRIVEN_SCREEN_ID = ?) ) ) ) "
It looks like even if the EJB-QL has no "fn" , weblogic internally puts a fn in
the SQL query
thorick
OK, thanks for the info.
I understand what's going on now.
In versions of WLS prior to 8.1, the EJB QL String functions are
implemented using JDBC escapes with 'fn'.
The JDBC driver from Oracle does not support JDBC escapes so
it will not run the queries against the Oracle database.
In 8.1, the String functions were made to obey the <database-type> flag
for Oracle and a few other supported databases so that reliance on the
DBMS independent 'fn' was not relied on.
Unfortunately, not all JDBC vendors have decided to support 'fn'.
One option if you're committed to WLS version 7x would be to use
the WebLogic jDrivers for Oracle which do support the 'fn' escapes.
-thorick
Krish.Venkat
Unfortunately I cannot goto jdriver as it does not support certain features of
JDBC2.0 which classes12.zip of 8.1.7 supports and which the application is currently
using.
Another option I tried was to use the oracle thin driver classes12.zip for oracle9.2.0
which comes packaged with weblogic7.0sp1 server weblogic700\server\ext\jdbc\oracle\920\classes12.zip.
If I use the 920 drivers(with wls70sp1 and oracle8.1.7) and If I do not put "fn"
in my EJB-QL , I do not seem to get ny runtime exceptions like
"Non supported SQL92 token at position: 755: fn" which I get when I use classes12.zip
from 817.
But unfortunately I do not see the record from the BETWEEN clause being picked
up even if the between condition is satisfies..Now to debug the SQL query which
is getting fired from the container I have the jdbc log on, from which I got the
"Non supported SQL92 token at position: 755: fn" ....But with the classes12.zip
of 920 I don't see any trace getting written to the log file and in the log-file
I see the below
-------------------------------------------------------------
JDBC log stream started at Mon Feb 09 12:16:36 EST 2004
-------------------------------------------------------------
DriverManager.initialize: jdbc.drivers = null
JDBC DriverManager initialized
registerDriver: driver[className=oracle.jdbc.driver.OracleDriver,oracle.jdbc.driver.OracleDriver@7405c3]
DriverManager.getDriver("jdbc:oracle:thin:@nynwsddb1:1521:OD02")
trying driver[className=oracle.jdbc.driver.OracleDriver,oracle.jdbc.driver.OracleDriver@7405c3]
getDriver returning driver[className=oracle.jdbc.driver.OracleDriver,oracle.jdbc.driver.OracleDriver@7405c3]
Oracle Jdbc tracing is not avaliable in a non-debug zip/jar file
registerDriver: driver[className=weblogic.jdbc.jts.Driver,weblogic.jdbc.jts.Driver@56be00]
registerDriver: driver[className=weblogic.jdbc.pool.Driver,weblogic.jdbc.pool.Driver@78ae75]
The log-file says that tracing is not available...If I again replace back with
classes12.zip from 817 I can again see the trace..so I am not sure why tracing
is turned off in the classes12.zip from 920.
Firstly is my problem going to be solved by using classes12.zip from 920(as it
looks like the runtime exception is not thrown) and secondly why is the trace
turned off in the classes12.zip from 920.
Thanx,
Krish
Krish Venkat
From the below post , ignore the scenario of trace not happening..
I just needed a classes12_g.zip for that
But the question of whether I can use 920 with my environment still remains !
thorick
The question seems to come down to whether the 920 driver supports
JDBC escapes ('fn' functions). From your description, it sounds like it
does as it is not complaining about not being able to interpret the { fn ..}
syntax. If there are questions about driver support in WLS you might
want to try the JDBC newsgroups (or contact your technical support rep).
-thorick
Krish.Venkat
OK.. leaving aside whether 920 is good enuf or not...this is the other problem
I see
The below driver worked for debug..Now I see why the record is not getting returned...!
Now the EJB-QL which I have is
<ejb-ql>
<![CDATA[SELECT o FROM DrivingDrivenMatrix AS o WHERE o.paramId = ?1 and
((o.drivingField = ?2 and ((o.drivingCondition = 'EQUAL' and (?4 = o.drivingValue
or ?3 = o.drivingValue)) or (o.drivingCondition = '<=' and (?4 <= o.drivingValue
or ?3 <= o.drivingValue)) or (o.drivingCondition = '>=' and (?4 >= o.drivingValue
or ?3 >= o.drivingValue)) or (o.drivingCondition = 'BETWEEN' and (?3 BETWEEN substring(o.drivingValue,1,locate(o.drivingValue,'and')
- 2) and substring(o.drivingValue,locate(o.drivingValue,'and') + 4,LENGTH(o.drivingValue))
)))) or (o.drivingField = ?7 and o.drivingValue = ?8)) and ( o.drivenScreenId
= ?5 or o.drivenScreenId = ?6)]]>
</ejb-ql>
</query>
The sql statement from the weblogic jdbc trace is
SELECT WL0.DRIVEN_FIELD, WL0.DRIVEN_VALUE, WL0.DRIVING_FIELD, WL0.DRIVING_VALUE,
WL0.PARAM_ID, WL0.DEPENDENCY, WL0.DRIVEN_SCREEN_ID, WL0.DRIVING_CONDITION, WL0.LAST_UPDATE_TIME,
WL0.LAST_USER_ID, WL0.MATRIX_DESC, WL0.RULE_DEFN, WL0.VERSION FROM DRIVING_DRIVEN_MATRIX
WL0 WHERE ( (((WL0.PARAM_ID = ?)) AND ( (((WL0.DRIVING_FIELD = ?)) AND ( ( (
(((WL0.DRIVING_CONDITION = 'EQUAL')) AND ( ((? = WL0.DRIVING_VALUE) ) OR ((?
= WL0.DRIVING_VALUE) ) ) ) OR (((WL0.DRIVING_CONDITION = '<=')) AND ( ((? <=
WL0.DRIVING_VALUE) ) OR ((? <= WL0.DRIVING_VALUE) ) ) ) ) OR (((WL0.DRIVING_CONDITION
= '>=')) AND ( ((? >= WL0.DRIVING_VALUE) ) OR ((? >= WL0.DRIVING_VALUE) ) )
) ) OR (((WL0.DRIVING_CONDITION = 'BETWEEN')) AND (((? >= { fn SUBSTRING(
WL0.DRIVING_VALUE, 1, { fn LOCATE( WL0.DRIVING_VALUE, 'and' ) } - 2 ) } )) AND
((? <= { fn SUBSTRING( WL0.DRIVING_VALUE, { fn LOCATE( WL0.DRIVING_VALUE, 'and'
) } + 4, { fn LENGTH( WL0.DRIVING_VALUE ) } ) } ))) ) ) ) OR (((WL0.DRIVING_FIELD
= ?)) AND ((WL0.DRIVING_VALUE = ?)) ) )) AND ( ((WL0.DRIVEN_SCREEN_ID = ?) )
OR ((WL0.DRIVEN_SCREEN_ID = ?) ) ) )
The SQL statement which gets fired (this is in the oracle trace-file)is
SELECT WL0.DRIVEN_FIELD, WL0.DRIVEN_VALUE, WL0.DRIVING_FIELD, WL0.DRIVING_VALUE,
WL0.PARAM_ID, WL0.DEPENDENCY, WL0.DRIVEN_SCREEN_ID, WL0.D
RIVING_CONDITION, WL0.LAST_UPDATE_TIME, WL0.LAST_USER_ID, WL0.MATRIX_DESC, WL0.RULE_DEFN,
WL0.VERSION FROM DRIVING_DRIVEN_MATRIX WL0 WHE
RE ( (((WL0.PARAM_ID = :1)) AND ( (((WL0.DRIVING_FIELD = :2)) AND (((WL0.DRIVING_CONDITION
= 'BETWEEN')) AND (((:3 >= SUBSTR( WL0.DRIVING
_VALUE, 1, INSTR( 'and' , WL0.DRIVING_VALUE) - 2 ) )) AND ((:4 <= SUBSTR(
WL0.DRIVING_VALUE, INSTR( 'and' , WL0.DRIVING_VALUE) +
4, LENGTH( WL0.DRIVING_VALUE ) ) )))) ) OR (((WL0.DRIVING_FIELD = :5))
AND ((WL0.DRIVING_VALUE = :6)) ) )) AND ( ((WL0.DRIVEN_SCREE
N_ID = :7) ) OR ((WL0.DRIVEN_SCREEN_ID = :8) ) ) )
If u see the LOCATE function in JDBC gets converted to INSTR, which is correct..but
the parameters get converted wrongly..
instead of INSTR( WL0.DRIVING_VALUE,'and'), it gets converted to INSTR( 'and'
, WL0.DRIVING_VALUE) and
hence the record is not returned...
whatz going on here ?
Thanx,
Krish