Progress Embedded SQL-89 Guide and Reference

Progress Embedded SQL-89 Guide and Reference

? 2001 Progress Software Corporation. All rights reserved.

Progress? software products are copyrighted and all rights are reserved by Progress Software Corporation. This manual is also copyrighted and all rights are reserved. This manual may not, in whole or in part, be copied, photocopied, translated, or reduced to any electronic medium or machine-readable form without prior consent, in writing, from Progress Software Corporation.

The information in this manual is subject to change without notice, and Progress Software Corporation assumes no responsibility for any errors that may appear in this document.

The references in this manual to specific platforms supported are subject to change.

Progress, Progress Results, Provision and WebSpeed are registered trademarks of Progress Software Corporation in the United States and other countries. Apptivity, AppServer, ProVision Plus, SmartObjects, IntelliStream, and other Progress product names are trademarks of Progress Software Corporation.

SonicMQ is a trademark of Sonic Software Corporation in the United States and other countries.

Progress Software Corporation acknowledges the use of Raster Imaging Technology copyrighted by Snowbound Software 1993-1997 and the IBM XML Parser for Java Edition.

? IB M Corporation 1998-1999. All rights reserved. U.S. Government Users Restricted Rights — Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.

Progress is a registered trademark of Progress Software Corporation and is used by IBM Corporation in the mark Progress/400 under license. Progress/400 AND 400? are trademarks of IBM Corporation and are used by Progress Software Corporation under license.

Java and all Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries.

Any other trademarks and/or service marks contained herein are the property of their respective owners.

.

May 2001

Product Code: 4520

Item Number: 81088;9.1C

Contents Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi Purpose . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi Audience . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi Organization of This Manual . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi Typographical Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xii Syntax Notation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii Progress Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii Other Useful Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix Getting Started . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix

Development Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xx

Reporting Tools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi

4GL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxii

Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii

DataServers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii

SQL-89/Open Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii

SQL-92 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiv

Deployment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiv

WebSpeed. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv

Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv

SQL-92 Reference (These are non-Progress resources available from your technical bookseller.) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvi 1.Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1–1

1.1Progress/ESQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1–2

1.1.1Static and Dynamic ESQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1–2

1.2Prerequisites for Using ESQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1–2

1.3Progress/SQL and Progress/ESQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1–3

1.3.1Identifiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1–4

1.3.2Progress Function Support. . . . . . . . . . . . . . . . . . . . . . . . . . . . 1–4

Contents

iv

1.4Progress/ESQL Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1–5

1.4.1Large Client Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1–5

1.4.2Small Client Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1–5

1.4.3Small Client Model Versus Large Client Model. . . . . . . . . . . . .1–6

1.4.4Configuration and Deployment . . . . . . . . . . . . . . . . . . . . . . . . .1–7

1.5ESQL Directories and Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1–7

1.6Overview of Using ESQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1–9

1.6.1Designing and Programming the Application . . . . . . . . . . . . . .1–10

1.6.2Preprocessing the ESQL Source Files . . . . . . . . . . . . . . . . . . .1–11

1.6.3Compiling the Preprocessed Source Files . . . . . . . . . . . . . . . .1–14

1.6.4Linking the ESQL Executable. . . . . . . . . . . . . . . . . . . . . . . . . .1–14

2.Programming with ESQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–1

2.1Programming Capabilities and Options . . . . . . . . . . . . . . . . . . . . . . . . . .2–2

2.1.1ESQL Sessions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–2

2.1.2Static and Dynamic ESQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–2

2.1.3Progress/ESQL and ESQL-LIB. . . . . . . . . . . . . . . . . . . . . . . . .2–2

2.2Writing an ESQL Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–3

2.3Elements of Progress/ESQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–9

2.3.1Basic Progress/ESQL Syntax. . . . . . . . . . . . . . . . . . . . . . . . . .2–9

2.3.2Reserved Words. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–9

2.3.3Application Function Names. . . . . . . . . . . . . . . . . . . . . . . . . . .2–9

2.3.4SQL Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–10

2.3.5Progress Function Support. . . . . . . . . . . . . . . . . . . . . . . . . . . .2–12

2.3.63GL Access to Database Schema Triggers . . . . . . . . . . . . . . .2–13

2.3.7ESQL Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–15

2.3.8WHENEVER Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–18

2.3.9Progress/ESQL DDL Statements . . . . . . . . . . . . . . . . . . . . . . .2–20

2.4Host Language Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–21

2.4.1Host Language Variables and ESQL-LIB . . . . . . . . . . . . . . . . .2–21

2.4.2Host Language Variables and Dynamic ESQL. . . . . . . . . . . . .2–21

2.4.3ESQL Standard Type Definitions and Conversions . . . . . . . . .2–22

