Introduction to data access methods in embedded SQL

This article is an introduction to using the data access SQL facilities provided in the XL C/C++ compilers. It guides you through a simple SQL query then continues on to more complex use cases. The steps in this article are targeted and tested with DB2 V9 – V10 and XL C/C++ z/OS V1R13 – V2R1.

Share:

Igor Todorovski (itodorov@ca.ibm.com), Software Developer, IBM

Author1 photoIgor Todorovski is a software developer in the IBM XL Compilers group. He has been with IBM since 2008 and specializes in z/OS C/C++ compilers. Igor is also the author of the article “Error handling in embedded SQL for C/C++ on IBM z/OS systems.



Francesco Cassullo (cassullo@ca.ibm.com), Software Engineer , IBM

Author photoFrancesco Cassullo is a software engineer in the IBM XL Compilers group. He has been with IBM since 2008 working on the Fortran compiler for AIX/Linux/BlueGene, and the C/C++/COBOL compilers for z/OS. Francesco is also the author of the article “Error handling in embedded SQL for C/C++ on IBM z/OS systems.



28 January 2014

Also available in Chinese Russian Spanish

Background

IBM's XL C/C++ compiler for z/OS supports the use of embedded SQL statements in C/C++ programs. This article assumes the use of the XL C/C++ DB2 coprocessor. It describes the various methods of interacting with SQL data in an embedded SQL XL C/C++ program on z/OS.


Retrieving data from SQL using host variables

The most basic method of retrieving data from a SQL table is through the use of Host Variables. Host variables allow a C/C++ program to communicate with DB2.

Example: Host variables

Note: The examples throughout the article refer to the table of students in Listing 1.

Listing 1. Table of students
EXEC SQL CREATE TABLE Students                                              
(
   STUDENTNO INT NOT NULL,
   FIRSTNAME VARCHAR(12) NOT NULL,
   LASTNAME  VARCHAR(15) NOT NULL,
   DEPARTMENT CHAR(3) NOT NULL,
   PHONENUMBER CHAR(7),
   AGE  SMALLINT,
   GPA  FLOAT(5)
);

To retrieve, update, delete, or insert data into or from a SQL table, you must first declare a host variable in the application. A host variable is a C/C++ variable that is typically of a C/C++ type such as int, float, char, etc.

The XL C/C++ DB2 coprocessor stores the host variable data into a database request module (DBRM) dataset or Hierarchical File System (HFS) file.

Declaring host variables

Host variables are defined in a declare section block. Declaring a host variable indicates to the coprocessor that it can retrieve and feed data to or from SQL statements. Variables not declared in a declare section are not accepted in SQL statements.

EXEC SQL BEGIN DECLARE SECTION;
    int student_number;
EXEC SQL END DECLARE SECTION;

BEGIN and END declare section commands can be placed around any valid C/C++ declaration to denote the variable as a host variable.

Listing 2 queries the table Students for the student number of a "John Doe". The result is stored into the host variable student_number.

Listing 2. SQL query into a host variable
EXEC SQL SELECT STUDENTNO
       INTO :student_number
       FROM Students
       WHERE FIRSTNAME = 'John'
       AND LASTNAME = 'Doe';

Note: The host variable is preceded by a ':' character. This is the only way a host variable can be accessed within a SQL statement. Without it, the variable name is treated as a string literal. Additionally, the INTO clause is used to store the result of the SELECT statement into a host variable.

Host variables details

Host variables can be a standard type specifier, an array, or a structure. The same scoping rules apply as with other variables. Host variables can be used like any standard XL C/C++ variable, and additionally can be used in SQL statements.

Other acceptable host variable types include numeric, character, graphic, binary, LOB (Large Object), XML, and ROWID host variables. You can also specify a result set, table, LOB locators, and LOB or XML file reference variables. These host variable are more advanced and beyond the scope of this article.

Note: Not all XL C/C++ data types have SQL equivalents. For example, typedefs, long long, and register variables cannot be declared as host variables. For details on other supported types see the Application Programming and SQL Guide.


Real world SQL data access examples

Case 1. Insert a set of students into a table

Recall the Students table in Listing 1. In order to populate the table, you must insert a set of student records into the table. The table contains the following schema:

STUDENTNO → INT  
FIRSTNAME → VARCHAR(12)
LASTNAME  → VARCHAR(15)
DEPTARTMENT → CHAR(3)
PHONENUMBER → CHAR(7)
AGE → SMALLINT
GPA →  FLOAT(5)

Assume a school program that prompts for the student's information. The declare section for that program is defined as follows:

EXEC SQL BEGIN DECLARE SECTION;
    int student_number;
    char firstname[13];
    char lastname[16];
    char department[4];
    char phonenumber[8];
    short age;
    float gpa;
EXEC SQL END DECLARE SECTION;

Character fields require special attention. SQL statements accept only the following character representations:

  • Single-character form
  • Null-terminated character form
  • VARCHAR structured form
  • CLOBs (Character Large Objects)

For example, strcpy(firstname, "John"); is an example of a null-terminated character form.

Listing 3. Inserting students
EXEC SQL INSERT INTO Students
VALUES (:student_number, :firstname, :lastname,
        :department, :phonenumber, :age, :gpa);

Each host variable corresponds with a field in the table and populates that field with data.

Case 2. Select a student record into a host structure

A typical use case is to select an entire record. Instead of declaring a host variable for each field, the application can use a host structure. A host structure is similar in principle to a C struct, but it encapsulates a collection of host variables.

For the Students table, the structure is defined here:

EXEC SQL BEGIN DECLARE SECTION;
struct Student {
    int student_number;
    char firstname[13];
    char lastname[16];
    char department[4];
    char phonenumber[8];
    short age;
    float gpa;
} student_record;
EXEC SQL END DECLARE SECTION;

