ERserver. Embedded SQL programming. iseries. Version 5 Release 3

(1)

iSeries

Embedded

SQL

programming

Version

5 Release

3

(2)

(3)

iSeries

Embedded

SQL

programming

Version

5 Release

3

(4)

Note

Beforeusingthisinformationandtheproductitsupports,be suretoreadtheinformationin “Notices,”onpage193.

ThirdEdition(May2004)

Thiseditionappliestoversion5,release3,modification0ofIBMOperatingSystem/400(productnumber5722-SS1) andtoallsubsequentreleasesandmodificationsuntilotherwiseindicatedinneweditions.Thisversiondoesnot runonallreducedinstructionsetcomputer(RISC)modelsnordoesitrunonCISCmodels.

(5)

Chapter

1. Embedded

SQL

programming

1 Chapter

2. What’s

new

for

Version

5 Release

3 in

the

Embedded

SQL

programming

topic

.

. 3

Chapter

3. Print

this

topic

.

. 5

Chapter

4. Common

concepts

and

rules

for

using

embedded

SQL

.

. 7

WritingapplicationsthatuseSQL . . . 7 UsinghostvariablesinSQLstatements . . . 7

AssignmentrulesforhostvariablesinSQL

statements . . . 9 IndicatorvariablesinapplicationsthatuseSQL 11 HandlingSQLerrorreturncodesusingtheSQLCA 13 UsingtheSQLdiagnosticsarea. . . 14

UpdatingapplicationstousetheSQLdiagnostics area. . . 14 iSeriesserverprogrammingmodel . . . 15 AdditionalnotesonusingtheSQLdiagnostics area. . . 15 Example:SQLroutineexception . . . 15 Example:LoggingitemsfromtheSQL

diagnosticsarea . . . 16 Handlingexceptionconditionswiththe

WHENEVERStatement . . . 16

Chapter

5. Coding

SQL

statements

in

C

and

C++

applications

.

. 19

DefiningtheSQLCommunicationsAreainCand C++applicationsthatuseSQL . . . 19 DefiningSQLDescriptorAreasinCandC++

applicationsthatuseSQL. . . 20 EmbeddingSQLstatementsinCandC++

applicationsthatuseSQL. . . 22 CommentsinCandC++applicationsthatuse SQL. . . 23 ContinuationforSQLstatementsinCandC++ applicationsthatuseSQL. . . 23 IncludingcodeinCandC++applicationsthat useSQL . . . 23 MarginsinCandC++applicationsthatuseSQL 23 NamesinCandC++applicationsthatuseSQL 23 NULLsandNULsinCandC++applications thatuseSQL . . . 24 StatementlabelsinCandC++applicationsthat useSQL . . . 24 PreprocessorsequenceforCandC++

applicationsthatuseSQL. . . 24 TrigraphsinCandC++applicationsthatuse SQL. . . 24 WHENEVERStatementinCandC++

UsinghostvariablesinCandC++applicationsthat useSQL . . . 24

DeclaringhostvariablesinCandC++

applicationsthatuseSQL. . . 25 UsinghoststructuresinCandC++applications thatuseSQL. . . 35

HoststructuredeclarationsinCandC++

applicationsthatuseSQL. . . 36 HoststructureindicatorarrayinCandC++

applicationsthatuseSQL. . . 39 UsingarraysofhoststructuresinCandC++

applicationsthatuseSQL. . . 39 HoststructurearrayinCandC++applications thatuseSQL . . . 40 HoststructurearrayindicatorstructureinCand C++applicationsthatuseSQL . . . 43 UsingpointerdatatypesinCandC++applications thatuseSQL. . . 43 UsingtypedefinCandC++applicationsthatuse SQL. . . 44 UsingILECcompilerexternalfiledescriptionsinC andC++applicationsthatuseSQL . . . 45 DeterminingequivalentSQLandCorC++data types . . . 46

NotesonCandC++variabledeclarationand usage . . . 48 UsingindicatorvariablesinCandC++applications thatuseSQL. . . 48

Chapter

6. Coding

SQL

statements

in

COBOL

applications

.

. 51

DefiningtheSQLCommunicationsAreainCOBOL applicationsthatuseSQL. . . 51 DefiningSQLDescriptorAreasinCOBOL

applicationsthatuseSQL. . . 52 EmbeddingSQLstatementsinCOBOLapplications thatuseSQL. . . 53

CommentsinCOBOLapplicationsthatuseSQL 54 ContinuationforSQLstatementsinCOBOL

applicationsthatuseSQL. . . 54 IncludingcodeinCOBOLapplicationsthatuse SQL. . . 54 MarginsinCOBOLapplicationsthatuseSQL. . 55 SequencenumbersinCOBOLapplicationsthat useSQL . . . 55 NamesinCOBOLapplicationsthatuseSQL . . 55 COBOLcompile-timeoptionsinCOBOL

applicationsthatuseSQL. . . 55 StatementlabelsinCOBOLapplicationsthatuse SQL. . . 55 WHENEVERStatementinCOBOLapplications thatuseSQL . . . 55 MultiplesourceCOBOLprogramsandtheSQL COBOLprecompiler . . . 55 || | || || | || || | ||

(6)

UsinghostvariablesinCOBOLapplicationsthatuse SQL. . . 55

DeclaringhostvariablesinCOBOLapplications thatuseSQL . . . 56 UsinghoststructuresinCOBOLapplicationsthat useSQL . . . 64

HoststructureinCOBOLapplicationsthatuse SQL. . . 65 HoststructureindicatorarrayinCOBOL

applicationsthatuseSQL. . . 68 UsinghoststructurearraysinCOBOL

applicationsthatuseSQL. . . 69 HoststructurearrayinCOBOLapplicationsthat useSQL . . . 70 HostarrayindicatorstructureinCOBOL

applicationsthatuseSQL. . . 73 UsingexternalfiledescriptionsinCOBOL

applicationsthatuseSQL. . . 74 Usingexternalfiledescriptionsforhoststructure arraysinCOBOLapplicationsthatuseSQL . . 75 DeterminingequivalentSQLandCOBOLdatatypes 75 NotesonCOBOLvariabledeclarationandusage 78 UsingindicatorvariablesinCOBOLapplications thatuseSQL . . . 78

Chapter

7. Coding

SQL

statements

in

PL/I

applications

.

. 79

DefiningtheSQLCommunicationsAreainPL/I applicationsthatuseSQL. . . 79 DefiningSQLDescriptorAreasinPL/Iapplications thatuseSQL . . . 80 EmbeddingSQLstatementsinPL/Iapplications thatuseSQL . . . 81

Example:EmbeddingSQLstatementsinPL/I applicationsthatuseSQL. . . 81 CommentsinPL/IapplicationsthatuseSQL . . 81 ContinuationforSQLstatementsinPL/I

applicationsthatuseSQL. . . 81 IncludingcodeinPL/IapplicationsthatuseSQL 81 MarginsinPL/IapplicationsthatuseSQL. . . 82 NamesinPL/IapplicationsthatuseSQL . . . 82 StatementlabelsinPL/Iapplicationsthatuse SQL. . . 82 WHENEVERStatementinPL/Iapplicationsthat useSQL . . . 82 UsinghostvariablesinPL/Iapplicationsthatuse SQL. . . 82

DeclaringhostvariablesinPL/Iapplicationsthat useSQL . . . 82 UsinghoststructuresinPL/Iapplicationsthatuse SQL. . . 87

HoststructuresinPL/IapplicationsthatuseSQL 87 HoststructureindicatorarraysinPL/I

applicationsthatuseSQL. . . 88 UsinghoststructurearraysinPL/Iapplicationsthat useSQL . . . 89

HoststructurearrayinPL/Iapplicationsthatuse SQL. . . 89 UsingexternalfiledescriptionsinPL/Iapplications thatuseSQL . . . 91

UsingindicatorvariablesinPL/Iapplicationsthat useSQL . . . 93 DifferencesinPL/Ibecauseofstructureparameter passingtechniques . . . 94

Chapter

8. Coding

SQL

statements

in

RPG

for

iSeries

applications

.

. 95

DefiningtheSQLCommunicationsAreainRPGfor iSeriesapplicationsthatuseSQL . . . 95 DefiningSQLDescriptorAreasinRPGforiSeries applicationsthatuseSQL. . . 96 EmbeddingSQLstatementsinRPGforiSeries

