finder/sql - scalar functions - Weblogic

This is a discussion on finder/sql - scalar functions - Weblogic ; Hi , I am running WLS7sp1, oracle 8.1.7 with the classes12.zip drivers I have a finder method as follows: findDistinctDrivenFields1 java.lang.String java.lang.String java.lang.String java.lang.String SELECT o FROM DrivingDrivenMatrix AS o WHERE o.paramId = ?1 and ((o.drivingField = ?2 and (o.drivingCondition ...

+ Reply to Thread
Results 1 to 11 of 11

Thread: finder/sql - scalar functions

  1. finder/sql - scalar functions


    Hi , I am running WLS7sp1, oracle 8.1.7 with the classes12.zip drivers

    I have a finder method as follows:




    findDistinctDrivenFields1

    java.lang.String
    java.lang.String
    java.lang.String
    java.lang.String



    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)})}
    )
    )
    ))
    ]]>



    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.IllegalExpression Exception:
    [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(DBE rror.java:168)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:210)
    at oracle.jdbc.dbaccess.DBError.check_error(DBError.j ava: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(Orac leConnection.java:762)
    at oracle.jdbc.driver.OracleStatement.expandSqlEscape s(OracleStatement.java:4790)
    at oracle.jdbc.driver.OracleStatement.parseSqlKind(Or acleStatement.java:4779)
    at oracle.jdbc.driver.OraclePreparedStatement.(OraclePreparedStatement.java:209)
    at oracle.jdbc.driver.OraclePreparedStatement.(OraclePreparedStatement.java:165)
    at oracle.jdbc.driver.OracleConnection.privatePrepare Statement(OracleConnection.java:604)
    at oracle.jdbc.driver.OracleConnection.prepareStateme nt(OracleConnection.java:485)
    at weblogic.jdbc.jts.Connection.prepareStatement(Conn ection.java:138)
    at weblogic.jdbc.rmi.internal.ConnectionImpl.prepareS tatement(ConnectionImpl.java:139)
    at weblogic.jdbc.rmi.SerialConnection.prepareStatemen t(SerialConnection.java:92)
    at com.bofa.news.business.entity.drivingdrivenmatrix. DrivingDrivenMatrixBean_g8txe4__WebLogic_CMP_RDBMS .ejbFindDistinctDrivenFields1(DrivingDrivenMatrixB ean_g8txe4__WebLogic_CMP_RDBMS.java:1105)
    at java.lang.reflect.Method.invoke(Native Method)
    at weblogic.ejb20.cmp.rdbms.RDBMSPersistenceManager.c ollectionFinder(RDBMSPersistenceManager.java:314)
    at weblogic.ejb20.manager.BaseEntityManager.collectio nFinder(BaseEntityManager.java:739)
    at weblogic.ejb20.manager.BaseEntityManager.collectio nFinder(BaseEntityManager.java:712)
    at weblogic.ejb20.internal.EntityEJBLocalHome.finder( EntityEJBLocalHome.java:476)
    at com.bofa.news.business.entity.drivingdrivenmatrix. DrivingDrivenMatrixBean_g8txe4_LocalHomeImpl.findD istinctDrivenFields1(DrivingDrivenMatrixBean_g8txe 4_LocalHomeImpl.java:123)

    whatz am I missing ?

    Thanx,
    Krish



  2. Re: finder/sql - scalar functions


    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


  3. Re: finder/sql - scalar functions



    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(co nn, 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(DBE rror.java:168)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:210)
    at oracle.jdbc.dbaccess.DBError.check_error(DBError.j ava: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(Orac leConnection.java:762)
    at oracle.jdbc.driver.OracleStatement.expandSqlEscape s(OracleStatement.java:4790)
    at oracle.jdbc.driver.OracleStatement.parseSqlKind(Or acleStatement.java:4779)
    at oracle.jdbc.driver.OraclePreparedStatement.(OraclePreparedStatement.java:209)
    at oracle.jdbc.driver.OraclePreparedStatement.(OraclePreparedStatement.java:165)
    at oracle.jdbc.driver.OracleConnection.privatePrepare Statement(OracleConnection.java:604)
    at oracle.jdbc.driver.OracleConnection.prepareStateme nt(OracleConnection.java:485)
    at weblogic.jdbc.jts.Connection.prepareStatement(Conn ection.java:138)
    at weblogic.jdbc.rmi.internal.ConnectionImpl.prepareS tatement(ConnectionImpl.java:139)
    at weblogic.jdbc.rmi.SerialConnection.prepareStatemen t(SerialConnection.java:92)
    at com.bofa.news.business.entity.drivingdrivenmatrix. DrivingDrivenMatrixBean_g8txe4__WebLogic_CMP_RDBMS .ejbFindDistinctDrivenFields1(DrivingDrivenMatrixB ean_g8txe4__WebLogic_CMP_RDBMS.java:1105)
    at java.lang.reflect.Method.invoke(Native Method)
    at weblogic.ejb20.cmp.rdbms.RDBMSPersistenceManager.c ollectionFinder(RDBMSPersistenceManager.java:314)
    at weblogic.ejb20.manager.BaseEntityManager.collectio nFinder(BaseEntityManager.java:739)




    "thorick" wrote:
    >
    >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
    >



  4. Re: finder/sql - scalar functions



    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" wrote:
    >
    >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
    >



  5. Re: finder/sql - scalar functions


    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



  6. Re: finder/sql - scalar functions



    I already sent it out in the earlier post, but here it is again..

    FINDER WITHOUT FN in the ejb-jar.xml

    = ?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" wrote:
    >
    >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
    >
    >



  7. Re: finder/sql - scalar functions


    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 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



  8. Re: finder/sql - scalar functions



    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.z ip.

    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.j dbc.driver.OracleDriver@7405c3]
    DriverManager.getDriver("jdbcracle:thin:@nynwsddb1:1521:OD02")
    trying driver[className=oracle.jdbc.driver.OracleDriver,oracle.j dbc.driver.OracleDriver@7405c3]
    getDriver returning driver[className=oracle.jdbc.driver.OracleDriver,oracle.j dbc.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.j ts.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


    "thorick" wrote:
    >
    >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 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
    >
    >



  9. Re: finder/sql - scalar functions



    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 !

    "Krish.Venkat" wrote:
    >
    >
    >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.z ip.
    >
    >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.j dbc.driver.OracleDriver@7405c3]
    >DriverManager.getDriver("jdbcracle:thin:@nynwsddb1:1521:OD02")
    > trying driver[className=oracle.jdbc.driver.OracleDriver,oracle.j dbc.driver.OracleDriver@7405c3]
    >getDriver returning driver[className=oracle.jdbc.driver.OracleDriver,oracle.j dbc.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.j ts.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
    >
    >
    >"thorick" wrote:
    >>
    >>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 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
    >>
    >>

    >



  10. Re: finder/sql - scalar functions


    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


  11. Re: finder/sql - scalar functions



    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


    ((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,'an d') + 4,LENGTH(o.drivingValue))
    )))) or (o.drivingField = ?7 and o.drivingValue = ?8)) and ( o.drivenScreenId
    = ?5 or o.drivenScreenId = ?6)]]>



    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



    "thorick" wrote:
    >
    >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
    >



+ Reply to Thread