A simple C inventory program using embedded SQL - IBM DB2

To illustrate the basic fundamentals of building a C/C++ application, let us develop a simple C inventory program for a company to store inventory information about its products, and the associated quantity and location of each product.

For simplicity, we work with a single table to manage the inventory for the company. You need to create the SAMPLE database provided with DB2 Express-C for this program, because it will interact with the INVENTORY table.

The application interacts with the user to perform the following operations:

  • Enter a new product:
  • – New products need to be entered into the database. For a new product, its product ID/barcode, quantity on hand, and location informatio need to be added to the INVENTORY table.
    – An error will occur if the product already exists in the database.
  • Update quantity/Update location:
  • – Any changes in the quantity or location of a product need to be reflected in the table.
    – An error will occur if the product does not exist in the database.
  • Get information about a part:
  • – The user can query the database for a product to get information about the quantity at hand or its location.
    – An error will occur if the product does not exist in the database.

The INVENTORY table
The INVENTORY table in the SAMPLE database consists of three columns named PID, QUANTITY, and LOCATION. They store the ID/barcode, quantity on hand, and the location of a product. The PID column is the unique identifier for a product and must be distinct within the table. By default, the INVENTORY table in the SAMPLE database is populated with the records shown below.

Sample data from the INVENTORY table

Sample data from the INVENTORY table

Precompiler source file extensions
To start off, we need to give the C program the appropriate file extension, which the precompiler can recognize. C/C++ precompilers are needed to convert embedded SQL statements in each C/C++ source file to DB2 Runtime API calls to the DB2 engine.

Unless otherwise specified by the BINDFILE option, a package is generated in the target database during precompile time and is needed for SQL to be executed. A package is a database object containing sections, which correspond to embedded SQL statements in a source program module. A section contains a compiled/optimized SQL statement.

The precompiler can optionally defer the creation of a package and create a bind file, which contains all the information needed to create a package. To create a package, the bind file will need to later be manually bound to the database. This is known as deferred binding. Deferred binding provides more flexibility because applications only need to be precompiled once (without a package being created), but can be bound against multiple databases later to create a package using the one bind file. This means the same application can access several databases, instead of just the one database it was precompiled against.

Once a package is created, optimizer access plans are created for static embedded SQL statements from the current database statistics during bind time. Dynamic embedded SQL statements will get an access plan created during runtime.

Below it lists the C/C++ source file extensions required by the precompiler, along with the file extensions given to the modified source output files.

Precompiler file extensions

Precompiler file extensions

If we name our example C program inventory.sqc, and precompile the program once it is finished, this produces a modified source file with the file extension of .c. The OUTPUT option can be specified in the PREP/PRECOMPILE command to override the name of the modified source file.

Inventory program code template
We start off with a simple code skeleton of the application. This C program prompts the user for an inventory operation and calls the appropriate functions to insert (add_product()), update (update_product()), or perform queries (query_update()) against the INVENTOR table in the SAMPLE database. Applications can be written to contain both static and dynamic embedded SQL statements. For demonstration purposes, this application contains both types.

Code template of inventory.sqc

/****************************************************** ** Source File Name: inventory.sqc ** ** This simple C program will manage the inventory of ** products for a company. ** *******************************************************/

Dynamic embedded SQL is used to update information about a product. The user can choose to update the quantity or location of a particular product. The application will prompt the user to specify which piece of information about a product they wish to update. The complete SQL statement will not be known at precompile time because the column of the table (either QUANTITY or LOCATION) to be updated is not known. The column of the table to be updated will only be known at runtime once the user supplies the data. Only at that time can the SQL statement be built and submitted to the database fo processing.

Static embedded SQL is used in adding a new product and querying for information about a product, because the complete SQL statement will exist at precompile time. The system will provide a menu of basic inventory operations to the user, and look like below code.

Menu of inventory operations

------------------------------------------------- SIMPLE INVENTORY MANAGEMENT SYSTEM 1. Add New Product 2. Update Product Info (Quantity or Location) 3. Query Product 4. Exit Enter option:

Host variable declarations
Host variables used in static SQL statements are declared in a DECLARE SECTION. For example, let us declare the static host variables needed for our program, see below.

Host variable declarations

The EXEC SQL portion of the declaration is needed to indicate the beginning of an embedded SQL statement and must be terminated by a semicolon. The normal form is:

In the INVENTORY table in the SAMPLE database, the PID and LOCATION columns are of type VARCHAR(10) and VARCHAR(128), respectively. In the above declaration, we have defined the size of those columns to be of one more character to take into account the null terminator. This will ensure that returned data values from the database do not get truncated. It is permissible to have more than one DECLARE section in a source file, although all host variables declared must be distinct within the source file.

After declaring the host variables, we have the option of using them as input or output variables in an SQL statement. Example shows the permissible usage of host variables within an SQL statement.

SQL Statement with both input and output host variables

The input host variable for the above statement is PID and a value for it will be supplied to the database by the application. The outpu host variables are QUANTITY and LOCATION, and its data values will be returned from the database to the application. In both cases, a colon (:) must precede the host variable name.

Using db2bfd to display host variable declarations
If a bind file is created during precompile time, we can use a DB2 bind file description tool named db2bfd to look at the contents of the bind file. This may be useful for debugging and can display information such as SQL statements or host variables declared. Below it lists the db2bfd command options.

db2bfd command options

db2bfd command options

For example, let us precompile the source file and add the option to create a bind file by running the command:

db2 prep inventory.sqc bindfile

This will produce a bind file named inventory.bnd. Running the command db2bfd -v inventory.bnd provides CLP output as shown below.

db2bfd sample output

Example-db2bfd sample output

Using db2dclgn to generate host variable declarations
DB2 can simplify development by automatically generating host variable declarations for a specified database table. In the command will generate an .h output file with the same name as the table. It will contain the host variable declarations.

db2dclgn example

Connecting to a database
Let us connect to the database within the application and start implementing the operations in our application. To connect to the database, we use the CONNECT TO statement. We add the following SQL statement into the main function of our program.

Alternatively, we can choose to connect to the database using a particular user ID and password:

If this method is chosen, host variables for the user ID and password need to be declared for use in the CONNECT statement. In our simple inventory application, we just connect to the SAMPLE database using the default user ID.

Disconnecting from a database
To disconnect from the database, we can add the following statement at the end of our program.

The SQL Communications Area (SQLCA)
The SQL Communications Area (SQLCA) is a DB2 data structure useful in obtaining information pertaining to the processing of an SQL statement or an API call. As the SQLCA is updated by the database manager after each SQL statement, it is important we examine the structure to ensure that any unexpected errors will be handled by the application.

The SQLCA structure provides important information such as:

  • SQLCODE:
  • This is an integer return code indicating whether the most recent SQL statement processed was successful or not. The value is 0 if successful, 100 if a query yields no results, positive if a warning occurred, and negative if the processing was unsuccessful and an error was returned.
  • SQLSTATE:
  • This is a five digit character length field that contains a return code from the last SQL statement processed. The return code is consistent with the SQL Standard.
  • SQLERRD:
  • This is an integer array containing six pieces of information related to the last SQL statement processed that can be useful when an error occurs.

To define the SQLCA, we can add the following statement to our program:

Alternatively, we can include the SQLCA header file and use it to declare an application variable:

If we had additional source files, we can add the following lines to the other source files:

Quick SQLCA example
Let us see a quick example of how we can use the SQLCA to get information related to the processing of an SQL statement. Let us connect to the database with an incorrect database name using the following statement:

EXEC SQL CONNECT TO sampsle;

Let us also add the following lines in the code to check whether the database connection was successful using the SQLCA:

We then precompile, compile, link, and run the program. The following is returned by the application:

Database connection error occurred. Sqlcode=-1013