applicationsthatuseSQL. . . 97 Example:EmbeddingSQLstatementsinRPGfor iSeriesapplicationsthatuseSQL . . . 97 CommentsinRPGforiSeriesapplicationsthat useSQL . . . 97 ContinuationforSQLstatementsinRPGfor

iSeriesapplicationsthatuseSQL . . . 97 IncludingcodeinRPGforiSeriesapplications thatuseSQL. . . 98 SequencenumbersinRPGforiSeries

applicationsthatuseSQL. . . 98 NamesinRPGforiSeriesapplicationsthatuse SQL. . . 98 StatementlabelsinRPGforiSeriesapplications thatuseSQL. . . 98 WHENEVERstatementinRPGforiSeries

applicationsthatuseSQL. . . 98 UsinghostvariablesinRPGforiSeriesapplications thatuseSQL. . . 98

DeclaringhostvariablesinRPGforiSeries

applicationsthatuseSQL. . . 98 UsinghoststructuresinRPGforiSeriesapplications thatuseSQL. . . 99 UsinghoststructurearraysinRPGforiSeries

applicationsthatuseSQL . . . 100 UsingexternalfiledescriptionsinRPGforiSeries applicationsthatuseSQL . . . 100

Externalfiledescriptionconsiderationsforhost structurearraysinRPGforiSeriesapplications thatuseSQL . . . 101 DeterminingequivalentSQLandRPGforiSeries datatypes . . . 102

AssignmentrulesinRPGforiSeriesapplications thatuseSQL . . . 104 UsingindicatorvariablesinRPGforiSeries

applicationsthatuseSQL . . . 104 Example:UsingindicatorvariablesinRPGfor iSeriesapplicationsthatuseSQL. . . 105 DifferencesinRPGforiSeriesbecauseofstructure parameterpassingtechniques. . . 105 CorrectlyendingacalledRPGforiSeriesprogram thatusesSQL . . . 105

Chapter

9. Coding

SQL

statements

in

ILE

RPG

for

iSeries

applications

.

. 107

DefiningtheSQLCommunicationsAreainILE

(7)

DefiningSQLDescriptorAreasinILERPGfor

iSeriesapplicationsthatuseSQL. . . 108 EmbeddingSQLstatementsinILERPGforiSeries applicationsthatuseSQL . . . 109

CommentsinILERPGforiSeriesapplications thatuseSQL . . . 110 ContinuationforSQLstatementsinILERPGfor iSeriesapplicationsthatuseSQL. . . 110 IncludingcodeinILERPGforiSeries

applicationsthatuseSQL . . . 110 UsingdirectivesinILERPGforiSeries

applicationsthatuseSQL . . . 111 SequencenumbersinILERPGforiSeries

applicationsthatuseSQL . . . 111 NamesinILERPGforiSeriesapplicationsthat useSQL . . . 111 StatementlabelsinILERPGforiSeries

applicationsthatuseSQL . . . 111 WHENEVERstatementinILERPGforiSeries applicationsthatuseSQL . . . 111 UsinghostvariablesinILERPGforiSeries

applicationsthatuseSQL . . . 111 DeclaringhostvariablesinILERPGforiSeries applicationsthatuseSQL . . . 112 UsinghoststructuresinILERPGforiSeries

applicationsthatuseSQL . . . 116 UsinghoststructurearraysinILERPGforiSeries applicationsthatuseSQL . . . 118 UsingexternalfiledescriptionsinILERPGfor

iSeriesapplicationsthatuseSQL. . . 119 Externalfiledescriptionconsiderationsforhost structurearraysinILERPGforiSeries

applicationsthatuseSQL . . . 120 DeterminingequivalentSQLandILERPGdata

types . . . 120 NotesonILERPGforiSeriesvariable

declarationandusage . . . 126 UsingindicatorvariablesinILERPGforiSeries

applicationsthatuseSQL . . . 126 Example:UsingindicatorvariablesinILERPG foriSeriesapplicationsthatuseSQL. . . 127 ExampleoftheSQLDAforamultiplerow-area

fetchinILERPGforiSeriesapplicationsthatuse SQL . . . 127 ExampleofdynamicSQLinanILERPGforiSeries applicationthatusesSQL . . . 128

Chapter

10. Coding

SQL

statements

in

REXX

applications

.

. 131

UsingtheSQLCommunicationsAreainREXX

applications. . . 131 UsingSQLDescriptorAreasinREXXapplications 132 EmbeddingSQLstatementsinREXXapplications 133 CommentsinREXXapplicationsthatuseSQL 135 ContinuationofSQLstatementsinREXX

applicationsthatuseSQL . . . 135 IncludingcodeinREXXapplicationsthatuse SQL . . . 135 MarginsinREXXapplicationsthatuseSQL . . 135 NamesinREXXapplicationsthatuseSQL . . 135

StatementlabelsinREXXapplicationsthatuse SQL . . . 135 HandlingerrorsandwarningsinREXX

applicationsthatuseSQL . . . 135 UsinghostvariablesinREXXapplicationsthatuse SQL . . . 136

Determiningdatatypesofinputhostvariables inREXXapplicationsthatuseSQL . . . 136 TheformatofoutputhostvariablesinREXX

applicationsthatuseSQL . . . 137 AvoidingREXXconversioninREXX

applicationsthatuseSQL . . . 138 UsingindicatorvariablesinREXXapplicationsthat useSQL . . . 138

Chapter

11. Preparing

and

running

a

program

with

SQL

statements

.

. 139

BasicprocessesoftheSQLprecompiler. . . 139 InputtotheSQLprecompiler. . . 140 SourcefileCCSIDsintheSQLprecompiler . . 140 OutputfromtheSQLprecompiler . . . 141 Non-ILESQLprecompilercommands . . . 145

Compilinganon-ILEapplicationprogramthat usesSQL. . . 146 ILESQLprecompilercommands. . . 146

CompilinganILEapplicationprogramthatuses SQL . . . 147 Settingcompileroptionsusingtheprecompiler

commands . . . 148 Interpretingcompileerrorsinapplicationsthatuse SQL . . . 148

Errorandwarningmessagesduringacompile ofapplicationprogramsthatuseSQL . . . . 148 BindinganapplicationthatusesSQL . . . 149 ProgramreferencesinapplicationsthatuseSQL 150 DisplayingSQLprecompileroptions . . . 150 RunningaprogramwithembeddedSQL . . . . 150

RunningaprogramwithembeddedSQL:

OS/400DDMconsiderations . . . 151 RunningaprogramwithembeddedSQL:

overrideconsiderations . . . 151 RunningaprogramwithembeddedSQL:SQL returncodes. . . 151

Chapter

12. Sample

programs

using

DB2

UDB

for

iSeries

statements

.

. 153

Example:SQLStatementsinILECandC++

Programs. . . 155 Example:SQLStatementsinCOBOLandILE

COBOLPrograms. . . 160 Example:SQLStatementsinPL/I . . . 168 Example:SQLStatementsinRPGforiSeries

Programs. . . 173 Example:SQLStatementsinILERPGforiSeries Programs. . . 179 Example:SQLStatementsinREXXPrograms. . . 185 Reportproducedbysampleprogramsthatuse

SQL . . . 189 |

| |

(8)

Chapter

13. DB2

UDB

for

iSeries

CL

command

descriptions

for

host

language

precompilers

.

. 191

CRTSQLCBL(CreateStructuredQueryLanguage COBOL)Command . . . 191

CRTSQLCBLI(CreateSQLILECOBOLObject) Command . . . 191

CRTSQLCI(CreateStructuredQueryLanguageILE CObject)Command . . . 191

CRTSQLCPPI(CreateStructuredQueryLanguage C++Object)Command . . . 191

CRTSQLPLI(CreateStructuredQueryLanguage PL/I)Command . . . 192

CRTSQLRPG(CreateStructuredQueryLanguage RPG)Command . . . 192

CRTSQLRPGI(CreateSQLILERPGObject) Command . . . 192

Appendix.

Notices

.

. 193

ProgrammingInterfaceInformation. . . 195

Trademarks . . . 195

Termsandconditionsfordownloadingand printingpublications. . . 195

(9)

Chapter

1. Embedded

SQL

programming

Thistopicexplainshow tocreatedatabaseapplicationsinhostlanguagesthatuseDB2®UDB foriSeries™ SQLstatementsandfunctions.

Seethefollowingtopicsfordetailedinformationaboutembedded SQLprogramming: What’snewforV5R3

SeethechangesandadditionstotheEmbeddedSQLProgramming topicforthisrelease. Printthistopic

