Alistof SQLreturncodes isprovidedinSQLMessagesandCodes topicintheiSeriesInformation Center.
Chapter
12.
Sample
programs
using
DB2
UDB
for
iSeries
statements
Thistopiccontains asampleapplicationshowinghow tocodeSQLstatementsineachofthelanguages supportedbytheDB2UDBforiSeriessystem.
Note: Readthe“Codedisclaimerinformation” onpage196forimportantlegalinformation. Examplesof programsthatuseSQLstatements
Programsthatprovideexamplesofhow tocodeembeddedSQLstatements areprovidedforthe followingprogramminglanguages:
v ILECandC++
v COBOLandILECOBOL v PL/I
v RPGforiSeries v ILERPG foriSeries v REXX
Thesampleapplication givesraisesbasedoncommission.
Eachsampleprogramproducesthesamereport,whichisshown attheendofthistopic.Thefirstpart of thereportshows,byproject,all employeesworking ontheprojectwho receiveda raise.Thesecondpart ofthereportshowsthenew salaryexpense foreachproject.
Notesaboutthesampleprograms:
Thefollowingnotesapplytoallthesampleprograms: SQLstatementscanbeenteredinupperorlowercase.
1 ThishostlanguagestatementretrievestheexternaldefinitionsfortheSQLtablePROJECT. These definitionscanbeusedashostvariablesor asa hoststructure.
Notes:
1. InRPG foriSeries,fieldnamesinan externallydescribedstructurethatarelongerthan6 characters mustberenamed.
2. REXXdoesnotsupporttheretrievalof externaldefinitions.
2 TheSQLINCLUDESQLCAstatementisusedtoincludetheSQLCAforPL/I,C, andCOBOL programs.For RPGprograms,theSQLprecompilerautomaticallyplacestheSQLCAdata
structureintothesourceattheendoftheInputspecification section.ForREXX,theSQLCAfields aremaintainedinseparate variablesratherthaninacontiguousdataareamappedbythe
SQLCA.
3 ThisSQLWHENEVERstatementdefinesthehostlanguagelabeltowhichcontrolispassedifan SQLERROR(SQLCODE<0) occursinan SQLstatement.ThisWHENEVERSQLERROR
statementappliestoallthefollowingSQLstatementsuntilthenext WHENEVERSQLERROR statementisencountered.REXXdoesnotsupporttheWHENEVERstatement.Instead,REXXuses theSIGNALON ERRORfacility.
4 ThisSQLUPDATE statementupdatestheSALARYcolumn,whichcontainstheemployeesalary bythepercentageinthehostvariable PERCENTAGE(PERCNT forRPG).Theupdatedrowsare
thosethathaveemployeecommissionsgreaterthan2000.ForREXX,this isPREPAREand EXECUTEsinceUPDATEcannotbe rundirectlyifthereisa hostvariable.
5 ThisSQLCOMMITstatementcommitsthechangesmadebytheSQLUPDATEstatement.Record locksonall changedrowsare released.
Note: TheprogramwasprecompiledusingCOMMIT(*CHG).(ForREXX,*CHGisthedefault.) 6 ThisSQLDECLARE CURSORstatementdefinescursorC1,whichjoinstwotables, EMPLOYEE andEMPPROJACT,and returnsrowsforemployeeswhoreceivedaraise (commission>2000). Rowsarereturnedinascendingorderbyprojectnumberand employeenumber (PROJNOand EMPNOcolumns).For REXX,thisisaPREPAREandDECLARECURSORsincetheDECLARE CURSORstatementcannotbespecifieddirectlywitha statementstringifit hashostvariables. 7 ThisSQLOPEN statementopenscursorC1sothattherowscanbefetched.
8 ThisSQLWHENEVER statementdefinesthehostlanguagelabeltowhichcontrolispassedwhen allrowsare fetched(SQLCODE = 100). ForREXX,theSQLCODEmust beexplicitlychecked. 9 ThisSQLFETCHstatementreturnsallcolumnsforcursorC1and placesthereturnedvaluesinto
thecorresponding elementsofthehoststructure.
10 Afterallrowsarefetched, controlispassedto thislabel.TheSQLCLOSEstatementclosescursor C1.
11 ThisSQLDECLARE CURSORstatementdefinescursorC2,whichjoinsthethreetables, EMPPROJACT,PROJECT,and EMPLOYEE.TheresultsaregroupedbycolumnsPROJNOand PROJNAME.TheCOUNTfunction returnsthenumberof rowsineachgroup.TheSUMfunction calculatesthenewsalarycostforeachproject.TheORDERBY1 clausespecifiesthatrowsare retrievedbasedonthecontentsofthefinalresultscolumn(EMPPROJACT.PROJNO).ForREXX, thisisaPREPAREandDECLARECURSORsincetheDECLARECURSORstatementcannotbe specifieddirectlywith astatementstringif ithashostvariables.
12 ThisSQLFETCHstatementreturnstheresultscolumnsfor cursorC2andplacesthereturned valuesintothecorresponding elementsofthehoststructuredescribedbytheprogram.
13 ThisSQLWHENEVER statementwith theCONTINUE optioncausesprocessingtocontinueto thenext statementregardlessif anerroroccursontheSQLROLLBACKstatement. Errorsarenot expectedontheSQLROLLBACK statement;however,thispreventstheprogramfromgoinginto aloopifanerrordoesoccur.SQLstatementsuntil thenext WHENEVERSQLERRORstatementis encountered.REXXdoesnotsupporttheWHENEVERstatement.Instead,REXXusesthe
SIGNALOFFERRORfacility.
14 ThisSQLROLLBACK statementrestores thetabletoitsoriginalconditionifan erroroccurred duringtheupdate.
IBMgrantsyoua nonexclusivecopyrightlicensetouseallprogramming codeexamplesfromwhichyou cangeneratesimilarfunction tailoredtoyour ownspecificneeds.
Allsamplecodeisprovided byIBMfor illustrativepurposesonly.Theseexamples havenotbeen
thoroughlytestedunderallconditions.IBM,therefore,cannotguaranteeorimplyreliability,serviceability, orfunctionofthese programs.
Allprogramscontainedhereinareprovided toyou″ASIS″withoutanywarrantiesof anykind.The impliedwarrantiesofnon-infringement,merchantability andfitnessforaparticularpurposeareexpressly disclaimed.