HP NonStop SQL/MP Programming 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 Found ation, 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 . © 19 88, 1989, 19 90 Ment at
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.

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
Dynamic SQL
SQL/MP Version Management

1-6

2. Host Variables

Specifying a Declare Section 2-1
Coding Host Variable Names
Using Corresponding SQL and C Data Types
Specifying Host Variables in SQL Statements 2-6
Declaring and Using Host Variables

1-5

1-7

2-2

2-3

2-7

Fixed-Length Character Data
Variable-Length Character Data
Structures
Decimal Data Types 2-11

2-9

Hewlett-Packard Company—429847-008

2-7

2-9

i

Contents

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

2. Host Variables (continued)

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

Single-Row SELECT Statement 4-4

Using a Column Value to Select Data
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

INSERT Statement 4-8

Inserting a Single Row

4-9

4-3

4-5

4-7

Inserting a Null Value
Inserting a Timestamp Value

UPDATE Statement

4-10

Updating a Single Row 4-11

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

4-9

4-10

ii

Contents

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
SQLCAFSCODE
SQLCAGETINFOLIST
SQLCATOBUFFER 5-14
SQLGETCATALOGVERSION 5-18
SQLGETOBJECTVERSION
SQLGETSYSTEMVERSION
SQLSADISPLAY 5-20

5-3
5-8

5-9

5-19

5-19

6. Explicit Program Compilation

Explicit Program Compilation 6-1
Developing a C Program in the Guardian Environment

Using TACL DEFINEs in the Guardian Environment
Specifying the SQL Pragma in the Guardian Environment 6-7

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

6-5

6-6

iii

Contents

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

Interactive Commands

7-5

Programmatic Commands

7-4

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
Preventing Program Invalidation

Automatic SQL Recompilation

Causes of Automatic Recompilation 8-6

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

8-4

8-4

8-5

iv

Contents

8. Program Invalidation and Automatic SQL

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

Recompilation (continued)

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
Using Dynamic SQL Cursors 10-20

Developing a Dynamic SQL Program

Specify the SQL Pragma 10-23
Copy any External Declarations
Declare the sqlcode Variable and Host Variables 10-23
Specify Any WHENEVER Directives
Specify the INCLUDE STRUCTURES Directive
Declare the SQLDA Structure and Names Buffer 10-24
Declare an SQLSA Structure

10-18

10-23

10-23

10-23

10-24

10-24

Process the Input Parameters
Read and Compile the SQL Statement
Process the Output Variables
Perform the Database Request and Display the Values 10-27

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

10-24

10-25

10-25

v

Contents

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_
CPRL_GETNUMTABLE_
CPRL_GETSPECIALTABLE_
CPRL_GETUPSHIFTTABLE_ 11-20
CPRL_INFO_ 11-20
CPRL_READOBJECT_
CPRL_UPSHIFT_

11-23

11-18

11-19

11-22

A. SQL/MP Sample Database

11-17

B. Memory Considerations

SQL/MP Internal Structures B-1
Using the SQLMEM Pragma
Estimating Memory Requirements
Avoiding Memory Stack Overflows B-4

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

B-2

B-2

vi

Contents

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

C. Maximizing Local Autonomy

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 6-1. Sample SQL Compiler Listing 6-25
Example 9-1. Checking the sqlcode Variable 9-5
Example 9-2.
Example 9-3.
Example 9-4. Version 300-325 SQLSA Structure 9-15
Example 9-5.
Example 10-1.

Example of the SQLSADISPLAY Display 5-22

Enabling and Disabling the WHENEVER Directive 9-9
Using the WHENEVER Directive 9-10

Version 330 (or later) SQLSA Structure 9-16

SQLDA Structure and Buffers 10-7
Example 10-2.
Example 10-3.
Example 10-4. Allocating the SQLDA Structure 10-30
Example 10-5.
Example 10-6.
Example 10-7. Basic Dynamic SQL Program 10-39

Getting Parameter Values 10-15

Using Statement and Cursor Host Variables 10-22

Allocating Memory for Parameters and Columns 10-32