2.4.4Using Host Language Variables . . . . . . . . . . . . . . . . . . . . . . . .2–23

2.5Indicator Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–24

2.5.1Declaring and Specifying Indicator Variables . . . . . . . . . . . . . .2–25

2.5.2Using Indicator Variables to Exchange NULL Values. . . . . . . .2–25

2.5.3Using Indicator Variables to Retrieve NULL Values . . . . . . . . .2–26

2.5.4Using Indicator Variables to Set NULL Values . . . . . . . . . . . . .2–26

2.6Error Handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–26

2.6.1SQLCODE Variable . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–27

2.6.2SQLSTATE Variable. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–29

2.6.3Progress Error Messages. . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–33

2.6.4Function Return Codes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–33

2.7Starting and Ending an ESQL Session . . . . . . . . . . . . . . . . . . . . . . . . . .2–34

Contents

2.7.1Starting a Session . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2–34

2.7.2Ending a Session. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2–37 2.8Connecting and Disconnecting Databases . . . . . . . . . . . . . . . . . . . . . . 2–37

2.8.1Connecting Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2–38

2.8.2Disconnecting Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2–39 2.9Managing Transactions in ESQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2–40

2.9.1Committing Work . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2–41

2.9.2Rolling Back Work . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2–41

2.9.3Using DDL Statements in Transactions. . . . . . . . . . . . . . . . . . 2–41 2.10Checking ESQL Run-time Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . 2–41

2.10.1Using the CAN-DO Function in a Singleton SELECT . . . . . . . 2–42

2.10.2Using the CAN-DO Function in a WHERE Clause. . . . . . . . . . 2–44 2.11Managing Multi-user Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2–44

2.11.1Application Lock Management . . . . . . . . . . . . . . . . . . . . . . . . 2–46 2.12Using Dynamic Embedded SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2–47

2.12.1Differences Between Dynamic and Static ESQL. . . . . . . . . . . 2–47 2.13Executing Dynamic ESQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2–49

2.1

3.1Immediate Execution. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2–49

2.1

3.2Prepared Execution. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2–49

2.1

3.3Elements of Dynamic ESQL Execution . . . . . . . . . . . . . . . . . . 2–50 2.14Using Immediate Execution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2–51 2.15Using Prepared Execution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2–52

2.15.1Elements of Prepared Execution . . . . . . . . . . . . . . . . . . . . . . 2–53

2.15.2Data Exchange in Prepared Execution . . . . . . . . . . . . . . . . . . 2–53

2.15.3Forms of Prepared Execution . . . . . . . . . . . . . . . . . . . . . . . . . 2–54

2.15.4Prepared Execution for Dynamic Cursor Specification . . . . . . 2–54

2.15.5Prepared Execution for Non-cursor Specification . . . . . . . . . . 2–55 2.16SQL Descriptor Area . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2–56

2.16.1Functions for Working with SQL Descriptor Areas. . . . . . . . . . 2–57

2.16.2SQLDA_T Structure Components . . . . . . . . . . . . . . . . . . . . . . 2–61

2.16.3SQLVAR_T Structure Components . . . . . . . . . . . . . . . . . . . . 2–62

2.16.4SQLDA Data Type Management. . . . . . . . . . . . . . . . . . . . . . . 2–64 2.17Executing Dynamic Cursor Specifications . . . . . . . . . . . . . . . . . . . . . . . 2–65

2.17.1Dynamic Cursor SELECT Using Progress/ESQL . . . . . . . . . . 2–66 2.18Using Dynamic Parameter Markers . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2–70

2.18.1Parameter Marker Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2–70

2.18.2Determining Parameter Marker Data Types . . . . . . . . . . . . . . 2–72

2.18.3Writing an Application with Parameter Markers. . . . . . . . . . . . 2–73

2.18.4Using Progress/ESQL and Parameter Markers. . . . . . . . . . . . 2–74

2.18.5Using ESQL-LIB and Parameter Markers . . . . . . . . . . . . . . . . 2–76 2.19Dynamic ESQL Statements for Prepared Execution . . . . . . . . . . . . . . . 2–78

2.19.1Dynamic PREPARE Statement . . . . . . . . . . . . . . . . . . . . . . . . 2–78

2.19.2Dynamic ALLOCATE DESCRIPTOR Statement. . . . . . . . . . . 2–79

2.19.3Dynamic DESCRIBE Statement . . . . . . . . . . . . . . . . . . . . . . . 2–80

2.19.4Dynamic DECLARE CURSOR Statement. . . . . . . . . . . . . . . . 2–80

v

Contents

vi

2.19.5Dynamic OPEN Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–81

2.19.6Dynamic EXECUTE Statement. . . . . . . . . . . . . . . . . . . . . . . . .2–82

