5 Replies Latest reply on May 6, 2013 12:28 PM by Steven Hawkins

    Writing a CLOB through Teiid

    Brian Dudley Newbie

      I have a VDB with tables containing CLOB fields. These are mapped to Text columns on a SQL Server database. The operation is generating an error of "The conversion of NCLOB to CLOB is unsupported". This message is coming from the SQL Server JDBC driver.

       

      It appears that the CLOB is being handled as an NCLOB somewhere internal to Teiid. This assumption comes from the fact that the client writer is dealing in CLOBs and it does not seem to make a difference how the column is actually defined on the database. I have not been able to generate, or even find reference, to the error message working only with SQL Server JDBC directly from the client.

       

      The client writer is not my actual use, it is just to demonstrate the problem. It uses a FileReader and setClob to try to store a file from disk.

       

      The platform is Teiid 8.3 Final and Teiid JDBC 8.4 Beta.

       

      Thanks in advance for any ideas.

        • 1. Re: Writing a CLOB through Teiid
          Steven Hawkins Master

          Our ClobType implements both Clob and NClob as there is functionally no difference to us.  However if SQLServer is doing a proactive instanceof check somewhere in their driver code, this may be a problem.  Can you provide a sample stacktrace and source query where this is occurring?

           

          Steve

          • 2. Re: Writing a CLOB through Teiid
            Brian Dudley Newbie

            The stack trace is below. Not sure what you mean, exactly, by source query. The stack trace contains the Insert statement. The operation is being done against this table definition.

             

             

            Create Table DocumentStore(
            DocumentID int Not Null primary key,
            Document text null,
            DocGUID uniqueidentifier Not Null Default NewID()
            )

             

            Trace:

            org.teiid.jdbc.TeiidSQLException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 FSTestRemote: 0 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [44, 'org.teiid.core.types.ClobImpl@2dd0b0f3'] SQL: INSERT INTO "dbo"."DocumentStore" (DocumentID, Document) VALUES (?, ?)]
            at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)
            at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)
            at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:660)
            at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:63)
            at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:516)
            at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:130)
            at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:37)
            at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:75)
            at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(SocketServerInstanceImpl.java:222)
            at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:257)
            at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
            at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
            at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
            at java.lang.reflect.Method.invoke(Unknown Source)
            at org.teiid.net.socket.SocketServerConnectionFactory$ShutdownHandler.invoke(SocketServerConnectionFactory.java:102)
            at com.sun.proxy.$Proxy1.read(Unknown Source)
            at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:356)
            at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:525)
            at org.teiid.jdbc.PreparedStatementImpl.executeUpdate(PreparedStatementImpl.java:264)
            at cg.test.filestream.ReadWriteFileStreamBlob.main(ReadWriteFileStreamBlob.java:70)
            Caused by: org.teiid.core.TeiidProcessingException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 FSTestRemote: 0 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [44, 'org.teiid.core.types.ClobImpl@2dd0b0f3'] SQL: INSERT INTO "dbo"."DocumentStore" (DocumentID, Document) VALUES (?, ?)]
            at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:549)
            at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:322)
            at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:306)
            at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:279)
            at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:149)
            at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:144)
            at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:110)
            at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:153)
            at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:400)
            at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:296)
            at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
            at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:224)
            at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:264)
            at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:123)
            at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:298)
            at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
            at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
            at java.lang.Thread.run(Unknown Source)
            Caused by: org.teiid.core.TeiidException: 0 Remote org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [44, 'org.teiid.core.types.ClobImpl@2dd0b0f3'] SQL: INSERT INTO "dbo"."DocumentStore" (DocumentID, Document) VALUES (?, ?)]
            at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:242)
            at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:76)
            at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:290)
            at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:257)
            at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:464)
            at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:178)
            at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:175)
            at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
            at java.util.concurrent.FutureTask.run(Unknown Source)
            at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:120)
            ... 6 more
            Caused by: java.sql.SQLException: Remote com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from NCLOB to CLOB is unsupported.
            at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
            at com.microsoft.sqlserver.jdbc.DataTypes.throwConversionError(DataTypes.java:1117)
            at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObject(SQLServerPreparedStatement.java:991)
            at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObject(SQLServerPreparedStatement.java:944)
            at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.setObject(WrappedPreparedStatement.java:957)
            at org.teiid.translator.jdbc.JDBCExecutionFactory.bindValue(JDBCExecutionFactory.java:838)
            at org.teiid.translator.jdbc.sybase.BaseSybaseExecutionFactory.bindValue(BaseSybaseExecutionFactory.java:137)
            at org.teiid.translator.jdbc.JDBCBaseExecution.bind(JDBCBaseExecution.java:102)
            at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:212)
            ... 15 more

             

            • 3. Re: Writing a CLOB through Teiid
              Steven Hawkins Master

              It looks highly likely that they are doing just that.  This may be driver version dependent.  Which driver are you using?  It may also not occur if you use JTDS. 

               

              Steve

              • 4. Re: Writing a CLOB through Teiid
                Brian Dudley Newbie

                Thanks for the quick reply...

                 

                I am using Microsoft JDBC Driver 4.0 for SQL Server.

                 

                I'll try JTDS and see what happens.

                 

                Thanks

                • 5. Re: Writing a CLOB through Teiid
                  Steven Hawkins Master

                  I've logged https://issues.jboss.org/browse/TEIID-2493 to correct the default behavior with the SQL Server driver.

                   

                  Thanks,

                  Steve