Issuing SQL statements in CLI applications

SQL statements are passed to CLI functions as SQLCHAR string variables. The variable can consist of one or more SQL statements, with or without parameter markers, depending on the type of processing you want. This topic describes the various ways SQL statements can be issued in CLI applications.

Before you begin

Before you issue an SQL statement, ensure you have allocated a statement handle.

Procedure

Perform either of the following steps to issue SQL statements:

To issue a single SQL statement, either initialize an SQLCHAR variable with the SQL statement and pass this variable to the CLI function, or directly pass a string argument cast to an SQLCHAR * to the function. For example:
```
SQLCHAR * stmt = (SQLCHAR *) "SELECT deptname, location FROM org";
/* ... */
SQLExecDirect (hstmt, stmt, SQL_NTS);
```
or
```
SQLExecDirect (hstmt, (SQLCHAR *) "SELECT deptname, location FROM org",
               SQL_NTS);
```

To issue multiple SQL statements on the same statement handle:

Initialize an array of SQLCHAR elements, where each element represents an individual SQL statement, or initialize a single SQLCHAR variable that contains the multiple statements delimited by a ";" character. For example:

SQLCHAR * multiple_stmts[] = {
  (SQLCHAR *) "SELECT deptname, location FROM org",
  (SQLCHAR *) "SELECT id, name FROM staff WHERE years > 5",
  (SQLCHAR *) "INSERT INTO org VALUES (99,'Hudson',20,'Western','Seattle')"
};

SQLCHAR * multiple_stmts = 
  "SELECT deptname, location FROM org;
   SELECT id, name FROM staff WHERE years > 5;
   INSERT INTO org VALUES (99, 'Hudson', 20, 'Western', 'Seattle')";

Call SQLExecDirect() to issue the first statement in the statement handle and then call SQLMoreResults() to issue subsequent statements in the statement handle, as shown in the following example:

/* Issuing the first SELECT statement of multiple_stmts */
cliRC = SQLExecDirect (hstmt, multiple_stmts, SQL_NTS);
/* ... process result-set of first SELECT statement ... */

/* Issuing the second SELECT statement of multiple_stmts */
cliRC = SQLMoreResults(hstmt);
/* ... process result-set of second SELECT statement ... */

/* Issuing the INSERT statement of multiple_stmts */
cliRC = SQLMoreResults(hstmt);
/* cliRC is set to SQL_NO_DATA_FOUND to indicate that */
/* there are no more SQL statements to issue */

When a list of SQL statements is specified on the same statement handle, only one statement is issued at a time, starting with the first statement in the list. Each subsequent statement is issued in the order it appears.

To issue SQL statements with parameter markers, see Binding parameter markers in CLI applications.
To capture and convert SQL statements dynamically executed with CLI (dynamic SQL) to static SQL, see Creating static SQL by using CLI/ODBC static profiling.