Printthistopictoviewa hardcopyofEmbeddedSQLProgramming. CommonconceptsandrulesforusingembeddedSQL

Becomefamiliarwith conceptsand rulesthatapplytousinghostvariables,tohandlingSQLerror andreturncodes, andtohandlingexceptionconditions withtheWHENEVERstatementin embeddedSQLprogramming.

CodingSQLStatementsin CandC++Applications

Learntheuniqueapplication andcoding requirementsforembeddingSQLstatementsinCorC++ programs.

CodingSQLStatementsin COBOLApplications

Learntheuniqueapplication andcoding requirementsforembeddingSQLstatementsinCOBOL programs.

CodingSQLStatementsin PL/IApplications

Learntheuniqueapplication andcoding requirementsforembeddingSQLstatementsiniSeries PL/Iprograms.

CodingSQLStatementsin RPGforiSeriesApplications

Learntheuniqueapplication andcoding requirementsforembeddingSQLstatementsinRPG for iSeriesprograms.

CodingSQLStatementsin ILERPGforiSeriesApplications

Learntheuniqueapplication andcoding requirementsforembeddingSQLstatementsinanILE RPGforiSeriesprogram.

CodingSQLStatementsin REXXApplications

LearnhowtoembedSQLstatementsinREXXapplications. PreparingandRunninga ProgramwithSQLStatements

Seesomeofthetasksforpreparingandrunninganapplicationprogram. SampleProgramsUsingDB2UDBforiSeriesStatements

Seesampleapplicationsshowinghow tocodeSQLstatementsineachofthelanguagessupported bytheDB2UDB foriSeriessystem.

DB2UDBforiSeriesCLcommanddescriptionsforhostlanguageprecompilers

Viewdescriptionsofcommandsusetoprecompileprogramswrittenintheprogramming languages discussedinEmbedded SQLProgramming.

(10)

(11)

Chapter

2. What’s

new

for

Version

5 Release

3 in

the

Embedded

SQL

programming

topic

v _This_topic_was_previously_a_manual_titled_SQL_Programming_with_Host_Languages. v _Using_the_SQL_Diagnostics_Area_to_handle_SQL_error_return _codes.

v _BINARY_and _VARBINARY_host_variables_in_the_C,_C++,_COBOL, _ILE_RPG,_and_PL/I _languages. v _Addition_of_standard_SQL_variable_names_for_SQL_{Communications}_Area_for_ILE _RPG.

v _Rules_for_host_variables_in_ILE_RPG.

v _{Preprocessing}_ILE_RPG_source_to_handle_directives,_including_support _for_nested_/COPY_statements. v _The_COMPILEOPT_parameter_in_Setting_the_compiler_options_using_the_precompiler_commands.

Howtoseewhat’sneworchanged

Tohelpyouseewheretechnicalchanges havebeen made,this informationuses: v _The _image_to_mark_where_new_or_changed_information_begins.

v _The _image_to_mark_where_new_or_changed_information_ends.

(12)

(13)

Chapter

3. Print

this

topic

ToviewordownloadthePDFversionofthisdocument,selectEmbedded SQLProgrammingPDF(about 1529KB).

Youcanview ordownloadtheserelatedtopics:

v _SQL_reference_(10,209 _KB)_contains_the_following_information:

– Referenceinformationforthetasksof systemadministration,databaseadministration,application programming,andoperation.

– Syntax,usage notes,keywords,andexamples foreachoftheSQLstatementsusedontheiSeries system.

v _SQL_programming₍₃₃₄₁_KB)_contains _the_following_information: – HowtousetheDB2UDB foriSerieslicensedprogram – Howtoaccessdataina database

– Howtoprepare,run,andtestan applicationprogramthatcontainsSQLstatements. v _Database_Performance_and_Query _Optimization₍₃₂₆₂_KB)_contains_the_following_information:

– Howtousethetoolsandfunctionsthatareavailable inDB2UDBforiSeries forgettingthebest performanceoutofyour databaseapplications.

– Howtorunqueries thatmakefulluseofthecapabilitiesoftheDB2 UDBforiSeriesintegrated database.

v _SQL_Call_Level_Interface_(ODBC)₍₂₄₂₉_KB)_contains_the_following_information: – UsingDB2UDB CLIasanalternativetoembedded dynamicSQL

– Descriptionsandexamples ofDB2UDB CLIfunctions. SavingPDFfiles

Tosavea PDFonyour workstationforviewingorprinting:

1. Right-clickthePDFinyourbrowser(right-click thelinkabove).

2. Click SaveTarget As...ifyouareusingInternetExplorer.ClickSave LinkAs...ifyouareusing Netscape Communicator.

3. Navigatetothedirectoryinwhichyouwould liketosavethePDF. 4. Click Save.

DownloadingAdobeAcrobatReader

YouneedAdobeAcrobatReadertovieworprintthesePDFs.Youcandownloada copyfromtheAdobe Website .

(14)

(15)

Chapter

4. Common

concepts

and

rules

for

using

embedded

SQL

Thistopicdescribes someconceptsandrulesthatarecommonto usingSQLstatements inahost languagethatinvolve:

v _“Writing_applications_that_use_SQL” v _“Using_host_variables_in_SQL_{statements”}

v _“Handling_SQL_error_return_codes _using_the_SQLCA”_on_page₁₃ v _“Using_the_SQL_diagnostics_area” _on_page₁₄

v _“Handling_exception_conditions_with _the_WHENEVER_Statement”_on_page₁₆

Note: Readthe“Codedisclaimerinformation” onpage196forimportantlegalinformation.

Writing

applications

that

use

SQL

Youcancreatedatabase applicationsinhost languagesthatuseDB2UDB foriSeriesSQLstatementsand functions.TouseembeddedSQL,youmust havetheDB2Query ManagerandSQLDevelopmentKit installed.Additionally,youmust havethecompilersforthehostlanguagesyouwanttouseinstalled. Selectthefollowingfor moreinformationaboutapplication requirementsandcodingrequirementsfor eachofthehostlanguages:

v _Chapter_5, _“Coding_SQL_statements_in_C_and _C++_{applications,”}_on_page₁₉ v _Chapter_6, _“Coding_SQL_statements_in_COBOL_{applications,”}_on_page₅₁ v _Chapter_7, _“Coding_SQL_statements_in_PL/I_{applications,”}_on_page₇₉

v _Chapter_8, _“Coding_SQL_statements_in_RPG_for_iSeries_{applications,”}_on_page₉₅ v _Chapter_9, _“Coding_SQL_statements_in_ILE_RPG _for_iSeries_{applications,”}_on_page₁₀₇ v _Chapter_10,_“Coding_SQL_statements_in_REXX_{applications,”}_on_page₁₃₁

v _Chapter_11,_“Preparing_and _running_a _program_with_SQL_{statements,”}_on_page₁₃₉

Note: For informationaboutusingJava™_as_a _host_language,_see_the_IBM® _Developer_Kit_for_Java.

Using

host

variables

in

SQL

statements

Whenyour programretrieves data,thevaluesareputintodataitemsdefinedbyyour programand specifiedwiththeINTOclauseofa SELECTINTOorFETCHstatement.Thedataitemsarecalled host variables.

Ahostvariableisafieldinyour programthatisspecifiedinanSQLstatement,usuallyasthesourceor targetforthevalueof acolumn.Thehostvariableand columnmustbe datatypecompatible.Host variablesmaynotbe usedtoidentifySQLobjects,suchastablesorviews,exceptin theDESCRIBE TABLEstatement.

Ahoststructureisagroup ofhostvariablesusedasthesourceortargetfora setofselectedvalues(for example,theset ofvaluesforthecolumns ofa row).Ahoststructurearrayisanarrayof hoststructures usedinthemultiple-rowFETCHandblockedINSERTstatements.

Note: By usinga hostvariableinstead ofaliteralvalueinanSQLstatement,yougivetheapplication programtheflexibilityitneedstoprocessdifferentrowsina tableorview.

(16)

Forexample,insteadofcoding anactualdepartmentnumberin aWHEREclause,youcanusea host variableset tothedepartmentnumberyouarecurrentlyinterestedin.

Hostvariablesare commonlyusedinSQLstatements intheseways:

1. Ina WHEREclause: Youcanuseahostvariable tospecifyavalue inthepredicate ofasearch