2.19.7Dynamic FETCH Statement . . . . . . . . . . . . . . . . . . . . . . . . . . .2–84

2.19.8Dynamic CLOSE Cursor Statement . . . . . . . . . . . . . . . . . . . . .2–86

2.19.9Dynamic DEALLOCATE DESCRIPTOR Statement. . . . . . . . .2–86

3.Building and Running ESQL Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3–1

3.1Choosing an ESQL Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3–2

3.1.1ESQL Large Client Model. . . . . . . . . . . . . . . . . . . . . . . . . . . . .3–2

3.1.2ESQL Small Client Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3–3

3.1.3Differences Between Small and Large Clients . . . . . . . . . . . . .3–6

3.2Overview of Building an ESQL Executable . . . . . . . . . . . . . . . . . . . . . . .3–6

3.3Preprocessing Progress/ESQL Source Files . . . . . . . . . . . . . . . . . . . . .3–7

3.3.1Precompile Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3–8

3.3.2Non-precompile Mode. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3–9

3.3.3ESQL Preprocessor Syntax and Operation . . . . . . . . . . . . . . .3–10

3.4Compiling ESQL Source Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3–12

3.4.1Compiling on UNIX Systems. . . . . . . . . . . . . . . . . . . . . . . . . . .3–12

3.4.2Compiling in Windows. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3–13

3.5Building Applications Containing Windows Resources . . . . . . . . . . . . . .3–13

3.6Linking ESQL Executables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3–14

3.6.1ESQL Client Executables . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3–14

3.6.2Linking for Windows with ESQL Small Client . . . . . . . . . . . . . .3–14

3.6.3PROBUILD and the Linker . . . . . . . . . . . . . . . . . . . . . . . . . . . .3–16

3.7Running ESQL Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3–17

3.7.1Preparing to Run Small and Large Client Applications. . . . . . .3–17

3.7.2Starting ESQL Small Clients . . . . . . . . . . . . . . . . . . . . . . . . . .3–20

3.7.3Connecting Small Clients to Open Interface Drivers. . . . . . . . .3–21

3.7.4Setting OIB and OID UNIX Environment Variables. . . . . . . . . .3–22

3.7.5Starting UNIX Open Interface Brokers . . . . . . . . . . . . . . . . . . .3–23

3.7.6Starting an OID Directly Without a Broker . . . . . . . . . . . . . . . .3–24

3.7.7Starting the Small Client Processes . . . . . . . . . . . . . . . . . . . . .3–24

3.7.8Starting Local Small Clients . . . . . . . . . . . . . . . . . . . . . . . . . . .3–26

3.7.9Organizing OID and Database Startup Parameters . . . . . . . . .3–26

3.7.10Shutting Down an ESQL Small Client Application . . . . . . . . . .3–27

3.8The ESQL Demo Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3–27

3.8.1Static Embedded SQL Demo Applications . . . . . . . . . . . . . . . .3–27

3.8.2Dynamic Embedded SQL Demo Applications. . . . . . . . . . . . . .3–29

4.ESQL-LIB Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4–1

4.1Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4–2

4.1.1Error and Status Returns . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4–2

4.1.2SQL Request Handles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4–2

4.1.3Static ESQL Variables and Literal Constants . . . . . . . . . . . . . .4–3

Contents

4.1.4Dynamic ESQL and the SQL Descriptor Area. . . . . . . . . . . . . 4–3

4.1.5ESQL-LIB Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4–4

4.1.6General Diagnostics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4–7

4.2ESQL-LIB Reference Entries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4–9

