whenever

Description

Specifies an action to occur whenever an executable SQL statement causes a specified condition.

Syntax

exec sql whenever {sqlerror | not found | sqlwarning} 
 {continue | go to label | goto label |
 stop | call routine_name [args]}; 

Parameters

sqlerror

Specifies an action to take when an error is detected, such as a syntax error returned to the Embedded SQL program from SQL Server.

not found

Specifies an action to take when a fetch or select into statement retrieves no data or when a searched update or delete statement affects no rows.

sqlwarning

Specifies an action to take when a warning is received; for example, when a character string is truncated.

continue

Take no action when the condition occurs.

go to | goto

Transfer control to the program statement at the specified label.

label

A host language statement label, such as a C label.

stop

Terminate the Embedded SQL program when the condition occurs.

call

Transfer control to a callable routine in the program, such as a user-defined function or subroutine.

routine_name

A host language routine that can be called. The routine must be able to be called from the source file that contains the whenever statement. You may need to declare the routine as external to compile the Embedded SQL program.

args

One or more arguments to be passed to the callable routine, using the parameter-passing conventions of the host language. The arguments can be any list of host variables, literals, or expressions that the host language allows. A space character should separate each argument from the next.

Examples

Example 1

exec sql whenever sqlerror call err_handler(); 
exec sql whenever sqlwarning call warn_handler(); 
 long SQLCODE; 
 exec sql begin declare section;
     CS_CHAR        lastname[40];
     CS_CHAR        firstname[20];
     CS_CHAR        phone[12];
exec sql end declare section;
 
 exec sql declare au_list cursor for
     select au_lname, au_fname, phone
     from authors 
     order by au_lname;
 
exec sql open au_list;
 
 exec sql whenever not found go to list_done;
 
 while (TRUE){
        exec sql fetch au_list 
             into :lastname, :firstname, :phone;
         printf(“Lastname is: %s\n”, lastname,        
“Firstname is: %s\n”, firstname, 
         “Phone number is: %s\n”, phone; 
 }
 list_done:
 exec sql close au_list;
 exec sql disconnect current;

Usage


SQL descriptor codes

The following table pertains to the SQL descriptor used for dynamic SQL statements. Sybase’s use of dynamic SQL values conforms to the ANSI/ISO 185-92 SQL-92 standards. For more information, see the appropriate ANSI/ISO documentation.

Table 10-5: SQL descriptor datatype codes

ANSI SQL datatype

Code

Sybase-defined datatype

Client-Library code

bit

14

smalldatetime

-9

character

1

money

-10

character varying

12

smallmoney

-11

date, time

9

text

-3

decimal

3

image

-4

double precision

8

tinyint

-8

float

6

binary

-5

integer

4

varbinary

-6

numeric

2

long binary

-7

real

7

longchar

-2

smallint

5

Table 10-6: SQL descriptor identifier values

Value

Description

type

The datatype of this column (item number) in the row. For values, see Table 10-5.

length

The length, in characters, of the dynamic parameter marker of target for the specified SQL descriptor.

returned_length

The length of char types of the values from the select column list.

precision

An integer specifying the total number of digits of precision for the CS_NUMERIC variable.

scale

An integer specifying the total number of digits after the decimal point for the CS_NUMERIC variable.

nullable

Equals 0 if the dynamic parameter marker can accept a null value; otherwise, equals 1.

indicator

Value for the indicator parameter associated with the dynamic parameter marker or target.

data

Value for the dynamic parameter marker or target associated with the specified SQL descriptor. If indicator is negative, this field is undefined.

name

The name of the specified SQL descriptor containing information about the dynamic parameter markers.