Chapter 3: Open PL/I SQL Options

This Chapter describes the SQL Options available with Open PL/I. These include the DB2 SQL Option that provides support for IBM's DB2 on UNIX and the Pro*PL/I Option that provides support for Oracle versions 10 and 11 on UNIX.

Oracle SQL Support

Open PL/I enables you to develop PL/I applications that make use of Oracle databases. Open PL/I's Pro*PLI precompiler allows you to compile and link PL/I applications containing embedded Oracle SQL statements to access these databases. This chapter gives the special instructions needed to compile and link such Open PL/I applications. For more information on Pro*PL/I, refer to the following documents:

• Programmer's Guide to the Oracle Precompilers
• Pro*PL/I Precompiler Supplement

Support for Pro*PL/I is included in the standard Open PL/I product set, but you must obtain from Micro Focus a Pro*PL/I license before it can be used. Also, before you can take advantage of EXEC SQL support, you must have installed Oracle version 10 on your system (the Oracle version 10 is also able to communicate with Oracle 11 database).

Running Pro*PL/I

The Oracle precompiler can be run using the Open PL/I driver (mfplx), or it can be invoked directly using the Pro*PL/I executable file. To invoke Pro*PL/I using mfplx, add the -oracle Compiler option to the command line:

mfplx filename.pl1 –oracle

Pro*PL/I Precompiler Options for mfplx

The following precompiler options are used with the Open PL/I command driver, mfplx. If you want to invoke Pro*PL/I directly, see the Pro*PL/I Precompiler Supplement for the available options.

The Pro*PL/I precompiler options can be entered on the command line or from a configuration file. We recommend the use of a configuration file.

DB2 SQL Support

Open PL/I enables you to develop PL/I applications that make use of databases maintained by IBM DB2. Open PL/I's DB2 SQL precompiler allows you to compile and link PL/I applications containing embedded DB2 SQL statements to access these databases. This document gives the special instructions needed to code, compile, and link such Open PL/I applications.

Support for DB2 SQL is included in the standard Open PL/I product set, but you must obtain from Micro Focus a DB2 SQL license before it can be used. Also, before you can take advantage of EXEC SQL support, you must have installed and started IBM's DB2 product on your system.

You can code DB2 EXEC SQL statements in Open PL/I applications using the language defined in the IBM document DATABASE 2 Client/Server SOL Reference. Specific requirements for your SQL code are described in the sections that follow.

DB2 SQL Precompiler Options

The following DB2 SQL precompiler options are supported by the Open PL/I driver program, mfplx:

Coding DB2 SQL Statements in Open PL/I Applications

Specific requirements for your embedded SQL code are described in the sections that follow.

Defining the DB2 SQL Communications Area

An Open PL/I program that contains SQL statements must include an SQL communications area (SQLCA). As shown in Figure 3-1, SQLCA includes an SQLCODE variable and an SQLSTATE variable.

The SQLCODE value is set by the Database Manager after each SQL statement is executed. An application can check the SQLCODE value to determine whether the last SQL statement was successful.

The SQLSTATE variable can be used as an alternative to the SQLCODE variable when analyzing the result of an SQL statement. Like the SQLCODE variable, the SQLSTATE variable is set by the Database Manager after each SQL statement is executed.

The SQLCA should be included by using the SQL INCLUDE statement:

exec sql include sqlca;

The SQLCA must not be defined within an SQL declare section. The scope of the SQLCODE and SQLSTATE declarations must include the scope of all SQL statements in the program.

Figure 3-1: Open PL/I Declaration of SQLCA (DB2)

