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

Introduction

Compiling and Executing a Host-Language Program

Compiling and Executing a Host-Language Program

The steps to compile and run a C program that contains embedded SQL statements
are similar to the steps you follow for a C program that does not contain embedded
SQL statements. You must perform only one extra step for a host-language program:
compiling the embedded SQL statements using the SQL compiler.

1. Compile the C source file (or files) that contain the embedded SQL statements
using the C compiler. The C compiler generates an object file that contains
C object code and SQL source statements.

2. If necessary, use the Binder program in the TNS environment or the native link
editor utility (nld) in the TNS/R environment to combine multiple object files into
one executable object file.

3. If you compiled the program in the TNS environment but plan to run it in the TNS/R
environment, consider running the Accelerator for the C object file as an optional
step to optimize the object code.

4. Run the SQL compiler (SQLCOMP) to compile the SQL source statements in the
C object file and to validate the output SQL program file for execution.

5. Run the SQL program file from a terminal using the TACL RUN (or RUND)
command or from a process using a system procedure such as NEWPROCESS or
PROCESS_CREATE_.

Version 315 (or later) SQL/MP software supports the development of C programs
containing embedded SQL statements in both the Guardian and OSS environments.
For more information, see Section 6, Explicit Program Compilation and Section 7,

Program Execution.

Processing Errors, Warnings, and Status Information

NonStop SQL/MP returns error and status information to a host-language program
after the execution of each embedded SQL statement or directive. NonStop SQL/MP
returns an SQL error or warning number to the SQLCODE variable and more extensive
information to these SQL data structures:

SQL communications area (SQLCA)––run-time information, including errors and



warnings, generated by the most recently run SQL statement
SQL statistics area (SQLSA)––statistics and performance information after the



execution of DML statements and some dynamic SQL statements
SQL descriptor area (SQLDA)––information about input parameters and output



variables in dynamic SQL statements

For more information about the SQLCA and SQLSA structures, see Section 9, Error

and Status Reporting. For information about the SQLDA structure, see Section 10,
Dynamic SQL Operations.

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

1-5

Introduction

Dynamic SQL

With static SQL statements, you code the actual SQL statement in the C source file.
However, with dynamic SQL, a C program can construct, compile, and run an SQL
statement at run time. You code a host variable as a placeholder for the dynamic SQL
statement, which is usually unknown or incomplete until run time.

A dynamic SQL statement requires some input, often from a user at a terminal, to
construct the final statement. The statement is constructed at run time from the user’s
input, compiled by the SQL compiler, and then run by an EXECUTE or EXECUTE
IMMEDIATE statement.

Example 1-2 shows a simple example of a dynamic INSERT statement (which is

similar to the static SQL INSERT statement in Example 1-1 on page 1-4). This program
example dynamically builds an INSERT statement that inserts information into the
PARTS table from information entered by a user.

Example 1-2. Dynamic SQL Statements in a C Program

Dynamic SQL

/* C source file */
...
char intext[201];
EXEC SQL BEGIN DECLARE SECTION;
char operation[201];
EXEC SQL END DECLARE SECTION;

void dynamic_insert_function(void)
{
...
/* User enters INSERT statement in the intext variable. */
strncpy (operation, intext, 201);
EXEC SQL EXECUTE IMMEDIATE :operation;
}

At run time, the program prompts a user for information to build the INSERT statement.
The user enters this information in the INTEXT variable:

INSERT INTO $vol5.sales.parts (partnum, price, partdesc)
VALUES (4120, 60000.00, "V8 DISK OPTION")

The program moves the statement to the host variable OPERATION. The program has
declared OPERATION as a host variable so that it is available to both SQL and C
statements. The program then uses the EXECUTE IMMEDIATE statement to compile
and run the INSERT statement in OPERATION. (This program could also have used
the PREPARE and EXECUTE statements to compile and run the statement.)

For more information, see Section 10, Dynamic SQL Operations

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

.

1-6

Introduction

SQL/MP Version Management

Each product version update (PVU) of NonStop SQL/MP has an associated version
number. The first two PVUs were version 1 (C10 and C20) and version 2 (C30).
Version 300 SQL/MP began using a three-digit version number to allow for software
product revisions (SPRs).

A new version number is always greater than the previous number, but the new
number might not follow a constant increment. For example, consecutive version
numbers after version 340 might be 345, 350, and 360.

In addition, SQL objects (tables, indexes, views, collations, and constraints), programs,
and catalogs have associated version numbers. This version number indicates the
SQL features used by the SQL object or program and the SQL/MP software with which
the SQL object or program is compatible. For example, a version 2 table might use the
date-time data types or allow null values in a column. A version 2 table is compatible
with version 2 and version 315 SQL/MP software, but it is not compatible with version 1
software.

SQL/MP Version Management

This manual includes this version information:

Using compatible versions of the C compiler, SQL compiler, and SQL executor to



compile and run a program
Using the data status language (DSL) statements: GET VERSION (for SQL



objects, catalogs, and SQL/MP software), GET VERSION OF PROGRAM, and
GET CATALOG OF SYSTEM

Generating different versions of the SQLSA and SQLDA structures



Using run-time SQLSA versioning, which allows a program to use an SQLSA



structure with the same version as the current SQL/MP software for the system
(available with version 340 or later SQL/MP software)

Converting a C program written for version 1 or version 2 SQL/MP software to use



version 300 (or later) SQL features and data structures

For additional information about version issues, see the SQL/MP Version Management
Guide.

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

1-7

Introduction

SQL/MP Version Management

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

1-8

2 Host Variables

A host variable is a data item you can use in both C statements and NonStop SQL/MP
statements to allow communication between the two types of statements. A host
variable appears as a C identifier and can be any C data item declared in a Declare
Section that has a corresponding SQL/MP data type as shown in Table 2-1
page 2-3 and Table 2-2
identifier (the left part) of a #define directive.

For static SQL operations, a host variable can be an input or an output variable (or
both in some cases) in an SQL statement. An input host variable transfers data from
the program to the database, whereas an output host variable transfers data from the
database to the program.

(For dynamic SQL operations, input parameters and output variables fulfill the same
function as input and output host variables in static SQL statements.)

An indicator variable is a two-byte integer variable, also declared in a Declare Section,
that is associated with a host variable. An indicator variable indicates whether a
column contains, or can contain, a null value. A null value means that a value is either
unknown for the row or does not apply to the row. A program uses an indicator variable
to insert null values into a database or to test a column value for a null value after
retrieving the value from a database.

on page 2-4. However, a host variable cannot be the name or

on

Topics include:

Specifying a Declare Section



Coding Host Variable Names on page 2-2



Using Corresponding SQL and C Data Types on page 2-3



Specifying Host Variables in SQL Statements on page 2-6



Declaring and Using Host Variables on page 2-7



Using Indicator Variables for Null Values on page 2-17



Creating Host Variables Using the INVOKE Directive on page 2-18



Associating a Character Set With a Host Variable on page 2-24



Specifying a Declare Section

You declare all host variables in a Declare Section. The BEGIN DECLARE SECTION
and END DECLARE SECTION directives designate a Declare Section. Follow these
guidelines when you specify a Declare Section:

Use the BEGIN DECLARE SECTION and END DECLARE SECTION directives



only in pairs.

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

2-1

Host Variables

Place a Declare Section with the C variable declarations. You can specify more



than one Declare Section in a program, if necessary, but you cannot nest Declare
Sections.

Do not place a Declare Section within a C structure declaration.



Specify the C #include directive in a Declare Section to copy declarations from



another file. However, do not use the SQL SOURCE directive.
Use either C or SQL comment statements in a Declare Section.



Coding Host Variable Names

Use C naming conventions for your host variable and indicator variable names.
A name can contain from 1 to 31 alphanumeric characters, including the underscore
(_), and must begin with a letter or an underscore. To avoid conflicts with HP names,
do not begin your names with two underscores or end them with one underscore. This
example uses a Declare Section with host variable names:

Coding Host Variable Names

EXEC SQL BEGIN DECLARE SECTION;

short order_number; /* simple variables */
char host_var_for_sql_statement;

struct employee /* structure */
{
short empnum;
char first_name[16];
union {
char last_name[21];
char name_code_item[3];
} union_last_name;
short deptnum;
short jobcode;
} employee_info;
int *ptr_to_table; /* pointer */

#include copyfile /* copy file */

EXEC SQL END DECLARE SECTION;

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

2-2

Host Variables

Using Corresponding SQL and C Data Types

Using Corresponding SQL and C Data Types

Table 2-1 and Table 2-2 on page 2-4 list the corresponding SQL and C data types.

Table 2-1. Corresponding SQL and C Character Data Types

SQL/MP Data Type C Data Type
Fixed-Length Character Data Type **

hostvar CHAR(l)

char hostvar [l + 1]

hostvar PIC X(l)

Fixed-Length Character Data Type With CHARACTER SET Clause

*

hostvar CHARACTER (l)
CHARACTER SET charset

char CHARACTER SET charset hostvar [l

*

+ 1]

hostvar PIC X(l)
CHARACTER SET charset

Fixed-Length Character Data Type With NATIONAL CHARACTER Clause

hostvar NATIONAL CHARACTER
(l)

char CHARACTER SET
charset hostvar [l + 1]

*

Variable-Length Character Data Type **

hostvar VARCHAR(l) struct {

short len;
char val [l + 1];

*

} hostvar ;

Variable-Length Character Data Type With CHARACTER SET Clause

hostvar VARCHAR (l)
CHARACTER SET charset

struct {
short len;
char CHARACTER SET
charset val [l + 1];

*

} hostvar ;

Variable-Length Character Data Type With NATIONAL CHARACTER Clause

hostvar NATIONAL CHARACTER
VARYING(l)

hostvar A host variable name; hostvar must follow the naming conventions for a C identifier.
l A positive integer that represents the length in characters of the host variable.
len, val The length and value of the host variable.
charset One of these character-set keywords: KANJI, KSC5601, ISO8859n,

where n is 1 – 9, or UNKNOWN (a single-byte unknown character set).

defcharset The system default multibyte character set; defcharset is KANJI, unless it is otherwise set

or changed during system generation.

*
**

An extra byte is generated as a place holder for a null terminator.
If a character set is not specified, the character set is UNKNOWN.

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

struct {
short
ch

len;

ar CHARACTER SET
defcharset val [l + 1];
} hostvar ;

2-3

*

Host Variables

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

SQL/MP Data Type C Data Type
Numeric Data Types

NUMERIC (1 to 4,s) SIGNED short

NUMERIC (1 to 4,s) UNSIGNED unsigned short

NUMERIC (5 to 9,s) SIGNED long

NUMERIC (5 to 9,s) UNSIGNED unsigned long

NUMERIC (10 to 18,s) SIGNED long long

PIC 9(l-s)V9(s) COMP Same as NUMERIC

DECIMAL (l, s) decimal[l + 1]

PIC 9(l-s)V9(s) decimal[l + 1]

SMALLINT SIGNED short

SMALLINT UNSIGNED unsigned short

Using Corresponding SQL and C Data Types

**

**

INTEGER SIGNED long

INTEGER UNSIGNED unsigned long

LARGEINT SIGNED long long

FLOAT (1 to 22 bits) float

REAL float

FLOAT (23 to 54 bits) double

DOUBLE PRECISION double

Date-Time and INTERVAL Data Types

DATETIME, TIMESTAMP, DATE, TIME char[l + 1]

INTERVAL char[l + 1]

l A positive integer that represents the length. For DECIMAL, l must range from 1 – 18.
s A positive integer that represents the scale of the number.

*
**

***

An extra byte is generated as a place holder for a null terminator.

The decimal data type is normally used to declare an array that can hold all the digit characters, the
sign, and, optionally, a null terminator. The size of the array should be no more than 20 (19 plus an
extra byte for the null terminator), or 21 (20 plus an extra byte for the null terminator) if a separate
sign is used

An INTERVAL data type has an extra byte for a sign.

.

*

***

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

2-4

Host Variables

Note. C programs that contain an embedded SQL/MP code do not support the use of
unsigned long long C variables even if that data type is not used for the SQL query.

C programs containing unsigned long long C variables outside the EXEC SQL statements
cannot be compiled in the Guardian and OSS environments. A workaround is to use the PC
cross compiler. C programs with unsigned long long variables within the EXEC SQL
statements cannot be compiled because NonStop SQL/MP does not support the unsigned long
long data type.

Data Conversion

NonStop SQL/MP performs the conversion between SQL and C data types:

When a host variable serves as an input variable (supplies a value to the



database), NonStop SQL/MP first converts the value that the variable contains to a
compatible SQL data type and then uses the value in the SQL operation.

When a host variable serves as an output variable (receives a value from a



database), NonStop SQL/MP converts the value to the data type of the host
variable.

Using Corresponding SQL and C Data Types

NonStop SQL/MP supports conversion within character types and numeric types, but
not between character and numeric types.

For conversion between character strings of different lengths, NonStop SQL/MP pads
the receiving string on the right with blanks as necessary. If the receiving string is too
short, NonStop SQL/MP truncates the right part of the longer string and returns a
warning code in the SQLCODE variable.

If an input value is too large for an SQL column, NonStop SQL/MP returns error 8300
(file system error encountered). If you are using the SQLCADISPLAY procedure to
obtain an error message, SQLCADISPLAY also returns file-system error number 1031.

For numeric types, NonStop SQL/MP converts data between signed and unsigned
types and between types with different precisions. Use the SETSCALE function to
communicate a number’s scale to and from a database.

Note. For optimal performance, declare host variables with corresponding data types and the
same lengths as their respective columns in SQL statements (with consideration for the extra
byte required for the null terminator). This programming practice minimizes the dat a conversion
performed by NonStop SQL/MP and therefore can improve the performance of your program.

CAST Function

The CAST function allows you to convert a parameter from one data type to another
data type (character and numeric data types only) in dynamic SQL statements. For
information about the CAST function, see the SQL/MP Reference Manual.

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

2-5

Host Variables

