SQL Statements

Embedding SQL statements
Using Indicator variables
- To detect null values
- To detect truncated values
Using database Cursor statements

Embedding SQL statements

SQL statements that communicate with a relational database can be embedded in your program.

Statements must be preceded with the keywords "EXEC SQL" (preferred ANSI standard) or the $ symbol:

EXEC SQL CONNECT TO "testdb";

EXEC SQL CREATE TABLE t1 (
  k2 integer NOT NULL PRIMARY KEY,
  t1 date DEFAULT TODAY NOT NULL,
  c char(10)
);

EXEC SQL DELETE FROM customer WHERE store_num = '101';

Host variables can substitute for literal values in your programs, to store the values retrieved from a database table or serve as parameters for SQL statements. To use a host variable in an SQL statement you must declare the variable, choosing a data type compatible with the data type of the related database column.

See Supported Data Types for a list of the SQL data types and the compatible FESQLC or C data types.

Data conversion in host variables used for input and fetch database operations is database-dependent; we recommend that you use a host variable that corresponds to the database column to avoid conversion problems.

The host variable name is preceded by a (:) symbol when it is used in an embedded SQL statement:

EXEC SQL BEGIN DECLARE SECTION;
   char p_state_code[3];     /* SQL type is CHAR(2) */
   varchar p_state_name[16]; /* SQL type is VARCHAR(15) */
EXEC SQL END DECLARE SECTION;

EXEC SQL INSERT INTO state VALUES (:p_state_code, :p_state_name);

Note: The char/varchar FESQLC data type has a null terminator. Therefore, when char/varchars are used to store values for a character-type database column, set the size of the host variable equal to the size of the database column + 1. See Host Variables for additional information.

Case sensitivity

The following components of an embedded SQL statement are case-sensitive:

Names of host variables

The variable ordernum is not the same as Ordernum. FESQLC considers these to be two different variables.

Data types

If you use the data type INT in a variable declaration it would not be recognized - int is the correct name. See Supported data types for the names of the FESQLC data types.

Cursor names and statement names

Usually, the values in variables are case-sensitive. All other components are not case-sensitive.

Escape characters and quotation marks

Both C and FESQLC use the backslash character \ as the escape character, to specify that the following character is to be considered literal and not interpreted. FESQLC allows you to enclose strings in either single or double quotes. Special care must be taken when the WHERE clause of your embedded SQL statement contains a backslash or quotation mark.

To search for a backslash character, escape the special significance of the backslash with the \ escape character: For example, to search for the company name The \\ Store:

EXEC SQL DECLARE c1 CURSOR FOR SELECT * FROM customer WHERE store_name = 'The \\\\ Store';
The first backslash causes the second backslash to be interpreted literally, and the third backslash causes the fourth backslash to be interpreted literally, resulting in the desired "The \\ Store".

ANSI standards do not allow you to use the same quotation character in an expression as the string delimiter and as a literal. Therefore, to search for a value containing an embedded singlequote, enclose the value with double quotes, and use the \ escape character to cause the single quote to be interpreted literally. For example, to search for the company name The ' Store:

EXEC SQL DECLARE c1 CURSOR FOR SELECT * FROM customer WHERE store_name = "The \' Store";

Comments

You can use the standard C comment indicator in your FESQLC statements:

EXEC SQL DELETE FROM customer;  /* deletes all rows */

Using Indicator variables

You can specify an indicator variable in your SQL statement to obtain information about a value returned by the statement.

Declare the indicator variable using the normal FESQLC syntax, and specify an appropriate data type; integer data types are used for indicators that detect null values or truncated values.

EXEC SQL BEGIN DECLARE SECTION;
   char p_state_code[3];
   int i_state_code;
EXEC SQL END DECLARE SECTION;

Use the indicator variable in the SELECT statement. Specify the indicator variable to be associated with the host variable, preceding the indicator variable with the word INDICATOR:

:p_state_code INDICATOR :i_state_code
Or, separate the variables with a symbol (: or $). There can be one or more whitespaces between the host variable name and the indicator name:
:p_state_code :i_state_code
$p_state_code$i_state_code
$p_state_code:i_state_code
Example:

EXEC SQL SELECT state_code INTO :p_state_code INDICATOR :i_state_code
     FROM state WHERE state_name = 'Illinois';

When FESQLC returns the value into the host variable, it will also set the corresponding indicator variable. Your program can check the values of any indicator variables before continuing.

To detect null values

If a database table column permits nulls, you may have a null value returned to the corresponding host variable specified in your SQL SELECT statement. If one of the values in a database column referenced for an aggregate function is null, you may also have a null value returned. Null (unknown) values can cause problems for your program. Use indicator variables to determine whether any values returned to the host variables are null:

FESQLC will set the value of an indicator variable to zero if the value returned for the associated host variable is not null.
If the value returned is null, FESQLC will set the indicator variable to -1.

Checking whether the variable is null before printing it:

  printf("%s\n", i_state_code == 0 ? p_state_code : "<no code>");

Tip: The NULL keyword of an INSERT statement allows you to insert a null value into a table row. As an alternative, you can use a negative indicator variable with the host variable. Set the value of the indicator variable to -1 when it is declared.

To detect truncated values

If the host variable referenced by an SQL SELECT statement is a character array, the value returned from the database may be truncated (the value returned is too large to fit into the array). Use indicator variables to determine whether any values returned are truncated.

FESQLC will set the value of an indicator variable to zero if the value returned for the associated host variable is not truncated.
If the value returned was truncated, FESQLC will set the value of the indicator variable to the original size in bytes of the host variable.

Example program:

01 #include <stdio.h>
02
03 int main()
04 {
05  EXEC SQL BEGIN DECLARE SECTION;
06  char p_state_code[3];
07  int i_state_code;
08  EXEC SQL END DECLARE SECTION;
09
10  EXEC SQL CONNECT TO "custdemo";
11  EXEC SQL SELECT state_code INTO :p_state_code INDICATOR :i_state_code 
12    FROM state 
13    WHERE state_name = 'Illinois'; 
14
15  printf("%-20s\n ",
16         i_state_code == 0 ? p_state_code : "<no name>"
17  );
18  EXEC SQL DISCONNECT ALL;
19 }

Note: line numbers are for reference only.

Using database cursor statements

You can use sequential, scroll, hold, update, or insert cursors in FESQLC programs. For example, if a SELECT statement will retrieve more than one row:

Declare the cursor for the select statement. This allocates storage to hold the cursor.
Open the cursor. The active set associated with the cursor is identified, and the cursor is positioned before the first row of the set.
Fetch the data into the host variable(s), until the last desired row is fetched.
Close the cursor when the fetch is complete. This releases the active result set associated with the cursor. A closed cursor can be re-opened.
Free the cursor when it is no longer needed by the program. A freed cursor must be declared again before it can be re-opened and used.

The cursor program statements must appear physically within the module in the order listed.

Example program:

01 #include <stdio.h>
02
03 EXEC SQL define NAME_LEN 20;  /* customer.store_name is a CHAR(20)  */
04 EXEC SQL define ADDR_LEN 20;  /* customer.addr is a CHAR(20)        */
05 EXEC SQL define STATE_LEN 2;  /* customer.state_code is a CHAR(2)   */
06
07 int main()
08 {
09
10  EXEC SQL BEGIN DECLARE SECTION;
11    int p_num;
12    varchar p_name[ NAME_LEN + 1 ];
13    int2 i_name;
14    varchar p_addr[ ADDR_LEN + 1 ];
15    int2 i_addr;
16    varchar p_addr2[ ADDR_LEN + 1 ];
17    int2 i_addr2;
18    char p_state_code[STATE_LEN + 1];
19  EXEC SQL END DECLARE SECTION;
20
21  printf( "Connecting...\n\n");
22  EXEC SQL CONNECT TO 'custdemo';
23
24  EXEC SQL DECLARE c1 CURSOR FOR        /* Declaring the cursor */
25    SELECT store_num, store_name, addr, addr2
26     FROM customer
27     WHERE state = :p_state;
28
29  strcpy(p_state, "IL");
30  EXEC SQL OPEN c1;                  /* Opening the cursor */
31
32  for (;;)                           /* loop to fetch the rows */
33  {
34   EXEC SQL FETCH c1 INTO :p_num,   
35    :p_name INDICATOR :i_name,
36    :p_addr INDICATOR :i_addr,
37    :p_addr2 INDICATOR :i_addr2;
38
39   if (strncmp(SQLSTATE, "02", 2) == 0) {
40     /* No more rows */
41     break;
42   }
43
44   printf("%6d %-20s\n %s %s\n",
45     p_num,
46     i_name == 0 ? p_name : "<no name>",
47     i_addr == 0 ? p_addr : "<no address>",
48     i_addr2 == 0 ? p_addr2 : ""
49   );
50  }
51
52  EXEC SQL CLOSE c1;     /* close the cursor */
53  EXEC SQL FREE c1       /* free the cursor  */
54
55  printf("\nDisconnecting...\n\n");
56  EXEC SQL DISCONNECT CURRENT;
57
58  return 0;
59 }

The code in lines 39-41 checks the SQLSTATE global variable to determine whether there are any more rows to retrieve. See Handling Exceptions.

See Dynamic SQL for additional examples using cursors and the PREPARE statement.

Note: The FETCH ... INTO ... syntax is supported by all databases. Declaring a cursor for SELECT ... INTO ... is Informix-specific syntax, and not recommended for portability.