IBPhoenix Development

Programming with the InterBase API

By Bernard Wheeler, Borland Developers Conference London 1997

Contents

1. Overview
2. Why Use The API?
3. What Do You Need?
4. Basic Concepts
5. Managing Database Connections
6. Handling Errors
7. Retrieving Connection Information
8. Controlling Simple Transactions Using SQL
9. Processing Query Results
10. Handling Dates
11. Summary

Overview

Any application which can access InterBase information, be it via ODBC, the BDE and SQL Link, or directly (e.g. InterBase Windows ISQL) makes use of the InterBase API. Sometimes that use is via an intermediary, as is the case with ODBC, or SQL Link access.

Accessing InterBase through an intermediary layer frequently obscures some of the functionality available through a native connection. While this does not present any issues for the majority of applications, there can be times when it is downright frustrating: the InterBase server provides some crucial functionality that is not being made available to you.

This paper discusses the basic techniques for accessing InterBase directly through the InterBase API. The development tool used for code illustration is Delphi, since most of the pertinent documentation covers C and C++ access in some depth.

This paper forms a foundation for using the API, from which developers can expand into their own areas of interest. For the sake of clarity and compactness it does not cover the following InterBase topics:

• Using blobs and blob filters
• Using arrays
• Using InterBase events

Why Use The API?

Before getting into the code, it is worthwhile outlining why a developer might forego the convenience (and development speed) afforded by using, say, the BDE and SQL Links for accessing InterBase.

Apart from the obscured functionality (e.g. arrays, blob filters, server and query statistics) mentioned above, there are a couple of other very sound reasons for "going native".

Firstly, if you application is only ever going to access an InterBase database and not Paradox, dBASE, or some other server, the deployment overhead of the BDE and its associated files and DLLs can more than double the "size" of an application.

Using the BDE may also require additional set-up and configuration routines to avoid conflicts with existing BDE installations. If you’ve taken the time and trouble to upgrade your own PC to use the latest version of the BDE and then you install an "off-the-shelf" package which then overwrites your version with a previous version during installation (and without prior warning), you have every right to be somewhat annoyed. Therefore as a developer of software which may be distributed to machines far and wide, you bear some responsibility to ensure that this doesn’t happen with one of your packages.

A second reason why the API may be of interest is application performance. The BDE carries out some pretty complex processing behind the scenes, automatically buffering results, doing datatype conversions and creating and issuing SQL commands on an application’s behalf. All of this takes processing power. If you’re skilled enough to always create your own SQL statements, manage transactions and handle database errors and exceptions yourself, you can optimise your application far more closely by using the InterBase API. In current jargon, your client application will be that little bit "thinner".

What Do You Need?

First of all, you will need both the API software and the right to use that software. This is dependent on the operating system platform under which you are developing.

What you will need next is the "header file" which contains all the definitions and function references for using the API. An official ‘C’ header file is included in the Developer Toolkit.

There is no "official" header file for Delphi, but several variants are available via on-line services. These are all unofficial and unsupported, but actually work - albeit to varying extents. Check that the version you wish to use is compatible with your Windows/Delphi platform combination.

These header files not only contain the function and procedure declarations, they also include definitions of InterBase variable types, so that cross-platform portability of (typically ‘C’) code is achieved.

Basic Concepts

Header Files

Throughout the rest of this paper, I shall refer to the InterBase header file as Ibase.pas, a Delphi version of the official ‘C’ header file Ibase.h. Different versions of the file exist with differing names.

Language Considerations

Since the API was designed for use with ‘C’, there are some steps and precautions which are appropriate to Delphi users in order to avoid errors. Firstly, all function and procedure definitions for use with 16-bit Windows must be qualified by ‘far’ and ‘cdecl’: for 32-bit Windows, ‘stdcall’ will suffice.

Secondly, whenever strings are passed to or from the API, it is safest to use arrays of characters with a null-terminator (when necessary) instead of Pascal-type strings. This may seem over-cautious to some, but care and attention to detail is always helpful in mixed-language development.

InterBase Handles

The InterBase API works with a number of "handles" of varying types to identify what is going on where. For example, there are database handles which identify database connections. There are also transaction handles for identifying transactions and so on.

These handles are allocated as a result of an API call, for example an attachment to a database. The information defining the nature of the attachment (user, password, etc.), transaction or whatever are set up in "parameter buffers" which are passed to the allocating API call.

An illustration:

• A database parameter buffer (DPB) is allocated and initialised with the appropriate values for the connection, including user name and password
• A database handle is allocated and initialised to zero (nil)
• A call is made to isc_attach_database, passing the addresses of both the DPB and the database handle
• As a result of a successful attachment, the value of the database handle is set to a unique, identifying value for that connection

The types for these handles are all defined in the header file Ibase.pas.

The Status Vector

The InterBase API makes extensive use of a status vector for reporting on the success or failure of API requests. This vector is defined as an array of 20 long integers:

ISC_STATUS = ISC_LONG;
ISC_STATUS_VECTOR = array[0..19] of ISC_STATUS;

(The type ISC_LONG is used to ensure compatibility across multiple InterBase platforms, from 16-bit Intel/Windows to 64-bit Alpha/OpenVMS.)

The address of this vector is frequently passed to the API calls, which populate it with the appropriate status values. These can be interpreted to determine the exact error (if any) and the accompanying error message.

Managing Database Connections

Attaching To A Database