condition,ortoreplace aliteralvalueinan expression.Forexample,ifyouhavedefineda fieldcalled EMPIDthatcontains anemployeenumber,youcanretrievethenameoftheemployeewhosenumber is000110with: MOVE ’000110’ TO EMPID. EXEC SQL SELECT LASTNAME INTO :PGM-LASTNAME FROM CORPDATA.EMPLOYEE WHERE EMPNO = :EMPID END-EXEC.

2. Asareceivingarea forcolumnvalues(namedinanINTOclause):Youcanusea hostvariableto specifya programdataareathatistocontainthecolumnvaluesofaretrievedrow.TheINTOclause namesoneor morehostvariablesthatyouwanttocontaincolumnvaluesreturnedbySQL.For example,supposeyouareretrievingtheEMPNO,LASTNAME,and WORKDEPTcolumn valuesfrom rowsintheCORPDATA.EMPLOYEEtable.Youcoulddefineahostvariable inyourprogramtohold eachcolumn,then namethehostvariableswithan INTOclause.Forexample:

EXEC SQL

SELECT EMPNO, LASTNAME, WORKDEPT INTO :CBLEMPNO, :CBLNAME, :CBLDEPT FROM CORPDATA.EMPLOYEE

WHERE EMPNO = :EMPID END-EXEC.

Inthis example,thehostvariable CBLEMPNOreceivesthevaluefromEMPNO, CBLNAMEreceives thevalue fromLASTNAME,andCBLDEPTreceivesthevalue fromWORKDEPT.

3. Asavalue inaSELECT clause:Whenspecifyinga listofitemsintheSELECTclause,youarenot restrictedtothecolumn namesof tablesandviews.Yourprogramcanreturna setofcolumnvalues intermixedwith hostvariablevaluesandliteralconstants.Forexample:

MOVE ’000220’ TO PERSON. EXEC SQL

SELECT "A", LASTNAME, SALARY, :RAISE, SALARY + :RAISE

INTO :PROCESS, :PERSON-NAME, :EMP-SAL, :EMP-RAISE, :EMP-TTL

FROM CORPDATA.EMPLOYEE WHERE EMPNO = :PERSON END-EXEC.

Theresultsare:

PROCESS PERSON-NAME EMP-SAL EMP-RAISE EMP-TTL

A LUTZ 29840 4476 34316

4. Asavalue inotherclausesofanSQLstatement: TheSETclauseinanUPDATE statement TheVALUESclauseinanINSERTstatement TheCALLstatement

Formoreinformationaboutthesestatements,seetheSQLReferencetopic. Formoreinformationaboutusinghostvariables,seethefollowingsections:

(17)

v _“Indicator_variables_used_with _host_{structures”}_on_page₁₂

Assignment

rules

for

host

variables

in

SQL

statements

SQLvaluesareassignedtohostvariablesduringtherunningofFETCH,SELECTINTO,SET,and VALUESINTOstatements.SQLvaluesare assignedfromhost variablesduring therunningofINSERT, UPDATE,andCALLstatements.All assignmentoperationsobservethefollowingrules:

v _Numbers_and _strings_are_compatible:

Numbers canbeassignedtocharacterorgraphicstringcolumnsorhostvariables.

Characterand graphicstringscanbeassignedtonumericcolumnsornumerichost variables. v _All_character_and_DBCS _graphic_strings_are_compatible_with_UCS-2_and_UTF-16_graphic_columns _if

conversionissupportedbetweentheCCSIDs.Allgraphicstringsare compatibleiftheCCSIDsare compatible.Allnumericvaluesarecompatible.Conversionsare performedbySQLwhenever necessary.AllcharacterandDBCSgraphic stringsarecompatiblewith UCS-2and UTF-16graphic columnsforassignmentoperations,ifconversionissupportedbetweentheCCSIDs.FortheCALL statement,characterand DBCSgraphicparametersarecompatiblewithUCS-2and UTF-16parameters ifconversionissupported.

v _Binary_strings_are_only_compatible_with_binary _strings.

v _A_null_value _cannot_be _assigned_to_a_host_variable _that_does_not_have_an_associated_indicator_variable. v _Different_types_of_date/time_values_are_not_compatible._Dates _are_only_compatible_with _dates_or_string representationsofdates;timesare onlycompatiblewithtimesorstringrepresentationsof times;and timestampsareonlycompatiblewith timestampsor stringrepresentationsoftimestamps.

Adatecanbe assignedonlytoa datecolumn,a charactercolumn,a DBCS-openorDBCS-either columnorvariable,ora charactervariable1.Theinsertorupdatevalueofa datecolumn mustbea dateorastringrepresentationofa date.

Atimecanbe assignedonlytoa timecolumn,acharactercolumn,a DBCS-openorDBCS-either columnorvariable,ora charactervariable.Theinsertorupdatevalue ofatimecolumnmustbe atime orastringrepresentationofa time.

Atimestampcanbe assignedonlytoatimestampcolumn,acharactercolumn,aDBCS-open or DBCS-eithercolumnorvariable, oracharactervariable.Theinsert orupdatevalue ofa timestamp columnmust beatimestamporastringrepresentationofa timestamp.

Seethefollowingtopicsformoreinformationonassignmentrules: v _“Rules_for_string_assignment_of _host_variables_in _SQL_{statements”} v _“Rules_for_CCSIDs_of _host_variables_in _SQL_{statements”}_on_page₁₀

v _“Rules_for_numeric_assignment_of_host_variables_in_SQL_{statements”}_on_page₁₀

v _“Rules_for_date, _time,_and_timestamp_assignment_of _host_variables_in_SQL_{statements”}_on_page₁₁ v _“Indicator_variables_in_applications_that_use_SQL”_on_page₁₁

Rules

for

string

assignment

of

host

variables

in

SQL

statements

Rulesregardingcharacterstringassignmentare:

v _When_a _character_or_graphic_string_is_assigned_to_a _column,_the_length _of_the_string_value_must _not_be greaterthanthelengthattributeofthecolumn.(Trailingblanks arenormallyincludedinthelengthof thestring.However,forstringassignment,trailing blanksarenotincludedinthelength ofthestring.) v _When_a _binary_string_is_assigned_to_a _column,_the_length_of_the_string_value_must _not_be _greater_than

thelengthattributeofthecolumn.(Hexadecimal zerosare normallyincludedinthelength ofthe string.However, forstringassignment,hexadecimal zerosarenotincludedinthelengthofthestring.)

1. ADBCS-openorDBCS-eithervariableisavariablethatwasdeclaredinthehostlanguagebyincludingthedefinitionofan externallydescribedfile.DBCS-openvariablesarealsodeclaredifthejobCCSIDindicatesMIXEDdata,ortheDECLARE VARIABLEstatementisusedandaMIXEDCCSIDortheFORMIXEDDATAclauseisspecified.SeeDECLAREVARIABLEinthe

| | | | | | | | | | |

(18)

v _When_a_MIXED_character_result_column_is_assigned_to_a_MIXED_column,_the_value_of_the_MIXED characterresultcolumnmust bea validMIXEDcharacterstring.

v _When_the_value _of_a_result _column_is_assigned_to_a _host_variable_and _the_string_value _of_the_result columnislongerthanthelengthattributeofthehostvariable,thestringistruncatedontheright by thenecessarynumberofcharacters.Ifthisoccurs, SQLWARN0andSQLWARN1(intheSQLCA)areset toW.

v _When_the_value _of_a_result _column_is_assigned_to_a _fixed-length_character_or_graphic _host_variable_or whenthevalueofa hostvariableisassignedtoafixed-lengthcharacterorgraphicresult columnand thelengthofthestringvalue isless thanthelengthattributeofthetarget,thestringispaddedonthe rightwith thenecessarynumber ofblanks.

v _When_the_value _of_a_result _column_is_assigned_to_a _fixed-length_binary_host _variable_or_when_the_value ofa hostvariableisassignedtoafixed-lengthbinaryresultcolumnand thelengthof thestringvalue islessthanthelength attributeof thetarget,thestringispaddedontherightwith thenecessary numberofhexadecimal zeroes.

v _When_a_MIXED_character_result_column_is_truncated_because_the_length _of_the_host_variable_into_which itwas beingassignedwasless thanthelengthofthestring,theshift-in characterat theendof the stringispreserved.Theresult,therefore,isstilla validMIXEDcharacterstring.

Rules

for

CCSIDs

of

host

variables

in

SQL

statements

CCSIDsmustbe consideredwhenyouassignonecharacterorgraphic valuetoanother.Thisincludesthe assignmentofhost variables.Thedatabasemanager usesacommonset ofsystemservicesforconverting SBCSdata,DBCSdata,MIXEDdata,and graphicdata.