/*--------------------------------------------------------------- */
/* DB2 SQL Communications Area                                    */ 
/* This should be included by the                                 */ 
/* 'EXEC SQL INCLUDE SQLCA;' statement.                           */ 
/* Do not place this within a SQL declare statement.              */
/*--------------------------------------------------------------- */ 
declare 1 sqlca,                              
  2 sqlcaid char(8),                 /* Eyecatcher = 'SQLA '      */
  2 sqlcabc fixed bin(31),           /* SQLCA size in bytes = 136 */                           
       2 sqlcode fixed bin(31),      /* SQL return code           */
       2 sqlerrm char(70) varying,   /* Error message tokens      */                            
       2 sqlerrp char (8),           /* Diagnostic information    */
       2 sqlerrd(1:6) fixed bin(31), /* Diagnostic information    */
       2 sqlwarn,                    /* Warning flags             */
         3 sqlwarn0 char(1),
         3 sqlwarnl char(1),
         3 sqlwarn2 char(1),
         3 sqlwarn3 char(1),
         3 sqlwarn5 char(1),
         3 sqlwarn6 char(1),
         3 sqlwarn7 char(1),
       2 sqltext,
         3 sqlwarn8 char(1),
         3 sqlwarn9 char(1),
         3 sqlwarna char(1),
         3 sqlstate char(5); /* State corresponding to SQLCODE */

/*    End of SQL Communications Area declaration */

Defining SQL Descriptor Areas

The following statements require an SQLDA:

• PREPARE statement-name INTO descriptor-name FROM host-variable
• EXECUTE ... USING DESCRIPTOR descriptor-name
• FETCH ... USING DESCRIPTOR descnptor-name
• OPEN ... USING DESCRIPTOR descriptor-name
• DESCRIBE statement-name INTO descriptor-name

Unlike the SQLCA, there can be more than one SQLDA in a program, and an SQLDA can have any valid name. An SQLDA should be included by using the SQL INCLUDE statement:

exec sql include sqlda;

The SQLDA must not be defined within an SQL declare section.

The Open PL/I SQLDA template includes %REPLACE statements for the SQL data types.

Figure 3-2: Open PL/I Declaration of an SQLDA (DB2)

/*-------------------------------------------------------------------------------- */
/* DB2 SQL Descriptor Template                                                         */
/* This should be included by the 'EXEC SQL INCLUDE SQLDA;' statement.                 */
/* Do not place this within an SQL declare statement.                                  */
/*------------------------------------------------------------------------------------ */
declare 1 sqlda based(sqldaptr),                                                       */
   2 sqldaid        char(8),                     /* Eye catcher 'SQLDA   '             */
   2 sqldabc fixed bin(31),                      /* SQLDA size in bytes 16 + 44 * SQLN */
   2 sqln           fixed bin(15),               /* Number of SQLVAR elements          */
   2 sqld           fixed bin(15),               /* Number of used SQLVAR elements     */
   2 sqlvar(1:sqlsize refer(sqln)),              /* Variable descriptor                */
     3 sqltype      fixed bin(15),               /* Variable data type                 */
     3 sqllen       fixed bin(15),               /* Variable length                    */
     3 sqldata      pointer,                     /* Pointer to variable data value     */
     3 sqlind       pointer,                     /* Pointer to null indicator          */
     3 sqlname      char(30) var;                /* Variable name                      */
declare sqlsize fixed bin(15);                   /* Number of sqlvars (sqln)           */
declare sqldaptr pointer;
/* End of SQL Descriptor Template declaration */

/* replaces for SQL type codes                                                         */
/* replaces of the SQL_TYP_Nxxnnn means with Null indicator                            */
%replace SQL_TYP_DATE      by 384; /* DATE */
%replace SQL_TYP_NDATE     by 385;
%replace SQL_TYP_TIME      by 388; /* TIME */
%replace SQL_TYP_NTIME     by 389;
%replace SQL_TYP_STAMP     by 392; /* TIMESTAMP */ 
%replace SQL_TYP_NSTAMP    by 393;
%replace SQL_TYP_VARCHAR   by 448; /* VARCHAR(I) varying length string I<=4000*/41) 
%replace SQL_TYP_NVARCHAR  by 449;
%replace SQL_TYP_CHAR      by 452; /* CHAR(I) - fixed length string */
%replace SQL_TYP_NCHAR     by 453;
%replace SQL_TYP_LONG      by 456; /* LONG VARCHAR - varying length I > 4000 */
%replace SQL_TYP_NLONG     by 457; 
%replace SQL_TYP_CSTR      by 460; /* varying length string for C */
%replace SQL_TYPNCSTR      by 461; 
%replace SQL_TYP_VARGRAPH  by 464; /* VARGRAPHIC(I) - varying length */
%replace SQL_TYP_NVARGRAPH by 465;
%replace SQL_TYP_GRAPHIC   by 468; /* GRAPHIC(I) - fixed length graphic */
%replace SQL_TYP_NGRAPHIC  by 469;
%replace SQL_TYP_LONGRAPH  by 472; /* LONG VARGRAPHIC(I) - varying length */ 




