Using embedded SQL v17

These two examples show how to use embedded SQL with EDB Postgres Advanced Server.

Example: A simple query

The first code sample shows how to execute a SELECT statement that 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;

If you plan to precompile the code in PROC mode, you can omit the BEGIN DECLARE…END DECLARE section. For more information about declaring host variables, see Declaring host variables.

The data type associated with each variable in 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 tells 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 prints an error message to stderr (standard error), using the sqlprint() function supplied with ecpglib. The next EXEC SQL statement establishes a connection with EDB Postgres Advanced Server:

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

In this 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 can 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 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. 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 that might 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 can contain the following values:

When including an indicator variable in an INTO clause, you don't need to include the optional INDICATOR keyword.

You can omit an indicator variable if you're certain that a query never returns a NULL value into the corresponding host variable. If you omit an indicator variable and a query returns a NULL value, ecpglib raises a runtime error.

Declaring host variables

You can use a host variable in a SQL statement at any point that a value can appear in 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 show 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're compiling in PROC mode, you can 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 aren't 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:

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 shown in the next example.

Example: Using a cursor to process a result set

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

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

After declaring host variables, the 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[])
{

DECLARE

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). The example omits 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 might think that you can refer to argv[1] and argv[2] in a SQL statement instead of creating a separate copy of each variable. However, that doesn't work. All host variables must be declared in a BEGIN/END DECLARE SECTION, unless you're compiling in PROC mode. Since argv is a function parameter (not an automatic variable), you can't declare it in a BEGIN/END DECLARE SECTION. If you're compiling in PROC mode, you can refer to any C variable in a SQL statement.

The next statement tells 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 EDB Postgres 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 contains 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 don't include an INTO clause. Instead, you specify the target variables (or descriptors) when you FETCH from the cursor.

OPEN

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 loops 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;

FETCH

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 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'. 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.

CLOSE

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

EXEC SQL CLOSE employees;
EXEC SQL DISCONNECT;