Specifying Host Variables in SQL Statements

Specifying Host Variables in SQL Statements

Use this syntax to specify a host variable in an SQL statement. You must precede the
host variable name with a colon (:). The colon causes the C compiler to handle the
name as a host variable. To use a pointer as a host variable in SQL statements, place
the colon before the asterisk.

:hostvar [[ INDICATOR ]:indicator_hostvar ]

[ TYPE AS { DATETIME [ start-date-time TO ] end-date-time }
]
[ { }
]
[ { DATE }
]
[ { }
]
[ { TIME }
]
[ { }
]
[ { TIMESTAMP }
]
[ { }
]
[ { INTERVAL start-date-time }
]
[ { [ ( start-field-precision ) ] }
]
[ { [ TO end-date-time ] }
]

hostvar

is the host variable name; hostvar can be any valid C identifier with a C data
type that corresponds to an SQL data type, but it cannot be on the left-hand side of
a #define directive. Precede hostvar with a colon (:) in an SQL statement.

INDICATOR

is a keyword that must precede indicator_hostvar.

indicator_hostvar

is an indicator variable of type short. Precede indicator_hostvar with a colon
(:) in an SQL statement.

For values returned to a host variable, indicator_hostvar is –1 if the value is
null or 0 if the value is not null. To insert null values into the database, set
indicator_hostvar to a value less than zero.

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

2-6

Host Variables

TYPE AS

specifies that the host variable will have the specified date-time (DATETIME,
DATE, TIME, or TIMESTAMP) or INTERVAL data type. If a host variable must
contain date-time or INTERVAL values, define it as a character data type. To cause
NonStop SQL/MP to handle the host variable as a scaled value, either use the
SETSCALE function or define the variable as C data type fixed.

Declaring and Using Host Variables

Declaring and Using Host Variables

You can declare and use these data types as host variables:

Fixed and variable length character data types (CHAR and VARCHAR)



Structures



Decimal data types



Fixed-point data types



Date-time and INTERVAL data types



Fixed-Length Character Data

The C language uses a character array plus a null terminator (\0) to store a string
literal. Most C string-handling routines (for example, strlen and printf) require the
null terminator. Follow these guidelines when you use character arrays as host
variables for string literals.

Declaring a Character Array

When you declare a character array as a host variable, the C compiler reserves the
last byte of the array as a place holder for a null terminator. Ther efore, declare a
character array one byte longer than the actual number of characters. (The INVOKE
directive automatically appends an extra byte to a character array, provided you do not
specify the CHAR_AS_ARRAY option in the SQL pragma.) This declaration is for an SQL
column up to 20 bytes long:

EXEC SQL BEGIN DECLARE SECTION;
char last_name[21]; /* 20-byte last name */
EXEC SQL END DECLARE SECTION;
...

Selecting Character Data

When selecting character data from a database to return to a host variable array,
NonStop SQL/MP does not append a null terminator to the data. Therefore, before
using the array in a C string-handling routine that requires a null terminator, you must
append a null terminator to the array. This example selects character data from the

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

2-7

Host Variables

SHIPMENTS table and appends a null terminator to the prod_desc array before
printing the data:

EXEC SQL BEGIN DECLARE SECTION;
short prod_num;
char prod_desc[11];
EXEC SQL END DECLARE SECTION;
...

EXEC SQL
SELECT prod_num, prod_desc INTO :prod_num, :prod_desc
FROM =shipments WHERE prod_num > min_num;
...
/* append null terminator before displaying string */
prod_desc[11] = "\0";
printf("%d %s\n", prod_num, prod_desc);

Inserting Character Data

In an SQL/MP database, fixed-length character columns are always padded with
blanks. Therefore, if the number of characters in an array is less than the size of the
character column, pad the array with blanks before inserting it into the database.
Otherwise, the INSERT statement stores the null terminator in the database, and
comparison operations fail. This example inserts data into the PRODUCTS table. The
prod_desc array is six bytes long (five byes for the column value and one byte for the
null terminator).

Fixed-Length Character Data

void function(void)
{
EXEC SQL BEGIN DECLARE SECTION;
char prod_desc[6]; /* Use for a 5-character column */
EXEC SQL END DECLARE SECTION;
memcpy(prod_desc, "abc ", 5); /* copy 5 characters */
/* (abc plus 2 blanks) */
...
EXEC SQL INSERT INTO =products VALUES (:prod_desc);
}

This example pads the prod_desc array with blanks before it inserts the array into the
database:

/* Routine to pad an array of characters */
/* with blanks on the right. */
void blank_pad(char *buf, size_t size)
{
size_t i;
i = strlen(buf);
if (i < size)
memset(&buf[i], ' ', size - i);
}

void function(void)
{
EXEC SQL BEGIN DECLARE SECTION;

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

2-8

Host Variables

char prod_desc[6]; /* use for 5-character column */
EXEC SQL END DECLARE SECTION;

strcpy(prod_desc, "abc"); /* Copy 3 characters and */
/* null terminator */
...
/* Do not include space for null byte in the size */
blank_pad(prod_desc, sizeof prod_desc - 1);
EXEC SQL INSERT INTO =products VALUES (:prod_desc);
}

Variable-Length Character Data

The VARCHAR data type represents one data element; however, the C compiler
converts the type to a structure with two data items. The C compiler derives the group
item name from the VARCHAR column name and the names of the subordinate data
items, where:

len is a numeric data item that represents the length.



Variable-Length Character Data

val is a fixed-length character data item for the string, plus an extra byte for the



null terminator, if the SQL pragma specifies the CHAR_AS_STRING option.

For example, if a column CUSTNAME is defined as VARCHAR(26), and the SQL
pragma specifies the CHAR_AS_STRING option, INVOKE generates this structure:

struct
{
short len;
char val[27];
} custname;

You can refer to the individual data items or the structure name as host variables.
If you explicitly declare a structure as a host variable for a VARCHAR column (rather

than using INVOKE), declare the length as a short data type (and not an int).

Structures

You can refer to a structure name as a host variable only if the structure corresponds
to a VARCHAR data type. For structures that do not correspond to a VARCHAR data
type, the fields within the structure are the host variables. However, when you refer to
an individual field name in the structure, you must include the structure name with the
field name. For example, the structure employee_info contains the empid and

empname fields:

EXEC SQL BEGIN DECLARE SECTION;
struct employee
{
long empid;
char empname[21];
} employee_info;
EXEC SQL END DECLARE SECTION;

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

2-9

Host Variables

To use a field as a host variable in an SQL statement, refer to the field by using the
structure name:

EXEC SQL SELECT empid, empname
INTO :employee_info.empid, :employee_info.empname
... ;

Structures

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

2-10

Host Variables

Decimal Data Types

Use the DECIMAL data type for ASCII numeric data. Because a decimal string is
actually a fixed-length character string that contains only ASCII digits, considerations
for fixed-length character strings also apply to decimal strings. Follow these guidelines
when you use character arrays as host variables for DECIMAL data:

Declare a decimal array one byte larger than the number of digits you expect to



store in the array.
Append a null terminator to an SQL/MP decimal string before you process it as a



C decimal string.
Right justify a C decimal string and pad the string on the left with ASCII zeros up to



the length of the corresponding SQL column before you insert the value into the
database.

HP C does not support direct manipulation of decimal strings. To perform C arithmetic
operations on SQL columns of DECIMAL data type, first convert the column to an
integral type using the dec_to_longlong routine.

Decimal Data Types

HP C also provides the longlong_to_dec routine to convert type long long to type
decimal. Although the longlong_to_dec routine supports a variety of formats for
signed decimal strings, NonStop SQL/MP supports only the embedded leading signed
format. Therefore, always specify the embedded leading signed format when you
intend to pass the converted decimal string to NonStop SQL/MP.

For more information about C routines, see the C/C++ Programmer’s Guide.

Fixed-Point Data Types

HP C does not have a data type that maps directly to a fixed-point number (that is, an
SQL numeric data type with scale). If you transfer fixed-point values to integral or
floating-point host variables, consider these guidelines:

When you transfer a fixed-point value to a host variable of floating-point data type,



NonStop SQL/MP converts the fixed-point value to a floating-point value and
generates a warning to indicate a loss of precision.

When you transfer a fixed-point value into an integer host variable, NonStop



SQL/MP stores the integral part of the value and generates a warning to indicate a
loss of data (the fractional part). To retain the fractional part, use the SETSCALE
function to scale the fixed-point value before transferring it to the host variable.

SETSCALE Function

The SETSCALE function directs NonStop SQL/MP to use a host variable in SQL
statements as if the host variable were declared with a specific scale. Use the
SETSCALE function for these operations:

To insert scaled values (for example, prices) into a database



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

2-11

Host Variables

To select database values into host variables



To refer to values stored in the database for comparisons



The SETSCALE function has this syntax:

SETSCALE (:host-variable
[ [ INDICATOR ] :indicator-variable ] , scale )

host-variable

is an integer host variable.

indicator-variable

is an indicator variable associated with the host variable.

scale

specifies the scale of host-variable. The values for scale depend on the size
of host-variable:

Fixed-Point Data Types

Size Values

2-byte integers 0 – 5 decimal digits
4-byte integers 0 – 10 decimal digits
8-byte integers 0 – 18 decimal digits

Follow these guidelines when you use the SETSCALE function:

If you are transferring a value from a host variable to a database using an INSERT



or UPDATE statement, you must assign a value to the host variable that allows for
the scale. For example, to insert a price of $123.45, assign 12345 to hostvar and
specify a scale of 2.

If you are retrieving a value from a database using a SELECT statement, NonStop



SQL/MP returns a value that allows for the scale in the host variable. For
example, if your program specifies a scale of 2 in the SELECT statement and

123.45 is stored in the database, SQL/MP returns 12345 to the host variable.
The scale is valid only for SQL statements. If you use the SETSCALE function in



SQL statements and the host variables in calculations using C statements, the
C statements must handle the scale.

To use SETSCALE in an expression, apply the SETSCALE function to each



operand individually rather than to the result of the expression. For example, this
expression adds two prices with a scale of 2 decimal places:

SETSCALE (:price1, 2) + SETSCALE (:price2, 2)

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

2-12

Host Variables

When you use the INVOKE directive for a column with a scaled data type, the



C compiler generates a comment that shows the scale of the column. For example,
for price with data type NUMERIC (8,2), INVOKE generates the following:

long price; /* scale is 2 */

These examples use the =parts DEFINE to represent the PARTS table. The first
example inserts a new row with the value 98.34 in the PARTS.PRICE column after
storing the value in the host variable host_var1. The value is multiplied by 100 for
storing as a whole number.

host_var1 = 9835;
EXEC SQL INSERT INTO =parts (price)
VALUES ( SETSCALE (:host_var1, 2) ) ;

The next example updates the PARTS.PRICE column for a disk controller to 158.34.
The value is multiplied by 100 and stored in the host variable host_var2.

host_var2 = 15834;
EXEC SQL UPDATE =parts
SET price = SETSCALE (:host_var2, 2)
WHERE parts.partdesc = "disk controller" ;

Date-Time and INTERVAL Data Types

The next example retrieves the value in the PARTS.PRICE column for a disk controller
and stores the value in the host variable host_var3. The value has a scale of 2.

EXEC SQL SELECT parts.price INTO SETSCALE ( :host_var3, 2 )
FROM =parts
WHERE parts.partdesc = "disk controller" ;

The next example retrieves the part description for the part with a price of 999.50. The
PARTS.PRICE value is stored in the host variable host_var4 and passed to NonStop
SQL/MP in the search condition. The retrieved value is stored in the host variable

host_varstore.

host_var4 = 99950;
EXEC SQL SELECT parts.partdesc INTO :host_varstore
FROM =parts
WHERE parts.price = SETSCALE (:host_var4,2);

Date-Time and INTERVAL Data Types

Table 2-3 describes the SQL/MP date-time and INTERVAL data types you can use for

host variables.

Table 2-3. Date-Time and INTERVAL Data Types (page 1 of 2)

Data Type Description

DATETIME Represents a date and time from year to microsecond (logical subsets,

such as MONTH TO DAY, are allowed)

DATE Represents a date and is a synonym for DATETIME YEAR TO DAY

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

2-13

Host Variables

Table 2-3. Date-Time and INTERVAL Data Types (page 2 of 2)

TIME Represents a time and is a synonym for DATETIME HOUR TO SECOND
TIMESTAMP Represents a date and time and is a synonym for DATETIME YEAR TO

INTERVAL Represents a duration of time as a year-month or day-time interval

To communicate date-time or INTERVAL values between C and SQL statements,
declare a character array as a host variable and then use the TYPE AS clause to
cause NonStop SQL/MP to interpret the value as a date-time or INTERVAL value. For
the syntax of the TYPE AS clause, see Specifying Host Variables in SQL Statements
on page 2-6.

You can insert or retrieve date-time values in any of three formats, independently of the
SQL column definition. For example, you can specify formats such as 08/15/1996,
1996-08-15, or 15.08.1996. You control the display format by inserting the value in the
format you want and retrieving the value using the DATEFORMAT function. You must
declare the host variable size to be consistent with the format you plan to use.

Date-Time and INTERVAL Data Types

FRACTION(6)

This example inserts date-time values into the BILLINGS table:

EXEC SQL BEGIN DECLARE SECTION;
struct billing_rec
{
char custnum[4];
char start_date[11];
char billing_date[11];
char time_before_pmt[5];
};
struct billing_rec billings = { ' ',' ',' ',' ' };
...
EXEC SQL END DECLARE SECTION;
...
strcpy(billings.billing_date, "1996-08-20");
strcpy(billings.time_before_pmt, " 90");
...
EXEC SQL
INSERT INTO billings VALUES
("923", DATE "1985-10-15",
:billing_date TYPE AS DATE,
:time_before_pmt TYPE AS INTERVAL DAY);
...

