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