Using Embedded SQL¶

Each of the following sections leads with a code sample, followed by an explanation of each section within the code sample.

Example - A Simple Query¶

The first code sample demonstrates how to execute a SELECT statement (which returns a single row), storing the results in a group of host variables. After declaring host variables, it connects to the edb sample database using a hard-coded role name and the associated password, and queries the emp table. The query returns the values into the declared host variables; after checking the value of the NULL indicator variable, it prints a simple result set onscreen and closes the connection.

/************************************************************
 * print_emp.pgc
 *
 */
#include <stdio.h>

int main(void)
{
  EXEC SQL BEGIN DECLARE SECTION;
    int v_empno;
    char v_ename[40];
    double v_sal;
    double v_comm;
    short v_comm_ind;
  EXEC SQL END DECLARE SECTION;

  EXEC SQL WHENEVER SQLERROR sqlprint;

  EXEC SQL CONNECT TO edb
    USER 'alice' IDENTIFIED BY '1safepwd';

  EXEC SQL
     SELECT
      empno, ename, sal, comm
     INTO
      :v_empno, :v_ename, :v_sal, :v_comm INDICATOR:v_comm_ind
     FROM
      emp
    WHERE
      empno = 7369;

  if (v_comm_ind)
    printf("empno(%d), ename(%s), sal(%.2f) comm(NULL)\n",
            v_empno, v_ename, v_sal);
  else
    printf("empno(%d), ename(%s), sal(%.2f) comm(%.2f)\n",
            v_empno, v_ename, v_sal, v_comm);
  EXEC SQL DISCONNECT;
}
/***********************************************************\*

The code sample begins by including the prototypes and type definitions for the C stdio library, and then declares the main function:

#include <stdio.h>

int main(void)
{

Next, the application declares a set of host variables used to interact with the database server:

EXEC SQL BEGIN DECLARE SECTION;
  int v_empno;
  char v_ename[40];
  double v_sal;
  double v_comm;
  short v_comm_ind;
EXEC SQL END DECLARE SECTION;

Please note that if you plan to pre-compile the code in PROC mode, you may omit the BEGIN DECLARE…END DECLARE section. For more information about declaring host variables, refer to the Declaring Host Variables.

The data type associated with each variable within the declaration section is a C data type. Data passed between the server and the client application must share a compatible data type; for more information about data types, see the Supported C Data Types.

The next statement instructs the server how to handle an error:

EXEC SQL WHENEVER SQLERROR sqlprint;

If the client application encounters an error in the SQL code, the server will print an error message to stderr (standard error), using the sqlprint() function supplied with ecpglib. The next EXEC SQL statement establishes a connection with Advanced Server:

EXEC SQL CONNECT TO edb
  USER 'alice' IDENTIFIED BY '1safepwd';

In our example, the client application connects to the edb database, using a role named alice with a password of 1safepwd.

The code then performs a query against the emp table:

EXEC SQL
   SELECT
     empno, ename, sal, comm
   INTO
     :v_empno, :v_ename, :v_sal, :v_comm INDICATOR :v_comm_ind
   FROM
     emp
  WHERE
     empno = 7369;

The query returns information about employee number 7369.

The SELECT statement uses an INTO clause to assign the retrieved values (from the empno, ename, sal and comm columns) into the :v_empno, :v_ename, :v_sal and :v_comm host variables (and the :v_comm_ind null indicator). The first value retrieved is assigned to the first variable listed in the INTO clause, the second value is assigned to the second variable, and so on.

The comm column contains the commission values earned by an employee, and could potentially contain a NULL value. The statement includes the INDICATOR keyword, and a host variable to hold a null indicator.

The code checks the null indicator, and displays the appropriate on-screen results:

if (v_comm_ind)
  printf("empno(%d), ename(%s), sal(%.2f) comm(NULL)\n",
          v_empno, v_ename, v_sal);
else
  printf("empno(%d), ename(%s), sal(%.2f) comm(%.2f)\n",
          v_empno, v_ename, v_sal, v_comm);

If the null indicator is 0 (that is, false), the comm column contains a meaningful value, and the printf function displays the commission. If the null indicator contains a non-zero value, comm is NULL, and printf displays a value of NULL. Please note that a host variable (other than a null indicator) contains no meaningful value if you fetch a NULL into that host variable; you must use null indicators to identify any value which may be NULL.

The final statement in the code sample closes the connection to the server:

 EXEC SQL DISCONNECT;
}

Using Indicator Variables¶

The previous example included an indicator variable that identifies any row in which the value of the comm column (when returned by the server) was NULL. An indicator variable is an extra host variable that denotes if the content of the preceding variable is NULL or truncated. The indicator variable is populated when the contents of a row are stored. An indicator variable may contain the following values:

Indicator Value	Denotes
If an indicator variable is less than 0.	The value returned by the server was NULL.
If an indicator variable is equal to 0.	The value returned by the server was not NULL, and was not truncated.
If an indicator variable is greater than 0.	The value returned by the server was truncated when stored in the host variable.

When including an indicator variable in an INTO clause, you are not required to include the optional INDICATOR keyword.

You may omit an indicator variable if you are certain that a query will never return a NULL value into the corresponding host variable. If you omit an indicator variable and a query returns a NULL value, ecpglib will raise a run-time error.

Declaring Host Variables¶

You can use a host variable in a SQL statement at any point that a value may appear within that statement. A host variable is a C variable that you can use to pass data values from the client application to the server, and return data from the server to the client application. A host variable can be:

an array
a typedef
a pointer
a struct
any scalar C data type

The code fragments that follow demonstrate using host variables in code compiled in PROC mode, and in non-PROC mode. The SQL statement adds a row to the dept table, inserting the values returned by the variables v_deptno, v_dname and v_loc into the deptno column, the dname column and the loc column, respectively.

If you are compiling in PROC mode, you may omit the EXEC SQL BEGIN DECLARE SECTION and EXEC SQL END DECLARE SECTION directives. PROC mode permits you to use C function parameters as host variables:

void addDept(int v_deptno, char v_dname, char v_loc)
{
  EXEC SQL INSERT INTO dept VALUES( :v_deptno, :v_dname, :v_loc);
}

If you are not compiling in PROC mode, you must wrap embedded variable declarations with the EXEC SQL BEGIN DECLARE SECTION and the EXEC SQL END DECLARE SECTION directives, as shown below:

void addDept(int v_deptno, char v_dname, char v_loc)
{
  EXEC SQL BEGIN DECLARE SECTION;
    int v_deptno_copy = v_deptno;
    char v_dname_copy[14+1] = v_dname;
    char v_loc_copy[13+1] = v_loc;
  EXEC SQL END DECLARE SECTION;

  EXEC SQL INSERT INTO dept VALUES( :v_deptno, :v_dname, :v_loc);
}

You can also include the INTO clause in a SELECT statement to use the host variables to retrieve information:

EXEC SQL SELECT deptno, dname, loc
  INTO :v_deptno, :v_dname, v_loc FROM dept;

Each column returned by the SELECT statement must have a type-compatible target variable in the INTO clause. This is a simple example that retrieves a single row; to retrieve more than one row, you must define a cursor, as demonstrated in the next example.

Example - Using a Cursor to Process a Result Set¶

The code sample that follows demonstrates using a cursor to process a result set. There are four basic steps involved in creating and using a cursor:

Use the DECLARE CURSOR statement to define a cursor.
Use the OPEN CURSOR statement to open the cursor.
Use the FETCH statement to retrieve data from a cursor.
Use the CLOSE CURSOR statement to close the cursor.

After declaring host variables, our example connects to the edb database using a user-supplied role name and password, and queries the emp table. The query returns the values into a cursor named employees. The code sample then opens the cursor, and loops through the result set a row at a time, printing the result set. When the sample detects the end of the result set, it closes the connection.

/************************************************************
 * print_emps.pgc
 *
 */
