Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 4 of 4 total
Thread Side effects of altering the default primary index (RecordID)
Wed, Sep 14 2016 2:47 AMPermanent Link

Steve Williamson

Foresiight Software

During some testing of DBISAM under FireDAC (via the ODBC driver), we have found that the FDTable component does not work with any DBISAM table that contains the default (auto-created) primary index.

The reason is to do with the way the underlying query is built up by FireDAC, which automatically incorporates the "RecordID" field in the ORDER BY clause.  This causes an error such as:
[FireDAC][Phys][ODBC][Elevate Software][DBISAM] DBISAM Engine Error # 11949 SQL parsing error - Expected column name but instead found A.RECORDID in SELECT SQL statement at line 3, column 28

This issue can be overcome by altering the primary index on each table to point to our own primary key field (e.g. CustomerID).  However, this means that the RecordID field is now no longer included in any index.

My question is:
Are there any negative side effects of altering the default primary index and removing RecordID?  Should we expect a performance impact or any other limitations?



Attachments: FireDAC FDTable DBISAM error RecordID.txt
Wed, Sep 14 2016 4:28 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Steve


With the caveat that I haven't used DBISAM for yonks and my version is growing cobwebs

I don't see any problem at all, unless you want date of creation order at any point. RecordID was there to ensure a unique primary key. I sort of remember discussions about this a long time ago RecordID isn't removed its just not involved in your indices.

Roy Lambert
Wed, Sep 14 2016 12:11 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Steve,

<< Are there any negative side effects of altering the default primary index and removing RecordID?  Should we expect a performance impact or any other limitations? >>

No, it won't affect anything at all, especially performance. As Roy indicated, it's just there as a "placeholder" primary index so that the DBISAM primaryindex requirement is satisfied.  Other than that, it isn't particular useful, especially because it's a virtual field and isn't actually defined anywhere (no TFields, etc.).

Tim Young
Elevate Software
www.elevatesoft.com
Wed, Sep 14 2016 8:21 PMPermanent Link

Steve Williamson

Foresiight Software

Great, thanks for the responses Tim and Roy.
Image