Application Building Guide

IBM VisualAge C++ for OS/2 Version 3

This section includes the following topics:

• DB2 API Applications
• DB2 CLI Applications
• DB2 CLI Stored Procedures
• 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 command files in both these directories contain commands to accept either a C or C++ source file, depending on the file extension. By default, the C++ commands are commented out in the command files in %DB2PATH\samples\c, and the C commands are commented out in the command files in %DB2PATH\samples\cpp. This section demonstrates building programs using the C command files.

DB2 API Applications

The command file bldvaapi.cmd, in %DB2PATH%\samples\c, and in %DB2PATH%\samples\cpp, contains the commands to build a DB2 API program. The parameter, %1, specifies the name of your source file.

@echo off
rem  bldvaapi command file
rem  Builds a C or C++ DB2 API program.
rem  Usage: bldvaapi <prog_name>
 
if "%1" == "" goto error
 
rem Compile the error-checking utility.  
icc -c util.c
rem For C++, comment out the above line and uncomment the following:  
rem icc -c util.cxx
 
rem  Compile the program.
icc -C+ -O- -Ti+ %1.c
rem For C++, comment out the above line and uncomment the following:
rem icc -C+ -O- -Ti+ %1.cxx
 
rem  Link step
ilink /NOFREE /NOI /DEBUG /ST:64000 /PM:VIO %1.obj util.obj,,,db2api;
 
goto exit
 
:error
echo Usage: bldvaapi <prog_name>
 
:exit
@echo on

To build the sample program client from the C source file client.c in %DB2PATH%\samples\c, or from the C++ source file client.cxx in %DB2PATH%\samples\cpp, enter:

 
   bldvaapi client

The result is an executable file, client. You can run the executable file by entering the executable name:

   client

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.

@echo off
rem  bldcli command file - OS/2 
rem  Builds a CLI program with IBM VisualAge C++.
rem  Usage: bldcli <prog_name>
 
if "%1" == "" goto error
 
rem Compile the error-checking utility.
icc -C+ -O- -Ti+ samputil.c
 
rem  Compile the program. 
icc -C+ -O- -Ti+ %1.c
 
rem  Link the program.
ilink /NOFREE /NOI /DEBUG /ST:64000 /PM:VIO %1.obj
    samputil.obj,%1.exe,NUL,db2cli.lib;
 
goto exit
 
:error
echo Usage: bldcli <prog_name>
 
:exit
@echo on

Compile and Link Options for bldcli
The command file contains the following 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
The command file contains the following 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. samputil.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 basiccon from the source file basiccon.c , enter:

   bldcli basiccon

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

   basiccon

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 samputil.c
 
rem Compile the program. 
icc -C+ -Ti+ -Ge- -Gm+ -W2 %1.c
 
rem Link the program and produce a DLL.
ilink /NOFREE /MAP /NOI /DEBUG /ST:64000 %1.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
The command file contains the following compile options: icc The IBM VisualAge C++ compiler. -C+ Perform compile only; no link. This book assumes that compile and link are separate 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.
The command file contains the following 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 outsrv2 stored procedure from the source file outsrv2.c , enter:

 
   bldclisp outsrv2

The command file uses the module definition file, outsrv2.def, contained in the same directory as the sample programs, to build the stored procedure. The command file copies the stored procedure DLL, outsrv2.dll, on the server in the path %DB2PATH%\function. For DB2DARI parameter style stored procedures where the invoked procedure matches the name of the stored procedure DLL, this location indicates that the stored procedure is fenced. If you want this type of stored procedure to be unfenced, you must move the DLL to the %DB2PATH%\function\unfenced directory. For all other types of DB2 stored procedures, you indicate whether it is fenced or not fenced with the CREATE FUNCTION statement in the calling program. For a full discussion on creating and using the different types of DB2 stored procedures, please see the "Stored Procedures" chapter in the Application Development Guide.

Note:

