Tutorial 5: Embedded SQL

In the previous tutorial we created a schema called "craig" and then connected using the schema's credentials. Once we were connected we created a table and inserted some data into it. The steps were as follows:

SQL> connect craig/craig
SQL> create table TEST (CITY varchar2(32), POPULATION number);
SQL> insert into TEST (CITY, POPULATION) values ('edmonton', 10);
SQL> insert into TEST (CITY, POPULATION) values ('vancouver', 20);
SQL> commit;
SQL> select * from test;

This was all done from the sqlplus executable. However what we really want to do now is query for that same information but inside a C++ program. To do so we are going to use what is called embedded SQL.


Embedded SQL

Embedded SQL allows us to use SQL statements inside our C++ code. We can then query information from the database and store the results in variables inside our C++ program.

For this tutorial I will start with a small program that connects to the database and issues the same "select * from test" statement that was done in the example above. This time instead of the results going to the screen they will go into arrays which we then output using cout.

////////////////////////////////////////////////////////////////////////////////
// Filename: main.pc
////////////////////////////////////////////////////////////////////////////////

//////////////
// INCLUDES //
//////////////
#include <sqlca.h>
#include <string.h>
#include <iostream>
using namespace std;

EXEC SQL INCLUDE sqlca;

int main()
{
    // Declare the connection string variable.
    EXEC SQL BEGIN DECLARE SECTION;
        char connectString[64];
    EXEC SQL END DECLARE SECTION;

    // Copy the connection data into the string.
    strcpy(connectString, "craig/craig@craig");

    // Connect to the database.
    EXEC SQL CONNECT :connectString;

    // Check if the connection was succesful.
    if(sqlca.sqlcode < 0)
    {
        cout << "Could not connect." << endl;
    }
    else
    {
        cout << "Connected." << endl;
    }

    // Declare the variables for the table that we are going to query from.
    EXEC SQL BEGIN DECLARE SECTION;
        VARCHAR city[32];
        int population;
    EXEC SQL END DECLARE SECTION;

    // Create and open a cursor to issue the select statement.
    EXEC SQL DECLARE test_cursor CURSOR FOR SELECT * FROM TEST;
    EXEC SQL OPEN test_cursor;

    // Grab the first row.
    EXEC SQL FETCH test_cursor INTO :city, :population;

    // Loop until there is no more data to fetch.
    while((sqlca.sqlcode >= 0) && (sqlca.sqlcode != 100) && (sqlca.sqlcode != 1403))
    {
        // Null terminate the string.
        city.arr[city.len] = '\0';

        // Display the result for this row.
        cout << "City: " << city.arr << endl;
        cout << "Population: " << population << endl;
        cout << endl;

        // Fetch the next row.
        EXEC SQL FETCH test_cursor INTO :city, :population;
    }

    // Close the cursor.
    EXEC SQL CLOSE test_cursor;

    return 0;
}

As you can see the program is pretty straight forward and does exactly what the example at the top of the page does. We now have access to the database information inside the C++ program.

However this is not a true C++ program and so it requires some pre-processing to strip out the embedded sql and replace it with proper structs, data types, and functions calls. To do this we need to use the Pro*C compiler to produce a proper C++ source file that we can then compile normally.


Pro*C Compiler

When you installed the oracle binaries it also installed the Pro*C compiler. It installs it in the $ORACLE_HOME/bin/ directory (on my current system it was located here: /ora/base/product/11.2.0.3/bin/proc).

To use the compiler you need to first set your oracle environment to the database (in the previous tutorials the database was named craig):

$ . oraenv
ORACLE_SID = [craig] ? craig

Now if you type in "which proc" it should show you the location of the binary correctly and allow you to invoke it.

Now to compile the above small program you will need to do the following. First notice I named it main.pc and not main.cpp. This is because the .pc extension means it is a Pro*C file (or Pro*C++). To compile it I execute the following:

$ proc INAME=main.pc CODE=CPP PARSE=NONE CPP_SUFFIX=cc

This will produce a main.cc program which now contains all the replaced embedded sql with the actual C sytle structs, data types, and function calls need to communicate with the oracle database. Now to finally compile the main.cc program you can execute the following using the correct includes and library locations:

$ g++ main.cc -I $ORACLE_HOME/precomp/public/ -L $ORACLE_HOME/lib -lclntsh -lsql11

On my system the ORACLE_HOME was /ora/base/product/11.2.0.3/ so it translated to "g++ main.cc -I /ora/base/product/11.2.0.3/precomp/public/ -L /ora/base/product/11.2.0.3/lib -lclntsh -lsql11". This then created the a.out program which I ran and it displayed the two rows from the database.

Back to Tutorial Index