Displaying Output 10-34

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

vii

Contents

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

Examples (continued)

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.

Table 2-3.
Table 3-1. Summary of SQL/MP Statements and Directives 3-3
Table 3-2.
Table 4-1. SQL/MP Statements for Data Retrieval and Modification 4-1
Table 4-2.
Table 5-1.
Table 5-2. Guardian System Procedures That Return SQL Information 5-2
Table 5-3.
Table 5-4.
Table 5-5.
Table 6-1.

Corresponding SQL and C Numeric, Date-Time, and INTERVAL Data
Types 2-4

Date-Time and INTERVAL Data Types 2-13

C Compiler Pragmas for SQL/MP 3-7

Determining the Cursor Position 4-16
SQL/MP System Procedures 5-1

SQLCAGETINFOLIST Procedure Error Codes 5-11
SQLCAGETINFOLIST Procedure Item Codes 5-11
SQLSADISPLAY Procedure Display Elements 5-22
C Compilers 6-2

Table 6-2. Compilation Mode and Execution Environment 6-2

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

viii

Contents

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-T ime 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

Tables (continued)

Table D-2. Version 2 SQLDA Structure Fields D-4
Table D-3. Version 1 SQLDA Structure Fields D-6

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

ix

Contents

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

x

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.

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

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.

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

xi

What’s New in This 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.



Changes to the H06.21/J06.06 manual

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

xii

What’s New in This 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



Changes in the G06.26 Manual

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

.

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

xiii

What’s New in This Manual

Changes in the G06.26 Manual

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

xiv

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 st atements 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



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

xv

About This Manual

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

SQL/MP Reference Manual Describes the NonStop SQL/MP language

SQL/MP Messages Manual Describes error and warning numbers and

SQL/MP Query Guide Describes how to retrieve and modify data in a

Related Manuals

database management system.

elements, including expressions, functions,
commands, statements, SQLCI utilities and
commands, and report writer commands.
This manual is the printed version of Online
Help.

messages returned by NonStop SQL, the SQL
file system, and FastSort.

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 NonSto p 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

Describes the NonStop SQL/MP programmatic
interface for C and COBOL applications.

COBOL

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

xvi

About This Manual

SQL/MP
Glossary

Introduction
to NonStop

SQL/MP

Guides

SQL/MP
Installation
and
Management
Guide

SQL/MP
Report Writer
Guide

SQL/MP
Version
Management
Guide

Reference Manuals

SQL/MP
Messages
Manual

Programming Manuals

VST001.vsd

SQL/MP
Reference
Manual

SQL/MP
Programming
Manual for
COBOL

SQL/MP
Programming
Manual for C

SQL/MP
Query Guide

Figure i shows the manuals in the NonStop SQL/MP library.

Figure i. NonStop SQL/MP Library

Related Manuals

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

xvii

About This Manual

Program Developme nt Manuals

Guardian Manuals

Guardian
Procedures
Calls
Reference
Manual

Guardian
Application
Conversion
Guide

Guardian
Programmer's
Guide

Guardian
Procedure
Errors and
Messages
Manual

Open Sys tem Service s (OSS) Manuals

OSS
Libra ry Ca lls
Reference
Manual

OSS
Programmer's
Guide

OSS
System Calls
Reference
Manual

OSS
Shell and
Utilities
Reference
Manual

C/C++
Programmer's
Guide

Accelerator
Manual

Binder
Manual

nld and nof t
Manual

Inspect
Manual

Guardian
TNS C
Library Calls
Reference
Manual

CROSSREF
Manual

Guardian
TNS/R
Native
C Library
Calls Ref
Manual

VST011.vsd

CRE
Programmer’s
Guide

Debug
Manual

In addition to the NonStop SQL/MP library, program development, Guardian, and OSS
manuals can be useful to a C programmer. They are shown in Figure ii and described
in Table ii, Table iii on page xx, and Table iv on page xx.

Figure ii. Program Development, System and OSS Manuals

Related Manuals

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

xviii

About This Manual

Table ii. Program Development Manuals

Manual Description

