MobiLink Synchronization User's Guide
  Synchronization Techniques

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 cursor script might look as follows:

SELECT pk, col1, col2
   FROM MyTable
      WHERE last_modified > ?
      AND employee = ?

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( ?, ? )}

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 Adaptive Server 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.

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

The following example works with IBM DB2 UDB.

CREATE PROCEDURE DownloadMyTable(
   IN last_dl_ts TIMESTAMP,
   IN u_name VARCHAR( 128 ) )
        EXTERNAL NAME 'DLMyTable!DownloadMyTable'
        RESULT SETS 1
        FENCED
        LANGUAGE JAVA PARAMETER STYLE DB2GENERAL

The following example is a Java implementation of the stored procedure, in DLMyTable.java. To return a result set, you must leave the result set open when the method returns:

import COM.ibm.db2.app.*;
import java.sql.*;

public class DLMyTable extends StoredProc
{
  public void DownloadMyTable(
      Date last_dl_ts,
      String u_name ) throws Exception
  {
      Connection  conn = getConnection();
      conn.setAutoCommit( false );
      Statement   s    = conn.createStatement();
      // Execute the select and leave it open.
      ResultSet   r    = s.executeQuery(
            "select pk, col1, col2 from MyTable"
                  + " where last_modified > '"
                  + last_dl_ts
                  + "' and employee = '"
                  + u_name + "'" );
    }
}