%replace SQL_TYP_NLONGRAPH by 473;
%replace SQL_TYP_LSTR      by 476; /* varying length string for Pascal */ 
%replace SQL_TYP_NLSTR     by 477;
%replace SQL_TYP_FLOAT     by 480; /* FLOAT - 8-byte floating point */ 
%replace SQL_TYP_NFLOAT    by 481;
%replace SQL_TYP_DECIMAL   by 484; /* DECIMAL (p,q), sqllen q + (256 * p) */
%replace SQL_TYP NDECIMAL  by 485;
%replace SQL_TYP_ZONED     by 488; /* Zoned Decimal -> DECIMAL (m,n) */
%replace SQL_TYP_NZONED    by 489; 
%replace SQL_TYP_INTEGER   by 496; /* INTEGER - 4-byte signed integer */
%replace SQL_TYP_NINTEGER  by 497; 
%replace SQL_TYP_SMALL     by 500; /* SMALLINT - 2-byte signed integer*/
%replace SQL_TYP NSMALL    by 501;
%replace SQL_TYP_NUMERIC   by 504; /* NUMERIC -> DECIMAL (m,n) */ 
%replace SQL_TYP_NNUMERIC  by 505;



/* End of EXEC SQL INCLUDE SQLDA  -------------------------------------------------*/ 

Embedding SQL Statements

You can add SQL statements to your program wherever executable statements can appear. Each SQL statement must begin with EXEC (or EXECUTE) SQL and end with a semicolon (;).

For example, an UPDATE statement might be coded as follows:

exec sql update Department
   set Mgrno    = :Mgr_Num
   where Deptno = :Int_Dept;

Comments

PL/I comments can be included in embedded SQL statements wherever a blank is allowed.

Continuation

The line continuation rules for embedded SQL statements are the same as for other PL/I statements.

Including Code

SQL statements or PL/I host variable declaration statements can be included by placing the following SQL statement at the point in the source code where the statements are to be embedded:

exec sql include {filename | 'filename'};

