2 Replies Latest reply on Sep 23, 2002 12:27 PM by Dain Sundstrom

    User Defined Data Types (UDTs)

    Jonathan Rosenblitt Newbie

      Hi

      I am experiencing the following difficulties with User Defined Data Types (UDTs)

      Configuration:
      JBoss 3.0.0
      DB2 UDB 7.2 Fixpack 2 & 7
      EJB 1.1 CMP

      I have defined a number of UDT's in DB2. I have updated the standardjaws.xml file with the various datatype definitions. My ejbcreate's work fine and all the data gets inserted to the DB2 Table.

      However my ejb finders all fail. I have set up a separate finder for each column(different UDT) in the table to test the UDT. I get the following error for each find method.

      2002-09-23 10:07:21,328 DEBUG [org.jboss.ejb.plugins.jaws.jdbc.JDBCCommand] findByfromdate command executing: SELECT CHARGEBORG_ID FROM CHARGE_NOUDT2 WHERE FROMDATE=?
      2002-09-23 10:07:21,328 DEBUG [org.jboss.ejb.plugins.jaws.jdbc.JDBCCommand] Set parameter: idx=1, jdbcType=TIMESTAMP, value=2002-09-30 10:15:20.0
      2002-09-23 10:07:21,343 ERROR [org.jboss.ejb.plugins.jaws.jdbc.JDBCCommand] Exception caught executing SQL
      COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0432N A parameter marker cannot have the user defined type name or reference target type name "CAL.RECORDTS". SQLSTATE=42841

      at COM.ibm.db2.jdbc.net.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:244)
      at COM.ibm.db2.jdbc.net.SQLExceptionGenerator.check_return_code(SQLExceptionGenerator.java:408)
      at COM.ibm.db2.jdbc.net.SQLExceptionGenerator.check_return_code(SQLExceptionGenerator.java:386)
      at COM.ibm.db2.jdbc.net.DB2PreparedStatement.executeQuery(DB2PreparedStatement.java:973)
      at org.jboss.resource.adapter.jdbc.local.LocalPreparedStatement.executeQuery(LocalPreparedStatement.java:289)
      at org.jboss.ejb.plugins.jaws.jdbc.JDBCQueryCommand.executeStatementAndHandleResult(JDBCQueryCommand.java:62)

      (The UDT CAL.RECORDTS is just a Timestamp.)


      When I run the SQL through the command window I get the following error:

      db2 => SELECT CHARGEBORG_ID FROM CHARGE_NOUDT2 WHERE FROMDATE='2002-09-30 10:15:20.0'
      SQL0401N The data types of the operands for the operation "=" are not compatible. SQLSTATE=42818
      db2 =>


      If I cast the fromdate field it works fine.

      db2 => SELECT CHARGEBORG_ID FROM CHARGE_NOUDT2 WHERE CAST(FROMDATE as TIMESTAMP) = '2002-09-30 10:15:20.0'

      CHARGEBORG_ID
      -------------
      5
      6

      2 record(s) selected.

      Am I missing something here or is there a problem with the finder method not correctly casting the data type.
      Is there a simple workaround or can't I use UDTs in JBoss. (Same problem experienced in weblogic)

      Thanks
      Jonathan