ISO/IEC 9075-3:2023

INTERNATIONAL ISO/IEC
STANDARD 9075-3
Sixth edition
2023-06
Information technology —
Database languages SQL —
Part 3:
Call-Level Interface (SQL/CLI)
Reference number
© ISO/IEC 2023
© ISO/IEC 2023
All rights reserved. Unless otherwise specified, or required in the context of its implementation, no part of this publication may
be reproduced or utilized otherwise in any form or by any means, electronic or mechanical, including photocopying, or posting on
the internet or an intranet, without prior written permission. Permission can be requested from either ISO at the address below
or ISO’s member body in the country of the requester.
ISO copyright office
CP 401 • Ch. de Blandonnet 8
CH-1214 Vernier, Geneva
Phone: +41 22 749 01 11
Email: copyright@iso.org
Website: www.iso.org
Published in Switzerland
ii
© ISO/IEC 2023 – All rights reserved

ISO/IEC9075-3:2023(E)
Contents Page
Foreword.ix
Introduction.xi
1 Scope.1
2 Normativereferences.2
3 Termsanddefinitions.3
4 Concepts.4
4.1 Notationsandconventions.4
4.1.1 Notations.4
4.1.2 Specificationofroutinedefinitions.4
4.2 IntroductiontoSQL/CLI.4
4.3 Return codes.8
4.4 DiagnosticsareasinSQL/CLI.9
4.4.1 IntroductiontodiagnosticsareasinSQL/CLI.9
4.4.2 SettingofROW_NUMBERandCOLUMN_NUMBERfields.12
4.5 Miscellaneous characteristics.12
4.5.1 Handles.12
4.5.2 Null-terminated strings.12
4.5.3 Null pointers.13
4.5.4 Environment attributes.13
4.5.5 Connectionattributes.13
4.5.6 Statementattributes.14
4.5.7 CLIdescriptorareas.14
4.5.8 Obtainingdiagnosticsduringmulti-rowfetch.15
4.6 SQL-invokedroutines.16
4.6.1 ResultsetsreturnedbySQL-invokedprocedures.16
4.7 Cursors.16
4.7.1 Generaldescriptionofcursors.16
4.8 Client-server operation.16
5 Lexicalelements.17
5.1 and.17
6 Call-LevelInterfacespecifications.18
6.1 .18
6.2 invocation.26
6.3 Implicitsetconnection.29
6.4 Preparingastatement.30
6.5 Executingastatement.33
6.6 ImplicitCLIpreparedcursor.35
6.7 ImplicitCLIproceduralresultcursor.37
©ISO/IEC2023–Allrightsreserved iii

ISO/IEC9075-3:2023(E)
6.8 InitialCLIcursor.38
6.9 ImplicitDESCRIBEUSINGclause.39
6.10 ImplicitEXECUTEUSINGandOPENUSINGclauses.45
6.11 ImplicitCALLUSINGclause.51
6.12 Fetchingarowset.56
6.13 ImplicitFETCHUSINGclause.60
6.14 Characterstringretrieval.66
6.15 Binarystringretrieval.68
6.16 Deferredparametercheck.70
6.17 DescriptionofCLIitemdescriptorareas.71
6.18 OthertablesassociatedwithCLI.82
6.19 SQL/CLIdatatypecorrespondences.106
7 SQL/CLIroutines.116
7.1 IntroductiontoSQL/CLIroutines.116
7.2 AllocConnect().116
7.3 AllocEnv().117
7.4 AllocHandle().118
7.5 AllocStmt().122
7.6 BindCol().123
7.7 BindParameter().125
7.8 Cancel().129
7.9 CloseCursor().131
7.10 ColAttribute().132
7.11 ColumnPrivileges().134
7.12 Columns().140
7.13 Connect().149
7.14 CopyDesc().153
7.15 DataSources().154
7.16 DescribeCol().156
7.17 Disconnect().158
7.18 EndTran().160
7.19 Error().164
7.20 ExecDirect().166
7.21 Execute().167
7.22 Fetch().168
7.23 FetchScroll().169
7.24 ForeignKeys().170
7.25 FreeConnect().182
7.26 FreeEnv().183
7.27 FreeHandle().184
7.28 FreeStmt().187
7.29 GetConnectAttr().189
7.30 GetCursorName().191
7.31 GetData().192
7.32 GetDescField().198
7.33 GetDescRec().200
7.34 GetDiagField().202
iv ©ISO/IEC2023–Allrightsreserved

