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,
    employee VARCHAR(100) NOT NULL,
    last_modified TIMESTAMP NOT NULL DEFAULT TIMESTAMP
)

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 DownloadMyTable
    @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

For Oracle, the result set can be returned by a REF CURSOR defined in a stored procedure. However, when using the iAnywhere Solutions 12 - Oracle ODBC driver, the REF CURSOR parameter should be defined as the last one in the parameter list of the stored procedure. The REF CURSOR parameter can be defined as OUT or IN OUT. The following stored procedure works with Oracle.

create or replace procedure DownloadMyTable(
	v_last_dl_ts IN TIMESTAMP,
	v_user_name IN VARCHAR,
	v_ref_crsr OUT SYS_REFCURSOR ) As
Begin
    Open v_ref_crsr For
        select pk, col1, col2
            from MyTable
                where last_modified >= v_last_dl_ts
                and employee = v_user_name;
End DownloadMyTable;

Next, use the ml_add_table_script stored procedure to define a call to DownloadMyTable as the download_cursor script for the synchronization table MyTable:

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

For Oracle, note that the DownloadMyTable stored procedure only takes two parameters, not three, and the MobiLink server fetches the result set through the REF CURSOR. The REF CURSOR is defined as the last parameter in the stored procedure definition.

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;