4 Replies Latest reply on Jul 29, 2004 7:15 AM by Oleksiy Lyulin

    Oracle functions mapping

    Oleksiy Lyulin Newbie

      How I can mapping the last_day or nvl Oracle functions to use it?
      I'm use JBoss-3.2.1 and Oracle8i.
      For resolve my problem I'm trying modify the file:
      D:\JBoss3.2.1\server\default\conf\standardjbosscmp-jdbc.xml
      In section

      <type-mapping>
       <name>Oracle8</name>
       <row-locking-template>SELECT ?1 FROM ?2 WHERE ?3 FOR UPDATE</row-locking-template>
       <pk-constraint-template>CONSTRAINT ?1 PRIMARY KEY (?2)</pk-constraint-template>
       <fk-constraint-template>ALTER TABLE ?1 ADD CONSTRAINT ?2 FOREIGN KEY (?3) REFERENCES ?4 (?5)</fk-constraint-template>
       <alias-header-prefix>t</alias-header-prefix>
       <alias-header-suffix>_</alias-header-suffix>
       <alias-max-length>30</alias-max-length>
       <subquery-supported>true</subquery-supported>
       <true-mapping>1</true-mapping>
       <false-mapping>0</false-mapping>
      
       <function-mapping>
       <function-name>concat</function-name>
       <function-sql>(?1 || ?2)</function-sql>
       </function-mapping>
       <function-mapping>
       <function-name>substring</function-name>
       <function-sql>substr(?1, ?2, ?3)</function-sql>
       </function-mapping>
       ...

      I'm added:
      <function-mapping>
       <function-name>lastday</function-name>
       <function-sql>last_day(?1)</function-sql>
       </function-mapping>


      In my project file (by path: D:\JBoss3.2.1\server\default\deploy\project.ear) in jar-file on folder META-INF and file jbosscmp-jdbc.xml (full path: project.ear\bookkeeping.jar\META-INF\jbosscmp-jdbc.xml) in finder method I'm trying use mapping Oracle function last_day:
      <query>
       <query-method>
       <method-name>findByClockNumber</method-name>
       <method-params>
       <method-param>java.lang.Integer</method-param>
       <method-param>java.util.Date</method-param>
       </method-params>
       </query-method>
       <jboss-ql>
       <![CDATA[select OBJECT(o) from AssetItemsClk o
      where o.clockNumber=?1 and (o.dateFrom < ?2 or o.dateFrom = ?2) and (o.dateTo is null or (o.dateTo > ?2 or o.dateTo = ?2))]]>
       </jboss-ql>
       <read-ahead>
       ...


      But when I deploing my project in to JBoss it deployment it with ERROR:
      2004-07-26 16:57:33,872 45938 ERROR [org.jboss.ejb.EntityContainer] (main:jboss.j2ee:jndiName=InfoBanks,service=EJB EntityContainer) Starting failed
      org.jboss.deployment.DeploymentException: Error compiling JBossQL statement 'select OBJECT(o) from AssetItemsTrans o
      where o.infoTransferId=?1 and (o.dateFrom < lastday(?2) or o.dateFrom = lastday(?2)) and (o.dateTo is null or (o.dateTo > ?2 or o.dateTo = ?2))'; - nested throwable: (org.jboss.ejb.p[26.07.2004 17:03:45] >> lugins.cmp.ejbql.ParseException: Encountered "o.dateFrom < lastday" at line 2, column 34.
      Was expecting one of:
      "ABS" ...
      "LENGTH" ...
      "LOCATE" ...
      "SQRT" ...
      "(" ...
      "+" ...
      "-" ...
      <INTEGER_LITERAL> ...
      <FLOATING_POINT_LITERAL> ...
      <NUMERIC_VALUED_PARAMETER> ...
      <NUMERIC_VALUED_PATH> ...
      "NOT" ...
      <COLLECTION_VALUED_PATH> ...
      <STRING_VALUED_PATH> ...
      "CONCAT" ...
      "[26.07.2004 17:03:46] >> SUBSTRING" ...
      "UCASE" ...
      "LCASE" ...
      <STRING_LITERAL> ...
      <STRING_VALUED_PARAMETER> ...
      <DATETIME_VALUED_PATH> "NOT" ...
      <DATETIME_VALUED_PATH> "IN" ...
      <BOOLEAN_VALUED_PATH> ...
      <BOOLEAN_VALUED_PARAMETER> ...
      "TRUE" ...
      "FALSE" ...
      <DATETIME_VALUED_PATH> "=" ...
      <DATETIME_VALUED_PATH> ">" ...
      <DATETIME_VALUED_PATH> ">=" ...
      <DATETIME_VALUED_PATH> "<" <DATETIME_VALUED_PATH> .[26.07.2004 17:03:47] >> ..
      <DATETIME_VALUED_PATH> "<" <DATETIME_VALUED_PARAMETER> ...
      <VALUE_CLASS_VALUED_PATH> ...
      <VALUE_CLASS_VALUED_PARAMETER> ...
      <ENTITY_VALUED_PATH> ...
      <IDENTIFICATION_VARIABLE> ...
      <ENTITY_VALUED_PARAMETER> ...
      <DATETIME_VALUED_PATH> "BETWEEN" ...
      )
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCJBossQLQuery.<init>(JDBCJBossQLQuery.java:46)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCCommandFactory.createJBossQLQuery[26.07.2004 17:03:48] >> (JDBCCommandFactory.java:63)