Example 1: Calling an SQL module procedure from a Pascal program

    The following example is a Pascal program that calls a procedure
    in an SQL module file:

    PROGRAM list_employees(OUTPUT);

    {
      Program to list employees' names whose last name matches a LIKE
      predicate.
      Note the following:
        1) The input parameter (like_string) to the SELECT expression
           in the DECLARE CURSOR is supplied on the OPEN_CURSOR call.
        2) The output parameters are returned on each FETCH_INTO call.
        3) The cursor is closed after the desired rows are processed,
           so that it will be positioned properly in subsequent
           operations.
     }

    TYPE
     LAST_NAME = PACKED ARRAY[1..14] OF CHAR;
     FIRST_NAME = PACKED ARRAY[1..10] OF CHAR;

    VAR
           { Variable data }

     sqlcode : INTEGER := 0;
     emp_last : LAST_NAME;
     emp_first: FIRST_NAME;
     like_string : LAST_NAME := 'T_ _ _ _ _ _ _ _ _ _ _ _ _';

           { Declarations of entry points in the SQL module }

     PROCEDURE SET_TRANS (VAR sqlcode : INTEGER); EXTERNAL;
     PROCEDURE OPEN_CURSOR (VAR sqlcode: INTEGER;
                                name : LAST_NAME); EXTERNAL;
     PROCEDURE FETCH_INTO (VAR sqlcode : INTEGER;
                           VAR last : LAST_NAME;
                           VAR first : FIRST_NAME); EXTERNAL;
     PROCEDURE CLOSE_CURSOR (VAR sqlcode : INTEGER); EXTERNAL;
     PROCEDURE ROLLBACK_TRANS (VAR sqlcode : INTEGER); EXTERNAL;
    BEGIN
     SET_TRANS (sqlcode);               { Start a read-only transaction.}
     OPEN_CURSOR (sqlcode, like_string);{ Open the cursor, supplying    }
                                        { the string to match against.  }
     WRITELN('Matching Employees:');    { Print header.                 }
     REPEAT                             { Iterate matching names.       }
      BEGIN
      FETCH_INTO (sqlcode, emp_last, emp_first);{ Fetch the next name.  }
      IF sqlcode = 0
      THEN
       WRITELN(emp_first, emp_last);    { Print employee information.   }
      END
     UNTIL sqlcode <> 0;
     IF sqlcode <> 100                  { Print any error information.  }
     THEN
      WRITELN ('SQL error code = ', sqlcode);
     CLOSE_CURSOR (sqlcode);            { Finish the cursor operation.  }
     ROLLBACK_TRANS (sqlcode);          { Finish the transaction.       }
    END.

    Here is the SQL module file that this program calls:

    MODULE employees
    LANGUAGE PASCAL
    AUTHORIZATION SQL_USER
    ALIAS RDB$DBHANDLE

     DECLARE ALIAS FOR FILENAME PERSONNEL

     DECLARE names CURSOR FOR
      SELECT LAST_NAME, FIRST_NAME
      FROM EMPLOYEES
      WHERE LAST_NAME LIKE match_string

     PROCEDURE SET_TRANS
      SQLCODE;
      SET TRANSACTION READ ONLY;

     PROCEDURE OPEN_CURSOR
      SQLCODE
      match_string CHAR(14);
      OPEN names;

     PROCEDURE FETCH_INTO
      SQLCODE
      l_name CHAR(14)
      f_name CHAR(10);
      FETCH names INTO l_name, f_name;

     PROCEDURE CLOSE_CURSOR
      SQLCODE;
      CLOSE names;

     PROCEDURE ROLLBACK_TRANS
      SQLCODE;
      ROLLBACK;

    Example 2: Calling an SQL module procedure from a C program

    The following example is a C program that calls a procedure that
    is in an SQL module file:

    /*
      C program to list employees' names where the last name matches a LIKE
      predicate.
      Note the following:
        1) The input parameter (like_string) to the SELECT expression
           in the DECLARE CURSOR is supplied on the OPEN_CURSOR call.
        2) The output parameters are returned on each FETCH_INTO call.
        3) The cursor is closed after the desired rows are processed,
           so that it will be positioned properly in subsequent operations.
     */

    #include <stdio.h>

    #pragma dictionary "name"

    typedef struct name NAME_TYPE;
    extern void FETCH_INTO (int *sqlcode, NAME_TYPE *name_record);

    typedef char LAST_NAME[15];
    typedef int *SQLCODE;

           /* Declarations of entry points in the SQL module */

    extern void SET_TRANS (int *sqlcode);
    extern void OPEN_CURSOR (int *sqlcode,
                             LAST_NAME name);

    extern void CLOSE_CURSOR (int *sqlcode);
    extern void ROLLBACK_TRANS (int *sqlcode);

    void main ()
    {
    int sqlcode = 0;
    NAME_TYPE name_record;
    LAST_NAME like_string = "T%";

    SET_TRANS (&sqlcode);                 /* Start a read-only transaction. */
    if (sqlcode != 0)                     /* Print any error information. */
      printf ("SQL error code = %d\n", sqlcode);

    OPEN_CURSOR (&sqlcode, like_string);  /* Open the cursor, supplying    */
                                          /* the string to match against.  */
    if (sqlcode != 0)                     /* Print any error information. */
      printf ("SQL error code = %d\n", sqlcode);

    printf ("Matching Employees:\n");     /* Print header. */
    do                                    /* Iterate matching names. */
      {
      FETCH_INTO (&sqlcode, &name_record);/* Fetch the next name. */
      if (sqlcode == 0)
       printf ("%s%s\n", name_record.f_name, name_record.l_name);
      }                                   /* Print employee information. */
     while (sqlcode == 0);
    if (sqlcode != 100)                   /* Print any error information. */
      printf ("SQL error code = %d\n", sqlcode);

    CLOSE_CURSOR (&sqlcode);              /* Complete the cursor operation. */
    if (sqlcode != 0)                     /* Print any error information. */
      printf ("SQL error code = %d\n", sqlcode);

    ROLLBACK_TRANS (&sqlcode);            /* Finish the transaction. */
    if (sqlcode != 0)                     /* Print any error information. */
      printf ("SQL error code = %d\n", sqlcode);

    }

    Here is the SQL module file that this program calls:

    MODULE employees
    LANGUAGE C
    AUTHORIZATION SQL_USER
    ALIAS RDB$DBHANDLE

    DECLARE ALIAS FOR PATHNAME 'MF_PERSONNEL'

    DECLARE names CURSOR FOR
     SELECT LAST_NAME, FIRST_NAME
     FROM EMPLOYEES
     WHERE LAST_NAME LIKE match_string

    PROCEDURE SET_TRANS
     SQLCODE;
    SET TRANSACTION READ ONLY;

    PROCEDURE OPEN_CURSOR
     SQLCODE
     match_string CHAR(14);
    OPEN names;

    PROCEDURE FETCH_INTO
     SQLCODE,
     name_record RECORD FROM 'name' END RECORD;
    FETCH names INTO name_record;

    PROCEDURE CLOSE_CURSOR
     SQLCODE;
    CLOSE names;

    PROCEDURE ROLLBACK_TRANS
     SQLCODE;
    ROLLBACK;