ISO/IEC9075-3:2023(E)
7.35 GetDiagRec().211
7.36 GetEnvAttr().213
7.37 GetFeatureInfo().215
7.38 GetFunctions().218
7.39 GetInfo().219
7.40 GetLength().223
7.41 GetParamData().225
7.42 GetPosition().231
7.43 GetSessionInfo().233
7.44 GetStmtAttr().235
7.45 GetSubString().238
7.46 GetTypeInfo().240
7.47 MoreResults().244
7.48 NextResult().245
7.49 NumResultCols().246
7.50 ParamData().247
7.51 Prepare().252
7.52 PrimaryKeys().253
7.53 PutData().258
7.54 RowCount().261
7.55 SetConnectAttr().262
7.56 SetCursorName().264
7.57 SetDescField().266
7.58 SetDescRec().271
7.59 SetEnvAttr().273
7.60 SetStmtAttr().275
7.61 SpecialColumns().279
7.62 StartTran().286
7.63 TablePrivileges().288
7.64 Tables().293
8 Additionaldatamanipulationrules.300
8.1 Effectofopeningacursor.300
9 DynamicSQL.301
9.1 .301
10 Statuscodes.302
10.1 SQLSTATE.302
11 Conformance.305
11.1 ClaimsofconformancetoSQL/CLI.305
11.2 AdditionalconformancerequirementsforSQL/CLI.305
11.3 ImpliedfeaturerelationshipsofSQL/CLI.305
AnnexA(informative) SQLconformancesummary.306
AnnexB(informative) Implementation-defined elements.308
AnnexC(informative) Implementation-dependent elements.329
AnnexD(informative) SQLoptionalfeaturetaxonomy.334
AnnexE(informative) Deprecatedfeatures.335
©ISO/IEC2023–Allrightsreserved v

ISO/IEC9075-3:2023(E)
AnnexF(informative) IncompatibilitieswithISO/IEC9075:2016.336
AnnexG(informative) DefectReportsnotaddressedinthiseditionofthisdocument.337
AnnexH(informative) Exampleheaderfiles.338
H.1 Cheaderfilesqlcli.h.338
H.2 COBOLlibraryitemSQLCLI.349
AnnexI(informative) ExampleCprograms.357
I.1 IntroductiontoExampleCprograms.357
I.2 Createtable,insert,select.357
I.3 Interactive Query.360
I.4 ProvidinglongdynamicargumentsatExecutetime.363
Index.366
vi ©ISO/IEC2023–Allrightsreserved

ISO/IEC9075-3:2023(E)
Tables
Table Page
1 HeaderfieldsinSQL/CLIdiagnosticsareas.10
2 StatusrecordfieldsinSQL/CLIdiagnosticsareas.10
3 SupportedcallingconventionsofSQL/CLIroutinesbylanguage.21
4 AbbreviatedSQL/CLIgenericnames.21
5 FieldsinSQL/CLIrowandparameterdescriptorareas.76
6 CodesusedforimplementationdatatypesinSQL/CLI.78
7 CodesusedforapplicationdatatypesinSQL/CLI.79
8 CodesassociatedwithdatetimedatatypesinSQL/CLI.80
9 CodesassociatedwithinSQL/CLI.80
10 CodesassociatedwithinSQL/CLI.81
11 Codesassociatedwithuser-definedtypesinSQL/CLI.81
12 CodesusedforSQL/CLIdiagnosticfields.82
13 CodesusedforSQL/CLIhandletypes.83
14 Codesusedfortransactiontermination.84
15 Codesusedforenvironmentattributes.84
16 Codesusedforconnectionattributes.84
17 Codesusedforstatementattributes.84
18 CodesusedforFreeStmtoptions.85
19 Datatypesofattributes.85
20 CodesusedforSQL/CLIdescriptorfields.86
21 AbilitytosetSQL/CLIdescriptorfields.88
22 AbilitytoretrieveSQL/CLIdescriptorfields.90
23 SQL/CLIdescriptorfielddefaultvalues.93
24 Codesusedforfetchorientation.95
25 Multi-rowfetchstatuscodes.95
26 MiscellaneouscodesusedinCLI.95
27 CodesusedtoidentifySQL/CLIroutines.96
28 Codesanddatatypesforimplementationinformation.99
29 Codesanddatatypesforsessionimplementationinformation.101
30 ValuesforTRANSACTIONISOLATIONOPTIONwithStartTran.101
31 ValuesforTRANSACTIONACCESSMODEwithStartTran.101
32 Codesusedforconcisedatatypes.101
33 CodesusedwithconcisedatetimedatatypesinSQL/CLI.103
34 CodesusedwithconciseintervaldatatypesinSQL/CLI.104
35 ConcisecodesusedwithdatetimedatatypesinSQL/CLI.104
36 ConcisecodesusedwithintervaldatatypesinSQL/CLI.104
37 Specialparametervalues.105
38 ColumntypesandscopesusedwithSpecialColumns.105
39 SQL/CLIdatatypecorrespondencesforAda.106
40 SQL/CLIdatatypecorrespondencesforC.107
41 SQL/CLIdatatypecorrespondencesforCOBOL.108
42 SQL/CLIdatatypecorrespondencesforFortran.110
43 SQL/CLIdatatypecorrespondencesforM.111
44 SQL/CLIdatatypecorrespondencesforPascal.112
45 SQL/CLIdatatypecorrespondencesforPL/I.114
46 SQLSTATEclassandsubclasscodes.302
47 ImpliedfeaturerelationshipsofSQL/CLI.305
©ISO/IEC2023–Allrightsreserved vii

