Programming Microsoft Embedded SQL for C

Microsoft® SQL Server™

Version 6.5

For the Microsoft® Windows NT™ Operating System

Microsoft Corporation

Information in this document is subject to change without notice. Companies, names, and data used in examples herein are fictitious unless otherwise noted. No part of this document may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without the express written permission of Microsoft Corporation.

©1994, 1996 Microsoft Corporation. All rights reserved.

Microsoft, MS, MS-DOS, CodeView, Visual Basic, Visual C++, Windows, Windows NT, and Win32 are either registered trademarks or trademarks of Microsoft in the United States and/or other countries.

Companies, names, and/or data used in screens and sample output are fictitious unless otherwise noted.

DB-Library is a trademark and Transact-SQL is a registered trademark of Sybase, Inc.

IBM is a registered trademark of International Business Machines Corporation.

Intel is a registered trademark of Intel Corporation.

Document No. xxxx
12345-6

Printed in the United States of America.


About This Manual

The information in this manual describes Embedded SQL for C and provides details about building applications for the Windows® operating system (16-bit), the Windows NT™ operating system, the Windows 95 operating system, and the MS-DOS® operating system. This manual also includes a directory of Embedded SQL statements.

This manual is written for programmers who are using the C programming language to develop applications for Microsoft SQL Server and who are familiar with the Transact-SQL® and C languages. You should also be familiar with the Windows NT, Windows 95, 16-bit Windows, or MS-DOS operating system.


Document Conventions

Throughout this manual, 16-bit Windows refers to the Microsoft Windows and the Windows for Workgroups operating systems unless a distinction is otherwise specified. Windows NT and Windows 95 are 32-bit operating systems. 16-bit Visual C++® refers to the Visual C++ development system (16-bit), and 32-bit Visual C++ refers to theVisual C++ development system (32-bit).

Throughout this manual, the conventions that are listed in the following table are used to distinguish elements of text.

Convention | Purpose
Bold | Represents program names, C commands, required syntax, and values.
Italic | Represents SQL datatypes and user-defined defaults.
Monospace | Represents examples, screen output, program code, and error messages.
UPPERCASE | Represents filenames, paths, and statements. You can, however, type entries in any combination of uppercase or lowercase letters.
braces ({ }) | Represents required items in syntax statements. Required information is contained within the braces and does not include the braces themselves.
brackets ([ ]) | Represents optional items in syntax statements. Optional information is contained within the brackets and does not include the brackets themselves.
vertical bar ( | ) | Separates items that are contained within braces or brackets. The vertical bar means you must choose one and only one item.
ellipsis ( ... ) | Indicates that the variable that precedes the ellipsis can be specified more than once.


Microsoft SQL Server Information

For more information about using Microsoft SQL Server, see the following manuals:

Microsoft SQL Server Programming DB-Library for C

Provides information about developing applications for SQL Server using the DB-Library for C call-level interface.

Microsoft SQL Server Programming DB-Library for Visual Basic

Provides information about the syntax and references for the DB-Library for Visual Basic API, which is used to write Microsoft Visual Basic® programming system-based client programs for the Windows operating systems.

Microsoft SQL Server Programming Open Data Services

Provides information about the syntax and references for Open Data Services, which is used to write server applications for client/server systems.

Microsoft SQL Server Programming SQL Distributed Management Objects

Provides information about the syntax and references for SQL Distributed Management Objects, which are 32-bit OLE Automation objects for the Windows 95 and Windows NT operating systems. The objects and their properties, methods, lists, and collections are used to write scripts and programs that can be used to administer multiple SQL Servers that are distributed across a network.

Microsoft SQL Server Transact-SQL Reference

Provides information about how to use Transact-SQL statements and other features to access databases. Provides syntax, descriptions, and examples for Transact-SQL statements, system stored procedures, utility programs, and system tables.

Microsoft SQL Server Database Developer's Companion

Provides an overview of Transact-SQL, which is an enhanced version of SQL (structured query language). Discusses managing data and database objects, such as tables, views, indexes, stored procedures, defaults, rules, constraints, and user-defined datatypes.

Microsoft SQL Server Setup

Provides instructions for installing Microsoft SQL Server software on servers and workstations. Discusses installing client utilities.

Microsoft SQL Server Administrator's Companion

Provides information about administering Microsoft SQL Server. Contains overview information, as well as procedures and examples.

What's New in Microsoft SQL Server 6.5

Provides details about upgrades and changes that were made to SQL Server to provide additional capabilities and to meet ANSI standards.


Chapter 1: Overview

Microsoft Embedded SQL for C offers programmers an alternative to writing Microsoft SQL Server client applications using the DB-Library for C or Open Database Connectivity (ODBC) application programming interfaces (APIs). Embedded SQL enables you to incorporate Transact-SQL statements into your C-language programs.


Call-level Method

SQL Server programs that are written using the DB-Library or ODBC API methods communicate directly with SQL Server through C function calls. DB-Library or ODBC functions pass SQL statements to SQL Server and return the results of queries. The call-level method of programming requires no precompiler; however, you cannot include Transact SQL statements in your C programs, like you can with Embedded SQL.

For example, to use DB-Library to connect to SQL Server and execute a simple query using the pubs sample database requires source code similar to the following:

#define DBNTWIN32
#include <sqlfront.h>
#include <sqldb.h>

main()
{
    DBPROCESS *dbproc;
    LOGINREC *login;
    RETCODE r;

    dbinit();
    login = dblogin();
    if (login == NULL)
        return (1);
    DBSETLUSER(login, "my_login");
    DBSETLPWD(login, "my_password");
    dbproc = dbopen(login, "my_server");
    dbfreelogin(login);
    if (dbproc == NULL)
        return (1);
    dbuse(dbproc, "pubs");
    dbcmd(dbproc,
        "select au_fname from authors where au_lname = 'White'");
    r = dbsqlexec(dbproc);
    if (r == FAIL)
        return (1);
    while (1)
    {
        r = dbresults(dbproc);
        if (r == SUCCEED)
        {
            /* Process the rows with dbnextrow() */
        }
        if ((r == FAIL) || (r == NO_MORE_RESULTS))
            break;
    }
    return (0);
}

The DB-Library approach, using C function calls, is more verbose and more flexible than the Embedded SQL approach, and it can be loosely coupled to any database structure. Because a great deal of the program's behavior can be changed dynamically, DB-Library programs are often general-purpose applications. DB-Library is well suited for environments where the database structure is not known in advance.


Embedded SQL Method

Embedded SQL programs require preprocessing by a precompiler. The Embedded SQL precompiler converts Embedded SQL statements in the program into function calls that can be accepted by a C compiler. The C compiler can then compile the resulting source code into an executable program.

For example, the following Embedded SQL code does the same tasks as the DB-Library example in the "Call-level Method" section earlier in this chapter:

main()
{
    EXEC SQL BEGIN DECLARE SECTION;
    char first_name[50];
    char last_name[] = "White";
    EXEC SQL END DECLARE SECTION;

    EXEC SQL CONNECT TO my_server.pubs
        USER my_login.my_password;
    EXEC SQL SELECT au_fname INTO :first_name
        from authors where au_lname = :last_name;
    return (0);
}

Note that each Embedded SQL statement starts with the introductory expression EXEC SQL. This expression tells the precompiler that the code that is entered between EXEC SQL and the semicolon (;) contains Embedded SQL statements.

The Embedded SQL approach, using programming statements similar to Transact-SQL, is more concise than the call-level method approach and is tightly coupled to the existing database structure. Because the SQL statements are directly included in the C source code, Embedded SQL programs are usually special-purpose applications. Embedded SQL is well suited for environments where the C programmer is also in control of the database structure. However, Embedded SQL is less flexible in environments where the database structure is changing or is not predictable.


System Requirements

Using Embedded SQL, you can compile and run applications for Windows NT, Windows 95, 16-bit Windows, or MS-DOS operating systems. To take advantage of statements that are enhanced for Microsoft Embedded SQL for C version 6.5, you must recompile your source code.


Windows NT

  • Microsoft Windows NT Workstation version 3.51 or later

    Or

    Microsoft Windows NT Server version 3.51 or later

  • Visual C++ development system (32-bit) version 2.0 or later

    Or

    A 100-percent compatible compiler and linker

  • Microsoft SQL Server version 6.0 or later; the NTWDBLIB.DLL file must be version 6.0 or later


Windows 95

  • Microsoft Windows 95
  • Visual C++ development system (32-bit) version 2.0 or later

    Or

    A 100-percent compatible compiler and linker

  • Microsoft SQL Server version 6.0 or later; the NTWDBLIB.DLL file must be version 6.0 or later


Windows

  • Microsoft Windows version 3.1 or later
    • Or
    • Microsoft Windows for Workgroups version 3.11 or later
  • Visual C++ development system (16 bit) version 1.52 or later

    Or

    A 100-percent compatible compiler and linker

  • Microsoft SQL Server version 6.0 or later; the MSDBLIB3.DLL file must be version 6.0 or later


MS-DOS

  • Microsoft MS-DOS version 6.22 or later
  • Visual C++ development system (16-bit) version 1.52 or later
    • Or
    • A 100-percent compatible compiler or linker
  • Microsoft SQL Server version 6.0 or later


Embedded SQL for C Files

The setup program installs the following groups of files:

In C:\MSSQL\BIN

File | Description
SQLAKW.DLL | Run-time services for 16-bit Windows
SQLPREP.EXE | Precompiler for 16-bit Windows and MS-DOS
SQLPREP1.EXE | Precompiler supplement for 16-bit Windows and MS-DOS

In C:\MSSQL\BINN

File | Description
NSQLPREP.EXE | Precompiler for Windows NT and Windows 95
SQLAIW32.DLL | Precompiler services for Windows NT and Windows 95
SQLAKW32.DLL | Run-time services for Windows NT and Windows 95

In C:\MSSQL\ESQL

File | Description
README.TXT | Text file with new information about Embedded SQL

In C:\MSSQL\ESQL\INCLUDE

File | Description
SQLCA.H | SQLCA header
SQLDA.H | SQLDA header

In C:\MSSQL\ESQL\LIB

File | Description
CAR.LIB | SQLCA library for MS-DOS
CAW.LIB | SQLCA library for 16-bit Windows
CAW32.LIB | SQLCA library for Windows NT and Windows 95
SQLAKD.LIB | Run-time services static library for MS-DOS
SQLAKW.LIB | Run-time services import library for 16-bit Windows
SQLAKW32.LIB | Run-time services import library for Windows NT and Windows 95

In C:\MSSQL\ESQL\MISC

File | Description
MSDBLIB3.DLL | DB-Library for 16-bit Windows
NTWDBLIB.DLL | DB-Library for Windows NT and Windows 95
RLDBLIB.LIB | Large-model DB-Library static library for MS-DOS

In C:\MSSQL\ESQL\SAMPLES

File | Description
*.* | C samples


Chapter 2: Embedded SQL Programming

Embedded SQL programs incorporate Transact-SQL statements into C source code. But because you cannot directly submit Embedded SQL programs to a C compiler, Embedded SQL source programs must be processed by a precompiler that produces source code that is acceptable to a C compiler.

Embedded SQL programming is a multistep development process that converts your original Embedded SQL source code into a SQL Server application—an executable file that is compiled for the appropriate operating system.


Embedded SQL Steps

Embedded SQL programming operates as follows:

1. From the command prompt, the name of the Embedded SQL source file and the appropriate build parameters are submitted to sqlprep, which is the Embedded SQL precompiler. The precompiler parses the submitted file, finds the Embedded SQL statements that are included in the code, and processes the statements.

2. The precompiler produces a C source code file with the Embedded SQL statements removed and, if appropriate, a bind file. The Embedded SQL statements are replaced by calls to the run-time library. The run-time library calls DB-Library to access SQL Servers across a network.

During this step, you can specify that stored procedures be automatically created and stored on a specific SQL Server or saved in a bind file. Bind files are Transact-SQL scripts that have a .BND extension.

3. The C source code file is compiled with a supported C compiler to produce an object code file.

4. The object code file and library routines are linked together with a supported linker to produce an executable file.

For more information about building an Embedded SQL application, see Chapter 4, "Building Applications."


Including Embedded SQL Statements

You can include Embedded SQL statements in the portions of your C code where C functions or routines can be placed. To distinguish Embedded SQL statements from the C source code, each Embedded SQL statement must begin with the introductory keyword EXEC SQL and end with a semicolon (;). An Embedded SQL statement that does not end with a semicolon (;) usually results in sqlprep compiler error –19104 "Incorrect SQL statement syntax."

You can use a backslash ( \ ) to continue SQL strings across more than one line of source code. A single quote must precede the first character of the SQL string on the first line of source code, and a single quote must appear after the last character of the SQL string on the last line of source code. For example:

EXEC SQL INSERT INTO TEXT132 VALUES ('TEST 192 IS THE TEST FOR THE R\
ULE OF THE CONTINUATION OF LINES FROM ONE LINE TO THE NEXT LINE.');

You can also insert C language code after the EXEC SQL keyword on the same line. For example:

EXEC SQL COMMIT TRAN; printf("\n");

Note

To include C language code after an EXEC SQL keyword on the same line, you must use the /NOLINES precompiler option.

You can also include Embedded SQL variable declaration sections in C language code where it is valid to declare variables. Use the BEGIN DECLARE SECTION and END DECLARE SECTION statements.


Embedded SQL Syntax

Embedded SQL syntax combines the IBM®-standard ANSI Embedded SQL syntax and most of the Transact-SQL syntax. Embedded SQL statements work somewhat differently from, or are in addition to, standard Transact-SQL statements.


Naming Conventions

Embedded SQL keywords and statements in your programs are not case-sensitive. For example, the following Embedded SQL statement fragments are equivalent:

EXEC SQL CONNECT TO
exec sql connect to
Exec Sql Connect To

However, embedded SQL cursor, prepared statement, and connection names are case-sensitive. The same case must be used to declare and use these names. For example, the following fragments declare two different cursors:

DECLARE CUR_NAME CURSOR
DECLARE cur_name CURSOR

The sort order of the SQL Server you are connecting to determines whether other words are case-sensitive.

Note that hyphens (-) are not permitted in Transact-SQL identifiers such as table and column names.


Scope and Visibility

The scope of Embedded SQL variable names (statements, cursors, and connections) follow the rules that apply to C variables.


Connecting to a Database

Use a CONNECT TO statement in your application to specify the SQL Server name, the database name, the login ID, and the password for the connection. You can connect to servers and databases other than those that were used during precompiling.

The default time-out for a database connection is 10 seconds. If the specified server does not respond to the connection request, or if the network name lookup fails, the compilation or execution suspends for approximately 10 seconds. Use the SET OPTION statement to change the connection time-out.

For more information about Embedded SQL statements, see Chapter 3, "Embedded SQL Statements."


Using Static and Dynamic Statements

Embedded SQL supports both static and dynamic SQL statements. Static SQL statements can be placed into stored procedures. Dynamic SQL statements are built and executed at run time.

A static SQL statement is a complete Transact-SQL statement that is embedded in the program source code. Static SQL statements can contain host variables.

A dynamic SQL statement is an incomplete Transact-SQL statement, some or all of which is supplied at run time.

With dynamic SQL statements, knowing the complete structure of an SQL statement before building the application is not necessary. Dynamic SQL statements allow run-time input to provide information about the database objects to query.

Dynamic SQL statements that are created by using the PREPARE and EXECUTE statements can contain parameter markers and host variables. Parameter markers are question marks (?) that act as placeholders for information that is supplied at run time. Dynamic SQL statements that are executed using the EXECUTE IMMEDIATE statement must conform to Transact-SQL language rules. They cannot contain parameter markers, host variables, or keywords that pertain exclusively to Embedded SQL.


Static SQL Statements

An entire Transact-SQL transaction, including variable declarations, control-of-flow language, and calls to stored procedures, can be coded as a single static SQL statement.

Static SQL statements can contain C-program host variables for input values and for output data. Host variables are defined by the host C application and are accessible to the C and Embedded SQL sections of your application. For more information about declaring and using host variables, see "Using Host Variables" and "Using the SQLDA Data Structure," later in this chapter.

When an SQL statement uses input host variables, the values of these variables are inserted in the statement before the statement runs. Output host variables are filled with the values that are returned after the statement runs.

Important

Because Embedded SQL does not support alternate format rows, COMPUTE and COMPUTE BY clauses are ignored. If a statement returns multiple results sets, only the first results set is recognized; subsequent results sets are discarded. Also, unless a cursor is used, if a statement returns more than one row, only the first row is recognized; subsequent rows are discarded.

At compile time, static SQL statements can be compiled as stored procedures into an access plan or executed as dynamic SQL statements. (Access plans and their alternatives are described in "Access Plans and Bind Files" and "Building Applications",) When a static SQL statement contains only a single transaction-management command, such as BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION, or SAVE TRANSACTION, the static SQL statement is not compiled into an access plan because stored procedures cannot contain unbalanced transaction-management statements. In these cases, at run time, the application issues unmatched transaction-management statements as dynamic SQL statements.

The rules for Transact-SQL stored procedures apply to static SQL statements. For more information, see the Microsoft SQL Server Transact-SQL Reference.


Dynamic SQL Statements

Dynamic SQL statements are not completely embedded in the source code; instead, portions are stored in program variables that can be modified at run time. Dynamic SQL statements consist of character strings that can contain question marks (?) as parameter markers, which act as place holders for input data. For example:

DELETE AUTHORS WHERE AU_FNAME = ? AND AU_LNAME = ?

Within an application, you can use the PREPARE, EXECUTE, and EXECUTE IMMEDIATE Embedded SQL statements to process a dynamic SQL statement. In general, dynamic SQL statements are prepared by using the PREPARE statement and then executed by using the EXECUTE statement. Optionally, you can combine the two statements by using the EXECUTE IMMEDIATE statement.

Using dynamic SQL statements, you can write an application that prompts a user or scans a file for information (such as database object names) that is unavailable at compile time. Use dynamic SQL statements when you must build an ad hoc SQL statement.


Using Host Variables

You can manage input and output for Embedded SQL statements by using host variables. Host variables are standard C-program variables that are declared in an Embedded SQL declare section by using the BEGIN DECLARE SECTION and END DECLARE SECTION statements.

Use host variables when the number of items and their datatypes are known at compile time. You can use host variables in static SQL statements to specify input values or to receive output values. You can also use host variables together with parameter markers in dynamic SQL statements to specify input values or to receive the output of a dynamically prepared cursor.

When a host variable name is used in an Embedded SQL statement, the variable name begins with a colon (:). This colon enables the compiler to distinguish between host variables and tables or columns that might have the same name.

The following example is of a C program that uses host variables. The program prompts the user for an author's last name and stores the entered value in the host variable szLastName. The program then retrieves the author's first name from the pubs database and stores the result in the host variable szFirstName.

#include <stdio.h>

int main ()
{
    EXEC SQL BEGIN DECLARE SECTION;
    char szLastName[30];
    char szFirstName[30];
    EXEC SQL END DECLARE SECTION;

    EXEC SQL CONNECT TO gizmo.pubs USER sa;

    printf("Type author's last name: ");
    gets(szLastName);

    EXEC SQL SELECT au_fname INTO :szFirstName
        from authors where au_lname = :szLastName;

    printf("Author's first name is %s.", szFirstName);
    return (0);
}


Declaring Host Variables

Before you use a host variable in an Embedded SQL statement, you must declare the variable by using normal C-language syntax within an Embedded SQL host declare section. Host variable declarations start with the Embedded SQL statement BEGIN DECLARE SECTION and end with the END DECLARE SECTION statement. For example:

EXEC SQL BEGIN DECLARE SECTION;
int nID;
unsigned short usNumber;
char szName[30];
EXEC SQL END DECLARE SECTION;

Note

Embedded SQL limits the length of host variable names to 30 characters. Declaring host variables with longer names causes an error at precompile time when Embedded SQL processes the host variable.

Host variables can be declared wherever C variables can be declared. Because Embedded SQL does not attempt to parse complex C variables such as structures or arrays (other than simple character arrays), you cannot directly use structures or arrays to receive rows or columns of data. However, you can use a structure member as a host variable. You can also use a pointer to a single array element as a host variable and index that pointer as appropriate before each use.


Host Variables and Null Values

Unlike SQL, the C language does not support variables with null (unknown or missing) values. Embedded SQL enables you to store and retrieve null values from a database by using host indicator variables. Together, a host variable and its companion indicator variable specify a single SQL value. Each of the variables must be preceded by a colon (:). When a host variable is NULL, its indicator variable has the value –1. When a host variable is not NULL, the value of the indicator variable specifies the maximum length of the host variable data.

Place indicator variables immediately after the corresponding host variable that is specified in the Embedded SQL statement. For example, the following embedded UPDATE statement uses a saleprice host variable with a companion saleprice_null indicator variable:

EXEC SQL UPDATE closeoutsale
    SET temp_price = :saleprice :saleprice_null, listprice = :oldprice;

Optionally, you can precede an indicator variable with the INDICATOR keyword when using a host variable and its associated indicator variable. For example, the following embedded UPDATE statement uses the INDICATOR keyword to more easily identify the indicator variable saleprice_null:

EXEC SQL UPDATE closeoutsale
    SET temp_price = :saleprice INDICATOR :saleprice_null;

In this example, if saleprice_null has a value of –1, when the UPDATE statement executes, Embedded SQL will change the statement to:

EXEC SQL UPDATE closeoutsale
    SET temp_price = null, listprice = :oldprice;

You cannot use indicator variables in a search condition. For example, you cannot use the following Embedded SQL statement:

EXEC SQL DELETE FROM closeoutsale
    WHERE temp_price = :saleprice :saleprice_null;

However, you can use the following technique to search for null values:

if (saleprice_null == –1)
{
    EXEC SQL DELETE FROM CLOSEOUTSALE
        WHERE temp_price is null;
}
else
{
    EXEC SQL DELETE FROM CLOSEOUTSALE
        WHERE temp_price = :saleprice;
}


Host Variables and Datatypes

SQL Server uses different datatypes than does the C programming language. Embedded SQL must convert, or map, C datatypes to the appropriate SQL Server datatypes. The following Embedded SQL code fragment illustrates the mapping of three host variables that are declared as C datatypes to their corresponding SQL Server datatypes:

EXEC SQL BEGIN DECLARE SECTION;
int hostvar1 = 39;
char *hostvar2 = "telescope";
float hostvar3 = 355.95;
EXEC SQL END DECLARE SECTION;

EXEC SQL UPDATE inventory
    SET department = :hostvar1
    WHERE part_num = "4572-3";

EXEC SQL UPDATE inventory
    SET prod_descrip = :hostvar2
    WHERE part_num = "4572-3";

EXEC SQL UPDATE inventory
    SET price = :hostvar3
    WHERE part_num = "4572-3";

In the first UPDATE statement, the department column has the SQL Server smallint (integer) datatype, because the host variable hostvar1 is declared as a C int (integer) datatype. Consequently, the datatypes from C map directly to SQL Server.

In the second UPDATE statement, the prod_descrip column has the SQL Server varchar (character) datatype. The hostvar2 host variable is declared as an array of the C char (character) datatype, which maps to the SQL varchar datatype.

In the third UPDATE statement, the price column has previously been assigned the SQL Server money datatype. No datatype in C corresponds to the SQL Server money datatype. Host variables that are to be used with SQL Server money datatypes can be declared as C floating-point or character datatypes. Embedded SQL converts those host variables to and from money values.

Output host variables of datatype char are now blank padded to their full declared length which is an ANSI requirement.

Input host variables of type char that are used to input binary values must have an explicitly declared length. They cannot be pointer datatypes.

The following example is legal:

    char vBinaryIn[100];

The following example is not legal:

    char *vBinaryIn="ff00";

Be sure to carefully match the datatypes of your host variables to their corresponding use in Embedded SQL statements. For more information about mapping datatypes from the C environment to the SQL Server environment, see Appendix A, "Advanced Programming."


Using the SQLDA Data Structure

When the number or datatypes of host variables to be passed are unknown at compile time, use the SQL descriptor area (SQLDA) data structure instead of host variables. You can use the SQLDA data structure to define the type of data to be passed from the database to the host variable, or vice versa. The SQLDA data structure is generally used with question marks (?) as parameter markers to specify input values for prepared SQL statements. You can also use the SQLDA data structure with the DESCRIBE or PREPARE INTO statements to receive data from a prepared SELECT statement. Although you cannot use the SQLDA data structure with static SQL statements, you can use it with a cursor FETCH statement, regardless of whether the cursor is dynamic or static.


Data Input and Output Using the SQLDA Data Structure

The SQLDA data structure contains descriptive information about each input parameter or output column. The structure contains the column name, the datatype, the length, and a pointer to the actual data buffer for each input or output variable.

For output data, you can use the DESCRIBE statement (or the PREPARE statement with the INTO option) to enter column name, datatype, and other data into the appropriate fields of the SQLDA data structure.

Before using the SQLDA structure in a PREPARE INTO or DESCRIBE statement, your application must set the length of SQLDA and the maximum number of entries.

To use the SQLDA structure for input data, your application must supply the data for the fields of the entire SQLDA data structure. If the sqltype field has an odd code number (value), the address of the indicator variable must also be supplied. (For more information about the SQLDA data structure, see Appendix A, "Advanced Programming." )


Using Cursors

When you write code for a transaction that retrieves a single row of results, you can use a SELECT INTO statement. This is called a singleton select statement.

When you write code for a transaction where the results set includes several rows of data, you must declare and use a cursor. For example, if you write code that includes a SELECT statement or stored procedure that returns multiple rows, you must declare a cursor and associate it with the SELECT statement. Then, by using the FETCH statement, you can retrieve one row at a time from the results set. A cursor is a mechanism you can use to fetch rows one at a time.

You can also use cursors to perform operations within a results set. These operations are known as positioned update and positioned delete. For more information, see "Positioned Update or Delete Statements," later in this chapter.

Important

Do not use Embedded SQL cursors to process Transact-SQL batches or other operations that return multiple results sets. If a statement returns multiple results sets, only the first results set is recognized and subsequent results sets are discarded. If COMPUTE rows are returned, the rows are also ignored.

Embedded SQL includes standard cursor types and browse cursor types. A standard cursor is used to retrieve one row of data at a time and shares the same connection to SQL Server as the main program. Standard cursors require a unique index in SQL Server version 6.0 and earlier. To set standard cursors, use the SET CURSORTYPE CUR_STANDARD statement or the DECLARE CURSOR statement with the FOR UPDATE option. A browse cursor is used to retrieve one row of data at a time and requires a separate connection to SQL Server. To set browse cursors, use the SET CURSORTYPE CUR_BROWSE statement.

Standard and browse cursors are declared and used (including FETCH and positioned update or delete operations) in the same way. Standard cursors allow multiple cursor operations to share the same connection to SQL Server; each browse cursor requires a separate connection. For most applications, standard cursors are recommended and are the defaults because a shared single connection avoids potential locking conflicts between cursors. Standard cursors also use the cursor functions of DB-Library, so Embedded SQL applications that use standard cursors can automatically take advantage of any future performance enhancements in DB-Library cursors.

Appendix B, "Standard DB-Library cursors," provides detailed descriptions of standard DB-Library cursors. DB-Library cursors have several options for controlling row membership, locking, and performance characteristics. These options are available to Embedded SQL programs through the SET ANSI_DEFAULTS, SET CURSOR_CLOSE_ON_COMMIT, SET SCROLLOPTION, SET CONCURRENCY, and SET FETCHBUFFER statements. A SET option remains in effect for all cursor operations within an Embedded SQL program until that option is changed by another SET statement.

Appendix C, "Browse Cursors," provides details about browse cursors. If positioned update or delete statements are to be used on a browse cursor, the SELECT statement that is used in the cursor declaration must include the FOR BROWSE option. However, because each browse cursor uses a separate database connection, SQL Server treats each cursor as a separate user. This can result in locking conflicts between different cursors that are used in the same program. For more information, see Appendix C, "Cursors and Lock Conflicts."


Declaring Cursors

To define a cursor for row-at-a-time retrieval, use the DECLARE CURSOR statement. You can declare a cursor in one of two ways: as a static cursoror as a dynamic cursor .

Using Static Cursors

For a static cursor, the complete SELECT statement is contained in the DECLARE CURSOR statement. The SELECT statement can contain host variables for input parameters. When the OPEN statement is performed on a cursor, the values of the input parameters for the host variable are read into the SELECT statement. You cannot specify host variables and SQLDA data structures in the OPEN statement for a static cursor, because the input host variables are already identified in the DECLARE CURSOR statement.

This is an example of a static cursor:

EXEC SQL BEGIN DECLARE SECTION;
char szLastName[] = "White";
char szFirstName[30];
EXEC SQL END DECLARE SECTION;

EXEC SQL
    DECLARE author_cursor CURSOR FOR
    SELECT au_fname from authors where au_lname = :szLastName;

EXEC SQL OPEN author_cursor;
EXEC SQL FETCH author_cursor INTO :szFirstName;

Using Dynamic Cursors

For a dynamic cursor, the SELECT statement is not contained in the DECLARE CURSOR statement. Instead, the DECLARE CURSOR statement references the name of a prepared SELECT statement. A prepared SELECT statement can contain parameter markers (?) to indicate that data is to be supplied when a cursor is opened. You must declare a dynamic cursor by using the DECLARE CURSOR statement before you prepare a SELECT statement.

When a prepared SELECT statement contains parameter markers, the corresponding OPEN statement must specify the host variables or the name of the SQLDA data structure that will supply the values for the parameter markers. The datatype, length, and address fields of the specified SQLDA data structure must already contain valid data.

This is an example of a dynamic cursor:

EXEC SQL BEGIN DECLARE SECTION;
char szCommand[] = "select au_fname from authors where au_lname = ?";
char szLastName[] = "White";
char szFirstName[30];
EXEC SQL END DECLARE SECTION;

EXEC SQL
    DECLARE author_cursor CURSOR FOR select_statement;

EXEC SQL
    PREPARE select_statement FROM :szCommand;

EXEC SQL OPEN author_cursor USING :szLastName;
EXEC SQL FETCH author_cursor INTO :szFirstName;


Positioned Update or Delete Statements

Positioned UPDATE and DELETE statements are used in conjunction with cursors and include WHERE CURRENT OF clauses instead of search condition clauses. The WHERE CURRENT OF clause specifies the location of the corresponding cursor.

You can perform a prepared positioned update or a ROLLBACK TRANSACTION operation only on dynamic cursors.

On a cursor declaration that contains a join, you can perform a positioned update or positioned delete only on the first table in the join list.


Managing Transactions

With Embedded SQL, you can use the full transaction control facilities of SQL Server.

The COMMIT statement marks the end of a user-defined transaction that was initiated by a BEGIN TRANSACTION statement. The COMMIT statement makes changes to the transaction's database permanent and visible to other users. It also removes all locks from the affected data so that other users can access the data.

As with other SQL Server applications, statements that are not bound by BEGIN TRANSACTION and COMMIT are automatically committed when the statement executes without an error.

The ROLLBACK statement reverses the effects of a user-specified transaction to the beginning of the OPEN TRANSACTION or to the last save point (marked by a Transact-SQL SAVE TRANSACTION statement) inside the open transaction. After a transaction is committed, it cannot be rolled back.

Note that by default, a COMMIT or ROLLBACK statement does not close cursors and applies only to the current connection if multiple connections are active. You can use the SET CURSOR_CLOSE_ON_COMMIT statement to automatically close all cursors on a connection when a COMMIT TRANSACTION or a ROLLBACK TRANSACTION statement is issued.


Using the SQLCA Data Structure

SQL Server uses the SQL communications area (SQLCA) data structure to trap and report run-time errors to your Embedded SQL applications. Your application can check the error fields and status indicators of the SQLCA data structure to determine the success or failure of an Embedded SQL statement. The precompiler automatically includes the SQLCA data structure in Embedded SQL applications.

You can include routines in your application to test the SQLCODE, SQLWARN, SQLERRM, SQLERRD, and SQLSTATE fields of the SQLCA data structure and to provide followup procedures according to the status that is returned. The SQLCODE field contains the negative SQL Server error code (the Embedded SQL standard requires that error codes be negative). The SQLWARN flags are set if certain exceptions, such as data truncation, occur. The SQLERRD1 field contains the error number; the SQLERRMC field contains the text of the error message. The SQLERRD3 array indicates the number of rows that are affected. The SQLSTATE field contains run-time errors that generate ANSI-standard SQLSTATE codes.

Because the character fields of SQLCA (like SQLWARN and SQLERRMC) are FAR pointers in Windows, you must use the %Fs format specifier for them when using printf and similar functions.

For more information about the SQLCA data structure, see Appendix A, "Advanced Programming."


The SQLCODE Variable

The most important and widely used field of the SQLCA data structure is the SQLCODE variable. Each time that SQL Server runs an Embedded SQL statement, it sets the value of the SQLCODE variable to indicate whether or not the last Embedded SQL statement completed successfully. A value of 0 indicates that the last Embedded SQL statement was successful. Values other than 0 indicate warnings or errors.

To use SQLCODE in your program, you can either declare it explicitly as long SQLCODE; or leave it undeclared and have the precompiler generate a declaration. However, the precompiler will only generate a declaration in a source module that contains a main() or a WinMain() function.

The preprocessor automatically inserts a definition of

extern long SQLCODE;

in all .SQC modules, so the actual definition of SQLCODE should only occur in one module. If your project does not include a .SQC file that includes a main() or WinMain() function, you must explicitly declare SQLCODE in one of your modules. Explicit SQLCODE declarations can occur either inside or outside of a host variable declaration block.

For more information about SQLCODE, see Appendix A, "Advanced Programming."


The SQLSTATE Variable

Microsoft Embedded SQL for C version 6.5 supports SQLSTATE codes, which return errors that are encountered at run time. SQLSTATE codes fall into two categories: those that must be generated locally by DB-Library or Embedded SQL, and those that are generated by the server. SQLSTATE codes always correspond to SQLCODE values. For more information about SQLSTATE messages, see Appendix E, "Messages."


Using the WHENEVER Statement

Writing code to check the value of the SQLCODE variable after each Embedded SQL statement becomes burdensome, especially when writing large programs. Another method for checking the status of the SQLCA data structure fields is the WHENEVER statement. The WHENEVER statement is not an executable statement—it is a directive to the Embedded SQL precompiler to automatically generate code to handle errors after each executable Embedded SQL statement, and it specifies the next action to be taken. The WHENEVER statement allows one of three actions (CONTINUE, GOTO, or CALL) to be registered for each of the three possible SQLCODE conditions (SQLWARNING, SQLERROR, or NOT FOUND).

A WHENEVER statement in the program code supersedes the conditions of all previous WHENEVER statements.

This is an example of a WHENEVER statement:

EXEC SQL WHENEVER sqlerror GOTO errormessage1;

EXEC SQL DELETE FROM homesales
    WHERE equity < 10000;

EXEC SQL DELETE FROM customerlist
    WHERE salary < 40000;

EXEC SQL WHENEVER sqlerror CONTINUE;

EXEC SQL UPDATE homesales
    SET equity = equity - loanvalue;

EXEC SQL WHENEVER sqlerror GOTO errormessage2;

EXEC SQL INSERT INTO homesales (seller_name, sale_price)
    real_estate('Jane Doe', 180000.00);
        .
        .
        .
errormessage1:
    printf("SQL DELETE error: %ld\n, sqlcode);
exit();

errormessage2:
    printf("SQL INSERT error: %ld\n, sqlcode);
exit();

For more information about the WHENEVER statement, see WHENEVER in Chapter 3, "Embedded SQL Statements."


Chapter 3: Embedded SQL Statements

Embedded SQL statements work somewhat differently from, or are in addition to, standard Transact-SQL statements.

You can include Embedded SQL statements in portions of your C code where C functions or routines can be placed. Each Embedded SQL statement must begin with the introductory keyword EXEC SQL and end with a semicolon (;) to distinguish Embedded SQL statements from the rest of the C source code. The maximum size of a single Embedded SQL statement is 8,191 characters for 16-bit Windows and 19,999 characters for Windows NT.

Embedded SQL keywords and statements are not case-sensitive. For example, the following Embedded SQL statement fragments are equivalent:

EXEC SQL CONNECT TO
exec sql connect to
Exec Sql Connect To

However, the names that are specified for Embedded SQL names such as cursors, prepared statements, and connections are case-sensitive. The same case must be used to declare and use these names. For example, the following fragments declare two different cursors:

DECLARE CUR_NAME CURSOR
DECLARE cur_name CURSOR

The sort order of the Microsoft SQL Server you are connecting to determines whether other words are case-sensitive. Note that hyphens (-) are not permitted in Transact-SQL identifiers such as table and column names.

Note

The Transact-SQL keyword null should not be in uppercase in Embedded SQL programs to avoid conflict with the C keyword NULL. Also, the Embedded SQL keyword delete and the Transact-SQL keyword in should not be in uppercase in applications for 32-bit Windows to avoid conflict with 32-bit Windows–defined constants in WINDOWS.H.


Statements

This chapter describes the Embedded SQL statements that are listed below. An asterisk (*) identifies statements that have names that are identical to names of Transact-SQL statements. For statements with asterisks, the syntax that is included in this chapter augments the standard Transact-SQL syntax.

BEGIN DECLARE SECTION | PREPARE
CLOSE* | SELECT INTO*
CONNECT TO | SET ANSI_DEFAULTS
DECLARE CURSOR* | SET CONCURRENCY
DELETE (POSITIONED)* | SET CONNECTION
DELETE (SEARCHED)* | SET CURSOR_CLOSE_ON_COMMIT
DESCRIBE | SET CURSORTYPE
DISCONNECT | SET FETCHBUFFER
END DECLARE SECTION | SET OPTION
EXECUTE* | SET SCROLLOPTION
EXECUTE IMMEDIATE | UPDATE (POSITIONED)*
FETCH* | UPDATE (SEARCHED)*
GET CONNECTION | WHENEVER
OPEN* |

For more information about standard Transact-SQL statements and syntax, see the Microsoft SQL Server Transact-SQL Reference and the Microsoft SQL Server Database Developer's Companion.


Chapter 4: Building Applications

  • To build an Embedded SQL application for the Windows NT or the Windows 95 operating systems, you must compile the application on a computer that is running Windows NT or Windows 95.
  • To build an Embedded SQL application for the 16-bit Windows operating system, you must compile the application on a computer that is running MS-DOS or 16-bit Windows.
  • To build an Embedded SQL application for the MS-DOS operating system, you must compile the application on a computer that is running MS-DOS version 6.22 or later.

The following illustration shows how application code that contains Embedded SQL statements is compiled, precompiled, and linked, and how the application operates during run time.

When you compile an Embedded SQL application, the general process of creating an executable program is the same regardless of which operating system you are compiling for. The specific procedures for precompiling, compiling, and linking Embedded SQL applications for Windows NT, Windows 95, 16-bit Windows, and MS-DOS are described later in this chapter.


Steps for Building an Application

The following steps apply to building all Embedded SQL applications:

1. Run the appropriate sqlprep precompiler to prepare your Embedded SQL program for compiling with a C compiler.

The sqlprep precompiler finds SQL statements, parses the SQL statements, and if applicable, creates an access plan or bind file. The precompiler creates a C program that can be compiled with an appropriate C compiler. For more information about sqlprep, see "Running the sqlprep Precompiler" later in this chapter.

If your program contains static SQL statements, depending on the options used, sqlprep creates an access plan, or a bind file, or neither. Access plans and bind files consist of a set of definitions for SQL Server stored procedures.

2. Compile the C program created by the precompiler by using an appropriate C compiler. The compiler creates object file(s).

3. Link the object file(s), the Embedded SQL library files, and any other needed library files to create an executable file for the desired environment.


Embedded SQL Applications at Run Time

When you run an Embedded SQL application, SQL statements are executed as follows:

1. For every SQL statement, the application calls the appropriate Embedded SQL run-time services.

2. If the SQL statement is static, the run-time services execute the previously compiled stored procedure with the appropriate input parameters. If the statement is dynamic, the run-time services issue the SQL statements directly.

3. The run-time services use DB-Library calls to send and retrieve data to and from SQL Server.

4. The run-time services insert data into C program host variables (or SQLDA data structures). Status and error information is inserted into the SQLCA data structure.


Processing Embedded SQL Statements

Embedded SQL fully supports all Transact-SQL extensions, including stored procedures, local variables, and control-of-flow language. Due to syntax conflicts with Embedded SQL reserved keywords and because static Embedded SQL statements are compiled into stored procedures by the precompiler, minor restrictions apply to how Transact-SQL extensions are implemented. The restrictions are as follows:

  • Abbreviate the Transact-SQL EXECUTE statement to EXEC to avoid conflict with the Embedded SQL EXECUTE statement.
  • Avoid using Transact-SQL statement labels in static SQL statements because they conflict with the syntax for host variables. However, you can use Transact-SQL labels in dynamic SQL statements.

Note

If Microsoft SQL Server returns an error during compilation, the SQL Server error code appears in the precompiler error message as a negative number. For example:

SQL Syntax Error -SQLCODE = -207

For a list of messages that are returned by Embedded SQL, see Appendix E, "Messages."

  • Because sqlprep converts all static SQL statements into stored procedures, all limitations for Transact-SQL stored procedures apply to static SQL statements. A static SQL statement that contains a single transaction-management statement (such as COMMIT or SAVEPOINT) is not compiled into a stored procedure. Instead, the statement is issued dynamically at run time.

For information about restrictions that relate to other Embedded SQL statements, see Chapter 3, "Embedded SQL Statements."


Access Plans and Bind Files

The static SQL statements of your application can be issued at run time (as are dynamic SQL statements), or they can be placed into an access plan. An access plan is a set of stored procedures. It includes a separate stored procedure for each static SQL statement.

Using the sqlprep precompiler, you can connect to a specified server and database and create an access plan. Each time an application is recompiled, old stored procedures with the same program module name are dropped.

If the database you need to connect to is unavailable when you are ready to compile, you can use the sqlprep precompiler to create a bind file. A bind file is a Transact-SQL script that is used to create stored procedures for the access plan. You can later use the SQL Server isql utility to apply the bind file to the database before you run your application.


Running the sqlprep Precompiler

You must run the appropriate sqlprep precompiler to prepare your Embedded SQL program for compiling with a C compiler. The following table lists operating systems and their precompilers.

Operating system | Precompiler
Windows NT | NSQLPREP.EXE
Windows 95 | NSQLPREP.EXE
16-bit Windows | SQLPREP.EXE
MS-DOS | SQLPREP.EXE

The sqlprep precompiler finds SQL statements, parses the SQL statements, and if applicable, creates an access plan or bind file. The precompiler creates a C program that can be compiled with an appropriate C compiler. For more information about compiling and linking, see "Compiling and Linking Embedded SQL Applications," later in this chapter.


Setup

Before you run the sqlprep precompiler, do the following:

  • Set the COMPILER environment variable to the full path and filename of your C compiler. For example:

    SET COMPILER=C:\MSVC\BIN\CL.EXE
  • Set the INCLUDE environment variable to include the full path where the SQLCA.H and SQLDA.H Embedded SQL header files are located. For example:

    SET INCLUDE=C:\MSVC\INCLUDE;C:\SQL\ESQL\INCLUDE

    The sqlprep precompiler automatically includes these header files in the C programs that it creates; do not explicitly include (by using #include) them in your Embedded SQL program.

  • To enable communication with SQL Server, ensure that an appropriate Net-Library is loaded or available on the path when precompiling with the /DB and /PASS options. For example, the named pipes Net-Library for the Windows NT operating system is DBNMPNTW.DLL, and the named pipes Net-Library for the MS-DOS operating system is the DBNMPIPE.EXE TSR.


Precompiler Syntax

You can use either a slash (/) or a hyphen (-) to designate a sqlprep precompiler option. For example, /DB server_name.database_name and -DB server_name.database_name are equivalent.

The sqlprep syntax is as follows:

sqlprep program_filename [/SQLACCESS | /NOSQLACCESS]
[/FLAGGER {ENTRY | NONE}] [/DB [server_name.]database_name
/PASS login[.password]] [/BIND filename] [/MSG filename] [/NOLOGO] [/PLAN name] [/NOLINES] [/user_defined_option]

where

program_filename

Is the filename (without the extension) of the Embedded SQL program for precompiling. The precompiler searches for the file name and the file extension .SQC. For example, if you run sqlprep myprogrm, sqlprep searches for MYPROGRM.SQC and precompiles it if it is found.

/SQLACCESS

Specifies that sqlprep will automatically create stored procedures for static embedded SQL sections of the program. You must also include /DB and /PASS to specify a direct connection to SQL Server for creating the stored procedures, or /BIND to create a bind file for later loading of the stored procedures. Note that for standard cursors, the original SELECT statement is used directly, and the stored procedures that were created by the /SQLACCESS option are not used.

/NOSQLACCESS

Specifies that sqlprep will not automatically create stored procedures for static embedded SQL sections of the program. If you also include /DB and /PASS to specify a direct connection to SQL Server, sqlprep responds as follows:

  • Issues a message
  • Connects to SQL Server
  • Drops stored procedures that were created from a previous precompile of the same program
  • Completes the precompile without creating new stored procedures

/FLAGGER

Specifies sending static SQL statements to the server at compile time for syntax checking. Syntax or compilation error messages that are generated from the server are displayed on the screen. If you specify the /FLAGGER option, you must also specify the /DB and /PASS options. You cannot use the /PLAN or /SQLACCESS options with the /FLAGGER option.

ENTRY

Specifies that the static SQL sections are also checked for FIPS 127-2 SQL compliance. If the precompiler encounters SQL statements that do not conform to the specified FIPS level of support, an error message is displayed on the screen or stored in the file specified by the /MSG option. Program output is generated the same as when you use the /NOSQLACCESS option (that is, stored procedures are not created).

NONE

Specifies that the static SQL sections are not checked for FIPS 127-2 SQL compliance but are checked for correct syntax. The existence of database objects that the sections refer to is also verified.

/DB

Specifies a database, and optionally a SQL Server, in which to put stored procedures for the access plan. If SQL Server is running on your local computer, you need to supply only the database_name. Whenever you use the /DB option, you must also use the /PASS option. For more information about using /DB see "Access Plan and Bind File Options," later in this chapter.

[server_name.]database_name

Is the same server name and/or database name that is in the Embedded SQL CONNECT TO statement in the program.

/PASS

Specifies $INTEGRATED support for the login[.password] parameter, so that the precompiler can use integrated security regardless of the server's security mode. The compile-time connection is force integrated. Any implicit run-time connection is also force integrated. Whenever you use the /PASS option, you must also use the /DB option. For more information about using /PASS, see "Access Plan and Bind File Options," later in this chapter.

login[.password]

Is a user's login ID and password, or a user's login ID only.

/BIND

Specifies that the extension .BND be automatically appended to the filename. The bind file is an isql script that can be used to create the desired stored procedures (it includes CREATE PROCEDURE statements). For more information about using /BIND, see "Access Plan and Bind File Options," later in this chapter.

When you create a bind file, you must use the Embedded SQL CONNECT TO statement to connect to the server. If you use this option and no stored procedure is created, the bind file is deleted.

filename

Is the name of a bind file (without an extension) to create in place of access plans.

/MSG

Specifies that the extension .MSG be automatically appended to the filename. If the file has a length of 0 after precompile, it is deleted.

filename

Is the name of a file (without an extension) to create to store error messages that are sent by the sqlprep precompiler.

/NOLOGO

Specifies suppression of the sqlprep banner and the compiler banner (sqlprep invokes the compiler).

/PLAN name

Specifies a non-default name for an access plan. (The default name is the program_filename without an SQL extension.)

/NOLINES

Specifies that the generated .C file be displayed for debugging instead of the .SQC file. You must use the /NOLINES option if the .SQC source code contains C language code that appears on the same line after an EXEC SQL statement.

/user_defined_option

Is a user-defined option to be passed to the C compiler.


Access Plan and Bind File Options

By using the /DB and /PASS options with the sqlprep precompiler, you can connect to a specified server and database and create an access plan (set of stored procedures) for each separately compiled program module (compilation unit). With the /DB and /PASS options, sqlprep makes a connection using the specified server name, database name, login ID, and password. The access plan consists of a separate stored procedure for each static SQL statement in each compiled program module.

By default, the stored procedure names consist of the following:

  • Name of the program module
  • A date/timestamp that is converted to eight printable ASCII characters
  • A dollar sign ($)
  • The access plan section number

The date/timestamp provides for re-creating stored procedures for the access plan with identical program module names each time the program is compiled. However, if you use the /PLAN option and specify a non-default plan name that ends with an underscore (_), sqlprep does not include the date/timestamp in the stored procedure names. You can, therefore, reuse the stored procedures that were created from a previous precompile.

If the database you need to connect to is unavailable, or you do not want to use it, when you are ready to compile, you can use the /BIND option in the sqlprep precompiler command line to create a bind file. The bind file is a Transact-SQL script that is used to create stored procedures for access plans. You can later use the SQL Server isql utility to apply the bind file to the database. However, to run a bind file as a SQL script, each line must contain no more than 1,000 characters if you want to use the isql utility. If one or more lines contain more than 1,000 characters, use the isqlw utility. You must apply the bind file to the database before you can run the corresponding C application.

If you do not specify the /DB and /PASS options or the /BIND option, static SQL statements are issued at run time, as are dynamic SQL statements.


Compiling and Linking Embedded SQL Applications

The sqlprep precompiler creates a C program with the file extension .C from your Embedded SQL programs. For example, from the Embedded SQL program MYPROGRM.SQC, sqlprep creates a C program named MYPROGRM.C. You can compile the C program for a specific operating system or platform by using the appropriate C compiler.

The Embedded SQL for C precompiler does not support preprocessing of C++ modules. To use Embedded SQL in a C++ application, you must create separate C-language modules for your data access functions and preprocess only those modules. ESQL/C also does not support the use of precompiled headers, since the first step in preprocessing is to expand all include files. You can improve compilation time by segregating data access code into .SQC modules that have a minimum number of #include files.

If your program contains an #include windows.h statement, you must precede it with the following two statements:

#define _OLE2_H_
#define NOIME

In this example, the OLE2.H and IMM.H header files are excluded. The OLE2.H and IMM.H header files are not compatible with version 6.5 of Embedded SQL.

The Transact-SQL keyword null should not be in uppercase in Embedded SQL programs to avoid conflict with the C keyword NULL. Also, the Embedded SQL keyword delete and the Transact-SQL keyword in should not be in uppercase in applications for 32-bit Windows to avoid conflict with 32-bit Windows–defined constants in WINDOWS.H.

For more information about how to compile and link precompiled Embedded SQL programs for each of the available platforms, see the following sections:

"Compiling and Linking for Windows NT and Windows 95"

"Compiling and Linking for 16-bit Windows"

"Compiling and Linking for MS-DOS"

"Compiling and Linking for QuickWin"


Compiling and Linking for Windows NT and Windows 95

You can compile and link precompiled Embedded SQL programs for Windows NT and Windows 95 on a computer that is running the Windows NT or Windows 95 operating system by using the Visual C++ development system (32-bit) version 2.0.

Before you compile an Embedded SQL program for Windows NT or Windows 95, set the LIB environment variable to include the full path where the following library files are located:

  • CAW32.LIB (supplied with Embedded SQL)
  • SQLAKW32.LIB (supplied with Embedded SQL)
  • KERNEL32.LIB (supplied with 32-bit Visual C++)
  • USER32.LIB (supplied with 32-bit Visual C++)
  • GDI32.LIB (supplied with 32-bit Visual C++)
  • LIBCMT.LIB (supplied with 32-bit Visual C++)

For example:

SET LIB=C:\MSDEV\LIB;C:\MSSQL\ESQL\LIB

To compile and link a program for Windows NT and Windows 95

1. Run the compiler CL.EXE as you would for Windows NT or Windows 95 to create an object file. For example:

CL /c /W3 /D"_X86_" MYPROGRM.C

In this example, the compiler creates the object file MYPROGRM.OBJ. The /D"_X86_" compiler option defines the symbol that is necessary for Windows NT and Windows 95 operating systems that run on Intel®-based computers. You can use any additional compiler options that are allowed by the compiler.

2. Run the linker (LINK.EXE) as you would for Windows NT or Windows 95 to link the compiled Embedded SQL object file and system libraries, which creates an executable file for Windows NTor Windows 95. When linking files for Windows NT –based or Windows 95–based programs, you must explicitly link with the SQLAKW32.LIB and CAW32.LIB libraries. For example:

LINK /NOD /subsystem:windows MYPROGRM.OBJ MYPROGRM.RES KERNEL32.LIB GDI32.LIB USER32.LIB LIBCMT.LIB SQLAKW32.LIB CAW32.LIB

In the example, the compiled object file MYPROGRM.OBJ, the system libraries, and the Embedded SQL libraries SQLAKW32.LIB and CAW32.LIB are linked together to create the executable file MYPROGRM.EXE for Windows NT or Windows 95.

To run a Windows NT–based or Windows 95–based Embedded SQL application, the dynamic-link libraries SQLAKW32.DLL, NTWDBLIB.DLL, and DBNMPNTW.DLL (or other appropriate Net-Library) must be available in your path.


Compiling and Linking for 16-bit Windows

You can compile and link precompiled Embedded SQL programs for 16-bit Windows on a computer that is running the MS-DOS or 16-bit Windows operating system by using the Visual C++ development system (16-bit) version 1.52 or later.

Before you compile an Embedded SQL program for 16-bit Windows, set the LIB environment variable to include the full path where the following library files are located:

  • CAW.LIB (supplied with Embedded SQL)
  • SQLAKW.LIB (supplied with Embedded SQL)
  • LIBW.LIB (supplied with 16-bit Visual C++)
  • MLIBCEW.LIB or LLIBCEW.LIB (supplied with 16-bit Visual C++)

For example:

SET LIB=C:\MSVC\LIB;C:\MSSQL\ESQL\LIB

To compile and link a program for 16-bit Windows

1. Run the compiler CL.EXE as you would for 16-bit Windows to create an object file. For example:

CL /c /W3 /AM MYPROGRM.C

In the example, the compiler creates the object file MYPROGRM.OBJ. The /AM compiler option sets the memory model of the program to medium. You can use any additional compiler options that are allowed by the compiler.

2. Run the linker LINK.EXE as you would for 16-bit Windows to link the compiled Embedded SQL object file and system libraries, which to creates an executable file for Windows. When linking files for Windows-based programs, you must explicitly link with the SQLAKW.LIB and CAW.LIB libraries. For example:

LINK /NOD MYPROGRM.OBJ,,, LIBW.LIB MLIBCEW.LIB SQLAKW.LIB CAW.LIB, MYPROGRM.DEF;

In the example, the compiled object file MYPROGRM.OBJ, the system libraries LIBW.LIB and MLIBCEW.LIB, and the Embedded SQL libraries SQLAKW.LIB and CAW.LIB are linked together to create the executable file MYPROGRM.EXE for Windows.

To run a 16-bit Windows–based Embedded SQL application, the dynamic-link libraries SQLAKW.DLL, MSDBLIB3.DLL, and DBNMP3.DLL (or other appropriate Net-Library) must be available in your path.


Compiling and Linking for MS-DOS

You can compile and link precompiled Embedded SQL programs for MS-DOS on a computer that is running the MS-DOS version 6.22 or later operating system by using the Visual C++ development system (16-bit) version 1.52 or later.

Before you compile an Embedded SQL program for MS-DOS, set the LIB environment variable to include the full path where the following library files are located:

  • CAR.LIB (supplied with Embedded SQL)
  • RLDBLIB.LIB (supplied with Embedded SQL)
  • SQLAKD.LIB (supplied with Embedded SQL)
  • LLIBCE.LIB (supplied with 16-bit Visual C++)

For example:

SET LIB=C:\MSVCNT\LIB;C:\MSSQL\ESQL\LIB

To compile and link a program for MS-DOS

1. Run the compiler CL.EXE as you would for MS-DOS to create an object file. For example:

CL /c /W3 /AL MYPROGRM.C

In the example, the compiler creates the object file MYPROGRM.OBJ. The /AL compiler option sets the memory model of the program to large. You can use any additional compiler options that are allowed by the compiler.

2. Run the linker LINK.EXE as you would for MS-DOS to link the compiled Embedded SQL object file and system libraries which creates an executable file for MS-DOS. When linking files for MS-DOS–based programs, you must explicitly link with the SQLAKD.LIB, RLDBLIB.LIB, OLDNAMES.LIB and CAR.LIB libraries. For example:

LINK /NOD MYPROGRM.OBJ,,, LLIBCE.LIB SQLAKD.LIB CAR.LIB OLDNAMES.LIB RLDBLIB.LIB, MYPROGRM.DEF;

In the example, the compiled object file MYPROGRM.OBJ, the system library LLIBCE.LIB, the Embedded SQL libraries SQLAKD.LIB, CAR.LIB, and OLDNAMES.LIB and the DB-Library library RLDBLIB.LIB are linked together to create the executable file MYPROGRM.EXE for MS-DOS.

To run an MS-DOS–based Embedded SQL application, load the appropriate Net-Library TSR.


Compiling and Linking for QuickWin

QuickWin is a set of libraries that helps convert source code for MS-DOS into 16-bit Windows–based applications. You can compile and link precompiled Embedded SQL programs for QuickWin on a computer that is running the MS-DOS or 16-bit Windows operating system by using the Visual C++ development system (16-bit) version 1.52.

Before you compile an Embedded SQL program for QuickWin, set the LIB environment variable to include the full path where the following library files are located:

  • CAW.LIB (supplied with Embedded SQL)
  • SQLAKW.LIB (supplied with Embedded SQL)
  • LIBW.LIB (supplied with 16-bit Visual C++)
  • MLIBCEWQ.LIB or LLIBCEWQ.LIB (supplied with 16-bit Visual C++)

For example:

SET LIB=C:\MSVC\LIB;C:\MSSQL\ESQL\LIB

To compile and link a program for QuickWin

1. Run the compiler CL.EXE as you would for 16-bit Windows to create an object file. For example:

CL /c /W3 /AM MYPROGRM.C

In the example, the compiler creates the object file MYPROGRM.OBJ. The /AM compiler option sets the memory model of the program to medium. You can use any additional compiler options that are allowed by the compiler.

2. Run the linker LINK.EXE as you would for 16-bit Windows to link the compiled Embedded SQL object file and system libraries which creates an executable file for Windows. When linking files for Windows-based programs, you must explicitly link with the SQLAKW.LIB and CAW.LIB libraries. For example:

LINK /NOD MYPROGRM.OBJ,,, LIBW.LIB MLIBCEWQ.LIB SQLAKW.LIB CAW.LIB, MYPROGRM.DEF;

In the example, the compiled object file MYPROGRM.OBJ, the system libraries LIBW.LIB and MLIBCEWQ.LIB, and the Embedded SQL libraries SQLAKW.LIB and CAW.LIB are linked together to create the executable file MYPROGRM.EXE for Windows.

To run a 16-bit Windows–based Embedded SQL application, the dynamic-link libraries SQLAKW.DLL, MSDBLIB3.DLL, and DBNMP3.DLL (or other appropriate Net-Library) must be available in your path.


Debugging Embedded SQL Programs

You can use the Microsoft debuggers (including the integrated debugger in 16-bit Visual C++ and 32-bit Visual C++), CodeView® window-oriented debugger for Windows, and WinDebug for Windows NT and Windows 95 to examine your Embedded SQL program while it is executing. A Microsoft debugger displays Embedded SQL source statements as lines of executable code. While debugging, you can set breakpoints on Embedded SQL statements to test host variables. To monitor SQLCA and SQLDA fields, you must monitor the SQLCA and SQLDA data structures and member variables. For example, you must monitor sqlca->sqlcode instead of the SQLCODE macro, and you must monitor sqlca->sqlerrd[0] instead of the SQLERRD1 macro.

When you use the integrated debugger in 16-bit Visual C++ or 32-bit Visual C++, you can set breakpoints on lines of source code in your .SQC file. However, to begin a debugging session, you must first open the .C file that is generated by sqlprep and make that window active. Then from the Debug menu, choose Go.

When you use CodeView for 16-bit Windows and step through the code, the cursor disappears until it reaches the next C statement.


Sample Programs

The Embedded SQL setup program installs the source codes for several sample programs in C:\MSSQL\ESQL\SAMPLES.C by default.

Before you build any of the sample programs, you can use the SETENV.BAT batch file to set the COMPILER, INCLUDE, and LIB environment variables. Edit the SETENV.BAT file to set the variables as appropriate for your system.

You can build any of the sample programs by using one of two batch files that are included with the samples: MAKEC.BAT and MAKEW.BAT. Use MAKEC.BAT to build a C application for the Windows NT console and QuickWin environments. Use MAKEW.BAT to build Win16 and Win32 applications for graphical environments such as Windows NT, Windows 95, 16-bit Windows, and MS-DOS. All of the samples share the single make file MAKEFILE, which contains the steps and options that are necessary to build an Embedded SQL application.

Both the MAKEC.BAT and MAKEW.BAT batch files set environment variables that are used by MAKEFILE to control the build process. Edit the batch files to set the environment variables as appropriate for your system. For example, both batch files contain the lines:

set SERVER=my_server
set LOGIN=my_login
set PASSWORD=my_password
set DATABASE=my_database

You must set these environment variables as appropriate for your SQL Server. For example:

set SERVER=gizmo
set LOGIN=sa
set PASSWORD=
set DATABASE=pubs

When started, each sample application accepts the following command-line arguments for specifying the SQL Server, the login ID, the password, and the database to use as follows. Each sample application will generate a prompt for unspecified options.

program [{/s | /S} [server_name]] [{/u | /U} login_id] [{/p | /P} [password]]
[{/d | /D} database]

where

program

Is the Embedded SQL sample program you are starting.

server_name

Is the name of the SQL Server to connect to. If server_name is omitted, the local SQL Server is connected to.

login_id

Is the login ID of the SQL Server that is connected to.

password

Is the password of the SQL Server that is connected to. If password is omitted, a NULL password is assumed.

database

Is the database to use.


GENCHAR (Generic for C)

GENCHAR is a generic Embedded SQL character-mode program that is written using plain C, standard C libraries, and Embedded SQL statements. GENCHAR connects to the specified SQL Server and issues a SELECT statement query from the authors table in the pubs database.

GENWIN uses the following files:

GCUTIL.C | GENCHAR.H
GCUTIL.H | QUICKWIN.DEF
GENCHAR.SQC |

You can build GENCHAR for the following environments by using the associated commands.

Environment | Command
Windows NT and Windows 95 | makec genchar nt
MS-DOS | makec genchar dos
QuickWin | makec genchar qwin


GENWIN (Generic for Windows NT, and Windows 95, and 16-bit Windows)

GENWIN is a generic Embedded SQL program for Windows NT, Windows 95, and 16-bit Windows that was written by using the Win16/Win32 API, C language, standard libraries for 16-bit Windows and C, and Embedded SQL statements. GENCHAR connects to the specified SQL Server and issues a SELECT statement query from the authors table in the pubs database.

GENWIN uses the following files:

GENWIN.DEF | GENWIN.SQC
GENWIN.H | GWUTIL.C
GENWIN.ICO | GWUTIL.H
GENWIN.RC |

You can build GENWIN for the following environments by using the associated commands.

Environment | Command
Windows NT and Windows 95 | makew genwin nt
16-bit Windows | makew genwin win


EDBLIB (Embedded SQL and DB-Library for Windows NT, Windows 95, and 16-bit Windows)

EDBLIB is an Embedded SQL and DB-Library program for Windows NT, Windows 95, and 16-bit Windows that was written by using the Win16/Win32 API, C language, standard libraries for 16-bit Windows and C, Embedded SQL statements, and DB-Library functions. EDBLIB connects to the specified SQL Server and issues a SELECT statement query from the authors table in the pubs database by using Embedded SQL and DB-Library. It uses the GET CONNECTION statement to obtain the DB-Library DBPROCESS connection pointer.

EDBLIB uses the following files:

EDBLIB.DEF | EDBLIB.SQC
EDBLIB.H | GWUTIL.C
EDBLIB.ICO | GWUTIL.H
EDBLIB.RC |

Before building this sample, change the following line in the MAKEW.BAT batch file from

set DBLIB=0

to

set DBLIB=1

This change tells MAKEFILE to link the EDBLIB program to DB-Library. Then you can build EDBLIB for the following environments by using the associated commands.

Environment | Command
Windows NT and Windows 95 | makew edblib nt
MS-DOS | makew edblib dos
16-bit Windows | makew edblib win


Examples

The examples, 1 through 8, describe Embedded SQL character-mode programs that are written using plain C, standard C libraries, and Embedded SQL statements. Each program connects to the specified SQL Server and executes a series of Embedded SQL statements.

Each program that is described in the following examples uses the appropriate EXAMPLEn.SQC, GCUTIL.C, GCUTIL.H, and QUICKWIN.DEF files. Some of these programs use the author2 table, which you can create by using the AUTHOR2.SQL file.

You can build an example program for the following environments by using the associated commands.

Environment | Command
Windows NT and Windows 95 | makec examplen nt, where n is the number of the example program
MS-DOS | makec examplen dos, where n is the number of the example program
QuickWin | makec examplen qwin, where n is the number of the example program

Example 1

  • Declares and opens a standard cursor by using a prepared SQL statement.
  • Fetches rows from the sysobjects table in the specified database.

Example 2

  • Declares and opens a browse cursor.
  • Fetches rows from the sales table in the pubs database into a C structure.

Example 3

  • Declares and opens a standard cursor.
  • Fetches rows from the author2 table in the pubs database.

    After each row is fetched, it:

    1. Begins a transaction.

    2. Performs a dummy update to lock that row.

    3. Allows you to change the author's last name in that row.

    4. Commits the transaction.

Example 4

  • Connects to two SQL Servers, and declares and opens two standard cursors by using a prepared SQL statement.
  • Fetches rows from the author2 table in the pubs database of each SQL Server.
  • Allows you to issue positioned updates of each author's last name as the rows are fetched.

Example 5

  • Issues a singleton select statement to retrieve a row from the titles table in the pubs database into a C structure or into the individual fields of a C structure.
  • Uses browse cursors with a SELECT statement and the sp_who stored procedure.
  • Issues static and dynamic INSERT statements.
  • Issues a SELECT statement of various date and time values.

Example 6

  • Issues multiple static SQL statements within a transaction in a Transact-SQL batch.
  • Executes the sp_addtype and sp_droptype stored procedures.
  • Uses a browse cursor with the sp_who stored procedure.
  • Issues a SELECT statement into a temporary table and retrieves a row from that temporary table by using the EXECUTE IMMEDIATE statement.
  • Executes a static Transact-SQL batch that uses control-of-flow language.

Example 7

  • Opens a standard cursor by using HOLDLOCK within a transaction; inserts the cursor.
  • Updates and deletes rows from the authors table in the pubs database.
  • Inserts rows into the authors table within a transaction and then rolls back that transaction.
  • Demonstrates that triggers are enforced by attempting to delete a row from the titles table that fires the deltitle trigger.
  • Uses a browse cursor with the sp_who stored procedure.
  • Executes the sp_addtype and sp_droptype stored procedures.

Example 8

  • Demonstrates behavior that is similar to that of isql.


Appendix A: Advanced Programming

Most of the features that are used in writing Embedded SQL code are discussed in Chapter 2, "Embedded SQL Programming." Appendix A provides supplemental information for developing Embedded SQL applications and discusses:

  • Datatype mappings from Transact-SQL to C, and vice versa
  • The SQLDA data structure for data input and output
  • The contents of the SQLCA data structure
  • The EXEC statement and how to use it to selectively bypass the creation of an access plan


Datatype Mapping

Embedded SQL maps C datatypes to SQL Server datatypes, and vice versa. Conversions are supported for all datatypes except datetime or smalldatetime, money or smallmoney, and decimal or numeric in some instances. The following table lists C datatypes and the SQL Server datatypes they map to. Datatypes that can be converted to datetime or smalldatetime, money or smallmoney, and decimal or numeric are listed with a corresponding T in the respective columns of the following table.

C datatype | Assigned SQL Server datatype | datetime or smalldatetime | money or smallmoney | decimal or numeric
short | smallint | F | F | F
int | smallint | F | F | F
long | int | F | F | F
float | real | F | F | F
double | float | F | F | F
char | varchar[x]1 | T | T | T
void *p | binary2 | T | T | T
char byte | tinyint | F | F | F
1 For more information, see "Mapping Character Datatypes," later in this chapter. 2 For more information, see "Pointers as Host Variables," later in this chapter.

Output data is truncated if the receiving datatype is too short. This causes an exception (warning), and the SQLCODE field of the SQLCA data structure is set to +1. Input data can be truncated if the receiving SQL Server column is too short. However, in this case, no exception is generated.

Because text datatypes are not allowed in stored procedures, you cannot use C fields that are more than 255 bytes long in static SQL statements that are compiled into access plans.


Mapping Character Datatypes

You can declare character datatypes as host variables with a fixed maximum length by explicitly supplying the length of the character array or by declaring the host variable with an initial value. For example:

EXEC SQL BEGIN DECLARE SECTION;
char var1[18];
char var2[] = "Initialized string";
EXEC SQL END DECLARE SECTION;

In the example, two host variables are declared, each with a maximum length of 18 bytes.

Host variables that are declared as single-byte characters with no explicit length or initial values (for example, char var3) are treated as 1-byte integer datatypes.

The following table shows how C character datatypes are mapped to or from the SQL Server char, varchar, or text datatype.

Mapped datatypes | When converted
From C character to SQL Server char, varchar, or text | Data is copied and truncated or padded with blanks if the SQL Server table that is receiving the columns is set to a fixed length.
To C character from SQL Server char, varchar, or text | Data is copied and, if necessary, truncated to the length of the receiving field and is terminated with a NULL. If data is truncated, the SQLWARN1 field of the SQLCA data structure is set.


Pointers as Host Variables

A C host variable that is declared as a pointer to a void (void *p) is treated as a data buffer of unknown length. Your program must verify that enough memory is allocated to hold any output data that is received.


Mapping Date or Time Data

Because C does not have a date or time datatype, SQL Server date or time columns are converted to characters by using the SQL Server default date format. For example:

mm dd yyyy hh:mm:ss[am | pm]

You can send dates to SQL Server from C character fields by using any of the character date formats that are acceptable to SQL Server. For a date without a time, use an 11-byte receiving field. If the data is truncated, note that the SQLWARN3 field of the SQLCA data structure is set. For other formats, use the Transact-SQL CONVERT statement. For example, to convert a time without a date, use a statement similar to this:

SELECT CONVERT(char, date, 8) FROM sales

When you attempt to put a fixed-length datatype into a buffer that is too small for it, an error occurs and no data is copied.


Mapping Binary Data

On singleton selects and cursor fetches, SQL Server binary, varbinary, and image columns are mapped to C host variable declared as character arrays without any data conversion. The host variable is treated as a byte array. In order to retrieve the hexadecimal character representation of a binary, varbinary, or image column, use the Transact SQL CONVERT function on the column.

When input, a C character array datatype can be mapped to a SQL Server binary, varbinary, or image column by using dynamic SQL statements. To do this, use two question marks (??) as parameter markers. The data format on input is the raw binary data (not its hexadecimal character representation.) If you include at least one space between the parameter marker and its indicator, you can use indicator variables. You cannot use static SQL statements to map binary data on input parameters.


Preparing SQLDA for Data Input and Output

The SQLDA data structure contains descriptive information about each input parameter or output column. The structure contains the column name, the datatype, the length, and a pointer to the actual data buffer for each input or output parameter.

For output data that uses the SQLDA data structure, you can use the DESCRIBE statement (or the PREPARE statement with the INTO option) to enter the column name, the datatype, and other data into the appropriate fields of the SQLDA data structure.

Before using the SQLDA data structure in a PREPARE INTO or DESCRIBE statement, your application must set the size of the SQLDA data structure in bytes and the maximum number of entries. These numbers are reflected in the sqldabc and sqln fields, respectively.

Before performing a FETCH statement, the application must insert into the sqldata field the address of each program variable that will receive the data from the corresponding column. (The sqldata field is part of sqlvar within the SQLDA data structure.) If indicator variables are used, sqlind must also be set to the corresponding address. The datatype field (sqltype) and length field (sqllen) are filled with information in the SQL Server column from a PREPARE INTO or a DESCRIBE statement. The value in the datatype and length fields can be overridden by the application before a FETCH statement is executed. The sqltype code and the sqld address that are assigned must be one of the valid datatypes that are listed in the "Valid Values for sqltype" section, later in this chapter.

Before the DESCRIBE or PREPARE statement is issued, the value of sqln must be set higher than the anticipated number of output columns. If the number of columns is unknown, you can set sqln to 0 and then issue a DESCRIBE statement. No column detail information is moved into the SQLDA data structure, but the number of columns in the results set is inserted into sqld.

To use the SQLDA data structure for input data, your application must supply data for the fields of the entire SQLDA data structure, including the sqln, sqld, sqldabc, sqltype, sqllen, and sqldata fields for each variable. If the sqltype field has an odd code number (value), the address of the indicator variable must also be supplied.


SQLDA Data Structure

The SQLDA data structure definition (from SQLDA.H) looks like this:

// SQL Descriptor Area - SQLDA

struct sqlda

{

unsigned char sqldaid[8];

// Eye catcher = 'SQLDA  '

long sqldabc;

// SQLDA size in bytes = 16+44*SQLN

short sqln;

// Number of SQLVAR elements

short sqld;

// Num of used SQLVAR elements

struct sqlvar

{

   short sqltype;

// Variable datatype

   short sqllen;

// Variable data length

   unsigned char FAR

      *sqldata;

// Pointer to variable data value

   short FAR *sqlind;

// Pointer to null indicator

   struct sqlname

// Variable name

   {

      short length;

// Name length [1..30]

      unsigned char

         data[30];

// Variable or column name

   } sqlname;

} sqlvar[1];

};

SQLDA
Fields and datatypes for the SQLDA data structure are described in the following table.

Field and datatype | Contains
sqldaid |
unsigned char* | The text string SQLDA. This field is not used for FETCH, OPEN, or EXECUTE statements.
sqldabc |
long | The length of the SQLDA data structure (sqln* 44 + 16).
sqln |
short | The total number of sqlvar entries that are allocated; equal to the number of input parameters or output columns.
sqld |
short | The number of sqlvar entries that are used.
sqlvar |
struct | The values that are listed in the sqlvar table; the values can occur several times, listed once per column in the results set or input parameter.
sqltype |
short | The number that represents the datatype of columns or host variables, and that indicates whether null values are allowed. For valid values, see "Valid Values for sqltype," later in this chapter.
sqllen |
short | The external length of a value from a column.
sqldata |
unsigned char far* | The address of the host variable (which must be inserted by the application) for FETCH, OPEN, and EXECUTE statements. For DESCRIBE and PREPARE statements, sqldata is not used.
sqlind |
short far* | The address of an indicator variable for FETCH, OPEN, and EXECUTE statements, if one exists.
 | If the column does not permit a null value, the field is undefined. If the column permits a null value, sqlind is set to –1 if the data value is null or to 0 if the data value is not null. For DESCRIBE and PREPARE statements, sqlind is not used.
sqlname |
struct | The name and length of the column. (Is not used for FETCH, OPEN, and EXECUTE statements.)
length |
short | The length of the name column.
name |
unsigned char* | The name of the column. For a derived column, this field contains the ASCII numeric literal value that represents the derived column's original position within the select list.

Valid Values for sqltype
The following table lists the values for the sqltype field in SQLDA data structure and corresponding SQL Server datatypes for which they can serve as host variables in a FETCH statement or and EXECUTE statement. For each pair of sqltype codes, the odd number type signifies a host variable with a corresponding null indicator variable needed for setting or retrieving null values.

Sqltype code | Datatype description | Corresponding SQL Server datatype | Sample declaration
392/393 | 26-byte date & time char format corresponds to the formats supported by dbconvert for datetime to/from char (See "Programming DB-Library for C") | datetime, smalldatetime | char date1[27] = "Mar 7 1988 7:12PM";
444/445 | Binary | binary, varbinary, image, timestamp Note: sqltype 444/445 is automatically used for these SQL Server column types on output. | char binary1[4097];
452/453 | Char string <=254 bytes; not automatically null terminated Note: Make sure you initialize the full array with nulls when using this type for output. | char, varchar, or text | char mychar[255];
456/457 | Length-prefixed long character field; not automatically null terminated | char, varchar, or text | struct TEXTVAR { short len; char data[4097]; } textvar;
462/463 | Null-terminated string Note: Declarations of known length (mychar1) will be padded with blanks and a terminating null. Declarations of char pointers (mychar2) will not be blank padded and the application program must ensure sufficient space is allocated. | char, varchar, or text. | char mychar1[41]; char * mychar2;
480/481 | 8-byte floating point | float, real, int, smallint, tinyint, decimal, numeric, money, smallmoney | double mydouble1;
482/483 | 4-byte floating point | float, real, int, smallint, tinyint, decimal, numeric, money, smallmoney | float myfloat1;
496/497 | 4-byte integer | int, smallint, tinyint, bit | long myint1;
500/501 | 2-byte integer | smallint, tinyint, bit | short myshort1;

When using SQLDAs in a DESCRIBE or PREPARE INTO, the following datatype codes are returned.

SQL Server column | sqltype returned by DESCRIBE or PREPARE INTO | Comments
char, varchar | 452/453 | 452/453 is a Cobol char datatype, not null terminated. Easier to use 462/463 from C.
text | 456/457 | sqllen set to max of 32767 for text
binary, varbinary, image, timestamp | 444/445 | sqllen set to max of 32767 for image
smallint, tinyint, bit | 500/501 |
int | 496/497 |
float | 480/481 |
real | 482/483 |
datetime, smalldatetime | 392/393 |
decimal, numeric, money, smallmoney | 484/485 | Cobol decimal format; not supported for FETCH or EXECUTE in ESQL/C (use 480 or 482 instead). sqllen encoded with scale and precision; use sqllen &= 0xFF to get just precision.

DESCRIBE and PREPARE INTO are only supported for output columns of SELECT statements. They are not supported for INSERT, UPDATE, or DELETE statements, or for any statement requiring an input host variable.


SQLCA Data Structure

The SQLCA data structure contains status information about the Embedded SQL statement that was last executed. The structure definition for SQLCA (from SQLCA.H) looks like this:

// SQL Communication Area - SQLCA

typedef struct sqlca

{

unsigned char

sqlcaid[EYECATCH_LEN];

// Eyecatcher = 'SQLCA   '

long sqlcabc;

// SQLCA size in bytes = 136

long sqlcode;

// SQL return code

short sqlerrml;

// Length for SQLERRMC

unsigned char

  sqlerrmc[SQLERRMC_SIZ];

// Error message tokens

unsigned char sqlerrp[8];

// Diagnostic information

long sqlerrd[6];

// Diagnostic information

unsigned char sqlwarn[8];

// Warning flags

unsigned char sqlext[3];

// Reserved

unsigned char sqlstate[5];

// new member

} SQLCA;

SQLCA
Fields and datatypes for the SQLCA data structure are described in the following table.

Field and C datatype | Contains
sqlcaid |
unsigned char | The text string SQLCA.
sqlabc |
long | The length of the SQLCA data structure.
sqlcode |
long | The status code for the last-run SQL statement:
 | 0 The statement ran without error.
 | 1 The statement ran, but an exception was generated.
 | 100 A FETCH statement was issued, but no more rows satisfy the SELECT statement criteria that was used to define the cursor; no rows were processed.
 | < 0 (negative) The statement did not run due to an application, database, system, or network error.
sqlerrm | Error messages that consist of two parts.
sqlerrml | The length of the error message in sqlerrmc (0 to 70).
short |
sqlerrmc | The text of the error message. Error messages that are longer than 70 bytes are truncated.
unsigned char |
sqlerrp |
unsigned char | Reserved (diagnostic information).
sqlerrd |
long | An array of six integer status codes (the codes that are not listed in the fields below are reserved).
sqlerrd[1] | SQL Server error number.
sqlerrd[2] | SQL Server severity level.
sqlerrd[3] | The number of rows affected.
sqlwarn | Eight warning flags, each containing a blank or W (those not listed in the fields below are reserved):
sqlwarn[0] |
unsigned char | A summary of all warning fields. Blank indicates no warnings.
sqlwarn[1] |
unsigned char | W indicates that a character string was truncated during output binding.
sqlwarn[2] |
unsigned char | Not used.
sqlwarn[3] |
unsigned char | W indicates that the number of columns does not match the number of host variables.
sqlext |
unsigned char | Reserved.
sqlstate |
unsigned char | SQLSTATE runtime error codes.

Warning

If the number of host variables and parameter markers does not match, SQLWARN3 is set to W. This condition is considered an exception (SQLCODE is set to +1). Exceeding the number of host variables or SQLDA data structure entries is fatal (SQLCODE = –19313). During a FETCH statement or a singleton select statement, SQLWARN3 is set if the number of columns is not equal to the number of host variables (or SQLDA data structure entries). The lower of the two is the number of items that are actually processed. For more information about SQLCA, see "Using the SQLCA Data Structure," in Chapter 2, "Embedded SQL Programming."


Selectively Bypassing the Creation of Access Plans

To run a stored procedure by using a static SQL statement, include an EXECUTE statement before the stored procedure call to bypass the creation of an access plan. If you do not include an EXECUTE statement before the stored procedure call, a stored procedure is created to run the stored procedure that was called. Using EXECUTE eliminates this extra step. For example:

EXEC SQL EXEC sp-addlogin :loginame, :password;

You can also use the selective bypass method to issue an SQL statement dynamically from a static Embedded SQL statement without using the PREPARE and EXECUTE statements. For example:

EXEC SQL EXEC CREATE TABLE t1 (c1 int,...);

The current values of any host variables are substituted into the statement.


Appendix B: Standard DB-Library Cursors

Embedded SQL programs use the SET SCROLLOPTION, SET CONCURRENCY, SET FETCHBUFFER, and SET CURSOR_CLOSE_ON_COMMIT statements to control cursor options. All other standard cursor behavior is handled automatically by the Embedded SQL library.

Standard cursors use the current database connection. Because standard cursors do not use separate database connections as do browse cursors, many of the locking problems experienced with the use of browse cursors are avoided. Standard cursors also use the cursor functions of DB-Library and will automatically take advantage of any future performance enhancements made to DB-Library cursors.


Defining Cursors

Standard cursors can be used to scroll through and update a results set. Standard cursors do not require a unique index for SQL Server version 6.5; do not require a timestamp or a second connection to a database for updates, and do not create a copy of the entire results set. The following tables summarize these differences.

Standard cursors will continue to be enhanced in later versions; however, browse cursors will not.

Standard cursor | Description
Connections to the server | All share the same connection.
Timestamp column | Not required.
Transaction behavior | All cursors share the same transaction space. Standard cursors cannot block each other.
Unique index | Not required for SQL Server version 6.5.
Data currency | Data values are always current. Row membership depends on the SCROLLOPTION setting:
 | If keyset-driven, membership is fixed at cursor-open time.
 | If dynamic, membership changes.
Restrictions on use of SQL language | Cannot be used on stored procedures. Cannot contain UNION, GROUP BY, and HAVING clauses. In SQL Server version 6.5, dynamic cursors can contain an ORDER BY clause if the table has a unique index.

Browse cursor | Description
Connections to the server | One per cursor, plus one base connection for all updates.
Timestamp column | Required for positioned update or delete operations.
Transaction behavior | Read-only cursors can block updates that are made by the same program.
Unique index | Required for update only.
Data currency | For read-only cursors, all data is current.
 | For updatable cursors, the server takes a snapshot of the data and stores it in the tempdb database when the cursor is opened.
Restrictions on use of SQL language | Updatable cursors require the FOR BROWSE clause.


Sensitivity to Change

Standard cursors fall into one of two categories, depending on their sensitivity to change: keyset-driven or dynamic.

With Keyset-driven standard cursors, values can change, but order and membership in the results set remain fixed at cursor open time.

With Dynamic standard cursors, values, order, and membership in the results set can change.


Keyset-driven Standard Cursors

In a keyset-driven cursor, the membership of rows in the results set and the order are fixed at cursor-open time, but changes to values that are made by the cursor owner and committed changes made by other users are visible. If a change disqualifies a row for membership or affects the order of a row, the row does not disappear or move unless the cursor is closed and reopened. Inserted data does not appear, but changes to existing data do appear as the rows are fetched.

Specify keyset-driven cursors by issuing the SET SCROLLOPTION KEYSET statement. Dynamic standard cursors are the default if no SET SCROLLOPTION statement is issued.

In a keyset-driven cursor, all keys for the results set are kept locally (which is one reason a unique index is required). Given the results of N rows, the keyset contains the same N rows that are in the results set. The fetch buffer contains 10 rows by default, and moves forward through the keysets as each FETCH statement is executed. If the SET ANSI_DEFAULTS ON statement is issued, the fetch buffer contains 1 row. You can modify the size of the fetch buffer by using the SET FETCH_BUFFER statement.

Although values can change between fetches, rows do not move around if the changes affect ORDER BY columns, and they do not disappear if they no longer satisfy the WHERE clause.


Dynamic Standard Cursors

In a dynamic standard cursor, committed changes that are made by anyone and uncommitted changes that are made by the cursor owner become visible the next time the user fetches data. Changes include insertions and deletions, as well as changes in order and membership. (Deleted rows do not leave holes.) Dynamic standard cursors cannot use a GROUP BY or HAVING clause. Dynamic standard cursors can use an ORDER BY clause in SQL Server version 6.5, but only if the table has a unique index.

Specify dynamic standard cursors by issuing the SET SCROLLOPTION DYNAMIC statement. This statement sets the keyset equal to the size of the fetch buffer, which is one row in Embedded SQL. Given a results set of N rows, the keyset and the fetch buffer are identical. They both contain a single row, which moves forward through the results set as each FETCH statement is executed.


Concurrency Control

Standard cursors control, through several options, concurrent access (when more than one user accesses and updates the same data at the same time). With concurrent access, data would soon become unreliable without some type of control. To activate the particular concurrency control desired, specify one of the options in the following table in a SET CONCURRENCY statement.

Option | Result
READONLY | Updates are not permitted.
LOCKCC | The rows are locked when they are fetched inside a user-initiated transaction. No other user can update these rows. Updates that are issued by the cursor owner are guaranteed to succeed.
 | Note that with Microsoft SQL Server, locks that are placed by LOCKCC prevent other users from reading and updating the locked data. Use the BEGIN TRANSACTION and COMMIT TRANSACTION statement to hold the locks. For more information about locking, see "Holding Locks," later in this chapter.
OPTCC and OPTCCVAL | Fetched rows are not locked; other users can update or read them.

To detect collisions between updates that are issued by the cursor owner and those that are issued by other users, standard cursors save and compare timestamps or column values. Therefore, if you specify either of the optimistic concurrency control options (OPTCC or OPTCCVAL), you may want to design the application to retry updates that fail because of collisions with other updates.

The two optimistic concurrency control options differ in the way they detect collisions, as described in the following table.

Option | Method of detection
OPTCC | Compares timestamps if available; otherwise, saves and then compares the value of all non-text, non-image columns in the tables with their previous values.
OPTCCVAL | Compares all non-text, non-image values whether or not a timestamp is available.


Holding Locks

When using standard cursors with Microsoft SQL Server, the duration of locks that are acquired during cursor operations is controlled by the application. In other words, an application that uses SET CONCURRENCY LOCKCC must also issue BEGIN TRANSACTION for the locking to have any affect. To hold the lock on the currently fetched row when LOCKCC is used, the application must issue a BEGIN TRANSACTION statement before each FETCH statement, and a COMMIT TRANSACTION statement after all operations on that row are complete.

For repeatable-read consistency, specify HOLDLOCK in the SELECT statement when opening the standard cursor, and issue a BEGIN TRANSACTION statement before the first FETCH statement. Locks are obtained as the data is fetched and are retained until the application issues a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement.


Cursors and Stored Procedures

Microsoft cursors use catalog stored procedures, which include:

sp_databases | sp_indexes
sp_tables | sp_fkeys
sp_columns | sp_table_privileges
sp_stored_procedures | sp_column_privileges
sp_sproc_columns | sp_server_info
sp_pkeys |


Appendix C: Browse Cursors

Browse cursors are implemented as separate database connections and are therefore treated as separate users, which can cause locking conflicts between cursors and UPDATE statements.


UPDATE and DELETE Statements

Positioned UPDATE and DELETE statements are used in conjunction with cursors and include WHERE CURRENT OF clauses instead of search condition clauses. The WHERE CURRENT OF clause specifies the location of the corresponding cursor.

Before a cursor can be used by a positioned UPDATE or DELETE statement, the SELECT statement in the cursor declaration must contain the FOR BROWSE option. (The SQL Server FOR BROWSE option is similar to the FOR UPDATE option in other SQL databases, but you must use SQL Server syntax.) To use the FOR BROWSE option, the table must have both a unique index and a timestamp column.

When performing a positioned UPDATE or DELETE statement, a method called optimistic concurrency control helps to prevent conflicts with other users. Optimistic concurrency control allows users to share data with less interference than they would experience with locking, which is the alternative concurrency control method.

Although optimistic concurrency control minimizes the likelihood of conflicts with other users, write your application so that it can handle updates to tables that are rejected due to locking conflicts or other problems. Use the SQLCODE field in the SQL communications area (SQLCA) data structure to detect conflicts with other users. (A SQLCODE value of –532 means that the positioned UPDATE or DELETE statement failed because of a conflict with another user.) For more information about the SQLCODE field, see "Using the SQLCA Data Structure," in Chapter 2, "Embedded SQL Programming." For more information about browse-mode processing, see Appendix A, "Advanced Programming."

A positioned update can be performed twice on the same row. To do this, use the FETCH statement to obtain the row, begin a transaction by using BEGIN TRANSACTION, and update a non-key column to itself. This locks the row and prevents other users from reading or updating it until a COMMIT TRANSACTION statement is issued.


Isolation Levels

An isolation level determines the degree to which data is isolated for use by one process and guarded against interference from other processes. With browse cursors, isolation level is controlled on a per-cursor basis in Microsoft SQL Server.

If you do not specify the HOLDLOCK option in the DECLARE CURSOR statement, the isolation level is similar to cursor stability. SQL Server maintains only a share lock on a single page of the database as you retrieve rows with the FETCH statement by using a browse cursor. As long as the cursor is located on a given row, no other process can update that data page.

When you use the HOLDLOCK option, the isolation level is set to repeatable read. With the FETCH statement and by using a browse cursor, SQL Server maintains a share lock on each fetched page of the database. No updates are permitted to the fetched data of the results set as long as the cursor is open, no matter what its position is in the table. The repeatable read isolation level is useful when you want to scan a results set and produce a self-consistent summary report without locking the entire results set. Other users can update rows of the results set that have not been fetched, but fetched rows cannot be updated until the cursor is closed. When a cursor is declared with HOLDLOCK, the lock is freed when the cursor closes. To reread a results set without freeing the lock, reopen the cursor without closing it.

When you use a DECLARE CURSOR statement with the FOR BROWSE option (which is required for UPDATE or DELETE WHERE CURRENT OF statements), SQL Server makes a snapshot of the results set when the cursor is opened. No locks are placed on the original data; the cursor cannot detect any changes that are made to the data as it fetches rows. If the cursor is reopened, SQL Server makes a new snapshot of the data, so the results might not be the same.

The FOR BROWSE and HOLDLOCK options are mutually exclusive. However, you can have cursors repeatedly read, and you can update rows by first locking the rows with the HOLDLOCK option and then opening a cursor by using the FOR BROWSE statement.

The following example illustrates the use of the HOLDLOCK option with a browse cursor:

/* Declare a cursor for browse. */
EXEC SQL DECLARE CURSOR c1 FOR SELECT * FROM orders FOR BROWSE;

/* Begin a transaction using dynamic SQL. */
strcpy(prep, "begin transaction");
EXEC SQL EXECUTE IMMEDIATE :prep;

/* Issue a singleton select that checks all rows but */
/* return one row of output only. */
EXEC SQL SELECT COUNT(*) INTO :count FROM orders HOLDLOCK;

/* The results set is now locked until the transaction is complete. */
/* Open the cursor previously declared for browse, do some fetches */
/* and updates, close it, reopen it, and so on. */
EXEC SQL OPEN c1;

while (SQLCODE ==0)
{
   EXEC SQL FETCH c1 INTO :order_struct;
                .
                .
                .
   EXEC SQL UPDATE orders SET trancode = :new_code
WHERE CURRENT OF c1;
}

EXEC SQL CLOSE c1;
EXEC SQL OPEN c1;

/* Some fetch and update operations can be done here, */
/* and the tables will not be changed. */

EXEC SQL CLOSE c2;
strcpy(prep, "commit transaction");
EXEC SQL EXECUTE IMMEDIATE :prep;
/* Now all locks are free. */

Because the updates occur over the same connection as the HOLDLOCK operation in the example, conflict does not occur between the FOR BROWSE and the HOLDLOCK options. The locks are released when the transaction is committed or rolled back.


Cursors and Lock Conflicts

Updates are issued through a single database connection including updates that reference a cursor (for example, in an UPDATE WHERE CURRENT OF cursor_name statement). Because of this, locking conflicts do not occur between updates that are issued under the same CONNECT TO, SET CONNECT, or default connection.

Each cursor's retrieval operations are performed through a separate database connection.

The following situations block cursors:

  • Cursors that are declared without FOR BROWSE place a read lock on the current page as they move through the results set. No changes can be made to that page by anyone, not even the cursor owner, until the cursor moves on.

    When the cursor attempts to read a page that contains an uncommitted change (made by anyone, including the cursor owner), the cursor waits until the change is committed.

  • Cursors that are declared with FOR BROWSE must wait for uncommitted changes (made by anyone, including the cursor owner) only during the OPEN CURSOR operation. Once the cursor is opened, subsequent changes do not cause the cursor to wait. When a cursor is reopened, it can be blocked by uncommitted changes.

Note

SQL Server internally performs locking at the page level rather than at the row level. This means that a second operation can be locked out by the first cursor operation even though the operations are accessing different rows.


Appendix D: Keywords

The following are reserved keywords for Embedded SQL. Additionally, keywords that are reserved for Transact-SQL are also reserved for Embedded SQL. (See the Microsoft SQL Server Transact-SQL Reference.)

APPLICATION | FETCH | OPTCCVAL
CALL | FETCHBUFFER | OPTION
CLOSE | FOUND | QUERYTIME
CONCURRENCY | GET | READONLY
CONNECT | HOST | SCROLLOPTION
CONNECTION | IMMEDIATE | SECTION
CUR_BROWSE | INCLUDE | SQLCA
CUR_STANDARD | INDICATOR | SQLDA
CURRENT | KEYSET | SQLERROR
CURSOR | LOCKCC | SQLWARNING
CURSORTYPE | LOGINTIME | USER
DESCRIBE | MIXED | USING
DESCRIPTOR | NOT | WHENEVER
DISCONNECT | OF | WORK
DYNAMIC | OPEN |
FORWARD | OPTCC |


Appendix E: Messages

When developing and running Embedded SQL programs, you will see different status and error messages. All message numbers that are returned by Embedded SQL are negative numbers.


Embedded SQL for C Messages

The messages in the following table are generated by Embedded SQL. Because the messages are not generated by SQL Server, they do not appear in the sysmessages table.

Message number | Run time/ compile time | Description
4998 | C | Attempt to connect to the specified database sever failed.
19031 | C | Unable to open bindfile.
19051 | C | Too many sections.
19101 | R | Statement too long.
19103 | R | Illegal %s value %s. Non-numeric %s value %s. (Invalid number for the timeout value.)
19104 | R/C | Incorrect SQL statement syntax.
19199 | C | ESQL keyword(s) detected in PREPARE statement.
19306 | C | Host variable used but not declared.
19313 | R | Too few host variables.
19324 | C | Host variable may not be used in this context.
19408 | R | Invalid SQL datatype for SQL_TYP_DECIMAL.
19413 | R | Data overflow occurred during decimal data conversion.
19422 | R | Unknown SQL Server datatype.
19423 | R | Invalid destination datatype.
19501 | R | No cursor declared.
19505 | C | Duplicate cursor name: %s.
19508 | R | Cursor is not positioned on a row.
19514 | R | Cursor is not prepared.
19517 | R | Cursor open attempted for non-SELECT prepared statement.
19521 | R | Open cursor failure for section %d of plans.
19523 | R | Failure to locate/close cursor. Section %d, plan %s.
19524 | R | Table for this cursor not updatable.
19525 | R | Attempt to fetch on unopened cursor.
19526 | R | No access plan for this cursor.
19527 | R | Could not get section for this cursor.
19528 | R | Connection for section %d of plan %s has NULL DBPROCESS.
19701 | R | NULL connection name.
 |  | Connection %s not found.
19702 | R | Connection name not found.
 |  | Attempt to close nonexistent connection.
19703 | R | Failed to get DBPROCESS. Autoconnect failure.
19706 | R | Login failure in section %d.
19707 | R | Duplicate connection name.
19822 | R | Improperly initialized user SQLDA.
19911 | C | The SQL datatype specified for a host variable is invalid.
19913 | C | The token identifier has already been used.
19917 | C | Invalid or incorrect option to sqlainit().
19946 | C | Cursor %s not declared.
19953 | C | Invalid call type.
19955 | R | Text not found in %s section %u.
19956 | R | Access plan section or statement text not found.
19957 | R | Access plan or statement text not found.
19994 | R | Can't run next BEGIN DECLARE sections. Statement ignored.
19995 | R | END DECLARE encountered without preceding BEGIN DECLARE statement. Statement ignored.
19999 | C | An internal error occurred.


DB-Library Messages

Error messages with message numbers in the 10,000 range are issued by DB-Library. These messages are listed in the following table.

Error number | Error/ error severity | Description
10000 | SQLEMEM EXRESOURCE | Unable to allocate sufficient memory.
10001 | SQLENULL EXPROGRAM | NULL DBPROCESS pointer encountered.
10002 | SQLENLOG EXCONSISTENCY | NULL LOGINREC pointer encountered.
10003 | SQLEPWD EXUSER | Login incorrect.
10004 | SQLECONN EXCOMM | Unable to connect: SQL Server is unavailable or does not exist.
10005 | SQLEDDNE EXINFO | DBPROCESS is dead or not enabled.
10006 | SQLENULLO EXCONSISTENCY | Attempt to login with NULL LOGINREC.
10007 | SQLESMSG EXSERVER | General SQL Server error: Check messages from SQL Server.
10008 | SQLEBTOK EXCOMM | Bad token from SQL Server: Datastream processing out of synchronization.
10009 | SQLENSPE EXPROGRAM | General nonspecific DB-Library error.
10010 | SQLEREAD EXCOMM | Read from SQL Server failed.
10011 | SQLECNOR EXPROGRAM | Column number out of range.
10012 | SQLETSIT EXINFO | Attempt to call dbtsput with an invalid timestamp.
10013 | SQLEPARM EXCONSISTENCY | Invalid parameter in DB-Library function reference.
10014 | SQLEAUTN EXPROGRAM | Attempt to update the timestamp of a table with no timestamp column.
10015 | SQLECOFL EXCONVERSION | Data conversion resulted in overflow.
10016 | SQLERDCN EXCONVERSION | Requested data conversion does not exist.
10017 | SQLEICN EXPROGRAM | Invalid value for computeid or invalid compute column number.
10018 | SQLECLOS EXCOMM | Error in closing network connection.
10019 | SQLENTXT EXPROGRAM | Attempt to get text point/timestamp from a nontext column.
10020 | SQLEDNTI EXPROGRAM | Attempt to use dbtxtsput to put a new text timestamp into a column that has a datatype that is neither SQLTEXT nor SQLIMAGE.
10021 | SQLETMTD EXPROGRAM | Attempt to send too much TEXT data through dbmoretext.
10022 | SQLEASEC EXPROGRAM | Attempt to send an empty command buffer to the SQL Server.
10023 | SQLENTLL EXUSER | Name too long for LOGINREC field.
10024 | SQLETIME EXTIME | SQL Server connection timed out.
10025 | SQLEWRIT EXCOMM | Write to SQL Server failed.
10026 | SQLEMODE EXCOMM | Network connection not in correct mode — invalid SQL Server connection.
10027 | SQLEOOB EXCOMM | Error in sending out-of-band data to SQL Server.
10028 | SQLEITIM EXPROGRAM | Illegal timeout value specified.
10029 | SQLEDBPS EXRESOURCE | Maximum number of DBPROCESSes already allocated.
10030 | SQLEIOPT EXPROGRAM | Attempt to use invalid dboption.
10031 | SQLEASNL EXPROGRAM | Attempt to set fields in a null LOGINREC.
10032 | SQLEASUL EXPROGRAM | Attempt to set unknown LOGINREC field.
10033 | SQLENPRM EXPROGRAM | NULL parameter not allowed for this dboption.
10034 | SQLEDBOP EXPROGRAM | Invalid or out of range dbn parameter.
10035 | SQLENSIP EXPROGRAM | Negative starting index passed to dbstrcpy.
10036 | SQLECNULL EXPROGRAM | NULL destination variable not allowed.
10037 | SQLESEOF EXCOMM | Unexpected EOF from SQL Server.
10038 | SQLERPND EXPROGRAM | Attempt to initiate a new SQL Server operation with results pending.
10039 | SQLECSYN EXCONVERSION | Attempt to convert data stopped by syntax error in source field.
10040 | SQLENONET EXCOMM | DB-Library network communications layer not loaded.
10041 | SQLEBTYP EXPROGRAM | Unknown bind type passed to DB-Library function.
10042 | SQLEABNC EXPROGRAM | Attempt to bind to a nonexistent column.
10043 | SQLEABMT EXPROGRAM | User attempted a dbbind with mismatched column and variable types.
10044 | SQLEABNP EXPROGRAM | Attempt to bind using NULL pointers.
10045 | SQLEBNCR EXPROGRAM | Attempt to bind user variable to a nonexistent compute row.
10046 | SQLEAAMT EXPROGRAM | User attempted a dbaltbind with mismatched column and variable types.
10047 | SQLENXID EXNONFATAL | The server did not grant a distributed-transaction ID.
10048 | SQLEIFNB EXPROGRAM | Illegal field number passed to bcp_control.
10049 | SQLEKBCO EXINFO | 1000 rows successfully bulk copied to host file.
10050 | SQLEBBCI EXINFO | Batch successfully bulk copied to SQL Server.
10051 | SQLEKBCI EXINFO | 1000 rows sent to SQL Server.
10052 | SQLEBCWE EXNONFATAL | I/O error while writing bcp datafile.
10053 | SQLEBCNN EXUSER | Attempt to bulk copy a null value into a server column that does not accept null values.
10054 | SQLEBCOR EXCONSISTENCY | Attempt to bulk copy an oversized row to SQL Server.
10055 | SQLEBCPI EXPROGRAM | Call bcp_init before any other bcp routines.
10056 | SQLEBCPN EXPROGRAM | Use bcp_bind, bcp_collen, and bcp_colptr only after calling bcp_init with the copy direction set to DB_IN.
10057 | SQLEBCPB EXPROGRAM | Do not use bcp_bind after bcp_init has been passed a non-null input filename.
10058 | SQLEVDPT EXUSER | For bulk copy, all variable-length data must have either a length-prefix or a terminator specified.
10059 | SQLEBIVI EXPROGRAM | Use bcp_columns and bcp_colfmt only after bcp_init has been passed a valid input file.
10060 | SQLEBCBC EXPROGRAM | Call bcp_columns before bcp_colfmt.
10061 | SQLEBCFO EXUSER | Host files must contain at least one column: bcp.
10062 | SQLEBCVH EXPROGRAM | Call bcp_exec only after bcp_init has been passed a valid host file.
10063 | SQLEBCUO EXRESOURCE | Unable to open host datafile: bcp.
10064 | SQLEBUOE EXRESOURCE | Unable to open error file: bcp.
10065 | SQLEBWEF EXNONFATAL | I/O error while writing bcp error file.
10066 | SQLEBTMT EXPROGRAM | Attempt to send too much text data with bcp_moretext.
10067 | SQLEBEOF EXNONFATAL | Unexpected EOF encountered in bcp datafile.
10068 | SQLEBCSI EXCONSISTENCY | Host-file columns can be skipped only when copying into the server.
10069 | SQLEPNUL EXCONSISTENCY | NULL program pointer encountered.
10070 | SQLEBSKERR EXCONSISTENCY | Cannot seek in datafile.
10071 | SQLEBDIO EXPROGRAM | Bad bulk-copy direction.
10072 | SQLEBCNT EXUSER | Attempt to use bulk copy with a nonexistent server table.
10073 | SQLEMDBP EXPROGRAM | Attempt to set maximum number of DPPROCESS lower than 1.
10075 | SQLCRSINV EXPROGRAM | Invalid cursor statement.
10076 | SQLCRSCMD EXPROGRAM | Attempt to call cursor functions when there are commands waiting to be executed.
10077 | SQLCRSNOIND EXINFO | One of the tables involved in the cursor statement does not have a unique index.
10078 | SQLCRSDIS EXPROGRAM | Cursor statement contains one of the disallowed phrases COMPUTE, UNION, FOR BROWSE, or SELECT INTO.
10079 | SQLCRSAGR EXPROGRAM | Aggregate functions are not allowed in a cursor statement.
10080 | SQLCRSORD EXPROGRAM | Only fully keyset driven cursors can have ORDER BY, GROUP BY, or HAVING PHRASES.
10081 | SQLCRSMEM EXPROGRAM | Keyset or window scroll size exceeds the memory limitations of this machine.
10082 | SQLCRSBSKEY EXPROGRAM | Keyset cannot be scrolled backward in mixed cursors with a previous fetch type.
10083 | SQLCRSNORES EXINFO | Cursor statement generated no results.
10084 | SQLCRSVIEW EXPROGRAM | A view cannot be joined with another table or a view in a cursor statement.
10085 | SQLCRSBUFR EXPROGRAM | Row buffering should not be turned on when using cursor functions.
10086 | SQLCRSFROWN EXINFO | Row number to be fetched is outside valid range.
10087 | SQLCRSBROL EXPROGRAM | Backward scrolling cannot be used in a forward scrolling cursor.
10088 | SQLCRSFRAND EXPROGRAM | Fetch types RANDOM and RELATIVE can only be used within the keyset of keyset driven cursors.
10089 | SQLCRSFLAST EXPROGRAM | Fetch type LAST requires fully keyset driven cursors.
10090 | SQLCRSRO EXPROGRAM | Data locking or modifications cannot be made in a READONLY cursor.
10091 | SQLCRSTAB EXPROGRAM | Table name must be determined in operations that involve data locking or modifications.
10092 | SQLCRSUPDTAB EXPROGRAM | Update or insert operations that use bind variables require single table cursors.
10093 | SQLCRSUPDNB EXPROGRAM | Update or insert operations cannot use bind variables when binding type is NOBIND.
10094 | SQLCRSVIIND EXPROGRAM | The view used in the cursor statement does not include all the unique index columns of the underlying tables.
10095 | SQLCRSNOUPD EXINFO | Update or delete operation did not affect any rows.
10096 | SQLCRSOS2 EXPROGRAM | Cursors are not supported for this server.
10097 | SQLEBCSA EXPROGRAM | The BCP hostfile %s contains only %ld rows. Skipping all of these rows is not allowed.
10098 | SQLCRSRO EXPROGRAM | Data locking or modifications cannot be made in a READONLY cursor.
10099 | SQLEBCNE EXPROGRAM | The table %s contains only %ld rows. Copying up to row %ld is not possible.
10100 | SQLEBCSK EXPROGRAM | The table %s contains only %ld rows. Skipping all of these rows is not allowed.
10101 | SQLEUVBF EXPROGRAM | Attempt to read unknown version of bcp format file.
10102 | SQLEBIHC EXPROGRAM | Incorrect host-column number found in bcp format file.
10103 | SQLEBWFF EXRESOURCE | I/O error while reading bcp format file.
10106 | SQLEBCPS EXPROGRAM | The row length exceeds SQL Server's maximum allowable size.
10107 | SQLEDTC EXRESOURCE | Microsoft Distributed Transaction Coordinator call failed.
10108 | SQLENOTIMPL EXPROGRAM | This function is not supported on this platform at this time.
10109 | SQLENONFLOAT EXPROGRAM | Float conversion attempt failed. The source is invalid.

The following table lists all error severities for DB-Library along with their numerical equivalents and an explanation of the type of error.

Error severity | Severity number | Description
EXINFO | 1 | Informational, nonerror.
EXUSER | 2 | User error.
EXNONFATAL | 3 | Nonfatal error.
EXCONVERSION | 4 | Error in DB-Library data conversion.
EXSERVER | 5 | The server has returned an error flag.
EXTIME | 6 | Timeout period exceeded while waiting for a response from the server — the DBPROCESS is still alive.
EXPROGRAM | 7 | Coding error in user program.
EXRESOURCE | 8 | Running out of resources — the DBPROCESS may be dead.
EXCOMM | 9 | Failure in communication with server — the DBPROCESS is dead.
EXFATAL | 10 | Fatal error — the DBPROCESS is dead.
EXCONSISTENCY | 11 | Internal software error — notify your primary support provider.


SQLSTATE Messages

SQLSTATE codes return values that are errors, warnings, or "no data found" messages. The following table displays how SQLSTATE and SQLCODE messages correspond to each other.

SQLSTATE code | SQLCODE code
First digit greater than 0 | Negative number
First digit equal to 1 | 1
First digit equal to 2 | 100

If a WHENEVER SQLERROR CALL myproc() statement is in effect, myproc() will be called in all cases where SQLSTATE returns a value with a nonzero first digit.

Use SQLSTATE to check for exceptions and warnings. To use SQLSTATE in your program, you must explicitly declare SQLSTATE within a host variable declaration block. For example:

EXEC SQL BEGIN DECLARE SECTION ;
char SQLSTATE [6] ;  // 5 characters for code and one character for
                     // null terminator
EXEC SQL END DECLARE SECTION;

Errors caught at compile time using the /SQLACCESS option will not be mapped to SQLSTATE values at runtime.

The following table lists SQL Server errors and the SQLSTATE codes they map to.

SQLSTATE code | SQL Server message or DB-Library error numbers | Description
01001 | 532,10095 (DB-Library) | Cursor update or delete failure due to optimistic concurrency check failure.
01003 | 8153 | Elimination of null values by set operator (warning )
01004 | n/a | Select or fetch into host variable that is too short.
02000 | n/a | No data found. Equivalent to SQLCODE = 100. Occurs when: Cursor fetch at end of result set Cursor fetch on empty result set Singleton select returns no data Searched delete affects no rows* Insert…Select affects no rows* Searched update affects no rows* The conditions designated with * are only detected when the program is compiled with the NOSQLACCESS option to NSQLPREP.
07002 | n/a | Number of columns does not match number of host variables.
21000 | 512 | Singleton select returns more than one row , or subquery preceded by = returns more than one value
22001 | 8152 | String data truncated on right on insert or update
22002 | n/a | Null value returned with no indicator variable provided.
22003 | 168,220,232,234, 236-238,244, 246,248,519-524, 535,8115, 10015 (DB-Library) | Arithmetic overflow error
22005 | 206,235,245,247,249, 256,257,305,409,518, 529 | Datatype conversion error.
22008 | 210,211,241,242,295, 296,517 | Datatype conversion error.
22012 | 8134 | Divide by zero error.
22019 | 1010, 506 | Invalid escape character
22022 | n/a | Indicator variable overflow.
22024 | n/a | Unterminated C string passed as input host variable
22025 | 310 | Invalid escape sequence, e.g. escape char not followed by %, _, or escape char.
23000 | 233,272,273,515,530, 547,1505,1508,2601, 2615,2626,2627,3604, 3605 | Constraint violation
24000 | n/a | Invalid cursor state.
25000 | 266,277,611,628,3902, 3903,3906,3908,6401 | Transaction state error.
40001 | 1205,1211,2625, 3309,7112 | Transaction rollback due to deadlock.
42000 | 207,208,213,229, 230,260 | Syntax error, permission violation, or other non-specific error.
44000 | 550 | View with check option violation