We have already covered an overview of the process of attaching to a database in the previous section. The database parameter buffer (DPB) is defined simply as an array of characters without a pre-defined record structure. This buffer will be filled with a number of parameter identifiers, followed by (where appropriate) the length of the parameter value and the parameter value itself. The first parameter has no associated value, but has the identifier of the DPB version - a predefined value in the header file.

The example code below illustrates the initialisation of the DPB with the DPB version number, a user name and a password. A database handle is then initialised and a call to attach to a database is made:

var
Status_Vector: ISC_STATUS_VECTOR;
db_handle: isc_db_handle;
procedure LoginToInterBase;

var
DPB_Buffer: array [0..255] of Char;
DPB_Index: Integer;
DBName_Cstr: array [0..255] of Char;

begin
{Initialise the buffer index and set the first character
value which is always the parameter buffer version number}
DPB_Index := 0;
DPB_Buffer[DPB_Index] := Char(isc_dpb_version1);
Inc(DPB_Index);

{Set the user name parameter identifier and copy the name 
from the user name edit field on the form}
DPB_Buffer[DPB_Index] := Char(isc_dpb_user_name);
Inc(DPB_Index);
DPB_Buffer[DPB_Index] := Char(Length(Edit_Username.Text));
Inc(DPB_Index);
StrPCopy(@DPB_Buffer[DPB_Index], Edit_Username.Text);
Inc(DPB_Index, Length(Edit_Username.Text));

{Set the password parameter identifier and copy the name 
from the password edit field on the form}
DPB_Buffer[DPB_Index] := Char(isc_dpb_password);
Inc(DPB_Index);
DPB_Buffer[DPB_Index] := Char(Length(Edit_Password.Text));
Inc(DPB_Index);
StrPCopy(@DPB_Buffer[DPB_Index], Edit_Password.Text);
Inc(DPB_Index, Length(Edit_Password.Text));

{Copy the database name from the edit field on the form to a
C-string style character array}
StrPCopy(DBName_Cstr, Edit_DBName.Text);

{Initialise the database handle to nil - mandatory}
db_handle := nil;

{Attach to the database}
isc_attach_database(@Status_Vector, Length(Edit_DBName.Text),
@DBName_Cstr, @db_handle, DPB_Index, DPB_Buffer);

{Check for errors…}

There are several other parameter options for the DPB which are listed in the InterBase API Guide. These include options for forcing a database "sweep", a verification check on internal structures, a database shutdown or re-activate, etc.

This API call (if successful) will have used the parameters in the DPB together with the database name passed as a parameter and will have allocated a unique identifying value to the database handle, db_handle.

Detaching From A Database

Detaching from a database is much more simple - in terms of the actual API call necessary. Since the database connection is uniquely identified by the database handle:

(Detach from database}
if db_handle <> nil then isc_detach_database(
@Status_Vector, @db_handle);

(Check for errors}

The above code segment first checks to see that the database handle has not been previously released before making the detach call, since this call will automatically clear the database handle value.

An alternative method for obtaining a database handle from a standard VCL TDatabase component is given below. This uses standard BDE API calls to interrogate a TDatabase component (called Database1 on form Form1 in the code below).

var
db_handle: isc_db_handle;

procedure TForm1.GetNativeHandle;

var
length: word;
buffer: array[0..63] of Char;

begin

{Check to see whether we have a live database connection}
if (Database1.Connected) and (Database1.Handle <> nil) then
begin

{Use a BDE call to check the database type}
Check(DbiGetProp(HDBIOBJ(Database1.Handle), dbDATABASETYPE,
@buffer, SizeOf(buffer), length));

{If the string returned identifies the component as connecting
to an InterBase database, then get the native handle}
if (StrIComp(Buffer, 'INTRBASE') = 0) then
Check( DbiGetProp( HDBIOBJ(Database1.Handle), dbNATIVEHNDL, 
@db_handle, sizeof( isc_db_handle), length))

else

{Otherwise, set the handle to nil to indicate no connection}
db_handle := nil;
end;

Handling Errors

Error Types

In the code segments above there are comments indicating that checks should be made for any errors. There are procedures in the InterBase API designed specifically for this purpose, most of which can be used with Delphi.

Errors are notified to the client application through the status vector and can be separated into two main categories:

1. SQL Errors
2. All other errors

SQL error handling is covered later in this paper.

Processing Non-SQL Errors

If an error arises as a result of an API call, the first element of the status vector is set to 1. Subsequent error codes and information is loaded contiguously into the status vector following on from the first element.

There are two main API calls for processing error codes in the status vector which are isc_print_status and isc_interprete. However, isc_print_status prints error messages directly to the screen - not a sound approach when using a windowed environment.

It is therefore a good idea to construct a replacement for this function using the other call, isc_interprete to extract each error number and obtain the appropriate InterBase message. The example given below uses exceptions to display these messages:

Type

PSTATUS_VECTOR = ^ISC_STATUS_VECTOR

{Declare exception class}

EIBError = class(Exception);

procedure isc_print_status(Status_Vector_ptr: PSTATUS_VECTOR);

var
msgBuffer: array[0..511] of Char;
errMsg: String;
lastMsg: String;
errCode: ISC_STATUS;

begin

{Start off the output message with a standard header)
errMsg := 'InterBase error:' + #13#10;

{For each non-zero code in the status vector, use isc_interprete to
return the next error message text into the local buffer}
repeat

errCode := isc_interprete(@msgBuffer, @Status_Vector_ptr);

{If we have a new error message, add a new line to our output
message that will appear in the exception, converting it
from a C-string first}
if lastMsg <> StrPas(msgBuffer) then

begin
lastMsg := StrPas(msgBuffer);
if Length(errMsg) <> 0 then
errMsg := errMsg + #13#10;
errMsg := errMsg + lastMsg;
end;