ISO/IEC9075-3:2023(E)
A.1 FeaturedefinitionsoutsideofConformanceRules.306
D.1 Featuretaxonomyforoptionalfeatures.334
viii ©ISO/IEC2023–Allrightsreserved

Foreword
ISO (the International Organization for Standardization) and IEC (the International Electrotechnical Com-
mission) form the specialized system for worldwide standardization. National bodies that are members
of ISO or IEC participate in the development of International Standards through technical committees
established by the respective organization to deal with particular fields of technical activity. ISO and IEC
technical committees collaborate in fields of mutual interest. Other international organizations, govern-
mental and non-governmental, in liaison with ISO and IEC, also take part in the work.
The procedures used to develop this document and those intended for its further maintenance are de-
scribed in the ISO/IEC Directives, Part 1. In particular, the different approval criteria needed for the dif-
ferent types of document should be noted. This document was drafted in accordance with the editorial
rules of the ISO/IEC Directives, Part 2 (see www.iso.org/directives or www.iec.ch/members_experts/ref-
docs).
ISO and IEC draw attention to the possibility that the implementation of this document may involve the
use of (a) patent(s). ISO and IEC take no position concerning the evidence, validity or applicability of any
claimed patent rights in respect thereof. As of the date of publication of this document, ISO and IEC have
not received notice of (a) patent(s) which may be required to implement this document. However, imple-
menters are cautioned that this may not represent the latest information, which may be obtained from
the patent database available at www.iso.org/patents and https://patents.iec.ch. ISO and IEC shall not be
held responsible for identifying any or all such patent rights.
Any trade name used in this document is information given for the convenience of users and does not
constitute an endorsement.
For an explanation of the voluntary nature of standards, the meaning of ISO specific terms and expres-
sions related to conformity assessment, as well as information about ISO's adherence to the World Trade
Organization (WTO) principles in the Technical Barriers to Trade (TBT) see www.iso.org/iso/fore-
word.html. In the IEC, see www.iec.ch/understanding-standards.
This document was prepared by Joint Technical Committee ISO/IEC JTC 1, Information technology, Sub-
committee SC 32, Data management and interchange.
This sixth edition cancels and replaces the fifth edition (ISO/IEC 9075-3:2016), which has been techni-
cally revised. It also incorporates the Technical Corrigendum ISO/IEC 9075-3:2016/Cor.1:2022.
The main changes are as follows:
— improve the presentation and accuracy of the summaries of implementation-defined and implemen-
tation-dependent aspects of this document;
— introduction of several digital artifacts;
— alignment with updated ISO house style and other guidelines for creating standards.
This sixth edition of ISO/IEC 9075-3 is designed to be used in conjunction with the following editions of
other parts of the ISO/IEC 9075 series, all published 2023:
— ISO/IEC 9075-1, sixth edition;
— ISO/IEC 9075-2, sixth edition;
— ISO/IEC 9075-4, seventh edition;
© ISO/IEC 2023 – All rights reserved ix

— ISO/IEC 9075-9, fifth edition;
— ISO/IEC 9075-10, fifth edition;
— ISO/IEC 9075-11, fifth edition;
— ISO/IEC 9075-13, fifth edition;
— ISO/IEC 9075-14, sixth edition;
— ISO/IEC 9075-15, second edition;
— ISO/IEC 9075-16, first edition.
A list of all parts in the ISO/IEC 9075 series can be found on the ISO and IEC websites.
Any feedback or questions on this document should be directed to the user’s national standards body. A
complete listing of these bodies can be found at www.iso.org/members.html and www.iec.ch/national-
committees.
x © ISO/IEC 2023 – All rights reserved

