Application Building Guide

Microsoft Visual C++

This section includes the following topics:

• ActiveX Data Objects (ADO)
• Object Linking and Embedding (OLE) Automation
• DB2 CLI Applications
• DB2 CLI Stored Procedures
• DB2 API and Embedded SQL Applications
• Embedded SQL Stored Procedures
• User-Defined Functions (UDFs)

ActiveX Data Objects (ADO)

DB2 ADO programs using Visual C++ can be compiled the same as regular C++ programs, once you make the following change.

To have your C++ source program run as an ADO program, you can put the following import statement at the top of your source program file:

#import "C:\program files\common files\system\ado\msado<VERSION NUMBER>.dll" \
	no_namespace \
	rename( "EOF", "adoEOF")

where <VERSION NUMBER> is the version number of the ADO library.

When the program is compiled, the user will need to verify that the msado<VERSION NUMBER>.dll is in the path specified. An alternative is to add C:\program files\common files\system\ado to the environment variable LIBPATH, and then use this shorter import statement in your source file:

#import <msado<VERSION NUMBER>.dll> \
	no_namespace \
	rename( "EOF", "adoEOF")

This is the method used in the DB2 sample program, BLOBAccess.dsp.

With this IMPORT statement, your DB2 program will have access to the ADO library. You can now compile your Visual C++ program as you would any other program. If you are also using another programming interface, such as DB2 APIs, or DB2 CLI, refer to the appropriate section in this chapter for additional information on building your program.

DB2 provides Visual C++ ADO sample programs in the %DB2PATH%\samples\ADO\VC directory.

Object Linking and Embedding (OLE) Automation

This section describes Object Linking and Embedding (OLE) automation UDFs in Microsoft Visual C++, as well as a sample OLE automation controller for stored procedures.

You can implement OLE automation UDFs and stored procedures in any language, as OLE is language independent, by exposing methods of OLE automation servers, and registering the methods as UDFs with DB2. Application development environments which support the development of OLE automation servers include certain versions of the following: Microsoft Visual Basic, Microsoft Visual C++, Microsoft Visual J++, Microsoft FoxPro, Borland Delphi, Powersoft PowerBuilder, and Micro Focus COBOL. Also, Java beans objects that are wrapped properly for OLE, for example with Microsoft Visual J++, can be accessed via OLE automation.

You need to refer to the documentation of the appropriate application development environment for further information on developing OLE automation servers. For more detailed information on DB2 programming using OLE automation, refer to the Application Development Guide.

OLE Automation UDFs and Stored Procedures

Microsoft Visual C++ supports the creation of OLE automation servers. Servers can be implemented using Microsoft Foundation Classes and the Microsoft Foundation Class application wizard, or implemented as Win32 applications. Servers can be DLLs or EXEs. Refer to the Microsoft Visual C++ documentation and to the OLE samples provided by Microsoft Visual C++ for further information. For information on building Visual C++ UDFs for DB2, see "User-Defined Functions (UDFs)". For information on building Visual C++ stored procedures with DB2 CLI, see "DB2 CLI Stored Procedures". For information on building Visual C++ embedded SQL stored procedures for DB2, see "Embedded SQL Stored Procedures".

DB2 provides self-containing samples of OLE automation UDFs and stored procedures in Microsoft Visual C++, located in the directory %DB2PATH%\samples\ole\msvc. For information on building and running the OLE automation UDF and stored procedure samples, please see the README file in %DB2PATH%\samples\ole.

DB2 CLI Applications

The batch file bldmcli.bat, 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 or .sqx extension, then the embprep batch file is called to precompile the program, producing a program file with either a .c or a .cxx extension, respectively.

@echo off
rem  bldmcli batch file - Windows 32-bit Operating Systems
rem  Builds a CLI program with Microsoft Visual C++.
rem  Usage: bldmcli 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 
 
rem Compile the error-checking utility.
cl -Z7 -Od -c -W1 -D_X86=1 -DWIN32 utilcli.c
 
rem  Compile the program.
if exist "%1.sqx" goto cpp
cl -Z7 -Od -c -W1 -D_X86=1 -DWIN32 %1.c
goto link_step
:cpp
cl -Z7 -Od -c -W2 -D_X86_=1 -DWIN32 %1.cxx
 
rem  Link the program.
:link_step
link -debug:full -debugtype:cv -OUT:%1.exe %1.obj utilcli.obj db2cli.lib
@echo on

Compile and Link Options for bldmcli
Compile Options: cl The Microsoft Visual C++ compiler. -Z7 C7 style CodeView information generated. -Od Disable optimizations. It is easier to use a debugger with optimization off. -c Perform compile only; no link. This book assumes that compile and link are separate steps. -W1 Set warning level. -D_X86_=1 Compiler option necessary for Windows 32-bit operating systems to run on Intel-based computers. -DWIN32 Compiler option necessary for Windows 32-bit operating systems.
Link Options: link Use the 32-bit linker to link edit. -debug:full Include debugging information. -debugtype:cv Indicate the debugger type. -OUT:%1.exe Specify the executable. %1.obj Include the object file. utilcli.obj Include the utility object file for error checking. 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:

 
   bldmcli tbinfo

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

   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:

      bldmcli dbusemx
   

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

      bldmcli dbusemx database
   

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

      bldmcli dbusemx database userid password
   

