Informix Guide to SQL: Syntax

Informix Guide to SQL: Syntax
Chapter 1: SQL Statements

Home Contents Index Master Index New Book

SQL Statements

FETCH

Use the FETCH statement to move a cursor to a new row in the active set and to retrieve the row values from memory.

Syntax

(1 of 2)
Element	Purpose	Restrictions	Syntax
cursor id	Identifier for a select or function cursor from which rows are to be retrieved	A `DECLARE` statement must have previously created the cursor and the `OPEN` statement must have previously open it.	Identifier, p. 1-966
cursor variable	Host variable that holds the value of cursor id	The host variable must be a character data type. The cursor identified in cursor variable must have been created in an earlier DECLARE statement and opened in an earlier OPEN statement.	Variable name must conform to language-specific rules for variable names.
data structure	Structure that has been declared as a host variable	The individual members of the data structure must be matched appropriately to the type of values that are being fetched. If you use a program array, you must list both the array name and a specific element of the array in data structure.	Data-structure name must conform to language-specific rules for data-structure names.
data variable	Host variable that receives one value from the fetched row	The host variable must have a data type that is appropriate for the value that is fetched into it.	Variable name must conform to language-specific rules for variable names.
descriptor	Quoted string that identifies the system-descriptor area into which you fetch the contents of a row	The system-descriptor area must have been allocated with the `ALLOCATE DESCRIPTOR` statement.	Quoted String, p. 1-1014
descriptor variable	Host variable name that holds the value of descriptor	The system-descriptor area that is identified in descriptor variable must have been allocated with the `ALLOCATE DESCRIPTOR` statement.	Variable name must conform to language-specific rules for variable names.
indicator variable	Host variable that receives a return code if null data is placed in the corresponding data variable	This parameter is optional, but use an indicator variable if the possibility exists that the value of data variable is null. If you specify the indicator variable without the `INDICATOR` keyword, you cannot put a space between data variable and indicator variable. The rules for placing a prefix before indicator variable are language-specific. See your `SQL API` manual for further information on indicator variables. Variable cannot be a `DATETIME` or `INTERVAL` data type.	Variable name must conform to language-specific rules for variable names.
row position	Integer value or host variable that contains an integer value. The integer value gives the position of the desired row in the active set of rows. See "FETCH with a Scroll Cursor" for a discussion of the `RELATIVE` and `ABSOLUTE` keywords and the meaning of row position with each keyword.	A value of `0` for row position is allowed with the `RELATIVE` keyword. A value of `0` fetches the current row. The value of row position must be `1` or higher with the `ABSOLUTE` keyword.	If you are using a host variable, variable name must conform to language-specific rules for variable names. If you are using a literal number, see Literal Number, p. 1-1001.
sqlda pointer	Pointer to an sqlda structure that receives the values from the fetched row	You cannot begin an sqlda pointer with a dollar sign ($) or a colon (:).	See the discussion of sqlda structure in the `INFORMIX`-`ESQL/C` `P`rogrammer's `M`anual.

Usage

The FETCH statement is one of four statements that are used for queries that return more than one row from the database. The four statements, DECLARE, OPEN, FETCH, and CLOSE, are used in the following sequence:

1. Declare a select or function cursor to control the active set of rows.

2. Open the cursor to begin execution of the query.

3. Fetch from the cursor to retrieve the contents of each row.

4. Close the cursor to break the association between the cursor and the active set.

You can declare a select or function cursor with either of the following cursor characteristics: a sequential cursor or a scroll cursor. The way the database server creates and stores members of the active set and then fetches rows from the active set differs depending on whether the cursor is a sequential cursor or a scroll cursor. (For more information, see "Cursor Characteristics" in the DECLARE statement)

X/O

In X/Open mode, if a cursor-direction value (such as NEXT or RELATIVE) is specified, a warning message is issued, indicating that the statement does not conform to X/Open standards.

FETCH with a Sequential Cursor

A sequential select or function cursor can fetch only the next row in sequence from the active set. The sole cursor-direction option that is available to a sequential cursor is the default value, NEXT. A sequential cursor can read through a table only once each time it is opened. The following example in INFORMIX-ESQL/C illustrates a FETCH statement with a sequential cursor:

EXEC SQL fetch seq_curs into :fname, :lname;

When the program opens a sequential cursor, the database server processes the query to the point of locating or constructing the first row of data. The goal of the database server is to tie up as few resources as possible.

Because the sequential cursor can retrieve only the next row, the database server can frequently create the active set one row at a time. On each FETCH operation, the database server returns the contents of the current row and locates the next row. This one-row-at-a-time strategy is not possible if the database server must create the entire active set to determine which row is the first row (as would be the case if the SELECT statement included an ORDER BY clause).

FETCH with a Scroll Cursor

A scroll select or function cursor can fetch any row in the active set, either by specifying an absolute row position or a relative offset. Use the following cursor-position options to specify a particular row that you want to retrieve.


Keyword	Effect
`NEXT`	Retrieves the next row in the active set.
`PREVIOUS`	Retrieves the previous row in the active set.
`PRIOR`	Is synonymous with `PREVIOUS`; it retrieves the previous row in the active set.
`FIRST`	Retrieves the first row in the active set.
`LAST`	Retrieves the last row in the active set.
`CURRENT`	Retrieves the current row in the active set (the same row as returned by the preceding `FETCH` statement from the scroll cursor).
`RELATIVE`	Retrieves the nth row, relative to the current cursor position in the active set, where row position supplies n. A negative value indicates the nth row prior to the current cursor position. If row position is `0`, the current row is fetched.
`ABSOLUTE`	Retrieves the nth row in the active set, where row position supplies n. Absolute row positions are numbered from 1.

The following INFORMIX-ESQL/C examples illustrate a FETCH statement with a scroll cursor:

EXEC SQL fetch  previous  q_curs  into  :orders;



EXEC SQL fetch  last  q_curs  into  :orders;



EXEC SQL fetch  relative  -10  q_curs  into  :orders;



printf("Which  row? ");

scanf("%d",row_num); 

EXEC SQL fetch  absolute  :row_num  q_curs  into  :orders;

EXEC SQL  declare ord_date  cursor for

	select  order_num,  order_date,  po_num

		into  :o_num,  :o_date,  :o_po FROM orders;

EXEC SQL open  ord_date;

EXEC SQL fetch  next  ord_date;

Use an indicator variable if the data that is returned from the SELECT statement might be null. See your SQL API manual for more information about indicator variables.

If you prepare a SELECT statement, the SELECT cannot include the INTO clause so you must use the INTO clause of the FETCH statement. For more information, see page 1-418.

INTO Clause of EXECUTE FUNCTION