ISO/IEC9075-3:2023(E)
Introduction
Theorganizationofthisdocumentisasfollows:
1) Clause1,“Scope”,specifiesthescopeofthisdocument.
2) Clause2,“Normativereferences”,identifiesadditionalstandardsthat,throughreferenceinthis
document,constituteprovisionsofthisdocument.
3) Clause3,“Termsanddefinitions”,definesthetermsanddefinitionsusedinthisdocument.
4) Clause4,“Concepts”,presentsconceptsusedinthedefinitionoftheCall-LevelInterface.
5) Clause5,“Lexicalelements”,definesthelexicalelementsofthelanguage.
6) Clause6,“Call-LevelInterfacespecifications”,definesfacilitiesforusingSQLthroughaCall-Level
Interface.
7) Clause7,“SQL/CLIroutines”,defineseachoftheroutinesthatcomprisetheCall-LevelInterface.
8) Clause8,“Additionaldatamanipulationrules”,definesadditionalrulesfordatamanipulation.
9) Clause9,“DynamicSQL”,definestheSQLdynamicstatements.
10) Clause10,“Statuscodes”,definesvaluesthatidentifythestatusoftheexecutionofSQL-statements
andthemechanismsbywhichthosevaluesarereturned.
11) Clause11,“Conformance”,definesthecriteriaforconformancetothisdocument.
12) AnnexA,“SQLconformancesummary”,isaninformativeAnnex.Itsummarizestheconformance
requirementsoftheSQLlanguage.
13) AnnexB,“Implementation-definedelements”,isaninformativeAnnex.Itliststhosefeaturesfor
whichthebodyofthisdocumentstatesthatthesyntax,themeaning,thereturnedresults,theeffect
onSQL-dataand/orschemas,orotheraspectispartlyorwhollyimplementation-defined.
14) AnnexC,“Implementation-dependentelements”,isaninformativeAnnex.Itliststhosefeaturesfor
whichthebodyofthisdocumentstatesthatthesyntax,themeaning,thereturnedresults,theeffect
onSQL-dataand/orschemas,orotheraspectispartlyorwhollyimplementation-dependent.
15) AnnexD,“SQLoptionalfeaturetaxonomy”,isaninformativeAnnex.Itidentifiestheoptionalfeatures
oftheSQLlanguagespecifiedinthisdocumentbyanidentifierandashortdescriptivename.This
taxonomyisusedtospecifyconformance.
16) AnnexE,“Deprecatedfeatures”,isaninformativeAnnex.Itlistsfeaturesthattheresponsible
TechnicalCommitteeintendsnottoincludeinafutureeditionofthisdocument.
17) AnnexF,“IncompatibilitieswithISO/IEC9075:2016”,isaninformativeAnnex.Itlistsincompatib-
ilitieswiththepreviouseditionofthisdocument.
18) AnnexG,“DefectReportsnotaddressedinthiseditionofthisdocument”,isaninformativeAnnex.
ItdescribestheDefectReportsthatwereknownatthetimeofpublicationofthisdocument.Each
oftheseproblemsisaproblemcarriedforwardfromthepreviouseditionofdocument.Nonew
problemshavebeencreatedinthedraftingofthiseditionofthisdocument.
19) AnnexH,“Exampleheaderfiles”,isaninformativeAnnex.Itprovidesexamplesoftypicaldefinition
filesforapplicationprogramsusingtheSQLCall-LevelInterface.
20) AnnexI,“ExampleCprograms”,isaninformativeAnnex.ItprovidesexamplesofusingtheSQLCall-
LevelInterfaceintheCprogramminglanguage.
©ISO/IEC2023–Allrightsreserved xi

ISO/IEC9075-3:2023(E)
Inthetextofthisdocument,ClausesandAnnexesbeginnewodd-numberedpages,andinClause6,“Call-
LevelInterfacespecifications”,throughClause11,“Conformance”,Subclausesbeginnewpages.Any
resultingblankspaceisnotsignificant.
xii ©ISO/IEC2023–Allrightsreserved

INTERNATIONAL STANDARD ISO/IEC 9075-3:2023(E)
Informationtechnology—DatabaselanguageSQL—
Part3:
Call-LevelInterface(SQL/CLI)
1 Scope
Thisdocumentdefinesthestructuresandproceduresthatcanbeusedtoexecutestatementsofthe
databaselanguageSQLfromwithinanapplicationwritteninaprogramminglanguageinsuchawaythat
proceduresusedareindependentoftheSQLstatementstobeexecuted.
©ISO/IEC2023–Allrightsreserved 1

ISO/IEC9075-3:2023(E)
2 Normativereferences
Thefollowingdocumentsarereferredtointhetextinsuchawaythatsomeoralloftheircontentconsti-
tutesrequirementsofthisdocument.Fordatedreferences,onlytheeditioncitedapplies.Forundated
references,thelatesteditionofthereferenceddocument(includinganyamendments)applies.
ISO/IEC1539-1:2018,Informationtechnology—Programminglanguages—Fortran—Part1:Base
language
ISO/IEC1539-2:2000,Informationtechnology—Programminglanguages—Fortran—Part2:Varying
lengthcharacterstrings
ISO1989:2014,Informationtechnology—Programminglanguages—COBOL
ISO6160:1979,Programminglanguages—PL/I(EndorsementofANSIX3.53-1976)
ISO7185:1990,Informationtechnology—Programminglanguages—Pascal
ISO/IEC8652:2012,Informationtechnology—Programminglanguages—Ada
ISO/IEC8652:2012/Cor.1:2016,Informationtechnology—Programminglanguages—Ada—Technical
Corrigendum1
ISO/IEC9075-1,Informationtechnology—Databaselanguages—SQL—Part1:Framework
(SQL/Framework)
ISO/IEC9075-2,Informationtechnology—Databaselanguages—SQL—Part2:Foundation
(SQL/Foundation)
ISO/IEC9075-11,Informationtechnology—Databaselanguages—SQL—Part11:Informationand
DefinitionSchemas(SQL/Schemata)
ISO/IEC9899:2018,Informationtechnology—Programminglanguages—C
ISO/IEC10206:1991,Informationtechnology—Programminglanguages—ExtendedPascal
ISO/IEC11756:1999,Informationtechnology—Programminglanguages—M
2 ©ISO/IEC2023–Allrightsreserved