C /C++Programmer’s Guide Describes HP extensions to the C and C++

nld and noft Manual Describes how to use the native link editor (nld)

Binder Manual Describes the Binder program, an interactive linker

Accelerator Manual Describes how to use the Accelerator to optimize

CRE Programmer’s Guide Describes the Common Run-Time Environment

Related Manuals

languages, including how to write applications that
run in either the Guardian or OSS environments.

and the native object file tool (noft).

that enables you to examine, modify, and combine
object files and to generate load maps and cross­reference listings.

TNS object code for the TNS/R execution
environment.

(CRE) and how to write and run mixed-language
programs.

CROSSREF Manual Describes the CROSSREF program, which

produces a cross-reference listing of selected
identifiers in an application.

Guardian TNS C Library Calls
Reference Manual

Describes the C run-time library available to TNS
and accelerated programs in the Guardian
environment.

Guardian TNS/R Native C Library
Calls Reference Manual

Describes the C run-time library available to TNS/R
programs in the Guardian environment.

Inspect Manual Describes the Inspect program, an interactive

source-level or machine-level debugger that
enables you to interrupt and resume program
execution and to display and modify variables.

Debug Manual Describes the Debug program, an interactive

machine-level debugger.

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

xix

About This Manual

Table iii. Guardian Manuals

Manual Description

Guardian Programmer’s Guide Describes how to use Guardian procedure calls

Notation Conventions

from an application to access operating system
services.

Guardian Procedure Calls Reference
Manual

Guardian Procedure Errors and
Messages Manual

Guardian Application Conversion
Guide

Describes the syntax for Guardian procedure calls.

Describes error codes, error lists, system
messages, and trap numbers for Guardian system
procedures.

Describes how to convert C, COBOL, Pascal, TAL,
and T A CL applications to use t he extended features
of the HP NonStop operating system.

Table iv. Open System Services (OSS) Manuals

Manual Description

Open System Services Library Calls
Reference Manual

Open System Services Programmer’s
Guide

Open System Services Shell and
Utilities Reference Manual

Open System Services System Calls
Reference Manual

Describes the syntax and semantics of the C run­time library in the OSS environment.

Describes how to use the OSS application
programming interface to the operating system.

Describes the syntax and semantics for using the
OSS shell and utilities.

Describes the syntax and programming
considerations for using OSS system calls.

Notation Conventions

General Syntax Notation

This list summarizes the conventions for syntax presentation in this manual.

UPPERCASE LETTERS. Uppercase letters indicate keywords and reserved words; enter

these items exactly as shown. Items not enclosed in brackets are required. For
example:

EXEC SQL CONTROL EXECUTOR PARALLEL EXECUTION ON;

lowercase italic letters. Lowercase italic letters indicate variable items that you supply.

Items not enclosed in brackets are required. For example:

#pragma SQL [ option ]
[ ( option [ , option ]... ) ]

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

xx

About This Manual

General Syntax Notation

Computer type. Computer type letters within text indicate C and Open System Services

(OSS) keywords and reserved words; enter these items exactly as shown. For
example:

SYSTYPEþOSS

[ ] Brackets. Brackets enclose optional synta x items. For example:

OUT [ list-file ]

A group of items enclosed in brackets is a list from which you can choose one item or
none. The items in the list can be arranged either vertically, with aligned brackets on
each side of the list, or horizontally, enclosed in a pair of brackets and separated by
vertical lines. For example:

[ , PAGES num-pages ]
[ , SQLMAP ]
[ , WHENEVERLIST ]
[ , RELEASE1 | RELEASE2 ]

{ } Braces. A group of items enclosed in braces is a list from which you are required to

choose one item. The items in the list can be arranged either vertically, with aligned
braces on each side of the list, or horizontally, enclosed in a pair of braces and
separated by vertical lines. For example:

{ PAGE[S] }
{ BYTE[S] }
{ MEGABYTE[S} }

| Vertical Line. A vertical line separates alternatives in a horizontal list that is enclosed in

brackets or braces. For example:

[ RECOMPILEONDEMAND | RECOMPILEALL ]