When you associate an EXECUTE FUNCTION statement with the cursor (a function cursor), the EXECUTE FUNCTION can contain an INTO clause to specify the program variables that are to receive the return values. In this case, the FETCH statement cannot contain an INTO clause. You can use this method only when the EXECUTE FUNCTION statement is written as part of the declaration of a cursor (see the DECLARE statement on page 1-303).

Tip: In earlier versions of Informix products, you could use the EXECUTE PROCEDURE statement to execute a stored procedure that returned values (an SPL function). For backward compatibility, the EXECUTE PROCEDURE statement still supports the INTO clause to receive return values. However, Informix recommends that new SPL functions use EXECUTE FUNCTION and the INTO clause. For more information, see "EXECUTE PROCEDURE".

The following example uses the INTO clause of the EXECUTE FUNCTION statement to specify program variables in INFORMIX-ESQL/C:

EXEC SQL  declare ord_date  cursor for

	execute function func1(20)

		into  :o_num,  :o_date,  :o_po;

EXEC SQL open  ord_date;

EXEC SQL fetch  next  ord_date;

Use an indicator variable if the data that is returned from the EXECUTE FUNCTION statement might be null. See INFORMIX-ESQL/C Programmer's Manual for more information about indicator variables.

INTO Clause of FETCH

When the SELECT or EXECUTE FUNCTION statement omits the INTO clause, you must specify the destination of the data whenever a row is fetched. For example, to dynamically execute a SELECT or EXECUTE FUNCTION statement, the SELECT or EXECUTE FUNCTION cannot include its INTO clause in the PREPARE statement. Therefore, the FETCH statement must include an INTO clause to retrieve data into a set of variables. This method lets you store different rows in different memory locations.

In the following INFORMIX-ESQL/C example, a series of complete rows is fetched into a program array. The INTO clause of each FETCH statement specifies an array element as well as the array name.