The result is an executable file, dbusemx.

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:

      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 script file bldmapi in sqllib/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 bldmcli batch file. Please see "DB2 CLI Applications" for the compile and link options common to both bldmapi and bldmcli.

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

 
   bldmapi dbmconn

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

   dbmconn

DB2 CLI Stored Procedures

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

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

@echo off
rem bldmclis.bat file - Windows 32-bit Operating Systems
rem Builds a CLI stored procedure using the Microsoft Visual C++ compiler.
rem Usage: bldmclis prog_name
 
if "%1" == "" goto error
 
rem Compile the program. 
if exist "%1.cxx" goto cpp
cl -Z7 -Od -c -W2 -D_X86_=1 -DWIN32 %1.c  utilcli.c
goto link_step
:cpp
cl -Z7 -Od -c -W2 -D_X86_=1 -DWIN32 %1.cxx  utilcli.c
 
rem Link the program.
:link_step
link -debug:full -debugtype:cv -dll -out:%1.dll %1.obj utilcli.obj db2cli.lib -def:%1.def
 
rem Copy the stored procedure DLL to the 'function' directory
copy %1.dll "%DB2PATH%\function"
 
goto exit
:error
echo Usage: bldmclis prog_name 
:exit
@echo on

Compile and Link Options for bldmclis
Compile Options: cl The Microsoft Visual C++ compiler. -Z7 C7 style CodeView information generated. -Od Disable optimizations. It is easier to use a debugger with optimization off. -c Perform compile only; no link. This book assumes that compile and link are separate steps. -W2 Set warning level. -D_X86_=1 Compiler option necessary for Windows 32-bit operating systems to run on Intel-based computers. -DWIN32 Compiler option necessary for Windows 32-bit operating systems.
Link Options: link Use the 32-bit linker to link edit. -debug:full Include debugging information. -debugtype:cv Indicate the debugger type. -OUT:%1.dll Build a .DLL file. %1.obj Include the object file. utilcli.obj Include the utility object file for error-checking. db2cli.lib Link with the DB2 CLI library. -def:%1.def Use the module definition file. Refer to your compiler documentation for additional compiler options.

To build the spserver stored procedure from the source file spserver.c , enter:

   bldmclis spserver

The batch file uses the module definition file spserver.def, contained in the same directory as the CLI sample programs, to build the stored procedure. The batch file copies the stored procedure DLL, spserver.dll, 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 stored procedure spserver, you can build the CLI client application spclient that calls the stored procedure.

You can build spclient by using the script file, bldmcli. Refer to "DB2 CLI 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 stored procedure library, spserver, which 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 batch file bldmapp.bat, 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. 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 additional, 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, bldmapp passes the parameters to the precompile and bind 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 bldmapp.bat -- Windows 32-bit operating systems
rem Builds a Microsoft Visual C++ application program
rem Usage: bldmapp 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_emb
cl -Z7 -Od -c -W2 -D_X86_=1 -DWIN32 %1.c utilemb.c
goto link_embedded
:cpp_emb
cl -Z7 -Od -c -W2 -D_X86_=1 -DWIN32 %1.cxx utilemb.cxx
rem Link the program.
:link_embedded
link -debug:full -debugtype:cv -out:%1.exe %1.obj utilemb.obj db2api.lib
goto exit
 
:non_embedded
rem  Compile the program.
if exist "%1.cxx" goto cpp_non
cl -Z7 -Od -c -W2 -D_X86_=1 -DWIN32 %1.c utilapi.c
goto link_non_embedded
:cpp_non
cl -Z7 -Od -c -W2 -D_X86_=1 -DWIN32 %1.cxx utilapi.cxx
rem Link the program.
:link_non_embedded
link -debug:full -debugtype:cv -out:%1.exe %1.obj utilapi.obj db2api.lib
:exit
@echo on

Compile and Link Options for bldmapp
Compile Options: cl The Microsoft Visual C++ compiler. -Z7 C7 style CodeView information generated. -Od Disable optimizations. It is easier to use a debugger with optimization off. -c Perform compile only; no link. This book assumes that compile and link are separate steps. -W2 Set warning level. -D_X86_=1 Compiler option necessary for Windows 32-bit operating systems to run on Intel-based computers. -DWIN32 Compiler option necessary for Windows 32-bit operating systems.
Link Options: link Use the 32-bit linker to link edit. -debug:full Include debugging information. -debugtype:cv Indicate the debugger type. -out:%1.exe Specify a filename %1.obj Include the object file utilemb.obj If an embedded SQL program, include the embedded SQL utility object file for error checking. utilapi.obj If not an embedded SQL program, include the DB2 API utility object file for error checking. db2api.lib Link with the DB2 library. Refer to your compiler documentation for additional compiler options.