An unfenced stored procedure runs in the same address space as the database manager and results in increased performance when compared to a fenced stored procedure, which runs in an address space isolated from the database manager. With unfenced stored procedures there is a danger that user code could accidentally or maliciously damage the database control structures. Therefore, you should only run unfenced stored procedures when you need to maximize the performance benefits. Ensure these programs are thoroughly tested before running them as unfenced. Refer to the Application Development Guide for more information.

Once you build the stored procedure outsrv2, you can build the client application outcli2 that calls the stored procedure. You can build outcli2 by using the command file bldcli. Refer to "DB2 CLI Applications" for details.

To run the stored procedure, enter:

 
   outcli2 remote_database userid password

where

remote_database

Is the name of the database to which you want to connect. The name could be SAMPLE, or its remote alias, or some other name.

userid

Is a valid user ID.

password

Is a valid password.

The client application passes a variable to the server program, outsrv2, which gives it a value and then returns the variable to the client application.

Embedded SQL Applications

The command file bldvaemb.cmd, in %DB2PATH%\samples\c, and in %DB2PATH%\samples\cpp, contains the commands to build an embedded SQL program.

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. Parameter %3 specifies the user ID for the database, and %4 specifies the password. Only the first parameter, the source file name, is required. Database name, user ID, and password are optional. If no database name is supplied, the program uses the default sample database.

@echo off
rem bldvaemb command file
rem Builds a C or C++ program that contains embedded SQL
rem Usage: bldvaemb <prog_name> [ <db_name> [ < userid> <password> ]]
 
rem Connect to a database.
if "%1" == "" goto error
if "%2" == "" goto case1
if "%3" == "" goto case2
if "%4" == "" goto error
goto case3
:case1
   db2 connect to sample
   goto continue
:case2
   db2 connect to %2
   goto continue
:case3
   db2 connect to %2 user %3 using %4
   goto continue
:continue
 
rem Precompile the program. 
db2 prep %1.sqc bindfile
rem For C++, comment out the above line, and uncomment the following:
rem db2 prep %1.sqx bindfile
 
rem Compile the util.c error checking utility. 
icc -c util.c
rem For C++, comment out the above line, and uncomment the following:
rem icc -c util.cxx
 
rem Compile the program. 
icc -C+ -O- -Ti+ %1.c
rem For C++, comment out the above line, and uncomment the following:
rem icc -C+ -O- -Ti+ %1.cxx
 
rem Link the program.
ilink /NOFREE /NOI /DEBUG /ST:64000 /PM:VIO %1.obj util.obj,,,db2api;
 
rem Bind the program to the database.
db2 bind %1.bnd
 
rem Disconnect from the database.
db2 connect reset
 
goto exit
 
:error
echo Usage: bldvaemb <prog_name> [ <db_name> [ < userid> <password> ]]
 
:exit
@echo on

To build the sample program updat, from the C source file updat.sqc , or the C++ source file, updat.sqx , enter:

 
   bldvaemb updat

The result is an executable file, updat. You can run the executable file against the SAMPLE database by entering the executable name:

 
   updat

Embedded SQL Stored Procedures

The command file bldvastp, 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. Parameter %3 specifies the user ID for the database, and %4 specifies the password. Only the first parameter, the source file name, is required. Database name, user ID, and password are 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 bldvastp command file
rem Builds a C or C++ stored procedure
rem Usage: bldvastp <prog_name> [ <db_name> [ < userid> <password> ]]
 
rem Connect to a database.
if "%1" == "" goto error
if "%2" == "" goto case1
if "%3" == "" goto case2
if "%4" == "" goto error
goto case3
:case1
   db2 connect to sample
   goto continue
:case2
   db2 connect to %2
   goto continue
:case3
   db2 connect to %2 user %3 using %4
   goto continue
:continue
 
rem Precompile the program. 
db2 prep %1.sqc bindfile
rem For C++, comment out the above line and uncomment the following:
rem db2 prep %1.sqx bindfile
 
rem Compile the program. 
icc -C+ -Ti+ -Ge- -Gm+ -W2 %1.c
rem For C++, comment out the above line and uncomment the following:
rem icc -C+ -Ti+ -Ge- -Gm+ -W2 %1.cxx
 
