Table of Contents

Overview

Embedded SQL Programming

Embedded SQL Statements

Building Applications

Advanced Programming Topics

Standard DB-Library Cursors

Browse Cursors

Keywords

Messages


Overview

Microsoft® Embedded SQL for C offers to programmers an alternative to writing Microsoft SQL Server client applications using the DB-Library™ or Open Database Connectivity (ODBC) application programming interface (API). Embedded SQL programs enable you to include Transact-SQL™ statements directly in your C-language programs, an advantage to those familiar with programming with Transact-SQL.

Now see the following:

Call-level Method

Embedded SQL Method

System Requirements

Installing Embedded SQL


Call-level Method

SQL Server programs written using the DB-Library or ODBC API method 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. This type of programming requires no special precompiler, which is an advantage. With this type of programming, however, you cannot write Transact-SQL statements directly in your C programs.

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

#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 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 the Embedded SQL statements in the program into function calls acceptable to a C compiler. A C compiler can then compile the resulting source code into an executable program.

For example, the Embedded SQL code that does the same tasks shown in the DB-Library example in Call-level Method looks like this:

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 up to the next semicolon (;) contains Embedded SQL statements.

The Embedded SQL approach, using programming statements similar to Transact-SQL, is more concise than the function-call 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, it is less flexible for environments where the database structure is changing or is not known in advance.


System Requirements

Using Microsoft Embedded SQL for C, you can compile and run applications for the Microsoft Windows™ or Windows NT™ operating systems. To compile and run applications written with Embedded SQL, you need the files provided with Embedded SQL as well as the following software:

For Microsoft Windows:

•	Microsoft Windows 3.1 or later.

•	Microsoft Visual C++™ development system for Windows version 1.0 or later.

Or

Microsoft C/C++ version 7.0 or later.

•	Microsoft SQL Server for Windows NT version 4.2 or later. Your W3DBLIB.DLL (in the SQL\BIN directory) must be version 4.2 or later.

For Windows NT:

•	Microsoft Windows NT version 3.1 or later.

Or

Microsoft Windows NT Advanced Server version 3.1 or later.

•	Microsoft Visual C++ for Windows NT version 1.1 or later.

Or

Microsoft Developer Network Level II CD-ROM.

Or

Microsoft Win32® Software Development Kit.

•	Microsoft SQL Server for Windows NT version 4.2 or later. Your NTWDBLIB.DLL (in the SQL\DLL directory) must be version 4.2 or later.


Installing Embedded SQL

When you install Embedded SQL for C, the setup program copies the Embedded SQL for C files onto your hard disk.

To install Embedded SQL for C

1.	Insert the Embedded SQL for C disk.

2.	From the Windows or Windows NT Program Manager File menu, choose Run.

3.	In the Command Line box type:

drive_letter:setup

where drive_letter is the disk drive that contains the Embedded SQL for C disk.

4.	Follow the on-screen instructions.

The setup program copies the Embedded SQL files onto your hard disk in the following directories:

Directory/Files	Description
C:\SQL\BIN
SQLAKW.DLL	Run-time services for Windows
SQLPREP.EXE	Precompiler for Windows
SQLPREP1.EXE	Precompiler supplement for Windows
C:\SQL\BINN
ESQLVER.EXE	Version utility for Windows NT
NSQLPREP.EXE	Precompiler for Windows NT
C:\SQL\DLL
SQLAIW32.DLL	Precompiler services for Windows NT
SQLAKW32.DLL	Run-time services for Windows NT
C:\SQL\ESQL\INCLUDE
SQLCA.H	SQLCA header
SQLDA.H	SQLDA header
C:\SQL\ESQL\LIB
CAW32.LIB	SQLCA library for Windows NT
CAW.LIB	SQLCA library for Windows
SQLAKW32.LIB	Run-time services import library for Windows NT
SQLAKW.LIB	Run-time services import library for Windows
C:\SQL\ESQL\MISC
NTWDBLIB.DLL	DB-Library for Windows NT
W3DBLIB.DLL	DB-Library for Windows
C:\SQL\ESQL\SAMPLES.C
*.*	C samples


Embedded SQL Programming

Embedded SQL programs incorporate 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 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 compiled for the appropriate operating system.

Now see the following:

Building Applications

Embedded SQL Steps

Including Embedded SQL Statements

Embedded SQL Syntax

Connecting to a Database

Using Static and Dynamic Statements

Using Host Variables

Using the SQLDA Data Structure

Using Cursors

Managing Transactions

Tracking Errors Using the SQLCA Data Structure


Embedded SQL Steps

Embedded SQL programming includes the following steps:

1.	From the command line, the name of the Embedded SQL source file and the appropriate build parameters are submitted to sqlprep, the Embedded SQL precompiler. (The precompiler for Windows is SQLPREP.EXE, the precompiler for Windows NT is NSQLPREP.EXE.) The precompiler parses the submitted file, finds the Embedded SQL statements included in the code, and processes the statements.

2.	The precompiler produces a C source code file stripped of Embedded SQL statements and, if appropriate, a bind file. The Embedded SQL statements are replaced by calls to the run-time library. (The run-time library for Windows is SQLAKW.DLL, the run-time library for Windows NT is SQLAKN.DLL.) The run-time library calls DB-Library to access SQL Servers across a network.

During this step, stored procedures can be automatically created on a specific SQL Server, or they can be 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.


Including Embedded SQL Statements

You can include Embedded SQL statements anywhere in your C code that it is permissible to place a C function or routine. 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. An Embedded SQL statement that does not end with a semicolon (;) usually results in a sqlprep compiler error -19104 "Incorrect SQL statement syntax."

You can include Embedded SQL variable declaration sections, using the BEGIN DECLARE SECTION and END DECLARE SECTION statements, anywhere that it is valid to declare variables in C.


Embedded SQL Syntax

Embedded SQL for C syntax combines the IBM®-standard Embedded SQL syntax and most of the Transact-SQL syntax.

Now see the following:

Naming Conventions

Scope and Visibility

Embedded SQL Statements


Naming Conventions

Embedded SQL keywords 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

Embedded SQL cursor names, statement names, and connection names are case-sensitive. The same case must be used to declare and use these variable 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 names (statement names, cursor names, and connection names) 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, database name, login ID, and password for the connection. You can connect to servers and databases that are different from the ones used during compilation.

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.


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 not placed into stored procedures and are built and executed at run time.

With static SQL statements, before you build the application, you must know the structure of the SQL statement you want to run, including table and column names. These SQL statements are embedded in the program source code.

With dynamic SQL statements, you don't need to know the complete structure of a SQL statement before building the application. Dynamic SQL statements allow run-time input to provide information about the database objects to query.

Now see the following:

Static SQL Statements

Dynamic SQL Statements


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


Dynamic SQL Statements
Dynamic SQL statements - An incomplete Transact-SQL statement, some or all of which is supplied at run time. Dynamic SQL statements created using PREPARE and EXECUTE can contain parameter markers and host variables. Parameter markers are question marks (?) that act as placeholders for information supplied at run time. Dynamic SQL statements executed using EXECUTE IMMEDIATE must be pure Transact-SQL, and cannot contain paramenter markers, host variables, or keywords that pertain exclusively to Embedded SQL.


Static SQL Statements

An entire Transact-SQL transaction, containing 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 both 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.

When an SQL statement uses input host variables, the values of these variables are inserted in the statement before the statement runs. Any output host variables are filled with the values returned from the database.

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

At compile time, static SQL statements can be compiled as stored procedures into an access plan (access plans and their alternatives are described in Access Plans and Bind Files, Building Applications) or executed as dynamic SQL statements. When a static SQL statement contains only a single transaction-management command (such as BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION, or SAVE TRANSACTION), it 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.

All the rules for Transact-SQL stored procedures apply to static SQL statements. Stored procedures offer the advantage of increased performance.


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 parameter markers, question marks (?) that 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 using the PREPARE statement and then executed using the EXECUTE statement. Or you can combine the two statements using the EXECUTE IMMEDIATE statement. (For more information about Embedded SQL statements, see Embedded SQL Statements.)

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


Using Host Variables

Host variables provide a way to manage input and output for Embedded SQL statements. Host variables are standard C program variables that are declared in an Embedded SQL declare section 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 enables the compiler to distinguish between host variables and tables or columns that might have the same name.

The following example shows a simple 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 named 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);

}

Now see the following:

Declaring Host Variables

Host Variables and Null Values

Host Variables and Datatypes


Declaring Host Variables

Before you use a host variable in an Embedded SQL statement, you must declare the variable using the 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 for C 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 anywhere it is legal to declare C variables. Embedded SQL for C does not attempt to parse complex C variables such as structures or arrays (other than simple character arrays), so you cannot use structures or arrays directly 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. Both 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 following the corresponding host variable specified in the Embedded SQL statement. For example, the following embedded UPDATE statement uses a saleprice host variable with a companion indicator variable saleprice_null:

EXEC SQL UPDATE closeoutsale

SET temp_price = :saleprice :saleprice_null, listprice = :oldprice;

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

EXEC SQL UPDATE closeoutsale

SET temp_price = null, listprice = :oldprice;

You cannot use indicator variables in a search condition, but you can use the Transact-SQL is null function to search for null values. 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:

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 C. Embedded SQL must convert, or map, C datatypes to the appropriate SQL Server datatype. The following Embedded SQL code fragment illustrates the mapping of three host variables, 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";

The department column in the first UPDATE statement has the SQL Server smallint (integer) datatype. Because the host variable hostvar1 is declared as a C int (integer) datatype, the mapping of datatypes from C to SQL Server is straightforward and direct. The prod_descrip column in the second UPDATE statement has the SQL Server varchar (character) datatype. The hostvar2 variable is declared as an array of C char (character) data, which maps to the SQL varchar datatype.

In the third UPDATE statement, the price column has 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 variables can be declared as C floating-point or character datatypes. Embedded SQL does the appropriate conversions to and from a money value.

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 Advanced Programming Topics.


Using the SQLDA Data Structure

When either the number of host variables to be passed or their datatypes are unknown at compile time, use the SQLDA (SQL Descriptor Area) data structure instead of host variables. You use the SQLDA structure to define the type of data that you want to pass from the database to the host variable, or vice versa. The SQLDA structure is ordinarily used with parameter markers (?) to specify input values for prepared SQL statements. You can also use SQLDA with the DESCRIBE or PREPARE INTO statements to receive data from a prepared SELECT statement. Although you can't use the SQLDA structure with pure static SQL statements, you can use SQLDA with a cursor FETCH statement, whether the cursor is dynamic or static.

Now see the following:

Data Input and Output Using SQLDA


Data Input and Output Using SQLDA

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

For output data using SQLDA, 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 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. (For more information about the SQLDA structure, see Advanced Programming Topics.)

To use the SQLDA structure for input data, your application must supply the data for the fields of the entire SQLDA 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 structure, see Advanced Programming Topics.)


Using Cursors

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

When you write code where the result set includes several rows of data, you must declare and use a cursor, a mechanism that allows you to fetch rows one at a time, as well as to perform updates and deletions at a certain position within a result set. These mechanisms are also known as positioned update and positioned delete.

For example, if you write code using a SELECT statement or stored procedure that returns multiple rows, you must declare a cursor and associate it with the SELECT statement. Then, using the FETCH statement, you can retrieve one row at a time from the result set.

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

Microsoft Embedded SQL for C includes both standard cursor and browse cursor types. Use the SET CURSORTYPE statement to specify the type of cursor created by the OPEN statement. Standard cursors allow multiple cursor operations to share the same connection to SQL Server; each browse cursor requires a separate connection. It is recommended that most applications use standard cursors, because the sharing of a single connection avoids potential lock 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.

Standard DB-Library Cursors, provides a detailed description of standard DB-Library cursors. DB-Library cursors have a number of options for controlling row membership, locking, and performance characteristics. These options are all available to Embedded SQL programs through the 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.

Browse Cursors provides details about browse cursors. If positioned update or delete statements are to be used on a browse cursor, the SELECT statement used in the cursor declaration must include the FOR BROWSE option. Browse cursors can be declared using more types of SQL statements than standard-mode cursors. For example, you can declare a browse cursor using a stored procedure. 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 used in the same program.

Both standard and browse cursors are declared and used (including FETCH and positioned update or delete operations) in the same way.

Now see the following:

Declaring Cursors

Positioned Update or Delete Statements


standard cursor
standard cursor - A cursor (used for row-at-a-time retrieval) which shares the same connection to SQL Server as the main program. Standard cursors require a unique index, and use the cursor functions of Microsoft DB-Library. Standard cursors are set using the SET CURSORTYPE CUR_STANDARD statement.


browse cursor
browse cursor - A cursor (used for row-at-a-time retrieval) which requires a separate connection to SQL Server. Because SQL Server treats each connection as a separate user, locking conflicts can occur. Browse cursors are set using the SET CURSORTYPE CUR_BROWSE statement.


Declaring Cursors

Use the DECLARE CURSOR statement to define a cursor for row-at-a-time retrieval. You can declare a cursor in one of two ways: as a static cursor or as a dynamic cursor.

Now see the following:

Using Static Cursors

Using Dynamic Cursors


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 value of the host variable input parameters are read into the SELECT statement. You can't 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 dynamic cursors before they are prepared.

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

This following is an example of how to declare a dynamic cursor:

char szCommand[] = "select au_fname from authors where au_lname = ?";

EXEC SQL BEGIN DECLARE SECTION;

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 TRAN only on dynamically prepared cursors.

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


Managing Transactions

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

The COMMIT statement marks the end of a user-defined transaction initiated by a BEGIN TRANSACTION statement. The COMMIT statement makes the transaction's database changes 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 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 a COMMIT or ROLLBACK statement does not close cursors and applies only to the current connection if multiple connections are active.


Tracking Errors Using the SQLCA Data Structure

SQL Server traps and reports run-time errors to your Embedded SQL applications through the SQLCA (SQL communications area) data structure. 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 SQLCA data structure is automatically included in Embedded SQL applications by the precompiler.

The SQLCA data structure contains information that provides the status of an Embedded SQL statement that you run, showing whether or not the statement was successful.

You can include routines in your application to test the SQLCODE, SQLWARN, SQLERRM, and SQLERRD fields of the SQLCA data structure and to provide follow-up procedures according to the status 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 affected.

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 Advanced Programming Topics.

Now see the following:

The SQLCODE Variable

Using the WHENEVER Statement


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. The value of the SQLCODE variable indicates whether or not the last Embedded SQL statement run was successful. A value of 0 indicates that the last Embedded SQL statement was successful. Values other than 0 indicate warnings or errors. (For more information about SQLCODE, Advanced Programming Topics.)


Using the WHENEVER Statement

Explicitly checking 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 fields is to use a single WHENEVER statement in your application. 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 status values (SQLWARNING, SQLERROR, or NOT FOUND).

When a WHENEVER statement appears in the program code, it supersedes the conditions of all previous WHENEVER statements.

The following example shows the 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 Embedded SQL Statements.


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 anywhere in your C code that it is permissible to place a C function or routine. 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 8191 characters for Windows and 19,999 characters for Windows NT.

Embedded SQL keywords 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

Embedded SQL cursor names, statement names, and connection names are case-sensitive. The same case must be used to declare and use these variable 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.

Note  The Transact-SQL keyword null must 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 must not be in uppercase in programs for Windows NT, to avoid conflict with Windows NT−defined constants.

This chapter contains the following Embedded SQL statements. An asterisk (*) identifies statements that have names the same as those of Transact-SQL statements. For these statements, the syntax given in this chapter augments the standard Transact-SQL syntax. For more information about standard Transact-SQL statements and syntax, see the Microsoft SQL Server Transact-SQL Reference.

Now see the following:

Keywords

Statements


Statements

BEGIN DECLARE SECTION

CLOSE

CONNECT TO

DECLARE CURSOR

DELETE (POSITIONED)*

DELETE (SEARCHED)*

DESCRIBE

DISCONNECT

END DECLARE SECTION

EXECUTE*

EXECUTE IMMEDIATE

FETCH

GET CONNECTION

OPEN

PREPARE

SELECT INTO*

SET CONCURRENCY

SET CONNECTION

SET CURSORTYPE

SET FETCHBUFFER

SET OPTION

SET SCROLLOPTION

UPDATE (POSITIONED)*

UPDATE (SEARCHED)*

WHENEVER


BEGIN DECLARE SECTION

Marks the beginning of a C host variable declaration section.

Syntax

BEGIN DECLARE SECTION

Comments

The BEGIN DECLARE SECTION statement can be included anywhere where C permits variable declaration, and declared host variables follow the normal rules for scoping in C. Use END DECLARE SECTION to identify the end of a C declaration section. The embedded BEGIN DECLARE SECTION statement must follow an EXEC SQL statement.

Declare sections cannot be nested.

Use the following rules for declared host variables:

•	Host variables must be declared in C, not in Transact-SQL.

•	Host variables referenced by SQL statements must be included in a declaration section appearing before the SQL statement.

Example

EXEC SQL BEGIN DECLARE SECTION;

int id;

char name[30];

EXEC SQL END DECLARE SECTION;

See Also

END DECLARE SECTION


CLOSE

Ends row-at-a-time data retrieval initiated by the OPEN statement for a specified cursor and closes the cursor connection.

Syntax

CLOSE cursor_name

where

cursor_name

Specifies a previously declared and opened cursor. Cursor names can have as many as 30 characters, and can include alphanumeric characters and any symbols legal in filenames. Hyphens (-) are not permitted. The first character must be a letter.

Comments

The CLOSE statement discards unprocessed rows and frees any locks held by the cursor. The cursor must be declared and opened before it can be closed. All open cursors are closed automatically at the end of the program.

Example

EXEC SQL DECLARE C1 CURSOR FOR

SELECT ID, NAME, DEPT, JOB, YEARS, SALARY, COMM FROM STAFF;

EXEC SQL OPEN C1;

while (SQLCODE == 0)

{

/* SQLCODE will be zero if data successfully fetched */

EXEC SQL
FETCH C1 INTO :id, :name, :dept, :job, :years, :salary, :comm;
if (SQLCODE == 0)

printf("%4d %12s %10d %10s %2d %8d %8d",

id, name, dept, job, years, salary, comm);

}

EXEC SQL CLOSE C1;

See Also

DECLARE CURSOR

FETCH

OPEN


CONNECT TO

Connects to a specific database using the supplied username and password.

Syntax

CONNECT TO {[server_name.]database_name} [AS connection_name] USER login[.password]

where

server_name

Identifies the SQL Server. If you omit servername, the local server is assumed.

database_name

Identifies the database.

connection_name

Specifies a name for the connection. Connection names can have as many as 30 characters, and can include alphanumeric characters and any symbols legal in filenames. Hyphens (-) are not permitted. The first character must be a letter. Do not use current or all for the connection name, as they are invalid.

login

Specifies the user's login ID.

password

Specifies the user's password.

Comments

The options can include character literals or host variables. If you use only one connection, you don't need to supply a name for the connection. When you use more than one connection, you must specify a name for each connection.

Connection names are global within a process. Named connections are shared by separately compiled programs that are linked into a single executable module, and by a program and dynamic-link libraries executing in a single process.

All database transactions after a CONNECT TO statement that don't involve a browse cursor work through this most recently declared, current connection. To use a different connection, you must use the SET CONNECTION statement.

Examples

EXEC SQL CONNECT TO :svr USER :usr;

or

EXEC SQL CONNECT TO "gizmo.pubs" USER "sa";

or

EXEC SQL CONNECT TO gizmo.pubs USER sa;

See Also

DISCONNECT


DECLARE CURSOR

Defines a cursor for row-at-a-time data retrieval.

Syntax

DECLARE cursor_name CURSOR FOR {select_stmt | prepared_stmt_name}

where

cursor_name

Identifies a cursor in subsequent statements. Cursor names can have as many as 30 characters, and can include alphanumeric characters and any symbols legal in filenames. Hyphens (-) are not permitted. The first character must be a letter.

select_stmt

Is any valid Transact-SQL SELECT statement. Browse cursors can also use a stored procedure that contains a SELECT statement. This SELECT statement must not contain any aggregates.

prepared_stmt_name

Is the name of a prepared SQL SELECT statement.

Comments

The DECLARE CURSOR statement associates the cursor name with the specified SELECT statement and enables you to retrieve rows of data using the FETCH statement.

Cursor names are global within a program module (source code file). Cursors cannot be shared by separately compiled programs that are linked into a single executable module, or by a program and dynamic-link libraries executing in a single process.

The DECLARE CURSOR statement must appear before the first reference of the cursor. The SELECT statement runs when the cursor is opened.

The following rules apply to the SELECT statement:

It cannot contain an INTO clause or parameter markers (?).

•	It can contain input host variables previously identified in a host variable declaration section.

•	To enable repeatable reads, the SELECT statement must include a HOLDLOCK option. Additionally, standard cursors require an explicit user-defined transaction (opened using BEGIN TRANSACTION) to be open.

•	If positioned updates or deletions are to be performed on a browse cursor (see SET CURSORTYPE), the SELECT statement must include a FOR BROWSE option. Do not use FOR UPDATE.

Dynamic cursors must be declared before they are prepared.

Example

EXEC SQL DECLARE C1 CURSOR FOR

SELECT au_fname, au_lname FROM authors FOR BROWSE;

See Also

BEGIN DECLARE SECTION

CLOSE

FETCH

OPEN

PREPARE

SELECT INTO


DELETE (POSITIONED)

Removes the row where the cursor is currently positioned.

Syntax

DELETE [FROM] {table_name | view_name} WHERE CURRENT OF cursor_name

where

FROM

Specifies an optional keyword included for compatibility with other versions of SQL.

table_name

Specifies the same table name used in the SELECT statement (see DECLARE CURSOR).

view_name

Specifies the same view name used in the SELECT statement (see DECLARE CURSOR).

cursor_name

Specifies a previously declared, opened, and fetched cursor. Cursor names can have as many as 30 characters, and can include alphanumeric characters and any symbols legal in filenames. Hyphens (-) are not permitted. The first character must be a letter.

Comments

In addition to having the functionality of the Transact-SQL DELETE statement, the Embedded SQL DELETE statement includes functionality known as a positioned delete, which deletes the row most recently fetched by using a cursor. The other form of DELETE used in standard Transact-SQL statements is known as a searched delete.

Note that there is no search condition in a positioned delete. The WHERE CURRENT OF option is used in place of a search condition clause. The WHERE CURRENT OF option cannot be used in a PREPARE statement.

In a positioned delete using a browse cursor, the SELECT statement used to OPEN the cursor must include a FOR BROWSE clause, and the base table(s) must include a timestamp column. If an error prevents any row found by the search condition from being deleted, no changes are made to the database.

Using a browse cursor, or a standard cursor with optimistic concurrency control (SET CONCURRENCY with the OPTCC or OPTCCVAL option), if the row has been changed since the last FETCH statement, no changes are made to the database, and the value of SQLCODE is set to −532. Also, the SQLERRD3 field in SQLCA shows that no rows were processed.

Example

EXEC SQL DECLARE C1 CURSOR FOR

select au_fname, au_lname from authors for browse;

EXEC SQL OPEN C1;

while (SQLCODE == 0)

{

EXEC SQL FETCH C1 INTO :fname, :lname;

   if (SQLCODE == 0)

{
printf("%12s %12s\n", fname, lname);

printf("Delete? ");

scanf("%c", &reply);

if (reply == 'y')

{

EXEC SQL DELETE FROM AUTHORS WHERE CURRENT OF C1;

printf("delete sqlcode= %d\n", SQLCODE(ca));

}

}

}

See Also

DECLARE CURSOR

FETCH


DELETE (SEARCHED)

Removes table rows that meet the search criteria. DELETE is a standard Transact-SQL statement.

Syntax

DELETE [FROM] {table_name | view_name} [WHERE search_conditions]

where

FROM

Specifies an optional keyword included for compatibility with other versions of SQL.

table_name

Specifies the table to remove rows from.

view_name

Specifies the view to remove rows from.

search_conditions

Specifies any legal expression that can follow the standard Transact-SQL WHERE clause.

Comments

If you don't use a WHERE clause, all rows in the table specified in the DELETE statement are removed. The table, though it no longer contains data, continues to exist until you use a DROP TABLE statement.

You cannot use DELETE with a view that has a FROM clause specifying more than one table. This would change several tables, which is not allowed. However, UPDATE and INSERT statements that affect only one base table of the view are allowed.

Example

EXEC SQL DELETE FROM authors WHERE au_lname = 'White'


DESCRIBE

Populates the SQLDA data structure.

Syntax

DESCRIBE prepared_stmt_name INTO :sqlda_struct

where

prepared_stmt_name

Is a prepared SQL statement (see PREPARE).

:sqlda_struct

Specifies the output SQL descriptor area (SQLDA) data structure to be populated.

Comments

The DESCRIBE statement processes prepared, dynamic SQL statements. This statement populates the specified SQLDA data structure with the datatype, length, and column name of each column returned by the specified prepared statement. (Prepared statements are created using the PREPARE statement. Note that the DESCRIBE statement cannot be used in the FROM clause of a PREPARE statement.) The DESCRIBE statement works similar to a PREPARE statement with an INTO clause.

Before using DESCRIBE, the sqln and sqlabc fields of the SQLDA structure allocated by the application should be set to appropriate values. The sqln field must be set to the maximum number of column descriptor entries that can be held. The sqlabc field must be set to the length, in bytes, of the SQLDA structure, which is computed using the SQLDASIZE macro as follows:

SQLDASIZE(mysqlda->sqln)

When the DESCRIBE statement is executed, it sets the sqld field of the SQLDA structure to the number of column descriptors used by the prepared statement. If a nonselect statement was prepared, sqld is set to 0.

If the application does not know the maximum number of column descriptors necessary for the prepared statement, it can set sqln to 0 before using DESCRIBE. Then DESCRIBE will set sqld to the maximum number of column descriptors required without actually constructing any column descriptors.

After the DESCRIBE statement has populated the SQLDA structure, and before using the FETCH statement, the application must insert the address of each output variable into the sqldata field (part of the sqlvar field) of the SQLDA structure.

Example

#define NUM_RETURN_COLS 2

struct sqlda *mysqlda;

EXEC SQL BEGIN DECLARE SECTION;

char statement[] = "select au_fname, au_lname from authors";

EXEC SQL END DECLARE SECTION;

mysqlda = malloc(SQLDASIZE(NUM_RETURN_COLS));

if (mysqlda == NULL)

{

return;

}

mysqlda->sqln = NUM_RETURN_COLS;

mysqlda->sqldabc = SQLDASIZE(NUM_RETURN_COLS);

EXEC SQL DECLARE C1 CURSOR FOR stmt1;

EXEC SQL PREPARE stmt1 FROM :statement;

EXEC SQL DESCRIBE stmt1 INTO :mysqlda;

// SQLDA now contains a description of the dynamic SQL statement

EXEC SQL OPEN C1;

EXEC SQL FETCH C1 USING DESCRIPTOR :mysqlda;

See Also

Advanced Programming Topics

PREPARE


DISCONNECT

Cancels one or all database connections.

Syntax

DISCONNECT [connection_name | ALL | CURRENT]

where

connection_name

Specifies a name for the connection. Connection names can have as many as 30 characters, and can include alphanumeric characters and any symbols legal in filenames. Hyphens (-) are not permitted. The first character must be a letter. Do not use current or all for the connection name, as they are invalid.

ALL

Disconnects all connections. This option must be used before exiting the program.

CURRENT

Disconnects the current connection. The current position is either the most recent connection established by a CONNECT TO statement or a subsequent connection set by a SET CONNECTION statement.

Comments

The DISCONNECT statement ends connections made by the SET CONNECTION statement. The CURRENT option ends the connection made by the most recent SET CONNECTION statement.

When a connection is disconnected, all cursors opened for that connection are automatically closed.

An Embedded SQL for C program must issue a DISCONNECT ALL statement before exiting the main application to ensure proper clean up.

Example

EXEC SQL CONNECT TO jody.pubs AS jody1 USER sa;

EXEC SQL CONNECT TO buffy.pubs AS buffy1 USER sa;

EXEC SQL SET CONNECTION jody1;

EXEC SQL SELECT NAME FROM sysobjects INTO :name;

EXEC SQL SET CONNECTION buffy1;

EXEC SQL SELECT NAME FROM sysobjects INTO :name;

EXEC SQL DISCONNECT jody1;

EXEC SQL DISCONNECT buffy1;

// The first SELECT will take place against the pubs

// database on server "jody." The second SELECT will

// take place against the pubs database on server "buffy."

// In place of the two "disconnect" statements at the end,

// you could have also said:

// EXEC SQL DISCONNECT ALL;

See Also

CONNECT TO

SET CONNECTION


END DECLARE SECTION

Marks the end of a host variable declaration section.

Syntax

END DECLARE SECTION

Comments

The END DECLARE SECTION statement must be preceded by a BEGIN DECLARE SECTION statement.

Example

EXEC SQL BEGIN DECLARE SECTION;

int id;

char name[30];

EXEC SQL END DECLARE SECTION;

See Also

BEGIN DECLARE SECTION


EXECUTE

Runs a prepared SQL statement.

Syntax

EXECUTE prepared_stmt_name [USING DESCRIPTOR :sqlda_struct |
USING :hvar [,...]]

where

prepared_stmt_name

Is a previously prepared SQL statement name.

:sqlda_struct

Is a previously declared SQLDA data structure containing a description of the input values.

:hvar

Is one or more input host variables.

Comments

The EXECUTE statement processes dynamic SQL statements. It runs the specified prepared SQL statement after substituting values for any parameter markers (?) present. (Prepared statements are created using the PREPARE statement.) Only statements that do not return results are permitted.

If the prepared statement contains parameter markers, the EXECUTE statement must include either the USING :hvar option with the same number of host variables in the same order, or the USING DESCRIPTOR option identifying the SQLDA data structure already populated by the application.

With the USING DESCRIPTOR option, values of the program variables are substituted for parameter markers in the prepared statement. These program variables are addressed by corresponding sqldata entries in the SQLDA data structure. (The sqldata field is part of sqlvar.)

The number of parameter markers in the prepared statement must match the number of sqldata entries (USING DESCRIPTOR :sqlda_struct) or host variables (USING :hvar).

Example

EXEC SQL BEGIN DECLARE SECTION;

char   stmtbuf[] = "INSERT INTO publishers VALUES (?, ?, ?, ?)";

int    pubid;

char   pubname[30];

char   city[30];

char   state[3];

EXEC SQL END DECLARE SECTION;

/* Prompt the user for publication data */

printf("Enter publication ID number: ");

scanf("%d", pubid);

printf("Enter publication name: ");

scanf("%s", pubname);

printf("Enter city: ");

scanf("%s", city);

printf("Enter state: ");

scanf("%s", state);

EXEC SQL PREPARE stmt FROM :stmtbuf;

EXEC SQL EXECUTE stmt USING :pubid, :pubname, :city, :state;

See Also

EXECUTE IMMEDIATE

PREPARE


EXECUTE IMMEDIATE

Runs the SQL statement contained in the specified host variable.

Syntax

EXECUTE IMMEDIATE :stmt_hvar

where

:stmt_hvar

Is a character string host variable containing a Transact-SQL statement.

Comments

The EXECUTE IMMEDIATE statement must be pure Transact-SQL. It cannot contain input parameter markers or host variables. It cannot return results; any results returned from this statement are discarded. Additionally, the statement cannot contain SQL keywords that pertain exclusively to Embedded SQL keywords.

If any rows are returned, SQLCODE is set to +1, which indicates an exception.

Example

strcpy(prep, "delete from mf_table where name='elaine'");

EXEC SQL EXECUTE IMMEDIATE :prep;

See Also

EXECUTE


FETCH

Gets the next row from the result set produced by an OPEN statement for a specified cursor.

Syntax

FETCH cursor_name [USING DESCRIPTOR :sqlda_struct | INTO :hvar [,...]]

where

cursor_name

Is a previously declared and opened cursor. Cursor names can have as many as 30 characters, and can include alphanumeric characters and any symbols legal in filenames. Hyphens (-) are not permitted. The first character must be a letter.

:sqlda_struct

Is an output SQLDA data structure previously populated by the DESCRIBE statement and containing output value addresses. This option is used only with a cursor declared by prepared SELECT statements. (SELECT statements are prepared using the PREPARE statement.)

:hvar

Identifies one or more host variables to receive the data.

Comments

The FETCH statement retrieves the next row from the results set produced by the OPEN statement for this cursor and writes the values of the columns in that row to the corresponding host variables or to addresses specified in the SQLDA data structure.

An OPEN cursor_name statement must precede a FETCH statement, and the cursor must be open while FETCH runs. Also, the datatype of the host variable must be compatible with the datatype of the corresponding database column.

If the number of columns is less than the number of host variables, the value of SQLWARN3 is set to W. If an error occurs, no further columns are processed. (Processed columns are not undone.) The SQLCODE value of 100 indicates that no more rows exist in the results set. The USING DESCRIPTOR option can be used only with a dynamically defined cursor. The INTO :hvar option can be used with either a dynamic or static cursor.

Example

EXEC SQL DECLARE C1 CURSOR FOR

select au_fname, au_lname from authors for browse;

EXEC SQL OPEN C1;

while (SQLCODE == 0)

{

EXEC SQL FETCH C1 INTO :fname, :lname;
}

See Also

Advanced Programming Topics

DECLARE CURSOR

DESCRIBE

OPEN

PREPARE


GET CONNECTION

Retrieves the DBPROCESS pointer for the specified connection and stores it in a host variable for use with DB-Library function calls.

Syntax

GET CONNECTION connection_name INTO :hvar

where

connection_name

Specifies a previously opened connection name. Connection names can have as many as 30 characters, and can include alphanumeric characters and any symbols legal in filenames. Hyphens (-) are not permitted The first character must be a letter.

:hvar

Specifies the host variable, declared as type DBPROCESS *, used to store the DB-Library connection pointer. This DBPROCESS pointer can then be used with DB-Library function calls.

Comments

The GET CONNECTION statement stores the DB-Library DBPROCESS pointer for an Embedded SQL connection in a host variable. This DBPROCESS pointer can then be used to make DB-Library function calls. This is useful if you want to use features or functions specific to DB-Library, such as text and image handling functions, in your Embedded SQL for C program.

As with all DB-Library programs, you must first use #define to define the appropriate platform before including the DB-Library header files SQLFRONT.H and SQLDB.H and linking to the appropriate DB-Library .LIB file. Programs for Windows and QuickWin must first use #define DBMSWIN, and then link to W3DBLIB.LIB. Programs for Windows NT must first use #define DBNTWIN32, and then link to NTWDBLIB.LIB.

If you are using a WHENEVER statement in your program, the Embedded SQL keyword sqlerror must not be in uppercase, to avoid conflict with the DB-Library defined constant SQLERROR.

Example

#define DBNTWIN32

#include <windows.h>

#include <sqlfront.h>

#include <sqldb.h>

.

.

.

EXEC SQL BEGIN DECLARE SECTION;

DBPROCESS* dbproc;

EXEC SQL END DECLARE SECTION;

EXEC SQL CONNECT TO gizmo.pubs

AS my_connection

USER sa

EXEC SQL GET CONNECTION my_connection

INTO dbproc;

if (dbproc != NULL)

{

printf("Got DBPROCESS connection, current database is '%Fs'\n",

dbname(dbproc));

}

else

{

printf("ERROR: Getting DBPROCESS connection\n");

}

See Also

CONNECT TO


OPEN

Begins row-at-a-time data retrieval for a specified cursor.

Syntax

OPEN cursor_name [USING DESCRIPTOR :sqlda | USING :hvar [,...]]

where

cursor_name

Is a previously declared, opened, and fetched cursor. Cursor names can have as many as 30 characters, and can include alphanumeric characters and any symbols legal in filenames. Hyphens (-) are not permitted. The first character must be a letter.

:sqlda

Specifies an input SQLDA data structure previously constructed by the application. The SQLDA data structure contains the address, datatype, and length of each input parameter. This option is used only with cursors declared by dynamically prepared SQL statements.

:hvar

Specifies one or more input host variables corresponding to parameter markers in the SELECT statement. This option is used only with cursors declared by dynamically prepared SQL statements.

Comments

The OPEN statement runs the SELECT statement specified in the corresponding DECLARE CURSOR statement to produce the results set that is accessed one row at a time by the FETCH statement.

If the cursor is declared with a static SELECT statement (that is, one that was not prepared), the SELECT statement can contain host variables (:hvar) but not parameter markers (?). Host variables can only be used in place of constants. They cannot be used in place of the names of tables, columns, other database objects, or keywords. The current values of the host variables are substituted when the OPEN statement runs. This OPEN statement, because it is for a statically declared cursor, cannot contain the USING :hvar or USING DESCRIPTOR option.

If the cursor is declared with a dynamic SELECT statement (that is, one that was prepared), the SELECT statement can contain parameter markers but not host variables. Parameter markers are allowed in place of column names in the SELECT statement. If the SELECT statement has parameter markers, the OPEN statement must include either the USING :hvar option with the same number of host variables or the USING DESCRIPTOR option identifying the SQLDA data structure already populated by the application.

With the USING DESCRIPTOR option, values of the program variables are substituted for parameter markers in the SELECT statement. These program variables are addressed by corresponding sqldata entries in the SQLDA data structure. (For more information about SQLDA, see Using the SQLDA Data Structure.)

The number of parameter markers in the SELECT statement must match the number of sqldata entries (USING DESCRIPTOR :sqlda) or host variables (USING :hvar) in the OPEN statement.

A separate database connection is used for each open browse cursor. Each connection counts toward the total number of user connections configured on SQL Server. If the attempt to make a new connection when opening a browse cursor fails, or if there is no valid current connection when opening a standard cursor, a run-time error -19521 "Open cursor failure" usually occurs.

Example

EXEC SQL DECLARE C1 CURSOR FOR

SELECT au_fname,au_lname FROM authors FOR browse;

EXEC SQL OPEN C1;

while (SQLCODE == 0)

{

EXEC SQL FETCH C1 INTO :fname,:lname;

}

See Also

Advanced Programming Topics

CLOSE

DECLARE CURSOR

FETCH


PREPARE

Accepts an SQL statement from a character string in the host variable and associates the statement with a name.

Syntax

PREPARE stmt_name [INTO :sqlda] FROM :hvar

where

stmt_name

Specifies the statement to a subsequent EXECUTE or OPEN statement and/or a previous DECLARE CURSOR statement.

:sqlda

Specifies the output SQL descriptor area (SQLDA) data structure to be populated.

:hvar

Specifies the host variable that contains the SQL statement.

Comments

The PREPARE statement processes dynamic SQL statements.

The statement name cannot be reused in multiple PREPARE statements within the same program module. Statement names are global within a program module (source code file). Prepared statements cannot be shared by separately compiled programs that are linked into a single executable module, or by a program and dynamic-link libraries executing in a single process.

You can use a prepared statement in one of two ways:

•	You can open a prepared dynamic cursor. (Dynamic cursors must be declared before they are prepared.)

•	You can execute a prepared statement.

If the prepared statement is used by an EXECUTE statement, :hvar cannot contain an SQL statement that returns results.

Because singleton select statements (SELECT INTO) are not allowed in dynamic SQL statements, they cannot be prepared.

When using PREPARE, the SQL statement in :hvar cannot contain host variables or comments, but it can contain parameter markers (?). Additionally, the SQL statement cannot contain SQL keywords that pertain exclusively to Embedded SQL keywords.

The INTO :sqlda option merges the functionality of DESCRIBE into PREPARE. Therefore, these two sample statements are functionally identical:

EXEC SQL PREPARE gumbo INTO :mysqlda FROM :hamhock;

Or

EXEC SQL PREPARE gumbo FROM :hamhock;

EXEC SQL DESCRIBE gumbo INTO :mysqlda;

Note that the SQLDA data structure is populated only for output data. Even then, the value of each sqldata entry in the SQLDA structure must be set to the address of the corresponding program variable by the application. (The sqldata field is part of sqlvar.) The SQLDA data structure used for input parameters must be constructed entirely by the application. For more details, see Using the SQLDA Data Structure.

Example

EXEC SQL BEGIN DECLARE SECTION;

char	prep[] = "insert into mf_table values(?,?,?)";

char	name[30];

char	car[30];

double num;

EXEC SQL END DECLARE SECTION;

EXEC SQL PREPARE PREP_STAT FROM :prep;

while (SQLCODE == 0)

{

strcpy(name, "Elaine");

strcpy(car, "Lamborghini");

num = 4.9;

EXEC SQL EXECUTE PREP_STAT USING :name, :car, :num;

}

See Also

Advanced Programming Topics

DECLARE CURSOR

DESCRIBE

EXECUTE


SELECT INTO

Retrieves one row of results. The SELECT INTO statement is also known as a singleton select.

Syntax

SELECT [select_list] INTO :hvar [,...] select_options

where

select_list

Specifies the items (table columns, expressions) to retrieve data from.

:hvar

Specifies one or more host variables to receive the select_list items.

select_options

Specifies one or more statements or other options that can be used with the Transact-SQL SELECT statement (for example, a FROM or WHERE clause).

Comments

The SELECT INTO statement retrieves one row of results and assigns the values of the items in select_list to the host variables specified in the INTO list. If more columns are selected than the number of receiving host variables, then the value of SQLWARN3 is set to W. The datatype and length of the host variable must be compatible with the value assigned to it. If data is truncated, the value of SQLWARN3 is set to W.

The embedded SQL SELECT INTO statement is compatible with the Transact-SQL SELECT INTO statement. The Embedded SQL SELECT INTO statement is used only when results are retrieved for substitution in the host application. The Transact-SQL SELECT INTO statement does not return results to the application and must be issued using the Embedded SQL EXECUTE statement.

If more than one row is returned, SQLCODE is set to +1, which indicates an exception.

Example

EXEC SQL SELECT au_lname INTO :name FROM authors WHERE stor_id=:id;

See Also

BEGIN DECLARE SECTION

END DECLARE SECTION


SET CONCURRENCY

Sets the concurrency option for standard cursors.

Syntax

SET CONCURRENCY {LOCKCC | OPTCC | OPTCCVAL | READONLY}

where

LOCKCC

Specifies intent to update locking. If a FETCH statement is issued within a user-defined transaction, an exclusive lock is placed on the data before it is fetched. These exclusive locks prevent others from viewing or changing the data until the locks are released by closing the transaction.

OPTCC

Specifies optimistic concurrency control based on a timestamp column, if available, or based on all non-text, non-image columns.

OPTCCVAL

Specifies optimistic concurrency control based on all non-text, non-image columns.

READONLY

Specifies read-only cursors. Data retrieved by a FETCH statement cannot be modified.

Comments

The OPTCC option is the default. Once this statement is issued, it affects all subsequent cursor OPEN statements. This statement is ignored if you are using browse cursors.

If the OPTCC or OPTCCVAL option is used, an UPDATE WHERE CURRENT OF statement can fail if the row has been changed since the last FETCH statement. The application must be prepared to handle this situation.

If the LOCKCC option is used, you can choose to hold open the user-defined transaction only around each fetch. This requires that a SET FETCHBUFFER statement be issued before opening the cursor. Or you can choose to hold open the user-defined transaction for the life of the cursor. Note that holding open a transaction during LOCKCC cursor operations can significantly reduce concurrency and degrade performance.

Example

EXEC SQL SET CONCURRENCY READONLY

See Also

SET CURSORTYPE

SET FETCHBUFFER

SET SCROLLOPTION

Standard DB-Library Cursors


SET CONNECTION

Specifies which database connection to use for subsequent SQL statements.

Syntax

SET CONNECTION connection_name

where

connection_name

Specifies the name of a database connection. Connection names can have as many as 30 characters, and can include alphanumeric characters and any symbols legal in filenames. Hyphens (-) are not permitted. The first character must be a letter.

Comments

The value for connection_name must match the connection name specified in a previous CONNECT TO statement. The connection_name can be either the connection's literal name or a host variable containing character values. This can be used only with a named connection.

If you are using connections across compilation modules, you must use named connections.

Example

EXEC SQL CONNECT TO jody.pubs AS jody1 USER sa;

EXEC SQL CONNECT TO buffy.pubs AS buffy1 USER sa;

EXEC SQL SET CONNECTION jody1;

EXEC SQL SELECT NAME FROM sysobjects INTO :name;

EXEC SQL SET CONNECTION buffy1;

EXEC SQL SELECT NAME FROM sysobjects INTO :name;

EXEC SQL DISCONNECT jody1;

EXEC SQL DISCONNECT buffy1;

// The first SELECT will take place against the pubs

// database on server "jody." The second SELECT will

// take place against the pubs database on server "buffy."

// In place of the two "disconnect" statements at the end,

// you could have also said:

// EXEC SQL DISCONNECT ALL;

See Also

CONNECT TO


SET CURSORTYPE

Sets the use of either standard (DB-Library) cursors or browse (one-connection-per-cursor) cursors.

Syntax

SET CURSORTYPE {CUR_BROWSE | CUR_STANDARD}

where

CUR_BROWSE

Specifies that browse cursors are to be used. Each browse cursor requires a separate connection to SQL Server.

CUR_STANDARD

Specifies that standard DB-Library cursors are to be used. A unique index must exist on the source table. Each standard cursor shares the same single connection to SQL Server used by the application.

Comments

The CUR_BROWSE option is the default. Once this statement is issued, it affects all subsequent cursor OPEN statements.

If you intend to do positioned update or delete operations on a browse cursor, the SELECT statement used to open the browse cursor must include a FOR BROWSE clause, and the table must include a timestamp column.

Standard cursors cannot be opened using a stored procedure. The SELECT statement used to open a standard cursor cannot contain any of these Transact-SQL clauses: FOR BROWSE, SELECT INTO, COMPUTE, UNION. (See SET SCROLLOPTION for additional restrictions on standard cursors with a SCROLLOPTION of DYNAMIC.)

If your cursor definition violates any of these conditions, you will receive a SQLCODE of -19521 and error text describing the reason for the failure.

SQL Server treats each connection as a different user. Because each browse cursor uses a separate connection, locks held by one browse cursor can block operations attempted by other browse cursors. Because standard cursors share the same connection to SQL Server used by the application, the use of standard cursors eliminates many of the browse cursor locking problems.

Example

EXEC SQL SET CURSORTYPE CUR_STANDARD

See Also

Browse Cursors

SET CONCURRENCY

SET FETCHBUFFER

SET SCROLLOPTION

Standard DB-Library Cursors


SET FETCHBUFFER

Sets internally the number of rows retrieved at a single time for standard cursors.

Syntax

SET FETCHBUFFER num_rows

where

num_rows

Specifies internally the number of rows to be retrieved at a single time from SQL Server. Embedded SQL for C buffers num_rows internally and returns one row to the application for each FETCH statement issued.

Comments

The default is 1. Once this statement is issued, it affects all subsequent cursor OPEN statements. Because a FETCH statement returns only a single row, this option is for performance tuning only.

Example

EXEC SQL SET FETCHBUFFER 5

See Also

SET CONCURRENCY

SET CURSORTYPE

SET SCROLLOPTION

Standard DB-Library Cursors


SET OPTION

Assigns values for SQL Server query-processing options.

Syntax

SET OPTION {QUERYTIME | LOGINTIME | APPLICATION | HOST} value

where

QUERYTIME

Sets the number of seconds that DB-Library waits for SQL Server to respond to a Transact-SQL statement. The default is 0, meaning forever. (This option is functionally the same as the DB-Library dbsettime function.)

LOGINTIME

Sets the number of seconds that DB-Library waits for SQL Server to respond to a request for a DBPROCESS connection. The default is 10 seconds. (This option is functionally the same as the DB-Library dbsetlogintime function.)

APPLICATION

Sets the application name in the LOGINREQ structure. (This option is functionally the same as the DB-Library DBSETLAPP function.)

HOST

Sets the workstation name in the LOGINREQ structure. (This option is functionally the same as the DB-Library DBSETLHOST function.)

value

Is a valid value for the specified DB-Library option.

Comments

The value option can be either a literal or a host variable. The host variable must contain character values for APPLICATION or HOST; it must contain numeric values for LOGINTIME or QUERYTIME. The remaining DB-Library options are either not applicable to Embedded SQL (for example, row buffering) or can be set using Transact-SQL statements.

Example

EXEC SQL SET OPTION logintime 5;

EXEC SQL CONNECT TO jody.pubs USER sa;

// If we are unable to log in to the server "jody" within five

// seconds, the "connect" will time out and return to the program.

EXEC SQL SET OPTION querytime 2;

EXEC SQL SELECT name FROM sysobjects INTO :name;

// If the SELECT statement does not respond within two seconds,

// the query will time out and return to the program.

See Also

Using Host Variables


SET SCROLLOPTION

Sets the scrolling technique and row membership for standard cursors.

Syntax

SET SCROLLOPTION {DYNAMIC | FORWARD | KEYSET}

where

DYNAMIC

Specifies that row membership in the cursor is updated for every FETCH statement. Currently equivalent to FORWARD because the ability of DB-Library cursors to scroll backward is not available to Embedded SQL for C programs.

FORWARD

Specifies that row membership in the cursor is updated for every FETCH statement, and that the cursor can only scroll forward.

KEYSET

Specifies that row membership in the cursor is fixed at OPEN time.

Comments

The KEYSET option is the default and is the expected behavior for most Embedded SQL for C programs. Once this statement is issued, it affects all subsequent cursor OPEN statements. This statement is ignored if you are using browse cursors.

Row membership is determined by the subset of rows defined by the SELECT criteria that are available for retrieval using FETCH statements. This includes the specific data rows that are available, and what (if any) order those rows are in.

For KEYSET cursors, row membership is fixed when the OPEN statement is issued. After a KEYSET cursor has been opened, if new rows that would meet the SELECT criteria are added by another user, those new rows will not be visible within this cursor. You must close and reopen a KEYSET cursor to see those new rows.

For DYNAMIC and FORWARD cursors, row membership is dynamic. Each FETCH statement retrieves the most current data available. After a DYNAMIC or FORWARD cursor has been opened, if new rows that would meet the SELECT criteria are added by another user, those new rows will potentially be visible within this cursor using future FETCH statements. If the DYNAMIC or FORWARD options are to be used, the SELECT statement used to open the cursor cannot contain an ORDER BY, GROUP BY, or HAVING clause.

Example

EXEC SQL SET SCROLLOPTION DYNAMIC

See Also

SET CONCURRENCY

SET CURSORTYPE

SET FETCHBUFFER

SET SCROLLOPTION

Standard DB-Library Cursors


UPDATE (POSITIONED)

Changes data in the row where the cursor is currently positioned.

Syntax

UPDATE {table_name | view_name} SET column=expression[,...] WHERE CURRENT OF cursor_name

where

table_name

Specifies the table to be updated.

view_name

Specifies the view to be updated.

column

Specifies the column to be updated.

expression

Specifies a value for a particular column name. This value can be an expression or a null value.

cursor_name

Specifies a previously declared, opened, and fetched cursor. Cursor names can have as many as 30 characters, and can include alphanumeric characters and any symbols legal in filenames. Hyphens (-) are not permitted. The first character must be a letter.

Comments

In addition to having the functionality of the Transact-SQL UPDATE statement, the Embedded SQL UPDATE statement includes functionality known as a positioned update, which deletes the row most recently fetched by using a cursor. The other form of UPDATE used in standard Transact-SQL statements is known as a searched update.

Note that there is no search condition in a positioned update. The WHERE CURRENT OF option is used in place of a search condition clause. The WHERE CURRENT OF option cannot be used in a PREPARE statement.

In a positioned update using a browse cursor, the SELECT statement used to open the cursor must include a FOR BROWSE clause, and the base table(s) must include a timestamp column. If an error prevents any row found by the search condition from being deleted, no changes are made to the database.

Using a browse cursor, or a standard cursor with optimistic concurrency control (SET CONCURRENCY with the OPTCC or OPTCCVAL option), if the row has been changed since the last FETCH statement, no changes are made to the database, and the value of SQLCODE is set to −532. Also, the SQLERRD3 field in SQLCA shows that no rows were processed.

Example

while (SQLCODE == 0)

{
EXEC SQL FETCH c1 INTO :fname,:lname;

if (SQLCODE == 0)

{

printf("%s %s", fname, lname);

printf("Update? ");

scanf("%c", &reply);

if (reply == 'y')

{

printf("New last name? ");

scanf("%s", &lname);

EXEC SQL

UPDATE authors SET au_lname=:lname

WHERE CURRENT OF c1;

printf("update sqlcode= %s", SQLCODE);

}

}
 }

See Also

DECLARE CURSOR

FETCH


UPDATE (SEARCHED)

Changes data in existing rows. UPDATE is a standard Transact-SQL statement.

Syntax

UPDATE {table_name | view_name} SET [{table_name. | view_name.}] column_name={expression | NULL | (select_statement)}[,...] [FROM {table_name | view_name}[,...]] [WHERE search_condition]

where

table_name

Specifies the table to be updated.

view_name

Specifies the view to be updated.

column_name

Specifies the column to be updated.

expression

Specifies a value for a particular column name. This value can be an expression.

select_statement

Specifies a valid SELECT statement that returns a single result row with a single column of data.

search_condition

Specifies any legal expression that can follow the standard Transact-SQL WHERE clause.

Comments

Use UPDATE to change values in rows that have already been inserted. Use INSERT to add new rows.

Updating a varchar or text column with the empty string (' ') inserts a single space. All char columns are padded to the defined length.

All trailing spaces are removed from varchar column data except in the case of a string that contains only spaces. These strings are truncated to a single space.

The SQL batch size of 128K limits the maximum amount of data that you can alter with UPDATE. Since some memory is required for the query's execution plan, the actual size of the data you can include in an UPDATE statement is somewhat less. For example, you can update one column of about 125K or two columns of 60K each.

Example

UPDATE authors SET au_fname = 'Fred' WHERE au_lname = 'White'


WHENEVER

Specifies the default action (CONTINUE, GOTO, or CALL) to be taken after an error has occurred after an Embedded SQL statement is run.

Syntax

WHENEVER {NOT FOUND | SQLERROR | SQLWARNING} {CONTINUE | GOTO stmt_label | CALL function()}

where

NOT FOUND

Specifies that no rows were returned from a valid SELECT statement that executed properly or that a FETCH statement returned no more rows, and SQLCODE is set to 100 in the SQLCA structure.

SQLERROR

Specifies that an error message was received from SQL Server and stored in the SQLCA structure.

SQLWARNING

Specifies that an Embedded SQL for C warning occurred and was stored in the SQLCA structure.

CONTINUE

Causes the next sequential statement in the source program to run.

GOTO stmt_label

Identifies the place in the program that takes control when a certain error level is detected.

CALL function()

Calls a function defined in your application. The parentheses () are required. If you fail to included the parentheses, the function will not be called at run time. The function can include parameters.

Comments

The WHENEVER statement specifies the default action after running an Embedded SQL statement on each of the following conditions:

Condition	SQLCODE
No error	0
NOT FOUND	100
SQLWARNING	+1
SQLERROR	< 0 (negative)
The scope of a WHENEVER statement is related to the position of statements in the source program, not the run sequence. The default is CONTINUE for all three conditions.

Example

EXEC SQL WHENEVER sqlerror GOTO displayca;

.

.

.

EXEC SQL WHENEVER sqlerror CALL error_funct(param);

See Also

Advanced Programming Topics

Embedded SQL Programming


Building Applications

To run Embedded SQL applications for the Windows operating system, you must compile the applications on a computer running Windows. To run Embedded SQL applications for the Windows NT operating system, must compile the applications on a computer running Windows NT.

The following illustration shows how applications with Embedded SQL statements are compiled and linked, and how they operate during run time.

Now see the following:

Precompiling, Compiling, and Linking

Embedded SQL Applications at Run Time

Processing Embedded SQL Statements

Access Plans and Bind Files

Running the sqlprep Precompiler

Compiling and Linking Embedded SQL Applications

Debugging Embedded SQL Programs

Example Programs


Precompiling, Compiling, and Linking

Whether you compile an Embedded SQL application for the Windows or the Windows NT operating system, the general process of creating an executable application is the same. The specific procedures for compiling and linking Embedded SQL applications for Windows and Windows NT are described in separate sections later in this chapter. The following steps apply to building all Embedded SQL applications:

1.	Run the sqlprep precompiler (SQLPREP.EXE for Windows from an MS-DOS prompt, NSQLPREP.EXE for Windows NT) to prepare your Embedded SQL application 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 source code program that can be compiled with an appropriate C compiler. For more information about sqlprep, see Running the sqlprep Precompiler.

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

2.	Compile the C source code program created by the precompiler using an appropriate C compiler. The compiler creates object files.

3.	Link the object file(s), the Embedded SQL libraries, 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, the application handles SQL statements using the following process:

1.	Whenever it finds a SQL statement, the executable program calls the appropriate Embedded SQL run-time services for Windows (SQLAKW.DLL) or Windows NT (SQLAKW32.DLL).

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 structures). Status and error information is inserted into SQLCA.


Processing Embedded SQL Statements

You can use all Transact-SQL extensions with Embedded SQL, including stored procedures, local variables, and control-of-flow language. There are some minor restrictions due to syntax conflicts with the reserved keywords for Embedded SQL and because static Embedded SQL statements are compiled into stored procedures by the precompiler.

Abbreviate the Transact-SQL EXECUTE statement to EXEC so that it won't conflict with the Embedded SQL EXECUTE statement.

Transact-SQL statement labels cannot be used in static SQL statements because they conflict with the syntax for host variables. You can use Transact-SQL labels in dynamic SQL statements.

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

SQL Syntax Error -SQLCODE = -207

For a list of messages returned by Embedded SQL, see Messages.

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

For more information about restrictions related to other Embedded SQL statements, see Embedded SQL Statements.


Access Plans and Bind Files

The static SQL statements of your application can either be issued at run time (the same as dynamic SQL statements) or be placed into an access plan. An access plan is a set of stored procedures, with 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. Every time an application is recompiled, all old stored procedures with the same program module name are dropped.

If the database you need to connect to is unavailable at compile time, you can use the sqlprep precompiler to create a bind file. A bind file is a Transact-SQL script used to create the access plan stored procedures. You can later use the SQL Server isql utility program to apply the bind file to the database before running your application.


Running the sqlprep Precompiler

You must run the sqlprep precompiler (SQLPREP.EXE for Windows, NSQLPREP.EXE for Windows NT) 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 source code program that can be compiled with an appropriate C compiler. For more information about compiling and linking, see Compiling and Linking Embedded SQL Applications.

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 for C 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 source code files that it creates; don't explicitly include (#include) them in your programs.

•	When precompiling and using the /DB and /PASS options, be sure that an appropriate Net-Library is either loaded or available on the PATH to enable communication with SQL Server. For example, the named pipes Net-Library for the MS-DOS® operating system is the DBNMPIPE.EXE TSR, and the named pipes Net-Library for Windows NT is DBNMPNTW.DLL.

To run sqlprep, use the following syntax:

sqlprep program [/SQLACCESS | /NOSQLACCESS]
[/DB [server_name.]database_name /PASS login[.password]]
[/BIND filename] [/MSG filename] [/NOLOGO] [/PLAN name]
[/other_options]

where

program

Specifies the Embedded SQL program file (no extension) to be precompiled. The precompiler searches for a file with the program name and the file extension .SQC. For example, if you run sqlprep myprogrm, sqlprep searches for the program file 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 code. You must also include either /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 stored procedures that are created by this option are not used.

/NOSQLACCESS

Specifies that sqlprep will not automatically create stored procedures for static embedded SQL sections of the program code. If you also include /DB and /PASS to specify a direct connection to SQL Server, sqlprep will issue a warning message, connect to SQL Server, drop any stored procedures created from a previous precompile of the same program, and complete the precompile without creating any new stored procedures.

/DB [server_name.]database_name

Specifies a database, and optionally a SQL Server in which to put the access plan stored procedures. 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 precompiler option.

The server_name and/or database_name options in the sqlprep command line must match the server name and/or database name in the Embedded SQL CONNECT TO statement.

/PASS login[.password]

Specifies a user's login ID and password, or a user's login ID only. Whenever you use the /PASS option, you must also use the /DB precompiler option.

/BIND filename

Specifies the name of a bind file (no extension) to be created in place of the access plans. The extension .BND is automatically appended to the filename. The bind file is an isql script that can later be used to create the desired stored procedures (it includes CREATE PROCEDURE statements).

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

/MSG filename

Specifies the name of a file (no extension) to be created to receive error messages sent by the sqlprep precompiler. The extension .MSG is automatically appended to the filename. If the file has a length of 0, it is deleted.

/NOLOGO

Suppresses the sqlprep banner and the compiler banner (sqlprep invokes the compiler).

/PLAN name

Specifies a non-default name for an access plan. The name can have as many as eight characters.

/other_options

Specifies a user-defined option to be passed to the compiler.

Note  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.


Access Plan and Bind Files Options

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 the program module.

By default, the stored procedure names consist of the name of the program module, a date/timestamp converted to eight printable ASCII characters, a dollar sign ($), and the access plan section number. The date/timestamp means that access plan stored procedures are re-created each time. However, if you use the /PLAN option and specify a non-default plan name that ends with an underscore (_), sqlprep will not include the date/timestamp in the stored procedure names. This allows you to reuse the stored procedures created from a previous precompile.

Every time an application is recompiled, all old stored procedures with the same program module name are dropped.

If the database you need to connect to is unavailable at compile time (or you don't want to use it), you can use the /BIND option with the sqlprep precompiler to create a bind file. The bind file is a Transact-SQL script used to create the access plan stored procedures. You can later use the SQL Server isql utility program to apply the bind file to the database. You must apply the bind file to the database before you can run the corresponding C application.

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


Compiling and Linking Embedded SQL Applications

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

For more informationon how to compile and link Embedded SQL applications for each of the available platforms.

Now see the following:

Compiling and Linking for Windows

Compiling and Linking for QuickWin

Compiling and Linking for Windows NT


Compiling and Linking for Windows

You can compile and link Embedded SQL applications for Windows on a computer running the MS-DOS or Windows operating system, using Microsoft Visual C++ for Windows version 1.0 and later or Microsoft C/C++ version 7.0 and later.

Before you compile an Embedded SQL application for Windows, do the following:

•	Set the LIB environment variable to include the full path where the following library files are located:

CAW.LIB (supplied with Embedded SQL for C)

SQLAKW.LIB (supplied with Embedded SQL for C)

LIBW.LIB (supplied with Microsoft C/C++ or Visual C++ for Windows)

MLIBCEW.LIB or LLIBCEW.LIB (supplied with Microsoft C/C++ or Visual C++ for Windows)

For example:

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

To compile and link a program for Windows

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

CL /c /W3 /AM MYPROGRM.C

In this 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 allowed by the compiler.

2.	Run the linker (LINK.EXE) as you would for Windows to link the compiled Embedded SQL object file and system libraries to create 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 this example, the linker links the compiled object file MYPROGRM.OBJ, the system libraries LIBW.LIB and MLIBCEW.LIB, and the Embedded SQL for C libraries SQLAKW.LIB and CAW.LIB together to create the executable file MYPROGRM.EXE.

To run a Windows-based Embedded SQL for C program, you need the dynamic-link libraries SQLAKW.DLL, W3DBLIB.DLL, and DBNMP3.DLL (or other appropriate Net-Library) available in your PATH.


Compiling and Linking for QuickWin

QuickWin is a set of libraries that help turn source code for MS-DOS into simple Windows-based applications. You can compile and link Embedded SQL applications for QuickWin on a computer running the MS-DOS or Windows operating system, using Microsoft Visual C++ for Windows version 1.0 and later or Microsoft C/C++ version 7.0 and later.

Before you compile an Embedded SQL application for QuickWin, do the following:

•	Set the LIB environment variable to include the full path where the following library files are located:

CAW.LIB (supplied with Embedded SQL for C)

SQLAKW.LIB (supplied with Embedded SQL for C)

LIBW.LIB (supplied with Microsoft C/C++ or Visual C++ for Windows)

MLIBCEWQ.LIB or LLIBCEWQ.LIB (supplied with Microsoft C/C++ or Visual C++ for Windows)

For example:

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

To compile and link a QuickWin program for Windows

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

CL /c /W3 /AM MYPROGRM.C

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

2.	Run the linker (LINK.EXE) as you would for Windows to link the compiled Embedded SQL object file and system libraries to create 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 this example, the linker links the compiled object file MYPROGRM.OBJ, the system libraries LIBW.LIB and MLIBCEWQ.LIB, and the Embedded SQL for C libraries SQLAKW.LIB and CAW.LIB together to create the executable file MYPROGRM.EXE.

To run a Windows-based Embedded SQL for C program, you need the dynamic-link libraries SQLAKW.DLL, W3DBLIB.DLL, and DBNMP3.DLL (or other appropriate Net-Library) available in your PATH.


Compiling and Linking for Windows NT

You can compile and link Embedded SQL applications for Windows NT on a computer running the Windows NT operating system, using Microsoft Visual C++ for Windows NT version 1.1 and later or the Microsoft Developer Network Level II CD-ROM.

Before you compile an Embedded SQL application for Windows NT, do the following:

•	Set the LIB environment variable to include the full path where the following library files are located:

CAW32.LIB (supplied with Embedded SQL for C)

SQLAKW32.LIB (supplied with Embedded SQL for C)

KERNEL32.LIB (supplied with Visual C++ for Windows NT)

USER32.LIB (supplied with Visual C++ for Windows NT)

GDI32.LIB (supplied with Visual C++ for Windows NT)

LIBCMT.LIB (supplied with Visual C++ for Windows NT)

For example:

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

To compile and link a program for Windows NT

1.	Run the compiler (CL.EXE) as you would for Windows NT 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 necessary for Windows NT for Intel® platforms. You can use any additional compiler options allowed by the compiler.

2.	Run the linker (LINK.EXE) as you would for Windows NT to link the compiled Embedded SQL object file and system libraries to create an executable file for Windows NT. When linking files for Windows NT−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 CAN.LIB

In this example, the linker links the compiled object file MYPROGRM.OBJ, the system libraries, and the Embedded SQL for C libraries SQLAKW32.LIB and CAW32.LIB together to create the executable file MYPROGRM.EXE for Windows NT.

To run a Windows NT−based Embedded SQL for C program, you need the dynamic-link libraries SQLAKW32.DLL, NTWDBLIB.DLL, and DBNMPNTW.DLL (or other appropriate Net-Library) available in your PATH.


Debugging Embedded SQL Programs

You can use a Microsoft debugger, including the integrated debugger in Visual C++ for Windows and Visual C++ for Windows NT, CodeView® window-oriented debugger for Windows, and WinDebug for Windows NT, to examine your Embedded SQL for C program while it is executing. Using a Microsoft debugger, Embedded SQL source statements are displayed as lines of executable code. While debugging, you can set breakpoints on Embedded SQL statements and set watches to test host variables. To watch SQLCA and SQLDA fields, you must watch the SQLCA and SQLDA structures and members variables. For example, you must watch sqlca->sqlcode instead of the SQLCODE macro, and you must watch sqlca->sqlerrd[0] instead of the SQLERRD1 macro.

When using the integrated debugger in Visual C++ for Windows or Visual C++ for Windows NT, 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 generated by sqlprep and make that window active, and then from the Debug menu choose Go.

When using CodeView for Windows, when you step through the code, the cursor disappears until it reaches the next C statement.


Example Programs

The Embedded SQL for C setup program installs the source code for a number of sample programs (in C:\SQL\ESQL\SAMPLES.C by default).

Before building 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 these environment variables as appropriate for your system.

You can build any of the sample programs using one of two batch files (MAKEC.BAT and MAKEW.BAT) included with the samples. Use MAKEC.BAT to build a C application for the Windows NT console and QuickWin environments. Use MAKEW.BAT to build a Win16/Win32 application for the graphical environments in Windows NT and Windows. All of the samples share a single make file (MAKEFILE) that contains all the steps and options necessary to build an Embedded SQL for C application.

Both the MAKEC.BAT and MAKEW.BAT batch files set environment variables used by MAKEFILE to control the build process. Edit these 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, all the sample applications accept the following command-line arguments for specifying the SQL Server, login ID, password, and database to use, and will prompt you for unspecified options:

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

where:

program

Specifies the Embedded SQL for C sample application 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 assumed.

login_id

Is your SQL Server login ID.

password

Is your SQL Server password. If password is omitted, a NULL password is assumed.

database

Specifies the database to use.

Now see the following:

GENCHAR (Generic for C)

GENWIN (Generic for Windows and Windows NT)

EDBLIB (Embedded SQL and DB-Library for Windows and Windows NT)

Examples


GENCHAR (Generic for C)

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

This program uses the GENCHAR.SQC, GENCHAR.H, GCUTIL.C, GCUTIL.H, and QUICKWIN.DEF files.

You can build GenChar for the Windows NT console environment using this command:

makec genchar nt

and build it for the QuickWin environment using this command:

makec genchar qwin


GENWIN (Generic for Windows and Windows NT)

GENWIN is a generic Embedded SQL program for Windows and Windows NT, written using the Win16/Win32 API, the C language, standard libraries for Windows and C, and Embedded SQL statements. It connects to the specified SQL Server and issues a simple SELECT query from the authors table in the pubs database.

This program uses the GENWIN.SQC, GENWIN.H, GWUTIL.C, GWUTIL.H, GENWIN.DEF, GENWIN.ICO, and GENWIN.RC files.

You can build GenWin for Windows NT using this command:

makew genwin nt

and build it for Windows using this command:

makew genwin win


EDBLIB (Embedded SQL and DBLibrary for Windows and Windows NT)
EDBLIB (Embedded SQL and DB-Library for Windows and Windows NT)

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

This program uses the EDBLIB.SQC, EDBLIB.H, GWUTIL.C, GWUTIL.H, EDBLIB.DEF, EDBLIB.ICO, and EDBLIB.RC files.

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

set DBLIB=0

to this:

set DBLIB=1

This tells MAKEFILE to link the program to DB-Library. Then you can build EDBLIB for Windows NT using this command:

makew edblib nt

and build it for Windows using this command:

makew edblib win


Examples

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

Each of these programs uses the appropriate EXAMPLEn.SQC, GCUTIL.C, GCUTIL.H, and QUICKWIN.DEF files. Some of these programs use the author2 table, which can be created using the AUTHOR2.SQL file.

You can build an example program for the Windows NT console environment using this command:

makec examplen nt

and build it for the QuickWin environment using this command:

makec examplen qwin

where n is the number of the example program.

Now see the following:

Example 1

Example 2

Example 3

Example 4

Example 5

Example 6

Example 7

Example 8


Example 1

Example 1 declares and opens a standard cursor using a prepared SQL statement, then fetches rows from the sysobjects table in the specified database.


Example 2

Example 2 declares and opens a browse cursor, then fetches rows from the sales table in the pubs database into a C structure.


Example 3

Example 3 declares and opens a standard cursor, and fetches rows from the author2 table in the pubs database. After each row is fetched, it begins a transaction, performs a dummy update to lock that row, allows you to change that author's last name, and then commits the transaction.


Example 4

Example 4 connects to two SQL Servers, declares and opens two standard cursors using a prepared SQL statement, fetches rows from the author2 table in the pubs database of each SQL Server, and then allows you to issue positioned updates of each author's last name as the rows are fetched.


Example 5

Example 5 issues a singleton select to retrieve a row from the titles table in the pubs database into either a C structure or the individual fields of a C structure, uses browse cursors with a SELECT statement and the sp_who stored procedure, issues both static and dynamic INSERT statements, and issues a SELECT of various date and time values.


Example 6

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 into a temporary table and retrieves a row from that temporary table using EXECUTE IMMEDIATE, and executes a static Transact-SQL batch that uses control-of-flow language.


Example 7

Example 7 opens a standard cursor with HOLDLOCK within a transaction, inserts, 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, and executes the sp_addtype and sp_droptype stored procedures.


Example 8

Example 8 is a simple SQL command-line utility, similar to isql.


Advanced Programming Topics

Most of the features used in writing Embedded SQL code are discussed in Embedded SQL Programming. This appendix provides supplemental information for developing Embedded SQL applications. This appendix discusses:

•	Mapping datatypes from Transact-SQL to C, and vice versa

•	Using the SQLDA data structure for data input and output

•	The contents of the SQLCA data structure

•	Using the EXEC statement to selectively bypass the creation of an access plan


Data Mapping

Embedded SQL maps C datatypes to SQL Server datatypes, and vice versa. The C datatypes map to these SQL Server datatypes.

C datatype	Assigned SQL Server datatype
short	SMALLINT
int	SMALLINT
long	INT
float	REAL
double	FLOAT
char	VARCHAR[x] (1)
void *p	BINARY (2)
char byte	TINYINT

1	For more information, see Mapping Character Data.

2	See Pointers as Host Variables

And these datatypes map to:

char, varchar

text

binary, varbin (See Mapping Binary Data)

image (See Mapping Binary Data)

tinyint

smallint

int

bit

real

float

Conversions are supported for all datatypes except the following:

Unsupported Conversions

C  datatype	Assigned  SQL Server  datatype	datetime/ smalldatetime	money/ smallmoney
short	SMALLINT	X	X
int	SMALLINT	X	X
long	INT	X	X
float	REAL	X	X
double	FLOAT	X	X
char	VARCHAR[x] (1)
void *p	BINARY (2)
char byte	TINYINT	X	X

1	For more information, see Mapping Character Data.

2	See Pointers as Host Variables

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. In this case, no exception is generated.

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

Now see the following:

Mapping Character Data

Pointers as Host Variables

Mapping Date/Time Data

Mapping Binary Data

Preparing SQLDA for Data Input and Output

SQLDA Data Structure

SQLCA Data Structure


Mapping Character Data

You can declare character datatypes as host variables with a fixed maximum length by explicitly giving 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 this example, two host variables are declared, each with a maximum length of 18 bytes.

Host variables declared as a single-byte character with no explicit length or initial value (for example, char var3) are treated as a 1-byte integer datatype.

This table shows how C character data is translated 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 receiving the columns is fixed-length.
From SQL Server  char, varchar,  or text to C character	Data is copied and, if necessary, truncated to the length of the receiving field and terminated with a null. If data is truncated, the SQLWARN1 field of the SQLCA structure is set.


Pointers as Host Variables

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


Mapping Date/Time Data

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

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

You can send dates to SQL Server from C character fields using any of the character date formats acceptable to SQL Server. For date without 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 time without date, use a statement similar to this:

SELECT CONVERT(char, date, 8) FROM sales

Attempting to put any fixed-length datatype into a buffer that is too small causes an error and no data is copied.


Mapping Binary Data

SQL Server binary, varbinary, and image columns are mapped to C memory regions without data conversion, and data is truncated or padded with 0x00 according to the length of the receiving field. They are mapped to C character strings by conversion to or from a hexadecimal string without the leading "0x" characters.

When input, a C pointer datatype can be mapped to a SQL Server binary, varbinary, or image column using dynamic SQL statements. To do this, use two question marks (??) instead of one as parameter markers. 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, but you can use dynamic SQL statements with PREPARE/EXECUTE statements or the EXEC flag to avoid this limitation.


Data Input and Output Using SQLDA
Preparing SQLDA for Data Input and Output

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

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

Before using the SQLDA structure in a PREPARE INTO or DESCRIBE statement, your application must set the size of SQLDA 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 (sqllen) are filled with information in the SQL Server column from a PREPARE INTO or a DESCRIBE statement. These values can be overridden by the application prior to a FETCH statement. The sqltype code and the sqld address assigned must be one of the valid datatypes listed in the "Valid Values for sqltype" table, 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 this number is unknown, you can set sqln to 0, and then issue a DESCRIBE statement. No column detail information is moved into the SQLDA structure, but the number of columns in the results set is inserted into sqld.

To use the SQLDA structure for input data, your application must supply the data for the fields of the entire SQLDA structure, including the sqln, sqld, sqldabc, and 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 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];
};
Now see the following:

SQLDA

sqlvar

Valid Values for sqltype


SQLDA

Field/Datatype	Contains
sqldaid
unsigned char*	The text string SQLDA. This field is not used for FETCH, OPEN, or EXECUTE.
sqldabc
long	The length of the SQLDA data structure (sqln * 44 + 16).
sqln
short	The total number of sqlvar entries allocated, equal to the number of input parameters or output columns.
sqld
short	The number of sqlvar entries used.
sqlvar
struct	The values listed in the sqlvar table. It can occur several times, listed once per column in the results set or input parameter.


sqlvar

Field/Datatype	Contains
sqltype
short	The number representing the datatype of columns or host variables and indicating whether null values are allowed. (For Valid Values, see sqltype.)
sqllen
short	The external length of a value from a column.
sqldata
unsigned char far*	For FETCH, OPEN, and EXECUTE, the address of the host variable (must be inserted by the application). For DESCRIBE and PREPARE, sqldata is not used.
sqlind
short far*	For FETCH, OPEN, and EXECUTE, the address of an associated indicator variable, 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, sqlind is not used.
sqlname
struct	The name and length of the column (not used for FETCH, OPEN, and EXECUTE).
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

Code/Datatype	SQL Server datatype
392/393
26-byte date time	datetime
444/445*
Binary	binary, varbinary, image
452/453*
Fixed-length char <= 254 bytes	char or varchar
456/457
Long character	text
460/461*
Fixed-length character > 254 bytes/null-terminated character string	char, varchar, or text
462/463
Null-terminated character string	char, varchar, or text
480/481
8-byte floating point	float
482/483
4-byte floating point	real
484/485*
Money	money
496/497*
4-byte integer	int
500/501*
2-byte integer	smallint (and tinyint)
* These types can be returned in C by a PREPARE INTO or DESCRIBE statement.


SQLCA Data Structure

The SQLCA data structure contains status information about the last executed Embedded SQL statement. The SQLCA structure definition (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[8];	// Reserved
} SQLCA;
Now see the following:

SQLCA


SQLCA

Field/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 found.
	100  A FETCH was issued but no more rows satisfy the SELECT criteria 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, consisting of two parts:
sqlerrml
short	The length of the error message in sqlerrmc (0−70).
sqlerrmc
unsigned char	The text of the error message. Error messages longer than 70 bytes are truncated.
sqlerrp
unsigned char	Reserved (diagnostic information).
sqlerrd
long	An array of six integer status codes (those not listed below are reserved):
sqlerrd[1]	SQL Server error number.
sqlerrd[2]	SQL Server severity.
sqlerrd[3]	The number of rows affected.
sqlwarn	Eight warning flags, each containing a blank or W (those not listed below are reserved):
sqlwarn[0]
unsigned char	A summary of all warning fields. Blank means there are no warnings.
sqlwarn[1]
unsigned char	W indicates 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 doesn't match the number of host variables.
sqlext
unsigned char	Reserved.
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 structure entries is considered fatal (SQLCODE = −19313). During a FETCH statement or a singleton select, SQLWARN3 is set if the number of columns is not equal to the number of host variables (or SQLDA structure entries). The lower of the two is the number of items actually processed. For more information about SQLCA, see Tracking Errors Using the SQLCA Data Structure.


Selectively Bypassing the Creation of Access Plans

To invoke a stored procedure using a static SQL statement, include an EXEC statement before the stored procedure call to bypass the creation of an access plan. If you do not include an EXEC statement before the stored procedure call, another stored procedure is created that invokes the stored procedure called. Using EXEC eliminates this redundancy. For example:

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

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

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

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


Standard DB-Library Cursors

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

Standard cursors use the current database connection. Because they do not require a separate database connection as browse cursors do, standard cursors do not have many of the browse cursor locking problems. Standard cursors also use the cursor functions of Microsoft DB-Library, so they automatically take advantage of any future performance enhancements in DB-Library cursors.

Now see the following:

Defining Cursors

Sensitivity to Change

Holding Locks

Cursors and Stored Procedures


Defining Cursors

Both standard and browse cursors let the user scroll through and update a results set. Although standard cursors require a unique index, they do not require a timestamp or a second connection to a database for updates. They don't create a copy of the entire results set, either. The following table summarizes these differences:

Standard-mode Cursor
Connections to the server	All cursors share the same connection.
Timestamp column	Not required.
Transaction behavior	Cursors share same transaction space. Cannot block each other.
Unique index	Required.
Data currency	Data values 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 a UNION clause.
	Dynamic cursors cannot contain ORDER BY, GROUP BY, or HAVING clauses.
Future direction	Will continue to be enhanced. Restrictions will be removed over time.
Browse-mode Cursor
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 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 tempdb when the cursor is opened.
Restrictions on use of SQL language	Updatable cursors require the FOR BROWSE clause.
Future direction	Will not be changed.


Sensitivity to Change

Two main categories identify cursors according to their sensitivity to change:

Keyset-driven

Values can change, but order and membership in the results set remain fixed at open time (the moment a cursor is opened).

Dynamic

Values, order, and membership in the results set can all change.

Now see the following:

Concurrency Control


Keyset-driven Cursors

In a keyset-driven standard cursor, the membership of rows in the results set and the order are fixed at open time, but changes to values made by the cursor owner and committed changes made by other users are visible. If a change causes a row to no longer qualify for membership or if it affects a row's order, 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.

You specify keyset-driven standard cursors by issuing the SET SCROLLOPTION KEYSET statement. Keyset-driven 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 (one reason a unique index is required). Given the result of N rows, the keyset contains the same N rows that make up the result set. The fetch buffer contains one row, and moves forward through the keysets as each FETCH statement is executed.

Note that the size of the fetch buffer is one row in Embedded SQL for C.

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


Dynamic Cursors

In a dynamic standard cursor, committed changes made by anyone and uncommitted changes 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 cursors cannot use an ORDER BY, GROUP BY, or HAVING clause.

You specify dynamic standard cursors by issuing the SET SCROLLOPTION DYNAMIC statement. This sets the keyset equal to the size of the fetch buffer, which is one row in Embedded SQL for C. Given a result set of N rows, the keyset and the fetch buffer are identical. They both contain a single row, which moves forward through theresult 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 kind of control. To activate the particular concurrency control desired, specify one of the following options 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 issued by the cursor owner are guaranteed to succeed.
	Note that with Microsoft SQL Server for Windows NT version 4.2, locks placed by LOCKCC prevent other users from reading as well as updating the locked data, and the use of BEGIN TRANSACTION and COMMIT TRANSACTION is required to hold the locks. For more information about locking, see Holding Locks.
OPTCC and OPTCCVAL	Fetched rows are not locked, and other users can update or read them freely.
To detect collisions between updates issued by the cursor owner and those 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:

Option	Method of detection
OPTCC	Compares timestamps if available; otherwise, saves and 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 for Windows NT version 4.2, the duration of locks acquired during cursor operations is entirely under the control of the application. In other words, an application that uses SET CONCURRENCY LOCKCC must also issue BEGIN TRANSACTION for the locking to have any effect. To hold the lock on the currently fetched row when LOCKCC is used, the application must issue a BEGIN TRANSACTION 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 also use the catalog stored procedures, which include:

•	sp_databases

•	sp_tables

•	sp_columns

•	sp_stored_procedures

•	sp_sproc_columns

•	sp_indexes

•	sp_pkeys

•	sp_fkeys

•	sp_table_privileges

•	sp_column_privileges

•	sp_server_info

If you're running SQL Server for Windows NT or SQL Server for OS/2® version 4.2, these procedures are installed automatically. If you're running SQL Server for OS/2 version 1.1 or earlier, you must install them by running the INSTCAT.SQL script (found in the \SQL\INSTALL directory).


Browse Cursors

Browse cursors are implemented as separate database connections and therefore are treated as separate users, which raises the possibility of lock conflicts between cursors and UPDATE statements.

Now see the following:

UPDATE or DELETE Statements, Defined

Isolation Levels

Cursors and Lock Conflicts


UPDATE or DELETE Statements, Defined

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 the 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 prevent conflicts with other users. Optimistic concurrency control allows users to share data with less interference than they would experience with locking, the alternative concurrency control method.

Although optimistic concurrency control minimizes the likelihood of conflicts with other users, be sure to write your application so that it can handle updates to tables that are rejected due to lock conflicts or other problems. Use the SQLCODE field in the SQLCA (SQL Communications Area) 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 Tracking Errors Using the SQLCA Data Structure. For more information about browse-mode processing, see Advanced Programming Topics.

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


Isolation Levels

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

If you don't 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 FETCH using a browse cursor. This means that 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 FETCH, using a browse cursor, SQL Server maintains a share lock on every fetched page of the database. This means that no updates are permitted to the fetched data of the result set as long as the cursor is open, no matter what its position is in the table. This 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 result 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 result 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 copy of the results set when the cursor is opened. No locks are placed on the original data; the cursor cannot detect any changes 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 update rows by first locking the rows with the HOLDLOCK option and then opening a cursor using FOR BROWSE.

/* 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 result 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 previous example, there is no conflict between the FOR BROWSE and the HOLDLOCK options. The locks are released when the transaction is committed or rolled back.


Cursors and Lock Conflicts

All updates are issued through a single database connection. This includes updates referencing a cursor (for example, in an UPDATE WHERE CURRENT OF cursor_name statement). Because of this, there is no possibility of lock conflict between updates 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 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 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  Internally, SQL Server 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.


Keywords

The following are reserved keywords for Embedded SQL. Additionally, keywords reserved for Transact-SQL also apply to Embedded SQL.

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


Messages

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

Now see the following:

Embedded SQL for C Messages

DB-Library Messages


Embedded SQL for C Messages

The following messages are generated by Embedded SQL. Because they 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 data type
19423	R	Invalid destination data type
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"
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 dbproc
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 dbproc
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 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

The following error messages, with message numbers in the 10,000 range, are issued by DB-Library.

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 whose datatype 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 may 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 involving data locking or modifications.
10092	SQLCRSUPDTAB EXPROGRAM	Update or insert operations using 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.
The following table lists all error severities 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.