When you invoke a column with a date-time (DATETIME, DATE, TIME, or
TIMESTAMP) or INTERVAL data type, the data is represented as a character field. The
size of the field is determined by the range of the date-time or INTERVAL column. You
control the display format by inserting the value in the format you want and retrieving
the value using the DATEFORMAT function. If you use INVOKE to generate host
variables from an SQL table definition, you can specify the DATEFORMAT clause to
determine the size.

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

2-14

Host Variables

012

1988122

2





Year

Separator

Month

Separator

Day

Null

013

+3607Sign

Year

Separator

Month

Null

INTERVAL values are represented as character strings with a separator between the
values of the fields (year-month or day-time). An extra byte is generated at the
beginning of the INTERVAL string for a sign. The default representations for DATE and
INTERVAL values are shown in these examples.

DATE Representation

The column definition and representation in the table for December 22, 1988 is:

birth_date DATE

If the DATEFORMAT clause on the INVOKE directive specifies DEFAULT, a column
with the range of fields YEAR TO DAY is represented as an 11-character string
(10 characters plus a byte for a null character). The C compiler creates this structure:

Date-Time and INTERVAL Data Types

struct employee_rec {
char name[18];
char birth_date[11];
};

INTERVAL Representation

The column definition and representation in the table for 36 years, 7 months is:

AGE INTERVAL YEAR(2) TO MONTH

The C compiler creates this structure:

struct employee_rec {
char name[21];
char age[7];
};

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

2-15

Host Variables

Example—Creating DATETIME and INTERVAL Data Types

Example 2-1. Creating Valid DATETIME and INTERVAL Data Types

#include <stdio.h>
#include <string.h>
#include <sql.h>

#define STMT_LEN 256

EXEC SQL BEGIN DECLARE SECTION;
short sqlcode;
char hv_projdesc[30];
char hv_start_date[11];
char in_start_date[11];
char curspec[STMT_LEN];
EXEC SQL END DECLARE SECTION;

int main()
{
int len;
strcpy(curspec,
"SELECT projdesc, CAST(start_date AS CHAR(10)) FROM test1 "
"WHERE start_date <= CAST(CAST( ? AS CHAR(10)) "
"AS DATE) BROWSE ACCESS");
len = strlen(curspec);
memset(&curspec[len], ' ', STMT_LEN - len);

Date-Time and INTERVAL Data Types

EXEC SQL PREPARE cursor_spec from :curspec;

/* Declare the dynamic cursor from the prepared statement. */
EXEC SQL DECLARE get_proj CURSOR FOR cursor_spec;

/* Initialize the parameter in the WHERE clause. */
printf("Enter the most recent start date in the form yyyy-mm-dd: ");
scanf("%s", in_start_date);

/* Open the cursor using the value of the dynamic parameter. */
EXEC SQL OPEN get_proj USING :in_start_date;

/* Fetch the first row of the result table. */
EXEC SQL FETCH get_proj INTO :hv_projdesc,:hv_start_date;

while (sqlcode == 0)
{
hv_start_date[10]='\0';
printf("\n Start Date: %s", hv_start_date);

/* Fetch the next row of the result table. */
EXEC SQL FETCH get_proj INTO :hv_projdesc,:hv_start_date;
}
/* Close the cursor. */
EXEC SQL CLOSE get_proj;

return 0;
}

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

2-16

Host Variables

Using Indicator Variables for Null Values

Using Indicator Variables for Null Values

A null value in an SQL column indicates that the value is either unknown for the row or
is not applicable to the row. A program inserts a null value or tests for a null value
using an indicator variable. An indicator variable is a 2-byte integer variable associated
with the host variable that sets or receives the actual column value.

The INVOKE directive automatically declares indicator variables for columns defined to
allow null values. For information, see Using Indicator Variables With the INVOKE

Directive on page 2-22.

A program can use an indicator variable associated with a host variable:

To insert values into a database with an INSERT or UPDATE statement



To test for a null value after retrieving a value from a database with a



SELECTstatement

Inserting a Null Value

To insert values into a database with an INSERT or UPDATE statement, a program
sets the indicator variable to less than zero (0) for a null value or zero (0) for a nonnull
value before executing the statement. This statement inserts values into the ODETAIL
table. The columns UNIT_PRICE and QTY_ORDERED allow null values.

EXEC SQL INSERT INTO =odetail
(ordernum, partnum, unit_price, qty_ordered)
VALUES ( :odetail.ordernum,
:odetail.partnum,
:odetail.unit_price :odetail.unit_price_i,
:odetail.qty_ordered :odetail.qty_ordered_i );

Testing For a Null Value

To test for a null value, a program tests the indicator variable associated with a host
variable. This example selects values from the ODETAIL table and returns the values
to host variables. After the SELECT statement runs, the example tests the indicator
variable for a null value. If the value of the indicator variable is less than 0, the
associated column contains a null value.

EXEC SQL SELECT ordernum, partnum, unit_price, qty_ordered
INTO :odetail.ordernum,
:odetail.partnum,
:odetail.unit_price INDICATOR :odetail.unit_price_i,
:odetail.qty_ordered INDICATOR
:odetail.qty_ordered_i,
FROM sales.odetail
WHERE ordernum = 300380 AND partnum = 2402 ;
...
if ((odetail.unit_price_i < 0) ||
(odetail.qty_ordered_i < 0))
handle_null_value();

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

2-17

Host Variables

else display_result();
...

Retrieving Rows With Null Values

You can use an indicator variable to insert null values into a database or to test for a
null value after you retrieve a row. However , you cannot use an indicator variable set to
–1 in a WHERE clause to retrieve a row that contains a null value. In this case,
NonStop SQL/MP does not find the row and returns an sqlcode of 100, even if a
column actually contains a null value.

To retrieve a row that contains a null value, use the NULL predicate in the WHERE
clause. For example, to retrieve rows that have null values from the EMPLOYEE table
using a cursor, specify the NULL predicate in the WHERE clause in the associated
SELECT statement when you declare the cursor:

/* Declare a cursor to find rows with null salaries. */
EXEC SQL DECLARE get_null_salary CURSOR FOR
SELECT empnum, first_name, last_name,
deptnum, jobcode, salary
FROM =employee
WHERE salary IS NULL;
...
EXEC SQL OPEN get_null_salary ;
...

Retrieving Rows With Null Values

EXEC SQL FETCH get_null_salary INTO
:employee_record.empnum,
:employee_record.first_name,
:employee_record.last_name,
:employee_record.deptnum,
:employee_record.jobcode,
:employee_record.salary ;

/* Test SQLCODE. */
/* Process the row that contains the null salary. */
/* Branch back to FETCH the next row. */
...
EXEC SQL CLOSE get_null_salary ;

Creating Host Variables Using the INVOKE
Directive

The INVOKE directive creates host variables that correspond to columns in an SQL
table or view. INVOKE converts the column names to C identifiers and generates a
C declaration for each column. When a column allows null values, INVOKE also
creates an indicator variable for the column. For views only, INVOKE includes the
system-defined primary keys in the definition. You can use a class MAP DEFINE name
for a table or view name in an INVOKE directive, but not for a record name.

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

2-18

Host Variables

To run an INVOKE directive, a process started by the program must have read access
to the invoked tables or views during C compilation. For details, see Required

Access Authority on page 7-1.

The CHAR_AS_STRING and CHAR_AS_ARRAY options of the SQL pragma affect the
INVOKE directive as follows:

The CHAR_AS_STRING option (the default) causes INVOKE to generate character



data types with an extra byte for a null terminator.
The CHAR_AS_ARRAY option causes INVOKE to generate character data types



without the extra byte for a null terminator.

Advantages of Using an INVOKE Directive

Advantages of Using an INVOKE Directive

You can declare a host variable as a C structure corresponding to an SQL table or
view without using an INVOKE directive. However, using an INVOKE directive to
generate host variables has these advantages:

Program independence––If you modify a table or view, the INVOKE directive



re-creates the host variables to correspond to the new table or view when you
recompile the program. (You must, however, modify a program that refers to a
deleted column or must access a new column.)

TACL DEFINEs––The INVOKE directive accepts a class MAP DEFINE name for a



table or view name (but not for a structure tag).
Program performance––The INVOKE directive maps SQL data types to the



corresponding C data types. No data conversion is required at run time.
Program readability and maintenance––The INVOKE directive creates host



variables using the same names as column names in the table or view and
generates comments that show the table or view name and the time and date of
the definition.

C Structures Generated by the INVOKE Directive

These examples show the correspondence between tables TYPESC1 and TYPESC2
that contain columns of various SQL data types and the C structures generated by the
INVOKE directive. Example 2-2
that generate the tables, and Example 2-3
generated by the INVOKE directives.

on page 2-20 shows the CREATE TABLE statements

on page 2-21 shows the structures

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

2-19

Host Variables

Example 2-2. CREATE TABLE Statements

CREATE TABLE \NEWYORK.$DISK1.SQL.TYPESC1 (
type_char CHAR (10) NOT NULL,
type_char_null CHAR (10)
,
type_varchar VARCHAR (10) NOT NULL,
type_varchar_null VARCHAR (10)
,
type_num4_s NUMERIC (4) SIGNED NOT NULL,
type_num4_u NUMERIC (4) UNSIGNED NOT NULL,
type_num9_s NUMERIC (9,2) SIGNED NOT NULL,
type_num9_u NUMERIC (9,2) UNSIGNED NOT NULL,
type_num18_s NUMERIC (18,2) SIGNED NOT NULL,
type_small_s SMALLINT SIGNED NOT NULL,
type_small_u SMALLINT UNSIGNED NOT NULL,
type_int_s INTEGER SIGNED NOT NULL,
type_int_u INTEGER UNSIGNED NOT NULL,
type_large_s LARGEINT SIGNED NOT NULL,
type_decs DECIMAL (18,2) SIGNED NOT NULL,
type_dec_u DECIMAL (9,2) UNSIGNED NOT NULL,
type_pic_s PIC 9(9) COMP NOT NULL,
type_picx PIC X(10) NOT NULL,
type_picx_long PIC XXXXXXXXXXXXXXXXXXXX NOT NULL,
type_float_15 FLOAT (15) NOT NULL,
type_float_30 FLOAT (30) NOT NULL,
type_real REAL NOT NULL,
type_dbl_prec DOUBLE PRECISION NOT NULL,
type_datetime DATETIME YEAR TO DAY NOT NULL,
type_date DATE NOT NULL,
type_time TIME NOT NULL,
type_timestamp TIMESTAMP NOT NULL,
type_interval INTERVAL YEAR TO MONTH NOT NULL,
type_char_null_ok CHAR(10) DEFAULT NULL,
type_num_null_ok SMALLINT DEFAULT NULL
) CATALOG $SQL.SQLCAT ;
CREATE TABLE \NEWYORK.$DISK1.SQL.TYPESC2 (
type_char1 CHARACTER (10) CHARACTER SET ISO88591 NOT
NULL,
type_char1_null CHARACTER (10) CHARACTER SET ISO88591
,
type_char2 CHARACTER (10) CHARACTER SET KANJI NOT NULL,
type_char2_null CHARACTER (10) CHARACTER SET KANJI
,
type_nchar NCHAR (10) NOT NULL,
type_nchar_v NCHAR VARYING (10) NOT NULL
type_varchar1 VARCHAR (10) CHARACTER SET ISO88591 NOT
NULL,
type_varchar2 VARCHAR (10) CHARACTER SET KANJI NOT NULL,
type_picx1 PIC X(10) CHARACTER SET ISO88591 NOT NULL,
type_picx2 PIC X(10) CHARACTER SET KANJI NOT NULL
) CATALOG $SQL.SQLCAT ;

C Structures Generated by the INVOKE Directive

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

2-20

Host Variables

These INVOKE directives are coded in a C source file:

EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL INVOKE \newyork.$disk1.sql.typesc1 AS typesc1_struc;
EXEC SQL INVOKE \newyork.$disk2.sql.typesc1 AS typesc2_struc;
EXEC SQL END DECLARE SECTION;

Example 2-3. Structures Generated by the INVOKE Directive (page 1 of 2)

/* Record Definition for table \NEWYORK.$DISK1.SQL.TYPESC1
*/
/* Definition current at 13:52:15 - 8/27/96 */
struct typesc1_type {
char type_char[11];
short type_char_null_i;
char type_char_null[11];
struct {
short len;
char val[11];
} type_varchar;
short type_varchar_null_i;
struct {
short len
char val[11];
} type_varchar_null;
short type_num4_s;
unsigned short type_num4_u;
long type_num9_s; /* scale is 2 */
unsigned long type_num9_u; /* scale is 2 */
long long type_num18_s; /* scale is 2 */
short type_small_s;
unsigned short type_small_u;
long type_int_s;
unsigned long type_int_u;
long long type_large_s;
decimal type_decs[19]; /* scale is 2 */
decimal type_dec_u[10]; /* scale is 2 */
unsigned long type_pic_s;
char type_picx[11];
char type_picx_long[21];
float type_float_15;
double type_float_30;
float type_real;
double type_dbl_prec;
char type_datetime[11];
char type_date[11];

C Structures Generated by the INVOKE Directive

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

2-21

Host Variables

Example 2-3. Structures Generated by the INVOKE Directive (page 2 of 2)

char type_time[9];
char type_timestamp[27];
char type_interval[7];
short type_char_null_ok_i;
char type_char_null_ok[11];
short type_num_null_ok_i;
short type_num_null_ok; };
/* Record Definition for table \NEWYORK.$DISK1.SQL.TYPESC2
*/
/* Definition current at 13:52:19 - 8/27/96 */
struct typesc2_type {
char CHARACTER SET ISO88591 type_char1[11];
short type_char1_null_i;
char CHARACTER SET ISO88591
type_char1_null[11];
char CHARACTER SET KANJI type_char2[11];
short type_char2_null_i;
char CHARACTER SET KANJI type_char2_null[11];
char CHARACTER SET KANJI type_nchar[11];
struct {
short len;
char CHARACTER SET KANJI val[11];
} type_nchar_v;
struct {
short len;
char CHARACTER SET ISO88591 val[11];
} type_varchar1;
struct {
short len;
char CHARACTER SET KANJI val[11];
} type_varchar2;
char CHARACTER SET ISO88591 type_picx1[11];
char CHARACTER SET KANJI type_picx2[11];
};

