Today you most likely use the Borland Database Engine (BDE) for connecting to your Interbase server. However there is another method, which has not been fully explored that takes Interbase to the next level of productivity. That method is the Interbase API, accessed through the GDS.DLL or the GDS32.DLL. The BDE wraps these DLLs to communicate with the server, and post and retrieve the information requested by an application. Each method has its strong points and currently I have found that a harmony between the two can produce the desired product, your finished application.
The BDE provides easy access to your data which support other database back ends. It provides access to SQL queries and Stored Procedures. It does not handle events, metadata and array data. These functions are available only through using the API. In sample test cases I have found that most SQL statements return apx 5 times faster when using the API. This is due to the fact that your call must navigate through the BDE API to get to the Interbase API level. Interest is growing in removing the BDE completely from your applications to gain this performance boost. However there is currently no clean way to switch from the BDE to the API level without a massive Rewrite of your application. If your application is finished or in development this is the last thing most people would want to do. However, you can gain some of the advantages of API with your application that uses the BDE. This can allow you to take advantage of both feature sets. In this paper I will show where to get the needed information as well has present some of the basic information to get you started.
There is currently no import unit for the Interbase API. The work on the import unit for Delphi will be finished shortly, and may already be available when you read this. All of the functions can be found in the IBASE.H found in the ibserver\include directory of your Interbase server. There are several data types, that have been declared to match that the usage in the API reference Manual, that you should be familiar with. Their ere functionality will be discussed later.
const isc_dpb_version1 = 1; isc_dpb_user_name = 28; isc_dpb_password = 29; isc_tpb_concurrency = 2; isc_tpb_version3 = 3; isc_tpb_wait = 6; isc_tpb_write = 9; Type isc_db_handle = pointer; pisc_db_handle = ^isc_db_handle; isc_long = longint; pisc_long = ^isc_long; isc_status = longint; pisc_status = ^isc_status; isc_tr_handle = pointer; pisc_tr_handle = ^isc_tr_handle; status_vector = array[0..19] of isc_status; pstatus_vector = ^status_vector; ppstatus_vector = ^pstatus_vector; short = word; isc_teb = record db_ptr: pisc_db_handle; tpb_len: longint; tpb_ptr: pchar; end; pisc_teb = ^isc_teb; TXSQLVar = record end; TXSQLDA = record end; PTXSQLDA = ^TXSQLDA; isc_callback = procedure( ptr: pointer; length: short; updated: pchar); EInterbaseErr = class ( Exception);Most of the API functions require the isc_db_handle of an active Database connection, in order to work properly. This can be obtain two ways. The first is by calling the isc_attach_database() function directly. When this function is called you can obtain the isc_db_handle which all subsequent API uses to specify the database. The most complex part of the isc_attach_database() function is the database parameter buffer (DPB). The DPB is used to specify the user name and password for the database, as well as tailoring several other Features of your database connection. For further information regarding the DPB you should read the "Creating and Populating DPB" Section in the "Working with Databases" chapter of the Interbase API guide.
The following procedure is an example of using isc_attach_database() to connect to a database. It uses a function called HandleIBErrors that will be discussed later.
Function AttachDatabase (DatabaseName, username, password: string) : isc_db_handle;
var
Buffer: array[0..1023] of char;
BufPtr: integer;
dbname : array[0..255] of char;
status: status_vector;
IBerrCode: isc_status;
i: integer;
dbHandle : isc_db_handle;
begin
StrPCopy( dbName, DatabaseName);
dbHandle := nil;
{Init the Database Parameter Buffer (DPB)}
FillChar(Buffer,sizeof(Buffer),#0);
BufPtr := 0;
Buffer[0] := char(isc_dpb_version1);
inc(BufPtr);
{Add UserName to the DPB}
Buffer[BufPtr] := char(isc_dpb_user_name);
inc(BufPtr);
Buffer[BufPtr] := char(length(username));
inc(BufPtr);
StrPCopy(@Buffer[ BufPtr],username);
inc(BufPtr,length(username));
{Add Password to the DPB}
Buffer[BufPtr] := char(isc_dpb_password);
inc(BufPtr);
Buffer[BufPtr] := char(length(password));
inc(BufPtr);
StrPCopy(@Buffer[ BufPtr],password);
inc(BufPtr,length(password));
{Perform the actual isc_attach_database Call}
IBerrCode := isc_attach_database( @status, 0, @DBName, @dbHandle,
BufPtr, @Buffer);
{If there is an error an exception will be raised with correct message}
if IBerrCode <> 0 then HandleIBErrors( @status)
Result := dbHandle;
end;
When using this method of calling isc_attach_database() I must also disconnect
from the database. This involves using the isc_detach_database() function.
The following procedure is an example of how it is used.
Procedure DetachDatabase(dbHandle : isc_db_handle); var status : status_vector; IBerrCode : isc_status; begin IBerrCode := isc_detach_database( @status, @dbHandle); if IBerrCode <> 0 then HandleIBErrors( @status); end;The Second method for obtaining the isc_db_handle requires the BDE, and allows you to piggy back an active connection already established using a Database component. It calls the BDE Function DbiGetProp which retrieves the a specified property of an object, in this case I am using the TDatabase object. This method requires a lot less code, but now requires the BDE. If you already have a connection to your Interbase Server with the BDE then you should use this function, otherwise you may want to look into using the previous AttachDatabase and DetachDatabase functions.
function GetDatabaseHandle(var Fdatabase : TDatabase) : isc_db_handle; var length: word; begin if FDatabase.Connected then Check( DbiGetProp( HDBIOBJ(FDatabase.Handle), dbNATIVEHNDL, @result, sizeof( isc_db_handle), length)) else result := nil; end;Now that I have access to the isc_db_handle, I can perform common functions such as starting, committing and rolling back transactions. The Transaction functions use a transaction existence block (TEB). This is similar in complexity to the DPB. However, after careful review it can be exploited to its full potential. The TEB Record consists of 3 variables, the First is a Pointer to the isc_db_handle of the database you wish to start the transaction on, the second is the size in bytes of the transaction Parameter buffer(TPB), and the third is the TPB. The TPB is optional, in order to keep the following example simple I have decided not use the TPB, if you would like more information regarding the TPB, it can be found in the "Creating a Transaction Parameter Buffer" section of the "Working with Transactions" chapter of the Interbase API Guide. A transaction is started by calling the isc_start_multiple() function. This function will allow you to start a transaction over one or more databases on a single Interbase sever. The isc_start_multiple() function, has 4 Parameters, the first is the isc_status variable which is used to handle errors and will be discussed later, the second is the isc_tr_handle which will be used later to either commit or rollback the transaction, the third is the number of TEBs that will be in the fourth parameter, and the fourth is an array of TEBs. The following example demonstrates the use of the isc_start_multiple.
Function StartTransaction (const tebarray: array of isc_teb) : isc_tr_handle;
var
status : status_vector;
IBerrCode : isc_status;
begin
IBerrcode := isc_start_multiple( @status, @result, high(tebarray) + 1 , @tebarray);
if IBerrcode <> 0 then HandleIBErrors( @status);
end;
...
Var
Transaction : isc_tr_handle;
TEB : isc_teb;
...
teb.db_ptr := @DBHandle;
teb.tpb_len := 0;
teb.tpb_ptr := nil;
{ start Transaction }
transaction := StartTransaction(teb);
Once you have started a transaction you will either want to rollback the
transaction or commit the transaction. This is done with one of two functions
isc_commit_transcation and isc_rollback_transaction both take identical
parameters. The first is the status_vector used for error handling, and
the second is the transaction ID, which is obtained from calling isc_start_multiple.
...
var
status : status_vector;
IBerrCode : isc_status;
transaction : isc_tr_handle;
...
begin
...
StartTransaction (teb);
TRY
...
{Work with Database if Error Occurs Automatically Rollback, otherwise Commit}
errcode := isc_commit_transaction( @status, @transaction);
if errcode <> 0 then HandleIBErrors( @status);
EXCEPT
errcode := isc_rollback_transaction( @status, @transaction);
if errcode <> 0 then HandleIBErrors( @status);
END
Most of the API functions result with a isc_status value, which is a long
integer, if it is greater than or less than 0 then an error occured during
the process of the function, and there is a status_vector which contains
information regarding the error. There are 5 error handling functions isc_interprete(),
isc_print_sqlerror(), isc_print_status(), isc_sqlcode(), and isc_sql_interprete(),
Information regarding each of these functions can be found in the Handling
Error Conditions chapter of the Interbase API Guide. Use of isc_print_sqlerror(),
and isc_print_status() should be avoided as they both use the standard
C printf to write the message to the display. Since windows does not support
direct screen writes, it renders these functions as unavailable. For simplicity
I have created a HandleIBErrors procedure that handles Interbase errors,
it uses isc_interprete() to retrieve the Error message and raise a Delphi
exception. The HandleIBErrors function below was written by James Thorpe,
in a set of components available on CompuServe that handle the Interbase
Events.
procedure HandleIBErrors( status: pstatus_vector); var buffer: array[0..255] of char; errMsg, lastMsg: string; errCode: isc_status; begin errMsg := ''; repeat errCode := isc_interprete( @buffer, @status); if lastMsg <> strPas( Buffer) then begin lastMsg := strPas( buffer); if length( errMsg) <> 0 then errMsg := errMsg+#13#10; errMsg := errMsg+lastMsg; end; until errCode = 0; raise EInterbaseErr.Create( errMsg); end;With the knowledge of obtaining the isc_db_handle and transaction management you should be able to create mullet-database transactions, a feature currently not available with the BDE. Also you are well on your way to working with database exclusively with the API.
Another robust feature of Interbase is its Event Alerters, this feature requires use of the API. With Delphi 2.0 C/S it shipped with a component to handle Events. There is also a 16 Bit Version available for download as IBCTRLS.ZIP on CompuServe. I have taken sections of this code and documented what is going on, to give you understanding of what is going on, however I do believe the components are robust enough to handle most needs and you probably won't need to rewrite these components. The TIBEventAlerter has a few key methods to handle the Event Alerters. The first of which is the RegisterEvents method, which calls isc_event_block function which allocates two event parameter buffers (EPBs) for use in other API functions. The first of which is the event_buffer, and the second is the result_buffer. The isc_event_block() function takes a variable amount of parameters, which is not available in Delphi without the use of it's built-in assembler. This is the only instance where built-in assembler is required in the Interbase API, other functions that use variable amount of parameters have alternative functions to avoid this problem. The isc_event_block() takes 3 set Parameters and then 1-15 Pchar parameters for the event names. If you pass this function more than 15 Parameters then the function will fail. If you know how many events you are going to register before hand you can avoid the built-in assembler, by declaring the function to handle your set amount of functions, however, the method of using built-in assembler is preferred.
procedure TIBEventAlerter.RegisterEvents; var i: integer; bufptr: pointer; eventbufptr: pointer; resultbufptr: pointer; buflen: integer; begin ValidateDatabase( Database); if csDesigning in ComponentState then FRegistered := true else begin UnregisterEvents; if Events.Count = 0 then exit; for i := 0 to Events.Count-1 do StrPCopy( @Buffer[i][0], Events[i]); i := Events.Count; bufptr := @buffer[0]; eventbufptr := @EventBuffer; resultBufPtr := @ResultBuffer; asm mov ecx, dword ptr [i] mov eax, dword ptr [bufptr] @@1: push eax add eax, EventLength loop @@1 push dword ptr [i] push dword ptr [resultBufPtr] push dword ptr [eventBufPtr] call [IscEventBlock] mov dword ptr [bufLen], eax mov eax, dword ptr [i] shl eax, 2 add eax, 12 add esp, eax end; EventBufferlen := Buflen; FRegistered := true; QueueEvents; end; end;After the call to the isc_event_block the RegisterEvents method calls QueueEvents which in turn calls DoQueueEvents method which is the next Key Method. The DoQueueEvents method calls the isc_que_events() API function, which is called to request asynchronous notification of events. One of the parameters to isc_que_events() is a pointer to an asynchronous trap(AST) procedure which is declared as isc_callback which references IBEventCallback. The IBEventCallback Procedure triggers the Delphi Event of OnEventAlert so that your application can respond.
procedure TIBEventAlerter.DoQueueEvents; var status: status_vector; errCode: isc_status; callback: pointer; dbHandle: isc_db_handle; begin ValidateDatabase( DataBase); callback := @IBEventCallback; dbHandle := GetNativeHandle; errCode := IscQueEvents( @status, @dbHandle, @EventID, EventBufferLen, EventBuffer, isc_callback(callback), self); if errCode <> 0 then HandleIBErrors( @status); FQueued := true; end;The above two methods provide the necessary code to register your interest in the events request. However you must unregister your interest at some point in time, depending on your applications needs. The TIBEventAlerter has two methods to assist in doing this, UnregisterEvents and CancelEvents. The UnregisterEvents calls CancelEvents which in turn calls the isc_cancel_events() API function. After the call to the isc_cancel_events the isc_free is called to free the memory used by the two EPBs. Isc_cancel_events cancels the request for event notification from the server.
procedure TIBEventAlerter.CancelEvents; var status: status_vector; errCode: isc_status; dbHandle: isc_db_handle; begin if ProcessingEvents then raise EIBError.CreateRes( SInvalidCancellation); if FQueued then begin try // wait for event handler to finish before cancelling events EnterCriticalSection( CS); ValidateDatabase( Database); FQueued := false; Changing := true; dbHandle := GetNativeHandle; errCode := IscCancelEvents( @status, @dbHandle, @EventID); if errCode <> 0 then HandleIBErrors( @status) finally LeaveCriticalSection( CS); end; end; end; procedure TIBEventAlerter.UnregisterEvents; begin if ProcessingEvents then raise EIBError.CreateRes( SInvalidRegistration); if csDesigning in ComponentState then FRegistered := false else if not (csLoading in ComponentState) then begin CancelEvents; if FRegistered then begin IscFree( EventBuffer); EventBuffer := nil; IscFree( ResultBuffer); ResultBuffer := nil; end; FRegistered := false; end; end;There is a very powerful potential with the Event Alerters, as it allows the server to trigger your application instead of the application continually polling the server to look for data. If you are looking for a more complete picture of how Event Alerters work, I suggest you look at the source code to the TIBEventAlerter component more closely.
So far I have showed you how to exploit the features of the API that are not available in the BDE, however, since the BDE uses the API to access the Interbase data it is possible to remove the BDE completely from your application. The process of removing the BDE has several benefits as well as drawbacks. The first of which the BDE interfaces directly with all data-aware controls within Delphi. Direct interface is not available in with the API. The BDE also Handles local caching of sever data. By removing the BDE you can lessen the size of your application during distribution, also you will be removing an API level which can result in increased performance.
The next couple of topic areas involve Dynamic SQL (DSQL), which is used to handle SQL statements. Some of the SQL statements such as metadata, probably should be processed through the API. The SQL statements that return data may be easier to process within the BDE. When Executing any SQL statement in the API you will be using DSQL, it provides for the response of the data the SQL statement requested. Depending on what type of response you need depends on the Function you should use. The following functions are the commonly used functions of DSQL, isc_dsql_execute_immediate(), isc_dsql_alloc_statement(), isc_dsql_prepare(), isc_dsql_execute(), and isc_dsql_fetch(). Each function will be examined in detail as this is the heart of the Interbase API. Array and Blob data are not directly supported with DSQL. DSQL can return an ID that can be used in specific Blob and Array API functions. A Blob field can handle almost any type of data and deserves further review but is beyond the scope of this paper.
As with the other Interbase API functions there are datatypes that you must be familiar to with fully use DSQL. The most common data type is the XSQLDA Record, which refers to the extended SQL descriptor areas.. This Record provides for input and output descriptors. The XSQLDA Record contains a dynamic array of the XSQLVAR Record, which refers to the input and output declaration parameters. The following code segment provides the Type Declarations for the XSQLDA and XSQLVAR.
Type XSQLVAR = record sqltype: SmallInt; sqlscale: SmallInt; sqlsubtype: SmallInt; sqllen: SmallInt; sqldata: PChar; sqlind: ^SmallInt; sqlname_length: SmallInt; sqlname: array[0..31] of Char; relname_length: SmallInt; relname: array[0..31] of Char; ownname_length: SmallInt; ownname: array[0..31] of Char; aliasname_length: SmallInt; aliasname: array[0..31] of Char; end; XSQLDA = record version: SmallInt; sqldaid: array[0..7] of Char; sqldabc: ISC_LONG; sqln: SmallInt; sqld: SmallInt; sqlvar: array[0..0] of XSQLVAR; end;There are four methods to programming with DSQL that the Interbase API mentions in the "Working with Dynamic SQL" chapter. In the following sections I will expose how to use two of those methods. These methods will discuss working with statements without input parameters. The first method I will discuss involves "Non-Query Statements without Parameters" and the second involves "Query Statements without Parameters." Input parameters use the same data structure as the output results and require calling the isc_dsql_decribe_bind() API function and filling the XSQLDA structure with Parameters values. Another method is to perform a dynamically create the SQL Statement before calling the API. In the following sections I will be discussing use of the Output results, however I will not be dealing with the input parameters. If you would like further information on this subject you should refer to the "Working with Dynamic SQL" chapter of the API Guide.
Non-Query Statements without Parameters
This method is useful in several different types of statements that do not produce resultant data Update, Insert, Delete and DDL statements are some of the statements that could be used here. The CREATE DATABASE DDL statement could be used here as well. The first function that will be covered is the isc_dsql_execute_immediate() function, it prepares the DSQL statement specified in the statement parameter, executes it once and discards it. The following code segment demonstrates the use of isc_dsql_execute_immediate(). The last parameter is the XSQLDA Record, was set to NIL value, because I did not have any input parameters.
var trans: isc_tr_handle; status: status_vector; IBerrcode: isc_stat; dbhandle : isc_db_handle; SQLStatement : TStrings; ... IBerrcode := isc_dsql_execute_immediate( @status, @dbHandle, @trans, 0, PChar(SQLStatement.Text), 1, nil); if IBerrcode <> 0 then HandleIBErrors( @status); ...You can also use the isc_dsql_allocate_statement(), isc_dsql_prepare(), and isc_dsql_execute() API functions to do the same thing as the above section of code. If you are going to execute a statement string several times you should use this set of functions over the isc_dsql_execute_immediate() function. The following code segment demonstrates use of these functions. Notice that the XSQLDA record is set to Nil because there is no Input parameters or resultant data.
... StmtHandle := nil; errcode := isc_dsql_allocate_statement(@STATUS,@FDBHandle,@StmtHandle); if errcode <> 0 then HandleIBErrors( @status); errcode := isc_dsql_prepare(@STATUS,@trans,@StmtHandle,0,pchar(SQL.Text),1,nil); if errcode <> 0 then HandleIBErrors( @status); errcode := isc_dsql_execute(@status,@trans,@StmtHandle,1,nil); if errcode <> 0 then HandleIBErrors( @status); ...The above two methods of submitting Non-Query statements without any parameters, can be very useful although initially it can be difficult to see how. You could use this in an application to track user security on your Interbase server, by issuing the correct GRANT rights to each item in the database. Although non-query statements are good for metadata one should not forget the use of Insert, Update and Delete statements, all of which qualify for non-query statements.
Query Statements without Parameters
The API Guide lists three steps to this method.
To Prepare the statement string you must initialize the SQL statement handle (StmtHandle) then allocate the handle with the isc_dsql_allocate() function. The SQL statement is prepared with the isc_dsql_prepare(), then a call to isc_dsql_describe() is made, This sets up the xsqlda with information about the query. Then in the XSQLDA record you compare sqln to sqld to determine if the XSQLDA can handle the number of columns that are returned with the SQL Statement. If the sqld field is greater that the sqln field the structure memory space should be freed and reallocated, to handle the expected number of columns. The sqln and version field should be set up again and another call to the isc_dsql_describe should be made. After the XSQLDA is setup to handle the appropriate amount of columns you must set up the XSQLVAR structure for each item returned. This involves allocating memory for each datatype. The following example demonstrates the first two steps.
var
statusv : status_vector;
I,LP : integer;
out_da : PXSQLDA;
out_var : XSQLVAR;
...
begin
...
StmtHandle := nil;
getmem(out_da,SQLDA_LENGTH(4));
out_da^.version := SQLDA_VERSION1;
out_da^.sqln := 4;
errcode := isc_dsql_allocate_statement(@STATUS,@FDBHandle,@StmtHandle);
if errcode <> 0 then HandleIBErrors( @status);
errcode := isc_dsql_prepare(@STATUS,@trans,@StmtHandle,0,pchar(SQL.Text),1,nil);
if errcode <> 0 then HandleIBErrors( @status);
errcode := isc_dsql_describe(@STATUS,@trans,@StmtHandle,out_da);
if errcode <> 0 then HandleIBErrors( @status);
if out_da^.sqld > out_da^.sqln then
begin
I := out_da^.sqld;
freemem(out_da,SQLDA_LENGTH(4));
out_da^.version := SQLDA_VERSION1;
out_da^.sqln := I;
errcode := isc_dsql_describe(@STATUS,@StmtHandle,1,out_da);
if errcode <> 0 then HandleIBErrors( @status);
end;
for LP := 0 to out_da^.sqld do
begin
out_var := out_da^.sqlvar[LP];
Case out_var.sqltype of
SQL_VARYING : BEGIN
out_var.sqltype := SQL_TEXT;
getmem(out_var.sqldata,sizeof(char) * (out_var.sqllen + 2));
END;
SQL_TEXT : BEGIN
getmem(out_var.sqldata,sizeof(char) * (out_var.sqllen));
END;
. . .
// Process Remaining Types here
END; {Case out_var.sqltype of}
if out_var.sqltype = 1 then
getmem(out_var.sqlind,sizeof(short));
end; //for loop
...
You are now ready to execute the statement. When retrieving data it must
be retrieved within the context of a cursor. You must call isc_dsql_execute()
followed by isc_dsql_set_cursor_name() which opens the needed cursor. Then
you can call the isc_dsql_fetch() API function within a loop to retrieve
and process the data resulting from the SQL statement. The isc_dsql_fetch()
function returns 100 if no more rows remain to be processed. After you
have retrieved all of the information, you need to close the cursor with
the isc_dsql_free_statement() API Function. The following code example
demonstrates how to handle this.
... errcode := isc_dsql_execute(@status,@trans,@StmtHandle,1,nil); if errcode <> 0 then HandleIBErrors( @status); isc_dsql_set_cursor_name(@status,@StmtHandle,'dyn_cursor', null); TRY while fetch_stat = 0 do begin fetch_stat = isc_dsql_fetch(@Status,@StmtHandle,1,out_da) for LP := 0 to out_sqlda^.sqld do Process_Column (out_da^.sqlvar[lp]); end; if fetch_stat <> 100 then HandleIBErrors(@STATUS); FINALLY; isc_dsql_free_statement(@Status,@stmtHandle,DSQL_Close); END; ...Hopefully I have shown you how to use the Interbase API in your applications. However the question arises how to implement it. Development time can definitely increase with the use of the API and time should be spent into producing Class libraries to encompass the API to reduce code time. I would also suggest using the BDE, and using the API to exploit the features that are not available in the BDE, and when speed is a primary concern. This will give you the opportunity to exercise the best features of both the API and the BDE. For further information I suggest study of the Interbase API Guide, and Programmers Guide that can be purchased directly from Borland, as they were not shipped with Delphi 2.0 Client/Server. They both are from a C Perceptive but should provide you will valuable information. The IBASE.H found in the \IBSERVER\INCLUDE Directory is also a valuable source of information. There are also several individuals including myself the that frequent the CompuServe Forums in the BDEVTO Section, which can answer most of your question.
I would appreciate any questions or comments regarding this subject.
Robert J. Love
rlove@pobox.com
PO Box 160085
Freeport, UT
84016-0085