Previous Topic Next topic Print topic


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
Previous Topic Next topic Print topic