ISO/IEC9075-3:2023(E)
3 Termsanddefinitions
Forthepurposesofthisdocument,thetermsanddefinitionsgiveninISO/IEC9075-1,ISO/IEC9075-2
andthefollowingapply.
ISOandIECmaintainterminologicaldatabasesforuseinstandardizationatthefollowingaddresses:
— ISOOnlinebrowsingplatform:availableathttps://www.iso.org/obp
— IECElectropedia:availableathttps://www.electropedia.org/
3.1
datasource
SQL-serverthatispartofthecurrentSQL-connection
3.2
handle
CLIobjectreturnedbyanSQL/CLIimplementationwhenaCLIresourceisallocatedandusedbyan
SQL/CLIapplicationtoreferencethatCLIresource
3.3
pseudo-column
columnthatispartofatablebutisnotpartofthedescriptorforthattable
Note1toentry:Anexampleofsuchapseudo-columnisanimplementation-definedrowidentifier.
3.4
rowset
oneormorerowsretrievedinasingleinvocationoftheFetchandFetchScrollroutines
3.5
SQL/CLIapplication
applicationthatinvokessspecifiedinthisdocument
©ISO/IEC2023–Allrightsreserved 3

ISO/IEC9075-3:2023(E)
4 Concepts
ThisClausemodifiesClause4,“Concepts”,inISO/IEC9075-2.
4.1 Notationsandconventions
ThisSubclausemodifiesSubclause4.1,“Notationsandconventions”,inISO/IEC9075-2.
4.1.1 Notations
ThisSubclausemodifiesSubclause4.1.1,“Notations”,inISO/IEC9075-2.
ThenotationsusedinthisdocumentaredefinedinISO/IEC9075-1.
ThesyntaxdefinedinthisdocumentisavailablefromtheISOwebsiteasa“digitalartifact”.See
https://standards.iso.org/iso-iec/9075/-3/ed-6/en/todownloaddigitalartifactsfor
thisdocument.Todownloadthesyntaxdefinedinaplain-textformat,selectthefilenamed
ISO_IEC_9075-3(E)_CLI.bnf.txt.TodownloadthesyntaxdefinedinanXMLformat,selectthe
filenamedISO_IEC_9075-3(E)_CLI.bnf.xml.
4.1.2 Specificationofroutinedefinitions
Theroutinesinthisdocumentarespecifiedintermsofthefollowingcharacteristics.
— Function:Ashortstatementofthepurposeoftheroutine.
— Definition:Thenameoftheroutineandthename,mode,anddatatypeofeachofitsparameters.
— GeneralRules:Aspecificationoftherun-timeeffectoftheroutine.WheremorethanoneGeneral
Ruleisusedtospecifytheeffectofaroutine,therequiredeffectisthatwhichwouldbeobtained
bybeginningwiththefirstGeneralRuleandapplyingtheRulesinnumericsequenceuntilaRule
isappliedthatspecifiesorimpliesachangeinsequenceorterminationoftheapplicationofthe
Rules.UnlessotherwisespecifiedorimpliedbyaspecificRulethatisapplied,applicationofGeneral
Rulesterminateswhenthelastinthesequencehasbeenapplied.
4.2 IntroductiontoSQL/CLI
ThisSubclauseismodifiedbySubclause4.11,“IntroductiontoSQL/CLI”,inISO/IEC9075-9.
TheCall-LevelInterface(SQL/CLI)isabindingstyleforexecutingSQLstatements.Thisdocumentprovides
specificationsforroutinesthat:
— allocateanddeallocateresources;
— controlconnectionstoSQL-servers;
— executeSQLstatementsusingmechanismssimilartodynamicSQL;
— obtaindiagnosticinformation;
— controltransactiontermination;
— obtaininformationabouttheSQL/CLIimplementationandtheSQL-implementation.
AhandleisaCLIobjectreturnedbyanSQL/CLIimplementationwhenaCLIresourceisallocated;the
handleisusedbyanSQL/CLIapplicationtoreferencethatCLIresource.TheAllocHandleroutineallocates
4 ©ISO/IEC2023–Allrightsreserved