#include <stdio.h>

int main(int argc, char *argv[])
{
  EXEC SQL BEGIN DECLARE SECTION;
    char *username = argv[1];
    char *password = argv[2];
    int v_empno;
    char v_ename[40];
    double v_sal;
    double v_comm;
    short v_comm_ind;
  EXEC SQL END DECLARE SECTION;

  EXEC SQL WHENEVER SQLERROR sqlprint;

  EXEC SQL CONNECT TO edb USER :username IDENTIFIED BY :password;

  EXEC SQL DECLARE employees CURSOR FOR
    SELECT
      empno, ename, sal, comm
    FROM
      emp;

  EXEC SQL OPEN employees;

  EXEC SQL WHENEVER NOT FOUND DO break;

  for (;;)
  {
    EXEC SQL FETCH NEXT FROM employees
      INTO
       :v_empno, :v_ename, :v_sal, :v_comm INDICATOR :v_comm_ind;

    if (v_comm_ind)
      printf("empno(%d), ename(%s), sal(%.2f) comm(NULL)\n",
              v_empno, v_ename, v_sal);
    else
      printf("empno(%d), ename(%s), sal(%.2f) comm(%.2f)\n",
              v_empno, v_ename, v_sal, v_comm);
  }
  EXEC SQL CLOSE employees;
  EXEC SQL DISCONNECT;
}
/************************************************************

The code sample begins by including the prototypes and type definitions for the C stdio library, and then declares the main function:

#include <stdio.h>

int main(int argc, char *argv[])
{

Next, the application declares a set of host variables used to interact with the database server:

EXEC SQL BEGIN DECLARE SECTION;
  char *username = argv[1];
  char *password = argv[2];
  int v_empno;
  char v_ename[40];
  double v_sal;
  double v_comm;
  short v_comm_ind;
EXEC SQL END DECLARE SECTION;

argv[] is an array that contains the command line arguments entered when the user runs the client application. argv[1] contains the first command line argument (in this case, a username), and argv[2] contains the second command line argument (a password); please note that we have omitted the error-checking code you would normally include a real-world application. The declaration initializes the values of username and password, setting them to the values entered when the user invoked the client application.

You may be thinking that you could refer to argv[1] and argv[2] in a SQL statement (instead of creating a separate copy of each variable); that will not work. All host variables must be declared within a BEGIN/END DECLARE SECTION (unless you are compiling in PROC mode). Since argv is a function parameter (not an automatic variable), it cannot be declared within a BEGIN/END DECLARE SECTION. If you are compiling in PROC mode, you can refer to any C variable within a SQL statement.

The next statement instructs the server to respond to an SQL error by printing the text of the error message returned by ECPGPlus or the database server:

EXEC SQL WHENEVER SQLERROR sqlprint;

Then, the client application establishes a connection with Advanced Server:

EXEC SQL CONNECT TO edb USER :username IDENTIFIED BY :password;

The CONNECT statement creates a connection to the edb database, using the values found in the :username and :password host variables to authenticate the application to the server when connecting.

The next statement declares a cursor named employees:

EXEC SQL DECLARE employees CURSOR FOR
  SELECT
    empno, ename, sal, comm
  FROM
    emp;

employees will contain the result set of a SELECT statement on the emp table. The query returns employee information from the following columns: empno, ename, sal and comm. Notice that when you declare a cursor, you do not include an INTO clause - instead, you specify the target variables (or descriptors) when you FETCH from the cursor.

Before fetching rows from the cursor, the client application must OPEN the cursor:

EXEC SQL OPEN employees;

In the subsequent FETCH section, the client application will loop through the contents of the cursor; the client application includes a WHENEVER statement that instructs the server to break (that is, terminate the loop) when it reaches the end of the cursor:

EXEC SQL WHENEVER NOT FOUND DO break;

The client application then uses a FETCH statement to retrieve each row from the cursor INTO the previously declared host variables:

for (;;)
{
  EXEC SQL FETCH NEXT FROM employees
    INTO
     :v_empno, :v_ename, :v_sal, :v_comm INDICATOR :v_comm_ind;

The FETCH statement uses an INTO clause to assign the retrieved values into the :v_empno, :v_ename, :v_sal and :v_comm host variables (and the :v_comm_ind null indicator). The first value in the cursor is assigned to the first variable listed in the INTO clause, the second value is assigned to the second variable, and so on.

The FETCH statement also includes the INDICATOR keyword and a host variable to hold a null indicator. If the comm column for the retrieved record contains a NULL value, v_comm_ind is set to a non-zero value, indicating that the column is NULL.

The code then checks the null indicator, and displays the appropriate on-screen results:

if (v_comm_ind)
  printf("empno(%d), ename(%s), sal(%.2f) comm(NULL)\n",
          v_empno, v_ename, v_sal);
else
  printf("empno(%d), ename(%s), sal(%.2f) comm(%.2f)\n",
          v_empno, v_ename, v_sal, v_comm);
}

If the null indicator is 0 (that is, false), v_comm contains a meaningful value, and the printf function displays the commission. If the null indicator contains a non-zero value, comm is NULL, and printf displays the string 'NULL'. Please note that a host variable (other than a null indicator) contains no meaningful value if you fetch a NULL into that host variable; you must use null indicators for any value which may be NULL.

The final statements in the code sample close the cursor (employees), and the connection to the server:

EXEC SQL CLOSE employees;
EXEC SQL DISCONNECT;

Using Embedded SQL¶

Example - A Simple Query¶

Using Indicator Variables¶

Declaring Host Variables¶

Example - Using a Cursor to Process a Result Set¶

Previous topic

Next topic

Table of Contents