TherulesforCCSIDsareasfollows:

v _If_the_CCSID_of _the_source_matches _the_CCSID_of_the_target,_the_value_is_assigned_without_conversion. v _If_the_sub-type_for_the_source_or_target_is_BIT,_the_value _is_assigned_without_conversion.

v _If_the_value_is_either_null _or_an_empty_string, _the_value _is_assigned_without_conversion.

v _If_conversion_is_not_defined_between_specific_CCSIDs,_the_value_is_not_assigned_and _an_error_message_is issued.

v _If_conversion_is_defined_and_needed, _the_source_value _is_converted _to_the_CCSID_of_the_target_before theassignmentisperformed.

FormoreinformationaboutCCSIDs,seetheGlobalizationtopicintheInformationCenter.

Rules

for

numeric

assignment

of

host

variables

in

SQL

statements

Rulesregardingnumericassignmentare:

v _The_whole_part_of _a_number_may_be_altered_when_converting_it_to _{floating-point}_._A_{single-precision} floating-pointfieldcanonlycontainsevendecimal digits.Anywholepartofa numberthatcontains morethansevendigitsisalteredduetorounding.Adouble-precisionfloatingpointfieldcanonly contain16decimaldigits.Anywholepart ofanumber thatcontains morethan16digitsisaltereddue torounding.

v _The_whole_part_of _a_number_is_never_truncated._If_necessary, _the_fractional_part _of_a_number_is truncated.Ifthenumber,asconverted,doesnotfit intothetargethostvariableor column,a negative SQLCODEisreturned.

v _Whenever_a _decimal,_numeric, _or_integer_number_is_assigned_to_a_decimal,_numeric,_or_integer_column orhostvariable,thenumberisconverted,ifnecessary, totheprecision andscaleofthetarget.The necessarynumberof leadingzerosisaddedordeleted;inthefractionalpartof thenumber,the necessarynumberof trailingzerosisadded,orthenecessarynumberof trailingdigitsiseliminated. v _When_an_integer_or_{floating-point}_number_is_assigned_to_a _decimal_or_numeric_column_or_host

variable,thenumberisfirstconverted toatemporarydecimal ornumericnumberandthen converted, ifnecessary,to theprecision andscaleofthetarget.

– Whena halfwordbinaryinteger(SMALLINT)with0scaleisconvertedtodecimal ornumeric,the temporaryresulthasaprecision of5anda scaleof0.

| | | |

(19)

– Whena fullwordbinaryinteger(INTEGER) isconverted todecimalornumeric,thetemporary resulthasaprecisionof 11andascaleof 0.

– Whena doublefullwordbinaryinteger (BIGINT)isconvertedtoadecimal ornumeric,the temporaryresulthasaprecisionof 19andascaleof0.

– Whena floating-pointnumberisconvertedtodecimal ornumeric,thetemporaryresult hasa precisionof31andthemaximumscalethatallows thewholepartof thenumber toberepresented withoutlossofeither significanceoraccuracy.

Rules

for

date,

time,

and

timestamp

assignment

of

host

variables

in

SQL

statements

Whena dateisassignedtoa hostvariable,thedateisconverted tothestringrepresentationspecifiedby theDATFMTand DATSEPparametersof theCRTSQLxxxcommand. Leadingzerosarenotomittedfrom anypart ofthedaterepresentation.Thehostvariable mustbe afixedor variable-lengthcharacterstring variablewith alengthofat least10bytesfor*USA,*EUR,*JIS,or*ISO dateformats,8 bytesfor*MDY, *DMY,or*YMDdateformats,or6 bytesforthe*JULdateformat. Ifthelength isgreaterthan10,the stringispaddedontherightwithblanks.InILERPG andILE COBOL,thehost variablecanalsobe a datevariable.

Whena timeisassignedtoa hostvariable,thetimeisconvertedtothestringrepresentation bythe TIMFMTandTIMSEPparametersoftheCRTSQLxxxcommand.Leadingzerosare notomitted.Thehost variablemust bea fixedorvariable-lengthcharacterstringvariable.Ifthelengthofthehostvariable is greaterthanthestringrepresentation ofthetime,thestringispaddedontherightwithblanks.InILE RPGand ILECOBOL,thehostvariablecanalsobe atimevariable.

v _If_the_*USA_format_is_used, _the_length_of _the_host_variable _must_not_be _less_than_8.

v _If_the_*HMS,_*ISO,_*EUR,_or_*JIS_format_is_used, _the_length_of_the_host_variable_must _be_at_least ₈_bytes ifsecondsaretobe included,and5 bytesifonlyhours andminutesareneeded.Inthiscase,

SQLWARN0andSQLWARN1(intheSQLCA)aresettoW, andifanindicatorvariable isspecified,itis settotheactualnumberofsecondstruncated.

Whena timestampisassignedtoahostvariable,thetimestampisconverted toitsstringrepresentation. Leadingzerosarenotomittedfromanypart.Thehostvariable mustbe afixed orvariable-length characterstringvariable withalength ofatleast19bytes.Ifthelengthislessthan26,thehostvariable doesnotinclude allthedigitsofthemicroseconds.Ifthelengthisgreaterthan26,thehostvariableis paddedontheright withblanks.InILERPG andILECOBOL, thehostvariable canalsobea timestamp variable.

Indicator

variables

in

applications

that

use

SQL

Anindicatorvariableisahalfwordintegervariableusedtoindicatewhetheritsassociatedhost variable hasbeen assigneda nullvalue:

v _If_the_value_for_the_result_column_is_null,_SQL_puts_a_-1 _in_the_indicator_variable.

v _If_you_do _not_use_an_indicator_variable_and _the_result _column_is_a_null _value,_a_negative_SQLCODE_is returned.

v _If_the_value_for_the_result_column_causes_a _data_mapping_error._SQL_sets_the_indicator_variable_to_−2.

Youcanalso useanindicatorvariable toverifythataretrievedstringvaluehasnotbeen truncated.If truncationoccurs, theindicatorvariable containsapositiveintegerthatspecifiestheoriginallength ofthe string.Ifthestringrepresentsa largeobject(LOB),andtheoriginallengthofthestringisgreaterthan 32767,thevaluethatisstoredintheindicatorvariableis32767,sincenolargervaluecanbe storedina halfwordinteger.

Whenthedatabasemanager returnsa valuefroma resultcolumn,youcantest theindicatorvariable.If thevalueoftheindicatorvariableislessthanzero,youknowthevalueoftheresultscolumnisnull. Whenthedatabasemanager returnsa nullvalue,thehostvariable willbe settothedefaultvalueforthe resultcolumn.

(20)

Youspecifyan indicatorvariable (precededbya colon)immediatelyafter thehostvariableor immediatelyafter thekeywordINDICATOR.Forexample:

EXEC SQL

SELECT COUNT(*), AVG(SALARY) INTO :PLICNT, :PLISAL:INDNULL FROM CORPDATA.EMPLOYEE WHERE EDLEVEL < 18 END-EXEC.

Youcanthen testINDNULLtoseeifitcontains anegativevalue.Ifitdoes,youknowSQLreturneda nullvalue.

Alwaystest forNULLinacolumnbyusingtheISNULL predicate.Forexample: WHERE expression IS NULL

Donottest forNULLinthis way: MOVE -1 TO HUIND.

EXEC SQL...WHERE column-name = :HUI :HUIND

TheEQUALpredicatewillalwaysbe evaluatedasfalse whenitcomparesanull value.Theresultofthis examplewillselectnorows.

TheDISTINCTpredicatecanbeusedtoperform comparisonswhennullvaluesmayexist.See Predicates intheSQLReferencetopicfor moreinformation.

Seethefollowingtopicsformoreinformationaboutindicatorvariables: v _“Indicator_variables_used_with _host_{structures”}

v _“Indicator_variables_used_to_set _null_values”_on_page₁₃

Indicator

variables

used

with

host

structures

Youcanalso specifyanindicatorstructure(definedasan arrayofhalfwordintegervariables)tosupport ahoststructure.If theresultscolumnvaluesreturnedtoahoststructure canbe null,youcanaddan indicatorstructure nametothehoststructure name.ThisallowsSQLtonotifyyour programabouteach nullvalue returnedtoa hostvariableinthehoststructure.

Forexample,inCOBOL: 01 SAL-REC.

10 MIN-SAL PIC S9(6)V99 USAGE COMP-3. 10 AVG-SAL PIC S9(6)V99 USAGE COMP-3. 10 MAX-SAL PIC S9(6)V99 USAGE COMP-3. 01 SALTABLE.

