6 Replies Latest reply on Feb 4, 2016 2:59 PM by Steven Hawkins

    Stored procedure in view model from SQL server 2000

    Mirco Marchitiello Newbie

      Hi,

      we're facing some issues on some procedures developed on a SQL server 2000 when we create a view model reading the procedures from a source model.

      The source model is connected with the SQL server 2000 via jdts1.3.1 driver and executing the stored procedures on it they work fine.

      Then we have created a view model on those procedures but those executions lead to an error like

       

      TEIID10072 Invalid integer format in String: Villa, Ground Floor - Tel. 9389

       

      The procedure on SQL server 2000 is very simple

       

      ALTER PROCEDURE [dbo].[sp_CGB_GetMeetingRoomByIDTest]

          @RoomID        int

      AS

       

      SET NOCOUNT ON

       

      SELECT    RoomID,

              RoomCapacity,

              RoomLocation

      FROM    CGB_MEETINGROOMS

      WHERE    RoomID = @RoomID

       

      while on the view model is even simpler:

       

      BEGIN

          EXEC testProcedure.sp_CGB_GetMeetingRoomByIDTest(RoomID => testProcedureView.sp_CGB_GetMeetingRoomByIDTest.RoomID);

      END

       

      The Procedure ResultSet on both the procedures is

      RoomID :  int

      RoomCapacity : int

      RoomLocation : string

       

      Executing the procedure on the source mode,  EXEC testProcedure.sp_CGB_GetMeetingRoomByIDTest(1) the result is

       

      RoomID   RoomCapacity  RoomLocation                                  

      -------------------------------------------------------------------------------

      1        16        Villa, Ground Floor - Tel. 9389               

       

      while executing from the view model we get the error

       

      EIID10072 Invalid integer format in String: Villa, Ground Floor - Tel. 9389

       

      If we exclude from the procedures the field "RoomLocation" it works fine, so it seems that the string data type "RoomLocation" is somehow interpreted as an integer after running the source model procedure

       

      We've tried to replicate the same scenario on SQL server 2008 and there it works fine

       

      thanks,

      Mirco

        • 1. Re: Stored procedure in view model from SQL server 2000
          Steven Hawkins Master

          Can you attach a full stack trace and ideally the vdb so that I can confirm exactly where the conversion error is occurring?

           

          > We've tried to replicate the same scenario on SQL server 2008 and there it works fine

           

          So the exact same scenarios with the same driver against SQL server 2008 works as expected?  Ideally this would indicate that this is generally not a Teiid issue.  However from just the description there's nothing to indicate why this would occur with a virtual procedure, but not when executing the source procedure.

          • 2. Re: Stored procedure in view model from SQL server 2000
            Mirco Marchitiello Newbie

            Hi Steven,

            here's the VBD attached and the stack trace

             

            I can confirm you that the same procedure, with the same table and data on SQL Server 2008 work fine. We are currently using Teiid 8.13

             

            Thanks,

            Mirco

             

             

            2016-02-04 11:59:16,769 WARN  [org.teiid.CONNECTOR] (Worker93_QueryProcessorQueue1047) V/FcQRQWrBI2 TEIID30004 Connector returned a 0 row non-last batch: CASALE_GROUP.

            2016-02-04 11:59:16,769 WARN  [org.teiid.CONNECTOR] (Worker93_QueryProcessorQueue1049) V/FcQRQWrBI2 Connector worker process failed for atomic-request=V/FcQRQWrBI2.7.1.141: org.teiid.core.types.TransformationException: TEIID10072 Invalid integer format in String: Villa, Ground Floor - Tel. 9389

                at org.teiid.core.types.basic.StringToIntegerTransform.transformDirect(StringToIntegerTransform.java:43)

                at org.teiid.core.types.Transform.transform(Transform.java:47)

                at org.teiid.core.types.DataTypeManager.transformValue(DataTypeManager.java:942)

                at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.correctTypes(ConnectorWorkItem.java:572)

                at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.handleBatch(ConnectorWorkItem.java:435)

                at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.more(ConnectorWorkItem.java:226)

                at sun.reflect.GeneratedMethodAccessor102.invoke(Unknown Source)

                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

                at java.lang.reflect.Method.invoke(Method.java:497)

                at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)

                at com.sun.proxy.$Proxy57.more(Unknown Source)

                at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:309)

                at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)

                at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)

                at java.util.concurrent.FutureTask.run(FutureTask.java:266)

                at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)

                at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)

                at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)

                at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)

                at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

                at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

                at java.lang.Thread.run(Thread.java:745)

             

            2016-02-04 11:59:16,785 WARN  [org.teiid.PROCESSOR] (Worker92_QueryProcessorQueue1050) V/FcQRQWrBI2 TEIID30020 Processing exception for request V/FcQRQWrBI2.7 'TEIID10072 Invalid integer format in String: Villa, Ground Floor - Tel. 9389'. Originally TransformationException StringToIntegerTransform.java:43. Enable more detailed logging to see the entire stacktrace.

            • 4. Re: Stored procedure in view model from SQL server 2000
              Steven Hawkins Master

              There is an issue here when the source procedure has both a return value and a result set.  The return value is not retrieved with the source query with just your source execution, but is when called through the virtual procedure and this exception results.

               

              Can you log an issue for this: https://issues.jboss.org/projects/TEIID/summary

              • 5. Re: Stored procedure in view model from SQL server 2000
                Steven Hawkins Master

                > Try setting the database version property [1] as a translator override.

                 

                He's just using the JDBC simple in this case, so the database version doesn't come into play.