Using Indicator Variables With the INVOKE Directive

Using Indicator Variables With the INVOKE Directive

The INVOKE directive automatically generates a two-byte indicator variable with data
type short for each host variable corresponding to a column that allows a null value.
The name of the indicator variable is the same as the name of the corresponding
column plus a prefix, if you specify one, and a suffix. When you do not specify a prefix
or suffix, INVOKE appends the default suffix _I to the indicator variable name.

If a column name is 30 or 31 characters and the default indicator suffix _I is used, the
_I is truncated, and the indicator variable name is then identical to the corresponding
host variable name. To prevent this problem, use the PREFIX or NULL STRUCTURE
clause for column names that are 30 or 31 characters.

The format of the indicator variable name depends on the PREFIX, SUFFIX, and NULL
STRUCTURE clauses.

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

2-22

Host Variables

PREFIX and SUFFIX Clauses

The PREFIX and SUFFIX clauses cause INVOKE to generate an indicator variable
name derived from the column name and the prefix or suffix. This example shows an
INVOKE directive with the PREFIX and SUFFIX clauses as it appears in a C source
program:

EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL INVOKE ctable PREFIX beg_ SUFFIX _end;
EXEC SQL END DECLARE SECTION;

The C compiler generates this structure:

/* Record Definition for table \SYS.$DSK.PERSNL.CTABLE */
/* Definition current at 15:32:39 - 09/22/95 */
struct ctable_type {
short beg_znum_end;
long znum;
short beg_zchar_end;
char zchar[16];
};

Using Indicator Variables With the INVOKE Directive

NULL STRUCTURE Clause

The NULL STRUCTURE clause causes INVOKE to generate a structure for a column
that contains an indicator variable. The NULL STRUCTURE clause assigns the name
indicator to all indicator variables in the structure.

This example shows an INVOKE directive with the NULL STRUCTURE clause as it
appears in a C source program:

EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL INVOKE emptbl AS emptbl_rec NULL STRUCTURE;
...
EXEC SQL END DECLARE SECTION;
...

The C compiler generates this structure:

/* Record Definition for table \SYS.$VOL.SUBVOL.EMPTBL */
/* Definition current at 16:07:00 - 05/17/94 */

struct emptbl_rec {
unsigned short empnum;
struct {
short indicator;
char valu[16];
} first_name;
char last_name[21];
struct {
short indicator;
char valu[11];
} retire_date;
};

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

2-23

Host Variables

Using INVOKE With SQLCI

Using INVOKE With SQLCI

You can also run the INVOKE directive interactively through SQLCI to create host
variable declarations in a copy file. For example, this INVOKE directive generates a C
copy file from the DEPT table:

>> INVOKE =dept FORMAT C TO copylib (deptrec);
...

Using INVOKE with SQLCI provides less program independence than embedding
INVOKE in your program, because you must re-create the host variable declarations if
the referenced table changes. However, when necessary, you can edit the host
variables before copying them into your program’s compilation unit.

Use the #include directive, not the SQL SOURCE directive to copy the host variable
declarations in your program’s compilation unit.

Associating a Character Set With a Host
Variable

By default, NonStop SQL/MP associates a single-byte character set with a host
variable. To associate a specific character set such as Kanji or KSC5601 with a host
variable, include the CHARACTER SET clause in the host variable declaration using
this syntax:

char [ CHARACTER SET [ IS ] charset ] hostvar [ length ]

CHARACTER SET [ IS ]

are keywords that must precede the character set name. You must specify the
CHARACTER SET clause in uppercase letters. If you omit the clause, the
character set defaults to UNKNOWN.

charset

is the character set name, which must be one of these keywords (in uppercase
letters): ISO8859n (n ranges from 1 through 9), KANJI, KSC5601, or UNKNOWN.
The UNKNOWN keyword indicates an unknown single-byte character set and is
equivalent to omitting the CHARACTER SET clause.

hostvar

is the name of the host variable, which must follow the naming conventions for a
C identifier.

length

is the length in characters (not bytes) of the host variable. length must also
include an extra byte for the null terminator, if the SQL pragma specifies the
CHAR_AS_STRING option (the default).

Note. NonStop SQL/MP does not support the C wchar_t data type.

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

2-24

Host Variables

Treatment in C Statements

A C statement treats a host variable declared with the CHARACTER SET clause as if
the host variable had been declared without the clause. A C statement also treats the
host variable length as the specified length multiplied by the number of bytes per
character plus the null terminator if the SQL pragma specifies the CHAR_AS_STRING
option (the default). These examples show this treatment for single-byte and double­byte character set declarations:

Host Variable Declaration Treatment in C Statements

char CHARACTER SET ISO88591 hostv1[5] char hostv1[5]

char CHARACTER SET KANJI hostv2[10] char hostv2[20]

VARCHAR Data Type

If you specify the CHARACTER SET clause with a host variable declared as a
VARCHAR data type, you must set the length data item (len in the next example) of
the VARCHAR group item to the host variable length in bytes and not characters.

Treatment in C Statements

For example, this host variable declaration specifies the double-byte KANJI character
set for emp_name. The C assignment statement sets the length (emp_name.len) of
the host variable name to 16 characters because the name (emp_name.val) contains
8 double-byte characters (which are represented as “c1c2c3c4c5c6c7c8”).

EXEC SQL BEGIN DECLARE SECTION;
struct {
short len;
char CHARACTER SET KANJI val[10];
} emp_name;
EXEC SQL END DECLARE SECTION;
...
/* Insert data into the data base. */

strcpy (emp_name.val, "c1c2c3c4c5c6c7c8");
emp_name.len = strlen(emp_name.val);
EXEC SQL
INSERT INTO =employee VALUES (:emp_name);
...

/* Select data from the data base. */

EXEC SQL
SELECT employee_name INTO :emp_name FROM =employee;
emp_name.val[emp_name.len] = '\0';
...

The last C assignment statement sets the null terminator after the SELECT statement
returns the employee name from the EMPLOYEE table.

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

2-25

Host Variables

VARCHAR Data Type

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

2-26

3

SQL/MP Statements and Directives

For a detailed description, including the syntax, of all SQL/MP statements and
directives, see the SQL/MP Reference Manual.

This section includes:

Embedding SQL Statements



Finding Information on page 3-3



Embedding SQL Statements

Use this syntax to embed a NonStop SQL/MP statement or directive in a C source file.

EXEC SQL sql-statement-or-directive ;

sql-statement-or-directive

is any SQL statement or directive shown in Table 3-1 on page 3-3. The statement
or directive must begin with the keywords EXEC SQL and end with a semicolon (;).

Coding Statements and Directives

In general, handle embedded SQL statements and directives as if they were
C statements. Follow the same formatting and line continuation conventions that you
use for C statements. Here are a few specific guidelines to follow when you code
embedded SQL statements and directives in a C program:

Do not nest SQL statements or directives.



Use only SQL comments in SQL statements and directives. SQL comments begin



with a double hyphen (--) and end with the end of the line. You cannot use
C comments in SQL statements or directives.