sqlald() — Allocate Dynamic SQL Descriptor . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4–10 sqlalloc() — Allocate Dynamic Memory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4–11 sqlapplname() — Set the ESQL Application Name . . . . . . . . . . . . . . . . . . . . . . . . 4–13 sqlcdbind() — Bind Your Error Code Address to ESQL . . . . . . . . . . . . . . . . . . . . 4–15 sqlcommit() — Commit the Database Transaction . . . . . . . . . . . . . . . . . . . . . . . . 4–17 sqlconn() — Connect Database(s) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4–19 sqldelrow() — Delete Row at Open Cursor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4–20 sqldinit() — Initialize an Allocated SQL Descriptor Area . . . . . . . . . . . . . . . . . . . . 4–22 sqldiscon() — Disconnect a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4–24 sqldld() — Deallocate Dynamic Descriptor Area . . . . . . . . . . . . . . . . . . . . . . . . . . 4–25 sqldsize() — Calculate the Size of an SQL Descriptor Area . . . . . . . . . . . . . . . . . 4–27 sqldynclose() — Close an SQL Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4–29 sqldyndesc() — Get Dynamic Column Descriptions . . . . . . . . . . . . . . . . . . . . . . . 4–32 sqldyndescinp() — Get Dynamic Parameter Descriptions . . . . . . . . . . . . . . . . . . 4–34 sqldynexec() — Execute a Dynamic SQL Statement . . . . . . . . . . . . . . . . . . . . . . 4–36 sqldynftch() — Fetch Next Row of Dynamic Cursor . . . . . . . . . . . . . . . . . . . . . . . 4–38 sqldyngetn() — Get a Cursor Name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4–40 sqldynncols() — Get the Number of Columns in a Result Set . . . . . . . . . . . . . . . 4–42 sqldynnparms() — Get the Number of Parameter Markers . . . . . . . . . . . . . . . . . . 4–44 sqldynposfetch() — Fetch Row of Dynamic Open Cursor . . . . . . . . . . . . . . . . . . . 4–46 sqldynprep() — Prepare a Dynamic SQL Statement . . . . . . . . . . . . . . . . . . . . . . 4–49 sqldynrq() — Execute a Dynamic Non-SELECT Statement . . . . . . . . . . . . . . . . . 4–52 sqldynsetn() — Define a Cursor Name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4–54 sqlfetch() — Fetch Next Row of Open Cursor . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4–56 sqlfree() — Deallocate Dynamic Memory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4–58 sqlgetmsg() — Get Next Error Message . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4–60 sqlgetrqhdl() — Get a Dynamic Request Handle . . . . . . . . . . . . . . . . . . . . . . . . . 4–62 sqllogin() — Start a Progress ESQL Session . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4–64 sqllogout() — Terminate a Progress ESQL Session . . . . . . . . . . . . . . . . . . . . . . . 4–67 sqlrequest() — Execute a Static ESQL Statement . . . . . . . . . . . . . . . . . . . . . . . . 4–68 sqlrollback() — Rollback Any Pending Transaction . . . . . . . . . . . . . . . . . . . . . . . 4–71 sqlsetlogoption() — Set a Session-wide Option . . . . . . . . . . . . . . . . . . . . . . . . . . 4–72 sqlupdrow() — Update Row at Open Cursor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4–78 A.Progress ESQL and ANSI Standards . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A–1

A.1Data Type Conversion Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A–2

A.1.1Assignment Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A–2

A.1.2Comparison Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A–2

A.1.3UNION Data Type Compatibility . . . . . . . . . . . . . . . . . . . . . . . A–3

A.2Progress/ESQL Not Addressed in 1989 SQL Standard . . . . . . . . . . . . . A–3

vii

Contents

viii

A.3Standard Behavior Available Only with -Q or -Q2 . . . . . . . . . . . . . . . . . .A–4

A.4Progress/ESQL Extensions to 1989 SQL . . . . . . . . . . . . . . . . . . . . . . . .A–4

B.ESQL Data Type Compatibilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .B–1

B.1SQL and Core Progress Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . .B–2

B.2ESQL Standard C Data Type Definitions . . . . . . . . . . . . . . . . . . . . . . . .B–3

B.3Static ESQL Data Type Matching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .B–3

B.4Dynamic ESQL Data Type Matching and Conversion . . . . . . . . . . . . . .B–4

B.4.1The SQL Descriptor Area and Data Type Conversion . . . . . . .B–5

B.4.2Output Data Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .B–8

B.4.3Input Data Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .B–14

C.V6 HLI to V9 ESQL Migration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .C–1

C.1Migrating SQL Preprocessor-based Applications . . . . . . . . . . . . . . . . . .C–2

C.1.1Using Dynamic ESQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .C–2

C.1.2Calling sqllogout() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .C–2

C.1.3Using Host Language Variables . . . . . . . . . . . . . . . . . . . . . . . .C–2

C.1.4Preparing the Executable . . . . . . . . . . . . . . . . . . . . . . . . . . . . .C–3

C.2Migrating ESQL-LIB-based Applications . . . . . . . . . . . . . . . . . . . . . . . . .C–3

C.2.1Changing Calls to sqlcdbind() . . . . . . . . . . . . . . . . . . . . . . . . .C–3

C.2.2Changing sqlv[] Array References . . . . . . . . . . . . . . . . . . . .C–3

C.2.3Changing Function Return Behavior . . . . . . . . . . . . . . . . . . . .C–3

C.2.4Replacing Calls to sqldynopen() . . . . . . . . . . . . . . . . . . . . . . .C–4

C.2.5Replacing Calls to sqlclose() . . . . . . . . . . . . . . . . . . . . . . . . . .C–4

C.2.6Removing Obsolete Functions . . . . . . . . . . . . . . . . . . . . . . . . .C–4

C.2.7Preparing the Executable . . . . . . . . . . . . . . . . . . . . . . . . . . . . .C–4 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Index-1

Contents

Figures