02 SALIND PIC S9999 USAGE COMP-4 OCCURS 3 TIMES. 01 EDUC-LEVEL PIC S9999 COMP-4.

...

MOVE 20 TO EDUC-LEVEL. ...

EXEC SQL

SELECT MIN(SALARY), AVG(SALARY), MAX(SALARY) INTO :SAL-REC:SALIND

FROM CORPDATA.EMPLOYEE WHERE EDLEVEL>:EDUC-LEVEL END-EXEC.

Inthisexample,SALINDisanarraycontaining 3values,eachofwhichcanbetestedforanegative value.If,forexample,SALIND(1)containsa negativevalue,then thecorrespondinghostvariableinthe hoststructure(thatis, MIN-SAL)isnotchangedfor theselectedrow.

| |

(21)

Intheaboveexample,SQLselectsthecolumn valuesof therow intoahoststructure.Therefore,you mustuseacorrespondingstructure fortheindicatorvariablestodeterminewhich(ifany) selected columnvaluesarenull.

Indicator

variables

used

to

set

null

values

Youcanuseanindicatorvariabletoset anull valueina column.WhenprocessingUPDATEorINSERT statements,SQLcheckstheindicatorvariable(if itexists).Ifitcontainsanegativevalue,thecolumn valueissettonull. Ifitcontainsavalue greaterthan-1,theassociatedhostvariable containsa valuefor thecolumn.

Forexample,youcanspecifythata valuebeputina column(usinganINSERTorUPDATEstatement), butyoumaynotbe surethatthevalue wasspecifiedwith theinputdata.Toprovidethecapabilitytoset acolumntoa nullvalue,youcanwritethefollowingstatement:

EXEC SQL

UPDATE CORPDATA.EMPLOYEE

SET PHONENO = :NEWPHONE:PHONEIND WHERE EMPNO = :EMPID

END-EXEC.

WhenNEWPHONE containsotherthananullvalue, setPHONEINDtozerobyprecedingthestatement with:

MOVE 0 to PHONEIND.

Otherwise,totellSQLthatNEWPHONE containsanull value,setPHONEINDtoa negativevalue,as follows:

MOVE -1 TO PHONEIND.

Handling

SQL

error

return

codes

using

the

SQLCA

WhenanSQLstatementisprocessedinyourprogram, SQLplacesareturn codeintheSQLCODEand SQLSTATEfields.Thereturn codesindicatethesuccessorfailureoftherunningofyourstatement. If SQLencountersanerrorwhileprocessingthestatement, theSQLCODEisanegativenumberand SUBSTR(SQLSTATE,1,2)isnot'00','01',or'02'.IfSQLencountersanexceptionbutvalidconditionwhile processingyour statement,theSQLCODEisapositivenumberandSUBSTR(SQLSTATE,1,2)is'01'or'02'. IfyourSQLstatementisprocessedwithoutencounteringanerrororwarningcondition,theSQLCODEis zeroand theSQLSTATEis'00000'.

Note: Therearesituationswhenazero SQLCODEisreturnedtoyour programand theresult mightnot be satisfactory.Forexample,ifa valuewastruncatedasaresult ofrunningyour program,the SQLCODEreturnedtoyourprogramiszero.However,oneoftheSQLwarningflags

(SQLWARN1)indicatestruncation.Inthiscase, theSQLSTATEisnot'00000'.

Attention: IfyoudonottestfornegativeSQLCODEsorspecifyaWHENEVERSQLERRORstatement, yourprogramwillcontinuetothenextstatement. Continuingtorunafter anerrorcanproduce

unpredictableresults.

ThemainpurposeforSQLSTATEistoprovidecommonreturn codesforcommonreturn conditions amongthedifferentIBMrelationaldatabasesystems. SQLSTATEsareparticularlyusefulwhen handling problemswithdistributed databaseoperations.Formore information,seetheSQLReferencetopic. BecausetheSQLCAisa valuableproblem-diagnosistool, itisagoodideatoincludein yourapplication programstheinstructionsnecessarytodisplaysomeoftheinformationcontainedintheSQLCA.

EspeciallyimportantarethefollowingSQLCAfields:

SQLCODE Returncode.

(22)

SQLERRD(3) Thenumber ofrowsupdated,inserted,ordeletedbySQL.

SQLWARN0 Ifset toW,atleast oneoftheSQLwarningflags(SQLWARN1through SQLWARNA)isset.

FormoreinformationabouttheSQLCA,seeSQLCommunicationAreaintheSQLreference topic.To findaspecific SQLCODEorSQLSTATE,usetheSQLMessagefinder.For alisting ofDB2UDBforiSeries SQLCODEsandSQLSTATEs,seeSQLmessagesand codes.

Using

the

SQL

diagnostics

area

TheSQLdiagnosticsareaisusedtokeepthereturnedinformationforanSQLstatementthathasbeen runina program.Itcontainsall theinformationthatisavailabletoyouasanapplicationprogrammer throughtheSQLCA.Thereareadditionalvaluesavailabletoprovidemoredetailedinformationabout yourSQLstatementincludingconnectioninformation.Morethanone conditioncanbe returnedfroma singleSQLstatement. TheinformationintheSQLdiagnosticsareaisavailableforthepreviousSQL statementuntilthenextSQLstatementisrun.

Toaccesstheinformationfromthediagnosticsarea,usetheGETDIAGNOSTICSstatement. Inthis statement,youcanrequestmultiple piecesofinformationat onetimeaboutthepreviouslyrunSQL statement.Eachitemisreturnedina hostvariable.Youcanalso requesttoget astringthatcontainsall thediagnosticinformationthatisavailable.RunningtheGETDIAGNOSTICSstatementdoesnotclear thediagnosticsarea.

Seethefollowingtopics:

v _“Updating_applications_to_use_the_SQL_diagnostics_area” v _“iSeries_server_programming_model”_on_page₁₅

v _{“Additional}_notes_on_using_the_SQL_diagnostics_area” _on_page₁₅ v _“Example:_SQL_routine_exception” _on_page₁₅

v _“Example:_Logging _items_from_the_SQL_diagnostics_area” _on_page₁₆

Updating

applications

to

use

the

SQL

diagnostics

area

TherearemanyreasonsforreplacingtheSQLCAinapplications.Oneofthebestreasonsisthatthe SQLERRMfieldintheSQLCAisonly70bytesinlength.Thisisofteninsufficientforreturning meaningfulerrorinformationtothecallingapplication.Additional reasonsforconsideringtheSQL diagnosticsareaare multiplerowoperations,andlongcolumnandobjectnames.Reportingevensimple warningsissometimesdifficultwithin therestrictions ofthe136byteSQLCA.Quite often,thereturned tokensare truncatedtofit therestrictions oftheSQLCA.

CurrentapplicationsincludetheSQLCAdefinitionbyusingthefollowing: EXEC SQL INCLUDE SQLCA; /* Existing SQLCA */

WiththeconversiontousingtheSQLdiagnosticsarea,theapplicationwould firstdeclareastand-alone SQLSTATEvariable:

char SQLSTATE[6]; /* Stand-alone sqlstate */ Andpossiblya stand-aloneSQLCODEvariable:

long int SQLCODE; /* Stand-alone sqlcode */

Thecompletionstatusof theSQLstatementisverifiedbycheckingthestand-aloneSQLSTATEvariable.If uponthecompletionofthecurrentSQLstatement, theapplication choosestoretrievediagnostics,the applicationwouldruntheSQLGETDIAGNOSTICSstatement:

| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

(23)

char hv1[256]; long int hv2;

EXEC SQL GET DIAGNOSTICS :hv1 = COMMAND_FUNCTION, :hv2 = COMMAND_FUNCTION_CODE;

iSeries

server

programming

model

IntheiSeriesIntegratedLanguageEnvironment®(ILE),theSQLdiagnosticsareaisscoped toathread andanactivation group.Thismeansthatforeachactivationgroup inwhichathreadrunsSQL statements,aseparatediagnosticsareawillexistfortheactivation.

Additional

notes

on

using

the

SQL

diagnostics

area

Inanapplicationprogram, theSQLCAisreplacedwithanimplicit orstand-aloneSQLSTATEvariable, whichmustbe declaredintheprogram. WithmultipleconditionareasexistingintheSQLdiagnostics area,themostsevereerrororwarningisreturnedinthefirst diagnosticsarea.Thereisnospecific orderingofthemultipleconditions, exceptthatthefirstdiagnosticsareawillcontaintheinformationfor theSQLSTATEthatisalsoreturnedintheSQLSTATEvariable.

