Creating a C Application in HP-UX

This section describes how to create a simple C application that accesses the Albums table in MUSICDBE. The application will prompt the user for an album code, and it will display the title, recording company, and any comment on the album. Before you start, make sure you have created the DBEnvironment MUSICDBE according to the instructions in the tutorial "Setting up a Database with ISQL" earlier in this book.

First, change into the directory in which you have created the DBEnvironment MUSICDBE. Then, use the following command to copy the template file TMPLC1 into your current working directory:

   $ cp /usr/lib/allbase/hpsql/programs/TMPLC1 showalbum.sql Return

Change the permissions on the file as follows to permit editing:

   $ chmod 644 showalbum.sql Return

Use an editor to examine this file. At several locations in the file, you will see notations like the following:

   /******** Insert INCLUDE statement here *******/

In the sections that follow, you will insert specific sequences of SQL and/or C statements following these comments in the template. Follow instructions carefully.

Coding the INCLUDE Statement

The first SQL code to be embedded in the template is an INCLUDE statement:

   EXEC SQL INCLUDE SQLCA;

Insert this line in the template file so that the result looks like the following:

   /******** Insert INCLUDE statement here *****************/



   EXEC SQL INCLUDE SQLCA;



   /********************************************************/

The SQLCA is a communications area in which information is passed from ALLBASE/SQL to your application at run time. The SQLCA includes a variable known as sqlcode, which can be examined to determine whether or not a particular SQL statement was successful (zero) or unsuccessful (non-zero).

Coding the Host Variable Declarations

The variables which transfer data between your application and the DBEnvironment are known as host variables. You can use these variables both inside SQL statements and in ordinary program statements. When used in an SQL statement, a host variable must be preceded by a colon (:).

You must declare host variables inside a host variable declaration section. This is simply an area of code that begins with a BEGIN DECLARE SECTION statement and ends with an END DECLARE SECTION statement. In the template file, insert the following host variable declarations following the words "Insert Host Variables".

   /******** Insert Host Variables here ********************/



   EXEC SQL BEGIN DECLARE SECTION;

   int Albumcode;

   char Albumtitle[41];

   char Recordingco[11];

   char Comment[81];

   sqlind Commentind;

   EXEC SQL END DECLARE SECTION;



   /********************************************************/

This example shows the use of a special indicator variable Commentind of data type sqlind accompanying the Comment variable. You declare a variable of this kind to accompany each column that permits null values. If a null is encountered, the indicator variable is set to -1. If the column does not contain a null, the indicator variable is set to 0.

Coding the CONNECT and RELEASE Statements

As in ISQL, you must CONNECT to the DBEnvironment before you can perform queries against a database. In the template file, code the following statement to perform the CONNECT:

   /******** Insert CONNECT statement here *****************/



   EXEC SQL CONNECT TO 'MUSICDBE';



   /********************************************************/

Similarly, code the following statement to perform the RELEASE:

   /******** Insert RELEASE statement here *****************/



   EXEC SQL RELEASE;



   /********************************************************/

Note that you do not need to mention the name of the DBEnvironment in the RELEASE statement.

Coding the SELECT Statement

The main routine in the sample program contains the following SELECT statement, which you should include in the template following the tag, "Insert Select-Data." Be careful of the capitalization of host variable names. C is case-sensitive, but SQL is not. Therefore, only the host variable names in the following code are case-sensitive:

   /******** Insert SELECT Statement here ******************/



   EXEC SQL SELECT ALBUMCODE, ALBUMTITLE, 

   RECORDINGCO, COMMENT INTO :Albumcode, :Albumtitle,

   :Recordingco, :Comment :Commentind

   FROM ALBUMS WHERE ALBUMCODE = :Albumcode;



   /********************************************************/

This statement performs a query on the Albums table. Inside the SQL statement, a colon precedes each host variable. Elements that are not preceded by a colon are database objects--the table name in the FROM clause, and column names in the SELECT list and the WHERE clause. Note the placement of the indicator variable :Commentind immediately following the host variable :Comment with no separation by a comma. During the SELECT, the indicator variable is set to -1 if the content of the corresponding column (Comment) is null.

