Application Building Guide

IBM VisualAge C++ for OS/2 Version 3

This section includes the following topics:

• DB2 CLI Applications
• DB2 CLI Stored Procedures
• DB2 API and Embedded SQL Applications
• Embedded SQL Stored Procedures
• User-Defined Functions (UDFs)

Note:

The VisualAge C++ compiler is used for both C and C++ sample programs supplied in the %DB2PATH%\samples\c and %DB2PATH%\samples\cpp directories. The same command files are in both these directories. They contain commands to accept either a C or C++ source file, depending on the file extension.

DB2 CLI Applications

The command file bldcli, in %DB2PATH%\samples\cli, contains the commands to build a DB2 CLI program. The parameter, %1, specifies the name of your source file.

This is the only required parameter, and the only one needed for CLI programs that do not contain embedded SQL. Building embedded SQL programs requires a connection to the database so three optional parameters are also provided: the second parameter, $2, specifies the name of the database to which you want to connect; the third parameter, $3, specifies the user ID for the database, and $4 specifies the password.

If the program contains embedded SQL, indicated by the .sqc extension, then the embprep command file is called to precompile the program, producing a program file with a .c extension.

@echo off
rem  bldcli command file - OS/2 
rem  Builds a CLI program with IBM VisualAge C++.
rem  Usage: bldcli prog_name [ db_name [ userid password ]]
 
if exist "%1.sqc" call embprep %1 %2 %3 %4 
if exist "%1.sqx" call embprep %1 %2 %3 %4
if "%1" == "" goto error
 
rem Compile the error-checking utility.
icc -C+ -O- -Ti+ utilcli.c
 
rem  Compile the program.
if exist "%1.sqx" goto cpp
icc -C+ -O- -Ti+ %1.c
goto link_step
:cpp
icc -C+ -O- -Ti+ %1.cxx
 
rem  Link the program.
:link_step
ilink /NOFREE /NOI /DEBUG /ST:64000 /PM:VIO %1.obj utilcli.obj,%1.exe,NUL,db2cli.lib;
 
goto exit
:error
echo Usage: bldcli prog_name [ db_name [ userid password ]]
:exit
@echo on

Compile and Link Options for bldcli
Compile Options: icc The IBM VisualAge C++ compiler. -C+ Perform compile only; no link. This book assumes that compile and link are separate steps. -O- No optimization. It is easier to use a debugger with optimization off. -Ti+ Generate debugger information
Link Options: ilink Use the ilink linker to link edit. /NOFREE No free format. /NOI No Ignore Case. Force case sensitive identifiers. /DEBUG Include debugging information. /ST:64000 Specify a stack size of at least 64 000. /PM:VIO Enable the program to run in an OS/2 window. %1.obj Include the object file. utilcli.obj Include the utility object file for error checking. %1.exe Specify the executable. NUL Accept the default value. db2cli.lib Link with the DB2 CLI library. Refer to your compiler documentation for additional compiler options.

To build the sample program tbinfo from the source file tbinfo.c , enter:

 
   bldcli tbinfo

The result is an executable file, tbinfo.exe. You can run the executable file by entering the executable name (without the extension):

   tbinfo

Building and Running Embedded SQL Applications

There are three ways to build the embedded SQL application, dbusemx, from the source file dbusemx.sqc :

1. If connecting to the sample database on the same instance, enter:

      bldcli dbusemx
   

2. If connecting to another database on the same instance, also enter the database name:

      bldcli dbusemx database
   

3. If connecting to a database on another instance, also enter the user ID and password of the database instance:

      bldcli dbusemx database userid password
   

The result is an executable file, dbusemx.exe.

There are three ways to run this embedded SQL application:

1. If accessing the sample database on the same instance, simply enter the executable name (without the extension):

      dbusemx
   

2. If accessing another database on the same instance, enter the executable name and the database name:

      dbusemx database
   

3. If accessing a database on another instance, enter the executable name, database name, and user ID and password of the database instance:

      dbusemx database userid password
   

