Save the SQL Cursor - IBM AS400

This is a discussion on Save the SQL Cursor - IBM AS400 ; Hello, I created a function which receive in parameter a SQL request and an order (parameter) of reading (the First, Following, the Last one). The function sends back a pointer with the data in memory. Everything works OK, only I ...

+ Reply to Thread
Results 1 to 6 of 6

Thread: Save the SQL Cursor

  1. Save the SQL Cursor

    Hello,
    I created a function which receive in parameter a SQL request and an
    order (parameter) of reading (the First, Following, the Last one). The
    function sends back a pointer with the data in memory.
    Everything works OK, only I would like to use the same function, in
    the same job, with another SQL request. Indeed on, I shall like
    keeping the position of every cursor to continue to make readings on
    the other cursor.
    I have to duplicate the functions ?
    Or maybe there is a means of execute my function in different
    activation groups ?

    Example :

    ReadFile('Select * from fileA','First');
    ReadFile('Select * from fileA','Next');
    ReadFile('Select * from fileB','First');
    ReadFile('Select * from fileA','Next');

    Do you have an idea ?


  2. Re: Save the SQL Cursor

    On Sep 17, 5:50*pm, terang...@hotmail.fr wrote:
    > Hello,
    > I created a function which receive in parameter a SQL request *and an
    > order (parameter) of reading (the First, Following, the Last one). The
    > function sends back a pointer with the data in memory.
    > Everything works OK, only I would like to use the same function, in
    > the same job, with another SQL request. Indeed on, I shall like
    > keeping the position of every cursor to continue to make readings on
    > the other cursor.
    > I have to duplicate the functions ?
    > Or maybe there is a means of execute my function in different
    > activation groups ?
    >
    > Example :
    >
    > ReadFile('Select * from fileA','First');
    > ReadFile('Select * from fileA','Next');
    > ReadFile('Select * from fileB','First');
    > ReadFile('Select * from fileA','Next');
    >
    > Do you have an idea ?


    I dont think you could use different activation groups, how do you
    define which one you want every time you call the function?
    Perhaps your function can allocate arrays of sql statements & any
    other things it needs to keep inmemory, then every call to the
    function would compare the sql with the array & either find it & re-
    use it & the associated saved stuff in other arrays, or open a new
    cursor & save the data. You may need a close function I suppose
    somewhere?
    I dont know if this will work at all.

    Jonathan

  3. Re: Save the SQL Cursor

    I would like to manage to make this, without losing my cursor SQL on
    the first file1 :

    f1 = OpenFile('Select * from file1 where ....');
    Ptr1 = ReadFile(f1,'First');
    Ptr1 = ReadFile(f1,'Next');
    ....
    f2 = OpenFile('Select columnA, columnB from file2 where ....');
    Ptr2 = ReadFile(f2,'First');
    ....
    Ptr1 = ReadFile(f1,'Next'); <---------------
    ....
    Close(f1);
    Close(f2);



  4. Re: Save the SQL Cursor

    Perhaps the SQL Call Level Interface is what you need. It works similar to ODBC. It provides several
    service to do each and everything on your own, e.g.:

    Allocate environment handle SQLAllocEnv (%addr(henv))
    Allocate connection handle SQLAllocConnect (henv: %addr(hdbc))
    Connect to a database SQLConnect (hdbc: ...)
    Get a statement handle SQLAllocStmt (hdbc: %addr(hstmt))
    Execute a statement SQLExecDirect (hstmt: create: SQL_NTS)
    Release a statement SQLFreeStmt (hstmt: SQL_DROP)
    Disconnect from a database SQLDisconnect (hdbc)
    Free connection handle SQLFreeConnect (hdbc)
    Free environment handle SQLFreeEnv (henv)
    etc.

    You may use an array in order to store the state of each cursor. The index of the array entry could be
    the handle you pass back and forth between your procedure calls.

    Feel free to go to "http://www.tools400.de/English/Freeware/Snippets/snippets.html" in order to
    download a sample program to start from.

    Thomas.


    teranga30@hotmail.fr schrieb:
    > Hello,
    > I created a function which receive in parameter a SQL request and an
    > order (parameter) of reading (the First, Following, the Last one). The
    > function sends back a pointer with the data in memory.
    > Everything works OK, only I would like to use the same function, in
    > the same job, with another SQL request. Indeed on, I shall like
    > keeping the position of every cursor to continue to make readings on
    > the other cursor.
    > I have to duplicate the functions ?
    > Or maybe there is a means of execute my function in different
    > activation groups ?
    >
    > Example :
    >
    > ReadFile('Select * from fileA','First');
    > ReadFile('Select * from fileA','Next');
    > ReadFile('Select * from fileB','First');
    > ReadFile('Select * from fileA','Next');
    >
    > Do you have an idea ?
    >


  5. Re: Save the SQL Cursor

    Hello,
    Thank you for the answers.

    Given that there is no API to create the my own groups of activation
    dynamicly and call my function in a given group, the solutions which
    offer to me are:

    - Code all my SQL accesses in CLI with a SQLAllocEnv (Allocate
    environment handle)
    - Create a function (*caller) which will call several functions with
    named activation groups which will call quite in turn the function of
    reading (open / Read / closed) *CALLER.
    - to manage in my function of reading (open / Read / closed) several
    SQL cursors.

    The first case presents the advantage that it works without limit of
    open, but is complicated to code and to maintain for a customer.
    The second and third case, simpler, oblige me to define right now how
    many simultaneous OPEN will be possible by my function.

    I think that I am going to keep the third solution.
    Thank you for your contribution

  6. Re: Save the SQL Cursor

    On Sep 19, 6:26*pm, terang...@hotmail.fr wrote:
    > Hello,
    > Thank you for the answers.
    >
    > Given that there is no API to create the my own groups of activation
    > dynamicly and call my function in a given group, the solutions which
    > offer to me are:
    >
    > - Code all my SQL accesses in CLI with a SQLAllocEnv (Allocate
    > environment handle)
    > - Create a function (*caller) which will call several functions with
    > named activation groups which will call quite in turn the function of
    > reading (open / Read / closed) *CALLER.
    > - to manage in my function of reading (open / Read / closed) several
    > SQL cursors.
    >
    > The first case presents the advantage that it works without limit of
    > open, but is complicated to code and to maintain for a customer.
    > The second and third case, simpler, oblige me to define right now how
    > many simultaneous OPEN will be possible by my function.
    >
    > I think that I am going to keep the third solution.
    > Thank you for your contribution


    I may have been wrong about setting up activation groups:
    You could supply the source code for a program say just a simple CL
    which takes n parms & passes to the next program. Then you issue the
    crtbndcl command against this source with a target of qtemp/act0000001
    & an activation group of act0000001. The cursor program is set to use
    *caller for the activation group. Then cal the program you just
    created & generate a new activation group. You can call variable
    program names from RPG so thats not too difficult. Provided you start
    the activcation group & program name with a letter you are OK so this
    gives a few million groups, not sure what system resources & limits
    this would take. You might need to pass an additional parameter to
    give an option to close the group instead of rclactgrp.

    HTH
    Jonathan.

+ Reply to Thread