… Ellipsis. An ellipsis immediately following a single syntax item indicates that you can

repeat that syntax item any number of times. An ellipsis immediately following a pair of
brackets or braces indicates that you can repeat the enclosed sequence of syntax
items any number of times. For example:

[ , run-option ]...
An ellipsis in a programming example indicates that one or more lines of source code

have been omitted.

#include <cextdecs(SQLCAFSCODE)>
...
short fserr;
EXEC SQL INCLUDE SQLCA;
...
fserr = SQLCAFSCODE ((short *) &sqlca);
...

Punctuation. Parentheses, commas, semicolons, and other symbols not previously

described must be entered as shown.

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

xxi

About This Manual

HP Encourages Your Comments

Item Spacing. Spaces shown between items are required unless one of the items is a

punctuation symbol such as a parenthesis or a comma. For example:

SQLCOMP / IN object-file [ , OUT [ list-file ] ] /

If there is no space between two items, spaces are not permitted. In this example,
there are no spaces permitted between the period and any other items:

$process-name.#su-name

i and o. In the syntax diagrams for system procedure calls, i and o are used as follows:

/* i */ Input parameter–passes data to the procedure
/* o */ Output parameter–returns data to the calling program
/* i:o */ Input and output parameter–both passes and returns data

An example of the syntax for a procedure call is as follows:

#include <cextdecs(SQLCAFSCODE)>

short SQLCAFSCODE ( short *sqlca, /* i */
[ short first_flg ] ); /* i */

HP Encourages Your Comments

HP encourages your comments concerning this document. We are committed to
providing documentation that meets your needs. Send any errors found, suggestions
for improvement, or compliments to [e-mail].

Include the document title, part number, and any comment, error found, or suggestion
for improvement you have concerning this document.

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

xxii

1 Introduction

NonStop SQL/MP is the HP relational database management system (RDBMS) that
uses SQL to define and manipulate data in an SQL/MP database. You can run SQL
statements interactively by using the SQL/MP conversational interface (SQLCI) or
programmatically by embedding SQL statements and directives in a host-language
program written in COBOL, C, Pascal, or TAL. This manual describes the
programmatic interface to NonStop SQL/MP for C programs.

This section discusses:

Advantages of Using Embedded SQL Statements



Developing a C Program



Dynamic SQL on page 1-6



SQL/MP Version Management on page 1-7



Advantages of Using Embedded
SQL Statements

Using embedded SQL statements and directives in a C program to access an SQL/MP
database has these advantages:

A high-level, efficient database language––You code a request to access the



database using SQL statements. The SQL/MP optimizer then generates an
efficient path to perform your request.

Insulation against database changes––If a database administrator modifies an



SQL/MP database (for example, adds a column to a table), the change does not
affect the logic of your program.

Use of C statements to process data––You can access the database using SQL



statements and then use C statements to process and manipulate the data.
System support for data consistency––If you require audited tables and views, the



system maintains data consistency with the locking feature and the HP NonStop
Transaction Management Facility (TMF) subsystem.

Developing a C Program

You can embed static or dynamic SQL statements in a C source file. You embed a
static SQL statement as an actual SQL statement and run the SQL compiler to
explicitly compile the statement before you run the program. For a dynamic SQL
statement, you code a placeholder variable for the statement, and then construct,
SQL compile, and run the statement at run time.

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

1-1

Introduction

Declaring and Using Host Variables

A host variable is a C variable with a data type that corresponds to an SQL data type.
You use host variables to provide communication between C and SQL statements and
to receive data from a database or to insert data into a database.

You declare host variables in a Declare Section in the variable declarations part of your
program. A Declare Section begins with the BEGIN DECLARE SECTION directive and
ends with the END DECLARE SECTION directive. In this example, host_variable1,

host_variable2, number and name are host variables.

EXEC SQL BEGIN DECLARE SECTION;
int host_variable1; /* int host variable */
char host_variable2[19]; /* char host variable */
struct host_variable_names
{
long number; /* long host variable */
char name[31]; /* char host variable */
} hv_names;
...
EXEC SQL END DECLARE SECTION;

