HP NonStop SQL/MP User Manual

Dynamic SQL Operations
HP NonStop SQL/MP Programming Manual for C—429847-008
10-17
Null Values
If you want all your parameters and output variables to handle null values, your
program should access ind_ptr every time it accesses var_ptr.
Handling Null Values in Input Parameters
A program uses an indicator parameter to indicate that a null value was entered for a
parameter. The indicator parameter follows the parameter in the SQL statement; for
example:
INSERT INTO =employee VALUES (1000, ?p INDICATOR ?i );
If a user enters a null value for ?p, the program should set ?i to a value less than zero.
If a user enters a non-null value for ?p, the program should set ?i to 0. Both ?p and ?i
are in the names buffer, so the program can prompt the user for a null value.
Each parameter in the statement entered by the user or constructed by your program
must have a corresponding indicator parameter to handle possible null values, or a
run-time error occurs when a null value is encountered.
After DESCRIBE INPUT runs and for each input parameter described in an sqlvar
array in the input SQLDA structure, NonStop SQL/MP sets null_info to -1 if the
input parameter in the prepared statement allows a null value (that is, if the prepared
statement included a null indicator).
Your program then checks null_info. If null_info contains a -1 and you are
allocating memory dynamically, you can now allocate two bytes of memory for a null
indicator value, and then set ind_ptr to point to the memory. Allocate this memory at
the same time you allocate memory for a possible nonnull parameter value.
If the user specifies a null value for the parameter, assign a -1 to the location pointed to
by ind_ptr. NonStop SQL/MP checks this value and then transmits a null value for
the parameter.
However, if the user does not enter a null value for the input parameter, you can assign
a 0 to the location indicated by ind_ptr. NonStop SQL/MP checks ind_ptr, sees
that ind_ptr indicates a nonnull value, and gets the parameter value from the
location indicated by var_ptr.
Handling Null Values in Output Variables
DESCRIBE sets null_info to -1 if the output variable can be null (that is, if the
prepared statement includes a null indicator). If the value returned is null, NonStop
SQL/MP checks null_info and moves a -1 into the location pointed to by ind_ptr.
(Errors are returned if the value is null but null_info is zero (0) or if ind_ptr is an
invalid address.)
Your program must check null_info to determine whether the value returned can be
null. If null_info contains a -1, then your program checks the location pointed to by
ind_ptr. If that location contains a -1, then a null value was returned. If the location
contains 0, then a nonnull value was returned and your program should get the value
from the location pointed to by var_ptr.

HP NonStop SQL/MP Specifications

General

Platform	HP NonStop Servers
Data Model	Relational
SQL Standard	ANSI SQL
Concurrency Control	Multi-Version Concurrency Control (MVCC)
Fault Tolerance	Yes
High Availability	Yes
Category	Database Software
Initial Release	1980s
Operating System	HP NonStop OS
License	Proprietary
Type	Relational Database Management System
Transaction Management	ACID Compliance
Developer	Hewlett Packard Enterprise

Summary

1. Introduction

Developing a C Program

Outlines the process of creating C programs with embedded SQL statements.

Dynamic SQL

Describes how to construct, compile, and run SQL statements at runtime.

2. Host Variables

3. SQL/MP Statements and Directives

4. Data Retrieval and Modification

Single-Row SELECT Statement

Retrieves a single row of data from tables or views into host variables.

Multirow SELECT Statement

Returns multiple rows from tables or views, one row at a time, using cursors.

INSERT Statement

Inserts one or more rows into a table or protection view using host variables.

Using SQL Cursors

Explains how to use named pointers to access sets of rows one at a time.

5. SQL/MP System Procedures

6. Explicit Program Compilation

Explicit Program Compilation

Outlines the general steps for compiling C programs with embedded SQL.

Developing a C Program in the Guardian Environment

Details C program development within the Guardian environment.

Developing a C Program in the OSS Environment

Developing a C Program in a PC Host Environment

7. Program Execution

Required Access Authority

Specifies the access permissions needed to run NonStop SQL program files.

Entering the TACL RUN Command

Running a Program in the OSS Environment

8. Program Invalidation and Automatic SQL Recompilation

Program Invalidation

Explains how SQL program files can become invalid due to changes or errors.

Causes of Program Invalidation

Automatic SQL Recompilation

Describes the run-time recompilation process invoked by the SQL executor.

9. Error and Status Reporting

Returning Error and Warning Information

Using the WHENEVER Directive

10. Dynamic SQL Operations

Uses for Dynamic SQL

Identifies scenarios where dynamic SQL programs are beneficial.

Dynamic SQL Statements

Summarizes dynamic SQL statements usable in a C program.

Dynamic SQL Features

11. Character Processing Rules (CPRL) Procedures

Appendix B: Memory Considerations

Estimating Memory Requirements

Avoiding Memory Stack Overflows

Provides guidelines to prevent stack overflows for SQL statements.