Pervasive.SQL Recnum Support
Recnum Support means that when a table is converted from DataFlex to Pervasive.SQL, the driver will preserve the original recnum values of the DataFlex table.
You will only need to convert with recnum support if the recnum values have some meaningful value. For example, when the recnum value is used as the customer ID number in a customer file. Additionally, if you have defined relations from or to the recnum field, you must use recnum support. In general, recnum support will only be necessary with applications that are derived from older DataFlex character mode applications.
Tables without recnum support will still have a recnum field. Recnum values are calculated from the position of the record in the table. These recnum values cannot be used in indexes and are not numbered starting with 1 and incremented by 1.
Converting Tables
When a table is being converted with recnum support, an extra field will be inserted at the beginning of the record. This field is called RECID by default and is a 4-byte integer. The field will be the only segment of Index 0 (Pervasive.SQL) and is defined as an AUTOINCREMENT field, which means that Pervasive.SQL will assign a number to it whenever a new record is inserted that has no value (0) for the RECID field.
If you want to choose another name for this substitute for the DataFlex recnum fields, you can do so by adding a line into your DFBTRDRV.INT file like this:
RECID_NAME "MYRECID"
Note that this setting will only be used when converting tables. After tables have been converted, you can only change the name of the field by using a special tool for maintaining DDF files or by temporarily commenting out the RECNUM_SUPPORT keyword in the generated .INT file.
Index Numbers
Normally, the indexes of Pervasive.SQL tables are presented to the API with a number that is higher by one than Pervasive.SQL’s actual index number. This is needed because Pervasive.SQL indexes start numbering at 0. When a table has been converted with recnum support, the first Pervasive.SQL index (0) is the one that we use for the recnum field index. In that case, all other Pervasive.SQL index numbers are passed without modification, because Index Number 1 in Pervasive.SQL is Index Number 1 to DataFlex.
Opening Tables
When the driver opens a Pervasive.SQL table, it needs to know if this table has been converted with or without recnum support. This information has been written to the .INT file generated for the table when it was converted. The keyword RECNUM_SUPPORT should have a numeric value other than 0 in this case. If your .INT file is missing this line, the field that represents RECNUM will be the first field of the record according to DataFlex, and all other field numbers will be shifted up by 1. Numbering of the indexes will also be shifted by 1. Refer to Opening Tables for more information.
Opening Tables Directly
The driver is also capable of opening Pervasive.SQL tables directly, bypassing the .INT file created for the table. To do this, the root name of the table needs to be set to MYFILE.BTR or DFBTRDRV:MYFILE. Note that the extension used in the first example has nothing to do with the extension of the physical filename. It is just a signal for the API that the DataFlex Pervasive.SQL Driver should handle this file. When this method is used for opening tables, the driver will open the .INT file itself.
If you have no .INT file but converted the table with recnum support, you need to make this clear to the driver. The way to do this is to add a + sign to the end of the base name of the table. Examples of root names that will open tables directly with recnum support are:
MyFile+.BTR
DFBTRDRV:MyFile+
No Recnum Support
When you elect not to use recnum support, there are some things you will have to be aware of.
Recnum Value
The value that recnum returns will not start with 1 for the first record. It will also probably not be incremented by one for each subsequent record. The ‘gap’ between the recnums of two records following each other may be larger than 1.
Finding by Recnum
When no recnum support has been selected, the table can still be accessed on the recnum index. This will also be the fastest way to scan through a table.
One thing that works differently is that you can’t expect Pervasive.SQL to find a record by clearing a table and just moving a random value into recnum, even if this value falls within the maximum number of records. This is because the maximum number of records just gives you the number of records in the Pervasive.SQL database and tells you nothing about their recnum values!
The next sample shows a piece of code that will not work. Here, the program expects that moving a high value into the recnum field followed by a FIND LT will find the last record in the database. To find the last record, just clear the record buffer and do a FIND LT.
// Sample of things which might not work when
// recnum support has been switched off.
// Wrong code to find last record!
Open Customer
Move 999999 to Customer.Recnum
Find LT Customer by RECNUM
// Right code to find last record!
Open Customer
Find LT Customer by RECNUM