Plex 2E

  • 1.  .Net Runtime calling Stored Procs  not return a integer Value

    Posted Feb 06, 2016 09:50 AM

    Why does the .NET runtime via dispatcher not return a integer Value RETURN (Transact-SQL)

     

    In the plex  samples example DBfunctions model

     

    usp_GetAPIMessage

    #BEGIN

    IF EXISTS (SELECT name FROM sysobjects WHERE name = 'usp_GetAPIMessage' AND type = 'P')

    BEGIN TRY

        DROP PROCEDURE usp_GetAPIMessage

    END TRY

    BEGIN CATCH

        PRINT 'usp_GetAPIMessage exists but cant be dropped' -- This will appear in the Plex Log

        RETURN

    END CATCH

    #END

    #BEGIN

    -- =============================================

    -- Author: <George Jeffcock>

    -- Create date: <2 Feb 2016>

    -- Description: <Get API Message by ID>

    -- =============================================

    CREATE PROCEDURE [dbo].[usp_GetAPIMessage]

    @MessageID varchar(7)

    ,@ReturnInteger numeric(4,0) = 0 Output

    ,@MessageText256 varchar(256) = '' Output

    ,@MessageSeverity numeric(1,0) = 0 Output

    ,@MessageReplaceMarkers numeric(1,0) = 0 Output

    AS

    SET NOCOUNT ON

      IF @MessageID = ''

          BEGIN

          SET @ReturnInteger = 99

          RETURN 99

          END

      ELSE

          BEGIN

          SELECT TOP 1 @MessageID = MessageID, @MessageText256 = MessageText256, @MessageSeverity = MessageSeverity, @MessageReplaceMarkers = MessageReplaceMarkers FROM APIMSG WHERE MessageID = @MessageID ORDER BY MessageID

          DECLARE @PreserveROWCOUNT INT = @@ROWCOUNT

          END

      IF @@ERROR <> 0

         BEGIN

         SET @ReturnInteger = -1

         RETURN -1

         END

      ELSE

         BEGIN

         IF @PreserveROWCOUNT = 0

              BEGIN

              SET @ReturnInteger = 100

              RETURN 100

              END

        ELSE

             BEGIN

             SET @ReturnInteger = 0

             RETURN 0

             END

       END

    #END

     

     

    ODBC, for C++ client-based ODBC data access.   WORKS

    ODBCWinNT, for Windows C++ server data access with Microsoft SQL Server   WORKS

     

    but ODBC for C# OLE-DB data access  no integer is returned........ can people confirm..how do people error handle in .NET



  • 2.  Re: .Net Runtime calling Stored Procs  not return a integer Value

    Posted Feb 06, 2016 02:33 PM

    Calling Function of type "Database" is called via CallManager. CallManager will set  *Returned status to "ERR"  if an Exception is catched during execution of the SP. SP can use THROW statement instead of the RETURN -100.



  • 3.  Re: .Net Runtime calling Stored Procs  not return a integer Value

    Posted Feb 07, 2016 04:41 AM

    Thanks Lorenz (Shame envrionments dont work the same)

     

    But does the C++ respect the THROW statement or do you have to use RETURN -100 when

    ODBC, for C++ client-based ODBC data access. 

    ODBCWinNT, for Windows C++ server data access with Microsoft SQL Server

     

    And for

    ODBC for C# OLE-DB data access

    you can use Throw Statment.

     

    Trying to avoid writing twice the SP, quick test would answer this but on the off chance you know off the top of your head.



  • 4.  Re: .Net Runtime calling Stored Procs  not return a integer Value

    Posted Feb 07, 2016 04:47 AM

    What I liked was that I could use 'CallManager will set  *Returned status to "ERR" '  as the Call Status. An "exception" I saw as a a complete failure of the SP ie not found. So what the Returned status to "ERR" is set to ERR I set the *Call Status to ERROR and therefore reuse standard plex error handling.

     

    If the INTEGER is returned in the *Returned status (explicitly set by the developer like in the sample model) then you could code for INF, IEX etc which are not exceptions..



  • 5.  Re: .Net Runtime calling Stored Procs  not return a integer Value

    Posted Feb 07, 2016 04:52 AM

    i think CA need to answer this issue why does the NET environment not respect RETURN (Transact-SQL) as does the other environments as it is standard to return integer and is inbuilt into SP and you dont need to add it to your output etc



  • 6.  Re: .Net Runtime calling Stored Procs  not return a integer Value

    Posted Feb 07, 2016 04:57 AM

    -- =============================================

    -- Author: <George Jeffcock>

    -- Create date: <2 Feb 2016>

    -- Description: <Get API Message by ID>

    -- =============================================

    CREATE PROCEDURE [dbo].[usp_GetAPIMessage]

    @MessageID varchar(7)

    ,@ReturnInteger numeric(4,0) = 0 Output

    ,@MessageText256 varchar(256) = '' Output

    ,@MessageSeverity numeric(1,0) = 0 Output

    ,@MessageReplaceMarkers numeric(1,0) = 0 Output

    AS

      SET NOCOUNT ON

    IF @MessageID = ''

         BEGIN

         SET@ReturnInteger = 99

         RETURN 99

         END

    ELSE

        BEGIN

        SELECT TOP 1 @MessageID = MessageID, @MessageText256 = MessageText256, @MessageSeverity = MessageSeverity, @MessageReplaceMarkers = MessageReplaceMarkers FROM APIMSG WHERE MessageID = @MessageID ORDER BY MessageID

        DECLARE @PreserveROWCOUNT INT = @@ROWCOUNT

        END

      IF @@ERROR <> 0

        BEGIN

                      SET @ReturnInteger = -1

                      RETURN -1

        END

      ELSE

        BEGIN

      IF @PreserveROWCOUNT = 0

        BEGIN

     

                     SET @ReturnInteger = 100

                     RETURN 100

    END

      ELSE

    BEGIN

                     SET  @ReturnInteger = 0

                     RETURN 0

    END

        BEGIN

    #END

     

     

    so in the end I kinda doing it twice to satisfy both enviromnents..work in progress moving to NET

     

    Shame looks a little amateur..with the inbuilt Returns integer and our ca plex one @ReturnInteger

    Capture.PNG



  • 7.  Re: .Net Runtime calling Stored Procs  not return a integer Value
    Best Answer

    Posted Feb 07, 2016 05:44 AM

    Had a look at the .NET runtime for SQL client support. There is no code reading the RETURN value. In .NET this involves definition of a parameter with Direction "ReturnValue"  on the statement. If you can live with the *Returned Status set to "ERR" when the ReturnValue is < 0 this would be a simple change.

     

    I am not familiar with the C++ implementation. How do you get the ReturnValue (-100) in your AD ? Is the *Returned staus set to the ReturnValue ?

     

    Best

    Lorenz



  • 8.  Re: .Net Runtime calling Stored Procs  not return a integer Value

    Posted Feb 07, 2016 03:49 PM

    Yep in the WinC and WincNT variant which stating the obvious came before .NET, the *Returned status  is set to the RETURN (Transact-SQL) Integer with no extra coding by the plex developer and I would have assumed the NET version would be in keeping with what came before it, And consider also both WinC and NET are invoking the same Stored Procedure. I consider this issue confirmed it works differently as my findings had found.



  • 9.  Re: .Net Runtime calling Stored Procs  not return a integer Value

    Posted Feb 08, 2016 02:25 AM

    The handling of the RETURN Integer should be consistent throughout Plex Variants. I think in this case we need a runtime configuration flag that allows for returning the RETURN integer in the *Returned status field (Some newer applications will just rely on the *Returned status being "ERR" or blank and pass additional error information as regular Plex parameters).

     

    Unfortunately there is no way to safely map the RETURN value to "ERR" or blank. Even returning zero in case of a successful operation is just a convention.

     

    Best

    Lorenz



  • 10.  Re: .Net Runtime calling Stored Procs  not return a integer Value

    Posted Feb 08, 2016 02:43 AM

    I think we both know what will really happen.