Use only the C string delimiter, a double quote ("), for quoted strings.



Code an SQL statement or directive on a single source code line or over several



lines:

EXEC SQL WHENEVER SQLERROR :handle_error;

EXEC SQL DROP TABLE \ny.$disk1.invent.supplier;

EXEC SQL
SELECT customer.custname
INTO :customer.custname

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

3-1

SQL/MP Statements and Directives

FROM =customer
WHERE custnum = :find_this_customer

;

Placing Statements and Directives

Place SQL statements and directives and C compiler pragmas in a C source file.

SQL Pragma

To use embedded SQL statements and directives in a C program, you must specify the
SQL pragma before any SQL or C statements (except comment statements). You can
specify the SQL pragma either in your source file or as a compiler option in the implicit
TACL RUN command that starts the C compiler. This example uses the SQL pragma in
a source code file:

#pragma SQL

This example uses the SQL pragma as a compiler option:

Placing Statements and Directives

C / IN csrc, OUT $s.#clst, NOWAIT / cobj; SQL

After the SQL pragma, place other SQL statements and directives in a C source file as
described in these paragraphs.

C Variable Declarations

You can use these statements and directives with C variable declarations:

BEGIN DECLARE SECTION and END DECLARE SECTION directives



DECLARE CURSOR statements for static SQL operations



INVOKE directive



INCLUDE STRUCTURES directive



INCLUDE SQLCA, INCLUDE SQLSA, and INCLUDE SQLDA directives



C Executable Statements

You can use these statements with C executable statements:

Data manipulation language (DML) statements



Data control language (DCL) statements



Data definition language (DDL) statements



Data status language (DSL) statements



Transaction control statements



Dynamic SQL statements (including DECLARE CURSOR)



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

3-2

SQL/MP Statements and Directives

Anywhere in the Program

You can use these directives anywhere in a C program:

WHENEVER directives



SQL SOURCE directive



CONTROL directives



Finding Information

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

indicates where each statement or directive is documented.

Table 3-1. Summary of SQL/MP Statements and Directives (page 1 of 4)

Statement or Directive Manual * Description
Data Declaration Directives

Finding Information

BEGIN DECLARE SECTION SQLRM

SQLPM/C

END DECLARE SECTION SQLRM

SQLPM/C

INCLUDE STRUCTURES SQLRM

SQLPM/C

INCLUDE SQLCA SQLRM

SQLPM/C

INCLUDE SQLDA SQLRM

SQLPM/C

INCLUDE SQLSA SQLRM

SQLPM/C

INVOKE SQLRM

SQLPM/C

*This statement is documented in one or more of these manuals:

SQLRM SQL/MP Reference Manual
SQLPM/C SQL/MP Programming Manual for C

Designates the beginning of host variable
declarations.

Designates the end of host variable
declarations.

Specifies the version of SQL structures
generated.

Generates the SQLCA structure for run­time status and error information.

Generates the SQLDA structure to receive
information about input and output
variables for dynamic SQL statements.

Generates the SQLSA structure to receive
execution statistics about DML or
PREPARE statements.

Generates a structure description of a table
or view.

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

3-3

SQL/MP Statements and Directives

Table 3-1. Summary of SQL/MP Statements and Directives (page 2 of 4)

Statement or Directive Manual * Description
Data Definition Language (DDL) Statements

ALTER CATALOG SQLRM Alters the security attributes of a catalog.
ALTER COLLATION SQLRM Alters the security attributes of a collation;

ALTER INDEX SQLRM Alters security attributes of indexes; alters

ALTER PROGRAM SQLRM Alters security attributes for a program;

ALTER TABLE SQLRM Alters security attributes of tables; alters

Finding Information

renames a collation.

physical file attributes of indexes and
partitions of indexes; adds and drops
partitions; renames indexes and partitions.

renames a program.

physical file attributes of tables and
partitions of tables; alters the HEADING
attribute for columns of tables and views;
adds and drops table partitions; renames
tables and partitions of tables; adds new
columns to tables.

ALTER VIEW SQLRM Alters security attributes for a view or

renames a view.
COMMENT SQLRM Adds a comment to an object definition.
CREATE SQLRM Creates a collation, constraint, catalog,

index, table, or view.
DROP SQLRM Drops a collation, constraint, catalog, index,

program, table, or view.
HELP TEXT SQLRM Specifies help text for a column of a table

or view.
UPDA TE ST ATISTICS SQLRM Updates information about the contents of a

table and its indexes.

Error Checking Directives

WHENEVER SQLRM

SQLPM/C

Generates code that checks SQL statement

execution for errors, warnings, and the not

found condition for rows.

*This statement is documented in one or more of these manuals:

SQLRM SQL/MP Reference Manual
SQLPM/C SQL/MP Programming Manual for C

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

3-4

SQL/MP Statements and Directives

Table 3-1. Summary of SQL/MP Statements and Directives (page 3 of 4)

Statement or Directive Manual * Description
Data Manipulation Language (DML) Statements

Finding Information

CLOSE SQLRM

SQLPM/C

DECLARE CURSOR SQLRM

SQLPM/C

DELETE SQLRM

SQLPM/C

FETCH SQLRM

SQLPM/C

INSERT SQLRM

SQLPM/C

OPEN SQLRM

SQLPM/C

SELECT SQLRM

SQLPM/C

UPDATE SQLRM

SQLPM/C

Data Control Language (DCL) Statements

CONTROL EXECUTOR SQLRM

SQLPM/C

Terminates a cursor.

Defines a cursor.

Deletes rows from a table or view.

Retrieves a row from a cursor.

Inserts rows into a table or view.

Opens a cursor.

Retrieves data from tables and views.

Updates values in columns of a table or

view.

Specifies whether to process data using a

single executor or multiple executors

working in parallel.
CONTROL QUERY SQLRM

SQLPM/C

Specifies whether to optimize q uery time for

the first few rows or for all rows, whether to

consider a hash join algorithm for executing

queries, or whether to use execution-time

name resolution.
CONTROL TABLE SQLRM

SQLPM/C

Specifies parameters that control locks,

opens, buffers, access paths, join methods,

and join sequences on tables and views.
FREE RESOURCES SQLRM Closes cursors and releases locks held by

the program.
LOCK TABLE SQLRM Locks a table or underlying tables of a view

and associated indexes.
UNLOCK TABLE SQLRM Releases locks held on nonaudited tables

and views.

*This statement is documented in one or more of these manuals:

SQLRM SQL/MP Reference Manual
SQLPM/C SQL/MP Programming Manual for C

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

3-5

SQL/MP Statements and Directives

Table 3-1. Summary of SQL/MP Statements and Directives (page 4 of 4)

Statement or Directive Manual * Description
Data Status Language (DSL) Statements

GET CATALOG OF SYSTEM SQLRM Returns the name of a local or remote

Finding Information

system catalog.
GET VERSION SQLRM

SQLPM/C

Returns the version of a catalog, collation,

index, table, or view; also returns the

version of the SQL/MP system software.
GET VERSION OF

PROGRAM

SQLRM
SQLPM/C

Returns the program catalog version

(PCV), program format version (PFV), or

host object SQL version (HOSV) of an SQL

program file.

Transaction Control Statements

BEGIN WORK SQLRM Starts a TMF transaction.
COMMIT WORK SQLRM Commits all database changes made

during the current TMF transaction and

frees resources.
ROLLBACK WORK SQLRM Backs out the current TMF transaction and

frees resources.

Dynamic SQL Statements

DESCRIBE SQLRM

SQLPM/C

DESCRIBE INPUT SQLRM

SQLPM/C

Returns information about output variables

for a prepared statement.

Returns information about input variables

for a prepared statement.
EXECUTE SQLRM

Runs a prepared SQL statement.

SQLPM/C

EXECUTE IMMEDIATE SQLRM

SQLPM/C

PREPARE SQLRM

SQLPM/C

Runs an SQL statement contained in a host

variable.

Compiles a DDL, DML, DCL, or DSL

statement.
RELEASE SQLRM Deallocates memory for a dynamic SQL

statement referred to through a host

variable.

*This statement is documented in one or more of these manuals:

SQLRM SQL/MP Reference Manual
SQLPM/C SQL/MP Programming Manual for C

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

3-6

SQL/MP Statements and Directives

Table 3-2 summarizes the C compiler pragmas that apply to a C program containing

embedded SQL statements and directives. For a description of all C compiler pragmas,
see the C/C++ Programmer’s Guide.

Table 3-2. C Compiler Pragmas for SQL/MP

Pragma Manual* Description

Finding Information

SQL

SQLMEM

* This statement is documented in one or more of these manuals:

CPG C/C++ Programmer’s Guide
SQLPM/C SQL/MP Programming Manual for C

SQLPM/C
CPG

SQLPM/C
CPG

Indicates to the C compiler that a program contains
embedded SQL statements and directives.

Also specifies options for processing the SQL statements
or directives:

SQLMAP generates an SQL map in the listing.



WHENEVERLIST writes active WHENEVER options to



the listing file after each SQL statement is processed.
RELEASE1 or RELEASE2 specifies the version of the



SQL/MP features in the program (including the SQL
data structures) and the version of SQL/MP software
on which the program file can run.

Controls the placement of SQL internal structures in either
the user data segment or extended data segment.

SQLMEM applies only to the C compiler on TNS systems.
The native mode C (NMC) compiler on TNS/R systems
ignores this pragma.

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

3-7

SQL/MP Statements and Directives

Finding Information

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

3-8

4 Data Retrieval and Modification

You can access data in an SQL/MP database using this Data Manipulation Language
(DML) statements in a C program:

Simple data manipulations––SELECT (single-row), INSERT, UPDATE, and



DELETE statements
Cursor operations––DECLARE CURSOR, OPEN, FETCH, and CLOSE statements



where the cursor contains a SELECT, UPDATE, or DELETE statement

Topics include:

Opening and Closing Tables and Views on page 4-2



Single-Row SELECT Statement on page 4-4



Multirow SELECT Statement on page 4-6



INSERT Statement on page 4-8



UPDATE Statement on page 4-10



DELETE Statement on page 4-12



Using SQL Cursors on page 4-14



Table 4-1 provides some guidelines for using these statements.

Table 4-1. SQL/MP Statements for Data Retrieval and Modification (page 1 of 2)

SQL/MP Statement Description

Single-Row SELECT statement Retrieves a single row of data from a table or

protection view and places the specified column
values in host variables. Use when you need to
retrieve only a single row.

SELECT statement with a cursor Retrieves a set of rows from a table or view, one row

at a time, and places the specified column values in
host variables. Use when you need to retrieve more
than one row.

INSERT statement Inserts one or more rows into a table or protection

view. Use for all INSERT operations.

UPDATE statement without a cursor Updates the values in one or more columns in a

single row or a set of rows of a table or protection
view. Use when you do not need to test a column
value in a row before you update the row.

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

4-1

Data Retrieval and Modification

Table 4-1. SQL/MP Statements for Data Retrieval and Modification (page 2 of 2)

SQL/MP Statement Description

UPDATE statement with a cursor Updates the values in one or more columns in a set

DELETE statement without a cursor Deletes a single row or a set of rows from a table or

DELETE statement with a cursor Deletes a set of rows, one row at a time, from a table

Note. Using a cursor can sometimes degrade a program’s performance. A cursor operation
requires the OPEN, FETCH, and CLOSE statements, which increases the number of
messages between the file system and disk process. Therefore, consider not using a cursor if
a single-row SELECT statement is sufficient.

Opening and Closing Tables and Views

of rows, one row at a time. Use when you need to
test a column value in a row before you update the
row.

protection view. Use when you do not need to test a
column value in a row before you delete the row.

or protection view. Use when you need to test a
column value in a row before you delete the row.

Opening and Closing Tables and Views

NonStop SQL/MP automatically opens and closes tables and views during the
execution of DDL statements, DML statements, and SQL utility operations such as a
LOAD or COPY. NonStop SQL/MP opens a table or view when a host-language
program runs the first SQL statement that refers to the table or view and then closes
the table or view when the program that opened it stops. A program cannot explicitly
open an SQL table or view; however, a program can force NonStop SQL/MP to close a
table using the CLOSE TABLES option of the FREE RESOURCES statement.

By default, NonStop SQL/MP opens partitions of base tables and indexes only if they
are needed by a program. To cause NonStop SQL/MP to open all indexes and
partitions the first time a partition is accessed, use the OPEN ALL option of the
CONTROL TABLE directive.

Note. Using the CONTROL TABLE statement with the OPEN ALL option could increase the
amount of work done by an SQL statement. For efficient performance, use the OPEN ALL
option with the CONTROL TABLE statement only if all these are true:

When all open activity must occur when the program first starts (add a "dummy" call to the



cursor during initialization).
When the object containing the cursor will eventually access all partitions.



When the plan for the cursor is not a parallel plan.



Causes of SQL Error 8204 (Lost Open Error)

SQL error 8204 is sometimes referred to as the “lost open” error. This scenario
explains how this error can occur:

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

4-2

Data Retrieval and Modification

1. A program accesses a table or view using one or more static DML statements
(SELECT, INSERT, UPDATE, or DELETE) or a static cursor. The SQL executor
opens the table or view for the program.

2. Any locks associated with the statements in Step 1 are released (for example,
because the transaction ended). Another user then runs one of these DDL
statements or utility operations for the table or view, which causes the system to
terminate the program’s open:

ALTER TABLE with ADD COLUMN, ADD PARTITION, DROP PARTITION, or



RENAME
ALTER TABLE with AUDIT, BUFFERED, LOCKLENGTH, MAXEXTENTS,



SERIALWRITES, TABLECODE, or VERIFIEDWRITES
ALTER INDEX with ADD PARTITION, DROP PARTITION, or RENAME



ALTER INDEX with BUFFERED, MAXEXTENTS, TABLECODE,



SERIALWRITES, or VERIFIEDWRITES
ALTER VIEW with RENAME



Recovering From SQL Error 8204

CREATE CONSTRAINT and CREATE INDEX



DROP CONSTRAINT, DROP INDEX, DROP TABLE,



or DROP VIEW (protection view only)
UPDATE STATISTICS



COPY, LOAD, PURGEDATA, or RESTORE utility operation



(A disk or network line that goes down and then comes up again can also cause
the system to terminate a program’s open.)

3. The program tries to run another SQL statement for the table or view.

4. The SQL executor tries to recover, as described next. However, if it cannot recover
from the error, the executor returns error -8204 to the program, and the program
loses its open for the table or view.

Recovering From SQL Error 8204

If a program runs a static DML statement and the open for a table or view it is using
has been lost because of a DDL statement or utility operation, the SQL executor tries
to recover as described next.

Simple DML Statements

For static DML statements (SELECT, INSERT, UPDATE, and DELETE), the SQL
executor reopens the changed table or view and then retries the DML statement once
using the new definition of the table or view. If the retry is successful, the SQL executor
returns a warning (8204) to the program. However, if the retry fails, the SQL executor
returns an error (-8204).

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

4-3

Data Retrieval and Modification

To recover from SQL error -8204 for a simple DML statement, a program might need to
abnormally terminate the transaction and restart the operation from its beginning.

Because some DDL changes can invalidate a DML statement, the SQL executor might
first need to recompile the DML statement to use the new definition of the changed
table or view. In some cases, the similarity check can prevent recompilation. For more
information, see Section 8, Program Invalidation and Automatic SQL Recompilation.

If the program does not allow automatic recompilation (the NORECOMPILE option is
set), the SQL executor returns error -8027. In this case, you must explicitly recompile
the program using the new definition of the table or view.

Static Cursor Operations

For a static cursor operation, the SQL executor tries to reestablish the open in these
situations:

The program has not yet opened the cursor.



The program has opened the cursor, but the OPEN CURSOR statement did not



require any input host variables, and the first FETCH statement has not yet been
run.

Single-Row SELECT Statement

However, if the problem occurs on a FETCH statement, the SQL executor closes the
cursor and returns error -8204. The program must then close and reopen the cursor
before executing a subsequent FETCH statement. The program might need to
abnormally terminate the transaction and restart the cursor operation from its
beginning.

Single-Row SELECT Statement

A single-row SELECT statement retrieves a single row of data from one or more tables
or views and places the column values into corresponding host variables.

To select a set of rows, one row at a time using a cursor, see Using SQL Cursors
page 4-14.

To run a SELECT statement, a process started by the program must have read access
to all tables, protection views, and the underlying tables of any shorthand views used in
the statement. For details, see Required Access Authority on page 7-1.

Do not use an asterisk (*) in a SELECT statement in a C program. A SELECT
statement with an asterisk always assigns columns in the result table from the current
definition of the referenced tables or views. If columns have been added to a table, the
retrieved data values might not be in the expected order.

on

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

4-4

Data Retrieval and Modification

NonStop SQL/MP returns these values to sqlcode after a SELECT statement:

sqlcode Value Description

0 The SELECT statement was successful.
100 No rows qualified for the SELECT statement specification.

Using a Column Value to Select Data

<0 An error occurred;
>0 (¦100) A warning occurred;

sqlcode contains the error number.

sqlcode contains the warning number.

For more information about sqlcode, see Section 9, Error and Status Reporting.

Using a Column Value to Select Data

This SELECT statement returns a row containing a customer’s name and address
based on the unique value of a column (a nonkey value). Each customer is identified
by a unique number so that only one customer satisfies the query. This example uses
a WHERE clause to specify that the CUSTOMER.CUSTNAME column contains a
unique value equal to the host variable find_this_customer. (This example sets
find_this_customer to customer number 5635 using an assignment statement,
but in a typical application, a user would enter the number.)

EXEC SQL BEGIN DECLARE SECTION;
struct customer_type /* host variables */
{
short custnum;
char custname[19];
char street[23];
char city[15];
char state[13];
char postcode[11];
} customer;

int find_this_customer;
EXEC SQL END DECLARE SECTION;
...

...
void not_found_function(void) /* For NOT FOUND condition */
{
...
}
void find_record(void)
{
find_this_customer = 5635;
EXEC SQL SELECT customer.custname,
customer.street,
customer.city,
customer.state,
customer.postcode
INTO :customer.custname,
:customer.street,
:customer.city,
:customer.state,

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

4-5

Data Retrieval and Modification

:customer.postcode
FROM sales.customer
WHERE customer.custnum = :find_this_customer
BROWSE ACCESS;

/* Process data returned by the SELECT statement */
...
}
int main(void)
{
EXEC SQL WHENEVER NOT FOUND CALL :not_found_function;

find_record();
...
}

Using a Primary Key Value to Select Data

Using a Primary Key Value to Select Data

This SELECT statement returns an employee’s first name, last name, and depart ment
number from the EMPLOYEE table using a primary key value (EMPNUM column). The
WHERE clause specifies that the selected row contains a primary key with a value
equal to the host variable find_this_employee. The SELECT statement retrieves
only one row because the primary key value is unique.

find_this_employee = input_empnum /* set host variable */

EXEC SQL SELECT employee.first_name,
employee.last_name,
employee.deptnum
INTO :employee.first_name,
:employee.last_name,
:employee.deptnum
FROM persnl.employee
WHERE employee.empnum = :find_this_employee;

Multirow SELECT Statement

Applications frequently request a group of rows for display on a screen, then request
the next sequential group of rows.

If the operation is performed in a Pathway environment, a context-free server must
receive the starting position for requesting the next set of records from the requester. It
cannot save the starting position from a previous operation.

Assume that the initial request from the requester passes a blank or zeros, and that
each subsequent request passes the search column values of the last record returned.
The server uses the values sent from the requester to establish the starting position in
the table. The server fetches the next set of rows from that position.

These examples illustrate several ways to define cursors that reposition on a key
value. The illustrations start with a simple solution and proceed to increasingly complex
solutions.

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

4-6

Data Retrieval and Modification

Simple Example

In this example, the search is performed on one column, which is the primary key of
the table. For example, a cursor SELECT to retrieve all the columns in the EMPLOYEE
table by primary key.

The WHERE clause in this example selects on a primary key value. This means that
the SQL compiler can choose the primary index as the access path so that each
FETCH statement returns the next row in primary key sequence. This code is simple
and efficient:

SELECT EMPNUM, FIRST_NAME, LAST_NAME, DEPTNUM, SALARY
FROM =EMPLOYEE
WHERE EMPNUM > :LASTEMPNUM
ORDER BY EMPNUM

A More Complex Example

In a slightly more complex example, suppose that the search uses a column that is not
the primary key (for example, the column LAST_NAME). In this case, the query should
be faster if there is an index on LAST_NAME. Suppose that there is an index on
LAST_NAME in this example:

Simple Example

SELECT EMPNUM, FIRST_NAME, LAST_NAME, DEPTNUM, SALARY
FROM =EMPLOYEE
WHERE LAST_NAME> :LAST-LNAME
ORDER BY LAST_NAME

When an index on a nonkey column is efficient and available, the SQL compiler
probably chooses that index.

The Most Complex Example

A more complex problem occurs when the key is composed of multiple columns. In this
case, you should generally use a multivalue predicate for the comparison. This type of
predicate compares multiple columns with multiple values.

Suppose that you want to retrieve the next row in sequence by last name and first
name, and an index exists on the two columns containing the last name and the first
name. Code this type of request by using a multivalue predicate. A multivalue
predicate allows you to concatenate two or more columns and compare them with two
or more concatenated values. This type of predicate retrieves the next name in
sequence. For example:

SELECT EMPNUM, FIRST_NAME, LAST_NAME, DEPTNUM, SALARY
FROM =EMPLOYEE
WHERE (LAST_NAME, FIRST_NAME) > :LAST_NAME, :FIRST_NAME
ORDER BY LAST_NAME, FIRST_NAME

If there is an index on the two columns LAST_NAME and FIRST_NAME in that order,
this query is probably as efficient as it can be.

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

4-7

Data Retrieval and Modification

Do not code this request with this WHERE clause:

WHERE LAST_NAME > :LAST-LNAME
AND FIRST_NAME > :LAST-FNAME

This clause does not retrieve names with the same last name as :LAST-LNAME and a
first name greater than :LAST-FNAME.

Also, do not code this request with this WHERE clause:

WHERE ( (LAST_NAME = :LAST-LNAME
AND FIRST_NAME > :LAST-FNAME)
OR LAST_NAME > :LAST-LNAME)

This clause would produce the correct results, but very slowly. Whenever possible,
avoid the OR disjunctive in a WHERE clause.

INSERT Statement

The INSERT statement inserts one or more rows into a table or protection view. To
insert data, a program moves the new data to a series of host variables and then runs
an INSERT statement to transfer these host variable values to the table.

INSERT Statement

To run an INSERT statement, a process started by the program must have read and
write access to the table or view receiving the data and read access to tables or views
that you include in a SELECT statement. For details, see Required Access Authority
on page 7-1.

NonStop SQL/MP returns these values to sqlcode after an INSERT statement.

sqlcode Value Description

0 The INSERT statement was successful.
100 No rows qualified for an insert using a SELECT statement

specification.
<0 An error occurred;
>0 (¦100) A warning occurred;

If an INSERT statement runs successfully, the SQLCA structure contains the number
of rows inserted. (If the INSERT statement fails, do not rely on the SQLCA structure for
an accurate count of the number of rows inserted.) To return the contents of the
SQLCA, use the SQLCADISPLAY or SQLCATOBUFFER procedure.

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

and Status Reporting.

sqlcode contains the error number.

sqlcode contains the first warning number.

and Section 9, Error

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

4-8

Data Retrieval and Modification

Inserting a Single Row

This INSERT statement inserts a row (JOBCODE and JOBDESC columns) into the
JOB table:

EXEC SQL BEGIN DECLARE SECTION;
short hv_jobcode; /* host variables */
char hv_jobdesc[18];
...
EXEC SQL END DECLARE SECTION;
...
void insert_job(void)
{
/* Set the values of hv_jobcode and hv_jobdesc */
...

EXEC SQL INSERT INTO persnl.job
(jobcode, jobdesc)
VALUES (:hv_jobcode, :hv_jobdesc) ;
...
}

Inserting a Single Row

If the INSERT operation fails, check for SQL error 8227, which indicates you attempted
to insert a row with an existing key (primary or unique alternate).

Inserting a Null Value

This example inserts a row into the EMPLOYEE table and sets the SALARY column to
a null value using an indicator variable:

/* Variable declarations: */
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL INVOKE persnl.employee AS emp_tbl;
struct emp_tbl emp;
...
short ind_1;
EXEC SQL END DECLARE SECTION;
...
/* Executable statements: */
ind_1 = -1;
EXEC SQL INSERT INTO persnl.employee
VALUES (:emp.empnum, :emp.first_name,
:emp.last_name, :emp.deptnum, :emp.jobcode,
:emp.salary INDICATOR :ind_1);

This example uses the NULL keyword instead of an indicator variable:

EXEC SQL INSERT INTO persnl.employee
VALUES (:emp.empnum, :emp.first_name,
:emp.last_name, :emp.deptnum, :emp.jobcode,
NULL);

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

4-9

Data Retrieval and Modification

Inserting a Timestamp Value

This example inserts a timestamp value into tablet.columna. The columna
definition specifies the data type TIMESTAMP DEFAULT CURRENT. The example
uses the JULIANTIMESTAMP system procedures and the SQL
CONVERTTIMESTAMP function. To call system procedures, a program must include
declarations from the cextdecs header file.

#include <cextdecs(JULIANTIMESTAMP)>
...
EXEC SQL BEGIN DECLARE SECTION;
long long dtvar;
EXEC SQL END DECLARE SECTION;
short sqlcode;

int main(void)
{
...
/* Get Julian timestamp in GMT: */
dtvar = JULIANTIMESTAMP();
EXEC SQL BEGIN WORK;
/* Insert value into tablet: */
EXEC SQL INSERT INTO tablet (columna)
VALUES (CONVERTTIMESTAMP (:dtvar));
EXEC SQL COMMIT WORK;
...
}

Inserting a Timestamp Value

UPDATE Statement

The UPDATE statement updates the values in one or more columns in a single row or
in a set of rows of a table or protection view.

To update a set of rows, one row at a time using a cursor, see Using SQL Cursors on
page 4-14.

To run an UPDATE statement, a process started by the program must have read and
write access to the table or view being updated and read access to tables or views
specified in subqueries of the search condition. For details, see Required

Access Authority on page 7-1.

For audited tables and views, NonStop SQL/MP holds a lock on an updated row until
the TMF transaction is committed or rolled back. For a nonaudited table, NonStop
SQL/MP holds the lock until the program releases it.

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

4-10

Data Retrieval and Modification

NonStop SQL/MP returns these values to sqlcode after an UPDATE statement.

sqlcode Value Description

0 The UPDATE statement was successful.
100 No rows were found on a search condition.

Updating a Single Row

<0 An error occurred;
>0 (¦100) A warning occurred;

The UPDATE statement updates rows in sequence. If an error occurs, NonStop
SQL/MP returns an error code to sqlcode and terminates the UPDATE operation.
The SQLCA structure contains the number of rows that have been updated. (If the
UPDATE statement fails, do not rely on the SQLCA structure for an accurate count of
the number of updated rows.) To return the contents of the SQLCA structure, use the
SQLCADISPLAY or SQLCATOBUFFER procedure.

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

and Status Reporting.

Updating a Single Row

This example updates a single row of the ORDERS table that contains information
about the order number specified by update_ordernum. In a typical application, a
user enters the values for update_date and update_ordernum.

EXEC SQL BEGIN DECLARE SECTION;
struct orders_type
{
long ordernum;
long order_date;
long deliv_date;
short salesrep;
short custnum;
} orders;

sqlcode contains the error number.

sqlcode contains the first warning number.

long newdate;
EXEC SQL END DECLARE SECTION;
...

...
void update_orders(void)
{ ...

newdate = update_date;
orders.ordernum = update_ordernum;

EXEC SQL UPDATE sales.orders SET deliv_date = :newdate
WHERE ordernum = :orders.ordernum
STABLE ACCESS;
...
}

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

4-11

Data Retrieval and Modification

If the UPDATE operation fails, check for SQL error 8227, which indicates you
attempted to update a row with an existing key (primary or unique alternate).

Updating Multiple Rows

If you do not need to check a value in a row before you update the row, use a single
UPDATE statement to update multiple rows in a table.

This example updates the SALARY column of all rows in the EMPLOYEE table where
the SALARY value is less than hostvar_min_salary. A user enters the values for

hostvar_inc and hostvar_min_salary.

EXEC SQL
UPDATE persnl.employee
SET salary = salary * :hostvar_inc
WHERE salary < :hostvar_min_salary;

This example updates all rows in the EMPLOYEE.DEPTNUM column that contain the
value in hostvar_old_deptnum. After the update, all employees who were in the
department specified by hostvar_old_deptnum moved to the department specified
by hostvar_new_deptnum. A user enters the values for hostvar_old_deptnum
and hostvar_new_deptnum.

Updating Multiple Rows

EXEC SQL UPDATE persnl.employee
SET deptnum = :hostvar_new_deptnum
WHERE deptnum = :hostvar_old_deptnum;

Updating Columns With Null Values

This example updates the specified SALARY column to a null value using an indicator
variable. The set_to_nulls host variable specifies the row to update.

/* indicator-var is set to -1 */
EXEC SQL UPDATE persnl.employee
SET SALARY = :emp_tbl.salary
INDICATOR :indicator_var
WHERE :emp_tbl.jobcode = set_to_nulls;

This example uses the NULL keyword instead of an indicator variable:

EXEC SQL UPDATE persnl.employee SET SALARY = NULL
WHERE :emp_tbl.jobcode = set_to_nulls;

DELETE Statement

The DELETE statement deletes one or more rows from a table or protection view. If
you delete all rows from a table, the table still exists until it is deleted from the catalog
by a DROP TABLE statement. (To delete a set of rows, one row at a time using a
cursor, see Using SQL Cursors on page 4-14.)

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

4-12

Data Retrieval and Modification

To run a DELETE statement, a process started by the program must have read and
write access to the table or view and to tables or views specified in subqueries of the
search condition. For details, see Required Access Authority on page 7-1.

NonStop SQL/MP returns these values to sqlcode after a DELETE statement.

sqlcode Value Description

0 The DELETE statement was successful.
100 No rows were found on a search condition.

Deleting a Single Row

<0 An error occurred;
>0 (¦100) A warning occurred;

After a successful DELETE operation, the SQLCA structure contains the number of
rows deleted. If an error occurs, the SQLCA contains the approximate number of rows
deleted. To return the contents of the SQLCA, use SQLCA_DISPLAY2_ or
SQLCA_TOBUFFER2_ procedure. For more information, see Section 5, SQL/MP

System Procedures and Section 9, Error and Status Reporting.

Deleting a Single Row

To delete a single row, move a key value to a host variable and then specify the host
variable in the WHERE clause. This example deletes only one row of the EMPLOYEE
table because each value in empnum (the primary key) is unique. A user enters the
value for the host variable hostvar_empnum.

EXEC SQL DELETE FROM persnl.employee
WHERE empnum = :hostvar_empnum;

Deleting Multiple Rows

If you do not need to check a column value before you delete a row, use a single
DELETE statement to delete multiple rows in a table. This example deletes all rows (or
employees) from the EMPLOYEE table specified by delete_deptnum (which is
entered by a user).

sqlcode contains the error number.

sqlcode contains the first warning number.

EXEC SQL DELETE FROM persnl.employee
WHERE deptnum = :delete_deptnum ;

This example deletes all suppliers from the PARTSUPP table who charge more than

terminal_max_cost for a terminal. Terminal part numbers range from
term_first_num to term_last_num.

EXEC SQL DELETE FROM invent.partsupp
WHERE partnum BETWEEN :term_first_num AND :term_last_num
AND partcost > :terminal_max_cost ;

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

4-13

Data Retrieval and Modification

/* C source file */

EXEC SQL BEGIN DECLARE SECTION ;

• • • /* Declare host variable(s). */
EXEC SQL END DECLARE SECTION ;

• • •
EXEC SQL DECLARE cursor1 CURSOR FOR
SELECT column1, column2, column n
FROM =table
WHERE column1 = :hostvar_find_row ;

• • •
void find_row(void)
{

• • •
hostvar_find_row = initial_value ; /* Initialize the host variable(s). */

• • •
EXEC SQL OPEN cursor1 ; /* Open the cursor. */

/* Fetch data from a row into the host variable(s). */
EXEC SQL FETCH cursor1
INTO :hostvar_1, :hostvar_2, :hostvar n ;

• • • /* Process the row values in the host variable(s). */

• • • /* Branch back to fetch another row. */

EXEC SQL CLOSE cursor1 ; /* Close the cursor. */
}

009CDT .CDD

1

2

3

4

5

6

7

8

Using SQL Cursors

An SQL cursor is a named pointer that a host-language program (C, COBOL, Pascal,
or TAL) can use to access a set of rows in a table or view, one row at time. Using a
cursor, a program can process rows in the same way it might process records in a
sequential file. The program can test the data in each row at the current cursor position
and then if the data meets certain criteria, the program can display, update, delete, or
ignore the row.

Example 4-1 shows the steps that you follow to declare and use a static SQL cursor in

a C program. A cursor operation must run each statement in this specified order. All
steps are required, even if you run the FETCH statement only once to retrieve a single
row.

Example 4-1. Using a Static SQL Cursor in a C Program

Using SQL Cursors

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

4-14

Data Retrieval and Modification

The SQL statements used in Example 4-1 are described in detail later in this section:

DECLARE CURSOR Statement on page 4-18



OPEN Statement on page 4-19



FETCH Statement on page 4-20



Multirow SELECT Statement on page 4-21



UPDATE Statement on page 4-22



Multirow DELETE Statement on page 4-23



CLOSE Statement on page 4-24



For information about declaring host variables, see Section 2, Host Variables.

Steps for Using a Cursor

1. Declare any host variables you plan to use with the cursor.

Steps for Using a Cursor

2. Name and define the cursor using a DECLARE CURSOR statement. Follow the
conventions for an SQL identifier for the cursor name. The DECLARE CURSOR
statement also associates the cursor with a SELECT statement that specifies the
rows to retrieve.

3. Initialize any host variables you specified in the WHERE clause of the SELECT
statement in the DECLARE CURSOR statement.

4. Open the cursor using an OPEN statement. The OPEN statement determines the
result table and sorts the table if the SELECT statement includes the ORDER BY
clause. For audited tables or views, the OPEN statement also associates the
cursor with a TMF transaction.

5. Retrieve the column values from a row using the FETCH statement. The FETCH
statement positions the cursor at the next row of the result table and transfers the
column values defined in the associated SELECT statement to the corresponding
host variables. The FETCH statement also locks each row according to the access
specified by the SELECT statement.

For audited tables or views, the FETCH statement must run within the same TMF
transaction as the OPEN statement.

6. Process the column values returned from the current row to host variables. For
example, you might test a value and then delete or update the row.

7. After you process the current row, branch back to the FETCH statement and
retrieve the next row. Continue executing this loop until you have processed all
rows specified by the associated SELECT statement (and sqlcode equals 100).

8. Close the cursor using the CLOSE statement. The CLOSE statement releases the
result table established by the OPEN statement. (The FREE RESOURCES
statement also releases the result table.)

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

4-15

Data Retrieval and Modification

Process Access ID (PAID) Requirements

To use an SQL cursor, a process started by the program must have the access
authority shown in this table. NonS top SQL/MP checks this authority when the program
opens the cursor. For details, see Required Access Authority on page 7-1.

Access SQL Objects

Read Tables or protection views referred to in the SELECT statement associated with

the cursor (that is, specified in the DECLARE CURSOR statement)

Read Tables or protection views underlying the shorthand view, if the cursor refers to a

shorthand view

Write Tables referenced, if the cursor declaration includes the FOR UPDATE clause

A program can use a cursor whose declaration does not specify FOR UPDATE to
locate rows in a table to delete. NonStop SQL/MP tests the table only for read access
when the OPEN statement runs. However, because a DELETE operation requires
write access, NonStop SQL/MP checks for write access when you run the DELETE
statement.

Process Access ID (PAID) Requirements

A program contending for data access with other users can specify the IN EXCLUSIVE
MODE clause in the associated SELECT statement. NonStop SQL/MP then does not
have to convert the lock for a subsequent UPDATE or DELETE operation. However, if
a program is reading records accessed concurrently by a cursor defined with an IN
EXCLUSIVE MODE clause in another program, the first program must wait to access
the data.

Cursor Position

Table 4-2 describes the SQL statements that affect the cursor position in a program.

The cursor position is similar to the record position in a sequential file.

Table 4-2. Determining the Cursor Position

SQL Statement Cursor Position or Action

OPEN Positions the cursor before the first row.
FETCH Positions the cursor at the retrieved row (or the current

DELETE Positions the cursor between rows. For example, if the

position).

current row is deleted, the cursor is positioned either
between rows or before the next row and after the
preceding row.

SELECT Determines the order in which the rows are returned. To

CLOSE Causes no position; release the result table established

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

specify an order, include an ORDER BY clause.
Otherwise, the order is undefined.

by the cursor.

4-16

Data Retrieval and Modification

Cursor Stability

Cursor stability guarantees that a row at the current cursor position cannot be modified
by another program. For NonStop SQL/MP to guarantee cursor stability, you must
declare the cursor with the FOR UPDATE clause or specify the STABLE ACCESS
option.

In some cases, a program might be accessing a copy of a row instead of the actual
row. For example, a program might be accessing a copy of the row if the associated
SELECT statement defining the cursor requires that the system perform any of these
operations:

Ordering the rows by a column



Removing duplicate rows



Performing other operations that require the selected table to be copied into a



result table before it is used by a program

If your program is accessing a copy of a row instead of the actual row, the cursor
points to a copy of the data, and the data is concurrently available to other programs.
Accessing a copy of the data, however, never occurs if the cursor is declared with the
FOR UPDATE clause. In this case, your cursor points to the actual data and has cursor
stability.

Cursor Stability

Virtual Sequential Block Buffering (VSBB)

The SQL/MP optimizer often uses Virtual Sequential Block Buffering (VSBB) as an
access path strategy. Conflicting UPDATE, DELETE, or INSERT statements can
invalidate a cursor’s buffering for a table. Each invalidation forces the next FETCH
statement to send a message to the disk process to retrieve a new buffer, which can
substantially degrade a program’s performance. These statements invalidate the buffer
for cursor operations:

An INSERT statement on the same table by the current process



A stand-alone UPDATE or DELETE statement on the same table (directly or



through a view) by the same process
An UPDATE...WHERE CURRENT or DELETE...WHERE CURRENT statement



using a different cursor to access the same table (directly or through a view) by the
same process

For example, a loop containing both a FETCH statement and a stand-alone UPDATE
or DELETE statement on the same table invalidates the cursor’s buffer on every loop
iteration. You can minimize or eliminate this problem by following these guidelines:

Do not use INSERT statements within a cursor operation.



Use the UPDATE...WHERE CURRENT or DELETE...WHERE CURRENT



statement for a cursor rather than a stand-alone UPDATE or DELETE statement.

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

4-17

Data Retrieval and Modification

Do not open multiple cursors on a table if any of the cursors are used to update



that table.

DECLARE CURSOR Statement

The DECLARE CURSOR statement names and defines a cursor and associates the
cursor with a SELECT statement that specifies the rows to retrieve.

A C program requires no special authorization to run a DECLARE CURSOR
statement.

Follow these guidelines when you use a DECLARE CURSOR statement:

The cursor name specified in the DECLARE CURSOR statement is an SQL



identifier and is not case-sensitive. For example, NonStop SQL/MP considers Cur,
cur, CUR, and CuR as equivalent names.

Declare all host variables you use in the associated SELECT statement before the



DECLARE CURSOR statement. Host variables must also be within the same
scope as all the SQL statements that refer to them.

DECLARE CURSOR Statement

Place the DECLARE CURSOR statement in listing order before other SQL



statements, including the OPEN, FETCH, INSERT, DELETE, UPDATE, and
CLOSE statements, that refer to the cursor. The DECLARE CURSOR statement
must also be within the scope of statements that reference the cursor.

The DECLARE CURSOR statement does not affect the values in the SQLCA and



SQLSA data structures.

This example declares a cursor list_by_partnum:

EXEC SQL BEGIN DECLARE SECTION;
struct parts_type /* host variables */
{
short partnum;
char partdesc[19];
long price;
short qty_available
} parts_rec;
EXEC SQL END DECLARE SECTION;
...

EXEC SQL DECLARE list_by_partnum CURSOR FOR
SELECT partnum,
partdesc,
price,
qty_available
FROM =parts
WHERE partnum >= :parts_rec.partnum
ORDER BY partnum
BROWSE ACCESS;
...

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

4-18

Data Retrieval and Modification

OPEN Statement

The OPEN statement opens an SQL cursor. The OPEN operation orders and defines
the set of rows in the result table and then positions the cursor before the first row.

The OPEN statement does not acquire any locks unless a sort is necessary to order
the selected rows. (The FETCH statement acquires any locks associated with a
cursor.)

To run an OPEN statement for a cursor, a process started by the program must have
the access authority described in Process Access ID (PAID) Requirements on
page 4-16. For details, see Required Access Authority on page 7-1.

If the associated SELECT statement contains host variables in the WHERE clause,
you must initialize these host variables before you run the OPEN statement. When the
OPEN statement runs, NonStop SQL/MP defines the set of rows in the result table and
places the input host variables in its buffers. If you do not initialize the host variables
before you run the OPEN statement, these problems can occur:

If a host variable contains values with unexpected data types, overflow or



truncation errors can occur.

OPEN Statement

If a host variable contains old values from the previous execution of the program, a



subsequent FETCH statement uses these old values as the starting point to
retrieve data. Therefore, the FETCH does not begin at the expected location in the
result table.

The host variables must also be declared within the scope of the OPEN statement.
Some additional considerations for the OPEN statement are:

You must code an OPEN statement within the scope of all other SQL statements



(including the DECLARE CURSOR, FETCH, INSERT, DELETE, UPDATE, and
CLOSE statements) that use the cursor.

The OPEN statement must run before any FETCH statements for the cursor.



For audited tables and views, the OPEN statement must run within a TMF



transaction.
If data is materialized by the OPEN operation, NonStop SQL/MP returns statistics



to the SQLSA structure. For information about returning statistics to a program,
see Section 9, Error and Status Reporting

If the DECLARE CURSOR statement for the cursor specifies a sort operation (for



example, with an ORDER BY clause), do not issue an AWAITIO or AWAITIOX
statement with the filenum parameter set to -1 after you open the cursor;
otherwise, the sort operation fails with SQL error -8301.

.

This OPEN statement opens the list_by_partnum cursor:

...
EXEC SQL OPEN list_by_partnum;
...

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

4-19

Data Retrieval and Modification

FETCH Statement

The FETCH statement positions the cursor at the next row of the result table and
transfers a value from each column in the row specified by the associated SELECT
statement to the corresponding host variable.

To run a FETCH statement, a process started by the program must have read access
to tables or views associated with the cursor. For information about process access,
see Required Access Authority on page 7-1.

NonStop SQL/MP returns these values to sqlcode after a FETCH statement.

sqlcode Value Description

0 The FETCH statement was successful.
100 The end of a table was encountered.

FETCH Statement

<0 An error occurred;
>0 (¦100) A warning occurred;

sqlcode contains the error number.

sqlcode contains the first warning number.

The cursor must be open when the FETCH statement runs. The FETCH statement
must also run within the scope of all other SQL statements, including the DECLARE
CURSOR, OPEN, INSERT, DELETE, UPDATE, and CLOSE statements, that refer to
the cursor.

NonStop SQL/MP resets values in an SQLSA structure immediately before a FETCH
statement runs. If you use an SQLSA value elsewhere in your program, save the value
in a variable immediately after the FETCH statement runs. To monitor statistics for a
cursor, declare accumulator variables for the required values and add the SQLSA
values to the accumulator variables after each FETCH statement runs.

For audited tables and views, the FETCH statement must run within the same TMF
transaction as the OPEN statement for the cursor.

This FETCH statement retrieves information from the PARTS table:

EXEC SQL BEGIN DECLARE SECTION;

struct parts_type /* host variables */
{
short partnum;
char partdesc[19];
long price;
short qty_available
} parts_rec;
...
EXEC SQL END DECLARE SECTION;
...

...
EXEC SQL DECLARE list_by_partnum CURSOR FOR
SELECT partnum,partdesc,price,qty_available
FROM =parts

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

4-20

Data Retrieval and Modification

WHERE partnum >= :parts_rec.partnum
ORDER BY partnum
BROWSE ACCESS;
...

void list_func(void)
{
EXEC SQL OPEN list_by_partnum;
EXEC SQL FETCH list_by_partnum
INTO :parts_rec.partnum,
:parts_rec.partdesc,
:parts_rec.price,
:parts_rec.qty_available;
...
}

Multirow SELECT Statement

When used with a cursor , a SELECT statement can return multiple rows from a t able or
protection view, one row at a time. A cursor uses a FETCH statement to retrieve each
row and store the selected column values in host variables. The program can then
process the values (for example, list or save them in an array).

Multirow SELECT Statement

To run a SELECT statement, a process started by the program must have read access
to all tables, protection views, and the underlying tables of shorthand views used in the
statement. For information about process access, see Required Access Authority on
page 7-1.

All statements that refer to the cursor, including the DECLARE CURSOR, OPEN,
FETCH, and CLOSE statements, must be within the same scope.

This example uses the get_name_address cursor to return the name and address of
all customers within a certain range from the CUSTOMER table. For data consistency,
the SELECT statement includes the REPEATABLE ACCESS clause to lock the rows.
The BETWEEN clause specifies the range of zip codes, and the ORDER BY clause
sorts the rows by zip code (POSTCODE).

EXEC SQL BEGIN DECLARE SECTION;
char begin_code[11], end_code[11];
EXEC SQL INVOKE =customer AS customer_struct;
struct customer_struct customer_row;
...
EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE get_name_address CURSOR FOR
SELECT custname, street, city, state, postcode
FROM =customer
WHERE postcode BETWEEN :begin_code AND :end_code
ORDER BY postcode
REPEATABLE ACCESS;
...
void list_customers(void)
{

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

4-21

Data Retrieval and Modification

...
EXEC SQL OPEN get_name_address;

... /* Set values for begin_code and end_code. */

EXEC SQL FETCH get_name_address
INTO :customer_row.custname,
:customer_row.street,
:customer_row.city,
:customer_row.state
:customer_row.postcode;

... /* Process the row values. */

EXEC SQL CLOSE get_name_address;
}

UPDATE Statement

When used with a cursor, an UPDATE statement updates rows, one row at a time, in a
table or protection view. To identify the set of rows to update (or test), specify the FOR
UPDATE OF clause in the associated SELECT statement. Before you update each
row, you can test one or more column values. If you decide to update the row, specify
the WHERE CURRENT OF clause in the UPDATE statement.

UPDATE Statement

To run an UPDATE statement, a process started by the program must have read and
write access to the table or view being updated. This process must also have read
access to tables or views specified in subqueries of the search condition. For
information about process access, see Required Access Authority on page 7-1.

Do not use a stand-alone UPDATE statement to update a row that has been retrieved
using a FETCH statement. A stand-alone UPDATE statement invalidates the cursor's
buffering for the table and can substantially degrade performance.

An UPDATE statement must be within the scope of all other SQL statements, including
the DECLARE CURSOR, OPEN, FETCH, INSERT, and CLOSE statements, that refer
to the cursor . For audited tables and views, the UPDATE statement must run within the
same TMF transaction as the OPEN and FETCH statements for the cursor.

This example uses the cursor get_by_partnum and host variables

new_partdesc, new_price, and new_qty to update the PARTS table:

EXEC SQL DECLARE get_by_partnum CURSOR FOR
SELECT parts.partnum,
parts.partdesc,
parts.price,
parts.qty_available
FROM sales.parts
WHERE (parts.partnum >= :parts.partnum )
STABLE ACCESS
FOR UPDATE OF parts.partdesc,
parts.price,
parts.qty_available;

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

4-22

Data Retrieval and Modification

...
EXEC SQL OPEN get_by_partnum;
... /* Set values of the host variables. */

EXEC SQL FETCH get_by_partnum INTO ... ;

... /* Test the value(s) in the current row. */

/* Update the current row */
EXEC SQL UPDATE sales.parts
SET parts.partdesc = :new_partdesc,
parts.price = :new_price,
parts.qty_available = :new_qty
WHERE CURRENT OF get_by_partnum;

... /* Branch back to FETCH to get the next row. */
EXEC SQL CLOSE get_by_partnum;

Multirow DELETE Statement

When used with a cursor , a DELETE st atement deletes multiple rows one row at a time
from a table or protection view. You identify the set of rows to delete (or test) in the
associated SELECT statement. Before you delete a row, you can test one or more
column values, and then if you decide to delete the row, specify the WHERE
CURRENT OF clause in the DELETE statement.

Multirow DELETE Statement

If you delete all rows from a table, the table still exists until it is deleted from the catalog
by a DROP TABLE statement.

To run a DELETE statement, a process started by the program must have read and
write access to the table or view and to tables or views specified in subqueries of the
search condition. For more information about process access, see Required

Access Authority on page 7-1.

A DELETE statement must run within the scope of all other SQL statements, including
the DECLARE CURSOR, OPEN, FETCH, INSERT, and CLOSE statements, that refer
to the cursor. For audited tables and views, the DELETE statement must run within the
same TMF transaction as the OPEN and FETCH statements for the cursor.

Note. Do not use a stand-alone DELETE statement to delete a row that has been retrieved
using a FETCH statement. A stand-alone DELETE statement can invalidate the cursor’s
buffering for the table and degrade performance.

This example declares a cursor get_by_partnum, fetches data from the PARTS
table, tests the data, and then deletes specific rows:

EXEC SQL DECLARE get_by_partnum CURSOR FOR
SELECT partnum,
partdesc,
price,
qty_available

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

4-23

Data Retrieval and Modification

FROM sales.parts
WHERE (partnum >= :parts.partnum);

...

EXEC SQL OPEN get_by_partnum;

EXEC SQL FETCH get_by_partnum ... ;

... /* Test the value(s) in the current row. */

/* Delete the current row */
EXEC SQL DELETE FROM sales.parts
WHERE CURRENT OF get_by_partnum ;
... /* Branch back to FETCH the next row. */
EXEC SQL CLOSE get_by_partnum;

CLOSE Statement

The CLOSE statement closes an open SQL cursor. After the CLOSE statement runs,
the result table established by the OPEN statement no longer exists. To use the cursor
again, you must reopen it using an OPEN statement.

CLOSE Statement

A program does not require special authorization to run a CLOSE statement.
A CLOSE statement must be within the scope of all other SQL statements, including

the DECLARE CURSOR, OPEN, FETCH, INSERT, DELETE, and UPDATE
statements, that refer to the cursor.

This CLOSE statement closes the list_by_partnum cursor:

...

void list_func(void)
{
...
EXEC SQL CLOSE list_by_partnum;
}

Only an explicit CLOSE statement (or a FREE RESOURCES statement) closes an
open SQL cursor. The CLOSE operation releases the resources used by the cursor
and frees any locks the cursor holds. If you are planning to reuse a cursor later in your
program, you can usually leave it open to save the overhead of opening it. However, if
your program is a Pathway server, always close an open cursor before returning
control to the requester, especially if the requester initiated a TMF transaction.

Using Foreign Cursors

Foreign cursors are cursors that are not declared in the program or procedure in which
they are referenced. Only dynamic cursors can be foreign cursors. Static cursors
cannot be foreign cursors.

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

4-24

Data Retrieval and Modification

A reference to a foreign cursor contains two parts, a procedure name and a cursor
name. This example references a foreign cursor, list_by_partnum, which is
declared in the procedure update_inv:

update_inv.list_by_partnum

A foreign cursor reference can appear in an OPEN, FETCH, or CLOSE cursor
statement. It references a cursor that is declared in another procedure, which is not
necessarily in the same compile source file. References to a dynamic foreign cursor
are resolved at run time by the SQL Executor.

The cursor declaration and the PREPARE statement must be in the same procedure
so that the resolution between the PREPARE and the cursor declaration can occur to
detect whether a statement name has been prepared and to maintain proper
association between a procedure and a particular statement name.

This example declares a cursor list_by_partnum:

update_inv(void)

{

Using Foreign Cursors

EXEC SQL BEGIN DECLARE SECTION;
struct parts_type
{
/* define host variables here */
} parts_rec;
EXEC SQL END DECLARE SECTION;
...

EXEC SQL DECLARE list_by_partnum CURSOR FOR
SELECT partnum, /* defined above */
partdesc,
price,
qty_available
FROM =parts
WHERE partnum >= :parts_rec.partnum
ORDER BY partnum
BROWSE ACCESS;
EXEC SQL PREPARE dynamic_statement FROM :hv_text;
}

These statements open, fetch, and close a foreign cursor list_by_partnum that are
declared in the procedure update_inv:

/* Loop while not EOF: */
void update_inv_total(void)
{
/* describe input and output here */
exec sql open update_inv.list_by_partnum using descriptor input­sqlda;
}
{
/* describe input and output here */
exec sql fetch update_inv.list_by_partnum using descriptor
output-sqlda;

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

4-25

Data Retrieval and Modification

}
{
/* describe input and output here */
exec sql close update_inv.list_by_partnum;
}

Using Foreign Cursors

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

4-26

5 SQL/MP System Procedures

Table 5-1 describes the NonStop SQL/MP system procedures, which are written in

TAL, that a C program can call to return various SQL information. These procedures
are listed alphabetically.

Table 5-1. SQL/MP System Procedures

Procedure Description
To Return Error and Warning Information

SQLCADISPLAY Writes to a file or terminal the error and warning

messages that NonStop SQL/MP returns to the
SQLCA structure.

SQLCAFSCODE Returns information about file-system, disk-process, or

operating system errors from the SQLCA structure.

SQLCAGETINFOLIST Returns to an area in the program a specified subset of

the error or warning information in the SQLCA
structure.

SQLCATOBUFFER Returns to a record area in the program the error or

warning messages that NonStop SQL/MP returns to
the SQLCA structure.

To Return Version Information

SQLGETCATALOGVERSION Returns the version of an SQL catalog.
SQLGETOBJECTVERSION Returns the version of an SQL object (table, index, or

view).

SQLGETSYSTEMVERSION Returns the version of the SQL file-system and

disk-process components for a specified system.

To Return Execution Statistics

SQLSADISPLAY Writes to a file or terminal the execution statistics that

NonStop SQL/MP returns to the SQLSA structure.

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

5-1

SQL/MP System Procedures

Guardian System Procedures

In addition to the procedures in Table 5-1 on page 5-1, a C program can also call the
Guardian procedures described in Table 5-2 to return information about SQL objects
and programs. For a detailed description of these procedures, see the Guardian
Procedure Calls Reference Manual.

Table 5-2. Guardian System Procedures That Return SQL Information

Procedure Description

FILE_GETINFO_ Returns limited information, including the last error and

type, about a file using the file number.

FILE_GETINFOBYNAME_ Returns limited information about a file using the file

name.

FILE_GETINFOLIST__ Returns detailed information about a file using the file

number. Item codes 40, 82, 83, 84, and 85 apply to
NonStop SQL/MP.

Guardian System Procedures

FILE_GETINFOLISTBYNAME_ Returns detailed information about a file using the file

name. Item codes 40, 82, 83, 84, and 85 apply to
NonStop SQL/MP.

cextdecs Header File

The cextdecs header file contains source declarations for the SQL/MP and Guardian
system procedures. Use the #include directive as shown in this example to copy the
declarations from the cextdecs header file for the procedures you want to call in your
program:

...
#include <cextdecs ( FILE_OPEN_, \
READ, \
WRITEREAD, \
FILE_GETINFO_, \
FILE_CLOSE_, \
SQLCADISPLAY, \
SQLCAFSCODE )> nolist
...

