Load Ingest Data
Results 1 to 7 of 7

Thread: OpenVMS - Returning a Rowset from a Procedure

  1. #1
    Lyle A Bryant is offline Junior Member
    Join Date
    Jul 2008
    Posts
    2
    Rep Power
    0

    OpenVMS - Returning a Rowset from a Procedure

    Our application is written in .NET, using Attunity ADO.NET 2.0 client to connect to OpenVMS.

    We are currently looking to invoke BASIC procedures on OpenVMS that will be handling a variety of existing business logic implementations.

    The question: How do we return a rowset from an OpenVMS procedure?

    This will allow us to access the data within a DataSet object. So far are experience in calling procedures is all based on a single row returned from the Math_Simple sample function.

    Thanks in advance,

    Lyle A Bryant

  2. #2
    Boaz Newman is offline Support
    Join Date
    Aug 2006
    Posts
    30
    Rep Power
    0
    Hello Lyle

    The AIS procedure driver (not adapter) can be used to call a 3GL function (e.g. a Basic function) that is exposed as a stored procedure and present the result as a rowset. This is internally done by repeated calls to the function, and it requires that one of the procedure output paramaters has a property named EOS_VALUE (for End Of Stream indication).

    For example:

    HTML Code:
    <?xml version="1.0" encoding="UTF-8"?>
    <procedurefileName="READ_EMPLOYEE"name="READ_EMP">
        <dbCommand>LANGUAGE=COB</dbCommand>
        <fields>
            <field datatype="string"name="EMP_CODE"size="10">
                <dbCommand>MECHANISM=REFERENCE; ORDER=1</dbCommand>
            </field>
            <field datatype="string"name="EMP_FINAME"size="10">
                <dbCommand>MECHANISM=REFERENCE; ORDER=2</dbCommand>
            </field>
            <field datatype="string"name="EMP_FANAME"size="10">
                <dbCommand>MECHANISM=REFERENCE; ORDER=3</dbCommand>
            </field>
            <field datatype="int4"name="EOS">
                <dbCommand>MECHANISM=VALUE; ORDER=0 EOS_VALUE=0</dbCommand>
            </field>
        </fields>
        <parameters/>
    </procedure>

    Here the return value of the function (specified by OREDER=0) is used as the EOS flag but any other parameter can be used.
    In this example, the procedure will be called repeatedly until the function returns a zero value, any other value returned will cause another call to be initiated.

    Here is the BASIC code that implements the function:
    FUNCTION LONG READ_EMP(STRING EMP_CODE, STRING EMP_FINAME, STRING EMP_FANAME)
    RECORD EMPLOYEE
    STRING CODE=8
    STRING FIRST_NAME=15
    STRING FAMILY_NAME=20
    STRING PROFESSION_CODE=3
    STRING ADDRESS=30
    WORD CHILD_COUNTER
    STRING CHILDREN(1 TO 10)=15
    STRING FILLER1=284
    END RECORD
    MAP(EMP_MAP1) EMPLOYEE EMP1
    COMMON (EMP_COM1) LONG CHN, LONG OPEN_DONE
    DECLARE LONG EOS !END OF RECORD STREAM
    CHN = 1
    IF (OPEN_DONE <> 1) THEN
    OPEN "EMPLOYEES.DAT" FOR INPUT AS #CHN, &
    ACCESS READ, &
    MAP EMP_MAP1, &
    ORGANIZATION VIRTUAL
    OPEN_DONE = 1
    END IF
    EOS = 1
    WHEN ERROR IN
    GET #CHN
    USE
    IF ERR = 11 THEN !EOF
    EOS = 0
    END IF
    END WHEN
    IF EOS = 1 THEN
    EMP_CODE = EMP1::CODE
    EMP_FINAME = EMP1::FIRST_NAME
    EMP_FANAME = EMP1::FAMILY_NAME
    READ_EMP = 1
    ELSE
    CLOSE #CHN
    READ_EMP = 0
    END IF
    END FUNCTION
    Last edited by Boaz Newman; 07-09-2008 at 08:45 AM.
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  3. #3
    Join Date
    Sep 2006
    Posts
    233
    Rep Power
    10

    XML document

    If the basic function does not return an unlimited rowset but instead returns something out of a structure (e.g., some array of records) then you should also consider mapping the Basic function using the LegacyPlug adapter which essentially presents the Basic function as a stored procedure in ADO.NET that gets as input an XML document and returns an XML document for results
    By Dror Harari

    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  4. #4
    Lyle A Bryant is offline Junior Member
    Join Date
    Jul 2008
    Posts
    2
    Rep Power
    0

    EOS_VALUE Pattern Clarification Questions

    We would like to clarify a few points regarding the previously posted sample code provided for mapping the return value of a procedure for the VMS platform.

    1. Syntax Clarification

    <field datatype="int4" name="EOS">
    <dbCommand>
    MECHANISM=VALUE; ORDER=0 EOS_VALUE=0
    </dbCommand>
    </field>

    In the provided sample, there is no ; (semi colon) between
    ORDER=0 and the EOS_VALUE=0, or should there be a ; between them?

    MECHANISM=VALUE; ORDER=0; EOS_VALUE=0

    2. What is the expected behavior of the procedure driver if no return code parameter is defined? In essense, having no ORDER=0 field definition in the bindings? Will the procedure driver only call once? Will it call infinite? Will it depend on the return value of the function even if a binding isn't defined?

    3. Is there a way in .NET that we can "examine" this return value parameter to verify the value has been set to 0? Or is there an alternative way we can confirm the return value is being correctly set, like a log, or performance counter from Attunity?

    4. Is the VMS procedure being invoked required to be a function? Or could a sub be used for procedures that only return a single row?

    5. If a VMS procedure is only returning a single row, does the EOS flag need to return a non-zero value first, and then the second call sets it to zero to complete the call correctly?

    Thanks in advance,

    Lyle A Bryant

  5. #5
    Boaz Newman is offline Support
    Join Date
    Aug 2006
    Posts
    30
    Rep Power
    0

    EOS_VALUE Pattern Clarification Questions

    Hello and Sorry for the late reply

    1) You are right, a semi colon should be used between the items.

    2) The return code definition is not mandatory, the EOS value definition is, it can be used on any of the other OUTPUT parameters. For example you can define the field EMP_CODE with a EOS_VALUE="XXXXXXXXXX" and change your BASIC CODE accordingly:
    ELSE
    CLOSE #CHN
    EMP_CODE = "XXXXXXXXXX"
    END IF
    END FUNCTION

    3) There is no way to examine the value of the field with the EOS attribute once it got the end of stream value, simply because no records are returned to the calling interface (.NET in your case).

    4) It could be a sub or a function with one or more rows returned.

    5) Yes, the mechanism of EOS must be used even if only one row is returned.
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  6. #6
    Hein is offline Senior Member
    Join Date
    Dec 2007
    Location
    Nashua, NH - USA.
    Posts
    161
    Rep Power
    12

    Another example calling LIB$SPAWN this time.

    Thanks Boaz and Dror for this example.

    I created an other one calling LIB$SPAWN and returning the result output as a rowset.
    Kinda silly, kinda scary. Best not run NAV under SYSTEM with this in place!

    Here is the procedure definition I used

    Code:
          <procedure fileName="NAV_HELPERS" name="SPAWN">        <dbCommand>LANGUAGE=C</dbCommand>
            <fields>
              <field name="result_status" datatype="int4" >
                <dbCommand>ORDER=1</dbCommand>
              </field>
              <field  name="output_line" datatype="string" size="132">
                <dbCommand>ORDER=2; MECHANISM=DESCRIPTOR</dbCommand>
              </field>
              <field  name="EOS"  datatype="INT4">
                <dbCommand>ORDER=0; MECHANISM=VALUE; EOS_VALUE=0</dbCommand>
              </field>
            </fields>
            <parameters>
              <field  name="command_string" datatype="string" size="80">
                <dbCommand>ORDER=3; MECHANISM=DESCRIPTOR</dbCommand>
              </field>
            </parameters>
          </procedure>

    And here is the example procedure coded in BASIC ( I have a MACRO flavor for it as well)

    Code:
    1
    !
    ! SPAWN
    !
    ! Helper subroutine to create make a LIB$SPAWN call providing NO input, just a command
    ! The return is a rowset. This routine will be called until it signals EOS. (End Of Stream)
    !
    ! This subroutine is expected to be called as a PROCEDURE DATASOURCE in the
    ! AIS Navigator environment.
    !
    ! Output Arguments (Single row result set)
    !
    !   result_status       - Longword to receive call status or line number. Low bit set if succesful
    !   output_line         - line from command output
    !
    !
    ! Input Arguments (Parameters)
    !   command_string        - String with command to execute.  NO interactive input or output
    !
    
    
            FUNCTION LONG spawn ( LONG   result_status, STRING output_line, STRING command_string )
    
    
            OPTION   TYPE = EXPLICIT,SIZE = INTEGER LONG, CONSTANT TYPE = INTEGER
    
    
           %INCLUDE "$IODEF" %FROM %LIBRARY "SYS$LIBRARY:BASIC$STARLET.TLB"
    
    
            EXTERNAL LONG FUNCTION  LIB$SPAWN, SYS$CREMBX, SYS$DASSGN, SYS$QIOW, SYS$GETMSG, STR$COPY_R
    
    
            DECLARE STRING  mailbox_name
            DECLARE WORD    retlen
            DECLARE LONG    s
            MAP (volatile)  WORD iosb_status, iosb_bytecount, LONG iosb_pid,  ! MUST be adjacent. Handy IOSB layout &
                            LONG completion_status, line_number, WORD mailbox_channel
    
    
            MAP (fixed_strings) STRING spawn_output = 256
            MAP (fixed_strings) BYTE spawn_output_hack   ! Couldn't get LOC to work on the fixed string
    
    
            IF (0 = mailbox_channel) THEN ! First call
                line_number = 0
                mailbox_name = "NAV_HELPER_TMPMBX"
                s = SYS$CREMBX (, mailbox_channel, !maxmsg! ,!bufquo! ,!promsk! ,!acmode!, mailbox_name,,)
                GOTO return_error UNLESS (1 AND s)
    
    
                s = LIB$SPAWN( command_string,"NL:", mailbox_name, 1,,,,completion_status)   ! NOwait
                GOTO return_error UNLESS (1 AND s)
    
    
            END IF
    !
    ! Spawned process is running in the background, get a line from its SYS$OUTPUT, our mailbox
    !
            s = SYS$QIOW ( !efn!, mailbox_channel BY VALUE, IO$_READVBLK BY VALUE, iosb_status, &
                           !astadr!, !astprm!, spawn_output_hack, 256 BY VALUE,!p3!,!p4!,!p5!,!p6!)
    
    
            s = iosb_status UNLESS (1 <> (1 AND s))
    
    
     return_error:
            IF (1 <> (1 AND s)) THEN
                spawn = 0 ! End of Stream
                result_status = s  ! Getting text for test/debug only. NAVIGATOR will not see this.
                s = SYS$GETMSG( result_status BY VALUE, retlen, spawn_output,,)
                s = STR$COPY_R ( output_line, retlen, spawn_output_hack )
                IF (mailbox_channel <> 0) THEN s = SYS$DASSGN(mailbox_channel BY VALUE) END IF
                mailbox_channel = 0
            ELSE
                spawn = 1 ! Please call again, there is more to say.
                line_number = line_number + 1
                result_status = line_number
                s = STR$COPY_R ( output_line, iosb_bytecount, spawn_output_hack )
            END IF
            END FUNCTION


    And to LINK the shareable you would need something like:

    Code:
    $ ! NAV_HELPERS_LINK.COM Hein van den Heuvel, Attunity, June 2012
    $ LINK/SHARE=NAV_HELPERS_SHAREABLE.EXE'P1' NAV_HELPERS_CODE, SYS$INPUT:/OPT
    SYMBOL_VECTOR = (SPAWN         = PROCEDURE)
    With that in place, define a logical name NAV_HELPERS visible in the NAV_UTIL process to point to the physical file.
    Or... Just copy it to SYS$SHARE (aka SYS$LIBRARY) as NAV_HELPERS.EXE


    Here is how that works for me:

    Code:
    C:\Program Files\Attunity\Server\Tmp>nav_util execute nav_helpers
    
    
    NavSQL > select substr(output_line,1,80) "hello" from SPAWN ('show system');
    
    
    
    
    hello
    
    
    OpenVMS V8.4  on node BUNDY    5-JUL-2012 16:00:35.34   Uptime  0 05:32:52
      Pid    Process Name    State  Pri      I/O       CPU       Page flts  Pages
    00000081 SWAPPER         HIB     16        0   0 00:00:03.07         0      4
    00000084 LANACP          HIB     14       67   0 00:00:00.70       110    136
    00000086 IPCACP          HIB     10        9   0 00:00:00.11        37     51
    00000087 ERRFMT          HIB      8      339   0 00:00:00.38       113    134
    00000089 OPCOM           HIB      7      117   0 00:00:00.13       103     53
    :

    And finally, I prefer to test this stuff in isolation first. Here is my test code:

    Code:
     1      OPTION TYPE = EXPLICIT, SIZE = INTEGER LONG, CONSTANT TYPE = INTEGER
     !      Have fun, Hein van den Heuvel, Valbonne Xmas 1986
            On error go to hell!
    
    
     DECLARE LONG s, result_long, result_status
     DECLARE STRING command_string
     EXTERNAL LONG FUNCTION SPAWN
     MAP (x) STRING output_line = 80
    
    
    
      WHILE 1+1=2
       LINPUT "Command "; command_string
       IF "" = command_string THEN GOTO 2
       s = 1
       WHILE (s <> 0) ! End of Stream
         s = SPAWN ( result_long, output_line, command_string )
         PRINT s, result_long, output_line
       NEXT
     NEXT
    
    
    hell:  PRINT ERT$(ERR) UNLESS ERR = 11
            RESUME 2
     2      END


    Hope this helps someone out there some day!
    Regards,
    Hein

  7. #7
    Join Date
    Sep 2006
    Posts
    233
    Rep Power
    10
    Hein, very cool, very scary indeed. That's playing with fire.
    By Dror Harari

    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •