Embedding SQL in a C/C++ Program

Introduction

To use SQL within a programming environment, the following basic steps must be followed:

1) Create the source code with embedded SQL statements.
2) Preprocess the source code with a preprocessor.
3) Compile the preprocessed code using the normal compiler to produce an object program.
4) Link-edit the object deck with required linkage modules.
5) Execute the program.

Creating the Source File

Use any text editor to create the source code for the program. The source file must have a proper extension for the host language you are using; for the Oracle C/C++ preprocessor, use the extension pc.
 

Embedded SQL Statements

How you embed SQL statements will vary depending on the language. Oracle provides preprocessors for C/C++ and COBOL. When used with embedded code, these languages are called host languages.

Embedded SQL statements must be identified by the pre-processor and replaced with standard host language statements. Special delimiters are used for this purpose. In C/C++, SQL statements are preceded with EXEC SQL and terminated with a semi-colon (;). For example:

Host Language Variables

When coding a program with embedded SQL statements, there are three types of variables used in the program.

The first type of variables are standard host language variables that are only used in host language statements - for example, in the sample C++ program GETSTOCK, the repeat_loop variable is used to control the user input loop. These variables are declared in the same manner as in a normal program.

The second type of variables are the SQL items - tables, indexes, columns etc. that already exist and are defined in the SQL catalogs. These variables are not declared in the program - their descriptions are obtained from the SQL data dictionary.

The third type of variable is the "host language variable". Host language variables are regular program variables that are also used in SQL statements. These variables must be declared in an SQL declaration section that is preceded by:

        BEGIN DECLARE SECTION

and terminated by:

        END DECLARE SECTION

These variables can be used in normal host language statements as well as in the SQL statements.

When using a host language variable in an SQL statements, the host variable name must be preceded by a colon (:). The colon distinguishes the host variable from the SQL identifiers (column names). When host language variables are used in regular host language statements, do not code the colon.

For example, the following code could be used in a C/C++ program to declare two host language variables, STOCKSYMBOL and STOCKPRICE:

EXEC SQL BEGIN DECLARE SECTION;
    char StockSymbol[4];
    float StockPrice;
EXEC SQL END DECLARE SECTION;

    cin >> StockSymbol;

and in SQL statements; for example, the SELECT statement:

    SELECT CURRENTPRICE INTO :STOCKPRICE
        FROM STOCK
        WHERE STOCKSYMBOL = :STOCKSYMBOL

To avoid conversion errors, host language variables must be declared with host language data types that are compatible with the SQL data types declared for the corresponding variables in the SQL tables. Use the table given in the section Declaring Host Variables for the appropriate data type declarations.



Declaring Host Variables in C/C++

Host language variables can consist of upper and lower case letters and the underscore. They must begin with a letter. Host variable names can be any length but the preprocessor only recognizes the first 31 characters in the name.
 

Host Variable Data Types in C/C++

Use the chart below to determine the appropriate data type to be used in your C/C++ program.

You can use the "pseudotype" varchar for variable length strings. Oracle provides a predefined struct that replaces the varchar declaration when the program is preprocessed. For example, the preprocessor replaces the code for:

        varchar dataname[n];

with:

struct {
unsigned short len;
unsigned char arr[n+1];
} dataname;

Code the datatype varchar in either upper or lower case; do not use mixed case.

When the variable is assigned a value by Oracle with a FETCH or SELECT, the length of the character string is placed in the member len; this value can then be accessed by C/C++ statements.
 

SQL Data Type	C/C++ Data Type
NUMBER(n)	int or long int
NUMBER(m,n)	float or double
CHAR(n)	if n =1, use char for n>1, use char[n+1] (allows for the null-terminator)
VARCHAR2(n)	varchar[n]
DATE	char[n] or VARCHAR[n]; value for n depends on the date format

SQL Error Handling

Every SQL application program must provide for error handling. This will involve declaring an SQL communications area and telling SQL what to do when an error is encountered.

The SQL communications area (SQLCA) can be included in your program by coding one of the following statements:

EXEC SQL INCLUDE SQLCA;
#include <sqlca.h>

For example, the following SELECT statement gets a row from the STOCK table:

EXEC SQL select stockname, currentprice
           into :stockname, :currentprice
           from stock
           where stocksymbol = :stocksymbol;

if (sqlca.sqlcode == 100)
    cout << "Stock symbol not found";

The SQLCA structure also contains the variable: sqlca.sqlerrm.sqlerrmc which contains the text associated with the error code.

The variable SQLSTATE can also be used for error checking. SQLSTATE is a five character null-terminated string that contains codes that follow the SQL-92 standards. SQLSTATE must be declared in the DECLARE section. For example:

        char SQLSTATE[6];
 

The WHENEVER Statement

Errors can also be detected implicitly using the WHENEVER statement. The format of the WHENEVER statement is:

        WHENEVER <condition> <action>

Condition: can be either an SQL warning (SQLWARNING) or an SQL error (SQLERROR)

Some of the choices for action are:

continue - continue processing
do function - execute the function
goto label - transfer to the indicated labeled statement
stop - stop execution and rollback any uncommitted work

Connecting to Oracle

The CONNECT statement is used to establish a connection to the Oracle server. The syntax is:

        CONNECT userid IDENTIFIED BY password USING dbstring

dbstring is the Net8 specification string used to connect to the database.

To disconnect from Oracle, use the following statement:

        COMMIT RELEASE;

The COMMIT statement commits all updates; the RELEASE option releases all resources held by the user and disconnects from the database.
 

The SELECT - INTO Statement

The SELECT statement finds the row or rows of the table specified in the FROM clause that satisfy the given search condition.

If the statement returns only one row from the table the following form of the SELECT statement can be used:

SELECT select-list
    INTO one-or-more-host-variables
    FROM table-name(s)
    WHERE search-condition

The results are delivered into the respective host variables in the INTO clause. If no rows satisfy the search condition, SQL returns a value 100 in SQLCODE ("not found").

For example, the following statement could be used to retrieve the price for stock JDS:
 

SELECT CURRENTPRICE
    INTO :STOCKPRICE
    FROM STOCK
    WHERE STOCKSYMBOL = 'JDS'

Cursor Management

If more than one row is to be retrieved by a SELECT statement, the retrieved rows are managed using a "cursor". A cursor is essentially a pointer into a set of rows in the database.

Several cursors can be defined in a program - each is associated with a specified SELECT statement. The rows retrieved by the cursor are referred to as the "active set" of the cursor.

For a retrieval using a cursor, the programming steps involved are as follows:

        DECLARE cursor-name CURSOR FOR select-statement

The cursor-name must be unique in the program and follows the same naming rules as for SQL table names. The INTO clause is not coded for the SELECT statement.

In the program LISTVAL, a cursor is used to retrieve entries from the HOLDING and STOCK tables. The cursor is declared as follows:

DECLARE VALCRSR CURSOR FOR
    SELECT HOLDING.STOCKSYMBOL, PURCHASEDATE, NUMBERSHARES,
        PURCHASEPRICE, CURRENTPRICE
    FROM HOLDING, STOCK
    WHERE HOLDING.STOCKSYMBOL = STOCK.STOCKSYMBOL
        AND PORTFOLIONO = :PORTFOLIONO

        OPEN cursor-name

To retrieve the data identified by the SELECT statement, the FETCH statement is used:

        FETCH cursor-name INTO host-variable-list

The FETCH statement will retrieve the next row in the active set. The retrieved data is placed in the variables indicated in the list in the INTO clause in the FETCH statement, matching by position. If no rows have been returned or all rows in the active set have been fetched, SQL returns a value 100 in SQLCODE.

In LISTVAL, the following statement is used to retrieve rows using the cursor:

FETCH VALCRSR INTO :STOCKSYMBOL,:PURCHASEDATE, :NUMBERSHARES,
    :PURCHASEPRICE, :STOCKPRICE

        CLOSE cursor-name
 

Preprocessing a Program

The preprocessor converts any embedded SQL statements to comments and generates the required function calls to replace the statements.

The preprocessor generates a modified source program. Errors detected at this stage will only include SQL errors; the host language syntax is not examined during the preprocess phase.
 

Compiling and Linking

The host language compiler and linker are used to create an executable file.