SQL Message File

The SQLMSG file contains error messages, informational messages, and help text
used by SQLCI, the SQL compiler, and host-language programs. The default SQL
message file is $SYSTEM.SYSTEM.SQLMSG. A C program opens and reads the SQL
message file when it calls an SQL system procedure that returns error or status
information (for example, SQLCADISPLAY or SQLCATOBUFFER).

The SQLMSG file contains text in English. You can specify a different SQL message
file (for example, a file translated into French) with the =_SQL_MSG_node DEFINE.

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

5-2

SQL/MP System Procedures

For the alternate SQL message files available on your node, ask your database
administrator or service provider.

You can add (or modify) the =_SQL_MSG_node DEFINE either interactively from
TACL or SQLCI, or programmatically from a C program:

From TACL or SQLCI, enter an ADD DEFINE (or ALTER DEFINE) command. Do



not include a backslash (\) or a space before the node name. For example, this
command adds a new DEFINE for the $SQL.MSG.FRENCH message file on the
\PARIS node:

ADD DEFINE =_SQL_MSG_PARIS,CLASS MAP,FILE $SQL.MSG.FRENCH

For the _SQL_MSG_node DEFINE to be in effect for an SQLCI session, you must
add or change the DEFINE before you start the SQLCI session. If you add or
change the DEFINE after you start the session, NonStop SQL/MP returns warning
message 10201, which indicates that the DEFINE has been changed but the old
message file is still in effect.

