Digital PDFs

AA-ND79A-TE

December 1989

230 pages

Original

5.4MB

Document:	VAX Rdb VMS Guide to Using SQL Services 198912
Order Number:	AA-ND79A-TE
Revision:
Pages:	230
Original Filename:	http://bitsavers.org/pdf/dec/vax/vms/layered_product/AA-ND79A-TE_VAX_Rdb_VMS_Guide_to_Using_SQL_Services_198912.pdf

OCR Text

AA-ND79A-TE

VAXRdb/VMS
Guide to Using SQL/Services
December 1989

This manual describes how to develop application programs using the SQUServices
component of Rdb/VMS Version 3.1. It is intended for programmers who are familiar
with the dynamic SQL interface to the VAX Rdb/VMS relational database management
system.

Revision/Update Information:

This is a new manual.

Operating System:

VMS

Software Version:

VAX Rdb/VMS Version 3.1

digital equipment corporation
maynard, massachusetts

The information in this document is subject to change without notice and should
not be construed as a commitment by Digital Equipment Corporation. Digital
Equipment Corporation assumes no responsibility for any errors that may appear
in this document.
The soft.ware described in this document is furnished under a license and may be used
or copied only in accordance with the terms of such license.
No responsibility is assumed for the use or reliability of software on equipment that is
not supplied by Digital Equipment Corporation or its affiliated companies.
Restricted Rights: Use, duplication, or disclosure by the U.S. Government is subject to
restrictions as set forth in subparagraph (c)(l)(ii) of the Rights in Technical Data and
Computer Soft.ware clause at DFARS 252.227-7013.
© Digital Equipment Corporation 1989.

All Rights Reserved.
Printed in U. S.A
The Reader's Comments forms at the end of this document request your critical
evaluation to assist in preparing future documentation.
The following are trademarks of Digital Equipment Corporation:
ACMS
ALL-IN-1
DATATRIEVE
DEC
DEC/CMS
DEC/MMS
DECforms
DECintact
DECmate
DECnet
DECUS
DECwindows
DECwriter
DIBOL

MASSBUS
MicroVAX
PDP
P/OS
Professional
Rainbow
RALLY
Rdb/ELN
Rdb/VMS
ReGIS
RSTS
RSX
RT
TDMS

ULTRIX
UNIBUS
VAX
VAXCDD
VAXFMS
VAXcluster
VAXELN
VAXstation
VIDA
VMS
VT
Work Processor

~nmnama™

MS-DOS is a trademark of Microsoft. Corporation.
dBASE is a registered trademark of Ashton-Tate Corporation.
dBASE IV is a trademark of Ashton-Tate Corporation.

This document was prepared using VAX DOCUMENT, Version 1.2

Contents
Preface.....................................................

l Introduction
2 Dynamic SQL
2.1
2.1.1
2.1.2
2.2

2.2.l
2.2.2
2.2.3
2.2.4

2.2.5

Overview of Dynamic SQL Statements .................... .
Execution Statements .............................. .
Result Table Statements ............................ .
Using Dynamic SQL .................................. .
Parameter Markers ................................ .
SELECT Statements ............................... .
Unknown Statements ............................... .
The SQL Descriptor Area ............................ .
The SQL Communications Area ....................... .

2-1
2-2
2-2
2-3
2-5
2-5
2-6
2-6

2-7

3 Overview of Routines and Data Structures
3.1
3.1.1
3.1.2
3.1.3
3.1.4

3.2

Overview of API Routines .............................. .
Association Routines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
SQL Statement Routines ............................ .
Result Table Routines .............................. .
Utility Routines ................................... .
Overview of Data Structures ............................ .

3-1
3-1
3-2
3-2
3-3
3-3

iii

4 Programming Guidelines
4.1
4.1. l
4.1.2
4.1.3
4.2
4.2.l

Building SQL/Services Application Programs ............... .
Building Applications on the VMS Operating System ...... .
Building Applications on the MS-DOS Operating System ... .
Building Applications on the ULTRIX Operating System ... .
Sample Application: SQLSRV$DYNAMIC .................. .
Building the Sample Application on the VMS Operating
System .......................................... .
4.2.2
Building the Sample Application on the MS-DOS Operating
System .......................................... .
4.2.3
Building the Sample Application on the ULTRIX Operating
System .......................................... .
4.2.4
Running the Sample Application ...................... .
4.2.5
Sample Program Structure .......................... .
4.2.6
The Driver Modlile ................................. .
4.2.7
Creating and Releasing an Association ................. .
4.2.7. l
Passing the Association Identification Variable ........ .
4.2.8
Processing the Dynamic SQL Statement ................ .
4.2.8.l
Declaring and Allocating SQLDA Structures .......... .
4.2.8.2
Testing for Parameter Markers .................... .
4.2.8.3
Allocating Indicator and Data Variables ............. .
4.2.8.4
Processing Parameter Markers .................... .
4.2.8.5
Executing Non-SELECT Statements ................ .
4.2.8.6
Testing for SELECT Statements ................... .
4.2.8.7
Processing a Result Table ........................ .
4.2.8.8
Releasing Prepared Statements .................... .
4.2.8.9
Error Handling ................................ .
4.3
Performance Enhancements ............................. .
4.3.l
Batched Execution ................................. .
4.3.2
Fetching Multiple Rows ............................. .
4.4
Filtering Result Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4.4.l
Elements of Filter Expressions ....................... .
4.4.2
Constants ........................................ .
4.4.3
Placeholders . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4.4.4
Mathematical Operators ............................ .
4.4.5
Relational Operators ............................... .
4.4.6
Logical Operators .................................. .
4.4.7
String Operators .................................. .
4.4.8
Precedence of Opera tors ............................. .
4.5
Execution Logging .................................... .
4.5.l
Association Logging ................................ .
4.5.2
Routine Logging ................................... .
4.5.3
Message Protocol Logging ........................... .

4-1
4-1
4-2
4-2
4-3
4-3
4-3
4-4
4-4
4-5
4-6
4-7
4-7
4-8
4-10
4-12
4-13
4-13
4-15
4-15
4-16
4-18
4-18
4-19
4-20
4-20
4-21
4-21
4-22
4-22
4-23
4-23
4-24
4-24
4-24
4-25
4-25
4-26
4-28

5 Data Types and Environment Variables
5.1
5.1. l

5.1.2
5.1.3
5.1.4
5.2
5.2.l
5.2.2
5.2.3

Data fypes .· ......................................... .
SQLSRV_ASCII_STRING ........................... .
SQLSRV_GENERALIZED_NUMBER .................. .
SQLSRV_GENERALIZED_DATE ..................... .
SQLSRV_VARCIIAR ................................ .
Environment Variables ................................. .
SQLSRV_ENV_DATE .............................. .
SQLSRV_ENV_CENTURY ........................... .
SQLSRV_ENV_SET_EXACT ......................... .

5-1

5-2
5-2
5-2
5-3
5-3
5-3
5-3
5-4

6 API Routines
6.1
Documentation Format
6.1. l
Routine Name .................................... .
p.1.2
Return Values .................................... .
6.1.3
VAX Format Section ................................. .
6.1.4
C Format Section .................................. .
6.1.5
Parameters Section .........•.......................
6.1.5. l
Data fype Entry ............................... .
6.1.5.2
Access Entry .................................. .
6.1.5.3
Mechanism Entry ............................... .
6.2
sqlsrv_abort-Disconnect Association ...................... .
6.3
sqlsrv_allocate_sqlda_data-Allocate Variables .............. .
6.4
sqlsrv_associate-Create Client/Server Association ........... .
6.5
sqlsrv_close_cursor-Release Result Table .................. .
6.6
sqlsrv_execute-Execute Prepared Statement ............... .
6.7
sqlsrv_execute_immediate-Prepare and Execute Statement .... .
6.8

6.9
6.10
6.11

6.12
6.13
6.14
6.15
6.16
6.17

sqlsrv_fetch-Get Row from Result Table .................. .
sqlsrv_fetch_many-Get Multiple Rows from Result Table ..... .
sqlsrv_free_sqlda_data-Release Variables ................. .
sqlsrv_get_environment-Return Environment Variable Values ..
sqlsrv_open_cursor-Create Result Table ................... .
sqlsrv_prepare-Compile Statement and Initialize Structures ... .
sqlsrv_release-Release Client/Server Association ............ .
sqlsrv_release_statement-Release Statement Resources ...... .
sqlsrv_set_environment-Set Environment Variable Values .... .
sqlsrv_set_filter-Define Filter for Result Table .............. .

6-1
6-2
6-2
6-3
6-3
6-4
6-4
6-5
6-5
6-6
6-7
6-9
6-13
6-15
6-19
6-21
6-24
6-27
6-29
6-31
6-33
6-37
6-39
6-41
6-43

7 Data Structures
7.1
7.2
7.3
7.4
7 .5
7.6
7. 7

Documentation Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
ASSOCIATE_STR-Association Structure...................
SQLCA-SQL Communications Area.......................
SQLERRD-Part of SQLCA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
SQLDA-SQL Descriptor Area. . . . . . . . . . . . . . . . . . . . . . . . . . . .
SQLVAR-Parameter Marker or Select List Item . . . . . . . . . . . . .
SQLSRV_ENV_STR-Environment Variable Structure . . . . . . . . .

7-1
7-3
7-6
7-9
7-11
7-13
7-16

A Filter Expression Functions
A. l
A.2
A.3
A.4
A.5
A.6
A.7
A.8
A.9
A.10
A.11
A.12
A.13
A.14
A.15
A.16
A.17
A.18
A.19
A.20
A.21
A.22
A.23
A.24
A.25
A.26
A.27
A.28
A.29
A.30
A.31
A.32
A.33

ABS .......................... ······················
ACOS .............................................. .
ASC ............................................ · · · ·
ASIN .............................................. .
AT ................................................ .
ATAN .............................................. .
ATN2 .............................................. .
CDOW ............................................. .
CEILING ........................................... .
CHR ............................................... .
CMONTH ........................................... .

cos ............................................... .

CTOD .............................................. .
DAY ............................................... .
DIFFERENCE ....................................... .
DMY .............................................. .
DOW .............................................. .
DTOC .............................................. .
DTOR .............................................. .
DTOS .............................................. .
EXP ............................................... .
FLOOR ........................................... · ·
INT ................................. ·.·············
LEFT .............................................. .
LEN ............................................... .
LOG ............................................... .
LOGlO ............................................. .
LOWER ............................................ .
LTRIM ............................................. .
MDY .............................................. .
MOD .............................................. .
MONTH ............................................ .
Pl ................................................. .

A-2
A-3
A-5
A-6
A-7
A-8
A-9
A-11
A-12
A-13
A-14
A-15
A-16
A-17
A-18
A-19
A-21
A-22
A-23
A-24
A-25
A-26
A-27
A-28
A-29
A-30
A-31
A-32
A-33
A-34
A-35
A-36
A-37

A.34
A.35
A.36
A.37
A.38
A.39
A.40
A.41
A.42
A.43
A.44
A.45
A.46
A.47
A.48
A.49
A.50
A.51
A.52
A.53
A.54

RAND . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
REPLICATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
RIGHT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
ROUND . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
RTOD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
RTRIM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
SIGN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
SIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
SOUNDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
SPACE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
SQRT...............................................
STR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
STUFF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
SUBSTR.............................................
TAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
TIME . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
TRIM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
UPPER. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
USER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
VAL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
YEAR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

A-38
A-39
A-40
A-41
A-43
A-44
A-45
A-46
A-48
A-50
A-51
A-52
A-54
A-56
A-57
A-58
A-59
A-60
A-61
A-62
A-63

B SQL/Services Sample Application
C Sample Log Files
Index
Examples
B-1
B-2

C-1
C-2
C-3

The SQLSRV$DRIVER.C Module . . . . . . . . . . . . . . . . . . . . . . . . .
The SQLSRV$DYNAMIC.C Module . . . . . . . . . . . . . . . . . . . . . . . .
Sample Association Level Log . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Sample Routine Level Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Sample Message Protocol Level Log........................

B-1
B-4

C- l
C-2
C-7

vii

Figures
1-1
4-1
4-2

SQL/Services Architecture .............................. .
Statement Execution Flow .............................. .
Placeholders in Filter Expressions . . . . . . . . . . . . . . . . . . . . . . . . .

1-2
4-10
4-23

SQL Statements That Can Be Dynamically Executed . . . . . . . . . .
SQL Statements That Cannot Be Dynamically Executed . . . . . . . .
MS-DOS API Libraries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Settings for the SQLSRV_ENV_DATE Variable . . . . . . . . . . . . . . .
Settings for the SQLSRV_ENV_CENTURY Variable . . . . . . . . . . .
Settings for the SQLSRV_ENV_SET_EXACT Variable . . . . . . . . .
Sections in the Routine Template. . . . . . . . . . . . . . . . . . . . . . . . . .
API Return Values .................................. ; . .
API Parameter Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Values of the execute_flag Parameter. . . . . . . . . . . . . . . . . . . . . . .
Sections in the Data Structure Template . . . . . . . . . . . . . . . . . . . .
Error Code Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Values Placed in the SQLCA.SQLERRD[2] Field . . . . . . . . . . . . . .

2-4
2-4
4-2
5-1
5-3
5-4
5-4
6-1
6-3
6-4
6-16
7-1
7-9
7-10

Tables
2-1
2-2
4-1
5-1
5-2
5-3
5-4
6-1
6-2
6-3
6-4
7-1
7-2
7-3

(
viii

Preface
VAX RdbNMS, often referred to as RdbNMS in this manual, is a general
purpose database management system based on the relational data model.
SQL/Services is a client/server component of RdbNMS. It allows application
programs running on various types of computers to access DIGITAL Standard
Relational Interface (DSRI) compliant databases on other computers. For
example, an application program running on an MS-DOS personal computer (a
client) can access an RdbNMS database on a VAX computer (a server). This
manual describes how to develop SQL/Services application programs.

Intended Audience
This manual is intended for experienced applications programmers. 'lb use
SQL/Services, you should be familiar with:
• The RdbNMS SQL interface (an implementation of the industry-standard
structured query language)
• A high-level programming language (preferably C) that supports pointer
variables
If you are unfamiliar with SQL, it is recommended that you read the VAX
RdbNMS Guide to Using SQL and the VAX Rdb/VMS SQL Reference Manual
before attempting to write SQL/Services application programs.

Operating System Information
Information about the operating systems and related software that are
compatible with this version of RdbNMS is included in the RdbNMS media
kit.
For information on the compatibility of other software products with this
version of Rdb/VMS, refer to the System Support Addendum (SSA) that comes
with the Software Product Description (SPD). You can use the SPD/SSA to
verify which versions of your operating system are compatible with this version
ofRdbNMS.
Contact your Digital representative if you have questions about the
compatibility of other software products with this version of Rdb/VMS.

Structure
This manual has seven chapters and three appendixes.
Chapter 1

Introduces SQUServices

Chapter 2

Is a condensed discussion of dynamic SQL for those unfamiliar with
it

Chapter 3

Is an overview of the routines and data structures that make up
SQUServices

Chapter 4

Provides guidelines for application development, including a detailed
description of the sample application

Chapter 5

Is a detailed reference description of the SQilServices data types
and environment variables

Chapter 6

Is a detailed reference description of the SQilServices API routines

Chapter 7

Is a detailed reference description of the SQilServices data
structures

Appendix A

Describes the functions that can be used in filter expressions

Appendix B

Contains listings of the sample application

Appendix C

Contains listings of the log files produced by the Installation
Verification Procedure

SQUServices error message descriptions and user actions are provided in the
file SYS$HELP:SQLSRV$MSG.DOC.

Related Manuals
The following manuals contain information related to SQUServices.

• VAX Rdb/VMS Guide to Using SQL
Introduces the Rdb/VMS SQL (structured query language) interface, and
shows how to retrieve, store, and update data interactively and through
application programs.
• VAX Rdb/VMS SQL Reference Manual
Provides reference material and a complete description of the statements,
the interactive, dynamic, and module language interfaces, and the syntax
for SQL, the structured query language interface for Rdb/VMS.
• VAX Rdb/VMS Release Notes
Describes new features, problems and problems fixed, restrictions, and
other information related to the current release of Rdb/VMS. Contains
information about SQL and other Rdb/VMS interfaces and utilities.
• VAX Rdb /VMS Installation Guide
Describes how to install Rdb/VMS.
• VAX Rdb /VMS Introduction and Master Index
Introduces Rdb/VMS and explains major terms and concepts. Includes a
glossary, a directory of Rdb/VMS documentation, and a master index that
combines entries from all the Rdb/VMS manuals.

Conventions
This section explains the conventions used in this manual:
A vertical ellipsis in an example means that information not directly
related to the example has been omitted.

Color

In printed manuals, color in examples shows user input.

[]

Brackets enclose optional clauses from which you can choose one or
none.

The dollar sign represents the DIGITAL Command Language prompt.
This symbol indicates ~hat the DCL interpreter is ready for input.

The right angle bracket represents the MS-DOS command prompt. This
symbol indicates that the MS-DOS command language interpreter is
ready for input.

The percent sign represents the ULTRIX shell prompt. This symbol
indicates that the ULTRIX shell is ready for input.

e, f, t

Index entries in the printed manual may have a lowercase e, f, or t
following the page number; the e, f, or t is a reference to the example,
figure, or table, respectively, on that page.

References to Products
The SQUServices documentation to which this document belongs often refers
to VAX RdbNMS software as RdbNMS.

xii

1
Introduction

SQL/Services is a clientiserver component of RdbNMS. It allows application
programs running on various types of computers to access DIGITAL Standard
Relational Interface (DSRI) compliant databases on other computers, as shown
in Figure 1-1. For example, an application program running on an MS-DOS
personal computer (a client) can access an RdbNMS database on a VAX
computer (a server).
Application programs access SQL/Services through an Application
Programming Interface (API), which is a set of callable routines that
perform functions similar to dynamic SQL. In other words, an SQL/Services
application program executes SQL statements at run time. The SQL
statements can be embedded in the source code or can be formulated at run
time. The SQL statement syntax accepted by SQL/Services is identical to that
accepted by dynamic SQL.
The SQL/Services API communicates by means of DECnet with a server
process on the VAX system on which the target database resides. The server
software is present on all VAX systems running RdbNMS Version 3.1 or
higher.
The clientlserver association runs in the context of a user account. Thus, the
application program must provide a valid account name and password on the
server system.
The client/server association uses a message-based protocol that is virtually
transparent to the application program. Other than ensuring that DECnet is
installed on both the client and server system and allocating message buffers,
you need no knowledge of networking to develop SQL/Services applications.

Introduction

1-1

Figure 1-1

SQL/Services Architecture
Client

Server

SQUServices
Application Program

SQUServices
Server Process

•

: AP/ Call Interface

: Protocol

SQUServices API

•

_ DECnet

Protocol

•

DECnet

..
~

DECnet L...
Connection
1

: Dynamic

SOL

•
!DsR1

!DsR1

•

RdbNMS

1·

VIDA

Cc:>~
ZK-0996A-GE

1-2 Introduction

2
Dynamic SQL
This chapter provides a condensed discussion of dynamic SQL and discusses
the factors to consider when using it. If you are already familiar with dynamic
SQL, you may want to skip to Chapter 3, which provides an overview of
SQL/Services and how it differs from dynamic SQL.

Dynamic SQL allows application programs to formulate and execute SQL
statements at run time. It consists of:
• Statements
A set of SQL statements with which you can write applications using
either the SQL precompiler or the module language processor
•

])ata Structures
A set of data structures that provides a way for dynamic SQL and
application programs to exchange data and metadata (data about data)

Applications that use dynamic SQL might, for example, translate interactive
user input into SQL statements, or open, read, and execute :files containing
SQL statements. The SQL/Services server is itself a dynamic SQL application.

2. l

Overview of Dynamic SQL Statements
The dynamic SQL statements are summarized in Section 2.1.1 and
Section 2.1.2, which group the statements according to function. For each
dynamic SQL statement, there is an SQUServices API routine that performs
the same function. (Some API routines combine the functions of two dynamic
SQL statements.)

Dynamic SQL 2-1

2. l. 1 Execution Statements
Execution statements prepare and execute SQL statements and release
prepared SQL statement resources.
•

PREPARE
Checks the SQL statement to be dynamically executed for errors and
assigns a user-defined name to it. That name is referred to in DESCRIBE,
EXECUTE, and DECLARE CURSOR statements.

•

DESCRIBE
Checks a prepared SQL statement for the existence of select list items
or parameter markers (as explained in Section 2.2). If either is present,
DESCRIBE stores information about it in the SQL Descriptor Area
(SQLDA). (Using the SELECT LIST clause of the PREPARE statement
is equivalent to using the DESCRIBE statement with the SELECT LIST
argument.)

• EXECUTE
Executes a previously prepared SQL statement other than SELECT.
• EXECUTE IMMEDIATE
Prepares and executes in one step any SQL statement (other than
SELECT) that does not contain parameter markers.
• RELEASE
Releases all resources used by a prepared SQL statement and prevents the
prepared statement from executing again.
Except for the DESCRIBE statement, each of these dynamic SQL statements
has an equivalent SQL/Services routine. In SQL/Services, the DESCRIBE and
PREPARE statements are combined in a single routine, as shown in Table 2--2.

2.1. 2

Result Table Statements

Result table statements allow your program to declare a cursor, open a cursor,
fetch data from an open cursor, and close an open cursor.
• DECLARE CURSOR
Declares a cursor for a prepared SELECT statement.
• OPEN
Opens a cursor declared for a prepared SELECT statement.
• FETCH
Retrieves values from a cursor declared for a prepared SELECT statement.

2-2

Dynamic SQL

