Now, the base application, Btrvapp.exe, can use the wrapper DLL to access SQL Server data. Essentially, the wrapper makes SQL Server look like Btrieve to Btrvapp.exe. The next step is to consider how ODBC and Transact-SQL will access SQL Server data within the scope of the wrapper DLL. The wrapper is designed to use ISAM processing techniques to access SQL Server. Although this implementation successfully accesses SQL Server data without making changes to the base application code, the data is not accessed optimally.
In the Btrieve environment, posBlock is a unique area of memory that is associated with each open file and that contains logical positional information to access records. The Btrieve libraries initialize and use this memory area to perform data functions. The Btrieve application inserts into every Btrieve call a pointer to the posBlock.
The wrapper DLL does not need to maintain any Btrieve-specific data within the posBlock, so it is free to use this memory area for other operations. In the example DLL wrapper, the memory area is used to store the unique identifier for the SQL Server data affected by the requested operation. Regardless of the contents of the posBlock maintained by the wrapper DLL, each memory block must be unique to each corresponding SQL Server table set.
For example, Btrvapp.exe references two Btrieve files, Sales.btr and Titlepub.btr, where Sales.btr contains sales information for each title and Titlepub.btr maintains the title and publisher for each title. These files correspond to the bsales and titlepublishers tables that were created in the pubs database by the sample script, Morepubs.sql. In Btrvapp.exe, the B_OPEN operation opens the requested Btrieve file and creates its corresponding posBlock.
In the wrapper, the same posBlock now references a particular table by name. The wrapper DLL can be designed to store any form of a unique identifier that represents the SQL Server data that it accesses. Table names are used in the context of this migration strategy for ease of presentation. The keybuffer parameter contains the file name of the Btrieve file to be opened when B_OPEN is called. The wrapper DLL implementation of the B_OPEN function sets the posBlock equal to this file or table name. The following code fragment, taken from the wrapper DLL B_OPEN implementation (For more information, see source file Mybtrv32.c on the CD-ROM that accompanies this volume), demonstrates this concept:
/*Step1:*/
if (strlen((BTI_CHAR *) keyBuffer) <= MAX_POSBLOCK_LEN)
memcpy((BTI_CHAR *) posBlock, (BTI_CHAR *) keyBuffer, keyLength);
else
memcpy((BTI_CHAR *) posBlock, (BTI_CHAR* ) keyBuffer,
MAX_POSBLOCK_LEN -1);
In the example, the Sales.btr posBlock is set to Sales.btr and the Titlepub.btr posBlock is set to Titlepub.btr. Btrvapp.exe always knows what SQL Server table set is being referenced based on the file name referenced in the posBlock.
The same data record structure formats are used in both the base application and the wrapper DLL. This allows the wrapper DLL to transport record data between SQL Server and Btrvapp.exe in the same format as if the data were coming from Btrieve. The data record structures used in Btrvapp.exe and Mybtrv32.dll are presented in the following example. For more information, see source files Btrvapp.c and Mybtrv32.c.
/************************************************************
Data Record Structure Type Definitions
************************************************************/
//titlepub record structure
struct{
char TitleID[7]; //string
char Title[81]; //string
char Type[13]; //string
char PubID[5]; //string
float Price; //money
float Advance; //money
int Royalty; //integer
int YTD_Sales; //integer
char PubName[41]; //string
char City[21]; //string
char State[3]; //string
char Country[31]; //string
}tpRec;
//sales record structure
struct
{
char StorID[5]; //string
char TitleID[7]; //string
char OrdNum[21]; //string
int Qty; //integer
char PayTerms[13]; //string
}salesRec;
Within the sample wrapper implementation, the B_OPEN operation establishes a connection to SQL Server for each table set referenced by the base application Btrvapp.exe. The operation also creates and opens the cursors used to reference the SQL Server tables. The cursors are opened on the entire table without a WHERE clause to restrict the number of rows returned. These connections and cursors are used throughout Mybtrv32.dll to reference the SQL Server tables. To avoid the time and processing overhead associated with making or breaking connections to the server, the connections are not terminated until the application is closed.
This connection and cursor implementation were chosen for two reasons. First, they simulate a Btrieve application accessing a Btrieve file: one posBlock for every open file referenced by the application. Second, they demonstrate the inefficient use of connection management when SQL Server is accessed. Only one connection is needed in the context of this wrapper implementation because multiple server cursors can be opened and fetched concurrently on a single connection. Thus, the second connection is only creating overhead within the application. A more efficient connection management methodology uses only a single connection with multiple cursors opened on that connection.
There are many different ways to access SQL Server data with ODBC and SQL. The wrapper Mybtrv32.dll uses server-side cursors. Cursors were chosen for several reasons:
Each Btrieve operation that is performed in the base application is ported to an ODBC and SQL equivalent within the wrapper DLL. Some of the operations, like the B_SET_DIR operation, are not applicable to the SQL Server environment and do nothing within the wrapper DLL. Optimal implementation strategies of ODBC and SQL for both the wrapper DLL and the final application port are presented in “Converting the Application to ODBC and SQL” later in this chapter.
The wrapper DLL must use Btrieve return codes when exiting each function. Each wrapper function must return B_NO_ERROR or a Btrieve error code corresponding to the type of error that was encountered. By using a valid Btrieve return code, the base application code does not know that its library function is accessing SQL Server instead of Btrieve. You must return the Btrieve return codes that are expected by the base application in order for the wrapper DLL to work properly.
However, there is no direct mapping of SQL Server to Btrieve error codes. You must translate all SQL Server errors encountered in the ODBC code of the wrapper DLL to a Btrieve return code equivalent. The following example taken from the MakeConn function in the wrapper DLL source file Mybtrv32.c demonstrates this concept:
// Allocate a connection handle, set login timeout to 5 seconds, and
// connect to SQL Server
rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, hdbc); // Set login timeout
if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
rc=SQLSetConnectAttr(*hdbc, SQL_LOGIN_TIMEOUT,(SQLPOINTER)5, 0);
else{
// An error has been encountered: notify the user and return
ErrorDump("SQLAllocHandle HDBC", SQL_NULL_HENV, *hdbc,
SQL_NULL_HSTMT);
return B_UNRECOVERABLE_ERROR;
}
In case an error is encountered, the SQL Server error code must be mapped to an applicable Btrieve error code. For example, the preceding code fragment allocates a connection handle for use in the wrapper DLL. Because Btrieve does not have the concept of connection handles, it does not have a corresponding error code. The solution is to choose a Btrieve return code that closely matches the severity or context of the message. The connection handle error was severe enough to the application to warrant the Btrieve return code B_UNRECOVERABLE_ERROR. You can choose any Btrieve return code provided that the base application is designed to address it.