| Programmer to ProgrammerTM | |||||
|
|
|||||
|
|
|
||||
|
|
|
|
|
|
|
|
|
|
| |||||||||||||||||||
| The ASPToday
Article March 2, 2001 |
Previous
article - March 1, 2001 |
Next
article - March 5, 2001 | |||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||
| ABSTRACT |
| ||||||||||||||||||||||||||||||
|
| |||||||||||||||||||||||||||||||
| Article Discussion | Rate this article | Related Links | Index Entries | ||||||||
| ARTICLE | |||||||||||
On a recent project, DB2 for Windows was selected as the relational database in which to store the project's objects. The rationale was simple:
This article explains how to implement stored procedures in DB2 using Visual C++ and the OLE DB provider supplied by IBM. The stored procedure call developed will be accessed from ASP using ADO. The purpose of the article is to highlight a number of "gotcha's" encountered during development of the DB2 stored procedure call and its ultimate integration with ASP, and how to tackle them. This article development will guide users through the more complicated aspects of integrating DB2 stored procedure calls with ASP using ADO.
In July 2000, IBM released DB2 Version 7.0. This was the first version that included an OLE DB provider. Prior to this Windows developers used IBM's ODBC driver. In order to use ADO, a developer had to use the OLE DB/ODBC Bridge. Use of this bridge limits performance because it adds another layer between client and database.
The OLE DB Provider (ibmdadb2.dll) was not shipped in the initial release of DB2 7 but instead can be downloaded from: http://www-4.ibm.com/software/data/db2/udb/ad/v71/oledb.html?WROXEMPTOKEN=1613948Zh3uPFsq43Xgoy5y1kK. The page displayed by this URL contains a link to a crucial document, "Writing Applications Using the IBM OLE DB Provider for DB2". This document presents an overview the OLE DB features supported by the DB2 provider. ADO uses the DB2 OLE DB provider to access DB2. For this reason, the features supported by the OLE DB provider for DB2 determine the features supported by ADO when ADO accesses DB2.
IBM's OLE DB Provider for DB2 is both with respect to its threading model. By default ADO objects use apartment threading so taking advantage of free threading in the provider requires modification of how ADO objects are registered (see the following MSDN article for more information on changing the default threading model of ADO objects http://msdn.microsoft.com/workshop/server/components/daciisperf.asp?WROXEMPTOKEN=1613948Zh3uPFsq43Xgoy5y1kK).
DB2 allows stored procedures to be developed using SQL's "CREATE PROCEDURE" in order to specify a stored procedure call written in SQL. DB2 stored procedure calls can also be written in a high level language (C, C++, Java, Visual Basic, etc.). For our purposes, C++ was the language of choice. C++ was the optimal language for the low-level data manipulation required of the application.
Stored Procedure Calls made using the DB2 OLE DB provider have a tendency to be temperamental to install and execute, but they are highly reliable once installed and executed successfully. This article is very explicit with regard to issues such as directory location, case sensitivity and build/install processes. The protocol used to develop DB2 Stored Procedure Calls was developed after numerous hours of "not quite getting it right." It is recommended that the guidelines provided in this article be adhered to strictly.
Each C/C++ DB2 stored procedure is actually a DLL. The stored procedure DLL exposes a function registered with DB2. A DB2 server loads the DLL and invokes the function when the stored procedure is executed. IBM's SDK demonstrates creating a stored procedure DLL using their C++ development tool, Visual C++. The application that would ultimately use the DB2 stored procedure calls was developed using Visual Studio including Visual C++ 6. DB2 stored procedure development processes had to be adjusted to support Visual C++.
Throughout this article, file names and source code functions will be prefixed by SWP. The SWP stands for Software Pronto, Inc. (Jan's corporation, http://www.softwarepronto.com/?WROXEMPTOKEN=1613948Zh3uPFsq43Xgoy5y1kK). These letters indicate that the file/code is neither Windows nor DB2 in origin. SWP serves as a reminder in case Jan ever forgets the name of his corporation.
In order to execute the stored procedure DLL developed in the article, a DB2 database named SWPDemo should be created. No new tables are required for this database because all stored procedure calls demonstrated in this article execute against tables in the database's schema.
Before development of a DLL can begin, DB2 must be installed. Once the DB2 development environment is installed, the Visual C++ development environment has to be configured to access the DB2 header files and libraries. The steps taken to recognize the DB2 development files were performed using Visual C++ - Tools/Options/Directories. The steps taken are presented below. You should specify the same drive letter as corresponds to your DB2 installation. It is important that you add each new path to the existing path or paths. Do not overwrite the existing paths when performing the following steps:
These settings will have to be specified separately for each engineer on each machine used to develop with DB2. These paths could have been specified in Visual C++'s Project menu's, Settings menu item. Using the Project menus Setting's would have caused issues with the driver letters used for installation on specific machines. Given that a small number of developers were actually working on the DB2 stored procedure calls, the Tool menu's, Options menu item was seen as the easiest way to integrate DB2 with Visual C++.
The following steps should be taken in order to create a DB2 stored procedure call, DLL using Visual C++:
The module definition file (*.def) takes the following form:
EXPORTS SWPDB2Proc01 DllCanUnloadNow @1 PRIVATE DllGetClassObject @2 PRIVATE DllRegisterServer @3 PRIVATE DllUnregisterServer @4 PRIVATE
In the module definition file (*.def), the stored procedure call's function name is specified, SWPDB2Proc01. This insures that the stored procedure call will be exported as a C function (no name mangling). Four other functions are exported by the DLL: DllCanUnloadNow, DllGetClassObject, DllRegisterServer and DllUnregisterServer. Savvy developers recognize these functions as being those specified by COM DLL's. For the purposes of this article these functions are not COM related but will be used to aid in installing and uninstalling the stored procedure call (more on this later).
Once the general-purpose files are added to the project, perform the following steps:
DB2 does not provide Win32 console applications that can directly process a file with an SQX extension. A DB2-specific console window (Command Center) is used in conjunction with a set of utilities that manipulate the SQX file and the files generated from it. The utilities needed to manipulate the SQX file are:
The support material provided with this article contains a batch file, SWPDB2Prep.bat. This batch file automates the previously reviewed DB2 utilities (connect, prep and bind) and allows these utilities to be integrated with a C++ project. Each utility in the batch file is prefixed by DB2. This DB2 prefix indicates to DB2's Command Center that the utility is DB2 specific. The batch file, SWPDB2Prep.bat, is defined as follows:
db2 connect to %2 db2 prep %1.sqx bindfile db2 bind %1.bnd
The command-line for SWPDB2Prep.bat is as follows:
SWPDB2Prep.bat FileName DB2DatabaseName
Our specific example uses the database, SWPDemo (batch file parameter %1). The command-line for our specific example is:
SWPDB2Prep.bat SWPDB2Proc01 SWPDemo
The SWPDB2Proc01 in the previous command-line (batch file parameter %2) is the file portion of the *.sqx file and the file portion of the generated *.cxx file.
The batch file, SWPDB2Prep.bat, is used as a Custom Build step for the *.sqx file. This Custom Build step is setup by selecting Visual C++'s Project menu, the Settings menu item. From the dialog displayed, select the "Custom Build" tab, "Settings for: All Configurations" and the *.sqx file to which the custom build step is applied (SWPDB2Proc01.sqx). Correctly setup, this dialog appears as follows:
In the Project Settings dialog, note the following:
The DB2Cmd utility creates a DB2 Command-Line-Processing environment capable of running the utilities connect, prep and bind. The command-line options for DB2Cmd are:
The command actually executed by DB2Cmd is: ..\Common\SWPDB2Prep.bat $(InputName) SWPDemo. The batch file's first parameter (%1) is set to $(InputName). The ($InputName) is a Visual C++ parameter which corresponds to the input file (the file portion of the *.sqx file, namely SWPDB2Proc01). The batch file's second parameter (%2) corresponds to the database against which the code is bound (database, SWPDemo).
The body of the file, SWPDB2Proc01.sqx contains #include directives for the following headers:
The *.sqx file contains a function whose name corresponds to the name of the stored procedure call. For SWPDB2Proc01.sqx, this function is defined as follows:
SQL_API_RC SQL_API_FN SWPDB2Proc01(void *dummy1, void *dummy2,
struct sqlda *pExchangeDA, struct sqlca *pOutSQLCA)
The function, SWPDB2Proc01, is called by DB2 when the stored procedure call is invoked. The parameters to the stored procedure call are as follows:
It is not required that the function name and the DLL name be identical. However, the authors of this article noticed "unpredictable behavior" when the names differed. The error (a.k.a. the "unpredictable behavior") manifests itself when the stored procedure call is executed (an ADO Command's Execute method is called). Unless you have a prohibitive medical condition and a note from your mother, simply make the DLL and the function name the same. Remember, this "unpredictable behavior" is an install/development issue and is not encountered in deployed code (provided the appropriate steps were taken to avoid the issue). A clear limitation to this strategy is that there is only one stored procedure call per-DLL.
The *.sqx file contains a class, CSWPDB2Proc01Info, derived from CSWPBaseProcInfo (base-class found in SWPDB2ProcCommon.h). The class, CSWPDB2Proc01Info, contains methods used to install and uninstall the stored procedure call in a DB2 database. This class will be reviewed in detail when install/uninstall is presented.
The contents of SWPDB2Proc01.SQX are defined as follows:
// Common-header file used by all stored procedure calls
#include "..\common\SWPDB2ProcCommon.h"
// If this include does appear, then set
// Tools menu, Option Menu Item, Directory Tab, include directories
// to the location of your DB2 include files (typically as follows:
// C:\Program Files\SQLLIB\INCLUDE)
#include "sqlenv.h"
#include <stdio.h>
class CSWPDB2Proc01Info : public CSWPBaseProcInfo
{
// This class will be detailed in the install/uninstall portion
// of the article
};
static CSWPDB2Proc01Info db2Proc01Info;
// This is required by SWPDB2ProcCommon.cpp
CSWPBaseProcInfo *g_pSWPBaseProcInfo = &db2Proc01Info;
SQL_API_RC SQL_API_FN SWPDB2Proc01(void *dummy1, void *dummy2,
struct sqlda *pExchangeDA, struct sqlca *pOutSQLCA)
{
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
long lCount;
EXEC SQL END DECLARE SECTION;
// Count number of tables
EXEC SQL SELECT COUNT(*)
INTO :lCount
FROM SYSCAT.TABLES
WHERE TYPE='T';
sqlvar *pSQLVar;
pSQLVar = &(pExchangeDA->sqlvar[0]);
*(long *)(pSQLVar->sqldata) = SQLCODE; // Set return code parameter
pSQLVar = &(pExchangeDA->sqlvar[1]);
*(long *)(pSQLVar->sqldata) = lCount; // Set the count
#ifdef _DEBUG
char buffer[256];
sprintf(buffer, "SWPDB2Proc01 -- lCount: %d\n SQLCODE: %d\n",
lCount, SQLCODE);
::OutputDebugString(buffer);
return SQLZ_DISCONNECT_PROC;
#else
retun SQLZ_HOLD_PROC
#endif
} // SWPDB2Proc01();
The function (the actual stored procedure), SWPDB2Proc01, contains embedded SQL. All DB2 embedded SQL is pre-fixed by EXEC SQL. The function, SWPDB2Proc01, contains a variable, long lCount. This variable is declared between a BEGIN DECLARE SECTION statement and an END DECLARE STATEMENT, a SQL Declare Section. The variable, lCount, is therefore a "host variable". All host variables are used as part of DB2's pre-compilation of SWPDB2Proc01.sqx. All host variables must be specified in a SQL Declare Section.
The SQL executed by this function (SELECT COUNT(*) INTO :lCount FROM SYSCAT.TABLES WHERE TYPE='T') counts the number of tables in the database (entries with TYPE='T') and places the count into the variable, lCount. This select statement is specific to DB2's schema.
There are limits with respect to the SQL that can be executed within a stored procedure call. These are far to numerous to review in this article. Please consult the documentation for a complete overview of these limitations. The following URL can be used on a machine containing DB's documentation:
file:///C:/Program%20Files/SQLLIB/doc/html/db2s0/frame3.htm#sqls0619
The stored procedure call we are developing contains two parameters. The first parameter is of type integer returns the error code associated with the SQL executed. The code for accessing the return code of the SQL execution is as follows (an excerpt from SWPDB2Proc01.sqx):
pSQLVar = &(pExchangeDA->sqlvar[0]); *(long *)(pSQLVar->sqldata) = SQLCODE; // Set 0'th parameter
The second parameter is of type integer returns the number of tables in the specific DB2 database. This parameter corresponds to the value of the host variable, lCount. The code for setting the second parameter value returned is as follows:
pSQLVar = &(pExchangeDA->sqlvar[1]); *(long *)(pSQLVar->sqldata) = lCount; // Set the count
There is nothing ASP or Visual Studio specific about accessing embedded DB2 stored procedure calls. Numerous texts and articles exist in this subject area. The authors of this article highly recommend A Complete Guide to DB2 Universal Database by Don Chamberlin (Morgan Kaufmann Publishing). This text is specific to DB2 version 6.x, but the concepts apply to DB2 Version 7. The author, Don Chamberlin, is best known as one of the principal inventors of the SQL language.
The following code snippet from SWPDB2Proc.sqx displays status information to debug output using Win32's OutputDebugString function:
#ifdef _DEBUG
char buffer[256];
sprintf(buffer, "SWPDB2Proc01 -- lCount: %d\n SQLCODE: %d\n",
lCount, SQLCODE);
::OutputDebugString(buffer);
return SQLZ_DISCONNECT_PROC;
#else
retun SQLZ_HOLD_PROC
#endif
The output from OutputDebugString can be viewed using a utility from Microsoft's Platform SDK, namely DBMON. This utility can be downloaded from msdn at the following address:
In the previous code snippet two separate return codes are specified for the stored procedure call:
The SQL command CREATE PROCEDURE is used traditionally to create a stored procedure call. In DB2 it also serves to register a stored procedure call that is found in a C++ DLL. Before addressing the nuances of CREATE PROCEDURE some fences need to be mended. Specifically a stored procedure can be categorized as:
The SQL for creating a stored procedure using a C++ is defined as follows (note: this is only a limited subset of what the tasks CREATE PROCEDURE can perform):
CreateProc ::= CREATE PROCEDURE ProcName ((Parameters))
LANGUAGE C
EXTERNAL NAME 'FullDLLPath!FunctionName'
PARAMETER STYLE DB2DARI
FENCED
Parameters ::= (IN | OUT | INOUT) ParmName DataType
The elements of the CREATE PROCEDURE statement are as follows:
DB2's documentation states that it is possible to have two identically named stored procedure calls in the same schema. This is true provided that the number of parameters is different. This is unlike C++ where the number of parameters can be the same but the types must differ. A prudent developer will reduce the risk of any name collision and simply give each stored procedure call a different name.
There are more permutations that can be used with CREATE PROCEDURE. Please consult the documentation or the appropriate text to fully review the options.
A stored procedure call is uninstalled using DROP PROCEDURE ProcName. The value, ProcName, is the name of the DB2 stored procedure call to be dropped from the catalog.
Recall that DB2 stored procedure call DLL's are typically placed either of two locations depending on "fenced" or "un-fenced": \Program Files\SQLLIB\function, and \Program Files\SQLLIB\function\unfenced. According to the DB2 documentation stored procedure calls can be placed in any directory and not just the two previously discussed for "fenced" and "un-fenced" DLL's. The writers of this article have noticed unpredictable behavior when a DLL is placed in a path other than \Program Files\SQLLIB\function\unfenced or \Program Files\SQLLIB\function. The error (a.k.a. the "unpredictable behavior") manifests itself when the stored procedure call is executed (an ADO Command's Execute method is called). Unless you have a deep moral reason against using them, please install the DLL in the prescribed DB2 "fenced" and "un-fenced" directories. This "unpredictable behavior" is an install/development issue and is not encountered in deployed code (provided the appropriate preventative steps are taken).
In Visual C++, if you navigate to Project/Settings/General, you see the following screen - this was set as following to reflect the use of the standard DB2 fenced directory:
Notice in the previous dialog that the "Settings For:" is set to "All configurations". Notice also that "Output Files" is set to C:\Program Files\SQLLIB\function (the default fenced stored procedure call location).
During our company's last project a large number of stored procedure calls were developed. It was noticed that each stored procedure call had to provide the following pieces of information in order to be installed/uninstalled:
Each stored DB2 stored procedure call developed by Software Pronto, Inc. includes header file, SWPDB2ProcCommon.h. This file is defined as follows:
#pragma once
#ifndef SWPDB2PROCCOMMON_HDR__
#define SWPDB2PROCCOMMON_HDR__
#include <windows.h>
class CSWPBaseProcInfo
{
public:
virtual HRESULT GetStoredProcName(BSTR *pbstrName) = 0;
virtual HRESULT GetStoredProcParameters(BSTR *pbstrParms) = 0;
};
extern CSWPBaseProcInfo *g_pSWPBaseProcInfo;
#endif
Each *.sqx file is responsible for creating a class derived from CSWPBaseProcInfo and exposing an instance of this class as a global variable: CSWPBaseProcInfo *g_pSWPBaaseProcInfo. The derived class insures that each stored procedure call provides the stored procedure call's name (retrieved through GetStoredProcName) and parameters (retrieved through GetStoredProcParameters). The file, SWPDB2Proc01.sqx, defines class, CSWPDB2Proc01Info, in order to expose the stored procedure name and parameters associated with SWPDB2Proc01. The source code from SWPDBProc01.sqx specifies class, CSWPDB2Proc01Info, as follows:
class CSWPDB2Proc01Info : public CSWPBaseProcInfo
{
public:
HRESULT GetStoredProcName(BSTR *pbstrSQL)
{
*pbstrSQL = ::SysAllocString(L"SWPDB2Proc01");
if (!*pbstrSQL)
{
return E_OUTOFMEMORY;
}
return S_OK;
}
HRESULT GetStoredProcParameters(BSTR *pbstrParms)
{
*pbstrParms =
::SysAllocString(L"OUT RetCode int, OUT TheCount int");
if (!*pbstrParms)
{
return E_OUTOFMEMORY;
}
return S_OK;
}
};
static CSWPDB2Proc01Info db2Proc01Info;
// This is required by SWPDB2ProcCommon.cpp
CSWPBaseProcInfo *g_pSWPBaseProcInfo = &db2Proc01Info;
The source file, SWPDB2ProcCommon.cpp, provides the code that installs and uninstalls the stored procedure call. This function uses ADO to execute the CREATE PROCEDURE and DROP PROCEDURE SQL statements. The top portion of the source file, SWPDB2ProcCommon.cpp, is as follows:
// Don't forget to link with db2api.lib
#include "SWPDB2ProcCommon.h"
#include <windows.h>
#include <atlbase.h> // for CComBSTR, CComPtr
#include <oledberr.h> // for DB_E_NOTABLE
// C++ complains of a uninary minu applied to an enum (an unsigned)
#pragma warning (disable: 4146)
// If the following fails set Visual C++'s
// Tools menu, Option Menu Item, Directory Tab, include directories
// to the location of your ADO DLL (which contains the ADO type library).
// A typical location is: C:\PROGRAM FILES\COMMON FILES\SYSTEM\ADO
#import <msado15.dll> raw_interfaces_only
static HMODULE ghModule = NULL;
STDAPI DllCanUnloadNow(void)
{
return E_NOTIMPL;
}
STDAPI DllGetClassObject(REFCLSID rclsid, REFIID riid, LPVOID* ppv)
{
return E_NOTIMPL;
}
static CComBSTR gbstrConnectionString = L"PROVIDER=IBMDADB2;DSN=SWPDEMO";
With regard to the previous code snippet, some observations can be made:
The next portion of source file, SWPDB2Proc01, contains a function DllUnregisterServer. The function DllUnregisterServer performs the following steps:
STDAPI DllUnregisterServer(void)
{
HRESULT hr = S_OK;
CComPtr<ADODB::_Connection> spConnection;
if (FAILED(hr = spConnection.CoCreateInstance(
__uuidof(ADODB::Connection))))
{
return hr;
}
if (FAILED(hr = spConnection->put_ConnectionString(
gbstrConnectionString)))
{
return hr;
}
if (FAILED(hr = spConnection->Open(CComBSTR(L""), CComBSTR(L""),
CComBSTR(L""), ADODB::adConnectUnspecified)))
{
return hr;
}
CComBSTR bstrDropProc = L"DROP PROCEDURE ";
CComBSTR bstrProcName;
if (FAILED(hr = g_pSWPBaseProcInfo->GetStoredProcName(&bstrProcName)))
{
return hr;
}
bstrDropProc += (WCHAR *)bstrProcName;
if (FAILED(hr = spConnection->Execute(bstrDropProc, NULL,
ADODB::adCmdText | ADODB::adExecuteNoRecords, NULL)))
{
if (DB_E_NOTABLE == hr)
{
return S_FALSE; // no procedure to delete
}
else
{
return hr;
}
}
return hr;
}
The next portion of source file, SWPDB2Proc01, contains a function DllRegisterServer. The function DllRegisterServer performs the following steps:
STDAPI DllRegisterServer(void)
{
HRESULT hr = S_OK;
typedef enum eLimits
{
NumCharsInPath = MAX_PATH * 2
} tLimits;
TCHAR szFullPath[NumCharsInPath];
TCHAR *ptcFileName;
TCHAR *ptcFileExtension;
CComBSTR bstrCreateProc;
CComBSTR bstrParms;
CComBSTR bstrProcName;
if (!::GetModuleFileName(ghModule, szFullPath, NumCharsInPath))
{
return HRESULT_FROM_WIN32(::GetLastError());
}
ptcFileName = ::PathFindFileName(szFullPath);
if (!ptcFileName)
{
return E_FAIL; // this should not happen
}
ptcFileExtension = ::PathFindExtension(ptcFileName);
if (!ptcFileExtension)
{
return E_FAIL; // this should not happen
}
*ptcFileExtension = NULL;
if (FAILED(hr = g_pSWPBaseProcInfo->GetStoredProcName(&bstrProcName)))
{
return hr;
}
bstrCreateProc = L"CREATE PROCEDURE ";
// CREATE PROCEDURE procName
bstrCreateProc += (WCHAR *)bstrProcName;
// CREATE PROCEDURE procName (
bstrCreateProc += L" (";
if (FAILED(hr =
g_pSWPBaseProcInfo->GetStoredProcParameters(&bstrParms)))
{
return hr;
}
bstrCreateProc += (WCHAR *)bstrParms;
// CREATE PROCEDURE procName (parms) EXTERNAL NAME '
bstrCreateProc += L") EXTERNAL NAME '";
bstrCreateProc += szFullPath;
bstrCreateProc += L"!";
bstrCreateProc += (WCHAR *)bstrProcName;
// CREATE PROCEDURE procName (parms) EXTERNAL NAME 'fullpath!ProcName
bstrCreateProc += L"' "
L"LANGUAGE C "
L"DETERMINISTIC "
L"PARAMETER STYLE DB2DARI "
L"FENCED";
CComPtr<ADODB::_Connection> spConnection;
if (FAILED(hr = spConnection.CoCreateInstance(
__uuidof(ADODB::Connection))))
{
return hr;
}
if (FAILED(hr = spConnection->put_ConnectionString(
gbstrConnectionString)))
{
return hr;
}
if (FAILED(hr = spConnection->Open(CComBSTR(L""), CComBSTR(L""),
CComBSTR(L""), ADODB::adConnectUnspecified)))
{
return hr;
}
if (FAILED(hr = DllUnregisterServer()))
{
return hr;
}
if (FAILED(hr = spConnection->Execute(bstrCreateProc, NULL,
ADODB::adCmdText | ADODB::adExecuteNoRecords, NULL)))
{
return hr;
}
return hr;
}
The next portion of the source file, SWPDB2Proc01, contains a function DllMain. This function makes the DLL's module handle available globally. This module handle is passed into the Win32 function, GetModuleFileName, in order to resolve the DLL's fully qualified path.
BOOL APIENTRY DllMain(HMODULE hModule, DWORD dwReason, LPVOID lpReserved)
{
switch (dwReason)
{
case DLL_PROCESS_ATTACH:
ghModule = hModule;
break;
case DLL_THREAD_ATTACH:
break;
case DLL_THREAD_DETACH:
break;
case DLL_PROCESS_DETACH:
break;
}
return TRUE;
}
When a COM DLL is installed it uses Windows utility called RegSvr32.exe. This utility can also be used to install our stored procedure call DLL. RegSvr32.exe runs as follows:
It is extremely important that every time the package is registered with DB2 that the stored procedure call DLL is re-registered. Recall that when the *.sqx file is modified it will be rebuilt. This process includes executing the batch file, SWPDB2Prep.bat, that in turn registers the package with the specific DB2 server. DB2 maintains a timestamp between the DLL and the package. If the DLL is not re-installed after the package is re-installed the timestamp will conflict. Calls made within the stored procedure call will generate an error, -818. The following code snippet from SWPDB2Proc01.cpp (function, SWPDB2Proc01) is specifically where this error code is returned:
sqlvar *pSQLVar; pSQLVar = &(pExchangeDA->sqlvar[0]); *(long *)(pSQLVar->sqldata) = SQLCODE; // Set return code parameter
The error codes are found in header file, sqlcodes.h (see \Program Files\SQLLIB\include). An excerpt from sqlcodes.h is as follows:
#define SQL_RC_E818 -818 /* time stamp conflict */
The documentation with respect to this matter is far from verbose (to quote):
"An application and an associated package must have matching timestamps for the application to run, or an SQL0818N error is returned to the application."
In order to insure that the package and DLL are installed with each build, a trick is borrowed from COM DLL developers. This trick is to use RegSvr32 as part of a "Custom Build" step. Integration of this custom build step into the project, SWPDB2Proc01, is achieved by going to Project/Settings/Custom Build in Visual C++, and setting it up as follows:
Notice that the Settings For: dialog entry is set to All Configurations. Those not familiar with variables $(TargetPath) and $(OutDir) should look these up in the Visual C++ documentation.
The bulk of the setup is done for a general-purposed DB2 stored procedure infrastructure. The example DLL, SWPDB2Proc.dll, can install itself and un-install itself. At this stage, the DLL should be built so that it can be called from our Active Server Page.
The following test applications are provided to test stored procedure, SWPDB2Proc01:
The source code for SWPTest01.asp is as follows:
<%@ Language="VBScript" %>
<%
'Article: DB2 Stored Procedure Development Using C/C++ DLL's
'Authors: Jan D. Narkiewicz and Benny B. Johansen
'Written: Dec.2000
Option Explicit
'Constants pulled out of adovbs.inc, which normally resides in:
'c:\Program Files\Common Files\System\ado\
'---- DataTypeEnum Values ----
Const adInteger = 3
'---- ParameterDirectionEnum Values ----
Const adParamOutput = &H0002
'---- CommandTypeEnum Values ----
Const adCmdStoredProc = &H0004
function WriteLn(strLine)
Response.Write (strLine+"<BR>" + vbcrlf)
end function
'---------------------------------------------------------------------------
Sub SWPHandleProc01(command, parameters)
Dim retParm, countParm
Dim value
Set retParm = command.CreateParameter("", adInteger, adParamOutput, 4)
parameters.Append(retParm)
Set countParm = command.CreateParameter("", adInteger, adParamOutput, 4)
parameters.Append(countParm)
command.Execute
value = retParm.Value
If 0 <> value Then
WriteLn("DB2 Error: " & value)
return
End If
value = countParm.Value
WriteLn("Number of Tables: " & value)
Set retParm = Nothing
Set countParm = Nothing
End Sub
Sub SWPTestIt01(strStoredProc)
Dim connection
Dim command
Dim parameters
Dim count
Set connection = CreateObject("ADODB.Connection")
connection.Open "PROVIDER=IBMDADB2;DSN=SWPDEMO"
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandText = strStoredProc
command.CommandType = adCmdStoredProc
Set parameters = command.Parameters
If "SWPDB2Proc01" = strStoredProc Then
Call SWPHandleProc01(command, parameters)
Else ' Assume If "SWPDB2Proc02" = strStoredProc Then
WriteLn("Number 2")
End If
Set parameters = Nothing
Set command = Nothing
connection.Close
Set connection = Nothing
End Sub
'----------------------------------------------------------------------
%>
<html>
<head>
</head>
<body>
<h1>DB2 Stored Procedure Development Using C/C++ DLL's</h1>
Written for ASPToday by:<br>
Jan D. Narkiewicz and Benny B. Johansen<br>
<%
Dim strStoredProc
if Request.QueryString="Test01" then
strStoredProc="SWPDB2Proc01"
SWPTestIt01(strStoredProc)
end if
%>
<hr>
Please select one of the following options:<br><br>
<a href=SwpTest01.asp?Test01>Test Stored Procedure 1</a>  
</body>
</html>
The functions of interest within SWPTest01.asp are:
This article is extremely detailed in an attempt to help the reader write more code and spend less time on debugging install/setup issues. This article presented only a simple stored procedure call that returns two values of type integer. Other issues that readers should be aware of:
Given enough reader interest future articles could address DB2 areas such as:
Feedback appreciated!
|
| |||||||
| |||||||||||||||
|
| ASPToday is brought to you by
Wrox Press (http://www.wrox.com/). Please see our terms
and conditions and privacy
policy. ASPToday is optimised for Microsoft Internet Explorer 5 browsers. Please report any website problems to webmaster@asptoday.com. Copyright © 2001 Wrox Press. All Rights Reserved. |