EXEC SQL BEGIN DECLARE SECTION;

	char wanted_state[2];

	short int row_count = 0;

	struct customer_t{

	{

		int    c_no;

		char   fname[15];

		char   lname[15];

	} cust_rec[100];

EXEC SQL END DECLARE SECTION;



main()

{

	EXEC SQL connect to'stores7';

	printf("Enter 2-letter state code: ");

	scanf ("%s", wanted_state);



	EXEC SQL declare cust cursor for 

		select * from customer where state = :wanted_state;



	EXEC SQL open cust;



	EXEC SQL fetch cust into :cust_rec[row_count];

	while (SQLCODE == 0)

	{

		printf("\n%s %s", cust_rec[row_count].fname,

			cust_rec[row_count].lname);



		row_count++;

		EXEC SQL fetch cust into :cust_rec[row_count];

	}

	printf ("\n");

	EXEC SQL close cust;

	EXEC SQL free cust;

}

You can fetch into a program-array element only by using an INTO clause in the FETCH statement. When you are declaring a cursor, do not refer to an array element within the SQL statement.

Using a System-Descriptor Area

If you do not know the number of return values or their data types that a SELECT or EXECUTE FUNCTION statement returns at runtime, you can store output values in a system-descriptor area. A system-descriptor area describes the data type and memory location of one or more return values.

X/O

You can also use an sqlda structure to dynamically supply parameters (page 1-420). However, a system-descriptor area conforms to the X/Open standards.

Tip: If you are certain of the number and data type of values in the select list, you can use an INTO clause in the FETCH statement. For more information, see page 1-418.

To specify a system-descriptor area as the location of output values, use the USING SQL DESCRIPTOR clause of the FETCH statement. This clause introduces the name of the system-descriptor area into which you fetch the contents of a row or the return values of a user-defined function. You can then use the GET DESCRIPTOR statement to transfer the values that the FETCH statement returns from the system-descriptor area into host variables.

The following example shows the FETCH USING SQL DESCRIPTOR statement:

EXEC SQL allocate descriptor 'desc';

...

EXEC SQL declare selcurs cursor for 

	select * from customer where state = 'CA';

EXEC SQL describe selcurs using sql descriptor 'desc';

EXEC SQL open selcurs;

while (1)

	{

	EXEC SQL fetch selcurs using sql descriptor 'desc';

	...

The COUNT field in the system-descriptor area corresponds to the number of return values of the prepared statement. The value of COUNT must be less than or equal to the value of the occurrences that were specified when the system-descriptor area was allocated with the ALLOCATE DESCRIPTOR statement. You can obtain the value of a field with the GET DESCRIPTOR statement and set the value with the SET DESCRIPTOR statement.

For more information on how to use a system-descriptor area, see the INFORMIX-ESQL/C Programmer's Manual.

Using an sqlda Structure

If you do not know the number of return values or their data types that a SELECT or EXECUTE FUNCTION statement returns at runtime, you can store output values in an sqlda structure. An sqlda structure lists the data type and memory location of one or more return values.

1. Declare an sqlda pointer variable.

2. Use the DESCRIBE statement to fill in the sqlda structure.

3. Allocate memory to hold the data values.

4. Use the USING DESCRIPTOR clause of the FETCH statement to name the sqlda structure as the location into which you fetch the return values.

The following example shows a FETCH USING DESCRIPTOR statement:

struct sqlda *sqlda_ptr;

...

EXEC SQL declare selcurs2 cursor for 

	select * from customer where state = 'CA';

EXEC SQL describe selcurs2 into sqlda_ptr;

...

EXEC SQL open selcurs2;

while (1)

	{

	EXEC SQL fetch selcurs2 using descriptor sqlda_ptr;

	...

The sqld value specifies the number of output values that are described in occurrences of the sqlvar structures of the sqlda structure. This number must correspond to the number of return values from the prepared statement. For further information, refer to the sqlda discussion in the INFORMIX-ESQL/C Programmer's Manual.

Fetching a Row for Update

The FETCH statement does not ordinarily lock a row that is fetched. Thus, another process can modify (update or delete) the fetched row immediately after your program receives it. A fetched row is locked in the following cases:

When you set the isolation level to Repeatable Read, each row you fetch is locked with a read lock to keep it from changing until the cursor closes or the current transaction ends. Other programs can also read the locked rows.
When you set the isolation level to Cursor Stability, the current row is locked.

ANSI
In an ANSI-compliant database, an isolation level of Repeatable Read is the default; you can set it to something else.
When you are fetching through an update cursor (one that is declared FOR UPDATE), each row you fetch is locked with a promotable lock. Other programs can read the locked row, but no other program can place a promotable or write lock; therefore, the row is unchanged if another user tries to modify it using the WHERE CURRENT OF clause of UPDATE or DELETE statement.

When you modify a row, the lock is upgraded to a write lock and remains until the cursor is closed or the transaction ends. If you do not modify it, the lock might or might not be released when you fetch another row, depending on the isolation level you have set. The lock on an unchanged row is released as soon as another row is fetched, unless you are using Repeatable Read isolation (see the SET ISOLATION statement on page 1-722).

Important: You can hold locks on additional rows even when Repeatable Read isolation is not in use or is unavailable. Update the row with unchanged data to hold it locked while your program is reading other rows. You must evaluate the effect of this technique on performance in the context of your application, and you must be aware of the increased potential for deadlock.

When you use explicit transactions, be sure that a row is both fetched and modified within a single transaction; that is, both the FETCH statement and the subsequent UPDATE or DELETE statement must fall between a BEGIN WORK statement and the next COMMIT WORK statement.

Fetching From a Collection Cursor

A collection cursor allows you to access the individual elements of an ESQL/C collection variable. To declare a collection cursor, use the DECLARE statement and include the Collection Derived Table segment in the SELECT statement that you associate with the cursor. Once you open the collection cursor with the OPEN statement, the cursor allows you to access the elements of the collection variable.

For more information, see the Collection Derived Table segment on page 1-831. For more information on how to declare a collection cursor for a SELECT statement, see "A Select Cursor for a Collection Variable".

To fetch elements, one at a time, from a collection cursor, use the FETCH statement and the INTO clause. The FETCH statement identifies the collection cursor that is associated with the collection variable. The INTO clause identifies the host variable that holds the element value that is fetched from the collection cursor. The data type of the host variable in the INTO clause must match the element type of the collection.

Suppose you have a table called children with the following structure:

CREATE TABLE children

(
	age			SMALLINT,
	name			VARCHAR(30),
	fav_colors			SET(VARCHAR(20) NOT NULL),
)

The following ESQL/C code fragment shows how to fetch elements from the child_colors collection variable:

EXEC SQL BEGIN DECLARE SECTION;

	client collection child_colors;

	varchar one_favorite[21];

	char child_name[31] = "marybeth";

EXEC SQL END DECLARE SECTION;

EXEC SQL allocate collection :child_colors;

/* Get structure of fav_colors column for untyped 

  * child_colors collection variable */

EXEC SQL select fav_colors into :child_colors

	from children

	where name = :child_name;

/* Declare select cursor for child_colors collection

  * variable */

EXEC SQL declare colors_curs cursor for 

	select * from table(:child_colors);

EXEC SQL open colors_curs;

do

{

	EXEC SQL fetch colors_curs into :one_favorite;

	...

} while (SQLCODE == 0)

EXEC SQL close colors_curs;

EXEC SQL free colors_curs;

EXEC SQL deallocate collection :child_colors;

Once you have fetched a collection element, you can modify the element with the UPDATE or DELETE statements. For more information, see the UPDATE and DELETE statements in this manual. You can also insert new elements into the collection variable with an INSERT statement. For more information, see the INSERT statement.

Important: The collection variable stores the elements of the collection. However, it has no intrinsic connection with a database column. Once the collection variable contains the correct elements, you must then save the variable into the collection column with the INSERT or UPDATE statement.

Checking the Result of FETCH

You can use the SQLSTATE variable to check the result of each FETCH statement. The database server sets the SQLSTATE variable after each SQL statement. If a row is returned successfully, the SQLSTATE variable contains the value '00000'. If no row is found, the database server sets the SQLSTATE code to '02000', which indicates no data found, and the current row is unchanged. The following conditions set the SQLSTATE code to '02000', indicating no data found:

The active set contains no rows.
You issue a FETCH NEXT statement when the cursor points to the last row in the active set or points past it.
You issue a FETCH PRIOR or FETCH PREVIOUS statement when the cursor points to the first row in the active set.
You issue a FETCH RELATIVE n statement when no nth row exists in the active set.
You issue a FETCH ABSOLUTE n statement when no nth row exists in the active set.

The database server copies the SQLSTATE code from the RETURNED_SQLSTATE field of the system-diagnostics area. You can use the GET DIAGNOSTICS statement to examine the RETURNED_SQLSTATE field directly. The system-diagnostics area can also contain additional error information. See the GET DIAGNOSTICS statement in this manual for more information.

Tip: When you encounter an SQLSTATE (sqlca.sqlcode) error, a corresponding SQLCODE error might also exist. The SQLCODE variable contains the
Informix-specific error code. For more information about SQLCODE, see the "Informix Guide to SQL: Tutorial" and the "INFORMIX-ESQL/C Programmer's Manual."

References

See the ALLOCATE DESCRIPTOR, CLOSE, DEALLOCATE DESCRIPTOR, DECLARE, DESCRIBE, GET DESCRIPTOR, OPEN, PREPARE, and SET DESCRIPTOR statements in this manual for further information about using the FETCH statement with dynamic management statements.

In the Informix Guide to SQL: Tutorial, see the discussion of the FETCH statement in Chapter 5.

For further information about error checking, the system-descriptor area and the sqlda structure, see INFORMIX-ESQL/C Programmer's Manual.

FLUSH

Use the FLUSH statement to force rows that a PUT statement buffered to be written to the database.

Syntax

Element Purpose Restrictions Syntax
cursor id
Identifier for a cursor
A `DECLARE` statement must have previously created the cursor.
Identifier, p. 1-966

cursor variable
Host variable that identifies a cursor
Host variable must be a character data type. A `DECLARE` statement must have previously created the cursor.
Variable name must conform to language-specific rules for variable names.

Usage

The PUT statement adds a row to a buffer, and the buffer is written to the database when it is full. Use the FLUSH statement to force the insertion when the buffer is not full.

If the program terminates without closing the cursor, the buffer is left unflushed. Rows placed into the buffer since the last flush are lost. Do not expect the end of the program to close the cursor and flush the buffer.

The following example shows a FLUSH statement:

FLUSH  icurs

Error Checking FLUSH Statements

The sqlca structure contains information on the success of each FLUSH statement and the number of rows that are inserted successfully. The result of each FLUSH statement is contained in the SQLCODE variable (sqlca.sqlcode) and the sqlerrd[2] field of the sqlca structure.

When you use data buffering with an insert cursor, you do not discover errors until the buffer is flushed. For example, an input value that is incompatible with the data type of the column for which it is intended is discovered only when the buffer is flushed. When an error is discovered, rows in the buffer that are located after the error are not inserted; they are lost from memory.

The SQLCODE field is set either to an error code or to zero if no error occurs. The third element of the sqlerrd array is set to the number of rows that are successfully inserted into the database:

If a block of rows is successfully inserted into the database, SQLCODE is set to zero and sqlerrd to the count of rows.
If an error occurs while the FLUSH statement is inserting a block of rows, SQLCODE shows which error, and sqlerrd[2] contains the number of rows that were successfully inserted. (Uninserted rows are discarded from the buffer.)

Tip: When you encounter an SQLCODE error, a corresponding SQLSTATE error value might exist. Check the GET DIAGNOSTICS statement for information about getting the SQLSTATE value and using the GET DIAGNOSTICS statement to interpret the SQLSTATE value.

Counting Total and Pending Rows

To count the number of rows actually inserted into the database as well as the number not yet inserted, perform the following steps:

1. Prepare two integer variables, such as total and pending.

2. When the cursor opens, set both variables to 0.

3. Each time a PUT statement executes, increment both total and pending.

4. Whenever a FLUSH statement executes or the cursor is closed, subtract the third field of the SQLERRD array from pending.

References

See the CLOSE, DECLARE, OPEN, and PUT statements in this manual.

For information about the sqlca structure, see the INFORMIX-ESQL/C Programmer's Manual.

In the Informix Guide to SQL: Tutorial, see the discussion of FLUSH in Chapter 6.

FREE

The FREE statement releases resources that are allocated to a prepared statement or to a cursor.

Syntax

(1 of 2)

Element Purpose Restrictions Syntax
cursor id
Identifier for a cursor
A `DECLARE` statement must have previously created the cursor.
Identifier, p. 1-966

cursor variable
Host variable that identifies a cursor
Variable must be a character data type. Cursor must have been previously created by a `DECLARE` statement.
Variable name must conform to language-specific rules for variable names

statement id
Identifier for an `SQL` statement
The statement identifier must be defined in a previous `PREPARE` statement. After you release the database-server resources, you cannot use the statement identifier with a `DECLARE` cursor or with the `EXECUTE` statement until you prepare the statement again.
`PREPARE`, p. 1-541

statement id variable
A host variable that identifies an `SQL` statement
This variable must be defined in a previous `PREPARE` statement. Variable must be a character data type.
`PREPARE`, p. 1-541

Usage

The FREE statement releases the resources that were allocated for a prepared statement or a declared cursor in the application-development tool and the database server. Resources are allocated when you prepare a statement or when you open a cursor (see the DECLARE and OPEN statements on pages 1-303 and 1-528, respectively.)

The amount of available memory in the system limits the total number of open cursors and prepared statements that are allowed at one time in one process. Use FREE statement id or FREE statement id variable to release the resources that a prepared statement holds; use FREE cursor id or FREE cursor variable to release resources that a cursor holds.

Freeing a Statement

If you prepared a statement (but did not declare a cursor for it), FREE statement id (or statement id variable) releases the resources in both the application development tool and the database server.

If you declared a cursor for a prepared statement, FREE statement id (or statement id variable) releases only the resources in the application development tool; the cursor can still be used. The resources in the database server are released only when you free the cursor.

After you free a statement, you cannot execute it or declare a cursor for it until you prepare it again.

The following INFORMIX-ESQL/C example shows the sequence of statements that is used to free an implicitly prepared statement:

EXEC SQL prepare  sel_stmt from 'select  *  from  orders';

.

.

.

EXEC SQL free  sel_stmt;

The following INFORMIX-ESQL/C example shows the sequence of statements that are used to release the resources of an explicitly prepared statement. The first FREE statement in this example frees the cursor. The second FREE statement in this example frees the prepared statement.

sprintf(demoselect, "%s %s",

	"select  *  from  customer  ",

	"where  customer_num  between  100  and  200");

EXEC SQL prepare  sel_stmt  from :demoselect;

EXEC SQL declare  sel_curs  cursor  for  sel_stmt;

EXEC SQL open  sel_curs;

  .

  . 

  .

EXEC SQL close  sel_curs;

EXEC SQL free sel_curs;

EXEC SQL free sel_stmt;

Freeing a Cursor

If you declared a cursor for a prepared statement, freeing the cursor releases only the resources in the database server. To release the resources for the statement in the application-development tool, use FREE statement id (or statement id variable).

If a cursor is not declared for a prepared statement, freeing the cursor releases the resources in both the application-development tool and the database server.

After a cursor is freed, it cannot be opened until it is declared again. The cursor should be explicitly closed before it is freed.

For an example of a FREE statement that frees a cursor, see the second example in "Freeing a Statement".

References

See the CLOSE, DECLARE, EXECUTE, EXECUTE IMMEDIATE, and PREPARE statements in this manual.

In the Informix Guide to SQL: Tutorial, see the discussion of the FREE statement in Chapter 5.

GET DESCRIPTOR

Use the GET DESCRIPTOR statement to obtain values from a system-descriptor area.

Syntax


Element	Purpose	Restrictions	Syntax
descriptor	A quoted string that identifies a system-descriptor area from which information is to be obtained	The system-descriptor area must have been allocated in an `ALLOCATE DESCRIPTOR` statement.	Quoted String, p. 1-1014
descriptor variable	An embedded variable name that holds the value of descriptor	The system-descriptor area identified in descriptor variable must have been allocated in an `ALLOCATE DESCRIPTOR` statement.	The name of the embedded variable must conform to language-specific rules for variable names.
field host variable	The name of a host variable that receives the contents of the specified field from the system-descriptor area	The field host variable must be an appropriate type to receive the value of the specified field from the system-descriptor area	The name of the field host variable must conform to language-specific rules for variable names.
host variable	The name of a host variable that indicates how many values are described in the system-descriptor area	The host variable must be an integer data type.	The name of the host variable must conform to language-specific rules for variable names.
item number	An unsigned integer that represents one of the occurrences (item descriptors) in the system-descriptor area	The value of item number must be greater than zero and less than the number of occurrences that were specified when the system- descriptor area was allocated with the `ALLOCATE DESCRIPTOR` statement.	Literal Number, p. 1-1001
item number variable	The name of a host variable that holds the value of item number	The item number variable must be an integer data type.	The name of the item number variable must conform to language-specific rules for variable names.

Usage

The GET DESCRIPTOR statement can be used after you have described SELECT, EXECUTE FUNCTION, and INSERT statements with the DESCRIBE...USING SQL DESCRIPTOR statement. The GET DESCRIPTOR statement can obtain values from a system-descriptor area in the following instances:

Determine how many values are described in a system-descriptor area by retrieving the value in the COUNT field.
Determine the characteristics of each column or expression that is described in the system-descriptor area.
Copy a value from the system-descriptor area into a host variable after a FETCH statement.

If an error occurs during the assignment to any identified host variable, the contents of the host variable are undefined. The host variables that are used in the GET DESCRIPTOR statement must be declared in the INFORMIX-ESQL/C program. See the INFORMIX-ESQL/C Programmer's Manual for information on the role and contents of each field in the system-descriptor area and on how to declare host variables.

Using the COUNT Keyword

Use the COUNT keyword to determine how many values are described in the system-descriptor area. The following INFORMIX-ESQL/C example shows how to use a GET DESCRIPTOR statement with a host variable to determine how many values are described in the system-descriptor area called desc1:

main()

{

EXEC SQL BEGIN DECLARE SECTION;

	int  h_count;

EXEC SQL END DECLARE SECTION;



EXEC SQL allocate descriptor 'desc1' with max occurrences 20;



/* This section of program would prepare a SELECT or INSERT 

 * statement into the s_id statement id.

*/

EXEC SQL describe s_id using sql descriptor 'desc1';



EXEC SQL get  descriptor 'desc1' :h_count = count;

...

}

VALUE Clause

Use the VALUE clause to obtain information about a described column or expression or to retrieve values that the database server returns in a system-descriptor area. You can modify values for items after you use the DESCRIBE statement to fill the fields for a SELECT, EXECUTE FUNCTION, or INSERT statement, or you can obtain values for items for which you are providing a description (such as parameters in a WHERE clause).

The item number must be greater than zero and less than the number of occurrences that were specified when you allocated the system-descriptor area with the ALLOCATE DESCRIPTOR statement.

Using the VALUE Clause After a DESCRIBE

After you describe a SELECT, EXECUTE FUNCTION, or INSERT statement with the DESCRIBE...USING SQL DESCRIPTOR statement, the characteristics of each column or expression in the select list of the SELECT statement, the characteristics of the values returned by the EXECUTE FUNCTION statement, or the characteristics of each column in the INSERT statement are returned to the system-descriptor area. Each value in the system-descriptor area describes the characteristics of one returned column or expression. Each field and its possible contents are described in the INFORMIX-ESQL/C Programmer's Manual.

The following INFORMIX-ESQL/C example shows how to use a GET DESCRIPTOR statement to obtain data type information from the demodesc system-descriptor area:

EXEC SQL get descriptor 'demodesc' value :index

	    :type = TYPE,

	    :len = LENGTH,

	    :name = NAME;

printf("Column %d: type = %d, len = %d, name = %s\n",

		index, type, len, name);

The value that the database server returns into the TYPE field is a defined integer. To evaluate the data type that is returned, test for a specific integer value. The codes for the TYPE field are listed in the description of the SET DESCRIPTOR statement on page 1-702.

X/O

In X/Open mode, the X/Open code is returned to the TYPE field. You cannot mix the two modes because errors can result. For example, if a particular data type is not defined under X/Open mode but is defined for Informix products, executing a GET DESCRIPTOR statement can result in an error.

In X/Open mode, a warning message appears if ILENGTH, DATA, or ITYPE is used. It indicates that these fields are not standard X/Open fields for a system-descriptor area.

For more information about TYPE, ILENGTH, IDATA, and ITYPE, see the dynamic management chapter in the INFORMIX-ESQL/C Programmer's Manual. For more information about programming in X/Open mode, see the preprocessing and compilation syntax in the appropriate Informix SQL API programmer's manual.

If the TYPE field for a fetched value is DECIMAL or MONEY, the database server returns the precision and scale information for a column into the PRECISION and SCALE fields after a DESCRIBE statement is executed. If the TYPE is not DECIMAL or MONEY, the SCALE and PRECISION fields are undefined.

Using the VALUE Clause After a FETCH

Each time your program fetches a row, it must copy the fetched value into host variables so that the data can be used. To accomplish this task, use a GET DESCRIPTOR statement after each fetch of each value in the select list. If three values exist in the select list, you need to use three GET DESCRIPTOR statements after each fetch (assuming you want to read all three values). The item numbers for each of the three GET DESCRIPTOR statements are 1, 2, and 3.

The following INFORMIX-ESQL/C example shows how you can copy data from the DATA field into a host variable (result) after a fetch. For this example, it is predetermined that all returned values are the same data type.

EXEC SQL get  descriptor 'demodesc' :desc_count = count;

.

.

.

EXEC SQL fetch democursor using sql descriptor 'demodesc';

for (i = 1; i <= desc_count; i++)

	{

	if (sqlca.sqlcode != 0) break;

	EXEC SQL get descriptor 'demodesc' value :i :result = DATA;

	printf("%s ", result);

	}

printf("\n");

Fetching a Null Value

When you use GET DESCRIPTOR after a fetch, and the fetched value is null, the INDICATOR field is set to -1 (NULL). The value of DATA is undefined if INDICATOR indicates a null value. The host variable into which DATA is copied has an unpredictable value.

Using LENGTH or ILENGTH

If your DATA or IDATA field contains a character string, you must specify a value for LENGTH. If you specify LENGTH=0, LENGTH is automatically set to the maximum length of the string. The DATA or IDATA field might contain a literal character string or a character string that is derived from a character variable of CHAR or VARCHAR data type. This provides a method to determine dynamically the length of a string in the DATA or IDATA field.

If a DESCRIBE statement precedes a GET DESCRIPTOR statement, LENGTH is automatically set to the maximum length of the character field that is specified in your table.

This information is identical for ILENGTH. Use ILENGTH when you create a dynamic program that does not comply with the X/Open standard.

Describing an Opaque-Type Column

The DESCRIBE statement sets the following item-descriptor fields when the column to fetch has an opaque type as its data type:

The EXTYPEID field stores the extended ID for the opaque type.

extended_id

sysxtdtypes

The EXTYPENAME field stores the name of the opaque type.

name

extended_id

sysxtdtypes

The EXTYPELENGTH field stores the length of the opaque-type name.

This integer value is the length, in bytes, of the name of the opaque type.

The EXTYPEOWNERNAME field stores the name of the opaque-type owner.

owner

extended_id

sysxtdtypes

The EXTYPEOWNERLENGTH field stores the length of the value in the EXTTYPEOWNERNAME field.

This integer value is the length, in bytes, of the owner name for the opaque type.

Use these field names with the GET DESCRIPTOR statement to obtain information about an opaque column. For more information on the sysxtdtypes system catalog table, see Chapter 1 of the Informix Guide to SQL: Syntax.

Describing a Distinct-Type Column

The DESCRIBE statement sets the following item-descriptor fields when the column to fetch has an distinct type as its data type:

The SOURCEID field stores the extended identifier for the source data type.

source

sysxtdtypes

extended_id

The SOURCETYPE field stores the data-type constant for the source data type.

This value is the data-type constant (from the sqltypes.h file) for the data type of the source type for the distinct type. The codes for the SOURCETYPE field are listed in the description of the TYPE field in the SET DESCRIPTOR statement (

page 1-702

type

sysxtdtypes

extended_id

Use these field names with the GET DESCRIPTOR statement to obtain information about a distinct-type column. For more information on the sysxtdtypes system catalog table, see Chapter 1 of the Informix Guide to SQL: Syntax.

References

See the ALLOCATE DESCRIPTOR, DEALLOCATE DESCRIPTOR, DECLARE, DESCRIBE, EXECUTE, FETCH, OPEN, PREPARE, PUT, and SET DESCRIPTOR statements in this manual for more information about using dynamic SQL statements.

For more information about the system-descriptor area, see the INFORMIX-ESQL/C Programmer's Manual.

GET DIAGNOSTICS

Use the GET DIAGNOSTICS statement to return diagnostic information about executing an SQL statement. The GET DIAGNOSTICS statement uses one of two clauses, as the following list describes:

The Statement clause determines count and overflow information about errors and warnings generated by the most recent SQL statement.
The EXCEPTION clause provides specific information about errors and warnings generated by the most recent SQL statement.

Syntax

Usage

The GET DIAGNOSTICS statement retrieves selected status information from the diagnostics area and retrieves either count and overflow information or information on a specific exception. The diagnostics area is a data structure that stores diagnostic information about an executed SQL statement.

The GET DIAGNOSTICS statement never changes the contents of the diagnostics area.

Using the SQLSTATE Status Code

When an SQL statement executes, the database server sets a status code is automatically generated. This code represents one of the following exceptions:

Success to indicate that the SQL executed without exceptions
Warning to indicate that the SQL statement executed successfully but the database server encountered some condition that might limit the statement
End of Data or Not Found to indicate that the SQL statement executed successfully but it either found no matching rows (Not Found) or it did not operate on a row (End of Data)
Error to indicate that the SQL statement did not execute successfully

The database server stores this status code in a variable called SQLSTATE.

X/O

The SQLSTATE status variable conforms to the ANSI and X/Open standards.

Tip: Informix database servers also store a status code in an Informix-specific variable called SQLCODE and exception information in the SQLCA structure. For more information, see the "INFORMIX-ESQL/C Programmer's Manual."

Class and Subclass Codes

The SQLSTATE status code is a a five-character string that can contain only digits and capital letters. This string has the following two parts:

The first two characters of the SQLSTATE status code indicate a class.
The last three characters of the SQLSTATE status code indicate a subclass.

Figure 1-1 shows the structure of the SQLSTATE code. This example uses the value 08001, where 08 is the class code and 001 is the subclass code. The value 08001 represents the error server rejected the connection.


	Figure 1-1 The Structure of the SQLSTATE Status Code

The following table is a quick reference for interpreting class code values.

SQLSTATE Class Code Value Outcome
00
Success

01
Success with warning

02
End of data
not found

> 02
Errors

Support for ANSI Standards

All status codes returned to the SQLSTATE variable are ANSI compliant except in the following cases:

SQLSTATE codes with a class code of 01 and a subclass code that begins with a I are Informix-specific warning messages.
SQLSTATE code with a class code of 01 and a subclass code of U01 indicates that the a user-defined routine has returned a warning message that has been defined by a user-defined routine.
SQLSTATE codes with a class code of IX and any subclass code are Informix-specific error messages.
SQLSTATE codes whose class code begins with a digit in the range 5 to 9 or with a capital letter in the range I to Z indicate conditions that are currently undefined by ANSI. The only exception is that SQLSTATE codes whose class code is IX are Informix-specific error messages.
SQLSTATE code of U0001 indicates that a user-defined routine has returned an error message that has been defined by a user-defined routine.

List of SQLSTATE Codes

The following table describes the class codes, subclass codes, and the meaning of all valid warning and error codes associated with the SQLSTATE status variable.

(1 of 4)
Class	Subclass	Meaning
00	000	Success
01 01 01 01 01 01 01	000 002 003 004 005 006 007	Success with warning Disconnect error. Transaction rolled back Null value eliminated in set function String data, right truncation Insufficient item descriptor areas Privilege not revoked Privilege not granted
01 01 01 01 01 01 01 01 01 01	I01 I03 I04 I05 I06 I07 I08 I09 I10 I11	Database has transactions `ANSI`-compliant database selected `INFORMIX`-`U`niversal `S`erver database selected Float to decimal conversion has been used Informix extension to `ANSI`-compliant standard syntax `UPDATE/DELETE` statement does not have a `WHERE` clause An `ANSI` keyword has been used as a cursor name Number of items in the select list is not equal to the number in the into list Database server running in secondary mode Dataskip is turned on
01	U01	User-defined routine has defined the warning message text
02	000	No data found
07 07 07 07 07 07 07 07 07	000 001 002 003 004 005 006 008 009	Dynamic `SQL` error `USING` clause does not match dynamic parameters `USING` clause does not match target specifications Cursor specification cannot be executed `USING` clause is required for dynamic parameters Prepared statement is not a cursor specification Restricted data type attribute violation Invalid descriptor count Invalid descriptor index
08 08 08 08 08 08 08 08	000 001 002 003 004 006 007 S01	Connection exception Server rejected the connection Connection name in use Connection does not exist Client unable to establish connection Transaction rolled back Transaction state unknown Communication failure
0A 0A	000 001	Feature not supported Multiple server transactions
21 21 21	000 S01 S02	Cardinality violation Insert value list does not match column list Degree of derived table does not match column list
22 22 22 22 22 22 22 22 22 22	000 001 002 003 005 027 012 019 024 025	Data exception String data, right truncation Null value, no indicator parameter Numeric value out of range Error in assignment Data exception trim error Division by zero Invalid escape character Unterminated string Invalid escape sequence
23	000	Integrity constraint violation
24	000	Invalid cursor state
25	000	Invalid transaction state
2B	000	Dependent privilege descriptors still exist
2D	000	Invalid transaction termination
26	000	Invalid `SQL` statement identifier
2E	000	Invalid connection name
28	000	Invalid user-authorization specification
33	000	Invalid `SQL` descriptor name
34	000	Invalid cursor name
35	000	Invalid exception number
37	000	Syntax error or access violation in `PREPARE` or `EXECUTE IMMEDIATE`
3C	000	Duplicate cursor name
40 40	000 003	Transaction rollback Statement completion unknown
42	000	Syntax error or access violation
S0 S0 S0 S0 S0	000 001 002 011 021	Invalid name Base table or view table already exists Base table not found Index already exists Column already exists
S1	001	Memory allocation failure
IX	000	Informix reserved error message
U0	001	User-defined routine has defined the error message text

Using SQLSTATE in Applications

You can use a variable, called SQLSTATE, that you do not have to declare in your program. SQLSTATE contains the status code that is generated every time your program executes an SQL statement. This status code is essential for exception handling. You can examine the SQLSTATE variable to determine whether an SQL statement was successful. If the SQLSTATE variable indicates that the statement failed, you can execute a GET DIAGNOSTICS statement to obtain additional error information from the diagnostics area.

For an example of how to use an SQLSTATE variable in a program, see "Using GET DIAGNOSTICS for Error Checking".

Statement Clause

Element Purpose Restrictions Syntax
st_var
Host variable that receives status information about the most recent `SQL` statement. It receives information for the specified status field name.
Data type must match that of the requested field.
Variable name must conform to language-specific rules for variable names.

When retrieving count and overflow information, GET DIAGNOSTICS can deposit the values of the three statement fields into a corresponding host variable. The host-variable data type must be the same as that of the requested field. These three fields are represented by the following keywords.


Field Name Keyword	Field Data Type	Field Contents	ESQL/C Host Variable Data Type
`MORE`	Character	Y or N	char[2]
`NUMBER`	Integer	1 to 35,000	int
`ROW`_`COUNT`	Integer	0 to 999,999,999	int

Using the MORE Keyword

Use the MORE keyword to determine if the most recently executed SQL statement performed the following actions:

Stored all the exceptions it detected in the diagnostics area.

GET DIAGNOSTICS

N

Detected more exceptions than it stored in the diagnostics area.

GET DIAGNOSTICS

Y

The value of MORE is always N.

Using the NUMBER Keyword

Use the NUMBER keyword to count the number of exceptions that the most recently executed SQL statement placed into the diagnostics area. The NUMBER field can hold a value from 1 to 35,000, to indicate how many exceptions are counted.

Using the ROW_COUNT Keyword

Use the ROW_COUNT keyword to count the number of rows the most recently executed statement processed. The ROW_COUNT field counts the following number of rows:

Inserted into a table
Updated in a table
Deleted from a table

EXCEPTION Clause

Element Purpose Restrictions Syntax
en_var
Host variable that specifies an exception number for a `GET DIAGNOSTICS` statement
Variable must contain an integer value limited to a range from 1 to 35,000. Variable data type must be `INT` or `SMALLINT`.
Variable name must conform to language-specific rules for variable names.

except_num
Literal integer value that specifies the exception number for a `GET DIAGNOSTICS` statement. The except_num literal indicates one of the exception values from the number of exceptions returned by the `NUMBER` field in the Statement clause.
Integer value is limited to a range from 1 to 35,000.
Literal Number, p. 1-1001

ex_var
Host variable that you declare, which receives `EXCEPTION` information about the most recent `SQL` statement. Receives information for a specified exception field name.
Data type must match that of the requested field.
Variable name must conform to language-specific rules for variable names.

When GET DIAGNOSTICS retrieves exception information, it deposits the values of each of the seven fields into corresponding host variables. These fields are located in the diagnostics area and are derived from an exception raised by the most recent SQL statement.

The host-variable data type must be the same as that of the requested field. The seven exception information fields are represented by the keywords described in the following table.

Field Name
Keyword Field
Data Type Field
Contents ESQL/C Host Variable
Data Type
RETURNED_SQLSTATE
Character
SQLSTATE value
char[6]

CLASS_ORIGIN
Character
String
char[255]

SUBCLASS_ORIGIN
Character
String
char[255]

INFORMIX_SQLCODE
Integer
SQLCODE value
long int

MESSAGE_TEXT
Character
String
char[8191]

MESSAGE_LENGTH
Integer
Numeric value
int

SERVER_NAME
Character
String
char[255]

CONNECTION_NAME
Character
String
char[255]

The application specifies the exception by number, using either an unsigned integer, except_num, or an integer host variable (an exact numeric with a scale of 0), en_var. An exception with a value of 1 corresponds to the SQLSTATE value set by the most recent SQL statement other than GET DIAGNOSTICS. The association between other exception numbers and other exceptions raised by that SQL statement is undefined. Thus, no set order exists in which the diagnostic area can be filled with exception values. You always get at least one exception, even if the SQLSTATE value indicates success.

If an error occurs within the GET DIAGNOSTICS statement (that is, if an illegal exception number is requested), the Informix internal SQLCODE and SQLSTATE variables are set to the value of that exception. In addition, the GET DIAGNOSTICS fields are undefined.

Using the RETURNED_SQLSTATE Keyword

Use the RETURNED_SQLSTATE keyword to determine the SQLSTATE value that describes the exception.

Using the INFORMIX_SQLCODE Keyword

Use the INFORMIX_SQLCODE keyword to retrieve the value of the Informix-specific status code (SQLCODE) for the associated SQLSTATE (and RETURNED_SQLSTATE) value. The Informix Error Messages manual describes Informix-specific codes.

Using the CLASS_ORIGIN Keyword

Use the CLASS_ORIGIN keyword to retrieve the source of the class portion of the RETURNED_SQLSTATE (and SQLSTATE) value. Possible class origins include the following:

If the International Standards Organization (ISO) standard defines the class, the value of CLASS_ORIGIN is equal to 'ISO 9075'. ANSI SQL and ISO SQL are synonymous.
If Informix defines the class value, the value of CLASS_ORIGIN is equal to 'IX000'.
If a user-defined routine has defined the message in the MESSAGE_TEXT field, the value of CLASS_ORIGIN is 'U0001'. A routine can be an SPL routine or an external routine.

Using the SUBCLASS_ORIGIN Keyword

Use the SUBCLASS_ORIGIN keyword to define the source of the subclass portion of the RETURNED_SQLSTATE (and SQLSTATE) value. Possible subclass origins include the following:

If the International Standards Organization (ISO) standard defines the subclass value, the value of SUBCLASS_ORIGIN is equal to
'ISO 9075'. ANSI SQL and ISO SQL are synonymous.
If Informix defines the subclass value, the value of SUBCLASS_ORIGIN is equal to 'IX000'.
If a user-defined routine has defined the message in the MESSAGE_TEXT field, the value of SUBCLASS_ORIGIN is 'U0001'. A routine can be an SPL routine or an external routine.

Using the MESSAGE_TEXT Keyword

Use the MESSAGE_TEXT keyword to determine the message text of the exception (for example, an error message). User-defined routines (such as stored routines and external routines) can define their own message text, which you can access text through the MESSAGE_TEXT field.

The following values indicate that a user-defined routine has returned a message that the routine has defined:

The RETURNED_SQLSTATE field and SQLSTATE are as follows:
- "01U01": when the user-defined routine returns a user-defined warning message
- "U0001": when the user-defined routine returns a user-defined error message
The CLASS_ORIGIN and SUBCLASS_ORIGIN fields are set to "U0001".

Using the MESSAGE_LENGTH Keyword

Use the MESSAGE_LENGTH keyword to determine the length of the current string in the MESSAGE_TEXT field.

Using the SERVER_NAME Keyword

Use the SERVER_NAME keyword to determine the name of the database server associated with the actions of a CONNECT or DATABASE statement.

When the SERVER_NAME Field Is Updated

The GET DIAGNOSTICS statement updates the SERVER_NAME field when the following situations occur:

A CONNECT statement successfully executes.
A SET CONNECTION statement successfully executes.
A DISCONNECT statement successfully executes at the current connection.
A DISCONNECT ALL statement fails.

When the SERVER_NAME Field Is Not Updated

The SERVER_NAME field is not updated when:

a CONNECT statement fails.
a DISCONNECT statement fails (this does not include the DISCONNECT ALL statement).
a SET CONNECTION statement fails.

The SERVER_NAME field retains the value set in the previous SQL statement. If any of the preceding conditions occur on the first SQL statement that executes, the SERVER_NAME field is blank.

The Contents of the SERVER_NAME Field

The SERVER_NAME field contains different information after you execute the following statements.


Executed Statement	SERVER_NAME Field Contents
`CONNECT`	It contains the name of the database server to which you connect or fail to connect. Field is blank if you do not have a current connection or if you make a default connection.
`SET CONNECTION`	It contains the name of the database server to which you switch or fail to switch.
`DISCONNECT`	It contains the name of the database server from which you disconnect or fail to disconnect. If you disconnect and then you execute a `DISCONNECT` statement for a connection that is not current, the SERVER_NAME field remains unchanged.
`DISCONNECT ALL`	It sets the field to blank if the statement executes successfully. If the statement does not execute successfully, the `SERVER_NAME` field contains the names of all the database servers from which you did not disconnect. However, this information does not mean that the connection still exists.

If the CONNECT statement is successful, the SERVER_NAME field is set to one of the following values:

The INFORMIXSERVER value if the connection is to a default database server (that is, the CONNECT statement does not list a database server)
The name of the specific database server if the connection is to a specific database server

The DATABASE Statement

When you execute a DATABASE statement, the SERVER_NAME field contains the name of the server on which the database resides.

Using the CONNECTION_NAME Keyword

Use the CONNECTION_NAME keyword to specify a name for the connection used in your CONNECT or DATABASE statements.

When the CONNECTION_NAME Keyword Is Updated

GET DIAGNOSTICS updates the CONNECTION_NAME field when the following situations occur:

A CONNECT statement successfully executes.
A SET CONNECTION statement successfully executes.
A DISCONNECT statement successfully executes at the current connection. GET DIAGNOSTICS fills the CONNECTION_NAME field with blanks because no current connection exists.
A DISCONNECT ALL statement fails.

When CONNECTION_NAME Is Not Updated

The CONNECTION_NAME field is not updated when the following situations occur:

A CONNECT statement fails.
A DISCONNECT statement fails (this does not include the DISCONNECT ALL statement).
A SET CONNECTION statement fails.

The CONNECTION_NAME field retains the value set in the previous SQL statement. If any of the preceding conditions occur on the first SQL statement that executes, the CONNECTION_NAME field is blank.

The Contents of the CONNECTION_NAME Field

The CONNECTION_NAME field contains different information after you execute the following statements.


Executed Statement	CONNECTION_NAME Field Contents
`CONNECT`	It contains the name of the connection, specified in the `CONNECT` statement, to which you connect or fail to connect. The field is blank if you do not have a current connection or if you make a default connection.
`SET CONNECTION`	It contains the name of the connection, specified in the `CONNECT` statement, to which you switch or fail to switch.
`DISCONNECT`	It contains the name of the connection, specified in the `CONNECT` statement, from which you disconnect or fail to disconnect. If you disconnect, and then you execute a `DISCONNECT` statement for a connection that is not current, the CONNECTION_NAME field remains unchanged.
`DISCONNECT ALL`	The CONNECTION_NAME field is blank if the statement executes successfully. If the statement does not execute successfully, the CONNECTION_NAME field contains the names of all the connections, specified in your `CONNECT` statement, from which you did not disconnect. However, this information does not mean that the connection still exists.

If the CONNECT is successful, the CONNECTION_NAME field is set to the following values:

The name of the database environment as specified in the CONNECT statement if the CONNECT does not include the AS clause
The name of the connection (identifier after the AS keyword) if the CONNECT includes the AS clause

The DATABASE Statement

When you execute a DATABASE statement, the CONNECTION_NAME field is blank.

Using GET DIAGNOSTICS for Error Checking

The GET DIAGNOSTICS statement returns information held in various fields of the diagnostic area. For each field in the diagnostic area that you want to access, you must supply a host variable with a compatible data type.

The following examples illustrate using the GET DIAGNOSTICS statement to display error information. The first example shows an ESQL/C error display routine called disp_sqlstate_err().

void disp_sqlstate_err()
{
int j;

EXEC SQL BEGIN DECLARE SECTION;
    int exception_count;
    char overflow[2];
    int exception_num=1;
    char class_id[255];
    char subclass_id[255];
    char message[8191];
    int messlen;
    char sqlstate_code[6];
    int i;
EXEC SQL END DECLARE SECTION;

    printf("---------------------------------");
    printf("-------------------------\n");
    printf("SQLSTATE: %s\n",SQLSTATE);
    printf("SQLCODE: %d\n", SQLCODE);
    printf("\n");
 
    EXEC SQL get diagnostics :exception_count = NUMBER,
        :overflow = MORE;
    printf("EXCEPTIONS:  Number=%d\t", exception_count);
    printf("More? %s\n", overflow);
    for (i = 1; i <= exception_count; i++)
    {
        EXEC SQL get diagnostics  exception :i
            :sqlstate_code = RETURNED_SQLSTATE,
            :class_id = CLASS_ORIGIN, :subclass_id = SUBCLASS_ORIGIN,
            :message = MESSAGE_TEXT, :messlen = MESSAGE_LENGTH;
        printf("- - - - - - - - - - - - - - - - - - - -\n");
        printf("EXCEPTION %d: SQLSTATE=%s\n", i,
            sqlstate_code);
        message[messlen-1] ='\0';
        printf("MESSAGE TEXT: %s\n", message);

        j = stleng(class_id);
        while((class_id[j] == '\0') ||
              (class_id[j] == ' '))
            j--;
        class_id[j+1] = '\0';
        printf("CLASS ORIGIN: %s\n",class_id);

        j = stleng(subclass_id);
        while((subclass_id[j] == '\0') ||
              (subclass_id[j] == ' '))
            j--;
        subclass_id[j+1] = '\0';
        printf("SUBCLASS ORIGIN: %s\n",subclass_id);
    }

    printf("---------------------------------");
    printf("-------------------------\n");
}

References

In Chapter 5 of the Informix Guide to SQL: Tutorial, see the discussion about error-code handling. In addition, refer to the exception-handling chapter of the INFORMIX-ESQL/C Programmer's Manual.

Informix Guide to SQL: SyntaxChapter 1: SQL Statements Home Contents Index Master Index New Book

SQL Statements

FETCH

Row Numbers

INTO Clause of SELECT

Usage

Error Checking FLUSH Statements

Counting Total and Pending Rows

References

Usage

Freeing a Cursor

VALUE Clause

Fetching a Null Value

Syntax

Usage

List of SQLSTATE Codes

Using the SUBCLASS_ORIGIN Keyword

Using the MESSAGE_TEXT Keyword

Using the MESSAGE_LENGTH Keyword

When the SERVER_NAME Field Is Not Updated

The DATABASE Statement

When CONNECTION_NAME Is Not Updated

The DATABASE Statement

References

Informix Guide to SQL: Syntax
Chapter 1: SQL Statements

Home Contents Index Master Index New Book