To build the DB2 API non-embedded SQL sample program, client, from either the source file client.c , in %DB2PATH%\samples\c, or from the source file client.cxx , in %DB2PATH%\samples\cpp, enter:

   bldmapp client

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

   client

Building and Running Embedded SQL Applications

There are three ways to build the embedded SQL application, updat, from the C source file updat.sqc in %DB2PATH%\samples\c, or from the C++ source file updat.sqx in %DB2PATH%\samples\cpp:

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

      bldmapp updat
   

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

      bldmapp updat database
   

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

      bldmapp 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:

      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 batch file bldmsrv.bat, in %DB2PATH%\samples\c, and in %DB2PATH%\samples\cpp, contains the commands to build an embedded SQL stored procedure. The batch file builds 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, the source file name, is required. Database name is optional. If no database name is supplied, the program uses the default sample database.

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

@echo off
rem bldmsrv.bat -- Windows 32-bit operating systems
rem Builds a Microsoft Visual C++ stored procedure
rem Usage: bldmsrv prog_name [ db_name ]
 
rem Precompile and bind the program.
call embprep %1 %2
 
rem Compile the program.
if exist "%1.cxx" goto cpp
cl -Z7 -Od -c -W2 -D_X86_=1 -DWIN32 %1.c
goto link_step
:cpp
cl -Z7 -Od -c -W2 -D_X86_=1 -DWIN32 %1.cxx
 
:link_step
rem Link the program.
link -debug:full -debugtype:cv -out:%1.dll -dll %1.obj db2api.lib -def:%1.def
 
rem Copy the stored procedure DLL to the 'function' directory
copy %1.dll "%DB2PATH%\function"
@echo on

Compile and Link Options for bldmsrv
Compile Options: cl The Microsoft Visual C++ compiler. -Z7 C7 style CodeView information generated. -Od Disable optimization. -c Perform compile only; no link. This book assumes that compile and link are separate steps. -W2 Output warning, error, and severe and unrecoverable error messages. -D_X86_=1 Compiler option necessary for Windows 32-bit operating systems to run on Intel-based computers. -DWIN32 Compiler option necessary for Windows 32-bit operating systems.
Link Options: link Use the linker to link edit. -debug:full Include debugging information. -debugtype:cv Indicates the debugger type. -out:%1.dll Build a .DLL file. %1.obj Include the object file. db2api.lib Link with the DB2 library. -def:%1.def Module definition file. Refer to your compiler documentation for additional compiler options.

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

   bldmsrv spserver

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

    bldmsrv spserver database

The batch file uses the module definition file spserver.def, contained in the same directory as the sample programs, to build the DLL. The batch file copies the DLL, spserver.dll, 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 stored procedure DLL, spserver, you can build the client application spclient that calls it.

You can build spclient by using the script file, bldmapp. 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 stored procedure DLL, 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 batch file bldmudf, 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 batch 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 bldmudf.bat -- Windows 32-bit operating systems
rem Builds a Microsoft Visual C++ user-defined function (UDF).
rem Usage: bldmudf udf_prog_name
 
if "%1" == "" goto error
 
rem Compile the program.
if exist "%1.cxx" goto cpp
cl -Z7 -Od -c -W2 -D_X86_=1 -DWIN32 %1.c
goto link_step
:cpp
cl -Z7 -Od -c -W2 -D_X86_=1 -DWIN32 %1.cxx
 
:link_step
rem Link the program.
link -debug:full -debugtype:cv -dll -out:%1.dll %1.obj db2api.lib db2apie.lib -def:%1.def
 
rem Copy the UDF DLL to the 'function' directory
copy %1.dll "%DB2PATH%\function"
 
goto exit
:error
echo Usage: bldmudf prog_name
:exit
@echo on

Compile and Link Options for bldmudf
Compile Options: cl The Microsoft Visual C++ compiler. -Z7 C7 style CodeView information generated. -Od Disable optimization. -c Perform compile only; no link. This book assumes that compile and link are separate steps. -W2 Output warning, error, and severe and unrecoverable error messages. -D_X86_=1 Compiler option necessary for Windows 32-bit operating systems to run on Intel-based computers. -DWIN32 Compiler option necessary for Windows 32-bit operating systems.
Link Options: link Use the linker to link edit. -debug:full Include debugging information. -debugtype:cv Indicates the debugger type. -dll Create a DLL. -out:%1.dll Build a .DLL file. %1.obj Include the object file. db2api.lib Link with the DB2 library. db2apie.lib Link with the DB2 API Engine library. -def:%1.def Module definition file. Refer to your compiler documentation for additional compiler options.

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

   bldmudf udfsrv

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

Once you build udfsrv, you can build the client application, udfcli, 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 udfcli program from the udfcli.c source file in %DB2PATH%\samples\cli using the batch file bldmcli. Refer to "DB2 CLI Applications" for details.

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

You can build the embedded SQL C++ udfcli program from the udfcli.sqx source file in %DB2PATH%\samples\cpp using the batch file bldmapp. Refer to "DB2 API and Embedded SQL Applications" for details.

To run the UDF, enter:

   udfcli

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