Coding Statements to Display Data

After the query has extracted information from the database, you need to present the data to the user in some fashion. One way to do this is to use a printf statement to write the properly formatted data to the standard output. You can use the C format string to justify data in particular ways, to include literals, or to truncate the output.

The following statements should be inserted into the template file to display the output of the SQL SELECT statement:

   /******** Insert Display statements here ****************/



   printf("        Album Title: %s\n",Albumtitle);

   printf("       Recording Co: %s\n",Recordingco);

   if(Commentind == -1) sprintf(Comment,"");

   printf("            Comment: %s\n",Comment);



   /********************************************************/

If the value in the Comment column in the Albums table is NULL, the indicator variable (Commentind) will be set to -1. In this case, you initialize Comment, as the example shows.

Using the Preprocessor

After including all the embedded SQL statements, save the file as showalbum.sql. Then preprocess the file, using the following command:

   $ psqlc MUSICDBE -i showalbum.sql -d

If the preprocessor has found SQL errors, including syntax errors, they will be included in a file called sqlmsg, which you should examine. (Most C syntax errors are not included; these are displayed later, at compile time.) You must correct any preprocessing errors before going on to the next step. The most common SQL errors come about because of misspelling of column names or host variable names. Another common cause of problems is omitting punctuation, such as commas in a SELECT list, colons before host variables, or semicolons at the end of each SQL statement.

When the preprocessing has completed without error, display the program files in the current directory (file sizes may differ from those shown in this example):

   $ ll showalbum* Return

   -rw-rw-r--   1 peter    dbusers     4030 Oct  3 14:02 showalbum.c

   -rw-rw-r--   1 peter    dbusers     1626 Oct  3 14:01 showalbum.sql

   -rw-rw-r--   1 peter    dbusers      323 Oct  3 14:02 showalbum.sqle

   -rw-rw-r--   1 peter    dbusers     2000 Oct  3 14:02 showalbum.sqlm

   -rw-rw-r--   1 peter    dbusers     1745 Oct  3 14:02 showalbum.sqlt

   -rw-rw-r--   1 peter    dbusers      240 Oct  3 14:02 showalbum.sqlv

Notice that the preprocessor has created several new files, each with a different extension. The file with a .c extension is the modified source file which you will soon compile. The files with .sqlv, .sqlt, and .sqle extensions contain type definitions and variable declarations required for compiling and linking the program. Take a few moments to examine the content of these files with an editor.

The file with a .sqlm extension contains a module which could be installed in another DBEnvironment. This is a binary file which is used with the ISQL INSTALL command.

Compiling and Linking the Program

Use the following command to compile and link the modified C source program that was generated by the preprocessor:

   $ cc showalbum.c -o showalbum.r -lsql -lcl -lportnls  Return

This command runs the C compiler and the HP-UX linker to create an executable program showalbum.r. If there are any compile or link errors, you must make corrections in the original source file, that is, showalbum.sql, then you must repreprocess and recompile.

When the program compiles and links without error, you can execute it as in the following example:

   $ showalbum.r Return

   Enter an Album Code: 2008 Return

           Album Title: Franz Schubert: Lieder

          Recording Co: atlantic

               Comment:

The example assumes you have loaded the Albums table according to the instructions in "Setting Up a Database with ISQL." Run the program again, using different album codes between 2001 and 2010.

Debugging the Program

After the program has compiled without error, you can perform ordinary debugging tasks by running the program and observing the results. Remember, though, that you must edit the embedded SQL source file, not the preprocessor output, when you modify the program. If you edit the modified source file generated by the preprocessor, your changes will be lost the next time the program is preprocessed, since the preprocessor will overwrite that file.

Once the program runs correctly, skip ahead to the section entitled "Adding a Multiple Row SELECT Statement."




	NOTE: A completed source file for `showalbum` appears in /usr/lib/allbase/hpsql/programs/TMPLCA. You can check your code against this solution.

Summary of Steps

Here are the steps you have just carried out to prepare an embedded SQL application program:

Edit the Source File
Preprocess the Source File
Compile and Link the Modified Source File
Run the Executable Program