5 Replies Latest reply on Dec 6, 2012 4:03 PM by Steven Hawkins

    updating a clob colum fails in teiid designer

    Kamran Muhammad Newbie

      Hi !

      I have column "notes " as clob in a teiid model table "tubing notes", i can read the data from this table in teiid designer, however update is failing. What is the reason for this , i think i am not specifiyg the clob properly in query.

      Following is the query and the error stack trace i see in jboss log.


      'notes' is type of clob


      UPDATE "tubing_notes" SET "notes" = 'Some notes' WHERE (((("assembly_id" = '00069') AND ("assembly_note_id" = '00014')) AND ("well_id" = 'VQfzPoep25')) AND ("wellbore_id" = 'fPcFMeublR'))



      17:27:41,866 ERROR [stderr] (Taskid=10 User=system_admin: EDM.tubing_notes(update)) java.lang.NullPointerException
      17:27:41,867 ERROR [stderr] (Taskid=10 User=system_admin: EDM.tubing_notes(update))     at org.teiid.core.types.ClobType.getSubString(ClobType.java:77)
      17:27:41,868 ERROR [stderr] (Taskid=10 User=system_admin: EDM.tubing_notes(update))     at org.teiid.core.types.ClobType$1.charAt(ClobType.java:180)
      17:27:41,870 ERROR [stderr] (Taskid=10 User=system_admin: EDM.tubing_notes(update))     at org.teiid.core.util.HashCodeUtil.expHashCode(HashCodeUtil.java:155)
      17:27:41,871 ERROR [stderr] (Taskid=10 User=system_admin: EDM.tubing_notes(update))     at org.teiid.core.util.HashCodeUtil.expHashCode(HashCodeUtil.java:144)
      17:27:41,872 ERROR [stderr] (Taskid=10 User=system_admin: EDM.tubing_notes(update))     at org.teiid.core.types.ClobType.hashCode(ClobType.java:301)
      17:27:41,873 ERROR [stderr] (Taskid=10 User=system_admin: EDM.tubing_notes(update))     at java.lang.Object.toString(Object.java:219)
      17:27:41,874 ERROR [stderr] (Taskid=10 User=system_admin: EDM.tubing_notes(update))     at org.teiid.core.types.Streamable.toString(Streamable.java:113)
      17:27:41,875 ERROR [stderr] (Taskid=10 User=system_admin: EDM.tubing_notes(update))     at java.lang.String.valueOf(String.java:2826)
      17:27:41,876 ERROR [stderr] (Taskid=10 User=system_admin: EDM.tubing_notes(update))     at java.lang.StringBuilder.append(StringBuilder.java:115)
      17:27:41,878 ERROR [stderr] (Taskid=10 User=system_admin: EDM.tubing_notes(update))     at com.pgs.dm.slegge.server.dataaccess.edm_connect.EdmUpdateDataReceiver.removeUnchanged(EdmUpdateDataReceiver.java:92)
      17:27:41,879 ERROR [stderr] (Taskid=10 User=system_admin: EDM.tubing_notes(update))     at com.pgs.dm.slegge.server.dataaccess.edm_connect.EdmUpdateDataReceiver.<init>(EdmUpdateDataReceiver.java:51)
      17:27:41,880 ERROR [stderr] (Taskid=10 User=system_admin: EDM.tubing_notes(update))     at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      17:27:41,882 ERROR [stderr] (Taskid=10 User=system_admin: EDM.tubing_notes(update))     at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
      17:27:41,883 ERROR [stderr] (Taskid=10 User=system_admin: EDM.tubing_notes(update))     at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
      17:27:41,885 ERROR [stderr] (Taskid=10 User=system_admin: EDM.tubing_notes(update))     at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
      17:27:41,886 ERROR [stderr] (Taskid=10 User=system_admin: EDM.tubing_notes(update))     at com.pgs.dm.slegge.server.mstdictionary.UpdateCapabilityImpl.newDataReceiver(UpdateCapabilityImpl.java:278)
      17:27:41,887 ERROR [stderr] (Taskid=10 User=system_admin: EDM.tubing_notes(update))     at com.pgs.dm.slegge.server.mstdictionary.CapabilityBase.buildDataReceiver(CapabilityBase.java:639)
      17:27:41,888 ERROR [stderr] (Taskid=10 User=system_admin: EDM.tubing_notes(update))     at com.pgs.dm.slegge.server.mstdictionary.UpdateCapabilityImpl.execute(UpdateCapabilityImpl.java:152)
      17:27:41,890 ERROR [stderr] (Taskid=10 User=system_admin: EDM.tubing_notes(update))     at com.pgs.dm.slegge.server.threadrunner.UpdateExecutor.do_execute(UpdateExecutor.java:87)
      17:27:41,891 ERROR [stderr] (Taskid=10 User=system_admin: EDM.tubing_notes(update))     at com.pgs.dm.slegge.server.threadrunner.CapabilityExecutor.execute(CapabilityExecutor.java:252)
      17:27:41,892 ERROR [stderr] (Taskid=10 User=system_admin: EDM.tubing_notes(update))     at com.pgs.dm.slegge.server.threadrunner.request.Worker.run(Worker.java:47)
      17:27:41,893 ERROR [stderr] (Taskid=10 User=system_admin: EDM.tubing_notes(update))     at java.lang.Thread.run(Thread.java:662)
      17:27:41,909 INFO  [stdout] (Taskid=10 User=system_admin: EDM.tubing_notes(update)) Ignoring constraint on unconstrainable tubing_notes_v.n1.notes
      17:27:42,219 INFO  [stdout] (Taskid=10 User=system_admin: EDM.tubing_notes(update)) java.lang.NullPointerException
      17:27:42,813 WARN  [org.teiid.CONNECTOR] (Worker6_QueryProcessorQueue697) Connector worker process failed for atomic-request=EImd/hU90ick.0.0.3: org.teiid.translator.TranslatorException: update failed for update statement UPDATE "tubing_notes" SET "notes" = 'org.teiid.core.types.ClobImpl@3762ab3d' WHERE "tubing_notes"."assembly_id" = '00069' AND "tubing_notes"."assembly_note_id" = '00014' AND "tubing_notes"."well_id" = 'VQfzPoep25' AND "tubing_notes"."wellbore_id" = 'fPcFMeublR'
              at com.lgc.dsl.translators.phub.PowerhubJDBCUpdateExecution.execute(PowerhubJDBCUpdateExecution.java:137)
              at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:294) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
              at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:261) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
              at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:425) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
              at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:170) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
              at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:167) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
              at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303) [rt.jar:1.6.0_27]
              at java.util.concurrent.FutureTask.run(FutureTask.java:138) [rt.jar:1.6.0_27]
              at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:118) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
              at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:249) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
              at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:123) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
              at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:298) [teiid-engine-8.1.0.Final.jar:8.1.0.Final]
              at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) [rt.jar:1.6.0_27]
              at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) [rt.jar:1.6.0_27]
              at java.lang.Thread.run(Thread.java:662) [rt.jar:1.6.0_27]




        • 1. Re: updating a clob colum fails in teiid designer
          Steven Hawkins Master



          Tthere is an issue with the ClobType/Streamable.toString when underlying reference is null.  However it's unclear from the above if that is just a tangential issue as it's not the Teiid logic that it making that toString call and Teiid should not be supplying you with ClobType values that have a null reference.  Are you constructing or altering ClobTypes anywhere in your code?



          • 2. Re: updating a clob colum fails in teiid designer
            Kamran Muhammad Newbie

            Thanks Steven.

            Yes ! i have a custom translator, that converts an custom long string type to clob.

            i debugged the translator, however in translater , on updating the record i get "'org.teiid.core.types.ClobImpl@3762ab3d' value for notes column , instead of actual updated value. Should'nt while updating atleast at translator level. it should give the update value.



            • 3. Re: updating a clob colum fails in teiid designer
              Steven Hawkins Master

              "org.teiid.core.types.ClobImpl@..." is just the default toString() of a ClobType.  The value of the ClobType is held by the ClobImpl, which in this case should just wrap the char[] value 'Some notes'.


              ClobType is just a wrapper. ClobImpl provides an actual clob implementation on top of a char store, whether on disk or memory (which should be your case). Those implementation details shouldn't really matter to you though as the end result is that a Clob instance is provided to the translator.


              Are you perhaps at some point creating a ClobType using the default constructor and then not setting a reference value?



              • 4. Re: updating a clob colum fails in teiid designer
                Kamran Muhammad Newbie

                Thanks Steve.

                I got the issue. Issue is that i have longnvarchar sql type for this column. In Translator using teiid method (TypeFacility.getDataTypeNameFromSQLType(type); where type is -1 or longnvarchar)


                Now Teiid converts longnvarchcar to clob in this method. and my teiid model gets clob for this column. When i update this tabel through teiid , teiid sends clob, where as my data source expects a string and fails.


                Now is it correct for Teiid to convert longnvarchar to clob. if so what is the reason?





                • 5. Re: updating a clob colum fails in teiid designer
                  Steven Hawkins Master

                  > Now is it correct for Teiid to convert longnvarchar to clob. if so what is the reason?


                  We don't have an explicit longvarchar type so the only types we can map to are String or Clob.  Since there are many operations in Teiid that limit the length of a String to 4000 characters we map longvarchar to Clob to avoid truncating - and so that our buffering logic can convert the value to something that is disk backed if the value is too large.  How long are your longvarchar values?


                  However like you say that may not be the expected mapping from your source.  What source are you using?


                  If this is a dynamic scenario you can override the metadata import to return String instead or you can override the Translator binding logic to bind Clob as String if the native type is longvarchar.  If this is a common source we can add that fix to Teiid itself.