Query cannot update a text, ntext, or image column and a cluster

Discussions

EJB programming & troubleshooting: Query cannot update a text, ntext, or image column and a cluster

  1. Hi,

        This is my first mail to the group. I have an entity EJB which uses CMP.

    One of the CMP fields is of byte[] type.I am using MS SQL 2000 server

    as database server. I created a table in which the corresponding mapping

    column is of type IMAGE.I am using WEBLOGIC6.0 and JDBC Drivers. I am

    unable to create the entity EJB in which the size of the binary field is about 9kb..where as i am able to create the entity EJB with size of binary data field around 6kb. I dont think the problem is with the size of the data, because in the SQL 2K documentation the size fo IMAGE field is given as of variable 0 through 231-1 (2,147,483,647) bytes. The following is the trace of exceptions i got. I think this problem is faced by some of u already..I will thankful to u if u find some solution to fix this problem

    ************************

    javax.ejb.EJBException
     - with nested exception:
    [java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]The query processor could not produce a query plan from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.]
     at com.cmcltd.paymentserver.echeck.servercheckbook.CheckBookEntityEJB_WebLogic_CMP_RDBMS.ejbStore(CheckBookEntityEJB_WebLogic_CMP_RDBMS.java:739)
     at weblogic.ejb20.manager.DBManager.storeBean(DBManager.java:236)
     at weblogic.ejb20.manager.DBManager.beforeCompletion(DBManager.java:354)
     at weblogic.ejb20.internal.TxManager$TxListener.beforeCompletion(TxManager.java:218)
     at weblogic.transaction.internal.ServerSCInfo.callBeforeCompletions(ServerSCInfo.java:512)
     at weblogic.transaction.internal.ServerSCInfo.startPrePrepareAndChain(ServerSCInfo.java:79)
     at weblogic.transaction.internal.ServerTransactionImpl.localPrePrepareAndChain(ServerTransactionImpl.java:893)
     at weblogic.transaction.internal.ServerTransactionImpl.globalPrePrepare(ServerTransactionImpl.java:1229)
     at weblogic.transaction.internal.ServerTransactionImpl.commit(ServerTransactionImpl.java:168)
     at weblogic.ejb20.internal.BaseEJBHome.postHomeInvoke(BaseEJBHome.java:381)
     at weblogic.ejb20.internal.EntityEJBHome.create(EntityEJBHome.java:379)
     at com.cmcltd.paymentserver.echeck.servercheckbook.CheckBookEntityEJBHomeImpl.create(CheckBookEntityEJBHomeImpl.java:77)
     at com.cmcltd.paymentserver.echeck.servercheckbook.CheckBookEntityEJBHomeImpl_WLSkel.invoke(CheckBookEntityEJBHomeImpl_WLSkel.java:70)
     at weblogic.rmi.internal.BasicServerAdapter.invoke(BasicServerAdapter.java:373)
     at weblogic.rmi.cluster.ReplicaAwareServerRef.invoke(ReplicaAwareServerRef.java:128)
     at weblogic.rmi.internal.BasicServerAdapter.invoke(BasicServerAdapter.java:237)
     at weblogic.rmi.internal.BasicRequestHandler.handleRequest(BasicRequestHandler.java:118)
     at weblogic.rmi.internal.BasicRequestDispatcher.dispatch(BasicRequestDispatcher.java:115)
     at weblogic.rmi.internal.ServerRequest.sendOneWayRaw(ServerRequest.java:88)
     at weblogic.rmi.internal.ServerRequest.sendReceive(ServerRequest.java:108)
     at weblogic.rmi.cluster.ReplicaAwareRemoteRef.invoke(ReplicaAwareRemoteRef.java:247)
     at weblogic.rmi.cluster.ReplicaAwareRemoteRef.invoke(ReplicaAwareRemoteRef.java:225)
     at com.cmcltd.paymentserver.echeck.servercheckbook.CheckBookEntityEJBHomeImpl_WLStub.create(CheckBookEntityEJBHomeImpl_WLStub.java:132)
     at com.cmcltd.paymentserver.echeck.servercheckbook.CheckBookBean.addCheckBook(CheckBookBean.java:44)
     at jsp_servlet._addecheck._jspService(_addecheck.java:154)
     at weblogic.servlet.jsp.JspBase.service(JspBase.java:27)
     at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:213)
     at weblogic.servlet.internal.WebAppServletContext.invokeServlet(WebAppServletContext.java:1265)
     at weblogic.servlet.internal.ServletRequestImpl.execute(ServletRequestImpl.java:1622)
     at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:137)
     at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:120)
    --------------- nested within: ------------------
    weblogic.transaction.RollbackException: Unexpected exception in beforeCompletion: sync = [email protected] - with nested exception:
    [javax.ejb.EJBException
     - with nested exception:
    [java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]The query processor could not produce a query plan from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.]]
     at weblogic.transaction.internal.TransactionImpl.throwRollbackException(TransactionImpl.java:1248)
     at weblogic.transaction.internal.ServerTransactionImpl.commit(ServerTransactionImpl.java:218)
     at weblogic.ejb20.internal.BaseEJBHome.postHomeInvoke(BaseEJBHome.java:381)
     at weblogic.ejb20.internal.EntityEJBHome.create(EntityEJBHome.java:379)
     at com.cmcltd.paymentserver.echeck.servercheckbook.CheckBookEntityEJBHomeImpl.create(CheckBookEntityEJBHomeImpl.java:77)
     at com.cmcltd.paymentserver.echeck.servercheckbook.CheckBookEntityEJBHomeImpl_WLSkel.invoke(CheckBookEntityEJBHomeImpl_WLSkel.java:70)
     at weblogic.rmi.internal.BasicServerAdapter.invoke(BasicServerAdapter.java:373)
     at weblogic.rmi.cluster.ReplicaAwareServerRef.invoke(ReplicaAwareServerRef.java:128)
     at weblogic.rmi.internal.BasicServerAdapter.invoke(BasicServerAdapter.java:237)
     at weblogic.rmi.internal.BasicRequestHandler.handleRequest(BasicRequestHandler.java:118)
     at weblogic.rmi.internal.BasicRequestDispatcher.dispatch(BasicRequestDispatcher.java:115)
     at weblogic.rmi.internal.ServerRequest.sendOneWayRaw(ServerRequest.java:88)
     at weblogic.rmi.internal.ServerRequest.sendReceive(ServerRequest.java:108)
     at weblogic.rmi.cluster.ReplicaAwareRemoteRef.invoke(ReplicaAwareRemoteRef.java:247)
     at weblogic.rmi.cluster.ReplicaAwareRemoteRef.invoke(ReplicaAwareRemoteRef.java:225)
     at com.cmcltd.paymentserver.echeck.servercheckbook.CheckBookEntityEJBHomeImpl_WLStub.create(CheckBookEntityEJBHomeImpl_WLStub.java:132)
     at com.cmcltd.paymentserver.echeck.servercheckbook.CheckBookBean.addCheckBook(CheckBookBean.java:44)
     at jsp_servlet._addecheck._jspService(_addecheck.java:154)
     at weblogic.servlet.jsp.JspBase.service(JspBase.java:27)
     at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:213)
     at weblogic.servlet.internal.WebAppServletContext.invokeServlet(WebAppServletContext.java:1265)
     at weblogic.servlet.internal.ServletRequestImpl.execute(ServletRequestImpl.java:1622)
     at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:137)
     at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:120)


    ********************************

                    Thanx in advance

    Regards,

    N Narayan Singh
  2. Checking on dejanews, it appears that this is a problem when you try to update the key being used for a clustered index and the value of a text, etc. column at the same time. Do you still have this problem if you remove the clustered index?

    Mark
  3. Thanx Mark for ur reply to my query.
    I think in this context clustering index is some which determines the physical order of data in a table. I dont know much about its management..like is it created for a table by default or do we need to set it explicitly.. i did not mentioned anything while creating the table..
    If it is created by default, how to remove it? can u plz help me out in this regard.

    The funniest part is that i was able to create the row with the same binary data using JBoss app server in the same table(database). I think there is some property checking is being done by contair before storing the data in the table..
  4. Try:
         DROP INDEX table_name.index_name.

    If that doesn't work (it may be part of a unique constraint or a primary key) try:
         ALTER TABLE table_name DROP CONSTRAINT constraint_name

    Then drop the index. After that, you should be able to add back the constraint. If you have access to SQL Books Online you should be able to get help with most of the commands.

    Mark
  5. Oh, just to follow up, a clustered index does affect the physical ordering of the table. Rows are ordered based on the cluster keys. I'm not sure if this is created by default or not, but I didn't think it was. You should be able to use the commands I sent to remove the clustered index.

    Mark
  6. Hello Mark,

                I was able to insert the row in the table using Weblogic6.1 in the same table n database without making any changes at the database server.
    Thanx for ur response for my queries..I think its a bug in 6.0

    Regards,
    Narayan