until errCode = 0;

{Finally raise the error with the message}
raise EIBError.Create(errMsg);
end;

The majority of InterBase API calls return a status value indicating success or failure, so a common method of error handling (using the procedure above) would look like:

{Attach to the database}
if isc_attach_database(@Status_Vector, Length(Edit_DBName.Text),
@DBName_Cstr, @db_handle, DPB_Index, DPB_Buffer) <> 0 then

{Check for errors…}
isc_print_status(@Status_Vector);

Retrieving Connection Information

Overview

There are a number of informational and statistical items that can be retrieved from a connection to an InterBase database that are not directly available through the BDE.

An API application can determine the database page size, the number of pages allocated, version information of the database implementation, current and peak memory allocation, currently connected users (SuperServer architecture only), server performance statistics, etc.

These items of information are obtained through the use of the isc_database_info API call. This function is passed the details of two byte array buffers, one for input and one for output.

Buffer Formats

The input buffer is simply a set of contiguous single byte request identifiers, the values of which are defined in the header file Ibase.pas .

The result buffer is a repeating clusters of values filled in by the API call. The sequence in each cluster is:

1. A single byte containing the item identifier requested
2. A two-byte number specifying the number of bytes remaining in the cluster. This two-byte number is platform independent and is interpreted for the local platform by the API call isc_vax_integer.
3. A value stored in a variable number of bytes whose interpretation (string, number, etc.) depends on the item type requested.

Requesting Numeric Information

Below is a code segment which shows the request of the page size and allocation details from an already active database handle. These values are then displayed in two Delphi edit fields:

procedure TForm1.GetConnStats;

var

{Options buffer and index}

DBStatOpts: array [0..15] of Byte;
NumOpts: Integer;

{Result buffer array, index and pointer}

Result_Buffer: array [0..1023] of Byte;
RB_index: Integer;
RB_ptr: Pointer;

{Variables for handling each return item}
Item: Byte;
ItemLen: ISC_LONG;
Page_Size: Longint;
Num_Pages: Longint;

begin

{Exit if we’re not connected}
if db_handle = nil then
Exit;

{Set up the information options required}
NumOpts := 0;
DBStatOpts[NumOpts] := isc_info_page_size;
Inc(NumOpts);
DBStatOpts[NumOpts] := isc_info_allocation;
Inc(NumOpts);

{Issue the call to get the information required}
if isc_database_info (@Status_Vector, @db_handle, NumOpts,
@DBStatOpts, SizeOf(Result_Buffer), @Result_Buffer) <> 0 then
isc_print_status(@Status_Vector);

{Progress through the result buffer for each returned item cluster}
RB_Index := 0;
while (Result_Buffer[RB_Index] <> isc_info_end) do

begin

{Get the item type identifier}
Item := Result_Buffer[RB_Index];
Inc(RB_Index);
RB_ptr := @Result_Buffer[RB_Index];

{Get the length of the following data, translated from the
portable form}
ItemLen := isc_vax_integer(RB_ptr, 2);
Inc(RB_Index, 2);
RB_ptr := @Result_Buffer[RB_Index];

{Check the item type and process it accordingly}
case Item of

isc_info_page_size:
Page_Size := isc_vax_integer(RB_ptr, ItemLen);

isc_info_allocation:
Num_Pages := isc_vax_integer(RB_ptr, ItemLen);

end; {case}

{Increment our result index to the start of the next cluster}
Inc(RB_Index, ItemLen);

end; {while}

{Put the returned values in the form fields}
Edit_PageSize.Text := IntToStr(Page_Size);
Edit_NumPages.Text := IntToStr(Num_Pages);

end;

Handling Other Types Of Information

Some information options, such as isc_info_version return character strings (not null-terminated) preceded by a further single byte length indicator. The return results should be processed accordingly - bearing in mind that these are C-style byte arrays, rather than Pascal strings.

One particular information option - isc_info_user_names, returns one cluster for each user logged onto the system (on SuperServer architecture systems), so this cluster will repeat for the total number of users. For this reason the processing loop checks that the next cluster identifier is not isc_info_end, rather than simply counting the number of options requested.

Controlling Simple Transactions

Overview

Having successfully attached to a database, one would normally wish to access some of the data stored within. The scope of this paper covers the use of dynamic SQL commands.

Virtually every SQL command in InterBase needs to take place within the scope of a transaction (two exceptions being CREATE DATABASE and SET TRANSACTION). Whereas when using the BDE, or the InterBase Windows ISQL tool transactions can be implicitly started and explicitly committed, both operations must be explicitly executed when using the API.

In a similar way to database connections, transactions in InterBase are managed through the use of handles. Since transactions in InterBase do not fall within the scope of a single database (unlike the BDE), each transaction needs to be informed of all the databases which come within its scope, together with any specific options such as isolation level, lock contention behaviour, etc.

Buffers Used For Obtaining A Transaction Handle

Since a transaction can span a variable number of databases, the process for obtaining a transaction handle is slightly more complex than that for obtaining a database handle. There are two API calls designed for starting transactions:

• isc_start_transaction
• isc_start_multiple

The problem for Delphi developers with the first of these functions is that it takes a variable number of parameters depending on how many databases will take part in the transaction. Therefore the function to use with Delphi is isc_start_multiple. This makes use of two different buffer structures: one or more transaction existence buffer (TEB); and a transaction parameter buffer (TPB) for each TEB.

Each TEB is defined in the header file as having the following structure:

type