For debugging and illustrative purposes, we can check out the fields of the SQLCA to see what information is provided. Normally, not all SQLCA fields are needed to diagnose an application problem. We print the fields of the SQLCA as shown below.

Code to print the SQLCA fields

This produces the output shown below :

Sample output of values stored in the SQLCA

sqlcaid: SQLCA ê sqlcabc: 136 sqlcode: -1013 sqlerrml: 7 sqlerrmc: SAMPSLE sqlerrd[0]: 0 sqlerrd[1]: 0 sqlerrd[2]: 0 sqlerrd[3]: 0 sqlerrd[4]: 0 sqlerrd[5]: 0 sqlwarn: 42705 sqlstate: 42705

Looking up the SQLCODE and SQLSTATE, we discover that the problem resulted because the database manager could not find the database specified in the application. Alternatively, we can use a DB2 API called sqlaintp defined in sql.h to help get more information about the error. This function will return the error text for an SQLCODE in the SQLCA. An example of using this function is shown below.

Using the sqlaintp API to get error info

The function call above retrieves the SQLCODE error message and stores it in a buffer named errText. As input to the function, we provide the string buffer, which stores the error message (errText), the size of the string buffer (999), the maximum width of each line of the message text ('0' means no line breaks needed in the text), and the pointer to the SQLCA structure (sqlca).

The function call will generate the following message:

SQL1013N The database alias name or database name "SAMPSLE" could not be found. SQLSTATE=42705

After all this debugging, it appears the name of the database specified in our code was incorrect. The line needs to be corrected to:

EXEC SQL CONNECT TO sample;

Inserting data
The users of the inventory system in our example need to be able to add a new product into the database. The user might have the session shown below

Sample session adding a product into inventory program

When the user chooses the option to add a new product, this will invoke the add_product() function within the application. We need to write an INSERT statement to add a record to the INVENTORY table using the values supplied by the user. The complete definition of the add_product() function is shown below.

Complete definition of the add_product() function

In above code , we prompt the user for information on the product to be added and populate the host variables with the supplied values. We then use the host variables in the INSERT statement and check whether the processing was successful.

Note that we add a COMMIT after the execution of the INSERT. This is because when a database connection is initially established, the application starts a transaction with any executable SQL statement such as SELECT, INSERT, CREATE, GRANT, and so on. Within the transaction, any number of SQL statements can be issued. This transaction is considered to be an atomic unit of work where either all or none of the changes within a transaction are made. To end the transaction, a COMMIT or a ROLLBACK must be issued. Issuing a COMMIT will apply all the changes to the database, whereas issuing a ROLLBACK cancels any changes to be made to the database.

Retrieving data
There are multiple ways to retrieve data from a database. Depending on whether a single row or multiple row result set is returned, this determines whether a cursor will be used. A cursor is a mechanism that is used to process each row of a result set.

For example, assume we want to find all the products available at the store in our INVENTORY table. Because it is possible that more than one row is returned from the query, we use a cursor to retrieve and process the rows. Below code shows how to use a cursor.

Using a cursor

In above code, a cursor named c1 is declared to hold a resultset of product IDs. To use the cursor, the cursor is opened and data is retrieved into the PID host variable. Once we are finished processing the resultset, the cursor should be closed and a COMMIT executed to release any resources held.

In the inventory program we are developing, we need to retrieve information about a single product. Because products are unique within the INVENTORY table, we will only be retrieving one row back from the database (if the product exists). This means that instead of a cursor, we can use a SELECT INTO statement which will directly populate the host variables. A sample session from querying a product is shown below.

Sample session querying a product in the inventory program

------------------------------------------------- SIMPLE INVENTORY MANAGEMENT SYSTEM 1. Add New Product 2. Update Product Info (Quantity or Location) 3. Query Product 4. Exit Enter option: 3 ------------------------------------------------- Query A Product Enter Product ID : 101-101-10 Found Quantity : 50 Location : Warehouse -------------------------------------------------

Complete definition of the query_product() function