Figure 1–1:Large Client Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1–5 Figure 1–2:Small Client Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1–6 Figure 1–3:Steps to Build an ESQL Executable . . . . . . . . . . . . . . . . . . . . . . . . . . 1–9 Figure 1–4:ESQL Sample Source File Fragment . . . . . . . . . . . . . . . . . . . . . . . . . . 1–10 Figure 1–5:Preprocessed Source File Fragment (Not Precompiled) . . . . . . . . . . . 1–12 Figure 1–6:Preprocessed Source File Fragment (Precompiled) . . . . . . . . . . . . . . 1–13 Figure 1–7:Large Client with Compiler-functional Layers . . . . . . . . . . . . . . . . . . . 1–15 Figure 2–1:Sample ESQL Application (Not Preprocessed) . . . . . . . . . . . . . . . . . . 2–4 Figure 2–2: . . . . . . . . . . . . . . . . . . . . . .Sample ESQL Application (Preprocessed) 2–8 Figure 2–3:SQLDA_T Memory Map After Allocation . . . . . . . . . . . . . . . . . . . . . . . 2–60 Figure 3–1:Large Client Process Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3–2 Figure 3–2:Typical Local Large Client Configurations . . . . . . . . . . . . . . . . . . . . . . 3–3 Figure 3–3:Small Client Process Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3–4 Figure 3–4:Typical Remote Small Client Configuration . . . . . . . . . . . . . . . . . . . . . 3–5 Figure 3–5:Sample Linker Response File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3–15

ix

Contents

x Tables

Table 1–1:Default ESQL Directory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1–7 Table 1–2:Windows-only Files in Default ESQL Directory . . . . . . . . . . . . . . . . . . .1–8 Table 2–1:Progress/SQL Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–10 Table 2–2:Progress Function Support in ESQL . . . . . . . . . . . . . . . . . . . . . . . . . . .2–12 Table 2–3:Invalid 4GL Keywords in ESQL Schema Triggers . . . . . . . . . . . . . . . .2–14 Table 2–4:Parameter Values of sqlcdbind() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–19 Table 2–5:SQLCODE Return Code Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–27 Table 2–6:Meanings of the +100 Return Code . . . . . . . . . . . . . . . . . . . . . . . . . . .2–27 Table 2–7:SQLCODE Error Code Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–28 Table 2–8:SQLSTATE Error Code Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–30 Table 2–9:Progress Record Locking Phrases . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–44 Table 2–10:SQL Statements and Associated Record Locks . . . . . . . . . . . . . . . . . .2–45 Table 2–11:ESQL-LIB Routines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–57 Table 2–12:SQLDA_T Structure Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–61 Table 2–13:SQLVAR_T Structure Components . . . . . . . . . . . . . . . . . . . . . . . . . . .2–62 Table 2–14:Default Parameter Marker Data Types . . . . . . . . . . . . . . . . . . . . . . . . .2–72 Table 2–15:Data Types for Target Storage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2–85 Table 3–1:Common ESQL Startup Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . .3–18 Table 3–2:OID Connection (-SV) Parameter List . . . . . . . . . . . . . . . . . . . . . . . . . .3–21 Table 3–3:OIB and OID UNIX Environment Variables . . . . . . . . . . . . . . . . . . . . . .3–22 Table 4–1:Static ESQL Variable Declarations . . . . . . . . . . . . . . . . . . . . . . . . . . . .4–3 Table 4–2:ESQL-LIB Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4–4 Table 4–3:Normalized Function Returns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4–7 Table 4–4:Exceptional Function Returns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4–8 Table 4–5:Lock Callback Function Return Codes . . . . . . . . . . . . . . . . . . . . . . . . .4–73 Table B–1:SQL and Progress Core Data Types . . . . . . . . . . . . . . . . . . . . . . . . . .B–2 Table B–2:Progress/SQL Data Types and C Equivalents . . . . . . . . . . . . . . . . . . .B–3 Table B–3:Matching Dynamic SQL and C Data Types . . . . . . . . . . . . . . . . . . . . .B–5 Table B–4:Precision of SQL Descriptor Data Types . . . . . . . . . . . . . . . . . . . . . . .B–6 Table B–5:Scale of SQL Descriptor Data Types . . . . . . . . . . . . . . . . . . . . . . . . . .B–6 Table B–6:Output Length of SQL Descriptor Data Types . . . . . . . . . . . . . . . . . . .B–7 Table B–7:Dynamic Output from a CHARACTER Column . . . . . . . . . . . . . . . . . .B–8 Table B–8:Dynamic Output from an INTEGER Column . . . . . . . . . . . . . . . . . . . . .B–10 Table B–9:Dynamic Output from a DECIMAL Column . . . . . . . . . . . . . . . . . . . . . .B–11 Table B–10:Dynamic Output from a DATE Column . . . . . . . . . . . . . . . . . . . . . . . . .B–12 Table B–11:Dynamic Output from a LOGICAL Column . . . . . . . . . . . . . . . . . . . . . .B–13 Table B–12:Dynamic Input from SQL Character Data Types . . . . . . . . . . . . . . . . . .B–14 Table B–13:Dynamic Input from SQL Date Data Types . . . . . . . . . . . . . . . . . . . . . .B–15 Table B–14:Dynamic Input from SQL Decimal Data Types . . . . . . . . . . . . . . . . . . .B–16 Table B–15:Dynamic Input from SQL Floating Point Data Types . . . . . . . . . . . . . .B–17 Table B–16:Dynamic Input from SQL Integer Data Types . . . . . . . . . . . . . . . . . . . .B–17 Table B–17:Dynamic Input from SQL Logical Data Types . . . . . . . . . . . . . . . . . . . .B–18

