Embedded SQL for C


Changes to Chapter 1   System Requirements
System Requirements

New Platform Support


New Platform Support

In addition to the operating systems previously supported, you can now compile and run applications for the following operating systems:

•	Microsoft Windows NT Workstation version 3.51 or later for
Intel 32-bit x86-based microprocessors (such as the 80486 or the Pentium).

•	Microsoft Windows NT Server version 3.51 or later for Intel 32-bit x86-based microprocessors (such as the 80486 or the Pentium).

•	Microsoft Windows 95.

•	Microsoft Windows for Workgroups 3.11.

•	MS-DOS 6.22

For example, to build a MYPROG.C application for MS-DOS:

sqlprep myprog /prepflags

cl /c /AL myprog.c

link  /NOD myprog.obj,,,sqlakd.lib car.lib rldblib.lig llibce.lib oldnames.lib;


Changes to Chapter 2   ANSI-Compliant Code Changes
ANSI-Compliant Code Changes

The following code changes have been made in Embedded SQL 6.5 for ANSI compliance:

•	Use COMMIT TRANSACTION or COMMIT WORK synonymously in your programs. Previously, only COMMIT TRANSACTION was supported.

•	Use ROLLBACK TRANSACTION or ROLLBACK WORK synonymously in your programs. Previously, only ROLLBACK TRANSACTION was supported.

•	Optionally precede the indicator variable with the INDICATOR keyword when using a host variable and an associated indicator variable. (INDICATOR is now a reserved keyword for Embedded SQL.)

Example

EXEC SQL FETCH YYY INTO :EMPNO1, :HOURS1 INDICATOR :indic1

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

Example

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

•	C language code can be inserted on the same line following the end of an EXEC SQL statement.

Example

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

Note  If your program includes C language code on the same line following the end of an EXEC SQL command, you must use the new /NOLINES precompiler option.


Changes to Chapter 3   New Embedded SQL Statements
New Embedded SQL Statements

SET ANSI_DEFAULTS

SET CURSOR_CLOSE_ON_COMMIT

Changes to Embedded SQL Statements


SET ANSI_DEFAULTS

Sets ANSI defaults ON for the duration of the SQL Server query-processing work session or for the duration of a running trigger or a stored procedure. This statement is supported only when connected to Microsoft SQL Server 6.5 or later.

Syntax

SET ANSI_DEFAULTS ON;

where

ON

Alters the session to conform to ANSI compatibility.

Comments

The ON option has the following effects:

SET CONCURRENCY LOCKCC

SET CURSORTYPE CUR_STANDARD

SET CURSOR_CLOSE_ON_COMMIT ON

SET FETCHBUFFER 1

SET SCROLLOPTION FORWARD

Sends Transact-SQL statement SET ANSI_DEFAULTS ON to SQL Server

Note  The SET ANSI_DEFAULTS OFF statement is not supported. To reverse the effects of a SET ANSI_DEFAULTS ON statement, turn off each option listed above individually.

See Also

SET CONCURRENCY

SET CURSORTYPE

SET CURSOR_CLOSE_ON_COMMIT

SET FETCHBUFFER

SET SCROLLOPTION


SET CURSOR_CLOSE_ON_COMMIT

Sets all cursors on a connection to automatically close when a COMMIT TRANSACTION or a ROLLBACK TRANSACTION statement is issued.

Syntax

SET CURSOR_CLOSE_ON_COMMIT { ON | OFF };

where

ON

Closes all cursors on a connection when a COMMIT TRANSACTION or a ROLLBACK TRANSACTION statement is issued.

OFF (default)

Requires the calling program to close each cursor on a connection when a COMMIT TRANSACTION or a ROLLBACK TRANSACTION statement is issued.

Comments

You can use the SET CURSOR_CLOSE_ON_COMMIT command for both standard- and browse-mode cursors.

See Also

CLOSE

OPEN

SET ANSI_DEFAULTS


Changes to Embedded SQL Statements

Using CONNECT TO

Using DECLARE CURSOR

Using FETCH

Using SET CONCURRENCY

Using SET CURSORTYPE

Using SET FETCHBUFFER

Using SET SCROLLOPTION


Using CONNECT TO

Specify USER $INTEGRATED instead of specifying the login and password parameters to force integrated security for run-time or compile-time connections.


Using DECLARE CURSOR

The following changes have been made to the DECLARE CURSOR statement:

•	Specify INSENSITIVE after the cursor_name parameter. This will create a standard-mode, read-only cursor that is a snapshot of the cursor result set at open time. It is equivalent to the INSENSITIVE option of the Transact-SQL DECLARE CURSOR statement.

•	Specify FOR READ ONLY after the select_stmt parameter. This is equivalent to issuing both the SET CONCURRENCY READONLY and the SET CURSORTYPE CUR_STANDARD statements. Using the FOR READ ONLY option overrides the SET CONCURRENCY statement.

Note  Cursors are updatable by default. Therefore the SELECT statement does not require a FOR UPDATE option. However, if the SELECT statement contains the FOR UPDATE option, the effect is equivalent to issuing both the SET CONCURRENCY LOCKCC and the SET CURSORTYPE CUR_STANDARD statements. Using the FOR UPDATE option overrides the SET CONCURRENCY statement.

•	Specify SCROLL after the cursor_name parameter. This is equivalent to issuing the SET CURSORTYPE CUR_STANDARD and SET SCROLLOPTION KEYSET statements and allows first, last, and backwards fetch operations.

•	Optionally the cursor_name parameter can contain cursor names in quotation marks.


Using FETCH

Optionally use the NEXT, PRIOR, FIRST, or LAST option prior to the cursor_name parameter when retrieving a row from a cursor.

The NEXT option returns the first row of the results set if this is the first fetch against the cursor; otherwise, it moves the cursor one row within the results set. NEXT is the default method used to move through a results set.

The PRIOR option returns the previous row within the results set.

The FIRST option moves the cursor to the first row within the results set and returns the first row.

The LAST option moves the cursor to the last row within the results set and returns the last row.

Previously, the FETCH statement returned the first row of the results set during the first fetch from the cursor, and only moved the cursor to the next row during subsequent fetch operations.

Note  The PRIOR, FIRST, and LAST options are available only with cursors defined by using the SCROLL option.


Using SET CONCURRENCY

If ANSI_DEFAULTS is not set ON, OPTCC is the default setting. If ANSI_DEFAULTS is set ON, LOCKCC is the default setting.


Using SET CURSORTYPE

CUR_STANDARD is the default cursor setting instead of CUR_BROWSE.


Using SET FETCHBUFFER

ANSI_DEFAULTS is not set ON, by default, and the numrows parameter is set to 10. If ANSI_DEFAULTS is set ON, the numrows parameter is set to 1.

The number of fetch operations required to retrieve all data from a cursor can be reduced by setting the numrows parameter higher than the default setting of 10. You should ensure that the client has enough available memory for you to increase this setting. You might need to reduce the default setting of the numrows parameter if client memory is constrained or if the cursors have many columns or very wide columns.


Using SET SCROLLOPTION

FORWARD is the default scrolling setting instead of KEYSET.


Changes to Chapter 4   New Precompiler Options
New Precompiler Options

The precompiler supports the following new options:

/FLAGGER

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

The nsqlprep syntax using the /FLAGGER option is as follows:

nsqlprep /FLAGGER level

where

level is one of the following:

ENTRY

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

NONE

Specifies that the static SQL sections are not checked for FIPS 127-2 SQL compliance. Static SQL sections are still checked for correct syntax and the existence of any database objects they reference.

/NOLINES

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


Changes to Precompiler Options

The /PASS option supports $INTEGRATED for the login[.password] parameter so that the precompiler can use integrated security regardless of the server's security mode. The compile-time connection is force integrated. Any implicit run-time connection is also force integrated.

The 8-character restriction no longer applies to the name parameter when using the /PLAN option to specify a login id for non-database owners who compile a program.


Changes to Bind Files

To run a .BND file as a SQL script, each line must contain no more than 1,000 characters.


Changes to Appendix A   New SQLCA Data Structure
New SQLCA Data Structure

The information about the SQLCA data structure has changed as follows:

Embedded SQL now tracks runtime errors that generate SQLSTATE codes. Some of these errors are returned from SQL Server. Others are returned from Embedded SQL and DB-Library.

To use SQLSTATE code in your Embedded SQL for C program, declare a SQLSTATE host variable as follows:

EXEC SQL BEGIN DECLARE SECTION;

char[6] SQLSTATE;

EXEC SQL END DECLARE SECTION;

Your SQLSTATE host variable will be set after each EXEC SQL statement.

Internally, SQLSTATE codes are tracked in a new member variable of the SQLCA structure. The following shows the new SQLCA structure:

// SQL Communication Area - SQLCA
typedef struct tag_sqlca
{
unsigned char
sqlcaid[EYECATCH_LEN];	// Eyecatcher = 'SQLCA   '
long sqlcabc;	// SQLCA size in bytes = 136
long sqlcode;	// SQL return code
short sqlerrml;	// Length for SQLERRMC
unsigned char
  sqlerrmc[SQLERRMC_SIZ];	// Error message tokens
unsigned char sqlerrp[8];	// Diagnostic information
long sqlerrd[6];	// Diagnostic information
unsigned char sqlwarn[8];	// Warning flags
unsigned char sqlext[3];	// Reserved, was 8 bytes long
unsigned char sqlstate[5];	// new member

} SQLCA, *PSQLCA, far *LPSQLCA;


Changes to Appendix E   New SQLSTATE Messages
New SQLSTATE Messages

Embedded SQL and SQL Server now generate SQLSTATE messages that are ANSI-standard SQLSTATE messages.


Changes to SQLCODE values

The following SQLCODE values have changed:

Condition	New SQLCODE	Old SQLCODE
Singleton select returns no rows	-1	1
NULL value returns, but no indicator variable declared	-1	0
Second attempt to open cursor without corresponding close while CLOSE_ON_COMMIT is in force	-1	0
Server error encountered on cursor open	- < server message number >	-