Declaring and Using Host Variables

The C compiler accepts the CHARACTER SET clause in a host-variable declaration to
associate a single-byte or double-byte character set such as Kanji or KSC5601 with a
host variable.

When you specify a host variable in an SQL statement, precede the host variable
name with a colon (:). In C statements, you do not need the colon, as shown:

EXEC SQL SELECT column1 INTO :host_variable1 FROM =table
WHERE column1 > :host_variable2;
strcpy(new_name, host_variable1);

For more information, see Section 2, Host Variables.

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

1-2

Introduction

Embedding SQL/MP Statements and Directives

Embedding SQL/MP Statements and Directives

Table 1-1 lists the SQL/MP statements and directives you can embed in a C program.

Table 1-1. SQL/MP Statements and Directives

Type Statement or Directive

Data Declaration BEGIN DECLARE SECTION and END DECLARE SECTION

INVOKE
INCLUDE STRUCTURES
INCLUDE SQLCA, INCLUDE SQLDA, and INCLUDE SQLSA

Data Definition
Language (DDL)

Data Manipulation
Language (DML)

Data Status
Language (DSL)

Dynamic SQL
Operations

ALTER CATALOG, ALTER COLLATION, ALTER INDEX, ALTER
PROGRAM, ALTER TABLE, and ALTER VIEW

COMMENT
CREATE CATALOG, CREATE COLLATION, CREATE INDEX,

CREATE PROGRAM, CREATE TABLE, and CREATE VIEW
DROP
HELP TEXT
UPDATE STATISTICS
DECLARE CURSOR
OPEN
FETCH
SELECT, INSERT, UPDATE, DELETE
CLOSE
GET CATALOG OF SYSTEM
GET VERSION (for SQL/MP software, catalogs, and objects)
GET VERSION OF PROGRAM
PREPARE
DESCRIBE and DESCRIBE INPUT
EXECUTE and EXECUTE IMMEDIATE

RELEASE
Error Processing WHENEVER
Transaction Control BEGIN WORK, COMMIT WORK, and ROLLBACK WORK

Precede an embedded SQL statement or directive with the EXEC SQL keywords and
terminate it with a semicolon (;).

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

1-3

Introduction

Example 1-1 shows an example of static SQL statements embedded in a C program:

Example 1-1. Static SQL Statements in a C Program

/* C variable declarations */
...
EXEC SQL BEGIN DECLARE SECTION;

struct in_parts_struc /* host variables */
{
short in_partnum;
long in_price;
char in_partdesc[19];
} in_parts;
EXEC SQL END DECLARE SECTION;

void insert_function(void)
{
...
in_parts.in_partnum = 4120;
in_parts.in_price = 6000000;
strcpy (in_parts.in_partdesc, "V8 DISK OPTION ");
EXEC SQL
INSERT INTO $vol5.sales.parts (partnum, price, partdesc)
VALUES (:in_parts.in_partnum,
SETSCALE (:in_parts.in_price,2), /* scale is 2. */
:in_parts.in_partdesc);
... }

Calling SQL/MP System Procedures

For more information, see Section 3, SQL/MP Statements and Directives and

Section 4, Data Retrieval and Modification.

Calling SQL/MP System Procedures

NonStop SQL/MP provides system procedures, written in TAL, that perform various
SQL operations and functions. For example, the SQLCADISPLAY procedure returns
error information from the SQLDA structure after an SQL statement runs.

You call SQL system procedures from a C program in the same manner you call other
system procedures (for example, FILE_OPEN_, FILE_CLOSE_, or WRITEREAD). The
cextdecs header file contains source declarations for these procedures that you can
include in a program. This example calls the SQLCADISPLAY procedure by using all
default parameters:

#include <cextdecs(SQLCADISPLAY)>
...
SQLCADISPLAY( (short *) &sqlca);
... /* Process information from the SQLCA structure */

For more information, see Section 5, SQL/MP System Procedures

Character Processing Rules (CPRL) Procedures.

and Section 11,

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

1-4