ISC_TEB = record
db_ptr: pisc_db_handle; {The address of a valid database handle}
tpb_len: Longint; {The length of a TPB for this database}
tpb_ptr: PChar; {The address of a TPB fir this database}

end;

The call to isc_start_multiple takes the address of the first member of an array of TEB records (one for each participating database) as its last parameter. As can be seen above, each TEB refers to a TPB.

The TPB for each database is constructed to include all the configuration options to be applied for this transaction. These options specify whether the transaction is read only, or read/write, what the transaction isolation level is to be (concurrency, consistency, read committed), lock resolution behaviour and whether any table locks are to be reserved for this transaction.

The transaction parameter buffer is simply a byte array, the first member of which is always the TPB version number.

Using The Default Transaction Behaviour

InterBase allows a default TPB to be used for all transactions. This has the characteristics that are equivalent to the declaration below:

var

Default_TPB: array [0..3] of Byte;

begin

Default_TPB[0] := isc_tpb_version3;
Default_TPB[1] := isc_tpb_write;
Default_TPB[2] := isc_tpb_concurrency;
Default_TPB[3] := isc_tpb_wait;

This default TPB therefore provides read/write transaction with a repeatable read isolation level that waits when it encounters any lock conflicts (such as attempting to update a record that has already been updated by another transaction which hasn’t yet committed or rolled back).

To use the default TPB, simply set the address of the TPB to nil in the TEB and its length to 0 as below:

var

db_handle: isc_db_handle; {Database handle}
teb: ISC_TEB; {Transaction existence buffer}

procedure Init_Transaction;

begin

{If we’re not connected to this database, exit}
if (db_handle = nil) then

exit;

{Set up the TEB to use the default transaction style}
teb.db_ptr := @db_handle;
teb.tpb_len := 0;
teb.tpb_ptr := nil;
end;

The above example indicates that this transaction is going to apply to one database only, since only one TEB is declared. If you know in advance how many database connections will be involved in each transaction, you can define your array bounds for your TEBs at design time (the array of TEBs must be contiguous in memory). Alternatively, you can allocate the memory dynamically, making sure that it gets freed before the application exits.

Starting A Transaction

Once the TEBs have been initialised, the transaction can be started and a transaction handle allocated:

var

tr_handle: isc_tr_handle; {Transaction handle}

procedure Start_Transaction;

begin

{Set the unallocated transaction handle to nil - mandatory before
a call to start a transaction}
tr_handle := nil

{Start the transaction, passing the address of the TEB initialised
earlier}
if isc_start_multiple(@status, @tr_handle, 1, @teb) <> 0 then
isc_print_status(@status)

end;

Normally the transaction handle would be initialised at the same time as the TEB. The Start_Transaction procedure above would then check to see that the handle was unused before making the API call, raising an error or exception if the transaction handle was already in use.

Ending Transactions

Transactions must be ended to leave the database(s) in a consistent state. They can be committed, confirming all the changes made, releasing any locks on data and making the new data values available to other users of the database; or they can be rolled back, undoing all the changes made since the transaction was started, again releasing any data locks held.

The functions used for these two operations are extremely straightforward. The API calls to use are isc_commit_transaction and isc_rollback_transaction. They are both used in the fashion below:

{Check that the transaction that we have is actually active}
if (tr_handle <> nil) then
begin

{Commit the transaction. Rollback has the same format}
if isc_commit_transaction(@Status_Vector, @tr_handle) <> 0 then
begin

isc_print_status(@status);

end;

{As an extra precaution explicitly clear the transaction handle}
tr_handle := nil;

end;

Remember that apart from in the two circumstances highlighted at the start of this section, once a transaction has ended, either a new one must start, or the database connection must be dropped. Also remember that if a transaction is not ended before the application exits, it is automatically rolled back.

Save Points Within Transactions

The API call isc_commit_retaining commits all the outstanding work for a transaction, but does not free up any system resources, record streams or cursors which are associated with the transaction. This process is faster than committing a transaction in the normal way, starting a new one and re-issuing all the appropriate queries. The format of the call is identical to isc_commit_transaction.

If the transaction is later rolled back, work is only undone to the point of the last isc_commit_retaining. Any previous work in the transaction is retained.

This allows the concept of "save points" to be efficiently implemented within a transaction. The transaction should still be finally committed or rolled back.

Multi-Database Transactions

InterBase automatically invokes a two-phase commit protocol when multiple databases are involved in a single transaction. The process for this is therefore:

1. Either declare, or allocate memory for an array of ISC_TEB records, one for each database
2. Initialise each TEB with the appropriate database handle (previously obtained from a call to isc_attach_database) and either TPB details, or nil and 0 to indicate use of the default TPB
3. Initialise a transaction handle to nil
4. Call isc_start_multiple, passing the address of the first member of the TEB array
5. Issue whatever dynamic SQL commands you wish
6. Call either isc_commit_transaction, or isc_rollback_transaction, depending on the results of your SQL commands

This is exactly the same process as for single database transactions.

Using SQL

Overview

The InterBase API has a rich functionality set to cater for the efficient issuing of dynamic and repeated SQL commands to the database. These SQL commands can include data definition language (DDL) statements, such as ALTER TABLE and CREATE INDEX as well as data manipulation language (DML) statements like SELECT and UPDATE. SQL commands themselves are passed to the database as simple character strings.

There are two main ways of executing SQL commands:

1. Immediately, once only, for statements not returning data
2. Repeatedly, perhaps with varying parameters

The first method simply passes the SQL string to the database via a call to the function isc_dsql_execute_immediate and doesn’t look for any results - indeed an error is returned if there are any. This method is not covered in any further detail in this paper.