WiththeSQLCA,theapplicationprogramprovidesthestorage fortheSQLCAthatisusedto communicatetheresultsoftherunofanSQLstatement.WiththeSQLdiagnosticsarea,thedatabase managermanagesthestorageforthediagnostics,andtheGETDIAGNOSTICSstatementisprovidedto retrievethecontentsofthediagnosticsarea.

NotethattheSQLCAwillcontinuetobe supportedforapplicationprograms.Also, theGET DIAGNOSTICSstatementcanbeusedinanapplicationprogramthatusestheSQLCA.

Example:

SQL

routine

exception

Inthefollowingapplication example,a storedproceduresignalsanerrorwhenaninputvalueisoutof range:

EXEC SQL CREATE PROCEDURE check_input (IN p1 INT) LANGUAGE SQL READS SQL DATA

test: BEGIN IF p1< 0 THEN

SIGNAL SQLSTATE VALUE ’99999’

SET MESSAGE_TEXT = ’Bad input value’; END IF

END test;

Thecallingapplicationchecksfora failureandretrievestheinformationaboutthefailurefromtheSQL diagnosticsarea:

char SQLSTATE[6]; /* Stand-alone sqlstate */ long int SQLCODE; /* Stand-alone sqlcode */ long int hv1;

char hv2[6]; char hv3[256]; hv1 = -1;

EXEC SQL CALL check_input(:hv1); if (strncmp(SQLSTATE, "99999", 5) == 0) {

EXEC SQL GET DIAGNOSTICS CONDITION 1 :hv2 = RETURNED_SQLSTATE, :hv3 = MESSAGE_TEXT; } else { } | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

(24)

Example:

Logging

items

from

the

SQL

diagnostics

area

Inthisexample,anapplicationneedstologallerrorsforsecurityreasons.Thelogcouldbe usedto monitorthehealthofa systemortomonitor forinappropriateuseofa database.

ForeachSQLerrorthatoccurs, anentryisplacedinthelog.Theentryincludeswhentheerroroccurred, whatuser wasusingtheapplication,whattype ofSQLstatementwasrun,thereturnedSQLSTATEvalue, andthemessagenumberand correspondingcompletemessagetext.

Todeterminethedatatype ofeachdiagnosticitem,seeTable55intheGETDIAGNOSTICSarticleofthe SQLreferencetopic.

char stmt_command[256]; long int error_count; long int condition_number; char auth_id[256];

char error_state[6]; char msgid[128]; char msgtext[1024];

EXEC SQL WHENEVER SQLERROR GOTO error; (application code)

error:

EXEC SQL GET DIAGNOSTICS :stmt_command = COMMAND_FUNCTION, :error_count = NUMBER;

for (condition_number=1;i<=error_count;++condition_number) {

EXEC SQL GET DIAGNOSTICS CONDITION :condition_number :auth_id = DB2_AUTHORIZATION_ID,

:error_state = RETURNED_SQLSTATE, :msgid = DB2_MESSAGE_ID,

:msgtext = DB2_MESSAGE_TEXT;

EXEC SQL INSERT INTO error_log VALUES(CURRENT_TIMESTAMP, :stmt_command, :condition_number, :auth_id, :error_state, :msgid, :msgtext); }

Handling

exception

conditions

with

the

WHENEVER

Statement

TheWHENEVERstatementcausesSQLtochecktheSQLSTATEandSQLCODEandcontinueprocessing yourprogram, orbranchto anotherareainyour programif anerror,exception,orwarningexistsasa resultofrunninganSQLstatement.An exceptioncondition handlingsubroutine(partofyour program) canthenexaminetheSQLCODEor SQLSTATEfieldtotakeanactionspecific totheerrororexception situation.

Note: TheWHENEVERstatementisnotallowedinREXXprocedures.Forinformationonhandling exceptionconditions inREXX,seeChapter10,“CodingSQLstatementsinREXX applications.” TheWHENEVERstatementallowsyoutospecifywhatyouwanttodowheneverageneralcondition is true.Youcanspecifymorethanone WHENEVERstatementforthesamecondition.Whenyoudo this, thefirstWHENEVERstatementappliesto allsubsequentSQLstatementsinthesourceprogramuntil anotherWHENEVERstatementisspecified.

| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

(25)

EXEC SQL

WHENEVER condition action END-EXEC.

Therearethreeconditions youcanspecify:

SQLWARNING Specify SQLWARNINGtoindicatewhatyouwantdonewhen

SQLWARN0=WorSQLCODEcontainsa positivevalue otherthan100 (SUBSTR(SQLSTATE,1,2)=’01’).

Note: SQLWARN0couldbeset forseveraldifferentreasons.Forexample, ifthevalueofa columnwastruncatedwhenitwas movedintoa hostvariable,yourprogrammight notregard thisasanerror. SQLERROR Specify SQLERRORtoindicatewhatyouwantdonewhenanerrorcode

isreturnedastheresult ofanSQLstatement(SQLCODE<0) (SUBSTR(SQLSTATE,1,2)>’02’).

NOTFOUND Specify NOTFOUNDtoindicatewhatyouwantdonewhenan SQLCODEof+100anda SQLSTATEof'02000'isreturnedbecause: v _After_a _single-row_SELECT_is_issued_or_after _the_first _FETCH_is_issued

for acursor,thedatatheprogramspecifiesdoesnotexist. v _After_a _subsequent_FETCH,_no_more_rows_satisfying_the_cursor

select-statement arelefttoretrieve.

v _After_an_UPDATE,_a _DELETE,_or_an_INSERT,_no_row _meets_the_search condition.

Youcanalso specifytheactionyouwanttaken:

CONTINUE Thiscausesyourprogramtocontinuetothenext statement.

GOTOlabel Thiscausesyourprogramtobranchtoanareaintheprogram.Thelabel forthatareamaybepreceded withacolon.TheWHENEVER... GOTO statement:

v _Must _be_a _section_name_or_an_unqualified_paragraph_name_in_COBOL v _Is _a_label_in_PL/I_and _C

v _Is _the_label_of_a _TAG_in_RPG

Forexample,ifyouareretrievingrowsusinga cursor,youexpectthatSQLwilleventuallybe unableto findanotherrowwhentheFETCHstatementisissued.Toprepareforthis situation,specifya

WHENEVERNOTFOUNDGOTO...statementtocauseSQLtobranchtoa placein theprogramwhere youissuea CLOSEstatementinordertoclosethecursorproperly.

Note: AWHENEVERstatementaffectsallsubsequent sourceSQLstatementsuntilanotherWHENEVER isencountered.

Inotherwords,allSQLstatementscoded betweentwoWHENEVERstatements(orfollowingthefirst,if thereisonlyone)aregovernedbythefirst WHENEVERstatement,regardlessofthepaththeprogram takes.

Becauseofthis,theWHENEVERstatementmustprecedethefirstSQLstatementit istoaffect.Ifthe WHENEVERfollowstheSQLstatement, thebranchisnottakenonthebasisof thevalue ofthe

SQLCODEandSQLSTATEset bythatSQLstatement.However,if yourprogramcheckstheSQLCODEor SQLSTATEdirectly,thecheckmust bedoneafter theSQLstatementisrun.

(26)

TheWHENEVERstatementdoesnotprovidea CALLtoasubroutineoption.Forthisreason, youmight wanttoexaminetheSQLCODEorSQLSTATEvalue aftereachSQLstatementisrunand calla

(27)

Chapter

5. Coding

SQL

statements

in

C

and

C++

applications

Thistopicdescribes theuniqueapplication andcodingrequirementsforembeddingSQLstatementsina CorC++program. CprogramreferstoILE CforiSeriesprograms.C++programreferstoILEC++ programs.Thistopicalso definestherequirementsforhoststructuresandhostvariables.Formore details,seethefollowingsections:

v _“Defining_the_SQL_{Communications}_Area_in_C_and _C++_applications_that_use_SQL” v _“Defining_SQL_Descriptor_Areas_in _C_and_C++_applications_that_use_SQL” _on_page₂₀ v _“Embedding_SQL_statements_in_C_and_C++_applications_that_use_SQL”_on_page₂₂ v _“Using_host_variables_in_C_and _C++_applications_that_use_SQL”_on_page₂₄ v _“Using_host_structures_in_C_and _C++_applications_that_use_SQL”_on_page₃₅