ISO/IEC9075-3:2023(E)
4.2 IntroductiontoSQL/CLI
theresourcestomanageanSQL-environment,anSQL-connection,aCLIdescriptorarea,orSQL-statement
processing;wheninvoked,itreturnsanenvironmenthandle,aconnectionhandle,adescriptorhandle,
orastatementhandle,respectively.AnSQL-connectionisallocatedinthecontextofanallocatedSQL-
environment.CLIdescriptorareasandSQL-statementsareallocatedinthecontextofanallocatedSQL-
connection.TheFreeHandleroutinedeallocatesaspecifiedresource.TheAllocConnect,AllocEnv,and
AllocStmtroutinescanbeusedtoallocatetheresourcestomanageanSQL-connection,anSQL-environ-
ment,andSQL-statementprocessing,respectively,insteadofusingtheAllocHandleroutine.TheFreeCon-
nect,FreeEnv,andFreeStmtroutinescanbeusedtodeallocatethespecificresourceinsteadofusing
FreeHandle.
EachallocatedSQL-environmenthasanattributethatdetermineswhetheroutputcharacterstringsare
null-terminatedbytheSQL/CLIimplementation.TheSQL/CLIapplicationcansetthevalueofthis
attributebyusingtheroutineSetEnvAttrandcanretrievethecurrentvalueoftheattributebyusingthe
routineGetEnvAttr.
TheConnectroutineestablishesanSQL-connection,whichbecomesthecurrentSQL-connection.The
DisconnectroutineterminatesanestablishedSQL-connection.SwitchingbetweenestablishedSQL-con-
nectionsoccursautomaticallywhenevertheSQL/CLIapplicationswitchesprocessingtoadormantSQL-
connection,whichthenbecomesthecurrentSQL-connection.
TheExecDirectroutineisusedforaone-timeexecutionofanSQL-statement.ThePrepareroutineisused
toprepareanSQL-statementforsubsequentexecutionusingtheExecuteroutine.Inallthreecases,the
executedSQL-statementcancontaindynamicparameters.
Theinterfaceforadescriptionofdynamicparameters,dynamicparametervalues,theresultcolumnsof
aor,andthetargetspecifications
fortheresultcolumnsisaCLIdescriptorarea.ACLIdescriptorareaforeachtypeofinterfaceisautomat-
icallyallocatedwhenanSQL-statementisallocated.TheSQL/CLIapplicationmayallocateadditionalCLI
descriptorareasandnominatethemforuseastheinterfaceforthedescriptionofdynamicparameter
valuesorthedescriptionoftargetspecificationsbyusingtheroutineSetStmtAttr.TheSQL/CLIapplication
candeterminethehandlevalueoftheCLIdescriptorareacurrentlybeingusedforaspecificinterfaceby
usingtheroutineGetStmtAttr.TheGetDescFieldandGetDescRecroutinesenableinformationtobe
retrievedfromaCLIdescriptorarea.TheCopyDescroutineenablesthecontentsofaCLIdescriptorarea
tobecopiedtoanotherCLIdescriptorarea.
Whenaorispreparedorexecuted
immediately,adescriptionoftheresultcolumnsisautomaticallyprovidedintheapplicableCLIimple-
mentationdescriptorarea.Inthiscase,theSQL/CLIapplicationmayadditionallyretrieveinformation
byusingtheDescribeColand/ortheColAttributeroutinetoobtainadescriptionofasingleresultcolumn
andbyusingtheNumResultColsroutinetoobtainacountofthenumberofresultcolumns.TheSQL/CLI
applicationsetsvaluesintheCLIapplicationdescriptorareaforthedescriptionofthecorresponding
targetspecificationseitherexplicitly,byusingtheroutinesSetDescFieldandSetDescRec,orimplicitly,
byusingtheroutineBindCol.
WhenanSQL-statementispreparedorexecutedimmediately,adescriptionofthedynamicparameters
isautomaticallyprovidedintheapplicableCLIimplementationdescriptorareaifthisfacilityissupported
bythecurrentSQL-connection.AnattributeassociatedwiththeallocatedSQL-connectionindicates
whetherthisfacilityissupported.ThevalueoftheattributemayberetrievedusingtheroutineGetCon-
nectAttr.Regardlessofwhetherautomaticdescriptionissupported,alldynamicinputandinput/output
parametersshallbedefinedintheapplicationdescriptorareabeforeSQL-statementexecution.Thiscan
bedoneeitherexplicitly,byusingtheroutinesSetDescFieldandSetDescRec,orimplicitly,byusingthe
routineBindParameter.Thevalueofadynamicinputorinput/outputparametermaybeestablished
beforeSQL-statementexecution(immediateparametervalue)ormaybeprovidedduringSQL-statement
execution(deferredparametervalue).ItsdescriptionintheCLIdescriptorareadetermineswhichmethod
isinuse.TheParamDataroutineisusedtocyclethroughandprocessdeferredinputandinput/output
parametervalues.ThePutDataroutineisusedtoprovidethedeferredvalues.ThePutDataroutinealso
enablesthevaluesofcharacterstringinputandinput/outputparameterstobeprovidedpiecebypiece.
©ISO/IEC2023–Allrightsreserved 5