The second method uses statement handles to manage the preparation and execution of commands and the control of input and output values.

Statement Handles

In order to manage and manipulate statements and their results, InterBase makes use of statement handles, in much the same way as it uses database handles and transaction handles.

Statement handles are allocated when a statement is "prepared" for execution.

InterBase allows statements to hold parameters which will be "filled in" just before execution and allows efficient repeat execution of queries. These parameters are indicated by a placeholder character, "?", as in the example statement below:

SELECT first_name, last_name FROM employee WHERE dept_no = ?

Passing Parameters And Return Values

Parameters and return values are passed between the database and the client application via extended SQL descriptor areas (XSQLDAs). Each command can have up to two associated XSQLDAs, one for input parameters, one for output parameters.

The XSQLDA structure consists of a header block and an array of XSQLVAR records, one for each input parameter or result column. The number of members of the XSQLVAR array is indicated by a value in the XSQLDA header area.

The XSQLDA structure has to cope with variable numbers of parameters. Indeed, the number of return parameters (e.g. columns) may not be known before the command execution process is begun - how many columns will be returned by the statement "SELECT * FROM EMPLOYEE", for example? This means that memory is frequently dynamically allocated and freed when handling SQL commands, especially when those commands can be customised to some extent by an application’s users.

The definitions of the XSQLDA and XSQLVAR records are given below:

Type

