HP NonStop SQL/MP Owner's manual

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
© Copyright 2012 Hewlett-Packard Development Company, L.P.
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.
© 1990, 1991, 1992, 1993 Open Software Foundation, Inc. The OSF documentation and the OSF
software to which it relates are derived in part from materials supplied by the following:© 1987, 1988,
1989 Carnegie-Mellon University. © 1989, 1990, 1991 Digital Equipment Corporation.
© 1985, 1988, 1989, 1990 Encore Computer Corporation. © 1988 Free Software Foundation, Inc. ©
1987, 1988, 1989, 1990, 1991
Hewlett-Packard Company. © 1985, 1987, 1988, 1989, 1990, 1991, 1992 International Business
Machines Corporation. © 1988, 1989 Massachusetts Institute of Technology. © 1988, 1989, 1990 Mentat
Inc. © 1988 Microsoft Corporation. © 1987, 1988, 1989, 1990, 1991, 1992 SecureWare, Inc. © 1990,
1991 Siemens Nixdorf Informationssysteme AG. © 1986, 1989, 1996, 1997 Sun Microsystems, Inc. ©
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
Elz. © 1980, 1981, 1982, 1983, 1985, 1986, 1987, 1988, 1989 Regents of the University of California.

Hewlett-Packard Company—429847-008
i
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
ii
2. Host Variables (continued)
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)
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
iv
6. Explicit Program Compilation (continued)
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
v
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
vi
10. Dynamic SQL Operations (continued)
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

Contents
HP NonStop SQL/MP Programming Manual for C—429847-008
vii
C. Maximizing Local Autonomy
C. Maximizing Local Autonomy
Using a Local Partition C-1
Using TACL DEFINEs C-2
Using Current Statistics C-2
Skipping Unavailable Partitions C-3
D. Converting C Programs
Generating SQL Data Structures D-1
Generating SQLDA Structures D-2
Generating a Version 300 (or Later) SQLDA Structure D-3
Generating a Version 2 SQLDA Structure D-3
Generating a Version 1 SQLDA Structure D-6
Planning for Future PVUs D-8
SQL/MP Version Procedures D-8
RELEASE1 and RELEASE2 Options D-8
Index
Examples
Example 1-1. Static SQL Statements in a C Program 1-4
Example 1-2. Dynamic SQL Statements in a C Program 1-6
Example 2-1. Creating Valid DATETIME and INTERVAL Data Types 2-16
Example 2-2. CREATE TABLE Statements 2-20
Example 2-3. Structures Generated by the INVOKE Directive 2-21
Example 4-1. Using a Static SQL Cursor in a C Program 4-14
Example 5-1. Example of the SQLCAGETINFOLIST Procedure 5-13
Example 5-2. Example of the SQLSADISPLAY Display 5-22
Example 6-1. Sample SQL Compiler Listing 6-25
Example 9-1. Checking the sqlcode Variable 9-5
Example 9-2. Enabling and Disabling the WHENEVER Directive 9-9
Example 9-3. Using the WHENEVER Directive 9-10
Example 9-4. Version 300-325 SQLSA Structure 9-15
Example 9-5. Version 330 (or later) SQLSA Structure 9-16
Example 10-1. SQLDA Structure and Buffers 10-7
Example 10-2. Getting Parameter Values 10-15
Example 10-3. Using Statement and Cursor Host Variables 10-22
Example 10-4. Allocating the SQLDA Structure 10-30
Example 10-5. Allocating Memory for Parameters and Columns 10-32
Example 10-6. Displaying Output 10-34
Example 10-7. Basic Dynamic SQL Program 10-39