DB2 CLI Applications with DB2 APIs

DB2 includes CLI sample programs that use DB2 APIs to create and drop a database in order to demonstrate using CLI functions on more than one database. The descriptions of the CLI sample programs in Table 7 indicates the samples that use DB2 APIs.

The command file bldapi in %DB2PATH%\samples\cli contains the commands to build a DB2 CLI program with DB2 APIs. This file compiles and links in the utilapi utility file, which contains the DB2 APIs to create and drop a database. This is the only difference between this file and the bldcli command file. Please see "DB2 CLI Applications" for the compile and link options common to both bldapi and bldcli.

To build the sample program dbmconn from the source file dbmconn.c , enter:

 
   bldapi dbmconn

The result is an executable file, dbmconn.exe. You can run the executable file by entering the executable name (without the extension):

   dbmconn

DB2 CLI Stored Procedures

The command file bldclisp, in %DB2PATH%\samples\cli, contains the commands to build a CLI stored procedure. The command file builds the stored procedure into a DLL on the server.

The parameter, %1, specifies the name of your source file. The command file uses the source file name, %1, for the DLL name.

@echo off
rem  bldclisp command file - OS/2
rem  Builds a CLI stored procedure using the IBM VisualAge C++ compiler.
rem  Usage: bldclisp <prog_name>
 
if "%1" == "" goto error
 
rem Compile the error-checking utility.
icc -C+ -Ti+ -Ge- -Gm+ -W2 utilcli.c
rem Compile the program.
if exist "%1.cxx" goto cpp
icc -C+ -Ti+ -Ge- -Gm+ -W2 %1.c
goto link_step
:cpp
icc -C+ -Ti+ -Ge- -Gm+ -W2 %1.cxx
 
:link_step
rem Link the program and produce a DLL.
ilink /NOFREE /MAP /NOI /DEBUG /ST:64000 %1.obj utilcli.obj,%1.dll,,db2cli.lib,%1.def;
 
rem Copy the stored procedure DLL to the 'function' directory
copy %1.dll %DB2PATH%\function
 
goto exit
:error
echo Usage: bldclisp prog_name 
:exit
@echo on

Compile and Link Options for bldclisp
Compile Options: icc The IBM VisualAge C++ compiler. -C+ Perform compile only; no link. The command file has separate compile and link steps. -Ti+ Generate debugger information. -Ge- Build a .DLL file. Use the version of the run-time library that is statically linked. -Gm+ Link with multi-tasking libraries. -W2 Output warning, error, and severe and unrecoverable error messages.
Link Options: ilink Use the ilink linker to link edit. /NOFREE No free format. /MAP Generate a map file. /NOI No Ignore Case. Force case sensitive identifiers. /DEBUG Include debugging information. /ST:64000 Specify a stack size of at least 64000. %1.obj Include the object file. %1.dll Create a dynamic link library. db2cli.lib Link with the DB2 CLI library. %1.def Module definition file. Refer to your compiler documentation for additional compiler options.

To build the sample program spserver from the source file spserver.c , enter:

    bldclisp spserver

The script file copies the shared library to the server in the path %DB2PATH%\function.

Next, catalog the stored procedures by running the spcreate.db2 script on the server. First, connect to the database:

   db2 connect to sample

If the stored procedures were previously cataloged, you can drop them with this command:

   db2 -td@ -vf spdrop.db2

Then catalog them with this command:

   db2 -td@ -vf spcreate.db2

Then, stop and restart the database to allow the new shared library to be recognized. If necessary, set the file mode for the shared library so the DB2 instance can access it.

Once you build the shared library, spserver, you can build the CLI client application spclient that calls the stored procedures in the shared library.

You can build spclient by using the command file, bldcli. Refer to "DB2 CLI Applications" for details.

To access the shared library, run the sample client application by entering:

spclient database userid password

where

database

Is the name of the database to which you want to connect. The name could be sample, or its alias, or another database name.

userid

Is a valid user ID.

password

Is a valid password.

