0 Replies Latest reply on Dec 6, 2006 1:41 AM by Punamchand Waghamare

    Problem with storing SYBASE

    Punamchand Waghamare Newbie

      Problem with storing SYBASE "IMAGE" DATA TYPE in jboss using entity bean

      We are using JBoss-4.0.4.GA, Entity Bean 2.x (CMP), Sybase 12.5
      File Name :- jconn2.jar
      File Version :- 1.4.2_04
      File Size :- 876 kb

      When we try to insert an object in to column of data type "IMAGE" in Sybase by using EJB 2.x we get the fallowing exception

      2006-11-24 13:31:58,328 DEBUG [org.jboss.util.NestedThrowable] org.jboss.util.NestedThrowable.parentTraceEnabled=true
      2006-11-24 13:31:58,359 DEBUG [org.jboss.util.NestedThrowable] org.jboss.util.NestedThrowable.nestedTraceEnabled=false
      2006-11-24 13:31:58,359 DEBUG [org.jboss.util.NestedThrowable] org.jboss.util.NestedThrowable.detectDuplicateNesting=true
      2006-11-24 13:31:58,218 WARN [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Exception destroying ManagedConnection org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@a97cec[state=DESTROYED mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@1f71b53 handles=0 lastUse=1164355312640 permit=false trackByTx=false mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@22f376 context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@9ddfa3]
      org.jboss.resource.JBossResourceException: SQLException; - nested throwable: (java.sql.SQLException: JZ0C0: Connection is already closed.)
      at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.checkException(BaseWrapperManagedConnection.java:541)
      at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.destroy(BaseWrapperManagedConnection.java:255)
      at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.doDestroy(InternalManagedConnectionPool.java:539)
      at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.returnConnection(InternalManagedConnectionPool.java:329)
      at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.returnConnection(JBossManagedConnectionPool.java:552)
      at org.jboss.resource.connectionmanager.BaseConnectionManager2.returnManagedConnection(BaseConnectionManager2.java:407)
      at org.jboss.resource.connectionmanager.BaseConnectionManager2$BaseConnectionEventListener.connectionErrorOccurred(BaseConnectionManager2.java:768)
      at org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener.connectionErrorOccurred(TxConnectionManager.java:550)
      at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.broadcastConnectionError(BaseWrapperManagedConnection.java:327)
      at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.connectionError(BaseWrapperManagedConnection.java:313)
      at org.jboss.resource.adapter.jdbc.WrappedConnection.checkException(WrappedConnection.java:757)
      at org.jboss.resource.adapter.jdbc.WrappedStatement.checkException(WrappedStatement.java:768)
      at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:320)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCIdentityColumnCreateCommand.executeInsert(JDBCIdentityColumnCreateCommand.java:51)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractCreateCommand.performInsert(JDBCAbstractCreateCommand.java:286)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractCreateCommand.execute(JDBCAbstractCreateCommand.java:137)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.createEntity(JDBCStoreManager.java:572)
      at org.jboss.ejb.plugins.CMPPersistenceManager.createEntity(CMPPersistenceManager.java:222)
      at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.createEntity(CachedConnectionInterceptor.java:266)
      at org.jboss.ejb.EntityContainer.createLocalHome(EntityContainer.java:612)
      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.invocation.Invocation.performCall(Invocation.java:345)
      at org.jboss.ejb.EntityContainer$ContainerInterceptor.invokeHome(EntityContainer.java:1113)
      at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:90)
      at org.jboss.ejb.plugins.EntitySynchronizationInterceptor.invokeHome(EntitySynchronizationInterceptor.java:192)
      at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invokeHome(CachedConnectionInterceptor.java:212)
      at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:90)
      at org.jboss.ejb.plugins.EntityInstanceInterceptor.invokeHome(EntityInstanceInterceptor.java:117)
      at org.jboss.ejb.plugins.EntityLockInterceptor.invokeHome(EntityLockInterceptor.java:61)
      at org.jboss.ejb.plugins.EntityCreationInterceptor.invokeHome(EntityCreationInterceptor.java:28)
      at org.jboss.ejb.plugins.CallValidationInterceptor.invokeHome(CallValidationInterceptor.java:41)
      at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:109)
      at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:313)
      at org.jboss.ejb.plugins.TxInterceptorCMT.invokeHome(TxInterceptorCMT.java:126)
      at org.jboss.ejb.plugins.SecurityInterceptor.invokeHome(SecurityInterceptor.java:99)
      at org.jboss.ejb.plugins.LogInterceptor.invokeHome(LogInterceptor.java:121)
      at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invokeHome(ProxyFactoryFinderInterceptor.java:93)
      at org.jboss.ejb.EntityContainer.internalInvokeHome(EntityContainer.java:508)
      at org.jboss.ejb.Container.invoke(Container.java:891)
      at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invokeHome(BaseLocalProxyFactory.java:342)
      at org.jboss.ejb.plugins.local.LocalHomeProxy.invoke(LocalHomeProxy.java:118)
      at $Proxy94.create(Unknown Source)
      _____________________________________________________________________________________________________
      Here is the code for the bean



      /**
      * RecoverLogBean is an EntityBean. This EJBean illustrates:
      *
      * Container-managed JDBC persistence and transactions;
      * the code in this file never directly accesses the data storage.
      * Application-defined exceptions.
      * Use of EJB 2.0 relationships.
      * Use of EJB-QL in query methods.
      *
      * The javadoc comments in this document are critical and should be changed with consideration.
      * The EJBGen tool reads these comments and creates corresponding
      * EJB2.0 XML files as well as Home and Remote interfaces
      *
      * This bean uses EJBGen tags.
      *
      * @ejb.bean description="RecoverLog"
      * name="RecoverLog"
      * local-jndi-name="abs.RecoverLog"
      * type="CMP"
      * primkey-field = "id"
      * view-type="local"
      * cmp-version = "2.x"
      *
      * @jboss.persistence
      * table-name = "recoverLog"
      *
      * @ejb.pk class = "java.lang.Integer"
      *
      * @jboss.unknown-pk
      * column-name = "id"
      * jdbc-type = "INTEGER"
      * sql-type = "INTEGER"
      * class = "java.lang.Integer"
      *
      *
      * @jboss.entity-command name = "sybase-fetch-key"
      * class = "org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCSybaseCreateCommand"
      * @jboss.entity-command-attribute name = "pk-sql"
      * value = "SELECT @@IDENTITY"
      *
      * @ejb.finder
      * signature = "Collection findAll()"
      * query = "SELECT OBJECT(o) FROM RecoverLog o WHERE o.id IS NOT NULL"
      *
      * @ejb.finder
      * signature = "Collection findByStatus(java.lang.String status)"
      * query = "SELECT OBJECT(o) FROM RecoverLog o WHERE o.status = ?1"
      *
      * @ejb.finder
      * signature = "Collection findByStatusUnique(java.lang.String status)"
      * query = "SELECT OBJECT(o) FROM RecoverLog o WHERE o.status = ?1 AND o.id = (SELECT MIN(o1.id) FROM RecoverLog o1 WHERE o1.status = ?1 AND o1.attemptCount = (SELECT MIN(o2.attemptCount) FROM RecoverLog o2 WHERE o2.status = ?1))"
      *
      * @jboss.declared-sql alias = "o"
      * signature = "Collection findByStatusUnique(java.lang.String status)"
      * where = "o.status = {0} AND o.id = (SELECT MIN(o1.id) FROM RecoverLog o1 WHERE o1.status = {0} AND o1.attemptCount = (SELECT MIN(o2.attemptCount) FROM RecoverLog o2 WHERE o2.status = {0}))"
      *
      */
      abstract public class RecoverLogBean implements EntityBean
      {
      // ----------------------------------------------------------- Constants
      // JNDI name of the EJB home object
      // NOTE: Any changes here should also be reflected in the deployment descriptor.
      public final static String JNDI_NAME = "abs.RecoverLog";
      public final static String TYPE_CANCEL_BET = "Bet Cancel";
      public final static String TYPE_PERSIST_BET = "Bet Persist";
      public final static String TYPE_ACCOUNT_BALANCE = "Account Balance";
      public final static String STATUS_PENDING = "Pending";
      public final static String STATUS_RESOLVED = "Resolved";
      public final static String STATUS_INPROGRESS = "InProgress";
      // Added for CR 4311 - status for reaching the max attempt with failures
      public static final String STATUS_DISCARDED = "Discarded";

      // ----------------------------------------------------------- Member Variables
      /**
      * The container assigned reference to the entity
      */
      private EntityContext context;

      // ----------------------------------------------------------- Constructors
      public RecoverLogBean() { /* do nothing */ }

      // ----------------------------------------------------------- Public EJB Methods
      /**
      * Sets the context of the bean
      * @param ec EntityContext
      */
      public void setEntityContext(EntityContext ec) { context = ec; }

      /**
      * Clears the context of the bean
      */
      public void unsetEntityContext() { this.context = null; }

      /**
      * This method is called when the container picks this entity object
      * and assigns it to a specific entity object. Insert code here to
      * acquire any additional resources that it needs when it is in the
      * ready state.
      */
      public void ejbActivate() { /* do nothing */ }

      /**
      * This method is called when the container diassociates the bean
      * from the entity object identity and puts the instance back into
      * the pool of available instances. Insert code to release any
      * resources that should not be held while the instance is in the
      * pool.
      */
      public void ejbPassivate() { /* do nothing */ }

      /**
      * The container invokes this method on the bean whenever it
      * becomes necessary to synchronize the bean's state with the
      * state in the database. This method is called after the container
      * has loaded the bean's state from the database.
      */
      public void ejbLoad() { /* do nothing */ }

      /**
      * The container invokes this method on the bean whenever it
      * becomes necessary to synchronize the state in the database
      * with the state of the bean. This method is called before the
      * container extracts the fields and writes them into the database.
      */
      public void ejbStore() { /* do nothing */ }

      /**
      * The container invokes this method in response to a client-invoked
      * remove request. Insert code to implement any actions before the
      * bean is removed from the database.
      */
      public void ejbRemove() throws javax.ejb.RemoveException { /* do nothing */ }

      /**
      * This method is invoked when a client invokes the matching create()
      * on the home interface. Initialize the fields that will be used
      * used by the container to create a record in the database
      *
      * @ejb.create-method view-type = "local"
      *
      * @param info RecoverLogInfo
      * @return Integer pk
      * @throws CreateException
      */
      public Integer ejbCreate(RecoverLogInfo info) throws CreateException
      {
      //this.setId(new Long(new Date().getTime()));
      setAttemptCount(info.getAttemptCount());
      if(info.getStatus()==null)
      setStatus(STATUS_PENDING);
      else
      setStatus(info.getStatus());
      Object o = info.getObject();
      if(o instanceof BetPersistLogObject)
      this.setType(TYPE_PERSIST_BET);
      else if(o instanceof BetCancelLogObject)
      this.setType(TYPE_CANCEL_BET);
      else if(o instanceof BalanceLogObject) // Added for CR#3274 dt:5aug04
      this.setType(TYPE_ACCOUNT_BALANCE);
      this.setReferenceName(info.getReferenceName());
      setObject(o);
      setTimeEntered(info.getTimeEntered());
      setTimeUpdated(info.getTimeUpdated());
      return null;
      }

      /**
      * The container invokes this method after invoking the ejbCreate
      * method with the same arguments.
      * @param info RecoverLogInfo
      * @throws CreateException
      */
      public void ejbPostCreate(RecoverLogInfo info) throws CreateException
      {
      // do nothing
      }

      // ----------------------------------------------------------- Public Business Methods



      _____________________________________________________________________________________________________
      Here is the data sourse file for sybase ie. sybaseDataSource-ds.xml

      <?xml version="1.0" encoding="UTF-8"?>

      <!-- ===================================================================== -->
      <!-- -->
      <!-- JBoss Server Configuration -->
      <!-- -->
      <!-- New ConnectionManager setup for Sybase ASE/ASA jConnect driver -->
      <!-- Thanks to Marc Zampetti <zampetti@aol.net> -->
      <!-- This is a minimal example. See the generic example for other stuff -->
      <!-- ===================================================================== -->

      <!-- $Id: sybase-ds.xml,v 1.4 2004/09/15 14:37:40 loubyansky Exp $ -->



      <local-tx-datasource>
      <jndi-name>sybaseDataSource</jndi-name>
      <!-- Sybase jConnect URL for the database.
      NOTE: The hostname and port are made up values. The optional
      database name is provided, as well as some additinal Driver
      parameters.
      -->
      <connection-url>jdbc:sybase:Tds:system-name:5000/abs_dev?JCONNECT_VERSION=6</connection-url>
      <driver-class>com.sybase.jdbc2.jdbc.SybDriver</driver-class>
      <user-name>abs_dev</user-name>
      abs_dev

      <min-pool-size>5</min-pool-size>

      <!-- The maximum connections in a pool/sub-pool -->
      <max-pool-size>5</max-pool-size>

      <!-- sql to call when connection is created
      <new-connection-sql>some arbitrary sql</new-connection-sql>
      -->

      <!-- sql to call on an existing pooled connection when it is obtained from pool
      <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
      -->

      <!-- example of how to specify a class that determines a connection is valid before it is handed out from the pool
      <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.DummyValidConnectionChecker</valid-connection-checker-class-name>
      -->

      <!-- Whether to check all statements are closed when the connection is returned to the pool,
      this is a debugging feature that should be turned off in production -->
      <track-statements/>true<track-statements/>
      <new-connection-sql><![CDATA[SELECT COUNT(*) FROM SYSTEMPROPERTY]]></new-connection-sql>
      <check-valid-connection-sql><![CDATA[SELECT COUNT(*) FROM SYSTEMPROPERTY]]></check-valid-connection-sql>
      <no-tx-separate-pools/>
      <track-connection-by-tx/>

      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.SybaseExceptionSorter</exception-sorter-class-name>
      <!-- sql to call when connection is created
      <new-connection-sql>some arbitrary sql</new-connection-sql>
      -->

      <!-- sql to call on an existing pooled connection when it is obtained from pool
      <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
      -->

      <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->

      <type-mapping>Sybase</type-mapping>

      </local-tx-datasource>



      ______________________________________________________________________________

      The Script which create the table is as fallows


      create table recoverLog1 (
      id numeric(8,0) identity ,
      status varchar(30) not null ,
      type varchar(30) not null ,
      referenceName varchar(60) not null ,
      object varchar(260) not null ,
      attemptCount numeric(2,0) not null ,
      timeEntered datetime not null ,
      timeUpdated datetime null ,

      constraint PK_recoverlog1 PRIMARY KEY CLUSTERED ( id )
      ) with identity_gap = 50 on 'default'
      go


      create unique index type_referenceName_ux on RecoverLog1 (type, referenceName)
      go

      create index recoverLogIdx on recoverLog1 (id)
      go

      grant select on recoverLog1 to public
      go
      grant update on recoverLog1 to public
      go
      grant insert on recoverLog1 to public
      go

      _________________________________________________________________________________________
      The fallowing things were observed
      1) No call to callback method ie. ejbPostCreate, ejbStore