Preface

Purpose

You can easily build many database applications using the Progress 4GL. If necessary, you can

use Progress/SQL within a 4GL procedure to perform SQL operations such as joins. However,

there are many instances where you might need to build an application in a host language, such

as C, that can also access Progress databases and DataServers. For these applications, you can

use Progress Embedded SQL (ESQL). Progress ESQL provides an industry-standard means for

your application to read and write Progress data while running in a variety of software

configurations and environments.

This manual describes ESQL and the steps required to use it in your C applications. It also

provides information on the ANSI standards with which ESQL complies and describes the

available Progress extensions that you can use to take advantage of special Progress data

handling capabilities. This is not a tutorial on Structured Query Language (SQL).

Audience

This manual is intended for any programmer or software engineer experienced in building

database applications using the C language, and who is thoroughly familiar with general

database programming concepts and with the use of SQL.

Organization of This Manual

Chapter 1, “Introduction”

Describes the elements of ESQL, including its programming tools and deployment

architecture, and provides an overview of the steps required to build ESQL application.

Progress Embedded SQL-89 Guide and Reference

xii Chapter 2, “Programming with ESQL”

Describes how to produce ESQL source files, using ESQL programming tools to specify SQL statements within your C applications.

Chapter 3, “Building and Running ESQL Applications”

Describes how to choose among the deployment options available with ESQL

architecture, and how to turn your ESQL source files into ESQL executables.

Chapter 4, “ESQL-LIB Reference”

Describes the ESQL-LIB interface, including the data definitions and C function calls that support ESQL application programming.

Appendix A, “Progress ESQL and ANSI Standards”

Describes where Progress/ESQL complies with and extends the ANSI standard upon which ESQL is based.

Appendix B, “ESQL Data Type Compatibilities”

Describes how SQL data types are supported by ESQL, including the large variety of data conversions between C and SQL.

Appendix C, “V6 HLI to V9 ESQL Migration”

Describes the changes you make to a Version 6 HLI application to run it as a Version 9 ESQL application.

Index

Typographical Conventions

This manual uses the following typographical conventions:

?Bold typeface indicates:

– Commands or characters that the user types

– That a word carries particular weight or emphasis

Preface ?Italic typeface indicates:

– Progress variable information that the user supplies

– New terms

– Titles of complete publications

?Monospaced typeface indicates:

– Code examples

– System output

– Operating system filenames and pathnames

The following typographical conventions are used to represent keystrokes:

?Small capitals are used for Progress key functions and generic keyboard keys.

END-ERROR, GET, GO

ALT, CTRL, SPACEBAR, TAB

?When you have to press a combination of keys, they are joined by a dash. You press and hold down the first key, then press the second key.

CTRL-X

?When you have to press and release one key, then press another key, the key names are separated with a space.

ESCAPE H

ESCAPE CURSOR-LEFT

Syntax Notation

The syntax for each component follows a set of conventions:

?Uppercase words are keywords. Although they are always shown in uppercase, you can use either uppercase or lowercase when using them in a procedure.

In this example, ACCUM is a keyword:

SYNTAX

ACCUM aggregate expression

xiii

Progress Embedded SQL-89 Guide and Reference

xiv ?Italics identify options or arguments that you must supply. These options can be defined as part of the syntax or in a separate syntax identified by the name in italics. In the

ACCUM function above, the aggregate and expression options are defined with the

syntax for the ACCUM function in the Progress Language Reference.

?You must end all statements (except for DO, FOR, FUNCTION, PROCEDURE, and REPEAT) with a period. DO, FOR, FUNCTION, PROCEDURE, and REPEAT

statements can end with either a period or a colon, as in this example:

?Square brackets ([]) around an item indicate that the item, or a choice of one of the enclosed items, is optional.

In this example, STREAM stream, UNLESS-HIDDEN, and NO-ERROR are optional:

In some instances, square brackets are not a syntax notation, but part of the language.

