HP NonStop SQL/MP Manual del propietario

HP NonStop SQL/MP

Programming Manual

for C

Abstract

This manual documents the programming interface to HP NonStop™ SQL/MP for C

and is intended for application programmers who are embedding SQL statements and

directives in a C program.

Product Version

NonStop SQL/MP G06 and H01

Supported Release Version Updates (RVUs)

This publication supports J06.03 and all subsequent J-series RVUs, H06.03 and all subse-

quent H-series RVUs, G06.00 and all subsequent G-series RVUs, and D46.00 and all subse-

quent D-series RVUs, until otherwise indicated by its replacement publications.

Part Number Published

429847-008 August 2012

Document History

Part Number Product Version Published

429847-002 NonStop SQL/MP G06 December 2003

429847-003 NonStop SQL/MP G06 December 2004

429847-004 NonStop SQL/MP G06 April 2005

429847-005 NonStop SQL/MP G06 February 2006

429847-007 NonStop SQL/MP G06 and H01 August 2010

429847-008 NonStop SQL/MP G06 and H01 August 2012

Legal Notices

Legal Notice

Confidential computer software. Valid license from HP required for possession, use or copying.

Consistent with FAR 12.211 and 12.212, Commercial

Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are

licensed to the U.S. Government under vendor’s standard commercial license.

The information contained herein is subject to change without notice. The only warranties for HP

products and services are set forth in the express warranty statements accompanying such products

and services. Nothing herein should be construed as constituting an additional warranty. HP shall not be

liable for technical or editorial errors or omissions contained herein.

Export of the information contained in this publication may require authorization from the U.S.

Department of Commerce.

Microsoft, Windows, and Windows NT are U.S. registered trademarks of Microsoft Corporation.

Intel, Pentium, and Celeron are trademarks or registered trademarks of Intel Corporation or its

subsidiaries in the United States and other countries.

Java® is a registered trademark of Oracle and/or its affiliates.

Motif, OSF/1, UNIX, X/Open, and the "X" device are registered trademarks, and IT DialTone and The

Open Group are trademarks of The Open Group in the U.S. and other countries.

Open Software Foundation, OSF, the OSF logo, OSF/1, OSF/Motif, and Motif are trademarks of the

Open Software Foundation, Inc. OSF MAKES NO WARRANTY OF ANY KIND WITH REGARD TO THE

OSF MATERIAL PROVIDED HEREIN, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED

WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. OSF shall

not be liable for errors contained herein or for incidental consequential damages in connection with the

furnishing, performance, or use of this material.

1987, 1988, 1989, 1990, 1991

1989,1990, 1991 Transarc Corporation.OSF software and documentation are based in part on the

Fourth Berkeley Software Distribution under license from The Regents of the University of California.

OSF acknowledges the following individuals and institutions for their role in its development: Kenneth

C.R.C. Arnold, Gregory S. Couch, Conrad C. Huang, Ed James, Symmetric Computer Systems, Robert

Hewlett-Packard Company—429847-008

HP NonStop SQL/MP

Programming Manual for C

Index Examples Figures Tables

Legal Notices

What’s New in This Manual xi

Manual Information xi

New and Changed Information xi

About This Manual xv

Who Should Read This Guide xv

Related Manuals xv

Notation Conventions xx

HP Encourages Your Comments xxii

1. Introduction

Advantages of Using Embedded SQL Statements 1-1

Developing a C Program 1-1

Declaring and Using Host Variables 1-2

Embedding SQL/MP Statements and Directives 1-3

Calling SQL/MP System Procedures 1-4

Compiling and Executing a Host-Language Program 1-5

Processing Errors, Warnings, and Status Information 1-5

Dynamic SQL 1-6

SQL/MP Version Management 1-7

2. Host Variables

Specifying a Declare Section 2-1

Coding Host Variable Names 2-2

Using Corresponding SQL and C Data Types 2-3