From a C program, call the DEFINEADD (or DEFINESETATTR) system procedure.



Your program must add or alter the DEFINE before it calls a system procedure that
opens and reads the SQL message file. Otherwise, your program uses the default
message file. For more information about system procedures, see the Guardian
Procedure Calls Reference Manual.

SQLCADISPLAY

SQLCADISPLAY

The SQLCADISPLAY procedure displays error or warning information that NonStop
SQL/MP returns to the SQLCA data structure. SQLCADISPLAY writes this information
to a file or terminal.

The information returned to the SQLCA structure can originate from these subsystems
or system components:

NonStop SQL/MP



NonStop operating system



File system



Disk process (DP2)



FastSort program (SORTPROG process)



Sequential I/O (SIO) procedures



NonStop SQL/MP communicates errors, warnings, and statistics to a program through
the SQLCA structure. However, because the SQLCA contains information in a format
that is not appropriate for display, call the SQLCADISPLAY procedure to convert this
information to an appropriate format.

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

5-3

SQL/MP System Procedures

#include <cextdecs(SQLCADISPLAY)>

void SQLCADISPLAY (
short *sqlca, /* i */
[ short output_file_number, ] /* i */
[ short output_record_length, ] /* i */
[ short *sql_msg_file_number, ] /* i:o */
[ short errors, ] /* i */
[ short warnings, ] /* i */
[ short statistics, ] /* i */
[ short caller_error_loc, ] /* i */
[ short internal_error_loc, ] /* i */
[ char *prefix, ] /* i */
[ short prefix_length, ] /* i */
[ char *suffix, ] /* i */
[ short suffix_length, ] /* i */
[ short *detail_params ] /* i */
);