rem Link the program.
ilink /NOFREE /NOI /DEBUG /ST:64000 %1.obj,%1.dll,,db2api,%1.def;
 
rem Bind the program to the database.
db2 bind %1.bnd
 
rem Disconnect from the database.
db2 connect reset
 
rem Copy stored procedure to the %DB2PATH%\function directory.
rem Substitute the path where DB2 is installed for %DB2PATH%.
copy %1.dll %DB2PATH%\function
 
goto exit
 
:error
echo Usage: bldvastp <prog_name> [ <db_name> [ < userid> <password> ]]
 
:exit
@echo on

To build the stored procedure outsrv from the C source file outsrv.sqc , or the C++ source file outsrv.sqx , enter:

 
   bldvastp outsrv

The command file uses the module definition file, outsrv.def, contained in the same directory as the sample programs, to build the stored procedure. The command file copies the stored procedure DLL, outsrv.dll, on the server in the path %DB2PATH%\function. For DB2DARI parameter style stored procedures where the invoked procedure matches the name of the stored procedure DLL, this location indicates that the stored procedure is fenced. If you want this type of stored procedure to be unfenced, you must move the DLL to the %DB2PATH%\function\unfenced directory. For all other types of DB2 stored procedures, you indicate whether it is fenced or not fenced with the CREATE FUNCTION statement in the calling program. For a full discussion on creating and using the different types of DB2 stored procedures, please see the "Stored Procedures" chapter in the Application Development Guide.

Note:

An unfenced stored procedure runs in the same address space as the database manager and results in increased performance when compared to a fenced stored procedure, which runs in an address space isolated from the database manager. With unfenced stored procedures there is a danger that user code could accidentally or maliciously damage the database control structures. Therefore, you should only run unfenced stored procedures when you need to maximize the performance benefits. Ensure these programs are thoroughly tested before running them as unfenced. Refer to the Application Development Guide for more information.

Once you build the stored procedure outsrv, you can build the client application, outcli , that calls the stored procedure. You can build outcli by using the command file bldvaemb. Refer to "Embedded SQL Applications" for details.

To call the stored procedure, enter:

 
   outcli remote_database userid password

where

remote_database

Is the name of the database to which you want to connect. The name could be SAMPLE, or its remote alias, or some other name.

userid

Is a valid user ID.

password

Is a valid password.

The client application passes a variable to the server program, outsrv, which gives it a value and then returns the variable to the client application.

User-Defined Functions (UDFs)

The command file bldvaudf, 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 need to 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  bldvaudf command file
rem  Builds a C or C++ user-defined function (UDF)
rem  Usage: bldvaudf <UDF_name>
 
rem Compile the program. 
icc -C+ -Ti+ -Ge- -Gm+ -W2 %1.c
rem For C++, comment out the above line and uncomment the following:
rem icc -C+ -Ti+ -Ge- -Gm+ -W2 %1.cxx
 
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
@echo on

To build the user-defined function, udf, from the C source file, udf.c , enter:

   bldvaudf udf

The command file uses the module definition file, udf.def, contained in the same directory as the sample programs, to build the user-defined function. The command file copies the user-defined function DLL, udf.dll, to the server in the path %DB2PATH%\function.

Once you build udf, you can build the client application, calludf, that calls it. DB2 CLI as well as embedded SQL C and C++ versions of this program are provided.

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

You can build the C embedded SQL calludf program from the calludf.sqc source file in %DB2PATH%\samples\c using the command file bldvaemb. Refer to "Embedded SQL Applications" for details.

You can build the C++ embedded SQL calludf program from the calludf.sqx source file in %DB2PATH%\samples\cpp using the command file bldvaemb. Refer to "Embedded SQL Applications" for details.

To run the UDF, enter:

 
   calludf

The application calls functions from the udf library.

After you run the calling application, you can also invoke the UDF interactively using the command line processor. Connect to the database, then enter:

   db2 SELECT name, DOLLAR(salary), SAMP_MUL(DOLLAR(salary), FACTOR(1.2)) FROM staff

You do not have to type the SQL statement in uppercase.

[ DB2 List of Books | Search the DB2 Books ]