Now, you can reference the student_record host structure in a SELECT statement using:

EXEC SQL
  SELECT STUDENTNO, FIRSTNAME, LASTNAME, DEPARTMENT,
         PHONENUMBER, AGE, GPA
    INTO :student_record
    FROM Students
    WHERE STUDENTNO = 001;

Each element of the student_record host structure will be populated with the field values of the row associated with student number 001.

Case 3. Retrieve a list of the top 100 student GPAs

Now that the database is populated with a set of students, you can proceed to retrieve a list of student GPAs.

You can declare host variables as arrays using the following declaration statements:

EXEC SQL BEGIN DECLARE SECTION;
    float gpa_list[100];
EXEC SQL END DECLARE SECTION;

Use cursors to retrieve a list of student GPAs

To retrieve multiple rows in a single query, the application program must use a cursor.

A cursor points to a subset of rows determined by a SELECT statement. The application program can then fetch any number of rows from this subset. For example, assume a query that selects 1000 GPAs. These 1000 GPAs are stored into an intermediate result table. You can fetch any number of rows from this intermediate table into an array all at once.

Listing 4. Use cursors to fetch top 100 GPAs
EXEC SQL DECLARE STUDENTS_C1 CURSOR WITH ROWSET POSITIONING
  FOR SELECT gpa FROM STUDENTS WHERE 1=1 ORDER BY GPA DESC;
EXEC SQL OPEN STUDENTS_C1;
EXEC SQL FETCH NEXT ROWSET FROM STUDENTS_C1 FOR 100
  ROWS INTO :gpa_list;
EXEC SQL CLOSE STUDENTS_C1;

The first step declares a cursor named STUDENTS_C1 and associates it with the query:

 SELECT gpa FROM Students WHERE 1=1 ORDER BY GPA DESC;

The results of this query are stored into a result table. Once declared, open the cursor. This tells DB2 that it is ready to process the first row of the results from the select statement. Afterward, the fetch statement is used to retrieve the data from the result table. It allows you to control the number of rows retrieved and defines where the data is stored. Finally, the cursor is closed.

A subsequent fetch can be called to retrieve the next set of the top 100 GPAs from the result table.

The result of the cursor stores the GPA data into a host variable array, for easy retrieval.

for (int i = 0; i < 100; i++)
{
     printf("GPA: %f ", gpa_list[i]);
}

Indicator variables to identify null records

Although your query may ask for the top 100 GPAs, the table itself may contain less than 100 GPA's. If the Students table contains 10 records, then the loop will print unexpected values after the 10th iteration.

To get around this, DB2 provides indicator variables. Indicator variables contain a small integer value that indicates some information about the associated host variable.

Indicator variables can contain the following values:

  • 0 or a positive integer
    The value selected is not null.
  • -1
    The value selected is a null value.
  • -2
    A numeric string conversion error or a null result due to string conversion warnings
  • -3
    No value was returned
  • positive integer
    The value selected was truncated but is not null.

For example, if the indicator variable is an array and 10 elements were retrieved into an array of 100 elements, then the first 10 elements of the indicator variable would be set to 0.

To use the indicator variable, first declare it along with the gpa_list array. Indicator variables must be declared as a short.

Listing 5. Initializing indicator variables
EXEC SQL BEGIN DECLARE SECTION;
    float gpa_list[100];
    short gpa_indicator[100];
EXEC SQL END DECLARE SECTION;
for (int i = 0; i<100; i++) {  gpa_indicator[i] = -1; }

Each element in gpa_indicator is initially set to -1 to indicate that the host variable array is initially completely "null".

The fetch statement can be modified to include an indicator variable:

EXEC SQL FETCH NEXT ROWSET FROM STUDENTS_C1
  FOR 100 ROWS INTO :gpa_list :gpa_indicator;

Note, the indicator variable gpa_indicator follows the host variable gpa_list in the fetch statement. The indicator variable can only be used following a host variable.

The loop can now be modified to make use of the indicator and only print out rows that were successfully retrieved (omitting null variables) as shown below:

for (int i = 0; i < 100; i++)
{
    if (gpa_indicator[i] >= 0)
       printf("GPA: %f ", gpa_list[i]);
 }

Passing host variables as parameters

Host variables can be passed as parameters to user functions, but are treated as non-host variables in the local function scope. Additionally, you cannot surround a function parameter with a DECLARE SECTION block.

To work around this, you can declare a local host variable and assign it the value of the parameter (for simple types).

In such cases, a local host variable must be initialized with the host variable argument.

Listing 6. Passing host variable parameters
int selectGPA(int student_number)
{
EXEC SQL BEGIN DECLARE SECTION;
    // Initialize with a new local variable
    int student_number1 = student_number;  
    float gpa;
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT GPA
       INTO :gpa
       FROM STUDENTS
       WHERE :student_number1 = 12121;
}

int main()
{
EXEC SQL BEGIN DECLARE SECTION;
    int student_number;
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT STUDENTNO
       INTO :student_number
       FROM STUDENTS
       WHERE FIRSTNAME = "Bill"
       AND LASTNAME = "Doe";
selectGPA(student_number);
}

An alternative method is to declare the host variable globally.


Acknowledgements

The authors thank the following individuals who helped make this article possible: Zibi Sarbinowski, Jing Chen, and Kobi Vinayagamoorthy.


Downloads

DescriptionNameSize
C/C++ example source codesqlcode-jan143KB
C JCL example source codesqlcode_c3KB
C++ JCL example source codesqlcode_cpp3KB

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Rational software on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Rational
ArticleID=960937
ArticleTitle=Introduction to data access methods in embedded SQL
publish-date=01282014