Contents
HP NonStop SQL/MP Programming Manual for C—429847-008
viii
Examples (continued)
Examples (continued)
Example 10-8. Detailed Dynamic SQL Program 10-44
Example A-1. COPYLIB File for Sample Database A-3
Example D-1. Version 2 SQLDA Structure D-4
Example D-2. Version 1 SQLDA Structure D-6
Figures
Figure i. NonStop SQL/MP Library xvii
Figure ii. Program Development, System and OSS Manuals xviii
Figure 6-1. Explicit SQL Compilation of a C Program on TNS 6-3
Figure 6-2. Explicit SQL Compilation of a C Program on TNS/R 6-4
Figure 6-3. Explicit SQL Compilation of a C Program on TNS/E 6-5
Figure 6-4. SQL/MP Program File Format 6-24
Figure 7-1. Processes Running on a NonStop System 7-4
Figure 8-1. Timestamp Check 8-8
Figure 10-1. DESCRIBE INPUT’s Effect on Names Buffer 10-18
Figure A-1. SQL/MP Sample Database Relations A-2
Tables
Table i. NonStop SQL/MP Library xvi
Table ii. Program Development Manuals xix
Table iii. Guardian Manuals xx
Table iv. Open System Services (OSS) Manuals xx
Table 1-1. SQL/MP Statements and Directives 1-3
Table 2-1. Corresponding SQL and C Character Data Types 2-3
Table 2-2. Corresponding SQL and C Numeric, Date-Time, and INTERVAL Data
Types 2-4
Table 2-3. Date-Time and INTERVAL Data Types 2-13
Table 3-1. Summary of SQL/MP Statements and Directives 3-3
Table 3-2. C Compiler Pragmas for SQL/MP 3-7
Table 4-1. SQL/MP Statements for Data Retrieval and Modification 4-1
Table 4-2. Determining the Cursor Position 4-16
Table 5-1. SQL/MP System Procedures 5-1
Table 5-2. Guardian System Procedures That Return SQL Information 5-2
Table 5-3. SQLCAGETINFOLIST Procedure Error Codes 5-11
Table 5-4. SQLCAGETINFOLIST Procedure Item Codes 5-11
Table 5-5. SQLSADISPLAY Procedure Display Elements 5-22
Table 6-1. C Compilers 6-2
Table 6-2. Compilation Mode and Execution Environment 6-2

Contents
HP NonStop SQL/MP Programming Manual for C—429847-008
ix
Tables (continued)
Tables (continued)
Table 9-1. C Compiler Pseudocode for Checking the sqlcode Variable 9-6
Table 9-2. C Identifiers Generated by the INCLUDE SQLCA Directive 9-12
Table 9-3. System Procedures for the SQLCA Structure 9-12
Table 9-4. C Identifiers Generated by the INCLUDE SQLSA Directive 9-14
Table 9-5. SQLSA Structure Fields 9-17
Table 10-1. Dynamic SQL Statements 10-2
Table 10-2. C Identifiers Generated by the INCLUDE SQLDA Directive 10-5
Table 10-3. SQLDA Structure Fields 10-5
Table 10-4. SQLDA Data Type Declarations 10-8
Table 10-5. SQLDA Date-Time and INTERVAL Declarations 10-10
Table 10-6. SQLDA Character-Set IDs 10-11
Table 11-1. Character Processing Rules (CPRL) Procedures 11-1
Table B-1. SQL/MP Data Structures B-1
Table B-2. Virtual Memory Requirements for SQL Statements B-3
Table D-1. Changes to SQL Data Structures D-2
Table D-2. Version 2 SQLDA Structure Fields D-4
Table D-3. Version 1 SQLDA Structure Fields D-6

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

HP NonStop SQL/MP Programming Manual for C—429847-008
xi
What’s New in This Manual
Manual Information
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.
Document History
New and Changed Information
Changes to the H06.25/J06.14 manual:
Added -Wsqlconnect compiler option in -Wsqlconnect on page 6-33.
Added -HP_NSK_CONNECT_MODE environment variable option in
HP_NSK_CONNECT_MODE on page 6-34.
Part Number Published
429847-008 August 2012
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