SQLCADISPLAY

sqlca

is a pointer to the SQLCA structure. The C compiler automatically declares the
SQLCA structure when you specify the INCLUDE SQLCA directive.

output_file_number

is the output file number. If you omit this value or set it to a negative value,
SQLCADISPLAY displays information at your home terminal. In this case,
SQLCADISPLAY opens your home terminal, displays the message, and then
closes your terminal. This parameter is ignored if detail_params specifies
sequential I/O (SIO).

output_record_length

is the length in bytes of records to be written to the output file. The length must be
an integer value from 60 through 600. The default length is 79 bytes.

sql_msg_file_number

is the file number of the SQL message file (SQLMSG is the default file). If you
specify -1 as the input value, the system opens the message file and returns the
resulting file number. If you specify a value other than -1, the system uses that
value as the file number of the message file.

To improve the performance of multiple calls to the SQLCADISPLAY (or the
SQLCATOBUFFER procedure), specify -1 on the first call and then use the
returned file number for subsequent calls. By using the file number, the system
opens the file only once and uses the file number for subsequent calls. Otherwise,
the system opens the file for each call.

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

5-4

SQL/MP System Procedures

The SQLMSG file contains text in English. You can specify a different SQL
message file with the =_SQL_MSG_node DEFINE. For more information, see

SQL Message File on page 5-2.

errors

controls the display of error messages:
Y Display all errors.
N Display only the first error.
B Display all errors but suppress this prefix:

ERROR from subsystem [nn]

The default is Y.

warnings

controls the display of warning messages:

SQLCADISPLAY

Y Display all warning messages.
N Display all warning messages.
B Display all warnings but suppress this prefix:

WARNING from subsystem [nn]

The default is Y.

statistics

controls the display of statistics:
Y Display row and cost statistics if the value returned to the SQLCA in the

ROW or COST field is greater than or equal to 0.
N Do not display statistics.
R Display row statistics only.
C Display cost statistics only.

The default is Y.

caller_error_loc

controls the display of the program name and line number of the SQL statement
that received the error:

Y Display the program name and line number.
N Suppress the display.

The default is Y.

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

5-5

SQL/MP System Procedures

internal_error_loc

controls the display of the system-code location where the first error in the SQLCA
occurred:

Y Display the location.
N Suppress the display.

The default is Y.

prefix

is a string that the program uses to precede each output line. The default is three
asterisks and a space (*** ).

prefix_length

is the length of the prefix string for each output line. The length must be an
integer from 1 to 15. If you include prefix, prefix_length is required.

SQLCADISPLAY

suffix

is a string to be appended to each output line. The default is a null string.

suffix_length

is the length of the suffix string for each output line. The length must be an
integer value from 1 to 15. If you include suffix, suffix_length is required.

detail_params

determines whether the program uses sequential I/O (SIO) or Enscribe I/O to write
to the output file. The parameter detail_params points to a structure with this
layout:

struct detail_params_type
{
char sio;
short *out_fcb_1;
short *out_fcb_2;
} detail_params;

sio

specifies whether sequential I/O is used:
Y Use SIO; ignore output_file_number.
N Do not use SIO; write to output_file_number.

out_fcb_1

specifies the first output file control block if SIO is enabled.

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

5-6