Specifying Host Variables in SQL Statements 2-6

Declaring and Using Host Variables 2-7

Fixed-Length Character Data 2-7

Variable-Length Character Data 2-9

Structures 2-9

Decimal Data Types 2-11

Contents

HP NonStop SQL/MP Programming Manual for C—429847-008

2. Host Variables (continued)

Fixed-Point Data Types 2-11

Date-Time and INTERVAL Data Types 2-13

Using Indicator Variables for Null Values 2-17

Inserting a Null Value 2-17

Testing For a Null Value 2-17

Retrieving Rows With Null Values 2-18

Creating Host Variables Using the INVOKE Directive 2-18

Advantages of Using an INVOKE Directive 2-19

C Structures Generated by the INVOKE Directive 2-19

Using Indicator Variables With the INVOKE Directive 2-22

Using INVOKE With SQLCI 2-24

Associating a Character Set With a Host Variable 2-24

Treatment in C Statements 2-25

VARCHAR Data Type 2-25

3. SQL/MP Statements and Directives

Embedding SQL Statements 3-1

Coding Statements and Directives 3-1

Placing Statements and Directives 3-2

Finding Information 3-3

4. Data Retrieval and Modification

Opening and Closing Tables and Views 4-2

Causes of SQL Error 8204 (Lost Open Error) 4-2

Recovering From SQL Error 8204 4-3

Single-Row SELECT Statement 4-4

Using a Column Value to Select Data 4-5

Using a Primary Key Value to Select Data 4-6

Multirow SELECT Statement 4-6

Simple Example 4-7

A More Complex Example 4-7

The Most Complex Example 4-7

INSERT Statement 4-8

Inserting a Single Row 4-9

Inserting a Null Value 4-9

Inserting a Timestamp Value 4-10

UPDATE Statement 4-10

Updating a Single Row 4-11

Contents

HP NonStop SQL/MP Programming Manual for C—429847-008

iii

4. Data Retrieval and Modification (continued)

Updating Multiple Rows 4-12

Updating Columns With Null Values 4-12

DELETE Statement 4-12

Deleting a Single Row 4-13

Deleting Multiple Rows 4-13

Using SQL Cursors 4-14

Steps for Using a Cursor 4-15

Process Access ID (PAID) Requirements 4-16

Cursor Position 4-16

Cursor Stability 4-17

Virtual Sequential Block Buffering (VSBB) 4-17

DECLARE CURSOR Statement 4-18

OPEN Statement 4-19

FETCH Statement 4-20

Multirow SELECT Statement 4-21

UPDATE Statement 4-22

Multirow DELETE Statement 4-23

CLOSE Statement 4-24

Using Foreign Cursors 4-24

5. SQL/MP System Procedures

Guardian System Procedures 5-2

cextdecs Header File 5-2

SQL Message File 5-2

SQLCADISPLAY 5-3

SQLCAFSCODE 5-8

SQLCAGETINFOLIST 5-9

SQLCATOBUFFER 5-14

SQLGETCATALOGVERSION 5-18

SQLGETOBJECTVERSION 5-19

SQLGETSYSTEMVERSION 5-19

SQLSADISPLAY 5-20

6. Explicit Program Compilation

Explicit Program Compilation 6-1

Developing a C Program in the Guardian Environment 6-5

Using TACL DEFINEs in the Guardian Environment 6-6

Specifying the SQL Pragma in the Guardian Environment 6-7

Contents

HP NonStop SQL/MP Programming Manual for C—429847-008

6. Explicit Program Compilation (continued)

Running the TNS C Compiler in the Guardian Environment 6-9

Running the TNS/R NMC and TNS/E CCOMP Compiler in the Guardian

Environment 6-10

Binding SQL Program Files in the Guardian Environment 6-11

Running the SQL Compiler in the Guardian Environment 6-12

SQL Program File Format 6-24