The client application accesses the shared library, spserver, and executes a number of stored procedure functions on the server database. The output is returned to the client application.

DB2 API and Embedded SQL Applications

The command file bldapp.cmd, in %DB2PATH%\samples\c, and in %DB2PATH%\samples\cpp, contains the commands to build a DB2 application program.

The first parameter, %1, specifies the name of your source file. This is the only required parameter for programs that do not contain embedded SQL. Building embedded SQL programs requires a connection to the database so three optional parameters are also provided: the second parameter, %2, specifies the name of the database to which you want to connect; the third parameter, %3, specifies the user ID for the database, and %4 specifies the password.

For an embedded SQL program, bldapp passes the parameters to the precompile and bind command file, embprep. If no database name is supplied, the default sample database is used. The user ID and password parameters are only needed if the instance where the program is built is different from the instance where the database is located.

@echo off
rem bldapp command file -- OS/2
rem Builds a VisualAge C++ application program
rem Usage: bldapp <prog_name> [ <db_name> [ <userid> <password> ]] 
 
if exist "%1.sqx" goto embedded
if exist "%1.sqc" goto embedded
goto non_embedded
 
:embedded
rem Precompile and bind the program.
call embprep %1 %2 %3 %4
rem Compile the program.
if exist "%1.cxx" goto cpp_embedded
icc -c utilemb.c
icc -C+ -O- -Ti+ %1.c
goto link_embedded
:cpp_embedded
icc -c utilemb.cxx
icc -C+ -O- -Ti+ %1.cxx
goto link_embedded
 
:non_embedded
rem Compile the program.
if exist "%1.cxx" goto cpp
icc -c utilapi.c
icc -C+ -O- -Ti+ %1.c
goto link_non_embedded
:cpp
icc -c utilapi.cxx
icc -C+ -O- -Ti+ %1.cxx
goto link_non_embedded
 
rem Link the program.
:link_embedded
ilink /NOFREE /NOI /DEBUG /ST:64000 /PM:VIO %1.obj utilemb.obj,,,db2api;
goto exit
:link_non_embedded
ilink /NOFREE /NOI /DEBUG /ST:64000 /PM:VIO %1.obj utilapi.obj,,,db2api;
:exit
@echo on

To build the DB2 API non-embedded SQL sample program, client, from the source file client.c , enter:

   bldapp client

The result is an executable file, client.exe.

To run the executable file, enter the executable name (without the extension):

   client

Building and Running Embedded SQL Applications

There are three ways to build the embedded SQL application, updat, from the source file updat.sqc :

1. If connecting to the sample database on the same instance, enter:

      bldapp updat
   

2. If connecting to another database on the same instance, also enter the database name:

      bldapp updat database
   

3. If connecting to a database on another instance, also enter the user ID and password of the database instance:

      bldapp updat database userid password
   

The result is an executable file, updat.exe.

There are three ways to run this embedded SQL application:

1. If accessing the sample database on the same instance, simply enter the executable name (without the extension):

      updat
   

2. If accessing another database on the same instance, enter the executable name and the database name:

      updat database
   

3. If accessing a database on another instance, enter the executable name, database name, and user ID and password of the database instance:

      updat database userid password
   

Embedded SQL Stored Procedures

The command file bldsrv, in %DB2PATH%\samples\c, and in %DB2PATH%\samples\cpp, contains the commands to build an embedded SQL stored procedure. The command file compiles the stored procedure into a DLL on the server.

The first parameter, %1, specifies the name of your source file. The second parameter, %2, specifies the name of the database to which you want to connect. Since the stored procedure must be build on the same instance where the database resides, there are no parameters for user ID and password.

Only the first parameter, source file name, is required. Database name is optional. If no database name is supplied, the program uses the default sample database.

The command file uses the source file name, %1, for the DLL name.

@echo off
rem bldsrv command file -- OS/2
rem Builds a VisualAge C++ stored procedure
rem Usage: bldsrv <prog_name> [ <db_name> ] 
 
