Downloading a result set from a stored procedure call

You can download a result set from a stored procedure call. For example, you might currently have a download_cursor for the following table:

CREATE TABLE MyTable (
    pk INTEGER PRIMARY KEY NOT NULL,
    col1 VARCHAR(100) NOT NULL,
    col2 VARCHAR(20) NOT NULL
)

The download_cursor table script might look as follows:

SELECT pk, col1, col2
   FROM MyTable
      WHERE last_modified >= {ml s.last_table_download}
      AND employee = {ml s.username}

If you want your downloads to MyTable to use more sophisticated business logic, you can now create your script as follows, where DownloadMyTable is a stored procedure taking two parameters (last-download timestamp and MobiLink user name) and returning a result set. (This example uses an ODBC calling convention for portability):

{call DownloadMyTable( {ml s.last_table_download}, {ml s.username} )}

The following are some simple examples for each supported consolidated database. Consult the documentation for your consolidated database for full details.

The following example works with SQL Anywhere, Adaptive Server Enterprise, and Microsoft SQL Server.

CREATE PROCEDURE SPDownload
    @last_dl_ts DATETIME,
    @u_name VARCHAR( 128 )
AS
BEGIN
     SELECT pk, col1, col2
       FROM MyTable
          WHERE last_modified >= @last_dl_ts
          AND employee = @u_name
END

The following example works with Oracle. Oracle requires that a package be defined. This package must contain a record type for the result set, and a cursor type that returns the record type.

Create or replace package SPInfo as
Type SPRec is record (
    pk     integer,
    col1   varchar(100),
    col2   varchar(20)
);
Type SPCursor is ref cursor return SPRec;
End SPInfo;

Next, Oracle requires a stored procedure with the cursor type as the first parameter. Note that the download_cursor script only passes in two parameters, not three. For stored procedures returning result sets in Oracle, cursor types declared as parameters in the stored procedure definition define the structure of the result set, but do not define a true parameter as such. In this example, the stored procedure also adds the script to the MobiLink system table.

Create or replace procedure
    DownloadMyTable( v_spcursor IN OUT SPInfo.SPCursor,
                     v_last_dl_ts IN DATE,
                     v_user_name IN VARCHAR ) As
Begin
    Open v_spcursor For
       select pk, col1, col2
         from MyTable
           where last_modified >= v_last_dl_ts
           and employee = v_user_name;
End;

CALL ml_add_table_script( 
  'v1',
  'MyTable',
  'download_cursor',
  '{CALL DownloadMyTable( 
      {ml s.last_table_download},{ml s.username} )}'
);

The following example works with IBM DB2 LUW.

CREATE PROCEDURE DownloadMyTable(
   IN last_dl_ts TIMESTAMP,
   IN u_name VARCHAR( 128 ) )
        LANGUAGE SQL
        MODIFIES SQL DATA
        COMMIT ON RETURN NO
        DYNAMIC RESULT SETS 1

        BEGIN
            DECLARE C1, cursor WITH RETURN FOR
                SELECT pk, col1, col2 FROM MyTable
                WHERE last_modified >= last_dl_ts AND employee = u_name;
            OPEN C1;
        END;

The following example works with IBM DB2 mainframe.

CREATE PROCEDURE DownloadMyTable(
   IN last_dl_ts TIMESTAMP,
   IN u_name VARCHAR( 128 ) )
        LANGUAGE SQL
        MODIFIES SQL DATA
        EXTERNAL NAME MYDMT
        WLM ENVIRONMENT MYWLM
        COMMIT ON RETURN NO
        DYNAMIC RESULT SETS 1
        BEGIN
            DECLARE C1, cursor WITH RETURN FOR
                SELECT pk, col1, col2 FROM MyTable
                WHERE last_modified >= last_dl_ts AND employee = u_name;
            OPEN C1;
        END;

Discuss this page in DocCommentXchange.
Send feedback about this page using email.