Indicator variables
When a value is nullable, an application needs to check whether the value of a column returned is null or not. To do this using embedded SQL, we can use indicator variables. For example, if we want to check whether the quantity and location data value returned from the database is null, we can modify the statement in above code to the one shown in below code.

Using indicator values to check nullability

The INDICATOR keyword and the indicator host variable are added after each host variable to be checked. We need to ensure that the indicator host variables ind1 and ind2 are declared in the DECLARE section of the program. As the indicator variable is of SMALLINT data type, the C data type can be declared to be short. An indicator host variable with a value of -1 is null, any other value is considered not-NULL. The INDICATOR keyword is optional. Below code shows the same SQL statement without the INDICATOR keyword.

Using indicator without INDICATOR keyword

The WHENEVER Statement
Within an application, we can specify how to handle certain SQL conditions such as if an SQL error, warning, or a not found condition (SQLCODE of +100) is returned from the database.

There are three main types of the WHENEVER clause that can be in an embedded SQL statement:

EXEC SQL WHENEVER NOT FOUND <action> EXEC SQL WHENEVER SQLERROR <action> EXEC SQL WHENEVER SQLWARNING <action>

The possible actions are CONTINUE, GOTO <label>, or GO TO <label>.we added WHENEVER clauses into our query_ product() function. If a NOT FOUND condition occurs, we will print out the appropriate error message. Otherwise, we print a more generic error message.

Preparing SQL statements
We have been working with embedded static SQL statements in the add_product() and query_product() functions in our sample inventory program. However, for the inventory operation where a column is to be updated, we need to use embedded dynamic SQL and dynamically prepare the SQL statement.

This will finally generate an executable access plan in the package. Once that is done, we can execute the prepared SQL statement or declare a cursor to handle the result set. For example, let us say we wanted to delete a record from the INVENTORY table. Once we prepare the SQL statement, we can execute the statement multiple times using different parameter marker values but the same application package.

Preparing a DELETE statement

Assuming we have declared the deleteStmt and value host variables in a DECLARE section, the code fragment in below code prepares a statement with a single parameter marker. The prepared statement then gets executed multiple times using different data values. It is important to remember that a COMMIT must be issued in order for the changes to the database to occur. You might notice that preparing the DELETE statement in above code is quite unnecessary, because it can be done using static embedded SQL and host variables since we know the complete SQL statement. It can be rewritten as below.

Rewriting Updating a column using EXECUTE IMMEDIATE as a static SQL statement

If a statement can be written as static SQL, you might want to consider avoiding preparing a statement dynamically using parameter maker to avoid run-time costs incurred with a PREPARE. However, if static SQL is run on databases, which are continuously changing (which means the statistics keep changing), dynamic SQL might be more suitable. Also, dynamic SQL offers more flexibility than static SQL has to offer. In your application development, you need to keep these things in mind and choose your approaches wisely.

If an SQL statement does not contain host variables, nor parameter markers, and it does not return a result set, then the EXECUTE IMMEDIATE statement can be used. This statement prepares and executes a statement in one step. For example, in our inventory program, we need to update information about a product in the INVENTORY table as specified by the user. Because the column to be updated is unknown prior to precompile time, dynamic SQL must be used and we need to prepare the statement. Once we get the column to be updated and its value from the user, we can build the SQL string and then use the EXECUTE IMMEDIATE statement to prepare and execute in one step. This is shown in below code . A sample user session is shown in below code.

Updating a column using EXECUTE IMMEDIATE

Sample session updating a product in the inventory program

------------------------------------------------- SIMPLE INVENTORY MANAGEMENT SYSTEM 1. Add New Product 2. Update Product Info (Quantity or Location) 3. Query Product 4. Exit Enter option: 2 ------------------------------------------------- Update A Product 1. Update quantity 2. Update location Enter option: 1 Enter product ID to update: 101-101-10 Enter new quantity : 20 Product successfully updated. -------------------------------------------------

Complete C inventory program
After adding the data retrieve and manipulation code into the inventory program template shown in below code , we have a complete C inventory program as shown below