ISO/IEC9075-3:2023(E)
4.2 IntroductiontoSQL/CLI
Beforeaispreparedorexecutedimmediately,theSQL/CLIapplicationmaychoose
whetherornottobinddynamicoutputparametersintheCLIapplicationdescriptorarea.Thiscanbe
doneeitherexplicitly,byusingtheroutinesSetDescFieldandSetDescRec,orimplicitly,byusingthe
routineBindParameter.Afterexecutionofthestatement,valuesofunboundoutputandinput/output
parameterscanbeindividuallyretrievedusingtheGetParamDataroutine.TheGetParamDataroutine
alsoenablestheretrievalofthevaluesofcharacterandbinarystringoutputandinput/outputparameters
tobeaccomplishedpiecebypiece.
Whenaorisexecuted,aCLIprepared
cursorisimplicitlydeclaredandopened.Thenameofthecursorisdeterminedbythecursorname
propertyassociatedwiththeallocatedSQL-statement,whichcanbesuppliedbytheSQL/CLIapplication
byusingtheroutineSetCursorName.IfacursornameisnotsuppliedbytheSQL/CLIapplication,the
valueofthecursornamepropertyassociatedwiththeallocatedSQL-statementisanimplementation-
dependent(UV124)cursorname.ThecursornamepropertyassociatedwiththeallocatedSQL-statement
canberetrievedbyusingtheGetCursorNameroutine.Theoperationalsensitivity,scrollability,and
holdabilitypropertiesofaCLIpreparedcursoraredeterminedbytheCURSORSENSITIVITY,CURSOR
SCROLLABLE,andCURSORHOLDABLEattributes,respectively,oftheallocatedSQL-statementatthe
timetheCLIcursorisdeclaredandopened.TheSQL/CLIapplicationcansetthevaluesoftheseattributes
byusingtheSetStmtAttrroutineandcanretrievethecurrentvaluesoftheseattributesbyusingthe
GetStmtAttrroutine.TheoperationalreturnabilitypropertyofaCLIpreparedcursorisimplementation-
defined(IV031).
TheFetchandFetchScrollroutinesareusedtopositionanopenCLIcursoronarowandtoretrievethe
valuesofboundcolumnsforthatrow.Aboundcolumnisonewhosetargetspecificationinthespecified
CLIdescriptorareadefinesalocationforthetargetvalue.TheFetchroutinealwayspositionstheopen
CLIcursoronthenextrow,whereastheFetchScrollroutinemaybeusedtopositiontheopenCLIcursor
onaspecifiedrow.TheuseofFetchScrollwithaFetchOrientationotherthanNEXTispermittedonlyif
theoperationalscrollabilitypropertyoftheCLIcursorisSCROLL.TheFetchandFetchScrollroutines
canalsoretrievemultiplerowsinasinglecall;thesetofrowsthusretrievediscalledarowset.Thisis
accomplishedbysettingtheARRAY_SIZEfieldoftheapplicableapplicationrowdescriptortothedesired
numberofrows.Notethatthesinglerowfetchisjustaspecialcaseofmulti-rowfetch,wheretherowset
sizeis1(one).
ValuesforunboundcolumnscanbeindividuallyretrievedbyusingtheGetDataroutine.TheGetData
routinealsoenablestheretrievalofthevaluesofcharacterandbinarystringcolumnstobeaccomplished
piecebypiece.ThecurrentrowofaCLIcursorisarowofthecurrentrowsetindicatedbytheCURRENT
OFPOSITIONattributeoftheallocatedSQL-statementassociatedwiththeCLIcursor.Thecurrentrow
canbedeletedorupdatedbyexecutingaora parabledynamicupdatestatement:positioned>,respectively,forthatCLIcursorunderadifferent
allocatedSQL-statementtotheoneunderwhichtheCLIcursorwasopened.TheCloseCursorroutine
enablesaCLIcursortobeclosed.
ResultsetscanbereturnedtotheSQL/CLIapplicationasaresultofinvokingtheExecuteorExecDirect
routine,supplyingastatementhandlewhosecurrentstatementisa.Ifthe
invokesanSQL-invokedprocedureSIPthatreturnsanon-emptyresultsetsequenceRSS,thenaCLI
proceduralresultcursorisautomaticallyassociatedwiththestatementhandle.TheresultsetofthisCLI
proceduralresultcursoristhefirstresultsetofRSS.TheSQL/CLIapplicationcanlearnthatacursorhas
beenautomaticallyopenedbyinvokingNumResultColstodetermineiftheColumnCountispositive.If
thereismorethanoneresultsetintheresultsetsequence,thentheotherscanbeprocessedoneata
timeorinparallel.Toprocesstheresultsetsoneatatime,oncetheprocessingofagivenresultsetis
complete,theMoreResultsroutineisusedtodeterminewhetherthereareadditionalresultsetsand,if
thereare,topositiontheCLIproceduralresultcursorbeforethefirstrowinthenextresultset.Toprocess
theresultsetsinparallel,theNextResultroutineisusedtodeterminewhetherthereareadditionalresult
setsand,ifthereare,topositionaCLIproceduralresultcursorassociatedwithanotherstatementhandle
beforethefirstrowinthenextresultset.
6 ©ISO/IEC2023–Allrightsreserved