What’s New in This Manual
HP NonStop SQL/MP Programming Manual for C—429847-008
xii
Changes to the H06.21/J06.06 manual
Changes to the H06.21/J06.06 manual
Updated footnote about compiler version support under Table 6-1, C Compilers, on
page 6-2.
Changes to the G06.28 Manual
Added a Note on page 2-5 about the nonsupport for unsigned long long data type.
Changed the format of short output_file_number under SQLCADISPLAY on
pages 5-4 and 5-20.
Updated the information in Section 6, Explicit Program Compilation with the
information from the SQL Supplement for H-series RVUs.
Corrected Example 9-5 on page 9-16.

What’s New in This Manual
HP NonStop SQL/MP Programming Manual for C—429847-008
xiii
Changes in the G06.26 Manual
Corrected two field names in:
Table 9-5 on page 9-17
Table D-1 on page D-2
Changes in the G06.26 Manual
Updated information related to process access:
On page 2-19, for an INVOKE directive
On page 4-4, for a SELECT statement
On page 4-8, for an INSERT statement
On page 4-10, for an UPDATE statement
On page 4-13, for a DELETE statement
On page 4-16, for an SQL cursor
On page 4-19, for an OPEN CURSOR statement
On page 4-20, for a FETCH statement
On page 4-21, for a multirow SELECT statement
On page 4-22, for an UPDATE statement with a cursor
On page 4-23, for a DELETE statement with a cursor
On page 6-21, for an UPDATE STATISTICS statement
Added information about compiling NonStop C programs in the PC environment
under Developing a C Program in a PC Host Environment on page 6-33.
Added information about process access privileges under Required
Access Authority on page 7-1.
Corrected coding error on page 10-50 by shifting code from line 374 to 372.
Changed the real memory from 2 KB to 16 KB pages under Estimating Memory
Requirements on page B-4.

What’s New in This Manual
HP NonStop SQL/MP Programming Manual for C—429847-008
xiv
Changes in the G06.26 Manual

HP NonStop SQL/MP Programming Manual for C—429847-008
xv
About This Manual
This manual describes the NonStop SQL/MP programmatic interface for the HP
implementation of the C language. Using this interface, a C program can access a
NonStop SQL/MP database using embedded SQL statements and directives.
Who Should Read This Guide
This manual is intended for application programmers who are embedding SQL
statements and directives in a C program. The reader should be familiar with:
The C programming language
NonStop SQL/MP terms and concepts as described in the Introduction to
NonStop SQL/MP
The HP NonStop operating system, including either the Guardian or HP NonStop
Open System Services (OSS) environment
Related Manuals
The related manuals that an application programmer might find useful are:
NonStop SQL/MP library
Program development manuals
Guardian system manuals
OSS manuals

About This Manual
HP NonStop SQL/MP Programming Manual for C—429847-008
xvi
Related Manuals
Table i describes the manuals in the HP NonStop SQL/MP library.
Table i. NonStop SQL/MP Library
Manual Description
Introduction to NonStop SQL/MP Introduces the NonStop SQL/MP relational
database management system.
SQL/MP Reference Manual Describes the NonStop SQL/MP language
elements, including expressions, functions,
commands, statements, SQLCI utilities and
commands, and report writer commands.
This manual is the printed version of Online
Help.
SQL/MP Messages Manual Describes error and warning numbers and
messages returned by NonStop SQL, the SQL
file system, and FastSort.
SQL/MP Query Guide Describes how to retrieve and modify data in a
NonStop SQL/MP database and how to analyze
and improve query performance.
SQL/MP Version Management Guide Describes the rules governing version
management for the NonStop SQL/MP
software, catalogs, objects, messages,
programs, and data structures.
SQL/MP Installation and Management
Guide Describes how to plan, install, create, and manage a NonStop SQL/MP database and SQL
programs.
SQL/MP Report Writer Guide Describes how to use report writer commands
and SQLCI options to design and produce
reports.
SQL/MP Programming Manual for C
SQL/MP Programming Manual for
COBOL
Describes the NonStop SQL/MP programmatic
interface for C and COBOL applications.