(
I

•

CLOSE
Closes a cursor.

Except for the DECLARE CURSOR statement, each of these dynamic SQL
statements has an equivalent SQL/Services routine. In SQUServices, the
DECLARE CURSOR and OPEN CURSOR statements are combined in a single
routine, as shown in Table 2-2.
·

2.2 Using Dynamic SQL
In its simplest form, dynamic SQL consists of passing complete SQL
statements as string constants or variables to the EXECUTE IMMEDIATE
statement. This simple approach may be sufficient for some applications.
However, when you want to dynamically execute the same SQL statement
more than once, the EXECUTE IMMEDIATE approach is inefficient because it
does not save any context. A more efficient approach is to call the PREPARE
statement once, then call the EXECUTE statement as many times as needed.
As before, this approach may be sufficient for some applications.
However, to write applications that deal with the entire spectrum of SQL
statements, you must also consider the following restrictions:

• Not all SQL statements can be dynamically executed. The statements
that can be dynamically executed are listed in Table 2-1. Statements
that are valid only in interactive SQL cannot be dynamically executed.
The statements that are valid in precompiled and module language SQL
but cannot be dynamically executed are listed in Table 2-2. Most of the
statements in Table 2-2 are statements that make up dynamic SQL itself.
• Dynamically executed SELECT, INSERT, UPDATE, and DELETE
statements can contain parameters. The parameters can be constants but
they cannot be host variables. Th pass the value of a variable, it must be
represented by a parameter marker.

• You cannot use parameter markers when using the EXECUTE IMMEDIATE
statement; they are valid only when you are using the PREPARE and
EXECUTE statements.
• Because it generates output, you cannot pass a SELECT statement to the
EXECUTE or EXECUTE IMMEDIATE statement. Instead, you call the
PREPARE statement followed by DECLARE CURSOR, OPEN, FETCH,
and so forth.
Section 2.2.1 describes how to dynamically execute statements that contain
parameter markers. Section 2.2.2 describes how to access the data returned by
SELECT statements. Section 2.2.3 describes how to handle statements about
which the program has no information.

Dynamic SQL 2-3

Table 2-1

SQL Statements That Can Be Dynamically Executed

Statement

Parameter
Markers
Allowed?

Select
List
Items?

SELECT

Yes

PREPARE
DESCRIBE (optional)
DECLARE CURSOR
OPEN
FETCH
CLOSE
RELEASE (optional)

INSERT
UPDATE
DELETE

Yes

PREPARE
DESCRIBE (optional)
EXECUTE
RELEASE (optional)
EXECUTE IMMEDIATE (if
no parameter markers)

CREATE
ALTER
DROP
DECLARE SCHEMA
DECLARE TRANSACTION
SET TRANSACTION
COMMIT
ROLLBACK
GRANT
REVOKE
COMMENT ON

PREPARE
EXECUTE
RELEASE (optional)
EXECUTE IMMEDIATE

Table 2-2

Associated Dynamic SQL
Statements

SQL Statements That Cannot Be Dynamically Executed

SQL Statement

Related SQL/Servlces Routine

BEGIN DECLARE

none

sqlsrv_close_cursor

DECLARE CURSOR

sqlsrv_open_cursor (implicit in)

DECLARE STATEMENT

none

DECLARE TABLE

none

DESCRIBE

sqlsrv_prepare (implicit in)

END DECLARE

none

EXECUTE

sqlsrv_execute
(continued on next page)

2-4

Dynamic SQL

Table 2-2 (Cont.)

SQL Statements That Cannot Be Dynamically Executed

SQL Statement

Related SQL/Servlces Routine

EXECUTE IMMEDIATE

sqlsrv_execute_i:rn:rnediate

FETCH

sqlsrv_fetch, sqlsrv_fetch_:rnany

INCLUDE

none

OPEN

sqlsrv_open_cursor

PREPARE

sqlsrv_prepare

RELEASE

sqlsrv_release_state:rnent

SELECT ... INTO

none

(singleton select)

WHENEVER

none

2.2. 1 Parameter Markers
Parameter markers represent variables in dynamically executed SQL SELECT,
INSERT, UPDATE, and DELETE statements. Question marks (?) embedded
in the statement string denote parameters that are to be replaced when the
statement is dynamically executed. An example of an SQL statement with
parameter markers is:
INSERT INTO EMPLOYEES
(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, CITY)
VALUES ( ?, ?, ?, ? );

The mechanism for mapping parameter markers to variables in application
programs is a data structure called the SQLDA (see Section 2.2.4 and
Section 7 .5). The DESCRIBE statement writes information about parameter
markers into an SQLDA structure. Your program examines the SQLDA
structure, allocates a data variable for each parameter marker, obtains values
for the data variables, and writes the addresses of those variables into the
SQLDA, before dynamically executing the SQL statement. Alternatively, your
program can initialize the SQLDA itself, instead of calling the DESCRIBE
statement.

2.2.2

SELECT Statements

Programs that dynamically execute SELECT statements must declare a cursor
to receive the result table and must allocate memory for each select list item in
the SELECT statement. After the cursor is opened, FETCH statements return
values for rows of the result table.
As with parameter markers, the mechanism for mapping select list items to

host variables is a data structure called the SQLDA (see Section 2.2.4 and
Section 7.5). The DESCRIBE and PREPARE statements both write select list
information into the SQLDA.

Dynamic SQL 2-5

If the SELECT statement contains parameter markers, the program must also
set up host variables for the parameter markers and assign values to them.

2.2.3

Unknown Statements

It is possible to dynamically execute SQL statements about which the program
has no prior information. Such unknown statements may contain parameter
markers or select list items (or both). The program can use the PREPARE
and DESCRIBE statements to obtain two separate SQLDA structures
containing information about the numbers and data types of select list
items and parameter markers. Then the program allocates data variables
as appropriate and writes the addresses of those variables into the SQLDA
structures before executing the unknown statement.

2.2.4 The SQL Descriptor Area
SQL provides a data structure called the SQL Descriptor Area (SQLDA) that
provides a means for programs to communicate with SQL about parameter
markers and select list items. To use the SQLDA, host languages must support
pointer variables that provide indirect access to memory by storing the address
of data instead of directly storing data in the variable. Declarations for the
SQLDA structure in various languages can be found in include files that are
provided with SQL.
When SQL processes a DESCRIBE statement, it writes information about
select list items (for a DESCRIBE ... SELECT LIST statement) or parameter
markers (for a DESCRIBE ... MARKERS statement) of a prepared statement
into an SQLDA.
The host language program examines the SQLDA to determine how many
select list items (DESCRIBE ... SELECT LIST) or parameter markers
(DESCRIBE ... MARKERS) are present and the data type of each. The
program must provide memory (static or dynamic) for each parameter marker
or select list item, and write the address of each memory location into the
SQLDA.
For parameter markers, the program writes values into the SQLDA before
dynamically executing the SQL statement. For select list items, the program
reads the data written into the SQLDA by subsequent FETCH statements.
Section 7.5 describes the SQLDA in detail. In addition, the VAX Rdb/VMS
SQL Reference Manual contains an appendix on the SQLDA and a section on
the DESCRIBE statement that discusses the MARKERS and SELECT LIST
clauses of the DESCRIBE statement in more detail.

2-6

Dynamic SQL

2.2.5 The SQL Communications Area
The SQL Communications Area (SQLCA) is a collection of parameters
that SQL uses to provide information about the execution of SQL statements
to application programs. SQL updates the contents of the SQLCA after
completion of every executable SQL statement. The only fields of interest in
the SQLCA are the SQLCODE field and the third element of the SQLERRD
array.
The SQLCODE field shows whether a statement was successful, and for some
errors, the particular error when a state~ent is not successful.
SQL puts a value in the third element of the SQLERRD array after successful
execution of the following statements:
• INSERT: the number of rows stored by the statement
• UPDATE: the number of rows modified by the statement
• DELETE: the number of rows deleted by the statement
• FETCH: the number of the row on which the cursor is currently positioned
• OPEN: zero
• SELECT: the number of rows in the result table formed by the SELECT
statement (Note: SQLERRD is not updated for dynamic SELECT
statements)
Otherwise, the value of SQLERRD is undefined.
Section 7.3 describes the SQLCA in detail. In addition, the VAX Rdb/VMS
SQL Reference Manual contains an appendix on the SQLCA.

Dynamic SQL 2-7

(

3
Overview of Routines and Data Structures

This chapter provides overviews of the SQL/Services routines and data
structures.

3. 1 Overview of API Routines
The SQUServices Application Programming Interface (API) is a set of callable
routines that the client uses to access SQL/Services functions. The API
routines are grouped according to function and summarized in Section 3.1.1
through Section 3.1.4.

3. l . l

Association Routines

Association routines create and terminate client/server associations and control
the association environment (context).
•

sqlsrv_associate
Creates a client/server association. Makes the remote connection to the
server process and negotiates association values. For more information, see
Section 6.4.

•

sqlsrv_release
Terminates a client/server association in an orderly fashion. Sends
a message to the server requesting termination of the association,
disconnects the network link, and releases all client resources related
to the association. For more information, see Section 6.14.

•

sqlsrv_abort
Terminates a client/server association immediately. Disconnects from the
server and releases all client resources related to the association. For more
information, see Section 6.2.

Overview of Routines and Data Structures

3-1

• sqlsrv_set_environment
Sets new values for environment variables on the server. Environment
variables control date, time, and nwneric output formats, and stringmatching modes. For more information, see Section 6.16.
• sqlsrv_get_environment
Gets current values of environment variables. For more information, see
Section 6.11.

3.1.2

SQL Statement Routines

SQL statement routines prepare and execute SQL statements, and release
prepared SQL statement resources. These routines map directly to the
dynamic SQL interface.
• sqlsrv_prepare
Prepares (compiles) a dynamic SQL statement. It returns a statement
identifier and SQLDA metadata information (fields that describe parameter
markers and select list items). This routine maps to the dynamic SQL
PREPARE and DESCRIBE statements. For more information, see
Section 6.13.
• sqlsrv_execute
Executes a prepared SQL statement. This routine maps to the dynamic
SQL EXECUTE statement. For more information, see Section 6.6.
• sqlsrv_execute_immediate
Prepares and executes an SQL statement. This routine cannot be used if
the SQL statement contains parameter markers. This routine maps to the
dynamic SQL EXECUTE IMMEDIATE statement. For more information,
see Section 6.7.
• sqlsrv_release_statement
Releases client and server statement resources associated with a prepared
statement. This routine maps to the dynamic SQL RELEASE statement.
For more information, see Section 6.15.

3. 1.3

Result Table Routines

Result table routines allow the caller to fetch data from the server by providing
calls to open a cursor, associate a filter expression with a cursor, fetch from an
open cursor, and close an open cursor.
• sqlsrv_open_cursor
Opens a cursor by associating a cursor name with a prepared statement
identifier. The cursor name is used in each reference to the cursor. An SQL
DECLARE CURSOR statement is implicit within the sqlsrv_open_cursor
call. For more information, see Section 6.12.
·

3-2

Overview of Routines and Data Structures

• sqlsrv_set_filter
Associates a Boolean expression with a cursor to filter out unwanted
rows from the result table before they are sent to the client. For more
information, see Section 6.17.
• sqlsrv_fetch
Fetches one row of data from an open cursor. Can be used to fetch rows
of information from within an sqlsrv_fetch_many context. For more
information, see Section 6.8.
• sqlsrv_fetch_many
Requests that multiple rows of data be fetched and transmitted to the
client in one message. For more information, see Section 6.9.
•

sqlsrv_close_cursor
Closes an open cursor. For more information, see Section 6.5.

3. 1.4

Utility Routines

Utility routines provide local services to the caller.
•

sqlsrv_allocate_sqlda_data
Allocates memory for the SQLDA data buffer and indicator variable fields.
For more information, see Section 6.3.

•

sqlsrv_free_sqlda_data
Frees memory for the SQLDA data buffer and indicator variable fields. For
more information, see Section 6.10.

3.2 Overview of Data Structures
The API routines use the following data structures.
• ASSOCIATE_STR
This structure is passed as a parameter to sqlsrv_associate to enable or
disable various API functions. The sqlsrv_associate routine opens the
communications link between client and server and creates an association
context. For more information, see Section 7 .2.
• SQLDA
The SQLDA (SQL Descriptor Area) is used to exchange database metadata
and data for parameter markers (input) and select lists (output).
Parameter markers are required when the SQL statement refers to
data not defined at compile time. The SQL/Services SQLDA is identical to
that used by dynamic SQL. For more information, see Section 2.2.4 and
Section 7.5.

Overview of Routines and Data Structures 3-3

• SQLCA
The SQLCA (SQL Communications Area) is used to store error messages
and SQL statement information returned by SQUServices. When an API
routine returns a non-zero value, the SQLCA contains additional error
information. For more information, see Section 7.3.
• SQLSRV_ENV_STR
This structure provides a mechanism for requesting and receiving
environment variable values. An array of these structures is passed
to the API with one element for each environment variable. For more
information, see Section 7.7.

3-4

Overview of Routines and Data Structures

4
Programming Guidelines
This chapter describes how to develop application programs using
SQL/Services.

4. 1 Building SQL/Services Application Programs
The process of building SQUServices application programs consists of these
steps:
Compile your code using the following Hnclude compiler directives:
#include <sqlsrvda.h>
#include <sqlsrvca.h>
#include <sqlsrv.h>

/* SQLDA */
/* SQLCA */
/* other structures */

On most operating systems, include files are kept in a standard location,
indicated in C by placing angle brackets around the name of the file. If
these directives do not work on your system, ask the person who installed
the SQL/Services API where the include files are located.
2

Link your object module with the SQUServices API. Linking procedures
are system dependent and are thus discussed in separate sections.

4. 1. 1 Building Applications on the VMS Operating System
The VMS include files are installed in SYS$LIBRARY. Their names are
SQLSRVCA.H, SQLSRVDA.H, and SQLSRV.H.
1b link your program, enter the command:
$ LINK object.OBJ,SYS$LIBRARY:options_file/OPT

Replace object with the name of your object module and options_file with either
SQLSRV$API (D_float) or SQLSRV$APIG (G_float) depending on how you
compiled your source code. See the Introduction to VMS System Routines for
more information about VMS data types.

Programming Guldellnes 4-1

4.1.2

Building Applications on the MS-DOS Operating System

The MS-DOS include :files are installed in a directory created by the installer;
for example, C:\SQLSRV. Their names are SQLSRVCA.H, SQLSRVDA.H, and
SQLSRV.H.
'lb link your program, enter the command:
>LINK object,/STACK=n,,apilib+decnetlib+libc/NOD/NOE

Replace object with the name of your object module, n with the desired stack
size (1000 bytes plus whatever is required by your application), apilib with one
of the libraries shown in Table 4-1, decnetlib with the name of the DECnetDOS Programming Interface Library, and libc with the name of the C run-time
support library.
Table 4-1

MS-DOS API Libraries

Library

Memory Model

SQSAPIL.LIB

large

SQSAPIM.LIB

medium

SQSAPIS.LIB

small

Note The DECnet-DOS V2.1 Programming Interface Library contains a reference

to the undefined symbol dnet_askJor_password. Ignore any linker error
messages about this symbol.
You may find it useful to examine the procedures that build the MS-DOS API
Installation Verification Procedure (SQSIVP.BAT and SQSIVP.MAK) and the
sample application SQLSRV$DYNAMIC (see Section 4.2.2).

4. 1.3 Building Applications on the ULTRIX Operating System
The ULTRIX include files are installed in /usr/include or (if the installer did
not have superuser privileges) in a directory created by the installer. Their
names are sqlsrvca.h, sqlsrvda.h, and sqlsrv.h.
By default, the ULTRIX C compiler compiles and links your program in one
command:
% cc file sqsapi.a -o name
% chmod +x name

Replace file with the name of your source file and name with the name you
wish for the executable file.
You may :find it useful to examine the make file that builds the ULTRIX API
Installation Verification Procedure (sqsivpu.mak) and the make file that builds
the sample application SQLSRV$DYNAMIC (see Section 4.2.3).

4-2

Programming Guidelines

4.2 Sample Application: SQLSRV$DYNAMIC
This section describes a sample program written in C that illustrates a general
type of SQUServices application. The sample, SQLSRV$DYNAMIC, was
derived from SQL$DYNAMIC, the dynamic SQL sample program in the
VAX Rdb/VMS Guide to Using SQL, which is written in Ada and uses the
SQL module processor. The conversion involved recoding in portable C and
converting the SQL module language procedures to SQUServices API routine
calls. Complete source listings are provided in Example B-1 and Example B-2.
SQLSRV$DYNAMIC creates an association, accepts SQL statements from
the terminal, and executes them by calling routines in the SQUServices
API. In other words, the program resembles in some respects a portable
implementation of interactive SQL.
Like interactive SQL, SQLSRV$DYNAMIC recognizes the semicolon ( ; ) as
a line terminator and thus accepts multiple-line statements. Input lines
beginning with an exclamation point (!)are considered comments and are not
executed.
For input statements that contain parameter markers, the program describes
the data required and prompts for user input. For SELECT statements, the
program creates a cursor, and fetches and displays each row in the result table.
The source code for SQLSRV$DYNAMIC is included with the SQUServices
distribution so you can compile, link, and run it on your own system.

4.2.1

Building the Sample Application on the VMS Operating
System

The source code for SQLSRV$DYNAMIC is available on line in the directory
SYS$EXAMPLES. 'lb compile, link, and run SQLSRV$DYNAMIC, enter the
following commands:
$ cc sys$examples:sqlsrv$driver,sys$examples:sqlsrv$dynamic
$ link/exe=sqlsrv$dynamic sqlsrv$driver,sqlsrv$dynamic $ sys$library:sqlsrv$api/opt
$ run sqlsrv$dynamic

4.2.2

Building the Sample Application on the MS-DOS
Operating System

The source code for SQLSRV$DYNAMIC is available on line in the directory in
which the MS-DOS API was installed. If you have the MAKE utility on your
system, enter the following command:
> CD C:\SQLSRV
> MAKE SQSDYN.MAK
> SQSDYN

Programming Guldellnes 4-3

Otherwise, to compile and link the sample application, follow the instructions
in Section 4.1.2. The names of the source files are SQSDRV.C and SQSDYN.C.

4.2.3

Building the Sample Application on the ULTRIX Operating
System

The source code for SQLSRV$DYNAMIC is available on line. 'lb compile, link,
and run SQLSRV$DYNAMIC, enter the following command:
% cp /usr/sqlsrv/* .
% make - f sqsdynu.mak
% sqsdynu

Replace /usr/sqlsrv with the name of the directory in which the ULTRIX API
was installed. The names of the sample application source files are sqsdrvu.c
and sqsdynu.c.

4.2.4

Running the Sample Application

When SQLSRV$DYNAMIC starts up, it prompts for the information required
to create an association with (establish a DECnet connection with the server
process on) a remote system. When the association is made, the program
prints instructions and prompts for SQL statements to execute. For example,
on the VMS operating system:
$ run sqlsrv$dynamic
VMS server node: MYNODE
VMS server account name: MYNAME
VMS server account password: MYPASSWORD
Enter any dynamically executable SQL statement,
continuing it on successive lines.
Terminate the statement with a semicolon.
Built-in commands are: [no]echo and exit.
SQL> DECLARE SCHEMA FILENAME SQL PERSONNEL;
SQL> SELECT * FROM EMPLOYEES WHERE FIRST NAME
Enter value for:
FIRST NAME
Maximum length is: 11
DATA> Norman

4-4

Programming Guldellnes

------ BEGIN RESULT TABLE -----EMPLOYEE ID
00168
LAST NAME
Nash
FIRST NAME
Norman
MIDDLE INITIAL
ADDRESS DATA 1
87 West Rd.
ADDRESS DATA 2
CITY
Meadows
STATE
NH
POSTAL CODE
03587
SEX
M
BIRTHDAY
1932102300000000
STATUS CODE
1
---------- END OF ROW ----------

----------

END OF ROW ---------EMPLOYEE ID
00245
LAST NAME
Roberts
FIRST NAME
Norman
MIDDLE INITIAL
u
ADDRESS DATA 1
162 Tenby Dr.
ADDRESS DATA 2
CITY
Chocorua
STATE
NH
POSTAL CODE
03817
SEX
M
BIRTHDAY
1949061100000000
STATUS CODE
: 1
---------- END OF ROW
------- END RESULT TABLE ------SQL> EXIT;

----------

4.2.5

Sample Program Structure

The sample application SQLSRV$DYNAMIC consists of the following modules:
• The SQLSRV$DRIVER module accepts a string from the user
(ostensibly containing a dynamic SQL statement) and passes it to the
SQLSRV$DYNAMIC module.
• The SQLSRV$DYNAMIC module processes the statement, executing nonSELECT statements and displaying result tables from SELECT statements
on the terminal.

Programming Guldellnes 4-5

4.2.6 The Driver Module
When a user runs SQLSRV$DYNAMIC, it executes the main function in the
SQLSRV$DRIVER.C module, which does the following:
• Calls a routine to create an association. Although SQLSRV$DRIVER
creates only one association, SQL/Services allows an application to have
several associations active at any given time.
• Enters a loop that inputs dynamic SQL statements and passes them to the
function execute_statement for processing.
• Calls a routine to close the association.
The implementation of the terminal input/output in SQLSRV$DRIVER is
unimportant. The module is intended to be easily replaced. It does, however,
demonstrate how to declare the variables that are "global" to a client/server
association:
char
struct SQLCA
char

*assoc_id;
sqlca str;
long_error[512];

• The variable assoc_id identifies (provides a handle for) an active
client/server association. Every SQL/Services API routine has an
association identifier in its parameter list.
Assoc_id is declared as a pointer to a character object. The choice of char
as the data type is arbitrary because SQLSRV$DYNAMIC does not allocate
the object that assoc_id points to, nor does it ever directly access that
object. When SQLSRV$DYNAMIC calls the sqlsrv_associate routine, it
passes the address of assoc_id (a pointer to a pointer). The API allocates
the object and writes its address into assoc_id.
• The variable sqlca_str is real memory that is used as the communications
area for an active client/server association. It is declared as an instance of
the structure SQLCA, which is defined in the include file SQLSRVCA.H.
When SQLSRV$DYNAMIC calls the sqlsrv_associate routine, it passes
the address of the SQLCA structure. Then, whenever an API routine
call returns a status value other than SQL_SUCCESS, the application
can examine the SQLCA structure for error information. In addition,
SQL/Services uses the SQLCA to return various types of status
information, as described in Section 7.3.
• The variable long_error is real memory that is used as an alternative error
message text buffer. The SQLCA field that is intended for error message
text is only 70 bytes, which is too short for some error messages.
Long_error is 512 bytes, which is sufficient for all possible messages. For
more information, see Section 4.2.8.9 and Section 7.2.

4-6

Programming Guldellnes

(

4.2.7

Creating and Releasing an Association

The module SQLSRV$DYNAMIC contains a function named create_association
that does the following:
• Declares the variables required for an association, including the message
protocol buffers and sizes.
·•

Gets the node name, user name, and password for the server system from
the argument vector; if any of these are missing, the create_association
function prompts the user.

• Sets up the sizes (in bytes) of the read and write message protocol buffers.
read size = 1024;
writ;_size = 1024;

/* protocol buffer size value */
/* protocol buffer size value */

Buffer size is a tradeoff between message throughput, memory usage, and
maximum number of possible simultaneous associations. Larger buffers
result in fewer messages that must be transmitted between client and
server when you use the sqlsrv_fetch_many routine to fetch multiple rows
(see Section 4.3.2) or the sqlsrv_execute routine to send multiple rows (see
Section 4.3.1). You may have to fine tune the buffer sizes to optimize your
application for a specific platform.
• Sets up the association structure. This structure is described in detail in
Section 7 .2.
associate str.CLIENT_LOG = O;
/* disable client logging.
*/
associate str.SERVER LOG= O;
/* disable server logging.
*/
associate-str.LOCAL FLAG = O;
/* this is a remote session.
*/
associate-str.MEMORY ROUTINE = NULL;
/* use default alloc routine.
*/
associate-str.FREE MEMORY ROUTINE = NULL; /* use default free routine. */
associate-str.ERRBUFLEN =-512;
associate=str.ERRBUF = long_error;
/* use alternative error string */

•

Calls the API routine sqlsrv_associate to create the association.

Passing the Association Identification Variable If you are an
experienced C programmer and are familiar with multiple levels of indirection,
you may prefer to skip this section and go to Section 4.2.8.

4.2. 7. 1

The sqlsrv_associate routine is one of two API routines (the other is
sqlsrv_prepare) that require addresses to be passed by reference. In other
words, one of the arguments (assoc_id) is the address of an address, as in the
following example.

Programming Guidelines 4-7

create_association()

(
\
char *assoc_id;

/* pointer variable internal to function */

status

/*AP! routine call */

sqlsrv_associate(

&assoc_id);

/* address of pointer variable */

When the association identifier is declared in the calling function (as in
SQLSRV$DYNAMI C), make sure not to add an extra level of indirection. In
the following example, assoc_id is declared in the main program and passed as
a parameter to a function that calls the sqlsrv_associate routine:
main ()

char

*assoc_id;

/* pointer variable */

create_association(&assoc_id); /*call with address of pointer*/

The function that calls the sqlsrv_associate routine is as follows:
create association(assoc id)
char **assoc id;
-

/* function declaration */
/* formal parameter */

{

status = sqlsrv_associate(

assoc id);
wrong--> &assoc=id);

/* AP! routine call */

/*argument contains address of pointer*/
would add an extra level of indirection*/

}

For clarity, the formal association id parameter is defined as a pointer to
a pointer. A long integer would work as well because the parameter is an
address.

4.2.8

Processing the Dynamic SQL Statement

The module SQLSRV$DYNAMIC contains a function named execute_statement
that processes the statement string passed to it by the driver module. As
shown in Figure 4-1, the execute_statement function does the following:
• Declares SQLDA pointers and other variables.
• Calls the sqlsrv_prepare routine, which prepares (compiles) the statement
and returns a statement identification variable.

4-8

Programming Guidelines

• Tests the SQLDA pointers to determine whether the statement contains
parameter markers or is a SELECT statement.
•

If the statement string contains parameter markers, allocates data and
indicator variables for the parameter marker SQLDA and calls the
get_params function to get data values from the user.

• Calls the sqlsrv_execute routine to execute the statement, unless the
statement is a SELECT. In that case, SQLSRV$DYNAMIC:
Allocates data and indicator variables for the select list SQLDA
Opens a cursor
Fetches and displays the rows in the result table
Closes the cursor
• Releases the prepared statement.
Section 4.2.8.1 through Section 4.2.8.9 explain the workings of the execute_
statement and get_params functions in more detail.

Programming Guldellnes 4-9

Figure 4-1

Statement Execution Flow

Declare
SOLDA
pointers

Prepare

Allocate
variables

Get data

Allocate
variables

Open
cursor

Fetch

row

Process
row

Close
cursor

Execute
statement

Relea9e
statement
ZK-0998A-GE

4.2.8. l Declaring and Allocating SQLDA Structures The SQLDA structure
contains SQL parameter marker and select list metadata as well as pointers
to data and indicator variables. Thus, the SQLDA is the means by which your
application and the SQL/Services API communicate about the SQL statement
being prepared for execution.

SQL/Services applications must allocate variables that point to SQLDA
structures. The execute_statement function contains the following declarations:
struct SQLDA
struct SQLDA

*param sqlda;
*select_sqlda;

4-10 Programming Guldellnes

The include file SQLSRVDA.H defines the SQLDA structure as follows:
/*
* SQLDA: SQL Description Area data structure.
*/
struct SQLDA
SQLDAID [ 8] ;
char
long int
SQLDABC;
short int
SQLN;
/* Total # of occurrences in SQLVAR */
short int SQLD;
/* # of select list items or parameter
* markers in prepared statement
*/
struct SQLVAR SQLVARARY[l];/* Variable length SQLVARARY.
*/
};

Your application can either allocate its own SQLDA structures or request
SQL/Services to dynamically allocate them. Existing applications written for
the RdbNMS SQL interface or other ANSI dynamic SQL implementations
may use preallocated SQLDA structures. In new SQUServices applications,
however, you may find that the dynamic allocation approach has two major
advantages in terms of efficient memory usage:
•

One field in the SQLDA, the SQLVARARY, is an array of SQLVAR
structures, each of which contains metadata about one parameter marker
or one select list item.
/*
* SQLVAR: Variable portion of the SQ LOA structure.
*/
struct SQLVAR
short int
SQLTYPE;
/* SQL data type.
short int
SQLLEN;
/* SQL data length.
char
*SQLDATA;
/* ptr: SQL data.
short int
*SQLIND;
/* ptr: SQL indicator var.
short int
SQLNAME LEN;/* length of SQL name.
char
SQLNAME[30];/* SQL name.

*/
*/
*/
*/
*/
*/

};

The length of the SQLVARARY array can vary because jt is impossible
to predict exactly how many parameter markers or select list items will
be present in any given SQL statement. If the API allocates an SQLDA
structure, the SQLVARARY can be the exact size needed for any particular
statement. If you choose to allocate your own SQLDA structures, you must
make sure that the SQLVARARY is large enough for all of the parameter
markers or select list items that can be present in a statement.
•

By calling the sqlsrv_release_statement or sqlsrv_release routine, you
can request the API to deallocate the structures when they are no longer
needed. However, the API cannot deallocate structures that it did not
allocate.

Programming Guldellnes 4-11

4.2.8.2 Testing for Parameter Markers When your application calls the
sqlsrv_prepare routine, it passes two SQLDA pointer variables. The
sqlsrv_prepare routine is one of two API routines (sqlsrv_associate is the
other, as described in Section 4.2.7.1) that require addresses to be passed by
reference. In other words, an argument is the address of an address.
select sqlda = NULL;
param_;qlda = NULL;
sts = sqlsrv_prepare(
assoc id,
database id,
sql stat;ment,
&statement id,
&param sqlda,
&select_sqlda);

/* association handle.
/* database id, must be zero.
/* SQL stat;ment.

/* Prepared statement id.

*/
*/
*/
*/

The param_sqlda pointer can be NULL or can contain the address of a valid
SQLDA structure. If you supply a NULL pointer (as in SQLSRV$DYNAMIC)
and the SQL statement contains parameter markers, the API dynamically
allocates a parameter marker SQLDA and writes the address of the structure
into the param_sqlda pointer. In other words, the API allocates the parameter
marker SQLDA structure only when the structure is needed. Thus, your
application can test the pointer and branch based on the presence or absence
of the structure.
if (param_sqlda)

If you supply a param_sqlda pointer containing the address of a valid SQLDA
structure, the API uses that structure to store parameter marker metadata.
Applications using preallocated SQLDA structures can branch on the value
that the API writes into the SQLD field, which is the number of parameter
markers in the SQL statement:
if (param_sqlda.SQLD > 0)