XSQLVAR = record
sqltype: Smallint; {Datatype of field}
sqlscale: Smallint; {Scale factor, NUMERIC & DECIMAL}
sqlsubtype: Smallint; {Subtype for BLObs & Text types} 
sqllen: Smallint; {Length of data area}
sqldata: Pointer; {Address of data}
sqlind: ^Smallint; {Address of NULL indicator}
sqlname_length: Smallint; {Length of sqlname field}
sqlname: array [0..31] of Char; {Name of field}
relname_length: Smallint; {Length of relation name}
relname: array [0..31] of Char; {Field's relation name}
ownname_length: Smallint; {Length of owner’s name}
ownname: array [0..31] of Char; {Relation's owner name}
aliasname_length: Smallint; {Length of alias name}
aliasname: array [0..31] of Char; {Relation's alias name}

end;

PXSQLVAR = ^XSQLVAR;

XSQLDA = record
version: Smallint; {Version of this XSQLDA}
sqldaid: array [0..7] of Char; {Reserved for future use}
sqldabc: ISC_LONG; {Reserved for future use}
sqln: Smallint; {Number of XSQLVARs allocated}
sqld: Smallint; {Actual number of fields}
sqlvar: array [0..0] of XSQLVAR; {First XSQLVAR}

end;

PXSQLDA = ^XSQLDA;

The definition above sets the XSQLDA to only include one XSQLVAR (which is the minimum anyway). If more than one parameter is to be passed or returned, memory will need to be explicitly allocated and freed for these structures. For this purpose, a procedures is included in the header file for determining the length of the above structures and can be used as below:

var

POut_DA: PXSQLDA;
num_cols: Integer;

begin

{Allocate sufficient memory for the XSQLDA structure, given
a specific number of columns}
GetMem(POut_DA, XSQLDA_LENGTH(num_cols));

end;

[The API call used to determine the number of return columns for a statement will be covered shortly.]

Bear in mind with this approach that Delphi will only allow direct access (using the code example above) to POut_DA.sqlvar[0], since that’s what the record definition has. To work around this, either use a for loop with counter specifying the array element to set or examine, or use a pointer of type PXSQLVAR.

The Process Of Using SQL

The overall process of using any dynamic SQL statement with InterBase (given that the database is attached and a transaction has been started) is as follows:

1. Construct the SQL string (with any placeholders)
2. Allocate a statement handle
3. Set up a "dummy" output XSQLDA
4. Prepare the SQL statement - this gives details of the returned columns into the "dummy" output XSQLDA
5. Allocate any necessary memory for the "real" output XSQLDA
6. Initialise your output XSQLDA and XSQLVAR records: set the locations into which any returned values will be placed and their NULL flags - this may have to be dynamically allocated memory and should correspond to the correct InterBase data types
7. Re-bind your "real" output XSQLDA to the statement
8. Prepare a "dummy" input XSQLDA
9. Bind the "dummy" XSQLDA to the statement and check to see that it covers all the expected input parameters
10. Allocate any necessary memory for a "real" input XSQLDA
11. Initialise your input XSQLDA and XSQLVAR records: set the locations from which any input parameters and their NULL flags will be read - these will be local variables of the appropriate InterBase data types
12. Re-bind your new "real" input XSQLDA to your statement
13. Set your parameter values
14. Execute your statement
15. Declare a cursor for the result set if required
16. Process the results - covered in the next section
17. Close statement (and cursor)
18. Free the statement handle and all allocated memory

This may on the surface seem to be a particularly long-winded process, but once steps 1 to 12 have been successfully completed, only steps 13 to 17 need to be repeated for further executions of the statement with different parameters.

Using An INSERT With Parameters

Below is a code example which uses existing database and transaction handles to insert data (a "not null" integer followed by two strings) into a table. Note that some of the calls require a "dialect" value, which must always be set to the value 1.

var

stmt_handle: isc_stmt_handle;

In_DA: XSQLDA
PIn_DA: PXSQLDA;
Send_Val1: ISC_LONG;
Send_Val1_null: Smallint;
Send_Val2: array [0..255] of Char;
Send_Val2_null: Smallint;
Send_Val3: array [0..255] of Char;
Send_Val3_null: Smallint;

{plus the database and transaction handles and the status vector} 

procedure ready_statement;

var

SQL_Cstring: array [0..511] of Char;
LoopCount: Integer;
NumParams: Integer;

begin

{Build the SQL statement}
StrPCopy(SQL_Cstring, ‘INSERT INTO mytab VALUES (?, ?, ?)’);

{Allocate a statement handle}
if isc_dsql_allocate_statement(@Status_Vector, @db_handle,

@stmt_handle) <> 0 then
isc_print_status(@Status_Vector);

{Prepare the statement for repeat execution. We know we’re not
expecting any output results, so the output XSQLDA address is
set to nil}
if isc_dsql_prepare(@Status_Vector, @tr_handle, @stmt_handle, 0,
@SQL_Cstring, 1, nil) <> 0 then
isc_print_status(@Status_Vector);

{Initialise the XSQLDA version number - mandatory}
In_DA.Version := SQLDA_VERSION1;

{With the "dummy" input XSQLDA, find out how many input
parameters are expected}
if isc_dsql_describe_bind(@Status_Vector, @stmt_handle, 1,
@In_DA) <> 0 then
isc_print_status(@Status_Vector);

{Check the number of parameters InterBase is expecting}
NumParams := In_DA.SQLd;
if NumParams = 1 then

{We’ve already got enough memory for our input XSQLDA}
PIn_DA := @In_DA
else

begin

{Allocate sufficient memory and clean it out}
GetMem(PIn_DA, XSQLDA_LENGTH(num_cols));
FillChar(PIn_DA^, XSQLDA_LENGTH(num_cols), #0);
end; 

{Reset the values in our "real" input XSQLDA header}
PIn_DA.Version := SQLDA_VERSION1;
PIn_DA.SQLn := NumParams;
PIn_DA.SQLd := NumParams;

{Loop through the XSQLVAR records setting the locations of the
parameter variables, their null indicators and their types - as
defined in the header file}
for LoopCount := 0 to NumParams - 1 do
case LoopCount of
0 : begin

PIn_DA.sqlvar[LoopCount].sqltype := SQL_LONG;
PIn_DA.sqlvar[LoopCount].sqldata := @Send_Val1;
PIn_DA.sqlvar[LoopCount].sqlind := @Send_Val1_null;
end;

1 : begin

PIn_DA.sqlvar[LoopCount].sqltype := SQL_TEXT + SQL_NULL;
PIn_DA.sqlvar[LoopCount].sqldata := @Send_Val2;
PIn_DA.sqlvar[LoopCount].sqlind := @Send_Val2_null;
end;

2 : begin

PIn_DA.sqlvar[LoopCount].sqltype := SQL_TEXT + SQL_NULL;
PIn_DA.sqlvar[LoopCount].sqldata := @Send_Val3;
PIn_DA.sqlvar[LoopCount].sqlind := @Send_Val3_null;
end;
end; {case}

{end for}

{Re-bind to the real input XSQLDA}
if isc_dsql_describe_bind(@Status_Vector, @stmt_handle, 1,
PIn_DA) <> 0 then
isc_print_status(@Status_Vector);

end;

procedure execute_statement;

var

NumParams: Integer;
LoopCount: Integer;

begin

{Loop through the parameters and allocate their values}
for LoopCount := 0 to NumParams - 1 do
case LoopCount of
0 : begin
Send_Val1 := StrToInt(Form1.Edit_Param1);
Send_Val1_null := 0; {Not used anyway}
end;

1 : begin

StrPCopy(Send_Val2, Form1.Edit_Param2.Text);
PIn_DA.sqlvar[Loopcount].sqllen :=
Length(Form1.Edit_Param2.Text);

if Form1.Edit_Param2.Text <> '' then
Send_Val2_null := 0 {Not null}
else
Send_Val2_null := -1; {Null}
end;

2 : begin

StrPCopy(Send_Val3, Form1.Edit_Param3.Text);
PIn_DA.sqlvar[Loopcount].sqllen :=
Length(Form1.Edit_Param3.Text);

if Form1.Edit_Param3.Text <> '' then
Send_Val3_null := 0 {Not null}
else
Send_Val3_null := -1; {Null}
end;
end; {case}

{end for}

{Execute the statement - once again the dialect is 1 and the
address of the output XSQLDA is nil (no returned values)}
isc_dsql_execute2(@Status_Vector, @tr_handle, @stmt_handle,1, PIn_DA, nil)

{Check for SQL errors}
{Close the statement}

if isc_dsql_free_statement(@Status_Vector, @stmt_handle, DSQL_close) <> 0 then
isc_print_status(@Status_Vector);

end;

Using A SELECT With No Parameters

This process is similar to that given above:

• The calls to isc_dsql_describe_bind are replaced by calls to isc_dsql_describe
• isc_dsql_describe populates the output XSQLDA with the expected column types, names, etc.
• Memory needs to be allocated, or variables nominated to receive the incoming column values. Examining the output XSQLVAR records gives sufficient information for this to be done
• The call isc_dsql_execute is used in place of isc_dsql_execute2

Handling SQL Errors

SQL errors are notified to the calling application in exactly the same way as all other errors: through the status vector. However, there is also a convention of "SQLCODE" numbers, which can be interpreted. These are accessed by calling isc_sqlcode:

var

SQLCODE: ISC_LONG;

begin

if isc_dsql_execute2(@Status_Vector, @tr_handle, @stmt_handle, 
1, nil, POut_DA) <> 0 then

begin

{Get the SQLCODE and process the errors}
SQLCODE := isc_sqlcode(@Status_Vector);
isc_print_sqlerror(SQLCODE, @Status_Vector);

end;

end;

The InterBase API includes a default isc_print_sqlerror procedure which behaves in much the same way as the default isc_print_status, but gives SQL error messages. The code for a replacement which doesn’t write directly to the screen is given below.

Type

PSTATUS_VECTOR = ^ISC_STATUS_VECTOR

{Declare exception class}
EIBError = class(Exception);

procedure isc_print_sqlerror(SQLCODE: ISC_LONG; status_vector_ptr: PSTATUS_VECTOR);

var

msgBuffer: array[0..511] of Char;
errMsg: String;

begin

errMsg := 'InterBase SQL error (' + IntToStr(SQLCODE) + '):';

{Interpret the SQL code and return the error message}
isc_sql_interprete(SQLCODE, @msgBuffer, SizeOf(msgBuffer));

{Add the message text following a new line}

errMsg := errMsg + #13#10 + StrPas(msgBuffer);

{Raise the exception}

raise EIBError.Create(errMsg);

end;

Processing Query Results

Overview

Once the query has been executed, results will need to be processed by the calling application. Details of the column names, their types, their data length and their null status are all passed via the output XSQLDA. This is populated by the isc_dsql_describe call, which is called before the SQL query is actually executed. Therefore, after this call and before the statement is executed, memory space or receiving variable need to be allocated and their addresses entered into the output XSQLDA.

Once the query has been executed, each result row is returned by successive calls to isc_dsql_fetch. Between each of these calls the data residing in the receiving variables must be processed, as the next "fetch" will overwrite that data. When a fetch call returns a value indicating that there are no more rows, the statement can be closed and freed, along with all the memory dynamically allocated for the processing of that statement.

Allocating Receiving Variables

If the result columns and their order are known at application compile time, a developer can simply declare standard variables to receive the incoming data and their NULL value indicators:

Type

{Type declaration for VARCHAR variables up to 255 characters in
length}
ISC_VARCHAR = record
len : Smallint;
text : array [0..255] of Char;

end;

var

F_Name : ISC_VARCHAR;
L_Name : ISC_VARCHAR;
P_Ext : ISC_VARCHAR;
NullCol1 : Smallint;
NullCol2 : Smallint;
NullCol3 : Smallint; 

{Output XSQLDA declared and populated by isc_dsql_describe_bind 
elsewhere}
procedure alloc_recv_vars;

var

LoopCount: Integer;

begin

{Loop through the XSQLVAR array and allocate variables for the
actual values and their null indicators. We know their types
and positions in advance in this case}
for LoopCount := 0 to POut_DA.SQLd - 1 do
case LoopCount of

0 : begin

POut_DA.SQLvar[LoopCount].SQLdata := @F_Name;
POut_DA.SQLvar[LoopCount].SQLind := @NullCol1;

end;

1 : begin

POut_DA.SQLvar[LoopCount].SQLdata := @L_Name;
POut_DA.SQLvar[LoopCount].SQLind := @NullCol2;

end;

2 : begin

POut_DA.SQLvar[LoopCount].SQLdata := @P_Ext;
POut_DA.SQLvar[LoopCount].SQLind := @NullCol3;

end;

end; {case}

{end for}
end;

Alternatively, if queries are used for which the returning columns are not known at design time, for example SELECT * FROM employee, a more dynamic procedure is needed, along the lines of the code below which caters for CHAR, VARCHAR, DATE and INTEGER columns:

procedure alloc_dyn_recv_vars;

var

LoopCount: Integer;
Cur_Var: PXSQLVAR;
Field_length: Smallint;
Field_type: Smallint;
Field_null: Smallint;

begin

for LoopCount := 0 to POut_DA.sqld - 1 do

begin

{Set a pointer to the next XSQLVAR record}
Cur_Var := @POut_DA.sqlvar[LoopCount];

{Get the field type and length}
Field_type := Cur_Var.sqltype;
Field_length := Cur_Var.sqllen;

{If the field type is odd, it supports nulls. We’re not
interested in that just yet}
Field_null := 0;

if Odd(Field_type) then
begin

Dec(Field_type);
Field_null := 1;

end;

{Allocate memory depending on the incoming type}
case Field_type of
SQL_TEXT:

begin
GetMem(Cur_Var.sqldata, Field_length);
end; 

SQL_LONG:
begin
GetMem(Cur_Var.sqldata, SizeOf(ISC_LONG));
end;

SQL_DATE:
begin
{InterBase dates are stored in a "quadword" form}
GetMem(Cur_Var.sqldata, SizeOf(ISC_QUAD));
end;

SQL_VARYING:
begin
{Coerce the type into SQL_TEXT, for null termination of
the incoming string remembering to add the null flag}
Cur_Var.sqltype := SQL_TEXT + Field_null;

{Allocate enough space for the string and its length value}
GetMem(Cur_Var.sqldata, Field_length + 2);
end;

end; {case}

{Now to handle NULL values}
if Field_null <> 0 then
GetMem(Cur_Var.sqlind, SizeOf(Smallint));

end; {for}

end;

Coercing Returned Data

Returned data can be "coerced" into a different type, by altering the "sqltype" member of an XSQLVAR record (as shown above).

An example use of data coercion might be for receiving VARCHAR columns without having to deal with variable length data, or the length indicator at the beginning of the returned value. Instead, data is returned (space filled) as though it were a CHAR column.

Coercion can only be from one type to another compatible type, like from SQL_SHORT to SQL_LONG.

NUMERIC and DECIMAL types

These column types will be returned as SQL_SHORT, SQL_LONG, or SQL_DOUBLE, depending on the precision used for their definition.

Floating point data is handled by the integer types (SQL_SHORT and SQL_LONG) by applying a scaling factor which will be stored in the "sqlscale" member of the XSQLVAR record. Therefore, when processing any "unknown" incoming data of these types, the "sqlscale" value must be checked to see whether local conversion back to a floating point type is necessary.

The scaling factor is stored as a negative integer indicating the power of 10 by which the integer value must be multiplied:

a sqlscale value of -3 indicates that the value stored at the location pointed to by sqldata must be multiplied by 10-3, i.e. divided by 1000.

Fetching And Processing Data

When a query returns multiple records they need to be fetched one by one until there are no more waiting. This is done by using the isc_dsql_fetch call, which returns a value of 100 when there are no more records. Surprisingly enough, there is no pre-defined constant to represent this in the InterBase header file.

When a fetch is successful, data must be extracted from the receiving variables and processed. The sample code below displays each new row in the next line of a TStringGrid component. It uses a scenario in which all the data is either returned as, or coerced to SQL_TEXT:

procedure TForm1.Fetch_Row;

var

FetchStat: Integer;
SQLCODE: Integer;
LoopCount: Integer;

begin

{Assume that the database is connected, a transaction started and
a SQL statement has successfully executed}
{Fetch next record}
FetchStat := isc_dsql_fetch(@Status_Vector, @stmt_handle, 1, POut_DA);

{Check to see if there are any errors}
if FetchStat <> 0 then
begin

{Check to see if the "error" was just we’re run out of rows}
if FetchStat = 100 then
begin

{No more to fetch, so issue a call to close the statement
cursor without freeing up the handle so we can use it again.
The last parameter would be DSQL_drop to free up the handle
totally}
if isc_dsql_free_statement(@Status_Vector, @stmt_handle,
DSQL_close) <> 0 then
isc_print_status(@Status_Vector);

end

else

begin

{Handle any other sort of errors}
SQLCODE := isc_sqlcode(@Status_Vector);
isc_print_sqlerror(SQLCODE, @Status_Vector);

end;

end

else

{No errors}
begin

{If there’s no blank line at the end of the StringGrid, add one}
if StringGrid1.Cells[0, StringGrid1.Rowcount - 1] <> '' then
StringGrid1.Rowcount := StringGrid1.Rowcount + 1;

{Add the values to the last row of the grid from the receiving
variables}
for LoopCount := 0 to POut_DA.sqld - 1 do
StringGrid1.Cells[LoopCount, StringGrid1.Rowcount - 1] :=
StrPas(POut_DA.sqlvar[LoopCount].sqldata)

{end for}
end;

end;

Tidying Up Afterwards

The variable nature of dynamic SQL means that there are numerous cases of allocating memory dynamically. These must all be tidied up when a statement has completed and is no longer needed. This includes:

• the statement handle itself
• memory allocated for input and output XSQLDA structures
• memory allocated for receiving variables

To ensure the best programming practice, dynamic memory allocation is better done in try..except..finally blocks. These have not been used for the sake of simplicity of code.

Handling Dates

InterBase Date Storage

InterBase stores dates internally in a "quad byte" structure defined in the header file as:

Type

ISC_QUAD = record
isc_quad_high: ISC_LONG;
isc_quad_low: Cardinal;

end;

Translating Dates And Times

Date and time information are converted to and from this storage format by using two API calls:

• isc_encode_date
• isc_decode_date

The format used for the encoding and decoding is a ‘C’ style time structure defined in the header file as:

Type

TM = record

sec : Integer; { seconds (0-59) }
min : Integer; { minutes (0-59) }
hour : Integer; { hour (0-23) }
mday : Integer; { day of month (1-31) }
mon : Integer; { month of year (0-11) }
year : Integer; { year (year - 1900 ) }
wday : Integer; { day of week (0-6 Sunday=0)}
yday : Integer; { day of year (0-364) }
isdst : Integer; { daylight savings in effect (1 = True) }

end;

The method to decode a data returned by a record fetched from InterBase would therefore be as follows:

procedure ShowIBTime;

Var

ExpandedTime: TM;
TimeRecvVar: ISC_QUAD;
LoopCount: Integer;
DateString: String;

begin

{Code to retrieve the return column into TimeRecvVar, assuming
we’re looping through the XSQLVAR records and the date field
doesn’t allow NULLs}
for LoopCount := 0 to POut_DA.sqld - 1 do
if POut_DA.sqlvar[LoopCount].sqltype = ISC_DATE then
begin

{Get the returned value into local storage}
TimeRecvVar := POut_DA.sqlvar[index];
break; {for the sake of this example}

end;

{end for}
{Decode the date}
isc_decode_date(@TimeRecvVar, @ExpandedTime);

{Show a message with the date, UK-style}
DateString := ‘Date returned: ‘ +
IntToStr(ExpandedTime.mday) + ‘/’ +
IntToStr(ExpandedTime.mon + 1) + ‘/’ +
IntToStr(ExpandedTime.year + 1900);
ShowMessage(DateString);

end;

Summary

In this paper I have outlined the basic steps for utilising the InterBase API. The vast majority of high-performance, multi-database applications can be constructed simply by using the techniques outlined herein.

This paper forms a foundation from which to expand into blob and array manipulation and InterBase event handling. These topics, while a little more demanding than the material covered here should be straightforward enough to master once the reader is familiar with these practices and armed with the InterBase API Guide. Good luck.