ISO/IEC9075-3:2023(E)
4.2 IntroductiontoSQL/CLI
WhenaCLIproceduralresultcursorisassociatedwitharesultset,theoperationalsensitivity,scrollab-
ility,andholdabilitypropertiesoftheCLIproceduralresultcursorarethoseoftheresultsetasitwas
receivedfromthestoredprocedure.(TheCURSORSENSITIVITY,CURSORSCROLLABLE,andCURSOR
HOLDABLEattributesoftheallocatedSQL-statementareignored;usingSetStmtAttrtosettheseattributes
hasnoeffectonthecorrespondingoperationalpropertiesofaCLIproceduralresultcursor.)Theopera-
tionalreturnabilitypropertyofaCLIproceduralresultcursorisimplementation-defined(IV031).ACLI
proceduralresultcursorisnotupdatable.Otherwise,aCLIproceduralresultcursorisprocessedinthe
samewayasaCLIpreparedcursor.
Specialroutines,calledcatalogroutinesareavailabletoreturnresultsetsfromtheInformationSchema.
Theseroutinesaredescribedhere.
— ColumnPrivileges:Returnsalistoftheprivilegesheldonthecolumnswhosenamesadheretothe
requestedpattern(s)withinasinglespecifiedtable.Mostofthisinformationcanalsobeobtained
byusingtheExecDirectroutinetoissueanappropriatequeryontheCOLUMN_PRIVILEGESview
oftheInformationSchema.
— Columns:Returnsthecolumnnamesandattributesforallcolumnswhosenamesadheretothe
requestedpattern(s).MostofthisinformationcanalsobeobtainedbyusingtheExecDirectroutine
toissueanappropriatequeryontheCOLUMNSviewoftheInformationSchema.
— ForeignKeys:Returnseithertheprimarykeyofasinglespecifiedtabletogetherwiththeforeign
keysinallothertablesthatreferencethatprimarykeyortheforeignkeysofasinglespecifiedtable
togetherwithalltheprimaryanduniquekeysinallothertablesthatarereferencedbythoseforeign
keys.MostofthisinformationcanalsobeobtainedbyusingtheExecDirectroutinetoissuean
appropriatequeryontheTABLE_CONSTRAINTSviewandtheREFERENTIAL_CONSTRAINTSview
oftheInformationSchema.
— PrimaryKeys:Returnsalistofthecolumnsthatconstitutetheprimarykeyofasinglespecified
table.MostofthisinformationcanalsobeobtainedbyusingtheExecDirectroutinetoissuean
appropriatequeryontheTABLE_CONSTRAINTSviewandtheKEY_COLUMN_USAGEviewofthe
InformationSchema.
— SpecialColumns:Returnsalistofthecolumnsthatcanuniquelyidentifyaparticularrowwithina
singlespecifiedtable.MostofthisinformationcanalsobeobtainedbyusingtheExecDirectroutine
toissueanappropriatequeryontheCOLUMNSviewoftheInformationSchema.
— Tables:Returnsinformationaboutthetableswhosenamesadheretotherequestedpattern(s)and
type(s).MostofthisinformationcanalsobeobtainedbyusingtheExecDirectroutinetoissuean
appropriatequeryontheTABLESviewoftheInformationSchema.
— TablePrivileges:Returnsalistoftheprivilegesheldontableswhosenamesadheretotherequested
pattern(s).MostofthisinformationcanalsobeobtainedbyusingtheExecDirectroutinetoissue
anappropriatequeryontheTABLE_PRIVILEGESviewoftheInformationSchema.
Thesespecialroutinesareonlyavailableforasmallportionofthemetadatathatisavailableinthe
InformationSchema.Othermetadata(forexample,thataboutSQL-invokedroutines,triggers,anduser-
definedtypes)canbeobtainedbyexecutingappropriatequeriesontheviewsoftheInformationSchema.
TheGetPosition,GetLength,andGetSubStringroutinescaneachbeusedwithitsownindependent
statementhandletoaccessastringvalueattheserverthatisrepresentedbyaLargeObjectlocatorin
ordertodoanyofthefollowing.
— TheGetPositionroutinemaybeusedtodeterminewhetheragivensubstringexistswithinthat
stringand,ifitdoes,toobtainanintegervaluethatindicatesthestartingpositionofthefirst
appearanceofthegivensubstring.
— TheGetLengthroutinemaybeusedtoobtainthelengthofthatstringasaninteger.
©ISO/IEC2023–Allrightsreserved 7

ISO/IEC9075-3:2023(E)
4.2 IntroductiontoSQL/CLI
— TheGetSubStringroutinemaybeusedtoretrieveaportionofastring,oralternatively,tocreatea
newLargeObjectvalueattheserverwhichisaportionofthestringandtoreturnaLargeObject
locatorthatrepresentsthatvalue.
TheError,GetDiagField,andGetDiagRecroutinesobtaindiagnosticinformationaboutthemostrecent
routineoperatingonaparticularresource.TheErrorroutinealwaysretrievesinformationfromthenext
statusrecord,whereastheGetDiagFieldandGetDiagRecroutinesmaybeusedtoretrieveinformation
fromanidentifiedstatusrecord.
ThenumberofrowsaffectedbythelastexecutedSQL-statementcanbeobtainedbyusingtheRowCount
orGetDiagFieldroutine.
AnSQL-transactionisterminatedbyusingtheEndTranroutine.AnSQL-transactionisimplicitlyinitiated
wheneveraCLIroutineisinvokedthatrequiresthecontextofanSQL-transactionandnoSQL-transaction
isactive.AnSQL-transactionisexplicitlystarted,anditscharacteristicsset,byusingtheStartTranroutine.
NOTE1—ApplicationsareprohibitedfromusingtheExecDirectorExecuteroutinestoexecute ...