SQL Compiler Listings 6-25

Developing a C Program in the OSS Environment 6-28

Using TACL DEFINEs in the OSS Environment 6-29

Using the c89 Utility in the OSS Environment 6-30

Developing a C Program in a PC Host Environment 6-33

Using CONTROL Directives 6-34

Static SQL Statements 6-34

Dynamic SQL Statements 6-36

Using Compatible Compilation Tools 6-36

C Compiler 6-36

SQL Compiler 6-36

SQL Program Files 6-37

7. Program Execution

Required Access Authority 7-1

Using TACL DEFINEs 7-2

Entering the TACL RUN Command 7-3

Running a Program in the OSS Environment 7-3

Running a Program at a Low PIN 7-4

Interactive Commands 7-5

Programmatic Commands 7-5

Pathway Environment 7-6

Determining Compatibility With the SQL Executor 7-7

8. Program Invalidation and Automatic SQL Recompilation

Program Invalidation 8-1

SQL Compiler Validation Functions 8-1

Causes of Program Invalidation 8-2

File-Label and Catalog Inconsistencies 8-4

Preventing Program Invalidation 8-4

Automatic SQL Recompilation 8-5

Causes of Automatic Recompilation 8-6

Contents

HP NonStop SQL/MP Programming Manual for C—429847-008

8. Program Invalidation and Automatic SQL

Recompilation (continued)

8. Program Invalidation and Automatic SQL

Recompilation (continued)

Run-Time Recompilation Errors 8-9

Preventing Automatic Recompilations 8-9

9. Error and Status Reporting

Using the INCLUDE STRUCTURES Directive 9-1

Generating Structures With Different Versions 9-3

Checking the Version of the C Compiler 9-3

Sharing Structures 9-3

Returning Error and Warning Information 9-4

Checking the sqlcode Variable 9-4

Using the WHENEVER Directive 9-6

Returning Information From the SQLCA Structure 9-12

Returning Performance and Statistics Information 9-13

Declaring the SQLSA Structure 9-13

Using the SQLSA Structure 9-13

10. Dynamic SQL Operations

Uses for Dynamic SQL 10-1

Dynamic SQL Statements 10-2

Dynamic SQL Features 10-3

SQLDA Structure, Names Buffer, and Collation Buffer 10-3

Input Parameters and Output Variables 10-11

Null Values 10-16

Dynamic Allocation of Memory 10-18

Using Dynamic SQL Cursors 10-20

Developing a Dynamic SQL Program 10-23

Specify the SQL Pragma 10-23

Copy any External Declarations 10-23

Declare the sqlcode Variable and Host Variables 10-23

Specify Any WHENEVER Directives 10-23

Specify the INCLUDE STRUCTURES Directive 10-24

Declare the SQLDA Structure and Names Buffer 10-24

Declare an SQLSA Structure 10-24

Process the Input Parameters 10-24

Read and Compile the SQL Statement 10-25

Process the Output Variables 10-25

Perform the Database Request and Display the Values 10-27

Contents

HP NonStop SQL/MP Programming Manual for C—429847-008

10. Dynamic SQL Operations (continued)

Allocate Memory for the SQLDA Structures and Names Buffers 10-29

Allocate and Fill In Output Variables 10-33

Developing a Dynamic SQL Pathway Server 10-36

Dynamic SQL Sample Programs 10-37

Basic Dynamic SQL Program 10-37

Detailed Dynamic SQL Program 10-42

11. Character Processing Rules (CPRL) Procedures

cextdecs Header File 11-2

CPRL Return Codes 11-2

CPRL_ARE_ 11-3

CPRL_AREALPHAS_ 11-4

CPRL_ARENUMERICS_ 11-5

CPRL_COMPARE1ENCODED_ 11-6

CPRL_COMPARE_ 11-7

CPRL_COMPAREOBJECTS_ 11-8

CPRL_DECODE_ 11-9