filename can be enclosed in single quote characters ('). Quotes are required if filename contains any non-alphanumeric characters, as for example,

'inventory.db001'.

Names

Any valid PL/I variable name can be used for a host variable, subject to the following restriction: Do not use host variable names or external entry names that begin with "SQL". These names are reserved for the database manager or Open PL/I. The length of a host variable name must not exceed 30 characters.

Statement Labels

With the exception of the END DECLARE SECTION statement and the INCLUDE text-filename statement, executable SQL statements, like PL/I statements, can have a label prefix.

WHENEVER Statement

The target for a GOTO clause in an SQL WHENEVER statement must be a label in the PL/I source code and must be within the scope of any SQL statements affected by the WHENEVER statement.

Using Host Variables

All host variables used in SQL statements must be explicitly declared. A host variable used in an SQL statement must be declared prior to the first use of the host variable in an SQL statement, except when the -sqltwop precompiler option is used. In addition:

• All host variables within an SQL statement must be preceded by a colon (:).
• The names of host variables must be unique within a program, even if the host variables are in different blocks or procedures.
• An SQL statement that uses a host variable must be within the scope of the statement in which the variable is declared.
• A host variable cannot be declared as an array.

Declaring Host Variables

Host variable declarations can be made at the same places as ordinary PL/I variable declarations.

Not all PL/I data types are acceptable for host variables. The precompiler does not use the data attribute defaults specified in the PL/I DEFAULT statement. If the declaration for a variable is not recognized, any statement that references the variable may result in the message "The host variable token ID is not valid".

The SQL precompiler uses only the names and data attributes of host variable declarations; the alignment, scope, and storage class attributes are ignored.

Numeric Host Variables

The syntax for numeric host variable declarations is as follows:

DECLARE var-name-spec {BINARY | DECIMAL} scale-spec align-scope-storage ;

where:

Character Host Variables

The syntax for character host variable declarations is as follows:

DECLARE var-name-spec CHARACTER [(length)] [VARYING] align-scope-storage ;

where:

Determining Equivalent SQL and PL/I Data Types

The base SQLTYPE and SQLLEN of host variables are determined according to the following table. If a host variable appears with an indicator variable, the SQLTYPE is the base SQLTYPE plus one.

• For FLOAT data, only double-word BIN FLOAT is supported.
• Precisions, where appropriate, must be specified with integer constants. Default values are not provided.
• The SQLDA include file has %REPLACE statements for the SQL data types.

The following table can be used to determine the PL/I data type that is equivalent to a given SQL data type.

Determining Compatibility of SQL and PL/I Data Types

PL/I host variables in SQL statements must be type compatible with the columns that use them:

• Numeric data types are compatible with each other. A SMALLINT, INTEGER, DECIMAL, or FLOAT column is compatible with a PL/I host variable of BIN FIXED(15), BIN FIXED(31), DECIMAL(p,s), or BIN FLOAT(n), where 24 ≤ n ≤ 53.
• Character data types are compatible with each other. A CHAR or VARCHAR column is compatible with a fixed-length or varying-length PL/I character host variable. When necessary, the Database Manager automatically converts a fixed-length character string to a varying-length string or a varying-length string to a fixed-length string.
• Datetime data types are compatible with character host variables. A DATE, TIME, or TIMESTAMP column is compatible with a fixed-length or varying-length PL/I character host variable.

Using Host Structures

A PL/I host structure name can be a structure name. For example:

dcl 1 A,
      2  B,
        3  C1 char(...),
        3  C2 char(...);

In this example, B is the name of a host structure consisting of the scalar members C1 and C2.

However, structures (whether level 1 structures or substructures) may not be referenced in SQL statements. Only scalar members may be so referenced.

A fully qualified member name must be no more than 30 characters in length.

Using Indicator Variables

An indicator variable is a two-byte integer (BIN FIXED(15)). On retrieval, an indicator variable is used to show whether its associated host variable has been assigned a null value. On assignment to a column, a negative indicator value is used to indicate that a null value should be assigned.

Indicator variables are declared in the same way as host variables and the declarations of the two can be mixed in any way that seems appropriate.

Given the statement:

exec sql fetch Cls_Cursor into  :Cls_Cd,
                                :Day  :Day_Ind,
                                :Bgn  :Bgn_Ind,
                                :End  :End_Ind;

the variables could be declared as follows:

exec sql begin declare section;
dcl Cls_Cd     char(7);
dcl Day        bin fixed(15);
dcl Bgn        char(8);
dcl End        char(8);
dcl (Day_Ind, Bgn_Ind, End_Ind) bin fixed(15);

CONNECT TO Statement

You can use a host variable to represent the database name you want your application to connect to. For example:

exec sql connect to :dbase;

If a host variable is specified:

• It must be a character or character varying variable.
• It must be preceded by a colon and must not be followed by an indicator variable.
• The server-name that is contained within the host variable must be left-justified.
• If the length of the server-name is less than the length of the fixed-length character host variable, it must be padded on the right with blanks.

  dcl dbase char(10);
  dbase = 'SAMPLE';    /*four blanks are */
                       /* automatically appended */ 
  exec sql connect to :dbase;

COMPOUND Statement

Open PL/I requires a trailing semicolon after the start of an SQL compound statement. For example:

exec sql begin compound atomic static;
      select count(*) into :tmp3 from department; 
      select count(*) into :tmp4 from project; 
end compound;

Handling SQL Error Return Codes

Your DB2 product includes a routine that translates SQLCODE error values into readable text messages. When you process your program source with Open PL/I's DB2 SQL precompiler, an %INCLUDE statement is inserted into your program, and the include file this incorporates into your program contains a declaration of this SQLCODE translator routine as an entry, as follows:

declare sqldb2err entry(fixed bin(15) value,
                                    /* Buffer Size   */
               fixed bin(15) value, /* Line Width    */
               any,                 /* SQLCA         */
               any)                 /* Buffer        */
      returns(fixed bin(15))
      external('sqlgintp');

The return code values are:

• >0 Length of message
• -1 Internal DB2 error
• -2 No error, SQLCODE = 0
• -3 Invalid SQLCODE, no such error
• -4 Line width is 0
• -5 Invalid SQLCA, bad buffer address or bad buffer size

Example:

declare errmsg character(200);

exec sql connect to MYDB;
if sqlca.sqlcode < 0 then do;
   put skip list('Connect failed');
   /* Now print out reason for failure for most */ 
   /* recent SQL statement (i.e., the connect) */ 
   msg_len = sqldb2err(size(errmsg),0,sqlca,errmsg); 
   put skip list(substr(errmsg,1,msg_len));
   return;
   end;

You do not need to declare sqldb2err. This is done for you automatically, as described above.

For more information about this routine, see SQLGINTP (Get Error Message) in your IBM platform-specific DATABASE 2 Programming Reference manual.

Sample Programs

Open PL/I DB2 SQL Sample 1 Program

filename: sample1.sqp

sample1: proc options(main);

/*                                                  */
/* This program shows the usage of host-variables,  */ 
/* indicators, and some common SQL statements       */

/* The database this program uses is the IBMSAMPL   */ 
/* database that your DB2 product uses. You must be */
/* able to run the IBM DB2 demo sample program in   */ 
/* order to be able to run this program.            */
/*                                                  */

dcl idx fixed bin(15);
dcl errmsg character(200); 
dcl msg_len fixed bin(15);

exec sql include sqlca;

exec sql begin declare section;
   dcl cnt fixed bin(15); 
   dcl projno char(6);
   dcl projname char(24) varying;
   dcl deptno char(6);  
   dcl respemp char(6);

   dcl prstaff float bin(24);
   dcl prstaff_ind fixed bin(15);

   dcl prstdate char(10);
   dcl prstdate_ind fixed bin(15);

   dcl prendate char(10);
   dcl prendate_ind fixed bin(15);

   dcl majproj char(6);
   dcl majproj_ind fixed bin(15); 
exec sql end declare section;

/* Set up error label for all SQL statements. */ 
exec sql whenever sqlerror goto err_label;

/* Connect to the IBM DB2 sample demo data base */ 
exec sql connect to IBMSAMPL;

/* Determine how many rows are in table project. */ 
exec SQL SELECT COUNT(*) INTO :cnt from project;

put skip edit('Number of rows in table project:', cnt) (a(40),x,f(3,0)); 
put skip list('Ail the rows for project are:');
put skip;

/* Declare and use a cursor to fetch each row. */ 
exec sql declare cl cursor for
  select projno, projname, deptno, respemp, prstaff, 
         prstdate, prendate, majproj
  from project;

exec sql open cl;
do idx = 1 to cnt;
   prstaff 0;         /* re-init */
   prstdate = ' ';    /* re-init */
   prendate = ' ';    /* re-init */
   majproj = ' ';     /* re-init */
   exec sql fetch cl into :projno, 
                          :projname,
                          :deptno, 
                          :respemp, 
                          :prstaff  :prstaff_ind,
                          :prstdate :prstdate_ind,
                          :prendate :prendate_ind,
                          :majproj  :majproj_ind;
   put edit (projno, projname, deptno, respemp, prstaff, prstdate,
             prendate, majproj)
            (a(6),x,a(24),x,a(3),x,a(6),x,f(5,2),x,a(10),x,a(10),x,a(6));
   put skip;
   end;

 return;

err_label:
   msg_len = sqldb2err(200,0,sqlca,errmsg);
   put skip list(substr(errmsg,l,msg_len));

end sample1;

Compilation, Linking, and Execution

mfplx -db2 sample1.sqp -o sample1.out 

sample1.out
Number of rows in table project:         20
All the rows for project are: 
AD3100 ADMIN SERVICES        DO1 000010  6.50 01/01/1982 02/01/1983
AD3110 GENERAL ADMIN SYSTEMS D21 000070  6.00 01/01/1982 02/01/1983 AD3100
AD3111 PAYROLL PROGRAMMING   D21 000230  2.00 01/01/1982 02/01/1983 AD3110
AD3112 PERSONNEL PROGRAMMING D21 000250  1.00 01/01/1982 02/01/1983 AD3110
AD3113 ACCOUNT PROGRAMMING   D21 000270  2.00 01/01/1982 02/01/1983 AD3110
IF1000 QUERY SERVICES        CO1 000030  2.00 01/01/1982 02/01/1983
IF2000 USER EDUCATION        CO1 000030  1.00 01/01/1982 02/01/1983
MA2100 WELD LINE AUTOMATION  DO1 000010 12.00 01/01/1982 02/01/1983
MA2110 W L PROGRAMMING       Dl1 000060  9.00 01/01/1982 02/01/1983 MA2100
MA2111 W L PROGRAM DESIGN    Dl1 000220  2.00 01/01/1982 12/01/1982 MA2110
MA2112 W L ROBOT DESIGN      Dl1 000150  3.00 01/01/1982 12/01/1982 MA2110
MA2113 W L PROD CONT PROGS   D11 000160  3.00 02/15/1982 12/01/1982 MA2110
OP1000 OPERATION SUPPORT     E01 000050  6.00 01/01/1982 02/01/1983
0P1010 OPERATION             E11 000090  5.00 01/01/1982 02/01/1983 OP1000
0P2000 GEN SYSTEMS SERVICES  E01 000050  5.00 01/01/1982 02/01/1983
OP2010 SYSTEMS SUPPORT       E21 000100  4.00 01/01/1982 02/01/1983 OP2000
OP2011 SCP SYSTEMS SUPPORT   E21 000320  1.00 01/01/1982 02/01/1983 0P2010
OP2012 APPLICATIONS SUPPORT  E21 000330  1.00 01/01/1982 02/01/1983 0P2010
092013 DB/DC SUPPORT         E21 000340  1.00 01/01/1982 02/01/1983 0P2010
PL2100 WELD LINE PLANNING    B01 000020  1.00 01/01/1982 09/15/1982 MA2100

Open PL/I DB2 SQL Sample 2 Program

filename: sample2.sqp

sample2: proc options(main);

   /* Sample program 2                                              */
   /* Example of using a user defined SQLDA                         */

   /* The database this program uses is the IBMSAMPL database that  */ 
   /* your DB2 product uses. You must be able to run the IBM DB2    */ 
   /* demo sample program in order to be able to run this program   */

   /* Uses a table of the form:                                     */
   /*    create table project                                       */
   /*       (projno char(6) not null,                               */ 
   /*       projname varchar(24) not null,                          */ 
   /*       deptno char(3) not null,                                */
   /*       respemp char(6) not null,                               */
   /*       prstaff dec(5,2),                                       */
   /*       prstdate date)                                          */

dcl k fixed bin(15);

exec sql include sqlca;
exec sql include sqlda;

exec sql begin declare section; 
   dcl cnt fixed bin(15);
exec sql end declare section;

sqlsize 6;                     /* Lets only look at the 1st 6 columns */
allocate sqlda set(sqldapntr); /* sets sqln */
sqldaptr->sqld = 6;

dcl projno char(6) based;
sqldaptr->sqlda.sqlvar(1).sqltype = SQL_TYP_CHAR; 
allocate projno set(sqldaptr->sqlda.sqlvar(1).sqldata); 
sqldaptr->sqlda.sqlvar(1).sqllen = 6;

dcl projname char(24) var based;
saldaptr->sqlda.sqlvar(2).sqltype = SQL_TYP VARCHAR; 
allocate projname set(sqldaptr->sqlda.sqlvar(2).sqldata); 
sqldaptr->sqlda.sqlvar(2).sqllen = 24;

dcl deptno char(3) based;
sqldaptr->sqlda.sqlvar(3).sqltype = SQL_TYP_CHAR; 
allocate deptno set(sqldaptr->sqlda.sqlvar(3).sqldata); 
sqldaptr->sqlda.sqlvar(3).sqllen = 3;

dcl respemp char(6) based;
sqldaptr->sqlda.sqlvar(4).sqltype = SQL_TYP_CHAR; 
allocate respemp set(sqldaptr->sqlda.sqlvar(4).sqldata); 
sqldaptr->sqlda.sqlvar(4).sqllen = 6;

dcl prstaff float(24) binary based;
sqldaptr->sqlda.sqlvar(5).sqltype = SQL_TYP_FLOAT; 
allocate prstaff set(sqldaptr->sqlda.salvar(5).sqldata); 
sqldaptr->sqlda.sqlvar(5).sqllen = 8;

dcl prstdate char(10) based;
sqldaptr->sqlda.sqlvar(6).sqltype = SQL_TYP_CHAR; 
allocate prstdate set(sqldaptr->sqlda.sqlvar(6).sqldata); 
sqldaptr->sqlda.sqlvar(6).sqllen = 10;

exec sql connect to IBMSAMPL;
if SQLCA.SQLCODE < 0 THEN do;
   put skip list('Connect failed');
   return;
   end;
  else put skip list('Connect OK');
  put skip;
  exec sql whenever sqlerror goto err_label;
  put skip list('Projno Projname                 Dpt Respemp Prst Prstdate');
  put skip;

  exec sql declare cl cursor for select * from project;
  exec sql open cl;
  exec sql whenever not found go to done;
  do while ('1'b1);  /* do till fetch raises not found error */
     exec sql fetch c1 using descriptor :sqldaptr;
     put edit (sqldaptr->sqlda.sqlvar(1).sqldata->projno, 
               sqldaptr->sqlda.sqlvar(2).sqldata->projname, 
               sqldaptr->sqlda.sqlvar(3).sqldata->deptno, 
               sqldaptr->sqlda.sqlvar(4).sqldata->respemp, 
               sqldaptr->sqlda.sqlvar(5).sqldata->prstaff, 
               sqldaptr->sqlda.sqlvar(6).sqldata->prstdate)
              (a(6),x,a(24),x,a(3),x,a(6),x,f(5,2),x,a(10));
     put skip;
     end;
done:
   exec sql whenever not found continue;   /* turn off */

   put skip; 
   return;
err_label:
   put skip list('Branched to err_label');
   put skip list('sqlcode =', sqlcode);
   put skip list('sqlerrm =' , sqlerrm);
   put skip list('sqlstate =', sqlstate);
   return;

end sample2;

Output

Connect OK

Projno Projname              Opt Respemp  Prst Prstdate
AD3100 ADMIN SERVICES        D01 000010   6.50 01/01/1982
AD3110 GENERAL ADMIN SYSTEMS D21 000070   6.00 01/01/1982
AD3111 PAYROLL PROGRAMMING   D21 000230   2.00 01/01/1982
AD3112 PERSONNEL PROGRAMMING D21 000250   1.00 01/01/1982
AD3113 ACCOUNT PROGRAMMING   D21 000270   2.00 01/01/1982
1F1000 QUERY SERVICES        CO1 000030   2.00 01/01/1982
1F2000 USER EDUCATION        CO1 000030   1.00 01/01/1982
MA2100 WELD LINE AUTOMATION  DO1 000010  12.00 01/01/1982
MA2110 W L PROGRAMMING       D11 000060   9.00 01/01/1982 
MA2111 W L PROGRAM DESIGN    D11 000220   2.00 01/01/1982
MA2112 W L ROBOT DESIGN      D11 000150   3.00 01/01/1982
MA2113 W L PROD CONT PROGS   D11 000160   3.00 02/15/1982
OP1000 OPERATION SUPPORT     E01 000050   6.00 01/01/1982
OP1010 OPERATION             E11 000090   5.00 01/01/1982
OP2000 GEN SYSTEMS SERVICES  E01 000050   5.00 01/01/1982
OP2010 SYSTEMS SUPPORT       E21 000100   4.00 01/01/1982
OP2011 SCP SYSTEMS SUPPORT   E21 000320   1.00 01/01/1982
0P2012 APPLICATIONS SUPPORT  E21 000330   1.00 01/01/1982
0P2013 DB/DC SUPPORT         E21 000340   1.00 01/01/1982
PL2100 WELD LINE PLANNING    B01 000020   1.00 01/01/1982

Copyright © 2009 Micro Focus (IP) Ltd. All rights reserved.