rem Precompile and bind the program.
call embprep %1 %2
 
rem Compile the program.
if exist "%1.cxx" goto cpp
icc -C+ -Ti+ -Ge- -Gm+ -W2 %1.c
goto link_step
:cpp
icc -C+ -Ti+ -Ge- -Gm+ -W2 %1.cxx
 
:link_step
rem Link the program.
ilink /NOFREE /NOI /DEBUG /ST:64000 %1.obj,%1.dll,,db2api,%1.def;
 
rem Copy the stored procedure to the %DB2PATH%\function directory.
copy %1.dll %DB2PATH%\function
@echo on

To build the sample program spserver from source file spserver.sqc , if connecting to the sample database, enter:

    bldsrv spserver

If connecting to another database, also enter the database name:

    bldsrv spserver database

The command file copies the shared library to the server in the path %DB2PATH%\function.

Next, catalog the stored procedures by running the spcreate.db2 script on the server. First, connect to the database:

   db2 connect to sample

If the stored procedures were previously cataloged, you can drop them with this command:

   db2 -td@ -vf spdrop.db2

Then catalog them with this command:

   db2 -td@ -vf spcreate.db2

Then, stop and restart the database to allow the new shared library to be recognized. If necessary, set the file mode for the shared library so the DB2 instance can access it.

Once you build the shared library, spserver, you can build the client application spclient that accesses the shared library.

You can build spclient by using the command file, bldapp. Refer to "DB2 API and Embedded SQL Applications" for details.

To call the stored procedure, run the sample client application by entering:

spclient database userid password

where

database

Is the name of the database to which you want to connect. The name could be sample, or its alias, or another database name.

userid

Is a valid user ID.

password

Is a valid password.

The client application accesses the shared libary, spserver, and executes a number of stored procedure functions on the server database. The output is returned to the client application.

User-Defined Functions (UDFs)

The command file bldudf, in %DB2PATH%\samples\c, and in %DB2PATH%\samples\cpp, contains the commands to build a UDF.

UDFs cannot contain embedded SQL statements. Therefore, to build a UDF program, you do not connect to a database to precompile and bind the program.

The command file takes one parameter, %1, which specifies the name of your source file. It uses the source file name, %1, for the DLL name.

@echo off
rem  bldudf command file -- OS/2
rem  Builds a VisualAge C++ user-defined function (UDF)
rem  Usage: bldudf <prog_name> 
 
if "%1" == "" goto error
 
rem Compile the program.
if exist "%1.cxx" goto cpp
icc -C+ -Ti+ -Ge- -Gm+ -W2 %1.c
goto link_step
:cpp
rem icc -C+ -Ti+ -Ge- -Gm+ -W2 %1.cxx
 
:link_step
rem Link the program.
ilink /NOFREE /MAP /NOI /DEBUG /ST:64000 %1.obj,%1.dll,,db2api db2apie,%1.def;
 
rem Copy the UDF to the %DB2PATH%\function directory
copy %1.dll %DB2PATH%\function
 
goto exit
:error
echo Usage: bldudf prog_name 
:exit
@echo on

To build the user-defined function program udfsrv from the source file udfsrv.c , enter:

   
   bldudf udfsrv

The script file copies the UDF to the server in the path %DB2PATH%\function.

If necessary, set the file mode for the UDF so the client application can access it.

Once you build udfsrv, you can build the client application, udfcli, that calls it. DB2 CLI and embedded SQL versions of this program are provided.

You can build the DB2 CLI udfcli program from the udfcli.c source file in %DB2PATH%\samples\cli using the command file bldcli.cmd. Refer to "DB2 CLI Applications" for details.

You can build the embedded SQL udfcli program from the source file udfcli.sqc , in %DB2PATH%\samples\c, using the command file, bldapp. Refer to "DB2 API and Embedded SQL Applications" for details.

To call the UDF, run the sample calling application by entering the executable name (without the extension):

   udfcli

The calling application calls the ScalarUDF function from the udfsrv library.