v _“Using_arrays_of_host_structures_in_C_and_C++_applications_that_use_SQL”_on_page₃₉ v _“Using_pointer_data_types_in_C_and _C++_applications_that_use_SQL”_on_page₄₃ v _“Using_typedef_in _C_and_C++_applications_that_use_SQL” _on_page₄₄

v _“Using_ILE_C_compiler_external_file_descriptions_in_C_and _C++_applications_that_use_SQL”_on_page₄₅ v _{“Determining}_equivalent_SQL_and_C_or_C++_data_types”_on_page₄₆

v _“Using_indicator_variables_in_C_and_C++_applications_that_use_SQL”_on_page₄₈

Fora detailedsampleCprogramthatshowshow SQLstatementscanbeused,seeChapter12,“Sample programsusingDB2UDBforiSeriesstatements.”

Note: Readthe“Codedisclaimerinformation” onpage196forimportantlegalinformation.

Defining

the

SQL

Communications

Area

in

C

and

C++

applications

that

use

SQL

ACorC++programcanbewrittentousetheSQLCAtocheckreturn statusforembeddedSQL statements,ortheprogramcanusetheSQLdiagnosticsareatocheckreturn status.See UsingtheSQL diagnosticsareaformore information.TheSQLCAisnotrequiredif yourprogramusestheGET DIAGNOSTICSSQLstatement.

WhenusingtheSQLCA,aCorC++programthatcontainsSQLstatementsmustincludeone orbothof thefollowing:

v _An_SQLCODE_variable _declared_as_long_SQLCODE v _An_SQLSTATE_variable_declared_as_char_SQLSTATE[6]

Or,

v _An_SQLCA_(which_contains _an_SQLCODE_and_SQLSTATE_variable).

TheSQLCODEandSQLSTATEvaluesare setbythedatabase manageraftereachSQLstatementisrun. Anapplication cancheck theSQLCODEorSQLSTATEvalueto determinewhetherthelastSQL

statementwas successful.

YoucancodetheSQLCAina CorC++programdirectlyorbyusingtheSQLINCLUDEstatement. UsingtheSQLINCLUDEstatementrequeststheinclusionofastandarddeclaration:

EXEC SQL INCLUDE SQLCA ;

Astandarddeclaration includesa structuredefinitionanda staticdataareathatarenamed'sqlca'. |

| | |

(28)

TheSQLCODE,SQLSTATE, andSQLCAvariablesmust appearbeforeanyexecutablestatements.The scopeofthedeclarationmust includethescopeofall SQLstatementsintheprogram.

TheincludedCandC++sourcestatementsfortheSQLCAare: #ifndef SQLCODE

struct sqlca {

unsigned char sqlcaid[8];

long sqlcabc;

long sqlcode;

short sqlerrml; unsigned char sqlerrmc[70]; unsigned char sqlerrp[8]; long sqlerrd[6]; unsigned char sqlwarn[11]; unsigned char sqlstate[5]; };

#define SQLCODE sqlca.sqlcode #define SQLWARN0 sqlca.sqlwarn[0] #define SQLWARN1 sqlca.sqlwarn[1] #define SQLWARN2 sqlca.sqlwarn[2] #define SQLWARN3 sqlca.sqlwarn[3] #define SQLWARN4 sqlca.sqlwarn[4] #define SQLWARN5 sqlca.sqlwarn[5] #define SQLWARN6 sqlca.sqlwarn[6] #define SQLWARN7 sqlca.sqlwarn[7] #define SQLWARN8 sqlca.sqlwarn[8] #define SQLWARN9 sqlca.sqlwarn[9] #define SQLWARNA sqlca.sqlwarn[10] #define SQLSTATE sqlca.sqlstate #endif

struct sqlca sqlca = {0x0000000000000000};

Whena declareforSQLCODEisfoundintheprogramandtheprecompilerprovidestheSQLCA, SQLCADEreplacesSQLCODE.Whena declareforSQLSTATEisfoundintheprogramand the precompilerprovides theSQLCA,SQLSTOTEreplacesSQLSTATE.

Note: ManySQLerrormessagescontainmessagedatathatisofvaryinglength.Thelengths ofthesedata fields areembeddedinthevalue oftheSQLCAsqlerrmcfield.Becauseofthese lengths,printing thevalueof sqlerrmcfroma CorC++programmight giveunpredictableresults.

FormoreinformationaboutSQLCA,seeAppendixB,SQLCommunicationAreaintheSQLReference topic.

Defining

SQL

Descriptor

Areas

in

C

and

C++

applications

that

use

SQL

ThefollowingstatementsrequireanSQLDA: EXECUTE...USING DESCRIPTORdescriptor-name

FETCH...USING DESCRIPTORdescriptor-name

OPEN...USINGDESCRIPTORdescriptor-name

DESCRIBEstatement-nameINTOdescriptor-name

DESCRIBETABLEhost-variableINTO descriptor-name

PREPAREstatement-nameINTOdescriptor-name

CALL...USINGDESCRIPTORdescriptor-name

UnliketheSQLCA,morethanoneSQLDAcanbeintheprogram, andanSQLDAcanhaveanyvalid name.Youcancode anSQLDAinaCorC++programeitherdirectlyorbyusingtheSQLINCLUDE statement.Using theSQLINCLUDEstatementrequeststheinclusionofa standardSQLDAdeclaration:

(29)

Astandarddeclaration includesonlyastructure definitionwiththename’sqlda’. Cand C++declarationsthatareincludedfortheSQLDAare:

#ifndef SQLDASIZE struct sqlda {

unsigned char sqldaid[8]; long sqldabc; short sqln; short sqld; struct sqlvar { short sqltype; short sqllen;

unsigned char *sqldata; short *sqlind;

struct sqlname {

short length;

unsigned char data[30]; } sqlname;

} sqlvar[1]; };

#define SQLDASIZE(n) (sizeof(struct sqlda) + (n-1)* sizeof(struct sqlvar)) #endif

OnebenefitfromusingtheINCLUDESQLDASQLstatementisthatyoualso getthefollowingmacro definition:

#define SQLDASIZE(n) (sizeof(struct sqlda) + (n-1)* sizeof(struc sqlvar))

Thismacromakesiteasy toallocatestorageforan SQLDAwitha specifiednumberofSQLVARelements. Inthefollowingexample,theSQLDASIZEmacroisusedtoallocatestorageforanSQLDAwith 20 SQLVARelements.

#include <stdlib.h> EXEC SQL INCLUDE SQLDA; struct sqlda *mydaptr; short numvars = 20;

. .

mydaptr = (struct sqlda *) malloc(SQLDASIZE(numvars)); mydaptr->sqln = 20;

Hereareothermacrodefinitions thatareincludedwith theINCLUDESQLDAstatement:

GETSQLDOUBLED(daptr) Returns1 iftheSQLDApointedtobydaptrhasbeendoubled,or0 ifit hasnotbeendoubled.TheSQLDAisdoublediftheseventh byteinthe SQLDAIDfieldissetto’2’.

SETSQLDOUBLED(daptr,newvalue)

Sets theseventh byteofSQLDAIDtonewvalue. GETSQLDALONGLEN(daptr,n)

ReturnsthelengthattributeofthenthentryintheSQLDAtowhich daptr points.UsethisonlyiftheSQLDAwasdoubled andthenth SQLVARentryhasaLOBdatatype.

SETSQLDALONGLEN(daptr,n,len)

Sets theSQLLONGLEN fieldoftheSQLDAtowhichdaptr pointstolen forthenthentry.Use thisonlyif theSQLDAwasdoubledand thenth SQLVARentryhasaLOBdatatype.

GETSQLDALENPTR(daptr,n) Returnsa pointertotheactuallengthofthedataforthenth entryinthe SQLDAtowhichdaptr points.TheSQLDATALENpointerfieldreturnsa

ERserver. Embedded SQL programming. iseries. Version 5 Release 3

iSeries

Embedded

SQL

programming

Version

5

Release

3

iSeries

Embedded

SQL

programming

Version

5

Release

3

Contents

Chapter

1.

Embedded

SQL

programming

1

Chapter

2.

What’s

new

for

Version

5

Release

3

in

the

Embedded

SQL

programming

topic

.

.

.

.

.

.

.

.

.

. 3

Chapter

3.

Print

this

topic

.

.

.

.

.

.

. 5

Chapter

4.

Common

concepts

and

rules

for

using

embedded

SQL

.

.

.

.

.

.

. 7

Chapter

5.