{

A nonzero value in the SQLD field indicates the presence of parameter
markers.

4-12 Programming Guldellnes

(

4.2.8.3 Allocating Indicator and Data Variables If parameter markers
are present in the SQL statement, the prepare_statement function calls the
API routine sqlsrv_allocate_sqlda_data (which also can be used with select list
SQLDAs). If you prefer, your application can allocate and deallocate its own
data and indicator variables.
sts = sqlsrv_allocate_sqlda_data(assoc_id, param_sqlda);

This routine dynamically allocates a data variable of the appropriate type and
an indicator variable for each parameter marker and writes the addresses of
those variables into the SQLVAR. The length of each data variable matches the
SQLVAR.SQLLEN field.
A symmetric routine, sqlsrv_free_sqlda_data, deallocates the variables;
however, the API cannot deallocate variables that it did not allocate.
4.2.8.4 Processing Parameter Markers The SQLSRV$DYNAMIC module
includes a function named get_params that obtains values for parameter
markers. As in the SQLSRV$DRIVER module, the implementation of the
terminal input/output is unimportant. As demonstrated in the get_params
function, your application must perform the following steps:
Allocate data and indicator variables for the parameter markers, as
described in Section 4.2.8.3.
sts = sqlsrv_allocate_sqlda_data(assoc_id, param_sqlda);

Execute a loop that iterates once for each parameter marker in the SQL
statement. The API places that number in the SQLD field when it executes
the sqlsrv_prepare routine.
for (i = O; i < param_sqlda->SQLD; i++)

{

/* for */

Within the loop, set up a dispatch table based on the data type of the
column.

Programming Guldellnes 4-13

switch(param_sqlda->SQLVARARY[i] .SQLTYPE)
case SQLSRV_ASCII_STRING:
case SQLSRV_GENERALIZED_NUMBER:
case SQLSRV_GENERALIZED_DATE:

gets(param_sqlda->SQLVARARY[i] .SQLDATA);

break;
case SQLSRV_VARCHAR:

/* counted string */

break;
} /* switch */

For null-terminated ASCII strings (data types other than
SQLSRV_VARCHAR), access the SQLDATA field of the appropriate
SQLVAR element using the loop counter as an index into the SQLVARARY.
Because it uses terminal inputioutput to obtain data, the get_params
function calls the library routine gets to write directly into the data
variable.
4

For counted strings (SQLSRV_VARCHAR), which are typically used to
store binary data, your application must:

Write a signed word integer into the first word of the SQLDATA field of
the appropriate SQLVAR element. That integer represents the number
of 8-bit bytes of data to follow. If you are programming in C, you can
use a cast operator to coerce t11:e data variable into an integer so that
you can write into the first word.
char *p;

p = param sqlda->SQLVARARY[i] .SQLDATA;
*(short i~t *)p = len;

Copy the data into the second and subsequent words of the SQLDATA
field of the appropriate SQLVAR element. If you are programming in
C, you can use a char pointer to write individual bytes of data into the
variable. Use the sizeof operator to set the pointer to the first data
byte.
p += sizeof(short int);
strncpy(p,s,len);

4-14 Programming Guldellnes

Because the get_params function uses terminal input/output to obtain
data, it demonstrates the SQLSRV_VARCHAR type by calling the
library routine strncpy to copy in ASCII data.
Executing Non-SELECT Statements For non-SELECT statements,
the execute_statement function calls the API routine sqlsrv_execute.
sts = sqlsrv execute(

4.2.8.5

assoc_id,
database id,
statement id,
execute_flag,
param sqlda
) ;

/* association handle.
/* database id, must be zero.
I* Prepared-statement id.
/* Execute mode.
/* Parameter marker SQLDA.

*/
*/
*/

*/
*/

Testing for SELECT Statements The test for the presence of a
SELECT statement is the same as that for parameter markers. When your
application calls the sqlsrv_prepare routine, it passes two SQLDA pointer
variables.

4.2.8.6

select sqlda = NULL;
param_sqlda = NULL;
sts = sqlsrv_prepare(
assoc id,
database id,
sql_statement,
&statement id,
&param_sqlda,
&select_sqlda);

/* association handle.

/* database id, must be zero.

/* SQL statement.

/* Prepared statement id.

The select_sqlda pointer can be NULL or can contain the address of a valid
SQLDA structure. If you supply a NULL pointer (as in SQLSRV$DYNAMIC)
and the SQL statement is a SELECT, the API dynamically allocates a select
list SQLDA and writes the address of the structure into the select_sqlda
pointer. In other words, the API allocates the select list SQLDA structure only
when the structure is needed. Thus, your application can test the pointer and
branch based on the presence or absence of the structure.
if (select_sqlda)

If you supply a select_sqlda pointer containing the address of a valid

SQLDA structure, the API uses that structure to store select list metadata.
Applications using preallocated SQLDA structures can branch on the value
that the API writes into the SQLD field, which is the number of select list
items in the SQL statement.

Programming Guldellnes 4-15

if (select_sqlda.SQLD > 0)

{

(

"
A nonzero value in the SQLD field indicates the presence of select list items.
Processing a Result Table If the SQL statement is a SELECT
statement, the execute_statement function emulates interactive SQL by
printing out each row in the result table. The steps are:

4.2.8.7

Allocate data and indicator variables for the select list items, as described
in Section 4.2.8.3.
sts = sqlsrv_allocate_sqlda_data(assoc_id, select_sqlda);

Open a cursor.
sts

= sqlsrv open cursor(
-

aS"soc_id,
cursor name,
statem;nt id,
param sqlda
);

/* association id
/* handle for cursor
/* handle for SELECT statement

/* parameter marker SQLDA

Execute a loop that iterates at least once and stops when the sqlsrv_fetch
routine returns a status code indicating that the end of the result table has
been reached.
do
sts = sqlsrv fetch(
assoc_id,
cursor name,

OL,

select_sqlda

/* association id
/* handle for cursor
/* direction
/* row number
/* select list SQLDA

*/
*/
*/
*/
*/

) ;

while (sts != SQL_EOS);

*/
*/
*/
*I

Within the loop, set up a dispatch table based on the status code.

4-16 Programming Guldellnes

switch (sts) {
case SQL SUCCESS:
/* process the data */

break;
case SQL EOS:
printf("------- END RESULT TABLE -------\n");
break;
default:
return report_error(assoc_id, sqlca_str, long_error);
break;
} /* switch */

When sqlsrv_fetch returns a status code of SQL_SUCCESS, the select list
SQLDA contains metadata and data for one row of the result table. The
SQLDA.SQLD field contains the number of columns in the row. Set up
another loop that iterates once for each column.
for (i = O; i < select_sqlda->SQLD; i++)

{

/* for */

Within the inner loop, check the indicator variable for a NULL value. If a
non-NULL value is present, set up a dispatch table based on the data type
of the column.
if (*select sqlda->SQLVARARY[i] .SQLIND < 0)
printf(tiNULL\n");
else
switch (select_sqlda->SQLVARARY[i] .SQLTYPE)
case SQLSRV_ASCII_STRING:
case SQLSRV_GENERALIZED_NUMBER:
case SQLSRV GENERALIZED DATE:
printf(ti%s\n", select_sqlda->SQLVARARY[i].SQLDATA);
break;
case SQLSRV_VARCHAR:

break;

/* switch */

Again, the execute_statement function uses the loop variable as an index
into the SQLVARARY.

For counted strings (SQLSRV_VARCHAR), which are typically used to
store binary data, your application must:

Read the signed word integer from the first word of the SQLDATA
field of the appropriate SQLVAR element. That integer represents the

Programming Guldellnes 4-17

number of 8-bit bytes of data that follow. If you are programming in C,
you can use a cast operator to coerce the data variable into an integer
so that you can access the first word.
char *p;

p = select__ ~·'Jlda->SQLVARARY [i]. SQLDATA;
len = * (sh1. rt int *) p;

Use the data in the second and subsequent words of the SQLDATA
field of the appropriate SQLVAR element. If you are programming in
C, you can use a char pointer to read individual bytes of data from the
variable. Use the sizeof operator to set the pointer to the first data
character.
p += sizeof(short int);
printf("%-*.*s\n", len, len, p);

Because the execute_statement function uses terminal input/output,
it demonstrates the SQLSRV_VARCHAR type by calling the print(
routine to display ASCII data.
4.2.8.8 Releasing Prepared Statements When a prepared statement is no
longer needed, the execute_statement function calls the API routine
sqlsrv_release_statement to release the resources allocated for that statement.
sts = sqlsrv_release_statement(
assoc id,
1,
&statement id

/* association handle.
/*no. of statement id's.
/* statement id array.

*I
*/
*/

) ;

If your application prepares several statements at one time, you can release
any or all of them together by passing an array of multiple statement
identifiers to the API routine sqlsrv_release_statement. (The sample
application prepares only one statement at a time.) In C, an array is a pointer,
so by passing a pointer, the execute_statement function is actually passing an
array of one element.

4.2.8. 9 Error Handling It is a good programming practice to check the status
value returned by each call to an API routine.
if (sts != SQL_SUCCESS)
return report_error(assoc_id,

sqlca_str, long_error);

If an API routine call fails, the sample application calls the function
report_error, which contains a dispatch table based on the SQLCODE field of
the SQLCA structure.

4-18 Programming Guidelines

switch (sqlca_str->SQLCODE) {

case SQLSRV NETERR:
printf("DECnet returned an error.\n");
printf("SQLERRD[O]: x%lx\n", sqlca str->SQLERRD[O]);
printf("SQLERRD[2]: %d.\n", sqlca ;tr->SQLERRD[2]);
sqlsrv release(assoc id,stats); exit(2);
break;

case SQL_EOS:
printf("SELECT or cursor got to end of stream\n");
break;

/* switch */

When a DECnet error or a server error occurs, the report_error function:
•

Prints out the specific error code in SQLERRD[O] (see Table 7-2)

•

Prints out the contents of SQLERRD[2], which represents different things_
depending on the API routine, and in some cases the SQL statement that
was executing, as shown in Table 7-3

•

Releases the association

The report_error function also prints out error messages returned in the
alternative error text buffer (see Section 7.2) by VMS, RdbNMS, or dynamic
SQL.
if (strlen(long error) != 0)
printf ( "%s\;t"", long_error);

4.3 Performance Enhancements
This section describes how to enhance the performance of your application by
reducing the number of client/server network messages required to perform
operations.

Programming Guldellnes 4-19

4.3.1

Batched Execution

When your application executes a prepared INSERT, UPDATE, or DELETE
statement that contains parameter markers, it can control whether the API
sends one row of data at a time to the server for processing or several rows at a
time. Frequently, batched execution reduces the number of messages required
to complete the operation.
The mechanism for controlling batched execution is the execute_flag parameter
in the sqlsrv_execute routine, which is described in Section 6.6. The values of
the execute_flag parameter are shown in Table 6-4.
In normal (nonbatched) execution, the API places each set of parameter marker
values (rows) in the message buffer and sends the message to the server for
execution.
In batched execution, the API stores sets of parameter marker values (rows)
in the message buffer but does not send the message to the server until your
application signals the end of the batched execution.
If the message buffer becomes full during batched execution, the API sends

the message to the server and begins a new message in a manner that is
transparent to your application. In that case, when the batched parameter
marker values arrive on the server, it stores them in a buffer until your
application signals the end of the batched execution. If your application aborts
the batched execution, the API clears the buffers on both the client and the
server. Thus, the database remains consistent and there is no need to roll back
the transaction.

4.3.2

Fetching Multiple Rows

When your application fetches rows from a result table, it can control whether
the server sends one row of data at a time to the API or several rows at a time.
Fetching multiple rows at a time generally reduces the number of client/server
messages required to complete the operation.
The mechanism for fetching multiple rows is the sqlsrv_fetch_many routine,
which is described in Section 6.9. The repeat_count parameter specifies
the number of rows that the server can send to the API the next time your
application calls sqlsrv_fetch. A repeat_count value of 0 gets the entire result
table.
When the call to sqlsrv_fetch_many completes, the next call to sqlsrv_
fetch causes the API to get multiple rows of data and store them in the
message buffer. Then, subsequent calls to sqlsrv_fetch can fetch rows without
client/server messages.

4-20

Programming Guidelines

(
.

For example:
status
status
status
status
status
status
status

sqlsrv_fetch(
sqlsrv_fetch_many(
sqlsrv_fetch(
sqlsrv_fetch(
sqlsrv_fetch(
sqlsrv_fetch(
sqlsrv_fetch(

);

/* gets 1 row

. . 3 ..

);
);
) ;
);
);

/*
/*
/*
/*
/*

gets 3
gets 0
gets 0
gets 1
gets 1

) ;

rows
rows
rows
row
row

*/
*/
*/
*/
*/

When the specified number of rows have been fetched, the API returns to
the default behavior (one row at a time), which is necessary when executing
the SQL statements UPDATE ... WHERE CURRENT OF cursor-name and
DELETE ... WHERE CURRENT OF cursor-name.
If a sqlsrv_fetch_many operation requests more rows than can fit in the

message buffer at one time, the API clears and refills the message buffer in a
manner that is transparent to your application.

4.4 Filtering Result Tables
This section describes how your application can instruct the server to discard
unwanted rows from a result table before sending them to the client, reducing
the number of client/server messages required to complete the operation.
The sqlsrv_set_filter routine (Section 6.17) allows your application to define a
Boolean (true/false) expression and to associate that filter expression with a
cursor. When your application fetches rows from the result table, the server
evaluates the expression for each row and filters out (discards) those rows for
which the expression returns a value of false.

4.4. l

Elements of Filter Expressions

The syntax of filter expressions is similar to that of most high-level
programming languages.
The operands that can be used to form filter expressions are:

• Constants, as described in Section 4.4.2
• Placeholders, as described in Section 4.4.3
• Functions, as described in App.endix A
The operators that can be used to form filter expressions are:

• Mathematical operators, as described in Section 4.4.4
• Relational operators, as described in Section 4.4.5

Programming Guldellnes 4-21

• Logical operators, as described in Section 4.4.6
• String operators, as described in Section 4.4. 7
The precedence of the operators is described in Section 4.4.8.

4.4.2 Constants
The following types of constants can be used in filter expressions:
Character

ASCII string delimited by double quotes, single quotes, or brackets.

Numeric

Decimal or E notation. The internal representation of numeric data is
floating-point.

Date

Character string in the format {mm/dd/yy} (see Section 5.2 and
Section A.13).

4.4.3 Placeholders
Variables in filter expressions are represented by placeholders (question marks)
that correspond to columns in the result table. An index array maps the
placeholders to values in the select list SQLDA. Although they are ASCII
strings, the SQL/Services data types (see Chapter 5) behave as if they were
binary; SQLSRV_GENERALIZED_NUMBER data behave as floating-point
numeric data, and SQLSRV_GENERALIZED_DATE data behave as date type
data.
For example, suppose that your application prepares the following SELECT
statement in which columns A, B, and Care numeric data:
SELECT A,B,C FROM NUMBERS

The only useful rows from the result table are those for which the following
algebraic expression is true:
SIN(C + A)

+ 12 > B

Your application would specify the following filter expression, replacing the
variables with"?" placeholders:
SIN(? + ?) + 12

> ?

When your application calls sqlsrv_set_filter, it associates the placeholders
with columns in the result table by passing an index array into the select
list SQLDA. The first element of the index array corresponds to the leftmost
placeholder, and so forth. You would set up the index array as shown:
sqlda_index_array[O]
sqlda index array[l]
sqlda=index=array[2]

4-22

Programming Guldellnes

2; /* "C" */

/* "A" */
1; /* "B" */

(

The values of the array elements are zero-based indexes into the array of
SQLVAR structures, each element of which represents a column, as shown
in Figure 4-2. The first placeholder corresponds to column C, the data and
metadata for which is in SQLDA.SQLVARARY[2].
Figure 4-2

Placeholders in Filter Expressions
SQLVARARY

Index Array

SIN(?+?)+ 12 > ? - - - • • 2

ZK-0997 A-GE

4.4.4 Mathematical Operators
Mathematical operators in filter expressions generate numeric results.
Operator

Description

Precedence

()

Grouping

Unary Positive

Unary Negative

**or A

Exponentiation

Multiplication

Division

Addition

Subtraction

4.4.5

Relational Operators

Relational operators in filter expressions generate logical results; that is,
true (.T.) or false (.F.). You can us.e relational operators with character,
numeric, date, or logical operands. However, both operands in a relational
expression must be of the same type. Relational operators have only one level
of precedence and are performed in order from left to right.

Programming Guidelines 4-23

Operator

Description

<
>

Less than

Equal to

<>or#

Not equal to

Less than or equal to

Greater than or equal to

Substring comparison. (For example, if A and Bare character strings,
A$B returns a logical true if A is either identical to B or contained
within B.)

(

Greater than

4.4.6 Logical Operators
Logical operators in filter expressions obtain a logical result from comparing
two expressions.
Operator

Description

Precedence

()

Grouping

.NOT.

Logical not

.AND .

Logical and

.OR.

Logical or

4.4.7 String Operators
String operators in filter expressions concatenate two or more character
strings into a single character string. String operators have only one level of
precedence and are performed in order from left to right.
Operator

Description

()

Grouping

Trailing spaces between the strings are left intact when the strings are
joined.
Trailing spaces between the strings are moved to the end of the last
string.

4.4.8

Precedence of Operators

When several of the four types of operators are used in the same filter
expression, the precedence levels are:

4-24

Mathematical or string

Relational

Programming Guidelines

Logical

All operations of the same precedence level are performed in order from left
to right. Parentheses override the order in which operations are performed.
Operations within nested parentheses are performed first.

4.5 Execution Logging
This section describes how to use various types of execution logging to help
debug and monitor the performance of SQUServices applications.
The mechanism for enabling or disabling logging is the association structure
(see Section 7.2). It contains two fields, CLIENT_LOG and SERVER_LOG,
into which you place one or more of the values defined in the include file
SQLSRV.H, which are:
SQLSRV_LOG_DISABLED

Disables logging (default)

SQLSRV_LOG_ASSOCIATION

Enables association logging

SQLSRV_LOG_ROUTINE

Enables API routine logging

SQLSRV_LOG_PROTOCOL

Enables message protocol logging

SQLSRV_LOG_SCREEN

Sends logging output to the video display on the
client system as well as to the log file

All types of logging are valid on the client system; on the server system,
however, only message protocol logging is valid.
·

To enable more than one type of logging, add the appropriate constants. For
example:
associate_str.CLIENT_LOG = SQLSRV_LOG_ROUTINE + SQLSRV_LOG_SCREEN;

When you enable client logging, the API writes information into the file
CLIENT.LOG in the SQL/Services application program's current working
directory. When you enable server logging, the server process writes
information into the file SQLSRV.LOG in the default directory of the
association's UIC.

4.5. l

Association Logging

Association logging occurs whenever a client/server association is created,
terminated, or aborted. Use this type of logging to debug server access in
application programs.
Depending on the API routine called, association log entries include some or all
of the following items:

0 A header that identifies the entry as ASSOCIATE LEVEL LOG
8

The name of the API routine

0 The association identifier
Programming Guldellnes 4-25

e The name of the server node
0

The name of the user account on the server

0 The error status for the API routine
0 The detailed error code for network or server errors
For example:

0
8

ASSOCIATE LEVEL LOG
----SQLSRV_ASSOCIATE
--------SQLSRV ASSOCIATE ID: 106520
--------NODE: ~bcdef, C) USERNAME: xxxxxx,

0 SQLCODE: 0, 0 SQLERRD[O] 0 @

These messages indicate that an association with a server system was created
and terminated normally.

4.5. 2

Routine Logging

RoutiPe logging occurs whenever your application calls an SQL/Services
API routine. Use this type of logging to debug execution flow in application
programs.
Routine log entries include some or all of the following items:

4-26

0
8
0
0
0

A header that identifies the entry as ROUTINE LEVEL LOG

0
0

The SQL statement identifier

The name of the API routine
The length in bytes of the SQL statement string
The SQL statement string
The name of the cursor

The execution flag

Programming Guidelines

(

For example:

ROUTINE LEVEL LOG
----SQLSRV_PREPARE ~
--------SQL STATEMENT
------------len: 45,

0 value: Select * from sqlsrv_table where USERNAME - ? 0

ROUTINE LEVEL LOG
----SQLSRV_OPEN_CURSOR
--------CURSOR NAME
------------sqlsrv_cursor
--------STATEMENT ID
1199896 0

ROUTINE LEVEL LOG
----SQLSRV_EXECUTE
--------STATEMENT ID
------------1199896
--------EXECUTE FLAG

Routine log entries that follow the sqlsrv_prepare routine also include
metadata:

0
8
0
0
0

The type of SQLDA (parameter marker or select list)
The number of parameter markers or select list items
The SQL/Services data type
For non-numeric data, the length of the data variable
For numeric data, the length of the data variable and the scale factor (see
Section 7 .6)

0 The name of the column
For example:
ROUTINE LEVEL LOG
----SELECT LIST SQLDA
--------SQLDA: SQLD 4
--------[0] .SQLTYPE: SQLSRV ASCII_STRING, @) SQLLEN: 33
------------SQLNAME: USERNAME
--------[l] .SQLTYPE: SQLSRV GENERALIZED NUMBER, SQLLEN[O] 12, SQLLEN[l] 0
------------SQLNAME: INTEGE;_VALUE
--------[2] .SQLTYPE: SQLSRV_GENERALIZED_NUMBER, SQLLEN[O] 24, SQLLEN[l] 0
------------SQLNAME: DOUBLE VALUE
--------[3] .SQLTYPE: SQLSRV=GENERALIZED_DATE, SQLLEN: 17
------------SQLNAME: DATE VALUE

0
@

0 -

Programming Guidelines 4-27

Routine log entries that follow the sqlsrv_fetch, sqlsrv_open_cursor, and
sqlsrv_execute routines also include data:

0 The type of SQLDA (parameter marker or select list)
@ The number of parameter markers or select list items

4D The SQL/Services data type

e The value of the indicator variable
0 The length of the value of the data variable
0 The value of the data variable
For example:
ROUTINE LEVEL LOG
----SELECT LIST SQLDA
--------SQLDA: SQLD 4
------------[O] .SQLTYPE: SQLSRV ASCII STRING,

----------------len: 32,
------------[1] .SQLTYPE:
----------------len: 11,
------------[2] .SQLTYPE:
----------------len: 23,
------------(3] .SQLTYPE:
----------------len: 16,

4.5.3

@ val~e: x~xxx 0

6) SQLIND: 0 C)

SQLSRV GENERALIZED NUMBER, SQLIND: 0
value:-1
SQLSRV GENERALIZED NUMBER, SQLIND: 0
value:- l.280000000000000E+002
SQLSRV GENERALIZED DATE, SQLIND: 0
value:-1988070100000000

Message Protocol Logging

Message protocol logging occurs whenever a message is transmitted between
the client API and the server process. Use this type of logging to verify that
the SQL/Services client/server communications protocol is working as expected.
Protocol log entries include some or all of the following items:

0 A header that identifies the entry as PROTOCOL LEVEL

The word CLIENT or SERVER to indicate where the log file was written

0 The word "read" or "write" to indicate whether the packet was received or
transmitted, respectively

e The packet identification number, which is incremented from 0 from the
beginning of the association

0 The packet sequence- number, which is used in the following instances:
•

Batched execution

• Multiple row fetches
• Any message that is too large for a single packet

4-28

Programming Guidelines

0 The message tag, which either specifies a routine to be executed on the
server, an acknowledgment (ACK) that the routine was executed, or an
error (ERROR) message

0 Tags that represent routine parameters, including:
0 The SQL/Services data type

The total length in bytes of the data

Cli> The number of bytes of data in this packet

CD The data value

4B Subtags that describe SQLDA structures
For example:
PROTOCOL LEVEL LOG C) CLIENT: ~ write @)
----PACKET ID: 11, (> PACKET SEQUENCE: 0 @)
--------SQLSRV FETCH 0
------------CURSOR NAME @
----------------SQLSRV ASCII STRING,
len: 13
--------------------len: 13,- Qi) value: sqlsrv_cursor CD
--------END OF MESSAGE

PROTOCOL LEVEL LOG CLIENT: read
----PACKET ID: 11, PACKET SEQUENCE: 0
--------SQLSRV_FETCH ACK
------------FETCH ROW NUMBER
----------------SQLSRV_GENERALIZED_NUMBER, len: 1
--------------------len: 1, value: 1
------------SELECT LIST DATA @
----------------len: 2, value: 4
------------SQLVAR 48
----------------len: 2, value: 0
------------SQLDATA 48
----------------SQLSRV_ASCII_STRING, len: 32
--------------------len: 32, value: xxxxxx
------------SQLIND 48
----------------len: 2, value: 0

--------END OF MESSAGE

Programming Guldellnes 4-29

5
Data Types and Environment Variables
SQL/Services supports a subset of the SQL data types. Declarations for data
type names and constant values are provided in the include file SQLSRVDA.H.
In filter expressions, SQUServices uses environment variables to control the
format of date type data and the way that string matching works.

5. 1 Data Types
The SQL data types are listed in Table 5-1 with their SQUServices
represen ta ti on.
Table 5-1

Data Types

SQL Data Type

SQL/Servlces Data Type

SQL_INTEGER

SQLSRV_GENERALIZED_NUMBER

SQL_SMALLINT

SQLSRV_GENERALIZED_NUMBER

SQL_FLOAT

SQLSRV_GENERALIZED_NUMBER

SQL_CHAR

SQLSRV_ASCII_STRING

SQL_VARCHAR

SQLSRV_VARCHAR

SQL_DATE

SQLSRV_GENERALIZED_DATE

SQL_DECIMAL

SQLSRV_GENERALIZED_NUMBER

SQL_QUADWORD

SQLSRV_GENERALIZED_NUMBER

SQL_NUMERIC

SQLSRV_GENERALIZED_NUMBER

Data Types and Environment Variables 5-1

5.1. 1 SQLSRV_ASCll_STRING
The SQLSRV_ASCII_STRING data type is an array of 8-bit bytes containing
ASCII characters. A byte containing 0 (the null character) indicates the end of
the data. This data type is commonly known as an ASCIZ or null-terminated
string.

5.1.2

SQLSRV_GENERALIZED_NUMBER

The SQLSRV_GENERALIZED_NUMBER data type is an
SQLSRV_ASCII_STRING that is used to represent all numeric values. The
format is:
[-][NNN][.DD][E[-][xx]]

unary minus
NNN

integer portion of the number

.DD

decimal portion of the number

exponent identifier

exponent value

unary minus for exponent value

The brackets indicate the optional syntax. The one requirement is that either
the integer or decimal portion of the number must be specified.

5.1.3

SQLSRV_GENERALIZED_DATE

The SQLSRV_GENERALIZED_DATE data type is an
SQLSRV_ASCII_STRING that is used to represent all dates. The format is:
ccyymmdd[hh[ml(ss(ff]]]]

century

year

month

day

hour (24-hour format)

minute

second

fractions of a second

If you omit any of the optional fields, SQL/Services pads the string with zeros.
Thus, the default time is exactly midnight.

For example: May 4, 1989 11:04 a.m. would be represented as: 198905041104.

5-2 Data Types and Environment Variables

(
\

5.1.4· SQLSRV_VARCHAR
The SQLSRV_VARCHAR data type is a signed word integer followed by an
array of 8-bit bytes that can be used to store any sort of data, including binary.
The signed word contains the number of bytes that contain data. This type is
commonly known as a counted string. The maximum length of an
SQLSRV_VARCHAR is 16,383 bytes.

5.2 Environment Variables
Environment variables (SQLSRV_ENV_DATE, SQLSRV_ENV_CENTURY, and
SQLSRV_ENV_SET_EXACT) control the format of date type data and the way
that string matching works in filter expressions. For more information, see:
• filter expressions (Section 4.4)
• sqlsrv_get_environment (Section 6.11)
• sqlsrv_set_environment (Section 6.16)
•

sqlsrv_env_str (Section 7. 7)

5.2. l

SQLSRV_ENV_DATE

The SQLSRV_ENV_DATE variable controls the format of the date values used
in filter expressions. The settings are shown in Table 5-2.
Table 5-2

Settings for the SQLSRV_ENV_DATE Variable

Setting

SQLSRV_ENV_DATE_AMERICAN
SQLSRV_ENV_DATE_BRITISH
SQLSRV_ENV_DATE_GERMAN
SQLSRV_ENV_DATE_JAPAN
SQLSRV_ENV_DATE_ANSI
SQLSRV_ENV_DATE_FRENCH
SQLSRV_ENV_DATE_ITALIAN
SQLSRV_ENV_DATE_USA

5.2.2

Value

Result

mm/dd/yy

dd/mm/yy

dd.mm.yy

yy/mm/dd

yy.mm.dd

dd/mm/yy

dd-mm-yy

mm-dd-yy

Default

SQLSRV_ENV_CENTURY

The SQLSRV_ENV_CENTURY variable controls whether the century prefix is
included as part of the date format. The settings are shown in Table 5-3.

Data Types and Environment Variables 5-3

Table 5-3

Settings for the SQLSRV_ENV_CENTURY Variable

Setting

Value

Result

SQLSRV_ENV_CENTURY_OFF

Century is OFF.

SQLSRV_ENV_CENTURY_ON

Century is ON.

5.2.3

Default

SQLSRV_ENV_SET_EXACT

The SQLSRV_ENV_SET_EXACT variable controls whether a comparison
between two character strings requires the strings to be the same length. The
settings are shown in Table 5-4.
Table 5-4

5-4

Settings for the SQLSRV_ENV_SET_EXACT Variable

Setting

Value

Result

SQLSRV_ENV_SET_EXACT_OFF

Comparisons between
character strings begin
with the left character
in each string and
continue character-bycharacter to the end of
the string on the right of
the relational operator.
If the two strings are
equivalent up to that
point, the comparison
returns a value of true.

SQLSRV_ENV_SET_EXACT_ON

The comparison of
characters in each string
is the same except that
both character strings
must be the same length
for the comparison to
return a value of true.

Data Types and Environment Variables

Default

6
API Routines

This chapter describes the routines in the SQUServices client Application
Programming Interface (API).

6. l

Documentation Format
Each SQL/Services API routine is documented using a structured format
called the routine template. The sections of the routine template are listed in
Table 6-1, along with the information that is presented in each section and
the format used to present the information. Some sections require no further
explanation beyond what is given in Table 6-1. Those that require additional
explanation are discussed in the remaining subsections of this section.
Table 6-1

Sections in the Routine Template

Section

Description

Routine Name

Appears at the top of the page, followed by the English name of
the routine

Overview

Appears directly below the routine name and explains, usually in
one or two sentences, what the routine does

VAX Format

Gives the routine entry point name and the routine argument
list; also specifies whether arguments are required or optional

C Format

Shows the C function prototype from the include file SQLSRV:H

Parameters

Gives detailed information about each parameter
(continued on next page)

API Routines

6-1

Table 6-1 (Cont.)

Sections in the Routine Template

Section

Description

Contains detailed information about specific actions taken by the
routine, interaction between routine arguments, operation of the
routine within the context of a specific operating system, and
resources used by the routine

Notes

Contains additional pieces of information related to applications
programming

Errors

Lists the SQUServices errors that can occur in the routine

SQL Errors

Lists the SQL errors (if any) that can occur in the routine

6. 1. l

Routine Name

The SQL/Services API routine names are shown in the form sqlsrv_xxx
throughout the manual. In most Digital software documentation, the routine
template is language-independent but quite dependent on the VMS operating
system. Because the SQL/Services API must be portable across all supported
platforms, the routine template in this manual is intended for C programmers
who are concerned with portability.
Digital requires that all callable products that run on the VMS operating
system have routine names in the format facility_name$routine_name. Thus,
the VAX Format section of the template shows the routine name in the format
SQLSRV$routine_name.
However, the dollar sign character ( $ ) is not portable to all supported
platforms. Some C compilers return a syntax error when they encounter a
dollar sign character. Thus, SQL/Services automatically maps routine calls in
the portable C format to the dollar sign format in a manner that is transparent
to your application.

6. 1. 2 Return Values
The SQL/Services routine template does not include a "Returns" section.
Except where explicitly noted, the SQL/Services API routines return a signed
longword integer containing one of the values shown in Table 6-2.

6-2 API Routines

i
\

Table 6-2

API Return Values

Return Value

Description

n = SQL_SUCCESS 1

The routine completed successfully.

n < SQL_SUCCESS

An error occurred during processing. Refer to the
SQLCA.SQLCODE for the specific error.

n > SQL_SUCCESS

A warning was issued during processing. Refer to
the SQLCA for additional information.

The symbol SQL_SUCCESS is defined as O in the include file SQLSRVCA.H.

6. 1.3

VAX Format Section

In the VAX Format section:
• The entry point name is shown in uppercase letters.
• The argument names are shown in lowercase letters.
•

One or more spaces are used between the entry point name and the first
argument, and between each argument and the next.

•

Brackets surround optional arguments. In SQUServices, optional
arguments cannot be omitted; a value of 0, passed by value, indicates
that the API is to ignore the parameter.

•

Commas precede arguments instead of following them.

6. 1.4 C Format Section
The C Format section shows the function prototypes for the SQUServices API
routines exactly as they are declared in the include file SQLSRV.H. If you
are using a compiler that does not support function prototypes, such as the
ULTRIX C compiler, alternative declarations are also provided in SQLSRV.H.
For example, the following is the function prototype for the
sqlsrv_execute_immediate routine:
extern int sqlsrv execute immediate(
char *associate_id,
long int database_id,
char *sql_statement);

The following is the alternative function declaration for the same routine:
extern int sqlsrv execute immediate(associate id, database id,
sql_state;ent)
char *associate id;
long int database_id;
char *sql_statement;

API Routines

6-3

To avoid repetition, #include compiler directives are not repeated in each
routine template. When you write SQL/Services programs, use the following
#include directives:
#include <sqlsrvda.h>
#include <sqlsrvca.h>
#include <sqlsrv.h>

/* SQLDA structure definition.
/* SQLCA structure definition.
/* SQL/SERVICES structure definitions.

*/
*/
*/

6. 1.5 Parameters Section
The Parameters section contains detailed information about each parameter
listed in the call format. Parameters are described in the order in which they
appear in the call format.
The following format is used to describe each parameter:
name
data type:

the data type of the data specified by the parameter (see Section 6.1.5.1)

access:

the way in which the called routine accesses the data specified by the
parameter (see Section 6.1.5.2)

mechanism:

the way in which a parameter specifies the data to be used by the called
routine (see Section 6.1.5.3)

In addition, the Parameters section contains at least one paragraph of text
describing the purpose of the parameter.
6. 1.5. 1 Data Type Entry A parameter does not have a data type; rather, the
data specified by the parameter has a data type. The parameter is the vehicle
for passing of data to the called routine. However, the term parameter data
type is used to describe the data type of the data specified by the parameter.
Table 6-3 lists the data types used in SQUServices API routine calls and
structures.
Table 6-3

API Parameter Data Types

Data Type

Description

character string

Array of unsigned 8-bit integers

word (signed)

16-bit signed integer

word (unsigned)

16-bit unsigned integer

longword (signed)

32-bit signed integer

longword (signed) array

Array of signed 32-bit integers

longword (unsigned)

32-bit unsigned integer
(continued on next page)

6-4 API Routines

(

Documentation Format

Table 6-3 (Cont.)

API Parameter Data Types

Data Type

Description

pointer

32-bit unsigned integer that contains an address

structure

Named collection of variables (record in some languages)

structure array

Array of structures

undefined

Memory that is allocated and used by the API but never
accessed directly by the application (see the description of
the associate_id parameter in Section 6.4)

Regardless of the passing mechanism (described in Section 6.1.5.3), the data
type entry always refers to the data type of the data specified by the parameter.
6. 1.5.2 Access Entry The access entry describes the way in which the called
routine accesses the data specified by the parameter. The following three
access methods are used:

•

Read. Data needed by the called routine to perform its operation is read
but not returned.

• Write. Data that the called routine returns to the calling routine is written
into a location accessible to the calling routine.
• Modify. Data that is both read and returned by the called routine; input
data specified by the parameter is overwritten.
6. 1.5.3 Mechanism Entry The parameter passing mechanism is the way
in which a parameter specifies the data to be used by the called routine.
SQL/Services uses two passing mechanisms:

•

By value. The parameter contains a copy of the data to be used by the
routine.

•

By reference. The parameter contains the address of the data to be used by
the routine. In other words, the parameter is a pointer to the data.
Because C supports only call by value, write parameters other than arrays
and structures must be passed by means of pointers (variables that contain
the addresses of objects). References to names of arrays and structures are
automatically converted by the compiler to pointer expressions.

API Routines 6-5

sqlsrv_abort-Disconnect Association

6.2 sqlsrv_abort-Disconnect Association
The sqlsrv_abort routine drops the network link between the client and server,
frees client association resources, and rolls back active transactions on the
server.

VAX Format
SQLSRV$ABORT

associate_id

C Format
extern int sqlsrv abort(
char *associate_id);

Parameters
associate_ Id
data type:
access:
mechanism:

(
undefined
read
by reference

Handle used to identify the active association.

Errors

6-6

SQLSRV_INTERR

Internal error.

SQLSRV_INVASC

Invalid association identifier.

SQLSRV_NETERR

DECnet returned an error.

API Routines

sqlsrv _allocate_sqlda_data-Allocate Variables

6.3 sqlsrv_allocate_sqlda_data-Allocate
Variables
The sqlsrv_allocate_sqlda_data routine dynamically allocates data and
indicator variables. Your application passes an SQLDA structure to
sqlsrv_allocate_sqlda_data, which allocates variables of the appropriate
data type and writes the addresses of the newly allocated variables into the
SQLDATA and SQLIND fields in the SQLVAR array.

VAX Format
SQLSRV$ALLOCATE_SQLDA_DATA associate_id ,sqlda_str

C Format
extern int sqlsrv_allocate_sqlda_data(
char *associate id,
struct SQLDA *sqlda_str);

Parameters
associate_ld
data type:
access:
mechanism:

undefined
read
by reference

Handle used to identify the active association.

sqlda_str
data type:
access:
mechanism:

structure
modify
by reference

An SQLDA structure into whose SQLVAR array the API writes the address
of the newly allocated SQLDATA and SQLIND fields. You can pass any valid
SQLDA structure; it does not matter how the structure was allocated.

API Routines 6-7

sqlsrv_allocate_sqlda_data-Allocate Variables

Notes
You can free variables allocated by sqlsrv_allocate_sqlda_data explicitly by
calling sqlsrv_free_sqlda_data, or implicitly by calling sqlsrv_release_statement
or sqlsrv_release.

Errors

6-8

SQLSRV_INTERR

Internal error.

SQLSRV_INVARG

Invalid routine parameter.

SQLSRV_INVASC

Invalid association identifier.

SQLSRV_INVSQLDA

Invalid SQLDA structure.

SQLSRV_NO_MEM

API memory allocation failed.

API Routines

(

sqlsrv_associate-Create Client/Server Association

6.4 sqlsrv_associate-Create Client/Server
Association
The sqlsrv_associate routine creates a DECnet link between your application
and a server process, using the node name, user name, and password input
parameters. It creates an association handle (identification structure) used in
subsequent routine calls and binds specific variables (message protocol buffers
and an SQLCA structure) to the association.

VAX Format
SQLSRV$ASSOCIATE

node_name ,[user_name] ,[password) ,read_buffer ,write_buffer
,read_buffer_size ,write_buffer_size ,sqlca_str ,associate_str
,associate_id

C Format
extern int sqlsrv_associate(
char *node_name,
char *user_name,
char *password,
char *read_buffer,
char *write buffer,
long int read buffer_size,
long int write buffer size,
struct SQLCA *sqlca_str,
struct ASSOCIATE STR *associate_str,
char **associate=id);

Parameters
node_name
data type:
access:
mechanism:

character string
read
by reference

A null-terminated string containing the DECnet node name of the VAX system
on which the server resides.

API Routines 6-9

sqlsrv_associate-Create Client/Server Association
user_name (optional)
data type:
character string
access:
read
mechanism:
by reference

A null-terminated string containing the user name within whose context the
server session runs. If this parameter is NULL, and a default user name is
defined on your system, the API attempts to access the server by means of
proxy. If proxy access is disabled on the server, you must supply a user name;
otherwise the association fails. (See the Guide to DECnet-VAX Networking for
information on proxy access and the DECnet docwnentation for your system
for information on setting default access control data.)
password (optional)
data type:
character string
access:
read
mechanism:
by reference

A null-terminated string containing the password for the account within whose
context the server session runs.
read_buffer
data type:
access:
mechanism:

character string
modify
by reference

The buffer used by the API to receive messages from the server.
write_buffer
data type:
access:
mechanism:

character string
modify
by reference

The buffer used by the API to build messages to send to the server.
read_buffer_ size
data type:
access:
mechanism:

longword (signed)
read
by value

The size in bytes of the API buffer used to receive messages. The maximum
value is 65,535 bytes, the minimwn value is 256 bytes.

6-1 O API Routines

sqlsrv_associate-Create Client/Server Association
wrlte_buffer_size
data type:
access:
mechanism:

longword (signed)
read
by value

The size in bytes of the API buffer used to send messages. The maximum
value is 65,535 bytes, the minimum value is 256 bytes.
sqlca_str
data type:
access:
mechanism:

structure
modify
by reference

An SQLCA (SQL Communications Area) structure (see Section 7.3). Your
application must declare an instance of this structure and can refer to it when
any API routine called in the context of this association returns a status value
other than SQL_SUCCESS. (The SQLCA structure is defined in the include file
SQLSRVCA.H, along with all valid SQUServices error codes.)
associate_ str
data type:
access:
mechanism:

structure
modify
by reference

An ASSOCIATE_STR structure, used to define optional association
characteristics (see Section 7 .2). The ASSOCIATE_STR structure is defined in
the include file SQLSRV.H.
associate_ id
data type:
access:
mechanism:

pointer
write
by reference

A pointer variable into which the API writes the address of the newly
allocated associate_id (an undefined structure never accessed directly by your
application). This handle is used by all succeeding routines to identify the
active association.

Notes
In selecting buffer sizes for applications that will run on the MS-DOS operating
system, you must take into account the limitations of the small and medium
standard memory models in which the data segment is 64K bytes.

API Routines 6-11

sqlsrv_associate-Create Client/Server Association

Errors

6-12

SQLSRV_INTERR

Internal error.

SQLSRV_INVARG

Invalid routine parameter.

SQLSRV_INVASCSTR

Invalid parameter in ASSOCIATE_STR.

SQLSRV_INVBUFSIZ

Invalid read or write buffer size.

SQLSRV_INVSQLCA

Invalid SQLCA structure.

SQLSRV_NETERR

DECnet returned an error.

SQLSRV_NO_MEM

API memory allocation failed.

SQLSRV_OPNLOGFIL

Unable to open log file.

API Routines

sqlsrv_close_cursor-Release Result Table

6.5 sqlsrv_close_cursor-Release Result Table
The sqlsrv_close_cursor routine closes an open cursor.

VAX Format
SQLSRV$CLOSE_CURSOR

associate_id ,cursor_name

C Format
extern int sqlsrv close cursor(
char *a;sociate_id,
char *cursor_name);

Parameters
associate_id
data type:
access:
mechanism:

undefined
read
by reference

Handle used to identify the active association.
cursor_name
data type:
access:
mechanism:

character string
read
by reference

A null-terminated string used to identify the open cursor.

Errors
SQLSRV_INTERR

Internal error.

SQLSRV_INVASC

Invalid association identifier.

SQLSRV_INVCURNAM

Invalid cursor name.

API Routines

6-13

sqlsrv_close_cursor-Release Result Table
SQLSRV_MULTI_ACT

A batched sqlsrv_execute or sqlsrv_fetch_many
context is active.

SQLSRV_NETERR

DECnet returned an error.

SQL Errors
SQL_RDBERR

Rdb/VMS returned an error.

(

6-14

API Routines

sqlsrv_execute-Execute Prepared Statement

6.6 sqlsrv_execute-Execute Prepared
Statement
The sqlsrv_execute routine executes a prepared SQL statement and, if rows
were modified, updates the SQLCA.

VAX Format
SQLSRV$EXECUTE

associate_id ,database_id ,statement_id ,execute_flag
,parameter_marker_sqlda

C Format
extern int sqlsrv execute(
char *associate id,
long int database_id,
long int statement id,
short int execute_flag,
struct SQLDA *parameter_marker_sqlda);

Parameters
assoclate_ld
data type:
access:
mechanism:

undefined
read
by reference

Handle used to identify the active association.
database_id
data type:
access:
mechanism:

longword (signed)
read
by value

This parameter must be 0. Databases are referenced within the SQL statement
syntax.

API Routines 6-15

sqlsrv_execute-Execute Prepared Statement
statement_ld
data type:
access:
mechanism:

longword (signed)
read
by value

Variable identifying a previously prepared statement. When batching is
enabled, this parameter must remain the same. In other words, before
changing this parameter, you must first call the sqlsrv_execute routine and
pass an execute_flag parameter with a value of 0 or 2 (signaling that the
current batch is finished).
execute_ flag
data type:
access:
mechanism:

word (signed)
read
by value

For a prepared INSERT, UPDATE, or DELETE statement that contains
parameter markers and is executed more than once, this parameter specifies
whether the API sends single or multiple sets of parameter marker values
to the server for processing (see Section 4.3.1). For all other prepared SQL
statements, this value must be 0. The values of the execute_flag parameter are
shown in Table 6--4.
Table 6-4

Values of the execute_flag Parameter

Value

Function

Description

Nonbatched execution

Sends the contents of the message buffer to
the server for execution, including the current
parameter marker values.

Begins batched
execution

Stores the current parameter marker values
in the message buffer but does not send the
contents of the buffer to the server.

Ends batched execution

Sends the contents of the message buffer to the
server for execution, not including the current
parameter marker values.

Aborts batched
execution

Clears the contents of the message buffer and
clears all parameter marker values waiting to
execute on the server.

(
6-16

API Routines

sqlsrv_execute-Execute Prepared Statement
parameter_marker_sqlda
data type:
longword (unsigned)
access:
read
mechanism:
by reference

An SQLDA structure defining the parameter marker values for the SQL
statement to be executed.

Notes
• When you execute an UPDATE or DELETE statement, a single set
of parameter marker values can affect many rows. Thus, when your
application requests execution by calling the sqlsrv_execute routine with
an execute_flag parameter of 0 or 2, the API places the following status
information in the SQLCA structure:
The SQLERRD[l] contains the number of statements (sets of parameter
marker values) successfully executed.
The SQLERRD[2] contains the number of rows inserted, updated, or
deleted.
See Section 7.3 for more information about the SQLCA structure.
•

Batched execution stops (the sqlsrv_execute routine returns) if there is an
error.

•

If batched execution would result in a message buffer overflow, the
API sends the contents of the buffer to the server but does not request
execution.

•

During batched execution, you cannot call API routines other than
sqlsrv_execute; you must complete the batched execution before calling
other routines.

Errors
SQLSRV_INTERR

Internal error.

SQLSRV_INVARG

Invalid routine parameter.

SQLSRV_INVASC

Invalid association identifier.

SQLSRV_INVEXEFLG

Invalid execute flag.

API Routines 6-17

sqlsrv_execute-Execute Prepared Statement

SQLSRV_INVSQLDA

Invalid SQLDA structure.

SQLSRV_INVSTMID

Invalid statement identifier.

SQLSRV_MULTI_ACT

A batched sqlsrv_execute or sqlsrv_fetch_many
context is active.

SQLSRV_NETERR

DECnet returned an error.

SQL Errors
SQL_BAD_TXN_STATE

6-18

Invalid transaction state.

SQL_DEADLOCK

Deadlock encountered.

SQL_INTEG_FAIL

Constraint failed.

SQL_LOCK.._CONFLICT

Lock conflict.

SQL_NOT_VALID

Valid-if failed.

SQL_NO_DUP

Duplicate on index.

SQL_RDBERR

Rdb/VMS returned an error.

SQL_ROTXN

Read/write operation in read-only transaction.

SQL_UDCURNOPE

Cursor in update or delete is not open.

SQL_UDCURNPOS

Cursor in update or delete is not positioned on a
record.

API Routines

(

sqlsrv_execute_immediate-Prepare and Execute Statement

6.7 sqlsrv_execute_immediate-Prepare and
Execute Statement
The sqlsrv_execute_immediate routine prepares and executes an SQL
statement that does not contain parameter markers, and updates the SQLCA
with a value representing the number of rows modified as a result of the SQL
statement execution.

VAX Format
SQLSRV$EXECUTE_IMMEDIATE

associate_id ,database_id ,sql_statement

C Format
extern int sqlsrv_execute_immediate(
char *associate id,
long int database_id,
char *sql_statement);

Parameters
associate_ id
data type:
access:
mechanism:

undefined
read
by reference

Handle used to identify the active association.
database_ id
data type:
access:
mechanism:

longword (signed)
read
by value

This parameter must be 0. Databases are referenced within the SQL statement
syntax.

API Routines

6-19

sqlsrv_execute_immediate-Prepare and Execute Statement
sq/_ statement
data type:
access:
mechanism:

character string
read
by reference

A null-terminated string containing the SQL statement to be prepared and
executed by dynamic SQL.

Errors
SQLSRV_INTERR

Internal error.

SQLSRV_INVARG

Invalid routine parameter.

SQLSRV_INVASC

Invalid association identifier.

SQLSRV_MULTI_ACT

A batched sqlsrv_execute or sqlsrv_fetch_many
context is active.

SQLSRV_NETERR

DECnet returned an error.

SQL Errors

6-20

SQL_BAD_TXN_STATE

Invalid transaction state.

SQL_INTEG_FAIL

Constraint failed.

SQL_LOCK_CONFLICT

Lock conflict.

SQL_NOT_VALID

Valid-if failed.

SQL_NO_DUP

Duplicate on index.

SQL_RDBERR

RdbNMS returned an error.

SQL_ROTXN

Read/write operation in read-only transaction.

SQL_UDCURNOPE

Cursor in update or delete is not open.

SQL_UDCURNPOS

Cursor in update or delete is not positioned on a
record.

API Routines

sqlsrv_fetch-Get Row from Result Table

6.8 sqlsrv_fetch-Get Row from Result Table
The sqlsrv_fetch routine fetches a row of data into a select list SQLDA.

VAX Format
SQLSRV$FETCH

associate_id ,cursor_name ,direction ,row_number ,select_list_sqlda

C Format
extern int sqlsrv fetch(
char *associate_id,
char *cursor name,
short int di~ection,
long int row_number,
struct SQLDA *select_list_sqlda);

Parameters
associate_ id
data type:
access:
mechanism:

undefined
read
by reference

Handle used to identify the active association.
cursor_name
data type:
access:
mechanism:

character string
read
by reference

A null-terminated string used to identify the open cursor.
direction
data type:
access:
mechanism:

word (signed)
read
by value

This parameter is reserved and must be 0.

API Routines 6-21

sqlsrv_fetch-Get Row from Result Table
row_number
data type:
access:
mechanism:

longword (signed)
read
by value

This parameter is reserved and must be 0.

select_llst_ sqlda
data type:
access:
mechanism:

longword (unsigned)
modify
by reference

The select list SQLDA structure in which to store the row.

Notes
• A return value of SQL_EOS indicates end of data, that is, no more rows
appear in the result table. A call to the sqlsrv_fetch routine that returns a
status code of SQL_EOS does not return any data in the SQLDA. All rows
in the result table were returned by the preceding fetches.
• Although it modifies only one SQLDA structure per call, the sqlsrv_fetch
routine can download several rows of data when called within a
sqlsrv_fetch_many context. See Section 4.3.2 and Section 6.9.

Errors
SQLSRV_CNDERR

Filter run-time error.

SQLSRV_INTERR

Internal error.

SQLSRV_INVARG

Invalid routine parameter.

SQLSRV_INVASC

Invalid association identifier.

SQLSRV_INVCURNAM

Invalid cursor name.

SQLSRV_INVSQLDA

Invalid SQLDA structure.

SQLSRV_MULTI_ACT

A batched sqlsrv_execute or sqlsrv_fetch_many
context is active.

SQLSRV_NETERR

DECnet returned an error.

6-22 API Routines

sqlsrv_fetch-Get Row from Result Table

SQL Errors
SQL_CURNOTOPE

Cursor is not open.

SQL_DEADLOCK

Deadlock encountered.

SQL_EOS

SELECT or cursor got to end of stream.

SQL_LOCK_CONFLICT

Lock conflict.

SQL_NULLNOIND

NULL value and no indicator variable.

API Routines 6-23

sqlsrv_fetch_many-Get Multiple Rows from Result Table

6.9 sqlsrv_fetch_many-Get Multiple Rows
from Result Table
The sqlsrv_fetch_many routine causes the sqlsrv_fetch routine to transfer
multiple rows of data from the server, as described in Section 4.3.2. Frequently,
this reduces the number of client/server messages required to complete the
operation. By default, sqlsrv_fetch gets one row of data at a time.

VAX Format
SOLSRV$FETCH_MANY

associate_id ,cursor_name ,direction ,repeat_count

C Format
extern int sqlsrv fetch many(
char *a;sociate_id,
char *cursor_name,
short int direction,
short int repeat_count);

Parameters
assoclate_ld
data type:
access:
mechanism:

undefined
read
by reference

Handle used to identify the active association.
cursor_name
data type:
access:
mechanism:

character string
read
by reference

A null-terminated string used to identify the open cursor.

(
6-24

API Routines

sqlsrv_fetch_many-Get Multiple Rows from Result Table
direction
data type:
access:
mechanism:

word (signed)
read
by value

This parameter is reserved and must be 0.
repeat_ count
data type:
access:
mechanism:

word (signed)
read
by value

The number of rows to fetch. A value of 0 fetches the entire result table. A
value other than 0 fetches that number of rows. For example, an application
might fetch enough rows to fill one screen.

Notes
• When you specify a repeat_count other than 0, your application must call
the sqlsrv_fetch_many routine again once the specified number of rows
have been fetched. Otherwise, the API returns to the default behavior (one
row for each call to the sqlsrv_fetch routine).
•

During an sqlsrv_fetch_many operation, you cannot call API routines other
than sqlsrv_fetch. In other words, you must complete the operation before
calling other routines.

• A call to the sqlsrv_close_cursor routine aborts an sqlsrv_fetch_many
operation.
• SQL/Services prevents buffer overflow on the client in a manner that is
transparent to your application.
•

By default, the sqlsrv_fetch routine downloads only one row of data. That
way, your application can execute the SQL statements UPDATE ...
WHERE CURRENT OF cursor-name and DELETE ... WHERE
CURRENT OF cursor-name without having to reset the context.

API Routines 6-25

sqlsrv_fetch_many-Get Multiple Rows from Result Table

Errors
SQLSRV_INTERR

Internal error.

SQLSRV_INVARG

Invalid routine parameter.

SQLSRV_INVASC

Invalid association identifier.

SQLSRV_INVCURNAM

Invalid cursor name.

SQLSRV_INVREPCNT

Invalid repeat count.

SQLSRV_MULTl_ACT

A batched sqlsrv_execute or sqlsrv_fetch_many
context is active.

(
6-26 API Routines

sqlsrv_free _sqlda_data-Release Variables

6.10 sqlsrv_free_sqlda_data-Release
Variables
The sqlsrv_free_sqlda_data routine frees data and indicator variables that
were dynamically allocated by the sqlsrv_allocate_sqlda_data routine. Your
application passes an SQLDA structure to the API, which frees the variables
and writes zeros into the SQLDATA and SQLIND fields of the SQLVAR array.

VAX Format
SQLSRV$FREE_SQLDA_DATA

associate_id ,sqlda_str

C Format
extern int sqlsrv_free_sqlda_data(
char *associate_id,
struct SQLDA *sqlda_str);

Parameters
associate_ Id
data type:
access:
mechanism:

undefined
read
by reference

Handle used to identify the active association.
sqlda_str
data type:
access:
mechanism:

longword (unsigned)
modify
by reference

An SQLDA structure to modify.

API Routines 6-27

sqlsrv_free_sqlda_data-Release Variables

Errors
SQLSRV_INTERR

Internal error.

SQLSRV_INVASC

Invalid association identifier.

SQLSRV_INVSQLDA

Invalid SQLDA structure.

SQLSRV_MULTI_ACT

A batched sqlsrv_execute or sqlsrv_fetch_many
context is active.

SQLSRV_SQLDA_NOTALL

Attempt to deallocate static memory.

6-28 API Routines

sqlsrv_get_environment-Return Environment Variable Values

6.11 sqlsrv_get_environment-Return
Environment Variable Values
The sqlsrv_get_environment routine returns the values of environment
variables (as described in Section 5.2).

VAX Format
SQLSRV$GET_ ENVIRONMENT

associate_id ,env_ str_array_count ,env_ str_array

C Format
extern int sqlsrv get environment(
char *associate_id,
unsigned short int env str array count,
struct SQLSRV_ENV_STR *env=str_array);

Parameters
associate_ld
data type:
access:
mechanism:

undefined
read
by reference

Handle used to identify the active association.
env_ str_array_count
data type:
word (unsigned)
access:
read
mechanism:
by value

Specifies the number of env_str_array entries.
env_ str_array
data type:
access:
mechanism:

structure array
modify
by reference

Array of SQLSRV_ENV_STR structures (described in Section 7. 7), each of
which contains the information necessary to get an environment variable.

API Routines

6-29

sqlsrv_get_environment-Return Environment Variable Values

(
Description
Your application allocates an array of SQLSRV_ENV_STR structures and
sets the values of the ENV_TAG fields, which identify specific environment
variables. 'lb request information on all environment variables, set the
env_str_array[O].ENV_TAG field to SQLSRV_ENV_ALL. The env_str_array
must be large enough to receive all of the values. The number of values
returned is placed in the SQLCA.SQLERRD[2] field.

Errors

6-30

SQLSRV_INTERR

Internal error.

SQLSRV_INVARG

Invalid routine parameter.

SQLSRV_INVASC

Invalid association identifier.

SQLSRV_INVENVTAG

Invalid environment tag.

SQLSRV_INVENVVAR

Invalid environment variable.

SQLSRV_MULTI_ACT

A batched sqlsrv_execute or sqlsrv_fetch_many
context is active.

SQLSRV_NETERR

DECnet returned an error.

API Routines

sqlsrv_open_cursor-Create Result Table

6. 12 sqlsrv_open_cursor-Create Result Table
The sqlsrv_open_cursor routine opens a cursor for a prepared SELECT
statement. In SQUServices Version 3.1, sqlsrv_open_cursor reduces network
traffic by implicitly invoking the dynamic SQL statement DECLARE CURSOR.

VAX Format
SQLSRV$0PEN_CURSOR

associate_id ,cursor_name ,statement_id
,parameter_marker_sqlda

C Format
extern int sqlsrv open cursor(
char *;ssociate id,
char *cursor name,
long int statement id,
struct SQLDA *parameter_marker_sqlda);

Parameters
assoclate_ld
data type:
access:
mechanism:

undefined
read
by reference

Handle used to identify the active association.
cursor_name
data type:
access:
mechanism:

character string
read
by reference

A null-terminated string containing the result table identifier. All cursor
operations, including positional UPDATE and DELETE statements, must use
the cursor_name to identify the cursor.
statement_ Id
data type:
access:
mechanism:

longword (signed)
read
by value

API Routines 6-31

sqlsrv_open_cursor-Create Result Table
The identifier of the prepared SELECT statement. The sqlsrv_open_cursor
routine maps the cursor_name to the prepared statement.

parameter_marker_sqlda
data type:
longword (unsigned)
access:
read
mechanism:
by reference
An SQLDA structure defining the parameter marker values for the prepared
SELECT statement.

Errors
SQLSRV_INTERR
SQLSRV_INVARG

Internal error.

SQLSRV_INVASC

Invalid association identifier.

SQLSRV_INVCURNAM

Invalid cursor name.

Invalid routine parameter.

SQLSRV_INVSQLDA

Invalid SQLDA structure.

SQLSRV_INVSTMID

Invalid statement identifier.

SQLSRV_MULTI_ACT

A batched sqlsrv_execute or sqlsrv_fetch_many
context is active.

SQLSRV_NETERR

DECnet returned an error.

SQL Errors

6-32

SQL_CURALROPE

Cursor is already open.

SQL_LOCK_CONFLICT

Lock conflict.

SQL_RDBERR

RdbNMS returned an error.

API Routines

sqlsrv_prepare-Compile Statement and Initialize Structures

6. 13 sqlsrv_prepare-Compile Statement and
Initialize Structures
The sqlsrv_prepare routine prepares (compiles) the input SQL statement and
returns a value that identifies the prepared statement. It also initializes
SQLDA structures describing the parameter markers and select list items
in the SQL statement (it implicitly invokes the dynamic SQL DESCRIBE
statement to reduce message traffic).

VAX Format
SQLSRV$PR EPA RE

associate_id ,database_id ,sql_statement ,statement_id
,parameter_marker_sq Ida, select_list_sqlda

C Format
extern int sqlsrv_prepare(
char *associate_id,
long int database_id,
char *sql statement,
long int *statement id,
struct SQLDA **para;eter_marker_sqlda,
struct SQLDA **select_list_sqlda);

Parameters
associate_id
data type:
access:
mechanism:

undefined
read
by reference

Handle used to identify the active association.
database_ id
data type:
access:
mechanism:

longword (signed)
read
by value

This parameter must be 0. Databases are referenced within the SQL statement
syntax.

API Routines

6-33

sqlsrv_prepare-Compile Statement and Initialize Structures
sq/_ statement
data type:
access:
mechanism:

character string
read
by reference

A null-terminated string containing the SQL statement to be prepared.
statement_ld
data type:
access:
mechanism:

longword (signed)
write
by reference

The identifier used in all subsequent references to the prepared statement.
parameter_marker_sqlda
data type:
longword (unsigned)
access:
modify/write
mechanism:
by reference

An SQLDA structure used for parameter markers. If the value passed by the
caller is the address of an existing SQLDA structure, the API writes metadata
into that structure. If the SQL statement contains one or more parameter
markers("?" placeholders), there must be at least one SQLVAR structure for
each parameter marker.
If the value passed by the caller is NULL, the API determines whether an
SQLDA structure is needed. If an SQLDA is needed, the API performs the
following operations; otherwise it leaves the value NULL:

• Dynamically allocates an SQLDA structure containing the requisite
number of SQLVAR structures
• Writes parameter marker metadata into the SQLDA
• Returns the address of the SQLDA
select_llst_sqlda
data type:
access:
mechanism:

longword (unsigned)
modify/write
by reference

An SQLDA structure used for select list items. If the value passed by the
caller is the address of an existing SQLDA structure, the API writes metadata

6-34 API Routines

sqlsrv_prepare-Compile Statement and Initialize Structures
into that structure. If the SQL statement is a SELECT, there must be at least
one SQLVAR structure for each select list item.
If the value passed by the caller is NULL, the API determines whether an
SQLDA structure is needed. If an SQLDA is needed, the API performs the
following operations; otherwise it leaves the value NULL:
•

Dynamically allocates an SQLDA structure containing the requisite
number of SQLVAR structures

• Writes select list metadata into the SQLDA
•

Returns the address of the SQLDA

Description
In an SQLDA strncture returned by the sqlsrv_prepare routine, the
SQLVARARY[ ].SQLDATA (address of data variable) and
SQLVARARY[ ].SQLIND (address of indicator variable) fields are NULL.
Before calling the sqlsrv_execute routine, your application must allocate data
and indicator variables and must write the addresses of those variables into
SQLVARARY[ J.SQLDATA and SQLVARARY[ J.SQLIND, respectively.
Your application can perform those functions itself, or can call the
sqlsrv_allocate_sqlda_data routine to dynamically allocate the variables and to
write the addresses into the SQLDA.
'fypically, an application that finishes processing one SQL statement before
preparing the next SQL statement would use the sqlsrv_prepare routine to
allocate SQLDA strnctures and the sqlsrv_allocate_sqlda_data routine to
allocate data and indicator variables. An application that prepares more than
one SQL statement at a time and thus must use several different SQLDA
structures at the same time, can allocate as many as required and pass them
to the sqlsrv_prepare routine. Note, however, that you cannot use the
sqlsrv_release_statement or sqlsrv_free_sqlda_data routines to free memory
explicitly allocated by your application.

Notes
You must supply valid values for the parameter_marker_sqlda and
select_list_sqlda parameters. If the SQL statement is known not to contain
parameter markers or not to be a SELECT statement, supply NULL values.

API Routines 6-35

sqlsrv_prepare-Compile Statement and Initialize Structures

Errors
SQLSRV_INTERR

Internal error.

SQLSRV_INVARG

Invalid routine parameter.

SQLSRV_INVASC

Invalid association identifier.

SQLSRV_INVSQLDA

Invalid SQLDA structure.

SQLSRV_MULTI_ACT

A batched sqlsrv_execute or sqlsrv_fetch_many
context is active.

SQLSRV_NETERR

DECnet returned an error.

SQLSRV_NO_MEM

API memory allocation failed.

SQL Errors
SQL_RDBERR

6-36

API Routines

Rdb/VMS returned an error.

sqlsrv_release-Release Client/Server Association

6.14 sqlsrv_release-Release Client/Server
Association
The sqlsrv_release routine commits active transactions on the server and
requests an orderly termination of the association, which disconnects the
network link and frees client association resources.

VAX Format
SQLSRV$RELEASE

associate_id [,stats]

C Format
extern int sqlsrv_release(
char *associate_id,
char *stats);

Parameters
associate_/d
data type:
access:
mechanism:

undefined
read
by reference

Handle used to identify the active association.

stats (optional)
data type:
access:
mechanism:

undefined
modify
by reference

This parameter must be 0 (a null pointer).

API Routines

6-3 7

sqlsrv_release-Release Client/Server Association

Errors

6-38

SQLSRV_INTERR

Internal error.

SQLSRV_INVASC

Invalid association identifier.

SQLSRV_MULTI_ACT

A batched sqlsrv_execute or sqlsrv_fetch_many
context is active.

SQLSRV_NETERR

DECnet returned an error.

API Routines

sqlsrv_release_statement-Release Statement Resources

6.15 sqlsrv_release_statement-Release
Statement Resources
The :';qlsrv_release_statement routine frees all resources associated with one or
more TJrepared statements (including dynamically allocated SQLDA structures)
for both the client and server, and updates SQLERR[2] with the number of
statements that were released. Processing stops when an error is encountered.

VAX Format
SQLSRV$RELEASE_STATEMENT associate_id ,statement_id_count ,statement_id_array

C Format
extern int sqlsrv_release_statement(
char *associate id,
short int state;ent id count,
long int *statement=id=array);

Parameters
associate_id
data type:
access:
mechanism:

undefined
read
by reference

Handle used to identify the active association.
statement_ id_ count
data type:
word (signed)
access:
read
mechanism:
by value

The number of statement identifiers passed in the statement_id_array.

API Routines 6-39

sqlsrv_release_statement-Release Statement Resources
statement_id_array
data type:
longword (signed) array
access:
read
mechanism:
by reference

An array containing the identifiers (statement_id parameters returned by the
sqlsrv_prepare routine) of the statements to free.

Errors

6-40

SQLSRV_INTERR

Internal error.

SQLSRV_INVARG

Invalid routine parameter.

SQLSRV_INVASC

Invalid association identifier.

SQLSRV_INVSTMID

Invalid statement identifier.

SQLSRV_MULTI_ACT

A batched sqlsrv_execute or sqlsrv_fetch_many
context is active.

SQLSRV_NETERR

DECnet returned an error.

API Routines

sqlsrv_set_environment-Set Environment Variable Values

6. 16 sqlsrv_set_environment-Set Environment
Variable Values
The sqlsrv_set_environment routine sets the values of environment variables
(as described in Section 5.2).

VAX Format
SQLSRV$SET_ENVIRONMENT

associate _id ,env_str_array_count ,env_str_array

C Format
extern int sqlsrv set environment(
char *associate_id,
unsigned short env str array count,
struct SQLSRV_ENV_STR *env_str_array);

Parameters
associate_id
data type:
access:
mechanism:

undefined
read
by reference

Handle used to identify the active association.
env_ str_array_count
data type:
word (unsigned)
access:
read
mechanism:
by value
The number of elements in the env_str_array.
env_str_array
data type:
access:
mechanism:

longword (unsigned)
read
by reference

An array of SQLSRV_ENV_STR structures (described in Section 7. 7), each of
which contains the information necessary to set an environment variable.

API Routines

6-41

sqlsrv_set_environment-Set Environment Variable Values

Description
Your application allocates an array of SQLSRV_ENV_STR structures, each of
which describes an environment variable, and sets the values of the ENV_TAG
and ENV_VALUE fields.

Errors
SQLSRV_INTERR

Internal error.

SQLSRV_INVARG

Invalid routine parameter.

SQLSRV_INVASC
SQLSRV_INVENVTAG
SQLSRV_INVENVVAR

Invalid association identifier.

SQLSRV_MULTI_ACT

A batched sqlsrv_execute or sqlsrv_fetch_many
context is active.

SQLSRV_NETERR

DECnet returned an error.

6-42 API Routines

Invalid environment tag.
Invalid environment variable.

sqlsrv_set_filter-Define Filter for Result Table

6. 17 sqlsrv_set_filter-Define Filter for Result
Table
The sqlsrv_set_filter routine defines a Boolean filter expression (as described
in Section 4.4) and associates the expression with a result table. When your
application calls sqlsrv_fetch, the server applies the specified filter to each
row and eliminates from the result table those rows for which the expression
returns a value of false.

VAX Format
SQLSRV$5ET_FILTER

associate_id ,cursor_name ,filter_expression ,sqlda_index_count
,sqlda_index_array ,filter_precedence

C Format
extern int sqlsrv set filter(
char *associate_id,
char *cursor name,
char *filter-expression,
short int sqlda index count,
short int *sqlda index array,
short int_ filteryrecedence);

Parameters
associate_id
data type:
access:
mechanism:

undefined
read
by reference

Handle used to identify the active association.

cursor_name
data type:
access:
mechanism:

character string
read
by reference

A null-terminated string used to identify the open cursor.

API Routines 6-43

sqlsrv_set_filter-Define Filter for Result Table
filter_ expression
data type:
access:
mechanism:

character string
read
by reference

A null-terminated string containing the filter expression applied to the result
table by the server when your application fetches a row.
sqlda_lndex_ count
data type:
word (signed)
access:
read
mechanism:
by value

The number of "?" placeholders in the filter expression.
sqlda_lndex_array
word (signed) array
data type:
access:
read
mechanism:
by value

An array of zero-based indices into the select list SQLDA structure associated
with cursor_name. The first array element corresponds to the first"?"
placeholder in the filter expression, and so forth.
filter_precedence
data type:
access:
mechanism:

word (signed)
read
by value

This parameter must be 0.

Notes
• You can associate only one filter expression with a cursor.
• You can use environment variables to control the way that dates in filter
expressions are parsed (see sqlsrv_set_environment).

6-44

API Routines

sqlsrv_set_filter-Define Filter for Result Table

Errors
SQLSRV_FTRSYNERR

Syntax error in filter expression.

SQLSRV_INTERR

Internal error.

SQLSRV_INVARG

Invalid routine parameter.

SQLSRV_INVASC

Invalid association identifier.

SQLSRV_INVCURNAM

Invalid cursor name.

SQLSRV_INVIDX

Invalid sqlda_index_array.

SQLSRV_MULTI_ACT

A batched sqlsrv_execute or sqlsrv_fetch_many
context is active.

SQLSRV_NETERR

DECnet returned an error.

API Routines

6-45

(

7
Data Structures

This chapter describes the data structures that SQUServices uses to
communicate with the client application. Some of the data structures (the
SQLDA and SQLCA) are identical in allocation but not in usage with those in
dynamic SQL. Those structures are described in detail in the VAX Rdb/VMS
SQL Reference Manual. This manual provides relatively brief descriptions and
points out the differences in usage.

7. 1 Documentation Format
Each SQL/Services data structure is documented using a structured format
called a template. The sections of the template are shown in Table 7-1, along
with the information that is presented in each section and the format used to
present the information.
Table 7-1

Sections in the Data Structure Template

Section

Description

Structure Name

Appears at the top of the page, followed by the English
equivalent.

Overview

Appears directly below the structure name. The overview
explains, usually in one or two sentences, the purpose of the
structure.

Diagram

Shows the layout of the structure on a 32-bit machine
architecture.

Fields

Gives detailed information about each field.

The Fields section contains detailed information about each field in the data
structure. Fields are described in the order in which they appear in the
structure.

Data Structures

7-1

Documentation Format
The following format is used to describe each field:
field-name
data type:

the data type of the specific field (see Table 6-3)

C declaration:

how that field is declared in the SQUServices include files

set by:

whether the value of the field is set by the API, the application
program, or both

used by:

whether the value of the field is used by the API, the application
program, or both

In addition, the Fields section contains at least one paragraph of text
describing the purpose of the field.

7-2 Data Structures

ASSOCIATE_STR-Association Structure

7 .2 ASSOCIATE_STR-Association Structure
The association structure is a parameter that is passed to the sqlsrv_associate
routine to enable or disable API functions such as execution logging, userdefined memory allocation, local input/output, and alternative error message
buffering. The ASSOCIATE_STR is defined in the include file SQLSRV.H.

SERVER_LOG

CUENT_LOG

VERSION

LOCAL_FLAG

MEMORY_ROUTINE

FREE_MEMORY_ROUTINE

ERRBUFLEN

RESERVED

ERRBUF

Fields
CLIENT_LOG
data type:
C declaration:
set by:
used by:

word (unsigned)
unsigned short int CLIENT_LOG
program

API

Specifies the type of execution logging to be enabled or disabled on the client
system (see Section 4.5). The following constants are defined in the include file
SQLSRV.H:
SQLSRV_LOG_DISABLED

Disables logging (default)

SQLSRV_LOG_ASSOCIATION

Enables association logging

SQLSRV_LOG_ROUTINE

Enables API routine logging

SQLSRV_LOG_PROTOCOL

Enables message protocol logging

SQLSRV_LOG_SCREEN

Sends logging output to the video display on the
client system as well as to the log file

To enable more than one type of logging, add the appropriate com~tants.

Data Structures

7-3

ASSOCIATE_STR-Association Structure
SERVER_LOG

data type:
C declaration:
set by:
used by:

word (unsigned)
unsigned short int SERVER_LOG
program
API

Enables or disables message protocol logging on the server system (see
Section 4.5). The following constants are defined in the include file SQLSRV.H:
SQLSRV_LOG_DISABLED

Disables logging (default)

SQLSRV_LOG_PROTOCOL

Enables message protocol logging

LOCAL_FLAG ·

data type:
C declaration:
set by:
used by:

word (signed)
short int LOCAL_FLAG
program
API

Specifies whether SQL/Services can use local input/output instead of DECnet
input/output in the association and subsequent messages. Local input/output
is valid (and preferred) only when the server is on the same VAX system as
the application. However, a process can have only one local association at a
time. The user name and password parameters to the sqlsrv_associate call are
ignored; those associated with the current process are used instead.
0

DECnet input/output (default)

local input/output

VERSION

data type:
C declaration:
set by:
used by:

word (signed)
short int VERSION
reserved
unused

Must be 0.
MEMORY_ ROUTINE

data type:
C declaration:
set by:
used by:

pointer
char *(*MEMORY_ROUTINE) ()
program
API

A pointer to the entry point of a user-specified routine to be called by the
API for memory allocation. This feature is for client environments in which

7-4 Data Structures

ASSOCIATE_STR-Association Structure
a limited amount of memory is available. The default value is NULL, which
causes the API to use the portable C routine malloc() for all memory allocation.

FREE_MEMORY_ROUTINE
data type:
C declaration:
set by:
used by:

pointer
char (*FREE_MEMORY_ROUTINE) ()
program

API

A pointer to the entry point of a user-specified routine to be called by the API
for memory deallocation. The default value is NULL, which causes the API to
use the portable C routine free() for all memory deallocation.

RESERVED
data type:
C declaration:
set by:
used by:

word (signed)
short int RESERVED
program
unused

This field is reserved.

ERRBUFLEN
data type:
C declaration:
set by:
used by:

word (signed)
shortinf ERRBUFLEN
program

API

The length in bytes of ERRBUF. The recommended length is 512 bytes if
sufficient memory is available.

ERRBUF
data type:
C declaration:
set by:
used by:

pointer
char *ERRBUF

API
program

The address of a buffer in which to store ASCII error messages from
SQL/Services, SQL, Rdb, or VMS. If you supply a valid address, the API writes
error messages into this buffer instead of the SQLCA.SQLERRM.SQLERRMC
buffer, which is only 70 bytes long and may be too small to contain the entire
message. If you supply a NULL value, the API writes error messages into the
SQLCA.SQLERRM.SQLERRMC buffer.

Data Structures

7-5

SQLCA-SQL Communications Area

(

7.3 SQLCA-SQL Communications Area
The SQLCA structure is used to store information when an error occurs. This
structure is defined in the include file SQLSRVCA.H along with the error codes
generated by SQL/Services.

SQLCAID[3] "C"

SQLCAI 0[2) "L"

SQLCAID[1] "Q"

SQLCAID[O] "S"

SQLCAID[7] res

SQLCAID[6] res

SQLCAID[5] 0

SQLCAID[4] "A"

SQLCABC

SQLCODE

0
0

SQLERRM.SQLERRML
~~

7-6

SQLERRM.SQLERRMC[] (70 bytes)

SQLERRD[O]

SQLERRD[1]

SQLERRD[2]

SQLERRD[3]

SQLERRD[4]

SOLERRD[5]

SOLWARN3

SQLWARN2

SQLWARN1

SQLWARNO

SQLWARN7

SOLWARN6

SQLWARN5

SQLWARN4

SQLEXT[3]

SQLEXT[2]

SQLEXT[1]

SQLEXT[O]

SQLEXT[7]

SQLEXT[6]

SQLEXT[5]

SQLEXT[4]

Data Structures

SQLCA-SQL Communications Area
The SQL/Services SQLCA is based on the SQL SQLCA, which is described in
detail in the VAX Rdb/VMS SQL Reference Manual.

Fields
SQLCAID
data type:
C declaration:
set by:
used by:

character string
char SQLCAID (8)
API
unused

Structure identification field, present only for compatibility with SQL. Contaim
the null-terminated string "SQLCA" followed by two reserved bytes.

SQLCABC
data type:
C declaration:
set by:
used by:

longword (signed)
long int SQLCABC
API
program

Contains the size, in bytes, of the SQLCA structure. The value of this field is
always 128.

SQLCODE
data type:
C declaration:
set by:
used by:

longword (signed)
long int SQLCODE
API
program

Contains the error status for the most recently invoked SQL/Services routine.
A positive value indicates a warning, a negative value indicates an error, and
a 0 value indicates success. The include file SQLSRVCA.H contains the error
messages that correspond to all of the possible values of SQLCODE. The file
SQLSRV$MSG.DOC contains explanations of the errors and suggests user
actions.

SQLERRM.SQLERRML
data type:
C declaration:
set by:
used by:

word (signed)
short int SQLERRML
API
program

The length, in bytes, of the error message text returned in SQLERRMC.

Data Structures

7-7

SQLCA-SQL Communications Area

(
SQLERRM.SQLERRMC
data type:
character string
C declaration:
char SQLERRMC (70)
set by:
API
used by:
program

An ASCII string that describes the error (which may be from SQL/Services,
SQL, Rdb, or VMS) in more detail. Because some error messages are longer
than 70 bytes, you can use the ASSOCIATE_STR.ERRBUF field to define a
longer buffer (see Section 7 .2).
SQLERRD
data type:
C declaration:
set by:
used by:

array of longword (signed)
long int SQLERRD (6)
API
program

An array of six integers as described in Section 7.4.
SQLWARNn
data type:
C declaration:
set by:
used by:

character
charSQLWARNO ... SQLWARN7
unused
unused

A series of eight 1-character fields that SQL and the API do not use.
SQLEXT
data type:
C declaration:
set by:
used by:

character string
char SQLEXT (8)
unused
unused

Not used by the API.

7-8

Data Structures

SQLERRD-Part of SQLCA

7.4 SQLERRD-Part of SQLCA
The SQLERRD array contains six elements. SQL/Services uses only the first
three elements.

SQLERRD Elements
· SQLERRD[O]

Contains the detailed error code when the SQLCODE field is
SQLSRV_NETERR or SQLSRV_SRVERR, as defined in the include file
SQLSRVCA.H. Information about these error codes can be found at the
locations listed in Table 7-2.
Table 7-2

Error Code Files

Operating
System

Fiie Specification

Description

VMS

SYS$LIBRARY:SSDEF.H

System service return status code
definitions

MS-DOS

DERRNO.H

DECnet error codes (provided with
the DECnet-DOS software)

ULTRIX

/usr/include/errno.h

DECnet error codes (provided with
the DECnet-ULTRIX software)

Note This feature is an extension to Rdb/VMS SQL and ANSI SQL.
SQLERRD[1}

The number of rows processed successfully in a batched execution.
Note This feature is an extension to Rdb I VMS SQL and ANSI SQL.
SQLERRD[2}

The value placed in the SQLERRD[2] field depends on the type of SQL
statement executed, as shown in Table 7-3.

Data Structures

7-9

SQLERRD-Part of SQLCA

Table 7-3

(

Values Placed in the SQLCA.SQLERRD(2) Field

SQL Statement

API Routine

Value

INSERT1

sqlsrv_execute or
sqlsrv_execute_iininediate

The nuinber of rows stored.

UPDATE 1

sqlsrv_execute or
sqlsrv_execute_iininediate

The nuinher of rows Inodified.

DELETE 1

sqlsrv_execute or
sqlsrv_execute_iininediate

The nuinber of rows deleted.

FETCH

sqlsrv_fetch

The nuinber of the row on
which the cursor is currently
positioned. This is Inaintained
within a sqlsrv_fetch_Inany
context with the restriction
that positional SQL stateinents
cannot be invoked.

OPEN

sql_open_cursor

RELEASE

sqlsrv_release_stateinent

The nuinber of stateinents
released.

n/a

sqlsrv_get_environinent

The nUinber of environinent
variable values returned in
sql_str_array.

1
For INSERT, UPDATE, and DELETE statements that operate on multiple rows of data ("batched"
execution), the value of SQLDERR[2] reflects the total number of rows modified.

7-1 O Data Structures

SQLDA-SQL Descriptor Area

7.5 SQLDA-SQL Descriptor Area
The SQLDA structure contains SQL parameter marker and select list metadata
as well as pointers to data and indicator variables. It is defined in the include
file SQLSRVDA.H.
The SQUServices SQLDA is identical to the SQLDA structure in SQL. For
additional information on the SQLDA, read the dynamic SQL chapter in the
VAX Rdb /VMS Guide to Using SQL and the SQLDA appendix in the VAX
Rdb /VMS SQL Reference Manual.

SQLDAID[3] "D"

SQLDAID[2] "L"

SQLDAID[1] "O"

SQLDAID[O] "S"

SQLDAID[7) res

SQLDAID[6] res

SQLDAID[5] 0

SQLDAID[4] "A"

0
0

SOLA BC
SOLD

SOLN

,.{.,

'-4...

l.__~~~~~~~~~-S-Q-L-VA_R_A_R_Y_[_O.~.-·n_]_(4_4_b_y-te_s_)~~~~~~~~~---r

Fields
SQLDAID
data type:
C declaration:
set by:
used by:

character string
char SQLDAID(8)
API

unused

Structure identification field, present only for compatibility with dynamic SQL.
Contains the null-terminated string "SQLDA" followed by two reserved bytes.
SQLABC
data type:
C declaration:
set by:
used by:

longword (signed)
long int SQLABC
API

unused

The size, in bytes, of the SQLDA structure.

Data Structures

7-11

SQLDA-SQL Descriptor Area
SQLN

data type:
C declaration:
set by:
used by:

word (signed)
short int SQLN
see following text
API

The number of elements in the SQLVARARY. If the API allocated the SQLDA
structure, this value is the same as the SQLD field. If your application
allocated its own SQLDA structure, it must supply this value. In that case, the
SQLN field specifies the maximum number of select list items or parameter
marker items that can exist in an SQL statement that is prepared with a
particular SQLDA; a call to the sqlsrv_prepare routine with an SQLVARARY
that is too small returns an error.
SQLD

data type:
C declaration:
set by:
used by:

word (signed)
short int SQLD
API

program

The number of parameter markers or select list items in a prepared SQL
statement. In an SQLDA structure that was allocated by the API, this value is
the same as the SQLN field (the number of elements in the SQLVARARY).
SQLVARARY

data type:
C declaration:
set by:
used by:

array of structures
struct SQLVAR SQLVARARY( 1)
see Section 7.6
see Section 7.6

An array of SQLVAR structures (see Section 7 .6), each of which describes one
select list item or one parameter marker item.

7-12

Data Structures

SQLVAR-Parameter Marker or Select List Item

7.6 SQLVAR-Parameter Marker or Select List
Item
Each SQLVAR structure describes one select list item or parameter marker.

SQ LL EN

SQLTYPE
SQLDATA

SOUND

0
SQLNAME_LEN

SQLNAME[1)

SQLNAME[O)

SQLNAME[5)

SQLNAME[4]

SQLNAME[3]

SQLNAME[2)

SQLNAME[9]

SQLNAME[8]

SQLNAME[7]

SQLNAME[6)

SQLNAME[13)

SQLNAME[12)

SQLNAME[11)

SQLNAME[1 OJ

SQLNAME[17)

SQLNAME[16)

SQLNAME[15]

SQLNAME[14]

SQLNAME[21]

SQLNAME[20)

SQLNAME[19]

SQLNAME[18]

SQLNAME[25]

SQLNAME[24)

SQLNAME[23)

SQLNAME[22)

SQLNAME[29]

SQLNAME[28)

SQLNAME[27)

SQLNAME[26]

Fields
SQLTYPE
data type:
C declaration:
set by:
used by:

word (signed)
short int SQLTYPE
API
program

The SQL data type for the SQLVAR entry. This value represents the
SQL/Services data type as defined in the include file SQLSRVDA.H.

Data Structures

7-13

SQLVAR-Parameter Marker or Select List Item
idefine
idefine
idefine
idefine

SQLSRV_ASCII_STRING
SQLSRV_GENERALIZED_NUMBER
SQLSRV_GENERALIZED_DATE
SQLSRV_VARCHAR

SQLLEN
data type:
C declaration:
set by:
used by:

129
130
131
132

word (signed)
short int SQLLEN
see following text
program

For SQLSRV_ASCII_STRING, SQLSRV_GENERALIZED_DATE, and
SQLSRV_VARCHAR data, the length, in bytes, of the variable pointed to
by the SQLDATA field.
For SQLSRV_GENERALIZED_NUMBER, the SQLLEN field is split in half.
The low-order byte of SQLLEN indicates the size of the data variable. The
high-order byte indicates the scale factor (the number of digits to the right of
the decimal point). Thus, a scale factor of 0 indicates that the value is either
an integer or a floating-point number in E notation. A non-zero scale factor
indicates that the value is a decimal number.
SQLDATA

data type:
C declaration:
set by:
used by:

pointer
char *SQLDATA
program or API
program and API

The address of a variable used to store data (select list items or parameter
markers). If your application allocates data variables by calling the
sqlsrv_allocate_sqlda_data routine, the API initializes this field. If your
application allocates its own data variables, it must write the address of each
variable into an SQLDATA field. In that case, the API returns an error if an
SQLLEN value is less than the length of the associated data value.
SQL/ND
data type:
C declaration:
set by:
used by:

pointer
short int *SQLIND
program or API
program and API

The address of an indicator variable for the data. (A value of-1 in the
indicator variable indicates a null data value.) If your application calls the
sqlsrv_allocate_sqlda_data routine, the API initializes this field. Otherwise,

7-14

Data Structures

(

SQLVAR-Parameter Marker or Select List Item

)
your application must allocate its own indicator variables and write the
address of each variable into an SQLIND field.
SQLNAME_LEN
data type:
C declaration:
set by:
used by:

word (signed)
short int SQLNAME_LEN
API
program

The length, in bytes, of the name stored in the SQLNAME field.
SQLNAME
data type:
C declaration:
set by:
used by:

character string
char SQLNAME(30)
API
program

The column name of the select list or parameter marker entry. The maximum
length of a column name is 30 characters. If the actual name is less than 30
characters, the API returns a null-terminated string.

)

Data Structures

7-15

SQLSRV_ENV_STR-Environment Variable Structure

(

7. 7 SQLSRV_ENV_STR-Environment Variable
Structure
The SQLSRV_ENV_STR structure contains the value of an environment
variable, as described in Section 5.2. Your application passes an array of
SQLSRV_ENV_STR structures to the sqlsrv_set_environment and
sqlsrv_get_environment routines.
The SQLSRV_ENV_STR, environment variable names, and environment
variable settings are defined in the include file SQLSRV.H. The abbreviation
"env" is used in the include file for convenience.

ENV_RESERVED

ENV_TAG

ENV_VALUE

ENV_OPT_VALUE

Fields
ENV_TAG

data type:
C declaration:
set by:
used by:

word (unsigned)
unsigned short int ENV_TAG
program
API

Identifies the environment variable to be set or returned (SQLSRV_ENV_DATE
or SQLSRV_ENV_CENTURY).
ENV_RESERVED

data type:
C declaration:
set by:
used by:

word (signed)
short int ENV_RESERVED
program
unused

This field is reserved (must be 0).

7-16

Data Structures

SQLSRV_ENV_STR-Environment Variable Structure
ENV_VALUE
data type:
C declaration:
set by:
used by:

longword (signed)
long int ENV_VALUE
program or API
API or program

The value of the environment variable. For SQLSRV_ENV_DATE, see
Table 5-2. For SQLSRV_ENV_CENTURY, see Table 5-3.
ENV_OPT_VALUE
data type:
C declaration:
set by:
used by:

pointer
char *ENV_OPT_VALUE
unused
unused

This field is reserved.

Data Structures

7-17

A
Filter Expression Functions
This appendix describes the functions that can be used to evaluate or convert
data in filter expressions.
As described in Section 6.17, SQUServices applications can call the
sqlsrv_set_filter routine to associate Boolean filter expressions with result
tables. When your application calls sqlsrv_fetch, the API applies the specified
filter to each row and eliminates from the result table those rows for which the
expression returns FALSE.
The conventions used in Appendix A are:
<>

Angle brackets indicate that you supply a data value of the type
required for the item in the brackets

<expC>

Angle brackets enclosing expC indicate character data.

<expN>

Angle brackets enclosing expN indicate numeric data.

<expD>

Angle brackets enclosing expD indicate date type data.

[]

Brackets enclose optional items

The slash indicates an either/or choice

Fiiter Expression Functions

A-1

A. l ABS
The ABS function returns the absolute value of a numeric expression. The
returned value is always a positive number.

Syntax
ABS(<expN>)

Examples
The following expression returns the difference between two numbers without
regard to their sign ( 0 ).
ABS(3)

+ ABS(-3)

The following expression returns the number of days between two dates (268).
ABS (CTOD ( "12/25/88 ") -

A-2 Fiiter Expression Functions

CTOD ( "04/01/88"))

A.2 ACOS
The ACOS arccosine function calculates and returns the angle size in radians
for any given cosine value.

Syntax
ACOS(<expN>)

Arguments
<expN>

A numeric expression that is the cosine of a particular angle. The value of the
numeric expression must be between -1.0 and +1.0 inclusive.

Usage
The response is always a number that represents an angle size in radians
between zero and pi (7r).

Examples
The following expression returns 0. 7854.
ACOS(0.7071)

Fiiter Expression Functions A-3

See Also

A-4

ASIN

Section A.4

ATAN

Section A.6

ATN2

Section A.7

cos

Section A.12

DTOR

Section A.19

Section A.33

RTOD

Section A.38

SIN

Section A.41

TAN

Section A.48

Filter Expression Functions

A.3 ASC
The ASC function returns the ASCII decimal code of the first character from a
character expression.

Syntax
ASC(<expC>)

Examples
The following expression returns 78.
ASC("Nestle")

Fiiter Expression Functions A-5

A.4 ASIN
The ASIN arcsine function calculates and returns the angle size (in radians)
for any given sine value.

Syntax
ASIN( <expN>)

Arguments
<expN>
A numeric expression that is the sine of a particular angle. The value of the
numeric expression must be between -1.0 and +1.0 inclusive.

Usage
The value returned is always a floating-point number that represents an angle
size (in radians) between -7r/2 and +7r/2.

Examples
The following expression returns .5236.
AS IN ( • 5 0 0 0 )

See Also

A-6

ACOS

Section A.2

ATAN

Section A.6

ATN2

Section A.7

cos

Section A.12

DTOR

Section A.19

RTOD

Section A.38

SIN

Section A.41

TAN

Section A.48

Fiiter Expression Functions

A.5 AT
The AT function returns a number that shows the starting position of a
character string within a second string, counting from 1.

Syntax
AT(<expC>,<expC>)

Usage
The contained character string is called a substring. If the substring is not
contained within the second expression, the function returns a zero.

Examples
The following expression returns 4.
AT ("b",

"aaabaaa")

See Also

A-8

ACOS

Section A.2

ASIN

Section A.4

ATN2

Section A.7

cos

Section A.12

DTOR

Section A.19

RTOD

Section A.38

SIN

Section A.41

TAN

Section A.48

Fiiter Expression Functions

A.7 ATN2
The ATN2 arctangent function calculates and returns the angle size (in
radians) when the cosine and sine of a given point are specified.

Syntax
ATN2( <expN 1>,<expN2>)

Arguments
<expN1>

The sine of a particular angle
<expN2>

The cosine of that same angle

Usage
The value of the expression <expNl>/<expN2> must fall within the range of +7r
and -11".
This function returns values in all four quadrants, and is equivalent to
ATAN(x/y). It is easier to use than ATAN(x/y) because it eliminates divide-byzero errors.
The returned value is always a number that represents an angle size (in
radians) between +1r and -1!".

Examples
The following expression, which shows an integrated usage of trigonometric
functions, returns 30.00.
RTOD(ATN2(SIN(DTOR(30)),COS(DTOR(30))))

Fiiter Expression Functions

A-9

(
See Also
ATAN

Section A.6

cos

Section A.12

DTOR

Section A.19

RTOD

Section A.38

SIN

Section A.41

TAN

Section A.48

(

A-10 Filter Expression Functions

A.8 CDOW
The CDOW function returns the name of the day of the week from a date
expression.

Syntax
CDOW(<expD>)

Arguments
<expD>
A placeholder or any function that returns date type data

Examples
The following expression returns ''Monday".
CDOW ( { 02/29/88})

See Also
ACOS

Section A.2

ASIN

Section A.4

ATAN

Section A.6

ATN2

Section A.7

DTOR

Section A.19

RTOD

Section A.38

SIN

Section A.41

TAN

Section A.48

Fiiter Expression Functions

A-15

(

A.13 CTOD
The CTOD function converts a date stored as a character string to date type
data.

Syntax
CTOD( <expC>)

{expC}

Arguments
<expC>
The format of the character string is normally mm/dd/yy, but this format can
be changed by the environment variables SQLSRV_ENV_DATE and
SQLSRV_ENV_CENTURY (see Section 5.2).

Usage
The character expression used by CTOD can range from "01/01/0100" to
"12/31/9999". A twentieth century date is assumed if you use only two
numbers for the year.
You can also use braces {mm/dd/yy} to create a date type data from a literal
value.

See Also
ACOS

Section A.2

ATAN

Section A.6

ATN2

Section A.7

cos

Section A.12

RTOD

Section A.38

SIN

Section A.41

Fiiter Expression Functions A-23

A.20 DTOS
The DTOS function converts a date expression to a character string of the form
CCYYMMDD regardless of SQLSRV_ENV_CENTURY or
SQLSRV_ENV_DATE.

Syntax
DTOS(<expD>)

Usage
Use this function when you need a date expression in a character string that
has the same format regardless of environment variables.

Examples
The following expression returns "19880229".
DTOS({02/29/88})

See Also

A-30

EXP

Section A.21

LOGlO

Section A.27

Fiiter Expression Functions

(

A.27 LOG10
The LOGlO function returns the common log to the base 10 of a specified
number.

Syntax
LOG10( <expN>)

Usage
The LOGlO function returns the value for yin the equation y = LOGlO(x)
where x is the numeric expression used by the LOG10 function. This must be
a positive integer for the value of <expN>. LOGlO returns the value of y.

Examples
The following expression returns 0.3010.
LOG10(2.0000)

See Also

A-36

CMONTH

Section A.11

DAY

Section A.14

YEAR

Section A.54

Fiiter Expression Functions

A.33 Pl
The PI function returns the irrational number 3.14159, which is an
approximation of the constant pi (7r ), the ratio of the circumference of a circle
to its diameter.

Syntax
Pl()

Usage
The constant pi (7r) is used in mathematical and engineering calculations.

Examples
The following expression returns 3.14.
PI()

Fiiter Expression Functions A-3 7

(

A.34 RAND
The RAND function generates a random number. 1

Syntax
RAND([<expN>])

Arguments
<expN>
An optional numeric expression used as the seed to generate a new random
number. If the expression is a negative number, the seed is taken from the
system clock.

Usage
The RAND function computes a random number with or without a numeric
argument. You can repeat the function without an argument in order to get
subsequent random numbers in that sequence.
This function returns numbers between 0 and 0.999999 inclusive.
The default seed number is 100001. 'Th reset the seed to the default value, use
RAND( 100001).

Examples
The following expression returns 0.13.
RAND(23)

The following expression returns the next random number.
RAND()

Although this description uses the word "random," the value returned by the RAND function
is a pseudorandom number, that is, one of a very large but finite sequence of numbers.
Computers cannot generate truly random numbers.

A-38 Fiiter Expression Functions

A.35 REPLICATE
The REPLICATE function repeats a character expression a specified nwnber of
times.

Syntax
REPLICATE(<expC>, <expN>)

Arguments
<expC>

The character string to repeat
<expN>

The number of times to repeat <expC>

Usage
The output string must not exceed 254 characters (<expN> must be a nwnber
less than 254 divided by the nwnber of characters in <expC> ). Thus, when
you use the REPLICATE function to create histograms, you may need to use a
weighting factor.

Examples
The following expression returns "*****"
REPLICATE("*",5)

Fiiter Expression Functions A-39

A.36 RIGHT
The RIGHT function returns a specified number of characters from a character
expression, starting from the last character on the right.

Syntax
RIGHT(<expC>, <expN>)

Usage
The RIGHT function allows you to retrieve the last part of a character string
or a variable. The numeric expression defines the number of characters to
extract from the character string or variable.
If the numeric expression is zero or negative, RIGHT returns an empty string.
If the numeric expression is greater than the length of the character string,
RIGHT returns the entire string.

Examples
The following expression returns "def'.
RIGHT("abcdef",3)

See Also
ACOS

Section A.2

ASIN

Section A.4

ATAN

Section A.6

ATN2

Section A.7

cos

Section A.12

DTOR

Section A.19

SIN

Section A.41

TAN

Section A.48

Fiiter Expression Functions A-43

(

A.39 RTRIM
The RTRIM function removes all trailing blanks from a character string. This
function is identical to the TRIM function.

Syntax
RTRIM(<expC>)

Usage
Use this function to trim trailing blanks from character strings.
RTRIM( <expC>) followed by a comma inserts one blank space before the
next string. RTRIM(<expC>) followed by a plus sign does not insert any blank
space before the next string.

Examples
The following expression returns "Jones".
RTRIM("Jones

See Also

A-44

LEFT

Section A.24

LTRIM

Section A.29

RIGHT

Section A.36

TRIM

Section A.50

Fiiter Expression Functions

A.40 SIGN
The SIGN function returns a number representing the mathematical sign of a
numeric expression. It returns a 1 for a positive number, a -1 for a negative
number, and a 0 for zero.

Syntax
SIGN(<expN>)

Arguments
<8XpN>

A numeric expression

Usage
Use SIGN when the result of a calculation must have the same sign as the
initial values used, but where the result of the calculation can be of either sign.

Examples
The following expression returns -1.
SIGN (-999)

See Also
ACOS

Section A.2

ASIN

Section A.4

ATAN

Section A.6

ATN2

Section A.7

cos

Section A.12

DTOR

Section A.19

Section A.33

RTOD

Section A.38

TAN

Section A.48

Fiiter Expression Functions A-4 7

A.42 SOUNDEX
The SOUNDEX function provides a phonetic match (sound-alike) code to find a
match when the exact spelling is not known.

Syntax
SOUNDEX(<expC>)

Usage
The SOUNDEX function returns a 4-character code by using the following
algorithm:·
1

It retains the first letter of <expC>, the specified character expression.

It drops all occurrences of the letters a eh i o u w yin all positions except
the first one.

It assigns a number to the remaining letters:
bfpv

cgjkqsxz

If two or more adjacent letters have the same code, it drops all but the first
letter.

It provides a code of the form "letter digit digit digit". It adds trailing zeros
if there are fewer than three digits. It drops all digits after the third digit
on the right.

It stops at the first nonalphabetic character.

It skips over leading blanks.

It returns "0000" if the first nonblank character is non-alphabetic.

These steps produce a 4-character code. This code is used to find possible
sound-alike matches.

A-48 Fiiter Expression Functions

(

Examples
The following expression returns "K516".
SOUNDEX ("Kimberlee")

The following expression returns "K516".
SOUNDEX ("Kimbrel ea")

The following expression returns "K516".
SOUNDEX ( "Kimburley")

See Also
ACOS

Section A.2

ASIN

Section A.4

ATAN

Section A.6

ATN2

Section A.7

cos

Section A.12

SIN

Section A.41

Fiiter Expression Functions

A-57

(

A.49 TIME
The TIME function returns the system time as a character string in the format
hh:mm:ss.

Syntax
TIME()

Usage
To use TIME in calculations, convert the value returned to a numeric value
using SUBSTR and VAL.

A-58 Fiiter Expression Functions

A.50 TRIM
The TRIM function removes all trailing blanks from a character string. This
function is identical to the RTRIM function.

Syntax
TRIM( <expC>)

Usage
Use this function to trim trailing blanks from character strings. TRIM(<expC>)
followed by a comma inserts one blank space before the next string.
TRIM( <expC>) followed by a plus sign does not insert any blank space
before the next string.

Examples
The following expression returns "Jones".
TRIM ("Jones

See Also
STR

A-62

Section A.45

Fiiter Expression Functions

A.54 YEAR
The YEAR function returns the numeric value of the year from a date
expression. The result is always a 4-digit number.

Syntax
YEAR(<expD>)

Examples
The following expression returns 1988.
YEAR({02/29/88})

Fiiter Expression Functions A-63

(

B
SQL/Services Sample Application
This appendix gives complete source code listings for the two modules that
comprise the SQLSRV$DYNAMIC program. SQLSRV$DRIVER.C is listed in
Example B-1. SQLSRV$DYNAMIC.C is listed in Example B-2.
Example B-1

The SQLSRV$DRIVER.C Module

SQLSRV$DRIVER.C

/*
/*
/*

I*
I*

/*
/*
/*
/*
/*
/*

This module is part of an application program that demonstrates
SQL/Services.
It is provided for instructional purposes only.

*/
*/
*/
*/

*I
This module accepts a string from the terminal that contains an SQL
statement and then calls the other module (SQLSRV$DYNAMIC) to process
it.
You can substitute your own module for this driver. Instead of using
terminal I/O, your module could construct an SQL statement from
parameters passed by a calling module. For example, your module could
parse a non-SQL statement from a front-end system and build an SQL
statement from it.
However the module generates an SQL statement, it can be passed to a
module similar to SQLSRV$DYNAMIC for processing.

tinclude <stdio.h>
#include <sqlsrvda.h>
#include <sqlsrvca.h>
#include <sqlsrv.h>

/* Standard input/output.
/* SQLDA structure definition.
/* SQLCA structure, error definition.
/* SQL Services structure definitions.

*/
*/
*/
*/
*/

*/
*/
*/
*/

*/
*/

*I
*/
*/

(continued on next page)

SQL/Servlces Sample Appllcatlon

8-1

Example B-1 (Cont.)

The SQLSRV$DRIVER.C Module

(

main(argc,argv)
int
argc;
char
*argv[];
{

/* Variables for association */
char
struct SQLCA
char

*assoc id;
sqlca_str;
long_error[512];

/* Association handle.
/* SQL Context Area.
/* Alternative error buffer.

*I
*I

/* SQL statement text

/* Other variables */
char
int

sql_statement[1024];
sts, echo = O;

/* The definitions of the create association and release association
/* functions are in SQLSRV$DYNAMIC.

*/
*/

sts =create association(argc, argv, &assoc_id, &sqlca_str, long_error);
if (sts != SQL SUCCESS)
return sts;
/* Print user instructions once.

printf(" \n");
printf("Enter any dynamically executable SQL statement, \n");
printf("continuing it on successive lines.\n");
printf ("Terminate each statement with a semicolon. \n");
printf ("Built-in commands are: [no] echo and exit. \n");
printf(" \n");
while (1) {
get_statement(sql_statement, echo);
/* these string comparisons are case-sensitive */
if (!strcmp(sql statement, "echo"))
echo = 1; else if (!strcmp(sql_statement, "noecho"))
echo = O;
else if (!strcmp(sql statement, "exit"))
break;
else
execute statement(assoc id,&sqlca str,sql statement,long error);
/* while */ release_association(assoc_id,&sqlca_str,long_error);
/* main */

(continued on next page)

B-2 SQL/Servlces Sample Appllcatlon

Example B-1 (Cont.)

The SQLSRV$DRIVER.C Module

get statement(sql statement,echo)
char
*sql statement;
int
echo;
{

/* Get SQL statement from user, concatenating partial statements using
/* one space character as a separator.

*/
*/

char
int

*I
*/

part stmt [256];
end_of_stmt = O;

/* temporaries
/* flag for end of statement

printf("SQL> ");
sql statement[O] = '\0';
/* init statement string
while (lend of stmt) {
get_partial(part_stmt,&end_of_stmt,echo);
if (strlen(sql statement) != 0)
strcat(sql-statement," ");
/*add separator character
if (strlen(part stmt) > 0)
strcat(sql ;tatement,part stmt);
if (lend of stmt)
printf ( 11 cont> ");
} /* while */
/* get_statement */

get_partial(part_stmt,end_of_stmt,echo)
char
*part stmt;
int
*end of stmt;
int
echo; {

I* Get partial statement from user. Accept semicolon as line terminator */
/* and exclamation point as conunent line.
int

len;

*end of stmt = O;
gets(part stmt);
if (echo)printf ("%s\n", part stmt);
len = strlen(part stmt);
if (len > 0) {
trim(&part stmt[len-1));
/*delete trailing white space
len = strlen(part stmt);
if (len > 0) {
if (part_stmt[O] == '!') /*delete conunents
part stmt[O]
'\0';
else
*end of stmt
(part stmt[len-1) == ';');
if (*end-of-stmt) {
part-stmt[len-1) = '\0'; /*delete semicolon
if (len > 1)
trim(&part stmt[len-2)); /*delete white space
} /* if */
/* if */
} /* if */
/* get_partial */

*/
*/

*I
*/

(continued on next page)

SQL/Servlces Sample Appllcatlon

8-3

Example 8-1 (Cont.)

The SQLSRV$DRIVER.C Module

trim(string)
char *string;
{

if (*string == ' ' 11 *string == '\t') {
*string= '\0';
trim(--string);

Example 8-2

The SQLSRV$DYNAMIC.C Module

I*
I*

SQLSRV$DYNAMIC.C

/*
/*
/*
/*
/*
/*

This module is part of an application program that demonstrates
SQL/Services. It is provided for instructional purposes only.
This module contains the following routines:
create association

I*
/*
/*
/*

Creates an SQL/SERVICES client/server association. Checks command line
argument vector for names of server system, account, and password. If
not present, prompts user.

I*
/*

/*
/*
/*
/*

I*
I*

I*
I*
I*
I*
/*
/*
/*

*/
*/
*/

*/
*/
*/
*/
*/
*/
*/
*/
*/

release association

Terminates an SQL/SERVICES client/server association.

execute statement

Accepts a string containing a dynamically executable SQL statement from */
the other module (SQLSRV$DRIVER). If parameter markers are present, it */
calls get_params. If the statement is a SELECT, it opens a cursor,
*/
fetches rows, and displays them. If the statement is not a SELECT, it
*/
executes the statement.
*I
*/
get_params
*/
For each parameter marker in the SQL statement, get_params checks the
data type and inputs data from the terminal.
report_error

*/
*/
*/
*/

*I
Frints out the message that corresponds to the error code in the SQLCA. */
Also prints out error messages text if present. Aborts on DECnet
*/
errors.
*I

#include <stdio.h>
#include <sqlsrvda.h>
#include <sqlsrvca.h>
finclude <sqlsrv.h>

/* standard input/output.
/* SQLDA structure definition.
/* SQLCA structure, error definition.
/* SQL/Services structure definitions.

*/
*/
*/
*/

(continued on next page)

B-4 SQL/Servlces Sample Appllcatlon

Example B-2 (Cont.)

The SQLSRV$DVNAMIC.C Module

create association(argc,argv,assoc id,sqlca str,long error)
int
argc;
/*-argument -count
char
*argv[];
/* argument vector
char
**assoc id;
/* address of association id used
/* in all SQL/Services calls.
struct SQLCA
*sqlca_str;
/* context structure
char
*long_error;
/* alternative error buffer

*I
*/
*/
*/
*/
*/

{

/* Variables and structures for SQL/Services API */
struct ASSOCIATE STR
associate str;
char
node-name[B);
char
user=name[32];
char
password[32];
static char read buffer[512];
static char write buffer[512];
long int
read_size, write_size;

/* Association structure.
/* VMS node name.
/* VMS user name.
/* VMS password.
/* Protocol read buffer.
/* Protocol write buffer.
/* Protocol buffer sizes.

/* Other variables
int
int

sts;
i;

/* return status value.
I* loop counter.

/* Get the node name, user name and password values for the server
/* connection. Prompt the user if not in argument vector.

*I
*/
*/

*I
*I
*/

*I
*/
*/

switch (argc) {
case 1:
print f ("VMS server node: ") ;
gets(node name);
print f ( "vMS server account name : ") ;
gets(user name);
printf("vMS server account password: ");
gets (password);
break;
case 2:
strcpy(node name, argv[l));
printf("VMS-server account name: ");
gets(user name);
printf("vMS server account password: ");
gets (password);
break;
case 3:
strcpy(node_name, argv[l]);
strcpy(user name, argv[2));
printf("VMS-server account password: ");
gets (password);
break;
case 4:
strcpy(node name, argv[l]);
strcpy(user-name, argv[2]);
strcpy(password, argv[3]);
break;

(continued on next page)

SQL/Servlces Sample Appllcatlon

8-5

Example B-2 (Cont.)

The SQLSRV$DVNAMIC.C Module

(

default:
for (i = 4; i < arge; i++)
printf ("Extraneous argument ignored: %s\n", argv[i]);
break;
/* switch */
read size = 1024;
write_size = 1024;

/* protocol buffer size value */
/* protocol buffer size value */

/* Set up association structure */
associate str.CLIENT LOG
O;
/* disable client logging.
*/
associate-str.SERVER-LOG = O;
/* disable server logging.
*/
associate-str.LOCAL FLAG = O;
/* this is a remote session.
*/
associate-str.MEMORY ROUTINE =NULL;
/* use default alloc routine.
*/
associate-str.FREE MEMORY ROUTINE = NULL; /* use default free routine. */
associate-str.ERRBUFLEN =-512;
associate=str.ERRBUF = long_error;
/* use alternative error string */

/* Connect with the server and establish an association.
sts = sqlsrv associate(
node_name,
user name,
pass;ord,
read buffer,
write buffer,
read size,
write size,
sqlca-str,
&associate str,
assoc id );

/* node name.
/* user name.

*/
*/

/* password.
/* protocol read buffer.
/* protocol write buffer.
/* read buffer size.
/* write buffer size.
/* SQLCA structure.
/* Association structure.

/* Association handle.

*I
*/
*/
*/
*/
*/

if (sts != SQL SUCCESS)
return report_error(*assoc_id, sqlca_str, long_error);
} /* create_association */
release association(assoc id, sqlca str, long error)
char
*assoc_id;
/* association handle
*/
struct SQLCA
*sqlca str;
/* context structure
*/
char
*long_error;
/* alternative error buffer */
int
char

sts;
*stats = NULL;

/* return status value. */
/* reserved parameter */

* release the association.

sts = sqlsrv_release(assoc_id,stats);
if (sts != SQL SUCCESS)
return report_error(assoc_id, sqlca_str, long_error);

} /* release association */

(continued on next page)

B-6 SQL/Servlces Sample Appllcatlon

Example B-2 (Cont.)

The SQLSRV$DVNAMIC.C Module

execute statement(assoc id, sqlca str, sql statement, long error)
char
*assoc id;
I* association handle.
*I
struct SQLCA
*sqlca-str;
/* Context structure.
*/
char
*sql statement; /* SQL statement to execute
*/
char
*long_error;
/* alternative error buffer
*/
{

/* Variables and structures for SQL/Services API

int
short int
long int
char
long int
struct SQLDA
struct SQLDA

*/
*/
*/
*/
*/
*/
*/

sts;
execute flag;
statemell't id;
*cursor name = "SEL";
database id = OL;
*param sqlda;
*select_sqlda;

/* return status value.
/* Execute mode flag.
/* Prepared statement id.
/* Name of cursor.
/* Database ID. Not in Vl.O.
/* Parameter marker SQLDA.
/* Select list SQLDA.

/* Other variables
int
int
char

i;
len;
*p;

/* Loop counter
/* temporary
/* temporary

*I
*/

/* Call the sqlsrv_prepare routine to prepare the SQL statement and to
/* write parameter marker and select list information into the SQLDA
/* structures. If you pass NULL pointers to the parameter marker SQLDA
/* and the select list SQLDA, sqlsrv_prepare allocates and initializes
/* the structures if they are required.

*/
*/

*/
*/
*/

select sqlda = NULL;
param_sqlda = NULL;

/* You can also pass in existing SQLDA structures, in which case the
/* sqlsrv_prepare routine initializes them.
sts = sqlsrv_prepare(
assoc id,
database_id,
sql statement,
&statement id,
&param sqlda,
&select_sqlda);

/* association handle.
/* database_id, must be zero.
/* SQL statement.
/* Prepared statement id.

*/
*/

*I
*I
*I
*/

if (sts != SQL SUCCESS)
return report_error(assoc_id, sqlca_str, long_error);

(continued on next page)

SQL/Servlces Sample Appllcatlon

B-7

Example 8-2 (Cont.)

The SQLSRV$DVNAMIC.C Module

/* The call to sqlsrv_prepare succeeded. If it allocated a param_sqlda
/* structure, the SQL statement contains parameter markers. NOTE: if
/* you preallocated param_sqlda, test (param_sqlda.SQLD > 0) here.

*/
*/
*/

if (param_sqlda) {

/* Call routine to allocate data and indicator variables

sts = sqlsrv_allocate_sqlda_data(assoc_id, param_sqlda);
if (sts != SQL_SUCCESS)
return report_error(assoc_id, sqlca_str, long_error);

I* get values for parameter markers

get_params(param_sqlda);

/* If the sqlsrv_prepare routine allocated a select list SQLDA, the
/* statement is a SELECT. Open a cursor, fetch rows, display them on
/* the terminal, and close the cursor. NOTE: if you are using a
/* preallocated SQLDA, test (select_sqlda.SQLD > 0) here.

*/
*/
*/

if (select_sqlda) {

/* Call routine to allocate data and indicator variables
sts

= sqlsrv_allocate_sqlda_data(assoc_id, select_sqlda);

if (sts != SQL_SUCCESS)
return report_error(assoc_id, sqlca_str, long_error);
sts

= sqlsrv open cursor(
-

assoc_id,
cursor_name,
statement id,
param sqlda
);

/* association id
/* handle for cursor
/* handle for SELECT statement
/* parameter marker SQLDA

*/
*/

*I
*/

if (sts != SQL SUCCESS)
return rep;rt_error(assoc_id, sqlca_str, long_error);

/* fetch and display rows */
printf("------ BEGIN RESULT TABLE ------\n");
do {
sts = sqlsrv_fetch (
assoc_id,
/* association id
cursor_name,
/* handle for cursor
0,
I* direction
OL,
/* row number
select _sqlda
/* select list SQLDA

*I
*I
*I
*/
*/

);

(continued on next page)

B-8 SQL/Servlces Sample Application

(

Example B-2 (Cont.)

The SQLSRV$DVNAMIC.C Module

switch (sts)
case SQL SUCCESS:
for (i = O; i

< select_sqlda->SQLD; i++) {

/* SQLD contains number of columns */
/* print first 20 chars of column name */
printf("%-20.20s: ",select_sqlda->SQLVARARY[i] .SQLNAME);
/* check the indicator variable for NULL value */
if (*select sqlda->SQLVARARY[i] .SQLIND < 0)
printf(iiNULL\n");
else
switch (select_sqlda->SQLVARARY[i] .SQLTYPE)
case SQLSRV_ASCII_STRING:
case SQLSRV_GENERALIZED_NUMBER:
case SQLSRV_GENERALIZED_DATE:
/* Null-terminated strings */
printf ( "%s\n",
select sqlda->SQLVARARY[i] .SQLDATA);
break;
case SQLSRV_VARCHAR:
/* Counted string. The first word of the
*/
/* data buffer is the length. Set a pointer */
/* to the first ASCII character and print. */
p = select sqlda->SQLVARARY[i] .SQLDATA;
len = *(sh~rt int *)p;
p += sizeof(short int);
printf("%-*.*s\n", len, len, p);
/* Note: SQLSRV VARCHAR data is likely to
/* be binary. A-real application wouldn't
/* print it on the terminal.

*/
*/
*/;

break;
I* switch */
} /* for */
printf("---------- END OF ROW ----------\n");
break;
case SQL_EOS:
printf("------- END RESULT TABLE -------\n");
break;
default:
return report_error(assoc_id, sqlca_str, long_error);
break;
} /* switch */
while (sts != SQL_EOS);
sts = sqlsrv_close_cursor(assoc_id, cursor_name);
if (sts != SQL SUCCESS)
return rep;rt_error(assoc_id, sqlca_str, long_error);

(continued on next page)

SQL/Servlces Sample Appllcatlon

B-9

Example B-2 (Cont.)

The SQLSRV$DVNAMIC.C Module

else {
/* The SQL statement is not a SELECT and can be executed now.

execute_flag = O; /* Turn batching off. */
sts = sqlsrv_execute(
assoc id,
datab°i:se id,
statement_id,
execute flag,
param sqlda

/* association handle.
/* database id, must be zero.
/* Prepared-statement id.
/* Execute mode.
/* Parameter marker SQLDA.

);

*I
*/

if (sts != SQL_SUCCESS)
return report_error(assoc_id, sqlca_str, long_error);
/* else */
/* Release the SQL statement resources */
sts = sqlsrv release statement(
assoc id,
1,
&statement id
) ;

/* association handle.
/* no. of statement ids.
/* statement id array.

*I
*/

/*NOTE: You can pass in multiple statement ids in array format. We're */
/* only passing one here.
In c, an array is a pointer, so by passing a */
/* pointer, we pass an array of 1.
*/
if (sts != SQL SUCCESS)
return rep;rt_error(assoc_id, sqlca_str, long_error);
return(SQL_SUCCESS);
/* execute_statement */
get_params(param_sqlda)
struct SQLDA
*param sqlda;

{

int
int
char

i;
len;
s[80],*p;

/* Parameter marker SQLDA. */

/* loop counter */
/* temporary */
I* temporary */

for (i = O; i < param_sqlda->SQLD; i++)

I* SQLD contains the number of parameter markers */
(continued on next page)

B-10 SQL/Servlces Sample Appllcatlon

Example B-2 (Cont.)

The SQLSRV$DYNAMIC.C Module

switch(param_sqlda->SQLVARARY[i].SQLTYPE) {
/* branch on the data type of the parameter */
case SQLSRV ASCII STRING:
/* null-terminated strings */
case SQLSRV=GENERALIZED_NUMBER:
do {
printf("Enter value for:
");
printf("%s\n", param sqlda->SQLVARARY[i] .SQLNAME);
printf("Maximum length is: ");
printf("%d\n", param sqlda->SQLVARARY[i] .SQLLEN);
printf("DATA> ");
gets(param sqlda->SQLVARARY[i] .SQLDATA);
len = strlen(param sqlda->SQLVARARY[i] .SQLDATA);
if (len == 0)
printf("Value required. Please reenter.");
while (len == O);
break;
case SQLSRV VARCHAR:
/* counted string */
do {
printf ("Enter value for:
");
printf("%s\n", param sqlda->SQLVARARY[i] .SQLNAME);
printf ("Maximum length is: ");
printf("%d\n", param sqlda->SQLVARARY[i) .SQLLEN);
printf("DATA> ");
gets(s);
/* Get the length and write it into the first word of
/* the buffer. Set a pointer to the next byte and copy
/* in the ASCII data.

*/
*/
*/

len = strlen(s);
p = param sqlda->SQLVARARY[i] .SQLDATA;
*(short int *)p = len;
p += sizeof(short int);
strncpy(p,s,len);
if (len == 0)
printf("Value required. Please reenter.");
while (len == O);
break;
case SQLSRV GENERALIZED DATE:
/* null-terminated string */
do {
printf("Enter value for:
");
printf("%s\n", param sqlda->SQLVARARY[i] .SQLNAME);
printf("Maximum length is: ");
printf("%d\n", param sqlda->SQLVARARY[i] .SQLLEN);
printf ("Format is: c;yymmddhhmissff\n");
printf("DATA> ");
gets(param sqlda->SQLVARARY[i] .SQLDATA);
len = strlen(param sqlda->SQLVARARY[i] .SQLDATA);
if (len == 0)
printf("Value required. Please reenter.");
while (len == O);
break;

(continued on next page)

SQL/Servlces Sample Appllcatlon

B-11

Example 8-2 (Cont.)

The SQLSRV$DYNAMIC.C Module

default:
printf("Invalid data type: %d\n",
param sqlda->SQLVARARY[i) .SQLTYPE);
gets(s); f* dispose of value*/
break;
} /* switch */
/* for */
return(SQL_SUCCESS);

(

/* get_params */
report error(assoc id, sqlca str, long error)
char *a;soc id; !* association handle
*/
struct SQLCA
*sqlca-str;
/* context structure
*/
char
*long_;rror;
/* alternative error buffer */
{

char

*stats = NULL;

/* reserved parameter */

switch (sqlca_str->SQLCODE)
case SQLSRV CNDERR:
printf('ii"Filter runtime error.\n");
break;
case SQLSRV FTRSYNERR:
printf("syntax error in filter expression.");
break;
case SQLSRV INTERR:
printf('ii"Internal error. Examine SQLSRV.DMP and submit SPR.\n");
break;
case SQLSRV_INVARG:
printf("Invalid routine parameter.\n");
break;
case SQLSRV INVASC:
printf(,,..Invalid association id.\n");
break;
case SQLSRV_INVASCSTR:
printf("Invalid parameter in ASSOCIATE_STR.\n");
break;
case SQLSRV INVBUFSIZ:
printf(,,..Invalid read or write buffer size.\n");
break;
case SQLSRV INVCURNAM:
printf('ii"Invalid cursor name.\n");
break;
case SQLSRV INVENVTAG:
printf("Invalid environment tag.\n");
break;
case SQLSRV INVENVVAR:
printf("Invalid environment variable.\n");
break;
case SQLSRV INVEXEFLG:
printf("Invalid execute flag. \n");
break;
case SQLSRV INVIDX:
printf(,,..Invalid sqlda_index_array\n");
break;

(continued on next page)

8-12 SQL/Servlces Sample Application

Example B-2 (Cont.)

The SQLSRV$DVNAMIC.C Module

case SQLSRV INVREPCNT:
printf <"Invalid repeat count. \n");
break;
case SQLSRV INVSQLCA:
printf(ttinvalid SQLCA structure. \n");
break;
case SQLSRV INVSQLDA:
printf('Hinvalid SQLDA structure.\n");
break;
case SQLSRV INVSTMID:
printf <"Invalid statement id. \n");
break;
case SQLSRV MULTI ACT:
printf('HA batched sqlsrv execute or\n");
printf("sqlsrv fetch many context is active. \n");
break;
case SQLSRV NETERR:
printf('HDECnet returned an error. \n");
printf("SQLERRD[O]: x%lx\n", sqlca str->SQLERRD[O]);
printf("SQLERRD[2]: %d.\n", sqlca_str->SQLERRD[2]);
sqlsrv release(assoc id,stats);
exit (2);
break;
case SQLSRV NO MEM:
print f ('HAP! memory allocation failed. \n") ;
break;
case SQLSRV OPNLOGFIL:
printf('Hunable to open log file\n");
break;
case SQLSRV PRSERR:
printf('HFatal error in message parser\n");
break;
case SQLSRV SQLDA NOTALL:
printf("Attempt to deallocate static memory\n");
break;
case SQLSRV SRVERR:
printf('HThe server returned an error.\n");
printf("SQLERRD[O]: x%lx\n", sqlca str->SQLERRD(O]);
printf("SQLERRD[2]: %d.\n", sqlca str->SQLERR.0[2]);
sqlsrv release(assoc id,stats); exit(2f;
break;

/* SQL Errors */
case SQL BAD TXN STATE:
printf ("Invalid transaction state \n");
break;
case SQL CURALROPE:
printf("WARNING Cursor is already open\n");
break;
case SQL CURNOTOPE:
printf("Cursor not open\n");
break;
case SQL DEADLOCK:
printf("Deadlock encountered\n");
break;

(continued on next page)

SQL/Servlces Sample Appllcatlon

B-13

Example B-2 (Cont.)

The SQLSRV$DYNAMIC.C Module

case SQL EOS:
printf("SELECT or cursor at end of stream\n");
break;
case SQL INTEG FAIL:
printf("Constraint failed\n");
break;
case SQL LOCK CONFLICT:
printf("Lock conflict\n");
break;
case SQL NO DUP:
printf (iiouplicate on index\n");
break;
case SQL NOT VALID:
printf("Valid-if failed\n");
break;
case SQL NULLNOIND:
printf("NULL value and no indicator variable\n");
break;
case SQL OUTOFRAN:
printf("Value is out of range for a host variable\n");
break;
case SQL RDBERR:
printf("Rdb returned an error\n");
break;
case SQL ROTXN:
printf("Read/write operation in read-only transaction\n");
break;
case SQL SUCCESS:
printf("Command completed successfully\n");
break;
case SQL_UDCURNOPE:
printf("Cursor in update or delete not open\n");
break;
case SQL UDCURNPOS:
printf("Cursor in update or delete not positioned on record\n");
break;
default:
printf("Unknown error\n");
printf("SQLCA.SQLCODE: %d\n", sqlca_str->SQLCODE);
break;
/* switch */

/* Print out error message text if present */
if (strlen(long error) != 0)
printf("%s\n", long_error);
return 1;

/* report_error */

8-14 SQL/Servlces Sample Application

(

c
Sample Log Files
This appendix gives listings for each of several log files generated by the
SQUServices Installation Verification Procedure. The complete association
level log is shown in Example C-1. The complete routine level log is shown in
Example C-2. A partial message protocol level log is shown in Example C-3.
Example C-1

Sample Association Level Log

ASSOCIATE LEVEL LOG
----SQLSRV_ASSOCIATE
--------SQLSRV ASSOCIATE ID: 106520
--------NODE: abcdef, USERNAME: xxxxxx, SQLCODE: O, SQLERRD[O] 0

ASSOCIATE LEVEL LOG
----SQLSRV_RELEASE
--------SQLSRV_ASSOCIATE ID: 106520

Sample Log Flies C-1

Example C-2

Sample Routine Level Log

(

ROUTINE LEVEL LOG
----SQLSRV EXECUTE IMMEDIATE
--------SQL STATEMENT
------------len: 36, value: create schema filename SQLSRV_SAMPLE
ROUTINE LEVEL LOG
----SQLSRV EXECUTE IMMEDIATE
--------SQL STATEMENT
------------len: 119, value: create table SQLSRV_TABLE ( USERNAME
CHAR(32), INTE
------------GER VALUE
INTEGER, DOUBLE VALUE
DOUBLE PRECISION, DATE_VALUE
DATE )
ROUTINE LEVEL LOG
----SQLSRV_PREPARE
--------SQL STATEMENT
------------len: 102, value: insert into SQLSRV_TABLE ( USERNAME, INTEGER VALUE
------------, DOUBLE_VALUE, DATE VALUE
values ( ?, ?, ?, ? )
ROUTINE LEVEL LOG
----PARAMETER MARKER SQLDA
--------SQLDA: SQLD 4
--------[O].SQLTYPE: SQLSRV ASCII_STRING, SQLLEN: 33
------------SQLNAME: USERNAME
--------[l].SQLTYPE: SQLSRV_GENERALIZED_NUMBER, SQLLEN[O] 12, SQLLEN[l] 0
------------SQLNAME: INTEGER VALUE
--------[2].SQLTYPE: SQLSRV_GENERALIZED_NUMBER, SQLLEN[O] 24, SQLLEN[l] 0
------------SQLNAME: DOUBLE VALUE
--------[3].SQLTYPE: SQLSRV_GENERALIZED_DATE, SQLLEN: 17
------------SQLNAME: DATE VALUE
ROUTINE LEVEL LOG
----SQLSRV_ALLOCATE_SQLDA_DATA
ROUTINE LEVEL LOG
----SQLSRV_EXECUTE
--------STATEMENT ID
------------1199896
--------EXECUTE FLAG
0

--------PARAMETER MARKER SQLDA
------------SQLDA: SQLD 4
----------------[O] .SQLTYPE: SQLSRV ASCII STRING, SQLIND: 0
--------------------len: 6, value: ixxxxx----------------(1] .SQLTYPE: SQLSRV GENERALIZED NUMBER, SQLIND: 0
--------------------len: 1, value: l
----------------(2] .SQLTYPE: SQLSRV GENERALIZED NUMBER, SQLIND: 0
--------------------len: 10, value:-128.000000 ----------------(3] .SQLTYPE: SQLSRV GENERALIZED DATE, SQLIND: 0
--------------------len: 8, value: l9880701
-

(continued on next page)

C-2 Sample log Flies

Example C-2 (Cont.)

Sample Routine Level Log

ROUTINE LEVEL LOG
----SQLSRV_EXECUTE
--------STATEMENT ID
------------1199896
--------EXECUTE FLAG
0

--------PARAMETER MARKER SQLDA
------------SQLDA: SQLD 4
----------------[OJ .SQLTYPE: SQLSRV ASCII STRING, SQLIND: 0
--------------------len: 6, value: xxxxxx----------------[lJ .SQLTYPE: SQLSRV GENERALIZED NUMBER, SQLIND: 0
--------------------len: 1, value: 2
----------------[2J .SQLTYPE: SQLSRV GENERALIZED NUMBER, SQLIND: 0
--------------------len: 12, value:-32768.000000
----------------[3J .SQLTYPE: SQLSRV GENERALIZED DATE, SQLIND: 0
--------------------len: 8, value: l9880702
ROUTINE LEVEL LOG
----SQLSRV_EXECUTE
--------STATEMENT ID
------------1199896
--------EXECUTE FLAG
0

--------PARAMETER MARKER SQLDA
------------SQLDA: SQLD 4
----------------[OJ .SQLTYPE: SQLSRV ASCII STRING, SQLIND: 0
--------------------len: 6, value: xxxxxx----------------[lJ .SQLTYPE: SQLSRV_GENERALIZED_NUMBER, SQLIND: 0
--------------------len: 1, value: 3
----------------[2J .SQLTYPE: SQLSRV GENERALIZED NUMBER, SQLIND: 0
--------------------len: 13, value:-524288.000000
----------------[3J .SQLTYPE: SQLSRV GENERALIZED DATE, SQLIND: 0
--------------------len: 8, value: l9880703
ROUTINE LEVEL LOG
----SQLSRV RELEASE STATEMENT
--------STATEMENT ID
------------[OJ 1199896
ROUTINE LEVEL LOG
----SQLSRV_FREE_SQLDA_DATA
ROUTINE LEVEL LOG
----SQLSRV_PREPARE
--------SQL STATEMENT
------------len: 45, value: Select

* from sqlsrv_table where USERNAME ~ ?
(continued on next page)

Sample Log Flies

C-3

Example C-2 (Cont.)

Sample Routine Level Log

ROUTINE LEVEL LOG
----SELECT LIST SQLDA
--------SQLDA: SQLD 4
--------(0).SQLTYPE: SQLSRV ASCII_STRING, SQLLEN: 33
------------SQLNAME: USERNAME
--------(1) .SQLTYPE: SQLSRV_GENERALIZED_NUMBER, SQLLEN[O) 12, SQLLEN[l] 0
------------SQLNAME: INTEGER VALUE
--------(2).SQLTYPE: SQLSRV_GENERALIZED_NUMBER, SQLLEN[O] 24, SQLLEN[l) 0
------------SQLNAME: DOUBLE VALUE
--------[3].SQLTYPE: SQLSRV_GENERALIZED_DATE, SQLLEN: 17
------------SQLNAME: DATE VALUE
ROUTINE LEVEL LOG
----PARAMETER MARKER SQLDA
--------SQLDA: SQLD 1
--------[0).SQLTYPE: SQLSRV_ASCII_STRING, SQLLEN: 33
------------SQLNAME: USERNAME
ROUTINE LEVEL LOG
----SQLSRV_ALLOCATE_SQLDA_DATA
ROUTINE LEVEL LOG
----SQLSRV_ALLOCATE_SQLDA_DATA
ROUTINE LEVEL LOG
----SQLSRV_OPEN_CURSOR
--------CURSOR NAME

------------sqlsrv_cursor
--------STATEMENT ID
1199896
ROUTINE LEVEL LOG
----SQLSRV_FETCH
--------CURSOR NAME

------------sqlsrv_cursor
ROUTINE LEVEL LOG
----SELECT LIST SQLDA
--------SQLDA: SQLD 4
------------(0] .SQLTYPE:
----------------len: 32,
------------(1) .SQLTYPE:
----------------len: 11,
------------(2) .SQLTYPE:
----------------len: 23,
------------[3] .SQLTYPE:
----------------len: 16,

SQLSRV ASCII STRING, SQLIND: 0
value:-xxxxxx
SQLSRV GENERALIZED NUMBER, SQLIND: 0
value:-1
SQLSRV GENERALIZED NUMBER, SQLIND: 0
value:- 1.280000000000000E+002
SQLSRV GENERALIZED DATE, SQLIND: 0
value:-1988070100000000

ROUTINE LEVEL LOG
----SQLSRV FETCH
--------CURSOR NAME

------------sqlsrv_cursor

(continued on next page)

C-4 Sample Log Flies

(

Example C-2 (Cont.)

Sample Routine Level Log

ROUTINE LEVEL LOG
----SELECT LIST SQLDA
--------SQLDA: SQLD 4
------------[OJ .SQLTYPE:
----------------len: 32,
------------[1) .SQLTYPE:
----------------len: 11,
------------[2).SQLTYPE:
----------------len: 23,
------------[3].SQLTYPE:
----------------len: 16,

SQLSRV ASCII STRING, SQLIND: 0
value:-xxxxxx
SQLSRV_GENERALIZED_NUMBER, SQLIND: 0
value: 2
SQLSRV GENERALIZED NUMBER, SQLIND: 0
value:- 3.276800000000000E+004
SQLSRV GENERALIZED DATE, SQLIND: 0
value:-1988070200000000

ROUTINE LEVEL LOG
----SQLSRV_FETCH
--------CURSOR NAME
------------sqlsrv_cursor
ROUTINE LEVEL LOG
----SELECT LIST SQLDA
--------SQLDA: SQLD 4
------------[0] .SQLTYPE:
----------------len: 32,
------------[l) .SQLTYPE:
----------------len: 11,
------------[2) .SQLTYPE:
----------------len: 23,
------------[3) .SQLTYPE:
----------------len: 16,

SQLSRV ASCII STRING, SQLIND: 0
value:-xxxxxx
SQLSRV_GENERALIZED_NUMBER, SQLIND: 0
value: 3
SQLSRV GENERALIZED NUMBER, SQLIND: 0
value:- 5.242880000000000E+005
SQLSRV GENERALIZED DATE, SQLIND: 0
value:-1988070300000000

ROUTINE LEVEL LOG
----SQLSRV_FETCH
--------CURSOR NAME
------------sqlsrv_cursor
ROUTINE LEVEL LOG
----SQLSRV_CLOSE_CURSOR
--------CURSOR NAME
------------sqlsrv_cursor
ROUTINE LEVEL LOG
----SQLSRV_RELEASE_STATEMENT
--------STATEMENT ID
------------[0] 1199896
ROUTINE LEVEL LOG
----SQLSRV_FREE_SQLDA_DATA
ROUTINE LEVEL LOG
----SQLSRV_FREE_SQLDA_DATA
ROUTINE LEVEL LOG
----SQLSRV_PREPARE
--------SQL STATEMENT
------------len: 43, value: delete from SQLSRV_TABLE where USERNAME = ?

(continued on next page)

Sample Log Flies C-5

Example C-2 (Cont.)

Sample Routine Level Log

ROUTINE LEVEL LOG
----PARAMETER MARKER SQLDA
--------SQLDA: SQLD 1
--------(0].SQLTYPE: SQLSRV_ASCII_STRING, SQLLEN: 33
------------SQLNAME: USERNAME
ROUTINE LEVEL LOG
----SQLSRV_ALLOCATE_SQLDA_DATA
ROUTINE LEVEL LOG
----SQLSRV_EXECUTE
--------STATEMENT ID
------------1199896
--------EXECUTE FLAG
0

--------PARAMETER MARKER SQLDA
------------SQLDA: SQLD 1
----------------(0] .SQLTYPE: SQLSRV_ASCII_STRING, SQLIND: 0
--------------------len: 6, value: xxxxxx
ROUTINE LEVEL LOG
----SQLSRV_RELEASE_STATEMENT
--------STATEMENT ID
------------(0] 1199896
ROUTINE LEVEL LOG
----SQLSRV_FREE_SQLDA_DATA
ROUTINE LEVEL LOG
----SQLSRV_EXECUTE_IMMEDIATE
--------SQL STATEMENT
------------len: 6, value: Conunit
ROUTINE LEVEL LOG
----SQLSRV_EXECUTE_IMMEDIATE
--------SQL STATEMENT
------------len: 34, value: Drop Schema filename SQLSRV_SAMPLE

C-6

Sample Log Flies

(

Example C-3

)

Sample Message Protocol Level Log

PROTOCOL LEVEL LOG CLIENT: write (logonly)
----PACKET ID: 1, PACKET SEQUENCE: 0
--------SQLSRV ASSOCIATE
------------PROTOCOL VERSION
----------------len: 2, value: 1
------------READ BUFFER SIZE
----------------len: 2, value: 1024
------------WRITE BUFFER SIZE
----------------len: 2, value: 1024
--------END OF MESSAGE
PROTOCOL LEVEL LOG CLIENT: read
----PACKET ID: 1, PACKET SEQUENCE: 0
--------SQLSRV_ASSOCIATE ACK
------------PROTOCOL VERSION
----------------len: 2, value: 1
------------ASSOCIATE ID
----------------len: 2, value: 1
--------END OF MESSAGE
PROTOCOL LEVEL LOG CLIENT: write (logonly)
----PACKET ID: 2, PACKET SEQUENCE: 0
--------SQLSRV_EXECUTE IMMEDIATE
------------SQL STATEMENT
----------------SQLSRV ASCII STRING, len: 36
--------------------len: 36,-value: create schema filename SQLSRV_SAMPLE
--------END OF MESSAGE
PROTOCOL LEVEL LOG CLIENT: read
----PACKET ID: 2, PACKET SEQUENCE: 0
--------SQLSRV_EXECUTE IMMEDIATE ACK
------------STATUS
----------------SQLSRV GENERALIZED NUMBER, len: 1
--------------------len: 1, value:-0
------------EXECUTE PARAMETER
----------------SQLSRV GENERALIZED NUMBER, len: 1
--------------------len: 1, value:-0
--------END OF MESSAGE
PROTOCOL LEVEL LOG CLIENT: write (logonly)
----PACKET ID: 3, PACKET SEQUENCE: 0
--------SQLSRV_EXECUTE_IMMEDIATE
------------SQL STATEMENT
----------------SQLSRV ASCII STRING, len: 119
--------------------len: 119~ value: create table SQLSRV_TABLE ( USERNAME
CHAR(3

--------------------2), INTEGER_VALUE
--------------------TE VALUE
DATE )
--------END OF MESSAGE

INTEGER, DOUBLE VALUE

DOUBLE PRECISION, DA

(continued on next page)

Sample Log Flies C-7

Example C-3 (Cont.)

Sample Message Protocol Level Log

(

PROTOCOL LEVEL LOG CLIENT: read
----PACKET ID: 3, PACKET SEQUENCE: 0
--------SQLSRV_EXECUTE_IMMEDIATE ACK
------------STATUS
----------------SQLSRV_GENERALIZED_NUMBER, len: 1
--------------------len: 1, value: 0
------------EXECUTE PARAMETER
----------------SQLSRV GENERALIZED NUMBER, len: 1
--------------------len: 1, value:-0
--------END OF MESSAGE
PROTOCOL LEVEL LOG CLIENT: write (logonly)
----PACKET ID: 4, PACKET SEQUENCE: 0
--------SQLSRV_PREPARE
------------SQL STATEMENT
----------------SQLSRV ASCII STRING, len: 102
--------------------len: 102; value: insert into SQLSRV TABLE ( USERNAME, INTEG
--------------------ER VALUE, DOUBLE VALUE, DATE_VALUE ) values ( ?, ?, ?, ? )
--------END OF MESSAGEPROTOCOL LEVEL LOG CLIENT: read
----PACKET ID: 4, PACKET SEQUENCE: 0
--------SQLSRV_PREPARE ACK
------------STATEMENT ID
----------------SQLSRV GENERALIZED NUMBER, len: 7
--------------------len: 7, value:-1199896
------------PARAMETER MARKER SQLDA
----------------len: 2, value: 4
------------SQLVAR
----------------len: 2, value: 0
------------SQLTYl?E
----------------len: 2, value: 129
------------SQLLEN
----------------len: 2, value: 33
------------SQLNAME
----------------SQLSRV ASCII STRING, len: 8
--------------------len: 8, value: USERNAME
------------SQLVAR
----------------len: 2, value: 1
------------SQLTYl?E
----------------len: 2, value: 130
------------SQLLEN
----------------len: 2, value: 12
------------SQLNAME
----------------SQLSRV ASCII STRING, len: 13
--------------------len: 13,-value: INTEGER VALUE
------------SQLVAR
----------------len: 2, value: 2
------------SQLTYPE
----------------len: 2, value: 130
------------SQLLEN
----------------len: 2, value: 24
------------SQLNAME
----------------SQLSRV ASCII STRING, len: 12
--------------------len: 12,-value: DOUBLE VALUE

(

(continued on next page)

C-8

Sample Log Flies

Example C-3 (Cont.)

Sample Message Protocol Level Log

------------SQLVAR
----------------len: 2, value: 3
------------SQLTYPE
----------------len: ?., value: 131
------------SQLLEN
----------------len: 2, value: 17
------------SQLNAME
----------------SQLSRV ASCII STRING, len: 10
--------------------len: 10,-value: DATE_VALUE
--------END OF MESSAGE
PROTOCOL LEVEL LOG CLIENT: write (logonly)
----PACKET ID: 5, PACKET SEQUENCE: 0
--------SQLSRV_EXECUTE
------------STATEMENT ID
----------------SQLSRV GENERALIZED NUMBER, len: 7
--------------------len: 7, value:-1199896
------------REPEAT COUNT
----------------len: 2, value: 1
------------PARAMETER MARKER DATA
----------------len: 2, value: 4
------------SQLVAR
----------------len: 2, value: 0
------------SQLDATA
----------------SQLSRV ASCII STRING, len: 6
--------------------len: 6, value: xxxxxx
------------SQLIND
----------------len: 2, value: 0
------------SQLVAR
----------------len: 2, value: 1
------------SQLDATA
----------------SQLSRV GENERALIZED_NUMBER, len: 1
--------------------len: 1, value: 1
------------SQLIND
----------------len: 2, value: 0
------------SQLVAR
----------------len: 2, value: 2
------------SQLDATA
----------------SQLSRV GENERALIZED NUMBER, len: 10
--------------------len: 10, value~ 120.000000
------------SQLIND
----------------len: 2, value: 0
------------SQLVAR
----------------len: 2, value: 3
------------SQLDATA
----------------SQLSRV GENERALIZED_DATE, len: 8
--------------------len: 8, value: 19880701
------------SQLIND
----------------len: 2, value: 0
--------END OF MESSAGE

(continued on next page)

Sample Log Flies C-9

Example C-3 (Cont.)

Sample Message Protocol Level Log

(

PROTOCOL LEVEL LOG CLIENT: read
----PACKET ID: 5, PACKET SEQUENCE: 0
--------SQLSRV_EXECUTE ACK
------------STATUS
----------------SQLSRV GENERALIZED NUMBER, len: 1
--------------------le~: 1, value:-0
------------EXECUTE PARAMETER
----------------SQLSRV_GENERALIZED_NUMBER, len: 1
--------------------len: 1, value: 1
--------END OF MESSAGE
PROTOCOL LEVEL LOG CLIENT: write (logonly)
----PACKET ID: 6, PACKET SEQUENCE: 0
--------SQLSRV_EXECUTE
------------STATEMENT ID
----------------SQLSRV GENERALIZED NUMBER, len: 7
--------------------len: 7, value:-1199896
------------REPEAT COUNT
----------------len: 2, value: 1
------------PARAMETER MARKER DATA
----------------len: 2, value: 4
------------SQLVAR
----------------len: 2, value: 0
------------SQLDATA
----------------SQLSRV ASCII STRING, len: 6
--------------------le~: 6, value: xxxxxx
------------SQLIND
----------------len: 2, value: 0
------------SQLVAR
----------------len: 2, value: 1
------------SQLDATA
----------------SQLSRV GENERALIZED NUMBER, len: 1
--------------------len: 1, value:-2
------------SQLIND
----------------len: 2, value: 0
------------SQLVAR
----------------len: 2, value: 2
------------SQLDATA
----------------SQLSRV GENERALIZED NUMBER, len: 12
--------------------len: 12, value~ 32768.000000
------------SQLIND
----------------len: 2, value: 0
------------SQLVAR
----------------len: 2, value: 3
------------SQLDATA
----------------SQLSRV GENERALIZED_DATE, len: 8
--------------------le~: 8, value: 19880702
------------SQLIND
----------------len: 2, value: 0
--------END OF MESSAGE

(continued on next page)

C-10 Sample Log Flies

Example C-3 (Cont.)

Sample Message Protocol Level Log

PROTOCOL LEVEL LOG CLIENT: read
----PACKET ID: 6, PACKET SEQUENCE: 0
--------SQLSRV_EXECUTE ACK
------------STATUS
----------------SQLSRV GENERALIZED NUMBER, len: 1
--------------------len: 1, value:-0
------------EXECUTE PARAMETER
----------------SQLSRV GENERALIZED NUMBER, len: 1
--------------------len: 1, value:-1
------~-END OF MESSAGE
PROTOCOL LEVEL LOG CLIENT: write (logonly)
----PACKET ID: 7, PACKET SEQUENCE: 0
--------SQLSRV_EXECUTE
------------STATEMENT ID
----------------SQLSRV GENERALIZED NUMBER, len: 7
--------------------len: 7, value:-1199896
------------REPEAT COUNT
----------------len: 2, value: 1
------------PARAMETER MARKER DATA
----------------len: 2, value: 4
----------~-SQLVAR

----------------len: 2, value: 0
------------SQLDATA
----------------SQLSRV ASCII STRING, len: 6
--------------------len: 6, value: xxxxxx
------------SQLIND
----------------len: 2, value: O
------------SQLVAR
----------------len: 2, value: 1
------------SQLDATA
----------------SQLSRV GENERALIZED NUMBER, len: 1
--------------------len: 1, value:-3
------------SQLIND
----------------len: 2, value: 0
------------SQLVAR
----------------len: 2, value: 2
------------SQLDATA
----------------SQLSRV GENERALIZED NUMBER, len: 13
--------------------len: 13, value~ 524288.000000
------------SQLIND
----------------len: 2, value: O
------------SQLVAR
----------------len: 2, value: 3
------------SQLDATA
----------------SQLSRV GENERALIZED DATE, len: 8
--------------------len: 8, value:-19880703
------------SQLIND
----------------len: 2, value: O
--------END OF MESSAGE

Sample Log Flies C-11

(

Index
A
ABS function, A-2
Absolute value function, A-2
ACOS function, A--3
Allocation
of data and indicator variables, 6-35
American date format, 5--3t
ANSI date format, 5--3t
API
call interface, 1-2f
routines, 3-1
Application building
on MS-DOS, 4-2
on ULTRIX, 4-2
on VMS, 4-1
Arccosine function, A--3
Arcsine function, A-6
Arctangent function, A--8, A-9
Argument vector
used in sample application, 4-7
ASC function, A-5
ASCIZ, 5-2
ASIN function, A-6
ASSOCIATE_STR
and execution logging, 4-25
CLIENT_LOG field, 7--3
description of, 7--3 to 7-5
ERRB UF field, 7-5

ASSOCIATE_STR (Cont.)
ERRBUFLEN field, 7-5
FREE_MEMORY_ROUTINE field,
7-5
LOCAL_FLAG field, 7-4
MEMORY_ROUTINE field, 7-4
RESERVED field, 7-5
SERVER_LOG field, 7-4
setting up, 4-7
summary of, 3-3
VERSION field, 7-4
Association
aborting, 6--6
creating, 4-7, 6--9
data structure, 7--3
declaring variables for, 4-7
declaring variables global to, 4--6
logging, 4-25, 7--3
sample listing, C-le
multiple, 4--6
obtaining user name, A-61
releasing, 4-7
summary of routines, 3-1
terminating, 6--37
Association identifier
declaring, 4--6
passing, 4-7
purpose of, 4-6
Association structure
See ASSOCIATE_STR

lndex-1

ATAN function, A-8
AT function, A-7
ATN2 function, A-9

B
Batched execution, 4-20
status value in SQLERRD, 7-9
Binary
data type, 5-3
Braces
in date expression, A-16
British date format, 5-3t
Buffer, message
binding to association, 6-9
choosing size of, 4-7
role in performance enhancement,
4-20

c
c
routine name format, 6-3
Case conversion function
lower to upper, A-60
upper to lower, A-32
cast operator (C)
use of, 4-18
CDOW function, A-11
CEILING function, A-12
Century
in date format, 5-3
Character-to-ASCII conversion function,
A-5
Character-to-number conversion
function, A-62
CHR function, A-13
CLIENT LOG field
in sample application, 4-7
CLOSE statement
in dynamic SQL, 2-3
CMONTH function, A-14
Column
storage of name in SQLDA, 7-15
Common logarithm function, A-31

lndex-2

Communications area
See SQLCA
Constant
in filter expression, 4-22
Conversion function
character to ASCII decimal, A-5
character to number, A-62
date to character, A-16, A-19, A-22,
A-24, A-34
degrees to radians, A-23
lowercase to uppercase, A-60
number to character, A-13
number to string, A-52
radians to degrees, A-43
uppercase to lowercase, A-32
COS function, A-15
Cosine function, A-15
Counted string
data type, 5-3
CTOD function, A-16
Cursor
closing, 4-9, 6-13
opening, 4-9, 6-31

D
Data structure
summary of, 3-3
template, 7-1
Data type
determining, 4-14, 4-17
include file, 5-1
SQUServices representation, 5-lt
SQLTYPE field, 7-13
Data variable
allocating, 4-13
Date
controlling format of, 5-3
Date-to-character conversion function,
A-16,A-19,A-22,A-24,A-34
DAY function, A-17
Day of month function, A-17
Day of week function, A-11, A-21

(
\

DECLARE CURSOR statement
in dynamic SQL, 2-2
using, 2-3
DECnet, 1-2f
error code, 7-9
DECnet-DOS
Programming Interface Library, 4-2
Degrees-to-radians conversion function,
A-23
DELETE statement
and SQLERRD array, 2-7
status value in SQLERRD, 7-lOt
DELETE ... WHERE CURRENT OF
statement, 4-21
DESCRIBE statement, 2-2
alternative to, 2-5
use of, 2-6
Descriptor area
See SQLDA
DIFFERENCE function, A-18
DMY function, A-19
Dollar sign
in routine names, 6-2
DOW function, A-21
Driver module
See SQLSRV$DRIVER
DSRI, 1-1, 1-2f
DTOC function, A-22
DTOR function, A-23
DTOS function, A-24
Dynamic allocation
advantages of, 4-11
of data and indicator variables, 4-13,
6-7
of parameter marker SQLDA, 4-12
of select list SQLDA, 4-15
selecting routine for, 4-7
Dynamic SQL, 1-2f, 2-1
CLOSE statement, 2-3
DECLARE CURSOR statement, 2-2
DESCRIBE statement, 2-2
EXECUTE IMMEDIATE statement,
2-2
EXECUTE statement, 2-2

Dynamic SQL (Cont.)
FETCH statement, 2-2
OPEN statement, 2-2
parameter markers, 2-5
PREPARE statement, 2-2
purpose of SQLDA, 2-6
RELEASE statement, 2-2
select list items, 2-2, 2-5
similarity to SQUServices, 1-1
statement names, 2-2
statements not dynamically
executable, 2-4 t, 2-5t
D_float
use of on VMS, 4-1

E
Environment variable
data structure, 7-16
description of, 5-3
obtaining value of, 6-29
setting value of, 6-41
ERRBUF field
in sample application, 4-7
ERRBUFLEN field
in sample application, 4-7
Error buffer
alternative, 7-3
declaring, 4-6
in sample application, 4-7
Error code
location of, 7-9t
value in SQLERRD, 7-9
Error handling, 4-18
SQLCA structure, 7-6
EXECUTE IMMEDIATE statement,
2-2
using, 2-3
EXECUTE statement, 2-2
using, 2-3
Execute_flag parameter
use of, 4-20
Execution logging, 4-25
controlling, 7-3
EXP function, A-25

lndex-3

Exponent function, A-25

F
Fetching
of multiple rows, 4-20, ~24
FETCH statement
and SQLERRD array, 2-7
in dynamic SQL, 2-2
purpose of, 2-5
status value in SQLERRD, 7-lOt
using, 2-3
Filter expression, 4-21
definition of, 4-21
elements of, 4-21
functions in, A-1
operator precedence in, 4-24
placeholders in, 4-23f
setting, 6-43
use of constant in, 4-22
use of logical opera tor in, 4-24
use of mathematical operator in
4-23
'
use of placeholders in, 4-22
use of relational operator in, 4-23
use of string operator in, 4-24
Floating-point
using options files on VMS, 4-1
FLOOR function, A-26
FREE_MEMORY_ROUTINE field
in sample application, 4-7
French date format, 5-3t
Function
filter expression, A-1
Function prototype (C), 6-3

G
German date format, 5-3t
G_float
use of on VMS, 4-1

#include directive (C), 6-4

lndex-4

Include file
location of, 4-1
location on MS-DOS, 4-2
location on ULTRIX, 4-2
location on VMS, 4-1
use of, 6-4
use of in application program, 4-1
Index array
use of, 4-22
Indicator variable
allocating, 4-13
field in SQLDA, 7-15
INSERT statement
and SQLERRD array, 2-7
status value in SQLERRD, 7-lOt
INT function, A-27
Italian date format, 5-3t

J
Japanese date format, 5-3t

L
LEFT function, A-28
LEN function, A-29
Linking
on MS-DOS, 4-2
on VMS, 4-1
Local input/output
controlling, 7-3
LOCAL_FLAG field
in sample application, 4-7
LOGlO function, A-31
LOG function, A-30
Logging
association, 4-25
sample listing, C-le
in sample application, 4-7
message protocol, 4-28
sample listing, C-7e
program execution, 4-25
routine, 4-26
sample listing, C-2e

Logical operator
in filter expression, 4-24
LOWER function, A-32
LTRIM function, A-33

M
Mathematical operator
in filter expression, 4-23
MDY function, A-34
Memory allocation
defining routines for, 7-3
MEMORY_ROUTINE field
in sample application, 4-7
Message buffer
binding to association, 6-9
choosing size of, 4-7
role in performance enhancement,
4-20
Message protocol, 1-1
logging, 4-25t, 4-28, 7-3
sample listing, C-7e
MOD function, A-35
MONTH function, A-36
MS-DOS operating system
building applications on, 4-2
building sample application on, 4-3
network error codes, 7-9t

N
Name
obtaining current, A-61
Name of month function, A-14
Natural logarithm function, A-30
Network
creating link, 6-9
disconnecting link, 6--6, 6--37
error code, 7-9
Number-to-character conversion
function, A-13
Number-to-string conversion function,
A-52
Numeric data
scale factor, 7-14

0
OPEN statement
and SQLERRD array, 2-7
in dynamic SQL, 2-2
status value in SQLERRD, 7-lOt
using, 2-3
Operator
logical, 4-24
mathematical, 4-23
precedence, 4-24
relational, 4-23
string, 4-24
Options file
use of, 4-1

p
Parameter
access to data, 6-5
data type, 6-4
documentation format, 6-4
passing mechanism, 6-5
Parameter marker
checking for, 4-9
definition of, 2-5
in batched execution, 4-20
invalid in EXECUTE IMMEDIATE
statement, 2-3
processing, 4-13
purpose of, 2-3
SQLVAR structure, 7-13
testing for, 4-12
valid SQL statements, 2-4t
Performance
enhancing, 4-19
Phonetic matching, A-48
Pl function, A-37
Placeholder
in filter expression, 4-22
Prepared statement
releasing, 4-18
PREPARE statement, 2-2
use of, 2-6

lndex-5

PREPARE statement (Cont.)
using, 2-3
printf routine (C)
use of, 4-18
Protocol
logging, 4-28
message, 1-2f
Proxy access, ~10
Pseudorandom number function, A-38

Q
Question mark
in filter expression
See Placeholder
in SQL statement
See Parameter marker

R
Radians-to-degrees conversion function
A.-43
'
RA.ND function, A.-38
Relational opera tor
in filter expression, 4-23
RELEASE statement
in dynamic SQL, 2-2
status value in SQLERRD, 7-lOt
Releasing
data and indicator variables, ~27
prepared statement resources, ~9
Remainder function, A.-35
REPLICA.TE function, A.-39
Result table
creating, ~1
displaying, 4-9
fetching from, ~21
fetching multiple rows from 4-20
~24
'
'
filtering, 4-21, ~3
processing, 4-16
summary of routines, 3-2
Return value
of A.PI routine, ~2
RIGHT function, A.-40

lndex-6

Root (square) function, A.-51
ROUND function, A-41
Routine logging, 4-25t, 4-26, 7-3
sample listing, C-2e
Routine template
description of, ~1
RTOD function, A.-43
RTRIM function, A.-44

s
Sample application
See SQLSRV$DYNA.MIC
Scale factor
in numeric data, 7-14
Seed
in RAND function, A.-38
SELECT
checking for, 4-9
handling in SQLSRV$DYNA.MIC
4-3
'
processing, 4-16
testing for, 4-15
Select list
and DESCRIBE statement, 2-6
mapping of items to variables, 2-5
SQLVAR structure, 7-13
valid SQL statements, 2-4t
SELECT LIST clause
of PREPARE statement, 2-2
Select list items
and PREPARE statement, 2-2
SELECT statement
and SQLERRD array, 2-7
in dynamic SQL, 2-5
invalid in EXECUTE statement, 2-3
SERVER_LOG field
in sample application, 4-7
SIGN function, A.-45
SIN function, A.-46
Singleton SELECT, 2-5t
sizeof operator (C)
use of, 4-14, 4-18
Sound-alike matching, A-48

(

SOUNDEX function, A--48
and DIFFERENCE function, A-18
SPACE function, A-50
SQL
communications area
See SQLCA
descriptor area
See SQLDA
SQL$DYNAMIC, 4-3
SQLCA
allocating, 6-11
binding to association, 6-9
declaring, 4-6
definition of, 2-7
description of, 7-6 to 7-8
execution results in, 6-15, 6-17
6-19
'
number of environment variables
6-30
'
purpose of, 4-6
SQLCABC field, 7-7
SQLCAID field, 7-7
SQLCODE field, 7-7
SQLERRD field, 7-8
description of, 7-9 to 7-10
SQLERRM field, 7-7, 7-8
SQLEXT field, 7-8
SQLWARN field, 7-8
summary of, 3--4
use of, 6-3t
SQLCODE field
purpose of, 2-7
use of, 4-18
SQLDA
allocating, 4-10
allocating data variables for, 4-9
allocating indicator variables for, 4-9
allocation of data and indicator
variables, 6-7, 6-35
as defined in SQLSRVDA.H, 4-11
declaring, 4-8, 4-10
definition of, 2-6
description of, 7-11 to 7-12
dynamic allocation of, 4-12, 4-15

SQLDA (Cont.)
index array, 6--44
initialization of, 6-33
parameter marker, 2-5, 6-17, 6-32
initialization of, 6-34
releasing, 6-39
releasing data and indicator variables,
6-27
select list, 2-5, 6-21, 6-22
initialization of, 6-34
SQLABC field, 7-11
SQLDAID field, 7-11
SQLD field, 7-12
SQLN field, 7-12
SQLVARARY field, 7-12
SQLVAR structure
SQLDATA field, 7-14
SQLIND field, 7-14
SQLLEN field, 7-14
SQLNAME field, 7-15
SQLNAME_LEN field, 7-15
SQLTYPE field, 7-13 .
static allocation of, 4-12, 4-15
summary of, 3-3
SQL data type
SQL/Services representation, 5-lt
SQLD field
use of in loop, 4-13, 4-17
SQLERRD
contents of, 6-17
description of, 7-9 to 7-10
SQLERRD array
purpose of, 2-7
SQLERRD field
use of, 4-19
SQLLEN field
purpose of, 4-13
SQLSRV$DRIVER
source code listing, B-le
structure of, 4-6
SQLSRV$DYNAMIC, 4-3 to 4-19
building on MS-DOS, 4-3
building on ULTRIX, 4-4
building on VMS, 4-3

lndex-7

SQLSRV$DYNAMIC (Cont.)
execute_statement routine in, 4-lOf
module
structure of, 4-8
modules, 4-5
running, 4-4
source code listing, B-4e
SQLSRV.H
and execution logging, 4-25
location on MS-DOS, 4-2
location on ULTRIX, 4-2
location on VMS, 4-1
SQLSRV.LOG file
purpose of, 4-25
SQLSRVCA.H
error codes in, 7-9
location on MS-DOS, 4-2
location on ULTRIX, 4-2
location on VMS, 4-1
SQLSRVDA.H, 5-1
location on MS-DOS, 4-2
location on ULTRIX, 4-2
location on VMS, 4-1
sqlsrv_abort routine
description of, 6-6
summary of, 3-1
sqlsrv_allocate_sqlda_data routine,
6-27,6-35
description of, 6-7 to 6-8
summary of, 3-3
use of, 4-16
SQLSRV_ASCII_STRING
definition of, 5-2
sqlsrv_associate routine, 4-6
and ASSOCIATE_STR, 3-3
description of, 6-9 to 6-12
in sample application, 4-7
passing address to, 4-12
summary of, 3-1
use of double indirection in, 4-7
sqlsrv_close_cursor routine, 2-4t, 6-25
description of, 6-13 to 6-14
summary of, 3-3
SQLSRV_ENV_CENTURY

lndex-8

SQLSRV_ENV_CENTURY (Cont.)
definition of, 5-3
SQLSRV_ENV_DATE
definition of, 5-3
SQLSRV_ENV_SET_EXACT
definition of, 5-4
SQLSRV_ENV_STR
description of, 7-16 to 7-17
ENV_OPT_VALUE field, 7-17
ENV_RESERVED field, 7-16
ENV_TAG field, 7-16
ENV_VALUE field, 7-17
summary of, 3-4
sqlsrv_execute routine, 2-4t
and hatched execution, 4-20
description of, 6-15 to 6-18
effect of message buffer size on, 4-7
status value in SQLERRD, 7-lOt
summary of, 3-2
use of, 4-9, 4-15
sqlsrv_execute_immediate routine, 2-5t
description of, 6-19, 6-20
status value in SQLERRD, 7-lOt
summary of, 3-2
sqlsrv_fetch routine, 2-5t, 6-22
and filter expressions, 6-43, A-1
description of, 6-21 to 6-23
status value in SQLERRD, 7-lOt
summary of, 3-3
use of, 4-16
sqlsrv_fetch_many routine, 2-5t, 6-22
description of, 6-24 to 6-26
effect of message buffer size on, 4-7
summary of, 3-3
use of, 4-20
sqlsrv_free_sqlda_data routine, 6-8,
6-35
description of, 6-27 to 6-28
purpose of, 4-13
summary of, 3-3
SQLSRV_GENERALIZED_DATE
definition of, 5-2
SQLSRV_GENERALIZED_NUMBER
and SQLLEN field, 7-14

SQLSRV_GENERALIZED_NUMBER
(Cont.)
definition of, 5-2
sqlsrv_get_environment routine
description of, &-29 to 6-30
status value in SQLERRD, 7-lOt
summary of, 3-2
sqlsrv_open_cursor routine, 2-4t, 2-5t
description of, 6-31 to 6-32
status value in SQLERRD, 7-lOt
summary of, 3-2
use of, 4-16
sqlsrv_prepare routine, 2-4t, 2-5t
description of, 6-33 to 6-36
passing address to, 4-12
summary of, 3-2
use of, 4-8, 4-15
use of double indirection in, 4-7
sqlsrv_release routine, 6-8
description of, 6-37 to 6-38
implicit deallocation in, 4-11
summary of, 3-1
sqlsrv_release_statement routine, 2-5t,
6-8,6-35
description of, 6-39 to 6-40
implicit deallocation in, 4-11
status value in SQLERRD, 7-lOt
summary of, 3-2
use of, 4-18
sqlsrv_set_environment routine
description of, 6-41 to 6-42
summary of, 3-2
sqlsrv_set_filter routine, A-1
description of, 6-43 to 6-45
summary of, 3-3
use of, 4-21
SQLSRV_VARCHAR
definition of, 5-3
use of, 4-14, 4-17
SQL statement
summary of routines, 3-2
SQL statements
CLOSE
in dynamic SQL, 2-3

SQL statements (Cont.)
DECLARE CURSOR
dynamic SQL, 2-2
DESCRIBE, 2-2
dynamically executable, 2-4t
EXECUTE, 2-2
EXECUTE IMMEDIATE, 2-2
FETCH
in dynamic SQL, 2-2
not dynamically executable, 2-4t,
2-5t
OPEN
in dynamic SQL, 2-2
PREPARE, 2-2
RELEASE
in dynamic SQL, 2-2
SQLTYPE field
use of, 4-14, 4-17
SQLVAR
as defined in SQLSRVDA.H, 4-11
description of, 7-13 to 7-15
mapping placeholders to, 4-23
SQLDATA field, 7-14
SQLIND field, 7-14
SQLLEN field, 7-14
SQLNAME field, 7-15
SQLNAME_LEN field, 7-15
SQLTYPE field, 7-13
SQLVARARY
length of, 4-11
purpose of, 4-11
SQRT function, A-51
Square root function, A-51
Stack
in MS-DOS applications, 4-2
Statement, prepared
releasing, 4-18
STR function, A-52
String
counted
data type, 5-3
generation of, A-50
null-terminated, 5-2
String length function, A-29

lndex-9

String matching
controlling, 5-4
String operator
in filter expression, 4-24
String parsing function
LTRIM, A-33
RTRIM, A-44
TRIM, A-59
String search function, A-7
strncpy routine (C)
use of, 4-15
STUFF function, A-54
SUBSTR function, A-56
Substring extraction function, A-56
Substring function
left, A-28
right, A-40
Substring replacement function A-54
SYS$LIBRARY
'
include files in, 4-1

T
TAN function, A-57
Tangent function, A-57
TIME function, A-58
Transaction
aborting, 6-6
committing, 6-37
TRIM function, A-59
Truncation function, A-27

u
ULTRIX operating system
building applications on, 4-2
building sample application on, 4-4
C compiler
and function prototypes, 6-3
network error codes, 7-9t
UPDATE statement
and SQLERRD array, 2-7
status value in SQLERRD, 7-lOt
UPDATE ... WHERE CURRENT OF
statement, 4-21

lndex-10

UPPER function, A-60
USA date format, 5-3t
USER function, A-61
User names
obtaining current, A-61

v
VAL function, A-62
Variables
in filter expression, 4-22
represented by parameter marker
2-5
'
VAX routine name format, 6-2, 6-3
Video display
and execution logging, 4-25t, 7-3
VMS operating system
building applications on, 4-1
building sample application on, 4-3
status code definitions, 7-9t

y
YEAR function, A-63

How to Order Additional Documentation

Technical Support
If you need help deciding which documentation best meets your needs, call 800-343-4040
before placing your electronic, telephone, or direct mail order.

Electronic Orders
To place an order at the Electronic Store, dial 800-DEC-DEMO (800-332-3366) using
a 1200- or 2400-baud modem. If you need assistance using the Electronic Store,
call 800-DIGITAL (800-344-4825).

Telephone and Direct Mail Orders
Your Location

Call

Contact

Continental USA,
Alaska, or Hawaii

800-DIGITAL

Digital Equipment Corporation
P.O. Box CS2008
Nashua, New Hampshire 03061

Puerto Rico

809-754-7575

Local DIGITAL subsidiary

Canada

800-267-6215

Digital Equipment of Canada
Attn: DECdirect Operations KA02/2
P.O. Box 13000
100 Herzberg Road
Kanata, Ontario, Canada K2K 2A6

International
Internal 1

Local DIGITAL subsidiary or
approved distributor
SDC Order Processing - WMO/E15
or

Software Distribution Center
Digital Equipment Corporation
Westminster, Massachusetts 014 73

1For internal orders, you must submit an Internal Software Order Form (EN-01740-07).

(

Reader's Comments

VAX Rdb/VMS
Guide to Using SQUServlces
AA-ND79A-TE

Please use this form to comment on this manual. If you require a written reply to a
software problem and are eligible to receive one under Software Performance Report
(SPR) service, submit your comments on an SPR form.
Thank you for your assistance.

I rate this manual's:

Excellent
Accuracy (software works as manual says)
D
Completeness (enough information)
D
Clarity (easy to understand)
Organization (structure of subject matter)
Figures (useful)
Examples (useful)
Index (ability to find topic)
Page layout (easy to find information)

D
D
D
D
D
D

Good

Fair

Poor

D
D
D
D
D
D
D

0
0

D
D

0
0
0
D
0
D

D
D
D
0

I would like to see more/less
What I like best about this manual is
What I like least about this manual is

I found the following errors in this manual:
Page
Description

Additional comments or suggestions to improve this manual:

I am using Version _ _ _ of the software this manual describes.
Name!l'itle
Dept.
Date

Company
Mailing Address
Phone

I
I
I
I

- - - - - ---- -- - - - ---- -- --- -- - - ---·Fold Here and Tape - ------- - - - - - - - - - - - - - - - - - - - - - - - _1

Please

mamaamalM

Affix Stamp
Here

DIGITAL EQUIPMENT CORPORATION

Corporate User Publications
200 Forest Street
MR01-3/L12
Marlborough, MA 01752-9101

• - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Fold Here • - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

(

Reader's Comments

VAX Rdb/VMS
Gulde to Using SOL/Services
AA-ND79A-TE

I rate this manual's:
Accuracy (software works as manual says)
Completeness (enough information)
Clarity (easy to understand)
Organization (structure of subject matter)
Figures (useful)
Examples (useful)
Index (ability to find topic)
Page layout (easy to find information)

Excellent

Good

Fair

Poor

D
D
D
D
D
D
D
D

D
0
0
0
0
0
0
0

I would like to see more/less
What I like best about this manual is
What I like least about this manual is

I found the following errors in this manual:
Page
Description

Additional comments or suggestions to improve this manual:

I am using Version _ _ _ of the software this manual describes.
N ame!I'itle
Dept.
Company

Date

Mailing Address
Phone

I
I
I
I
I
I

------------------------------·Fold Here and Tape--------------------------------' /'
Please
Affix Stamp
Here

DIGITAL EQUIPMENT CORPORATION

Corporate User Publications
200 Forest Street
MR01-3/L12
Marlborough, MA 01752-9101

----------------------------------Fo~H~e-----------------------------------