A Unicorn InterGlobal White Paper
Greg McCreath
Printed March 2000
Using the undocumented API 2 for ultra fast access to DataFlex DataFiles
One of Unicorn’s keys strengths has always been our ability to integrate different systems from various origins. We are often called upon to make x talk to y via w, and quite often this involves DataFlex datafiles here or there. Whether that be talking to custom hardware, or communicating by RS-232 with remote devices we get asked to do some interesting and complex things. Every now and then, as experienced integrators, Unicorn gets called upon to perform a little bit of magic and do something a bit above and beyond the call of duty. So, imagine you are posed the following simple problem ….
“We need ultra fast access to DataFlex data files, we don’t use DataFlex, and an ODBC driver is not an option …”
The problem you are faced with is that generally, ODBC is the only option in the 32 bit development environment.
A History Lesson
Data Access Worldwide markets a 16 bit Windows DLL (Dynamic Link Library) API that you can use to access DataFlex data files for a 16 bit development environment. However, unless you are very clever and can ’thunk’ 32 bits into 16 bits (I can’t), you either need to use a 32 bit development platform that supports 16 bit DLLs (yeah right …) or you have to have a 32 bit DLL to use. The problem is … Data Access do not market, or support a 32 bit DLL for accessing DataFlex files.
However, having said that there is a 32 bit version of the DLL API in existence, and it quietly ships with Visual DataFlex, FlexODBC, and the Crystal Reports driver. The big problem is that is it undocumented, unsupported, unknown, changed from the 16 bit DLL, and generally kept quite about. The new DLL (APIDLL.DLL) is, in fact, the new 32 bit API 2, and that is just what we need to access DataFlex data files from 32 bit development languages.
In this article, we’ll be taking a look at performing native access to DataFlex data files from other 32 bit languages, and we’ll be using Borland’s Delphi as the example language, although the technology could equally apply to C or C++ or Visual Basic.
It has taken a long time to work through the changes in the new API 2 from the API 1 documentation. Most of it has been perspiration with a bit of inspiration here and there. Many of the function names have changed (and their operation has in some instances) from the published API 1.
The function names exported in a DLL are reasonable easy to find, they must be exported and recognised by the outside world in order to be a DLL under Windows. I use a free tool called ’pebrowse’ to look at DLLs in more detail. However, there are undoubtedly portions of the API2 that I do not know even exist, although I reckon I’ve managed to figure most of it .. kind of … 😉
Lets dive in …
Accessing any DLL from a development environment always requires defining the interface to the DLL using whatever facilities the language has. In DataFlex it is the External_Function and External_Procedure command that provide the mapping to the DLL. In Delphi it is the definition of function using the ’external’ directive. The Type definitions used in this article can be found as a separate section later on.
In order to use the API, we first need to initialise it and this is done with the dfInitialize function. Note that any constants used (like DFSUCCESS) are the same values as defined in the VDF packages. In API 1, dfInitialize only took a single parameter that was the configuration file name (a DFINI.CFG). In API two this would GPF unless a second parameter was added, and trial and error showed this to be a way of setting dfpath, or in API speak, initialising the DF_OPEN_PATH attribute. So essentially, we can now initialise the API and supply a dfpath at the same time. Like I said, a lot of trial and error and more than a few GPFs…Below is the Delphi definition for dfInitialise.
{****** Initialisation Function ******}Function dfInitialize(ConfigurationFile: DFLPSTR_T;
DfPath: DFLPSTR_T): DFERRVAL_T;External ’APIDLL.DLL’;
It would be used in a procedure as below
Var
ErrVal : SmallInt;
Begin
Errval :=dfinitialze(’’,’D:MYDATA’);
If ErrVal <> DFSUCCESS then DFShowError(’ Initialise Error: ’)
If ErrVal <> DFSUCCESS then Abort;
End;
After we have opened the data file using the dfFileOpen function we could test it to see it if was actually open using the dfAttributeGetNumeric. The function call used to be dfGetNumericAttribute in API 1 (!), but like I said, it’s quite easy to examine the names of exported functions in a DLL. Luckily, the parameters seem to be the same.
if dfAttributeGetNumeric(TestFile,0,0,DF_FILE_OPENED) = DFFALSE then
begin raise exception.Create(’File is not Open’);
abort;end;
Error Handling
The error handling functions in the new API 2 seem to have changed as well. The names have changed from dfGetError and dfSetError to dfErrorGet and dfErrorSet. I can see the name changes are meant to group together related functions, so all the error functions begin with dfError and so on. There is a dfErrorStackClear function that appears to do exactly what is says, and a few additional error functions I have not explored. Many of the functions that were documented in API 1 as having return codes (that indicated if an error occurred) now appear not to return anything meaningful at all. It look like the functions now push errors onto an error stack and it is your job to check for errors after each API function call. Popping errors of this stack is similar to API 1. The following routine would scan the error stack and pop each error of it.
procedure DFShowError(sVal: string) ;var
iErrDF: DFERRVAL_T ;
sErrMsg: DFLPSTR_T ;
iRet: DFSHORT_T; begin
repeat
iRet := dfErrorGet( @iErrDf, sErrMsg);
if iRet <> DFSUCCESS then
MessageDlg(Format(sVal + ’ %d %d %s’,[iRet, iErrDf,
sErrMsg]),mtInformation, [mbOk], 0);
until iRet = DFSUCCESS;end ;
Adding a record
To add a record, the process is quite similar to API 1 with the exception that it now seems that transaction processing is required in order to add/delete/modify record. Lets assume we have a DataFlex data file that we have opened as TestFile and it has the following fields
Unique_ID : Integer
Name : Ascii
JoinDate : Date
Note that error checking has been omitted in the following routine.
var
BCD: DFNUMBER_T;
TempStr : String;
sFieldVal: DFLPSTR_T ;
TempInt : Integer; begin
dFTransactionStart; {Start a DF Transaction}
dfFileClear(TestFile); {Clear the File}
{Write to Unique_ID field using a ’10’ value.}
dfLongtoBCD(10, @BCD1); {Convert a Long to BCD for DF }
dfFieldPutBCD(TestFile,1,@BCD1); {Field into Buffer}
{Write to Name field using pChar}
StrPCopy(sFieldVal, ’Unicorn Business Solutions’);
dfFieldPutString(TestFile,2,sFieldVal,0,0,0); {Field into Buffer}
{Write to JoinDate Field}
TempInt := Round(Date);
{Delphi Calculates dates from 30/12/1899, DataFlex (seems to?)
calculate them from 31/12/1899. So deduct one from the Delphi
date ..... I haven’t researched this fully as yet}
TempInt := TempInt - 1;
{Store DataFlex dates as Four Digits. Add the magic DF number}
if TempInt < 36525 then
TempInt := TempInt + 693975;
DfLongtoBCD(TempInt, @BCD1); {Convert a Long to BCD}
DfFieldPutBCD(TestFile,3,@BCD1); {Field Into Buffer}
DFRecordSave(TestFile); {Save it}
DFTransactionCommit; {Scrub it to disk}
dfErrorStackClear; end;
Find the record and modify it
Sometimes, of course, we will need to re-find the record and make some modifications to it. Surprisingly enough this is a reasonably straight forward process. The following code re-finds the added record and modifies the ’name’ field.
var
BCD1: DFNUMBER_T;
sFieldVal: DFLPSTR_T ;Begin
dfLongtoBCD(10, @BCD1) ; {Look for Unique_ID 10}
{Clear the buffer and seed it}
dfFileClear(TestFile);
dfFieldPutBCD(TestFile,1,@BCD1);
{Find the record}
dfRecordFind(TestFile,1,EQ);
{Do a reread, this implies a TransactionStart}
dfFileReread;
{Write to Name field using pChar}
StrCopy(sFieldVal, ’Get your Data’) ;
dfFieldPutString(TestFile,2,sFieldVal,0,0,0);
dfRecordSave(TestFile);
dfTransactionCommit;End;
Find the record and delete it
Now that we have added and changed a record, let’s re-find it again and delete it. Yes it’s pointless, but this is just to illustrate to process. Once again there is no error checking in this example.
var
BCD1: DFNUMBER_T;
begin
dfLongtoBCD(10, @BCD1) ;
{Clear the buffer and seed it}
dfFileClear(TestFile);
dfFieldPutBCD(TestFile,1,@BCD1) ;
dfRecordFind(TestFile,1,EQ); {Refind record 10}
dfRecordDelete(TestFile); {Delete the record}
end;
Direct API 2 access is FAST!
Conceptually, talking directly to the API 2 DLL is a little different to talking to the ODBC driver, or using DataFlex itself. This is as fast as is possible without actually linking the contents of the DLL into an executable. It has the least layers possible, either by a Windows abstraction (like ODBC) or by a product abstraction (like the Borland DataBase Engine – BDE), or by a class hierarchy like the Data Dictionaries in later versions of DataFlex. Essentially, using the API 2 is stuffing field contents directly into memory and passing them to the API 2 DLL to handle.
For a simple exercise of adding 10,000 records, modifying them, and deleting them (one by one), the following file definition was used.
| NUM | FIELD NAME | TYPE | SIZE | OFFST | IX | RELATES TO FILE.FIELD |
| ——– | —————————— | —— | —— | ——– | — | —————- |
| 1 | RECORD | NUM | 8.0 | 1 | 1 | |
| 2 | ASCII_1 | ASC | 30 | 5 | 2 | |
| 3 | ASCII_2 | ASC | 15 | 35 | 3 | |
| 4 | NUMBERIC_1 | NUM | 8.0 | 50 | 4 | |
| 5 | DATE_1 | DAT | 3 | 54 | 5 | |
| 6 | REAL_1 | NUM | 12.4 | 57 | ||
| 7 | MEMO_1 | TEX | 1008 | 65 |
| INDEX# | FIELDS | DES | U/C | LENGTH | LEVELS | SEGMENTS | MODE |
| ——— | ————– | —- | —- | ——— | ——— | ———— | ———– |
| 1 | RECORD | NO | NO | 4 | 3 | 1 | ON-LINE |
| 2 | ASCII_1 | NO | YES | 33 | 4 | 2 | ON-LINE |
| RECUM | NO | NO | |||||
| 3 | ASCII_2 | NO | YES | 18 | 3 | 2 | ON-LINE |
| RECNUM | NO | 18 | 3 | 2 | ON-LINE | ||
| 4 | NUMERIC_1 | NO | NO | 7 | 3 | 2 | ON-LINE |
| RECNUM | NO | NO | |||||
| 5 | DATE_1 | NO | NO | 6 | 3 | 2 | ON-LINE |
| RECNUM | NO | NO |
The results below clearly speak for themselves. ODBC is clearly the worst option here, being more than 12 times worse then direct API 2 access. Using VDF with file buffers (no data dictionaries) is more than a third slower than our direct API access. Starts to look like a good option doesn’t it!
| Task | ODBC (using Delphi) | VDF with file buffer access | API 2 (using Delphi) |
| Add records | 143 | 14 | 12 |
| Modify records | 172 | 29 | 13 |
| Delete Records | 115 | 12 | 10 |
| Total | 430 | 55 | 35 |
I would assume that using DataFlex Data Directories would be considerable slower than DataFlex file buffer access as it has more to do in the DataFlex language. I did not test it for this article.
Why would I use API 2?
This is simple. DataFlex is not meant to be an all purpose language. Its focus is quite clearly defined and that lies in the ’traditional IT’ area. There are times when other languages offer richer facilities for completing a job quicker, and sometimes those jobs need high-speed reliable access to DataFlex data files. Most often these sorts of programs use ASCII files as bridges between DataFlex and the other systems. Using the APUI means skipping the ASCII file stage. Gaining access to the DataFlex data files means a more stable, easily understood, and easily maintained system.
A few examples might be …
- Creating a NT service for listing to an IP port and disseminating DataFlex information across an network (or web).
- Using complex RS-232 communications for talking directly to remote devices/hardware and storing the result directly in DataFlex file.
- Communicating with specialist hardware and reading/writing to DataFlex files.
- Writing devices for other systems to speak to DataFlex data. An example here might be to develop a direct driver for the Borland database Engine (no small job!).
Type Definitions Used in Examples
Below are the Delphi pascal type definitions used throughout this article.
TCharArray = Array[0..200] of Char ;
DFSHORT_T = SmallInt;
DFERRVAL_T = DFSHORT_T; {For Error Return Values}
DFLPSTR_T = TCharArray; {Pointer to Char Array}
DFVOID_T = DFSHORT_T; {For ignored return values}
DFNUMBER_T = record bValue: array[0..11] of Char; end;
Finally …
Let me finish by saying that unravelling access the API 2 has most certainly been a lot of hard work, and a lot of trial and error … and a lot (and I mean a LOT) of Windows General Protection Faults. It is possible that some of the interface definitions shown in this article may not be 100% correct (but they seem to work …)