inventory.sqc

/****************************************************** ** Source File Name: inventory.sqc ** ** This simple C program will manage the inventory of ** products for a company ** *******************************************************/ /****************************************************** ** This function will add a product into the ** INVENTORY table. It demonstrates how to write a ** simple static SQL statement and work with the ** SQLCA to get back information on the processing of ** an SQL statement *******************************************************/

The SQL Descriptor Area (SQLDA)
The SQL Descriptor Area (SQLDA) is a structure that can provide information about dynamic SQL statements that are prepared and executed. With DESCRIBE, PREPARE, OPEN, FETCH, CALL, and EXECUTE statements, an application can retrieve information about the prepared statement, or input and output parameters of the SQL statement. In PREPARE and DESCRIBE, an SQLDA provides information to an application program about a prepared statement. In OPEN, EXECUTE, FETCH, and CALL, an SQLDA describes host variables.

The SQLDA is made up of header and SQLVAR describing its structure. Within it, there can be multiple SQLVAR elements defined. An individual SQLVAR structure can hold information regarding:

  • A column of a table in a DESCRIBE or PREPARE statement
  • A variable in a FETCH, EXECUTE, CALL, or OPEN statement

The fields, which describe the SQLDA, contain information on the size of the structure, whether the SQLDA has doubled in size (which will happen if LOB objects are used), the number of SQLVAR elements allocated, and the number of columns/variables in the SQL statement.

Depending on whether the SQLDA is being used to store input or output information of an SQL statement, SQLVAR elements can contain information about data types, lengths, column names, code pages, and much more. To define the SQLDA, we can add the following statement to our program:

Alternatively, we can include the SQLDA include file and declare an application variable to make use of the structure:

Once we define the SQLDA structure, we can use it within SQL statements to store input or output information as needed. For example, we can use it within a prepare statement where <sqlstatement> is a variable storing a dynamic SQL statement:

The above statement will prepare and store information into the SQLDA structure about the dynamic SQL statement specified in the statement. This is equivalent to the following two statements:

Note that the example above only describes the output rows of a resultset or in the case of a stored procedure CALL, the INOUT and OUT arguments. To get information about input parameter markers, you need to add an INPUT INTO

:*myInputSQLDA clause.

Let us try an example and see what is stored in the SQLDA. We include the sqlca.h header file, declare the mysqlda variable in our code, and define the variables.

Setting up the SQLDA

In above code, we define a buffer named test to store the SQL string that we will be working with. Also, we allocate an SQLDA structure which will hold two SQLVAR elements by using the SQLDASIZE macro, and as required, set a field named sqln which stores the number of SQLVAR elements needed. We defined 2 SQLVAR structures to be used for our application because we will be working with at most two table columns.

Next, we assign test to hold our SQL statement. We will be querying for the product ID (PID) field from the INVENTORY table. We prepare the statement, using the SQLDA to hold information about the column being returned, and subsequently print fields of the SQLDA structure.

Printing SQLDA Fields

Running this produces the following output.

Sample output of values stored in the SQLDA

Number of columns(sqld)= 1 Number of SQLVARs(sqln)= 2 Column name (sqlname.data)= PID Column name length (sqlname.length)= 3 Column datatype (sqltype)= 448 Column length (sqllen)= 10

The result in above code informs us that there are two SQLVAR elements defined (sqln), and one column in the resultset returned (sqld). The name of the column (sqlname.data) is PID which is of length (sqlname.length) 10 and of type (sqltype) SQL_VARCHAR (448).

Although we defined two SQLVAR elements, we only really needed one SQLVAR element because there is only one column returned by the SQL statement. In the PREPARE of a SELECT statement, ideally the number of SQLVAR elements defined should be equal to the number of columns expected from the result set. If more SQLVARs are defined than is needed, more memory would be unnecessarily used. If fewer SQLVARs are defined, there will not be enough SQLVAR elements to describe all the columns being returned.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM DB2 Topics