CPRL_DOWNSHIFT_ 11-10

CPRL_ENCODE_ 11-11

CPRL_GETALPHATABLE_ 11-12

CPRL_GETCHARCLASSTABLE_ 11-13

CPRL_GETDOWNSHIFTTABLE_ 11-14

CPRL_GETFIRST_ 11-15

CPRL_GETLAST_ 11-16

CPRL_GETNEXTINSEQUENCE_ 11-17

CPRL_GETNUMTABLE_ 11-18

CPRL_GETSPECIALTABLE_ 11-19

CPRL_GETUPSHIFTTABLE_ 11-20

CPRL_INFO_ 11-20

CPRL_READOBJECT_ 11-22

CPRL_UPSHIFT_ 11-23

A. SQL/MP Sample Database

B. Memory Considerations

SQL/MP Internal Structures B-1

Using the SQLMEM Pragma B-2

Estimating Memory Requirements B-2

Avoiding Memory Stack Overflows B-4

Tabla de contenidos

Otros manuales de Software de HP

HP Brio 7100 Manual de usuario

HP C7791C - DesignJet 130 Color Inkjet Printer Guía de configuración

HP NC320m - PCI Express Gigabit Server Adapter Manual de usuario

HP C7791C - DesignJet 130 Color Inkjet Printer Guía de configuración

HP Surestore Disk Array 12h - And FC60 Manual de usuario

HP Data Protector A.06.11 Guía de configuración rápida

HP Spooler FASTP Manual de usuario

HP T1936AA - Digital Sending Software Instrucciones de instalación

HP PROLIANT 56976839 Cómo utilizar

HP Integrity BL860c Manual de servicio

HP StorageWorks MSA 2/8 - SAN Switch Manual de usuario

HP D6030A - NetServer - E50 Manual de usuario

HP B2355-92068 Manual de instrucciones

HP Pavilion xg900 - Desktop PC Instrucciones de instalación y funcionamiento

HP t5630 - Thin Client Instrucciones de instalación y funcionamiento

HP Guardian Manual de usuario

HP MAILbus 400 Manual de usuario

HP ProBook 6445b - Notebook PC Manual de usuario

HP T1936AA - Digital Sending Software Manual de uso y cuidado

HP AB500A - Integrated Lights-Out Advanced Manual de usuario

HP T5720 - Compaq Thin Client Manual de instrucciones

HP BladeSystem bc2000 - Blade PC Manual de usuario

HP N1200 - StorageWorks Network Storage Router Guía de instalación

HP Surestore Disk Array 12h - And FC60 Manual de referencia

Manuales populares de Software de otras marcas

COMPRO

COMPRO COMPROFM Manual de usuario

Muratec

Muratec OFFICEBRIDGE ONLINE Manual de usuario

Oracle

Oracle Contact Center Anywhere 8.1 Manual de usuario

Adobe

Adobe 65007312 - Photoshop Lightroom Manual

Avaya

Avaya NULL One-X for RIM Blackberry Manual de usuario

PS Audio

PS Audio PowerPlay Manual del propietario

Brady

Brady LOCKOUT PRO 3.0 Manual de servicio

Avaya

Avaya Interaction Center Manual de usuario

Texas Instruments

Texas Instruments TI-83 Plus Silver Edition Manual

Novell

Novell GROUPWISE 8 - INTERNET AGENT Manual de usuario

Oracle

Oracle Application 9i Manual de usuario

Acer

Acer RDM Manual de usuario

Canon

Canon Vixia HF21 Manual de usuario

Canon

Canon ZR950 Manual de usuario

Samsung

Samsung Auto Backup Manual de usuario

Polycom

Polycom Vortex EF2201 Instrucciones de instalación y funcionamiento

Brocade Communications Systems

Brocade Communications Systems Brocade 8/12c Manual de usuario

Novell

Novell SP2 - S Manual de instrucciones