2 Replies Latest reply on May 11, 2007 10:05 AM by Roberto Cabeceiro Quint

    problem getting back the identity of a created row

    Roberto Cabeceiro Quint Newbie

      hi all,

      i have to migrate an application from postgres to mssqlserver2005.
      it went quite well, until so far, but now i'm blocked for a while.

      When user enter my application, a row is written to a table "Sessions(id int, user int, startTime datetime)".
      (the id is auto-generated by the database).

      I see the row is correctly written in the database, but when i want to retrieve the id that the database generated,
      the application crashes:


      2007-05-11 13:57:33,516 ERROR [org.jboss.ejb.plugins.LogInterceptor] RuntimeException:
      java.lang.ClassCastException
      at be.xxxx.xxx.xxx.bean.SessionBean$Proxy.getId(<generated>)
      at be.xxxx.xxx.xxx.bean.SessionBean.getSessionValue(SessionBean.java:120)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.ejb.EntityContainer$ContainerInterceptor.invoke(EntityContainer.java:1044)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCRelationInterceptor.invoke(JDBCRelationInterceptor.java:95)
      at org.jboss.ejb.plugins.EntitySynchronizationInterceptor.invoke(EntitySynchronizationInterceptor.java:301)
      at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:186)
      at org.jboss.ejb.plugins.EntityReentranceInterceptor.invoke(EntityReentranceInterceptor.java:82)
      at org.jboss.ejb.plugins.EntityInstanceInterceptor.invoke(EntityInstanceInterceptor.java:174)
      at org.jboss.ejb.plugins.EntityLockInterceptor.invoke(EntityLockInterceptor.java:89)
      at org.jboss.ejb.plugins.EntityCreationInterceptor.invoke(EntityCreationInterceptor.java:53)
      at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:84)
      at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:243)
      at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:104)
      at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:117)
      at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:191)
      at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:122)
      at org.jboss.ejb.EntityContainer.internalInvoke(EntityContainer.java:483)
      at org.jboss.ejb.Container.invoke(Container.java:674)
      at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invoke(BaseLocalProxyFactory.java:353)
      at org.jboss.ejb.plugins.local.EntityProxy.invoke(EntityProxy.java:38)
      at $Proxy220.getSessionValue(Unknown Source)


      However, I think I changed everything correctly:
      standardjbosscmp-jdbc.xml wrote:

      <jbosscmp-jdbc>



      <defaults>

      <datasource>java:/MSSQLDS</datasource>

      <datasource-mapping>MSSQL</datasource-mapping>



      <create-table>true</create-table>

      <remove-table>false</remove-table>

      <read-only>false</read-only>

      <time-out>300</time-out>

      <pk-constraint>true</pk-constraint>

      <fk-constraint>false</fk-constraint>

      <row-locking>false</row-locking>

      <preferred-relation-mapping>foreign-key</preferred-relation-mapping>

      <read-ahead>

      <strategy>on-load</strategy>

      <page-size>1000</page-size>

      <eager-load-group>*</eager-load-group>

      </read-ahead>

      <list-cache-max>1000</list-cache-max>



      <unknown-pk>

      <key-generator-factory>UUIDKeyGeneratorFactory</key-generator-factory>

      <unknown-pk-class>java.lang.String</unknown-pk-class>

      <jdbc-type>VARCHAR</jdbc-type>

      <sql-type>VARCHAR(32)</sql-type>

      </unknown-pk>



      <entity-command name="default"/>



      </defaults>



      <type-mapping>

      <name>MSSQL</name>

      <row-locking-template>SELECT ?1 FROM ?2 with (rowlock) WHERE ?3</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>
      <auto-increment-template>?1 IDENTITY</auto-increment-template>

      <alias-header-prefix>t</alias-header-prefix>

      <alias-header-suffix>_</alias-header-suffix>

      <alias-max-length>32</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>substring(?1, ?2, ?3)</function-sql>

      </function-mapping>

      <function-mapping>

      <function-name>lcase</function-name>

      <function-sql>lower(?1)</function-sql>

      </function-mapping>

      <function-mapping>

      <function-name>length</function-name>

      <function-sql>len(?1)</function-sql>

      </function-mapping>

      <function-mapping>

      <function-name>locate</function-name>

      <function-sql>charindex(?1, ?2, ?3)</function-sql>

      </function-mapping>

      <function-mapping>

      <function-name>abs</function-name>

      <function-sql>abs(?1)</function-sql>

      </function-mapping>

      <function-mapping>

      <function-name>sqrt</function-name>

      <function-sql>sqrt(?1)</function-sql>

      </function-mapping>

      <function-mapping>

      <function-name>ucase</function-name>

      <function-sql>upper(?1)</function-sql>

      </function-mapping>



      <mapping>

      <java-type>java.lang.Integer</java-type>

      <jdbc-type>INTEGER</jdbc-type>

      <sql-type>INT</sql-type>

      </mapping>



      <mapping>

      <java-type>java.lang.Character</java-type>

      <jdbc-type>CHAR</jdbc-type>

      <sql-type>CHAR</sql-type>

      </mapping>



      <mapping>

      <java-type>java.lang.Short</java-type>

      <jdbc-type>SMALLINT</jdbc-type>

      <sql-type>SMALLINT</sql-type>

      </mapping>



      <mapping>

      <java-type>java.lang.Long</java-type>

      <jdbc-type>BIGINT</jdbc-type>

      <sql-type>BIGINT</sql-type>

      </mapping>



      <mapping>

      <java-type>java.math.BigDecimal</java-type>

      <jdbc-type>VARCHAR</jdbc-type>

      <sql-type>VARCHAR(256)</sql-type>

      </mapping>



      <mapping>

      <java-type>java.lang.String</java-type>

      <jdbc-type>VARCHAR</jdbc-type>

      <sql-type>VARCHAR(256)</sql-type>

      </mapping>



      <mapping>

      <java-type>java.lang.Object</java-type>

      <jdbc-type>LONGVARBINARY</jdbc-type>

      <sql-type>IMAGE</sql-type>

      </mapping>



      <mapping>

      <java-type>java.lang.Byte</java-type>

      <jdbc-type>TINYINT</jdbc-type>

      <sql-type>TINYINT</sql-type>

      </mapping>



      <mapping>

      <!--

      | Note that you lose granularity here

      | Use a numeric type and store milliseconds if you really need it

      -->

      <java-type>java.sql.Timestamp</java-type>

      <jdbc-type>TIMESTAMP</jdbc-type>

      <sql-type>DATETIME</sql-type>

      </mapping>



      <mapping>

      <java-type>java.sql.Date</java-type>

      <jdbc-type>DATE</jdbc-type>

      <sql-type>DATETIME</sql-type>

      </mapping>



      <mapping>

      <java-type>java.sql.Time</java-type>

      <jdbc-type>TIME</jdbc-type>

      <sql-type>DATETIME</sql-type>

      </mapping>



      <mapping>

      <java-type>java.util.Date</java-type>

      <jdbc-type>TIMESTAMP</jdbc-type>

      <sql-type>DATETIME</sql-type>

      </mapping>



      <mapping>

      <java-type>java.lang.Boolean</java-type>

      <jdbc-type>BIT</jdbc-type>

      <sql-type>BIT</sql-type>

      </mapping>



      <mapping>

      <java-type>java.lang.Float</java-type>

      <jdbc-type>REAL</jdbc-type>

      <sql-type>REAL</sql-type>

      </mapping>



      <mapping>

      <java-type>java.lang.Double</java-type>

      <jdbc-type>DOUBLE</jdbc-type>

      <sql-type>FLOAT</sql-type>

      </mapping>

      </type-mapping>

      <entity-commands>

      <!-- default command; doesn't support unknown-pk -->

      <entity-command name="default"

      class="org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand"/>



      <!-- variation on default that skips select before insert -->

      <entity-command name="no-select-before-insert"

      class="org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand">

      <attribute name="SQLExceptionProcessor">jboss.jdbc:service=SQLExceptionProcessor</attribute>

      </entity-command>



      <!-- fetches next key value with the specified sql statement -->

      <entity-command name="pk-sql"

      class="org.jboss.ejb.plugins.cmp.jdbc.JDBCPkSqlCreateCommand">

      <attribute name="pk-sql">SELECT SCOPE_IDENTITY()</attribute>

      </entity-command>


      <!-- this command requires auto-increment element for unknown-pk -->

      <entity-command name="mysql-get-generated-keys"

      class="org.jboss.ejb.plugins.cmp.jdbc.mysql.JDBCMySQLCreateCommand"/>



      <!-- retrieves generated key of the record inserted into hsql db -->

      <entity-command name="hsqldb-fetch-key"

      class="org.jboss.ejb.plugins.cmp.jdbc.hsqldb.JDBCHsqldbCreateCommand"/>



      <!-- retrieves generated key of the record inserted into sybase db -->

      <entity-command name="sybase-fetch-key"

      class="org.jboss.ejb.plugins.cmp.jdbc.sybase.JDBCSybaseCreateCommand"/>



      <!-- retrieves serial value of the record inserted into informix db -->

      <entity-command name="informix-serial"

      class="org.jboss.ejb.plugins.cmp.jdbc.informix.JDBCInformixCreateCommand"/>



      <!-- uses key generator to fetch the next key value -->

      <entity-command name="key-generator"

      class="org.jboss.ejb.plugins.cmp.jdbc.JDBCKeyGeneratorCreateCommand">

      <attribute name="key-generator-factory">UUIDKeyGeneratorFactory</attribute>

      </entity-command>


      <!-- to auto-increment primary-keys -->

      <entity-command name="postgresql-fetch-seq"

      class="org.jboss.ejb.plugins.cmp.jdbc.postgres.JDBCPostgresCreateCommand"/>

      <entity-command
      name="get-generated-keys" class="org.jboss.ejb.plugins.cmp.jdbc.jdbc3.JDBCGetGeneratedKeysCreateCommand" />
      <!-- JDK1.4

      <entity-command name="get-generated-keys"

      class="org.jboss.ejb.plugins.cmp.jdbc.jdbc3.JDBCGetGeneratedKeysCreateCommand"/>

      -->

      </entity-commands>



      standardjaws.xml wrote:


      <jaws>
      <datasource>java:/MSSQLDS</datasource>
      <type-mapping>MSSQL</type-mapping>
      <debug>false</debug>

      <default-entity>
      <create-table>true</create-table>
      <remove-table>false</remove-table>
      <tuned-updates>true</tuned-updates>
      <read-only>false</read-only>
      <time-out>300</time-out>
      <row-locking>false</row-locking>
      <read-ahead>false</read-ahead>
      </default-entity>


      <type-mapping-definition>
      <name>MSSQL</name>

      <mapping>
      <java-type>java.lang.Integer</java-type>
      <jdbc-type>INTEGER</jdbc-type>
      <sql-type>INT</sql-type>
      </mapping>

      <mapping>
      <java-type>java.lang.Character</java-type>
      <jdbc-type>CHAR</jdbc-type>
      <sql-type>CHAR</sql-type>
      </mapping>

      <mapping>
      <java-type>java.lang.Short</java-type>
      <jdbc-type>SMALLINT</jdbc-type>
      <sql-type>SMALLINT</sql-type>
      </mapping>

      <mapping>
      <java-type>java.lang.Long</java-type>
      <jdbc-type>BIGINT</jdbc-type>
      <sql-type>BIGINT</sql-type>
      </mapping>

      <mapping>
      <java-type>java.math.BigDecimal</java-type>
      <jdbc-type>VARCHAR</jdbc-type>
      <sql-type>VARCHAR(256)</sql-type>
      </mapping>

      <mapping>
      <java-type>java.lang.String</java-type>
      <jdbc-type>VARCHAR</jdbc-type>
      <sql-type>VARCHAR(256)</sql-type>
      </mapping>

      <mapping>
      <java-type>java.lang.Object</java-type>
      <jdbc-type>JAVA_OBJECT</jdbc-type>
      <sql-type>IMAGE</sql-type>
      </mapping>

      <mapping>
      <java-type>java.lang.Byte</java-type>
      <jdbc-type>TINYINT</jdbc-type>
      <sql-type>TINYINT</sql-type>
      </mapping>

      <mapping>
      <java-type>java.sql.Timestamp</java-type>
      <jdbc-type>TIMESTAMP</jdbc-type>
      <sql-type>TIMESTAMP</sql-type>
      </mapping>

      <mapping>
      <java-type>java.sql.Date</java-type>
      <jdbc-type>DATE</jdbc-type>
      <sql-type>DATETIME</sql-type>
      </mapping>

      <mapping>
      <java-type>java.sql.Time</java-type>
      <jdbc-type>TIME</jdbc-type>
      <sql-type>DATETIME</sql-type>
      </mapping>

      <mapping>
      <java-type>java.util.Date</java-type>
      <jdbc-type>DATE</jdbc-type>
      <sql-type>DATETIME</sql-type>
      </mapping>

      <mapping>
      <java-type>java.lang.Boolean</java-type>
      <jdbc-type>BIT</jdbc-type>
      <sql-type>BIT</sql-type>
      </mapping>

      <mapping>
      <java-type>java.lang.Float</java-type>
      <jdbc-type>REAL</jdbc-type>
      <sql-type>REAL</sql-type>
      </mapping>

      <mapping>
      <java-type>java.lang.Double</java-type>
      <jdbc-type>DOUBLE</jdbc-type>
      <sql-type>FLOAT</sql-type>
      </mapping>
      </type-mapping-definition>





      this is in my bean wrote:

      /**
      * Entity bean die een sessie beschrijft.
      *
      * @ejb.bean
      * name="Session"
      * view-type="local"
      * local-jndi-name="LocalSessionEJB"
      * type="CMP"
      * cmp-version="2.x"
      * primkey-field="id"
      *
      * @ejb.transaction type="required"
      *
      * @ejb.persistence table-name="sessions"
      * @ejb.pk class="java.lang.Integer"
      *
      * @ejb.permission role-name="system"
      * @ejb.security-identity run-as="system"
      *
      * @ejb.value-object
      * @ejb.util generate="physical"
      *
      * @jboss.entity-command
      * name="get-generated-keys"
      *
      * @jboss.method-attributes pattern="get*" read-only="true"
      *
      */

      public Integer ejbCreate....
      public void ejbPostCreate...

      /**
      * id of the sessie.
      * @ejb.interface-method view-type="local"
      * @ejb.persistence
      * @jboss.persistence auto-increment="true"
      */
      public abstract Integer getId();

      /**
      * @ejb.interface-method view-type="local"
      */
      public abstract void setId(Integer id);

      public be.xxxx.xxxx.data.bean.SessionValue getSessionValue() {
      System.out.println("before Sessionvalue");
      SessionValue sessionValue
      = new be.tinc.bkw.data.bean.SessionValue();
      System.out.println("after Sessionvalue");
      UserLocal userLocal;
      System.out.println("getting the user");
      //added for debuggin
      System.out.println("user "+getUser());
      System.out.println("startTime "+getStartTime());
      System.out.println("endTime "+getEndTime());
      System.out.println("after getting the user");
      System.out.println("getting the id");
      //HERE IT CRASHES !!!!!!!!!!!!!!!!!!!
      System.out.println("the id is: "+getId());
      System.out.println("after getting the id");
      sessionValue.setId(getId());
      userLocal = getUser();
      if (null != userLocal) {
      sessionValue.setUser(userLocal.getUserValue());
      }
      sessionValue.setStartTime(getStartTime());
      if (getEndTime() != null){
      sessionValue.setEndTime(getEndTime());
      }
      return sessionValue;
      }