For example, this syntax for the INITIAL option uses brackets to bound an initial value list for an array variable definition. In these cases, normal text brackets ( [ ] ) are used:

NOTE:The ellipsis (...) indicates repetition, as shown in a following description.?Braces ({}) around an item indicate that the item, or a choice of one of the enclosed items, is required.

FOR EACH Customer:

DISPLAY Name.

END.

SYNTAX

DISPLAY [ STREAM stream][ UNLESS-HIDDEN ][ NO-ERROR ]

SYNTAX

INITIAL [ constant[ , constant]... ]

Preface

xv In this example, you must specify the items BY and expression and can optionally specify the item DESCENDING, in that order:

In some cases, braces are not a syntax notation, but part of the language.

For example, a called external procedure must use braces when referencing arguments passed by a calling procedure. In these cases, normal text braces ( { } ) are used:? A vertical bar (|) indicates a choice.

In this example, EACH, FIRST, and LAST are optional, but you can only choose one:In this example, you must select one of logical-name or alias :

?Ellipses (...) indicate that you can choose one or more of the preceding items. If a group

of items is enclosed in braces and followed by ellipses, you must choose one or more of those items. If a group of items is enclosed in brackets and followed by ellipses, you can optionally choose one or more of those items.

SYNTAX { BY expression [ DESCENDING ] }

SYNTAX

{ &argument-name }

SYNTAX PRESELECT [ EACH | FIRST | LAST ] record-phrase

SYNTAX CONNECTED ( { logical-name | alias } )

Progress Embedded SQL-89 Guide and Reference

xvi

In this example, you must include two expressions, but you can optionally include more.

Note that each subsequent expression must be preceded by a comma:

In this example, you must specify MESSAGE, then at least one of expression or SKIP, but any additional number of expression or SKIP is allowed:

In this example, you must specify {include-file, then optionally any number of argument or &argument-name = "argument-value", and then terminate with }:

?In some examples, the syntax is too long to place in one horizontal row. In such cases, optional items appear inpidually bracketed in multiple rows in order, left-to-right and top-to-bottom. This order generally applies, unless otherwise specified. Required items also appear on multiple rows in the required order, left-to-right and top-to-bottom. In cases where grouping and order might otherwise be ambiguous, braced (required) or bracketed (optional) groups clarify the groupings.

In this example, WITH is followed by several optional items:

SYNTAX

MAXIMUM ( expression , expression[ , expression]... )

SYNTAX

MESSAGE {expression| SKIP [ (n) ]}...

SYNTAX

{ include-file

[argument| &argument-name = "argument-value" ]... }

SYNTAX

WITH [ ACCUM max-length][expression DOWN ]

[ CENTERED ][n COLUMNS ][ SIDE-LABELS ]

[ STREAM-IO ]

Preface

xvii In this example, ASSIGN requires one of two choices: either one or more of field , or one of record . Other options available with either field or record are grouped with braces and brackets. The open and close braces indicate the required order of options:

Progress Messages

Progress displays several types of messages to inform you of routine and unusual occurrences:?Execution messages inform you of errors encountered while Progress is running a

procedure (for example, if Progress cannot find a record with a specified index field value).

?Compile messages inform you of errors found while Progress is reading and analyzing a procedure prior to running it (for example, if a procedure references a table name that is not defined in the database).

?

Startup messages inform you of unusual conditions detected while Progress is getting ready to execute (for example, if you entered an invalid startup parameter).After displaying a message, Progress proceeds in one of several ways:

?Continues execution, subject to the error-processing actions that you specify, or that are

assumed, as part of the procedure. This is the most common action taken following execution messages.

?Returns to the Progress Procedure Editor so that you can correct an error in a procedure. This is the usual action taken following compiler messages.

?Halts processing of a procedure and returns immediately to the Procedure Editor. This does not happen often.

?

Terminates the current session.SYNTAX ASSIGN { { [ FRAME frame ]

{ field [ = expression ] }

[ WHEN expression ]

} ...

| { record [ EXCEPT field ... ] }

}

Progress Embedded SQL-89 Guide and Reference

xviii Progress messages end with a message number in parentheses. In this example, the message number is 200:

Use Progress online help to get more information about Progress messages. On the Windows platform, many Progress tools include the following Help menu options to provide information about messages:

?Choose Help→ Recent Messages to display detailed descriptions of the most recent Progress message and all other messages returned in the current session.

?Choose Help→ Messages, then enter the message number to display a description of any Progress message. (If you encounter an error that terminates Progress, make a note of the message number before restarting.)

?In the Procedure Editor, press the HELP key (F2 or CTRL-W).

On the UNIX platform, you can use the Progress PRO command to start a single-user mode character Progress client session and view a brief description of a message by providing its number. Follow these steps:

1 ?Start the Progress Procedure Editor:

2 ?Press F

3 to access the menu bar, then choose Help→ Messages.

3 ?Type the message number, and press ENTER. Details about that message number appear.

4 ?Press F4 to close the message, press F3 to access the Procedure Editor menu, and choose

File→ Exit.

** Unknown table name table. (200)

install-dir/dlc/bin/pro

Preface

Other Useful Documentation

This section lists Progress Software Corporation documentation that you might find useful.

Unless otherwise specified, these manuals support both Windows and Character platforms and

are provided in electronic documentation format on CD-ROM.

Getting Started

Progress Electronic Documentation Installation and Configuration Guide (Hard copy only)

A booklet that describes how to install the Progress EDOC viewer and collection on UNIX

and Windows.

Progress Installation and Configuration Guide Version 9 for UNIX

A manual that describes how to install and set up Progress Version 9.1 for the UNIX

operating system.

Progress Installation and Configuration Guide Version 9 for Windows

A manual that describes how to install and set up Progress Version 9.1 for all supported

Windows and Citrix MetaFrame operating systems.

Progress Version 9 Product Update Bulletin

A guide that provides a brief description of each new feature of the release. The booklet

also explains where to find more detailed information in the documentation set about each

new feature.

Progress Application Development Environment — Getting Started(Windows only)

A practical guide to graphical application development within the Progress Application

Development Environment (ADE). This guide includes an overview of the ADE and its

tools, an overview of Progress SmartObject technology, and tutorials and exercises that

help you better understand SmartObject technology and how to use the ADE to develop

applications.

Progress Language Tutorial for Windows and Progress Language Tutorial for Character

Platform-specific tutorials designed for new Progress users. The tutorials use a

step-by-step approach to explore the Progress application development environment using

the 4GL.

xix

Progress Embedded SQL-89 Guide and Reference

xx

Progress Master Glossary for Windows and Progress Master Glossary for Character (EDOC only)

Platform-specific master glossaries for the Progress documentation set. These books are

in electronic format only.

Progress Master Index and Glossary for Windows and Progress Master Index and Glossary for Character (Hard copy only)

Platform-specific master indexes and glossaries for the Progress hard-copy documentation

set.

Progress Startup Command and Parameter Reference

A reference manual that describes the Progress startup commands and parameters in

alphabetical order.

Welcome to Progress (Hard copy only)

A booklet that explains how Progress software and media are packaged. An icon-based

map groups the documentation by functionality, providing an overall view of the

documentation set. Welcome to Progress also provides descriptions of the various services

Progress Software Corporation offers.

Development Tools

Progress ADM 2 Guide

A guide to using the Application Development Model, Version 2 (ADM 2) application

architecture to develop Progress applications. It includes instructions for building and

using Progress SmartObjects.

Progress ADM 2 Reference

A reference for the Application Development Model, Version 2 (ADM 2) application. It

includes descriptions of ADM 2 functions and procedures.

Progress AppBuilder Developer’s Guide(Windows only)

A programmer’s guide to using the Progress AppBuilder visual layout editor. AppBuilder

is a Rapid Application Development (RAD) tool that can significantly reduce the time and

effort required to create Progress applications.

Progress Basic Database Tools(Character only; information for Windows is in online help)

A guide for the Progress Database Administration tools, such as the Data Dictionary.

Preface Progress Basic Development Tools (Character only; information for Windows is in online help)

A guide for the Progress development toolset, including the Progress Procedure Editor and

the Application Compiler.

Progress Debugger Guide

A guide for the Progress Application Debugger. The Debugger helps you trace and correct

programming errors by allowing you to monitor and modify procedure execution as it

happens.

Progress Help Development Guide (Windows only)

A guide that describes how to develop and integrate an online help system for a Progress

application.

Progress Translation Manager Guide (Windows only)

A guide that describes how to use the Progress Translation Manager tool to manage the

entire process of translating the text phrases in Progress applications.

Progress Visual Translator Guide (Windows only)

A guide that describes how to use the Progress Visual Translator tool to translate text

phrases from procedures into one or more spoken languages.

Reporting Tools

Progress Report Builder Deployment Guide (Windows only)

An administration and development guide for generating Report Builder reports using the

Progress Report Engine.

Progress Report Builder Tutorial (Windows only)

A tutorial that provides step-by-step instructions for creating eight sample Report Builder

reports.

Progress Report Builder User’s Guide (Windows only)

A guide for generating reports with the Progress Report Builder.

Progress Results Administration and Development Guide (Windows only)

A guide for system administrators that describes how to set up and maintain the Results

product in a graphical environment. This guide also describes how to program, customize,

and package Results with your own products. In addition, it describes how to convert

character-based Results applications to graphical Results applications.

xxi

本文来源：https://www.bwwdw.com/article/u29q.html