Blog › ICP guides

Advantage Database developer on retainer: AdsTable.Edit lock hold, Sybase Advantage, SAP Advantage Database Server on monthly retainer

October 8, 2026 · ~15 min read

An Advantage Database Server developer was maintaining an inventory management application built in Delphi with the Advantage Database Server (ADS) 11 client libraries for a regional building-materials distributor. The application tracked SKU-level inventory quantities and bin locations across three warehouse locations. A “Receive Stock” form allowed warehouse supervisors to update the on-hand quantity for a SKU when new inventory arrived: the supervisor selected the SKU from a grid, clicked “Edit”, entered the received quantity, and clicked “Save”. The save button’s OnClick handler in Delphi called AdsInventory.Edit (which acquired an exclusive record lock on the current inventory row via the ADS server), read the existing quantity field, added the received quantity from the form input, applied a business-rule validation (the resulting on-hand quantity must not exceed the SKU’s maximum bin capacity), and on the passing branch called AdsInventory.Post (which wrote the updated quantity field and released the record lock). The developer tested the save handler against test inventory items with on-hand quantities well within bin capacity. In production, three inventory items were near or at their bin capacity when the supervisors attempted to receive additional stock; the business-rule check found that adding the received quantity would exceed the maximum bin capacity, raised a ShowMessage alert telling the supervisor to split the receipt across bins, and called Exit to return to the form. The AdsInventory.Edit lock on those three records remained held for the duration of the warehouse-management session: warehouse floor operators trying to perform count adjustments on those three SKUs received lock-timeout errors from the ADS server and could not open those inventory records. Concurrent lock timeouts: 3 → 0 after adding AdsInventory.Cancel in the validation-failure branch before the Exit call.

The root cause was a missing AdsTable.Cancel call on the failure branch of the OnClick handler. In the Advantage Database Server Delphi VCL component model, TAdsTable.Edit (and the equivalent TAdsQuery in query-based scenarios) transitions the dataset component into the dsEdit state. Entering dsEdit state acquires an exclusive write lock on the current record on the ADS server. The lock is held until one of three things happens: AdsTable.Post writes the buffered field changes to the ADS server and releases the lock; AdsTable.Cancel discards all buffered changes and releases the lock; or the ADS client connection is closed (which releases all locks held by that connection, either by explicitly calling AdsTable.Close / AdsConnection.Close or by the Delphi application process exiting). Calling Exit from a Delphi method, raising an exception, or simply returning from the OnClick procedure without calling Post or Cancel does not transition the component out of dsEdit state — the component remains in dsEdit, the ADS server retains the exclusive record lock, and any other application client that calls AdsTable.Edit on the same record will receive a lock-timeout error from the ADS server. In the building-materials distributor scenario, the three locked SKUs were available to the warehouse supervisor’s session (their AdsInventory component held the lock), but all other warehouse-management workstations could not access those records for quantity adjustments, count corrections, or bin reassignments. The fix was to add AdsInventory.Cancel in the validation-failure branch, explicitly transitioning the component back out of dsEdit state and releasing the ADS server lock before the error message was shown and the procedure exited.

The Advantage Database Server locking model has two distinct modes depending on the deployment configuration. In ADS local engine mode (used for single-user applications or development environments without a running ADS server service), ADS manages locks internally within the application process using file-system level locks on the table files themselves; the ADS client DLL maintains the lock table in the local process. In ADS server mode (the production multi-user configuration, where the ADS server service runs on a central server and multiple client workstations connect to it via TCP/IP), the ADS server service maintains the lock table centrally; AdsTable.Edit sends a lock-request message to the ADS server, which grants or denies it. The developer must test the application in ADS server mode against a running ADS server to observe multi-user locking behavior; testing only in local engine mode will not expose lock-hold bugs because local engine mode has no competing clients. In both modes, the semantic is the same: AdsTable.Edit acquires an exclusive record lock, and Cancel or Post releases it. A Delphi exception handler that catches exceptions from inside an edit sequence (between Edit and Post) must call Cancel before propagating or swallowing the exception; otherwise the component remains in dsEdit state after the exception handler exits, and the lock remains held on the ADS server for the rest of the connection lifetime.

Advantage Database Server supports two table formats: ADT (Advantage Database Table — ADS’s native proprietary table format, stored as .ADT data files with .ADI index files) and DBF (dBASE-compatible format, stored as .DBF data files with .CDX compound index files or individual .IDX index files). ADT tables offer ADS-specific features including the ADS data dictionary (a centralized schema catalog stored in a .ADD file that defines tables, columns, indexes, stored procedures, triggers, views, and referential integrity constraints), server-side triggers (procedural logic defined in the ADS data dictionary that fires on INSERT, UPDATE, or DELETE), and ADS full-text search (a full-text index engine built into ADS for searching memo and character fields). DBF table support allows ADS to serve as a server for existing xBase-compatible applications (dBASE, FoxPro, Clipper, Visual FoxPro) by replacing the file-server model with a true client-server lock-management model while keeping the same DBF file format on disk. A developer maintaining a mixed application — some ADT tables in the ADS data dictionary and some legacy DBF tables — must be aware that ADT and DBF tables have different index file types (.ADI vs .CDX/.IDX), and that ADS replication and ADS triggers are available only for ADT tables, not DBF tables.

Advantage Database Server, the ADS VCL model, and the Delphi edit state machine

Extended Systems (later acquired by Sybase, then by SAP) developed Advantage Database Server in the early 1990s as a true client-server replacement for the file-server model used by DOS-era xBase applications (dBASE, FoxPro, Clipper). In the file-server model, the database files are stored on a network file server and each workstation accesses them directly via network file I/O, using application-level record locking via RLOCK() / FLOCK() functions. The file-server model has well-known weaknesses: lock contention is resolved entirely by the client applications (there is no central arbiter), network file I/O is slow for large result sets, and a crashed workstation can leave stale locks in the file-level lock tables. ADS replaced this model: the ADS server service runs on the server, owns the data files exclusively, and manages all record-level locks centrally. Client workstations connect to the ADS server via TCP/IP and send data access requests; the server performs all file I/O and returns result sets and lock confirmations over the network. This gives ADS the lock-management reliability of a true client-server database with the file-format compatibility of the xBase world (supporting DBF tables for existing xBase applications and ADT tables for new development).

The ADS Delphi VCL component library provides TAdsTable, TAdsQuery, TAdsConnection, TAdsDatabase, and supporting components that integrate with Delphi’s standard TDataSet / TDataSource / TDBGrid / TDBEdit data-aware control architecture. TAdsTable is derived from TDataSet and implements the standard Delphi edit state machine: Browse (record displayed, not in edit mode), Edit (record open for editing, lock held on ADS server), Insert (new record buffer, not yet posted), dsSetKey (navigating by key value). The standard Delphi data-aware components (TDBGrid, TDBEdit, TDBMemo) interact with TAdsTable through the TDataSource mediator: when the user types in a TDBEdit, the TDataSource automatically calls AdsTable.Edit to transition the dataset into edit mode before the first keystroke is applied to the buffer. This means that in data-aware UI forms, AdsTable.Edit may be called implicitly by the user’s keyboard interaction with a TDBEdit control — the developer may not have written an explicit AdsTable.Edit call anywhere in their code, but the ADS record lock is still acquired the moment the user starts typing in an editable data-bound field. In programmatic scenarios (batch updates, button-triggered save operations like the building-materials inventory form), AdsTable.Edit is called explicitly by the developer. In both cases, the requirement is the same: every code path that does not call AdsTable.Post must call AdsTable.Cancel.

The ADS data dictionary (for ADT tables) enables several features not available with raw DBF files. ADS stored procedures are Pascal-compatible procedural scripts stored in the .ADD data dictionary file and executed on the ADS server; they can be called via TAdsQuery or via the ADS SQL EXECUTE PROCEDURE statement. ADS triggers fire on the server when an INSERT, UPDATE, or DELETE operation completes on a trigger-configured ADT table; triggers can enforce referential integrity, log audit records, or cascade changes to related tables. ADS full-text search indexes memo and large character fields for keyword search without scanning every row. ADS views define pre-built query results that can be accessed via TAdsQuery as if they were tables. ADS replication (available in Enterprise editions) allows ADS servers to synchronize ADT table data across multiple sites. These enterprise features are managed through the ADS Management Utility or the ADS Data Architect IDE. A retainer developer typically covers all of these: stored procedure correctness, trigger side-effects that produce unexpected update cascades, full-text search index rebuild after bulk imports, and replication conflict resolution.

ADS transaction support uses an explicit AdsConnection.BeginTransaction / AdsConnection.CommitTransaction / AdsConnection.RollbackTransaction model (or the equivalent Delphi TAdsDatabase.StartTransaction / Commit / Rollback calls). Within an ADS transaction, all AdsTable.Post calls are buffered and not committed to the ADS server until CommitTransaction is called; a RollbackTransaction discards all buffered changes. Record locks acquired by AdsTable.Edit within a transaction are held for the duration of the transaction — they are not released until CommitTransaction or RollbackTransaction completes. In a long-running transaction that edits many records sequentially, many record locks accumulate and are held simultaneously. If the transaction is never committed (for example, because the Delphi procedure that called BeginTransaction exits via an unhandled exception before reaching CommitTransaction), all locks are held until the ADS client connection is closed. A Delphi developer using ADS transactions must wrap every transaction block in a try-except-finally to guarantee that CommitTransaction or RollbackTransaction is always called. The AdsTable.Edit-without-Cancel bug is the single-record version of the same pattern; the transaction-without-rollback bug is the multi-record version of the same pattern.

Typical Advantage Database retainer work and what it looks like in a work log

AdsTable.Edit without AdsTable.Cancel on business-rule failure branches is the canonical Advantage Database invisible production lock. The pattern is consistent: a Delphi save-button handler calls AdsTable.Edit to acquire an exclusive record lock before reading current field values and applying a business-rule validation. The developer tests with records that always pass the validation; on the passing branch, AdsTable.Post is called, which writes the changes and releases the lock. In production, a subset of records hits the failure branch (the quantity exceeds bin capacity, the new status is invalid, the referenced foreign key does not exist). The handler raises a ShowMessage or shows a validation dialog and calls Exit or raise EValidation — without calling AdsTable.Cancel. The TAdsTable component remains in dsEdit state; the ADS server record lock remains held. Three inventory items locked for session lifetime; warehouse operators could not open those SKUs for adjustment. Fix: added AdsInventory.Cancel in the validation-failure branch before every Exit and before every raise. Concurrent lock timeouts: 3 → 0. Work log: “TInventoryForm.SaveButton_Click: AdsInventory.Edit acquired lock; quantity > bin capacity validation failed; ShowMessage + Exit without AdsInventory.Cancel; 3 SKUs locked for session; warehouse operators: 3 lock timeouts → 0; fix: AdsInventory.Cancel added before Exit on failure branch; 1.5h.”

ADS ADI index file corruption after abnormal ADS server shutdown is the second most common Advantage Database retainer pattern. When the ADS server service is stopped abruptly (service kill, server power failure, OS crash) while a client connection has a AdsTable.Post in progress — the data record written to the .ADT data file but the corresponding .ADI index tree update not yet complete — the .ADI index file may contain a partially-updated B-tree: some leaf pages have been updated with the new record’s key values, but the intermediate or root pages still reference the pre-update structure. A TAdsTable.FindKey([key_value]) call traversing the corrupted B-tree returns the wrong record offset or raises an ADS error code. The symptom is a find or locate operation returning a wrong record for a known-existing key, or failing with an ADS engine error where the previously-working key-based navigation breaks. The fix is to rebuild the .ADI index file using the ADS server’s REINDEX command (called via the ADS Management Utility or via an ADS SQL EXECUTE PROCEDURE sp_ReindexTable('tablename') call). The REINDEX operation scans the .ADT data file row by row and rebuilds all index entries in the .ADI file from scratch, without requiring any data file changes. The ADS server must be running for REINDEX; the table being reindexed must not have any active connections. Work log: “INVENTORY.ADT / INVENTORY.ADI: B-tree inconsistency after ADS server power failure; AdsInventory.FindKey([‘SKU-4471’]) returned wrong record; ADS Management Utility REINDEX INVENTORY: rebuilt .ADI from .ADT; FindKey now returns correct record; record count verified: 18,332 → 18,332 (no data loss); 1.5h.”

ADS client driver version mismatch and connection-level result inconsistencies is the third common Advantage Database retainer pattern. The ADS client libraries (primarily AdsAccess.dll and AdsClient.dll on Windows, or the ADS client shared library on Linux) must match the ADS server version. When a Delphi application compiled against ADS 11 client DLLs connects to an ADS 13 server (which may have been upgraded while the Delphi application’s deployment was not), the client DLL’s wire protocol and internal data structures may be incompatible with the server’s responses for certain operations. Common symptoms of version mismatch: TAdsQuery result sets that return fewer rows than expected for a given SQL query (the ADS client DLL misparses a record batch response format that changed between versions); AdsTable.FindKey returning wrong records in cases where the ADS server’s key comparison algorithm changed between versions; or ADS transactions that fail to commit with an undocumented error code. The fix is to update the ADS client DLLs on the affected workstations to the version that matches the running ADS server. Work log: “WS-WAREHOUSE-07: ADS client DLL version 11.10.0.22; ADS server version 13.20.0.35; TAdsQuery FIND_BY_SKU returned 0 rows for known-existing SKU ‘SKU-4471’; updated AdsAccess.dll and AdsClient.dll to 13.20.0.35 matching server; query now returns correct 1 row; affected workstation: 1; 1h.”

How HourTab tracks Advantage Database retainer hours

Advantage Database Server AdsTable.Edit-without-Cancel bugs are invisible by the same mechanism that makes them hard to diagnose: AdsTable.Edit returns successfully with no error or warning when it acquires the lock; the validation check that follows it also executes without error (the validation logic itself works correctly — it correctly identifies the bin-capacity violation); the ShowMessage alert displays correctly to the supervisor; the Exit returns the OnClick handler normally with no unhandled exception. The Delphi application shows no error. The ADS server log shows no error (the lock was legitimately granted). The warehouse-management application continues operating normally for the supervisor’s session. The only evidence of the problem is on a different workstation: a warehouse operator tries to open one of the three locked SKUs for a count adjustment and receives an ADS lock-timeout error. The supervisor’s workstation is probably idle by then; the supervisor has moved on to other tasks. Connecting the lock-timeout error on workstation B to the validation-failure exit on workstation A requires either a structured work log that captures every Edit-to-Post-or-Cancel sequence, or reading through the relevant Delphi source code to find every code path after AdsTable.Edit that does not call Cancel.

The work log must name the mechanism to be auditable: which Delphi form and handler (TInventoryForm.SaveButton_Click), the AdsTable.Edit call that acquired the lock, the business-rule validation that triggered the failure branch (quantity + received > max bin capacity), the exit path that bypassed Cancel (ShowMessage followed by Exit), the number of records locked (3 SKUs), the concurrent lock timeouts before and after (3 → 0), the fix (AdsInventory.Cancel added in the failure branch before Exit, and also in the exception handler that wraps the entire method body), and the verification (tested bin-capacity rejection in a multi-connection test environment; operator workstation no longer receives lock timeout). A log entry that says “fixed locking issue in inventory form, 1.5h” is not auditable. A log entry that names TInventoryForm.SaveButton_Click, the AdsInventory.Edit lock, the bin-capacity failure branch, the 3 locked SKUs, and the AdsInventory.Cancel-before-Exit fix is auditable and defensible to the building-materials distributor client. HourTab gives Advantage Database developers a public retainer-hours URL they send to clients — building-materials distributors, automotive parts dealers, food-service companies, and logistics firms that built Delphi applications with ADS 10/11/12/13 in the 2000s and 2010s for inventory management, order processing, and customer account tracking, maintained today by the original Delphi developer or a successor retainer consultant.

Comparative context: Advantage Database Server AdsTable.Edit-without-Cancel bugs are structurally identical to lock-hold patterns in other Delphi-native database components. Paradox retainers cover the ObjectPAL LOCKRECORD-without-UNLOCKRECORD pattern where Paradox record locks acquired before a business-rule check are not released on failure branches. dBASE retainers cover the RLOCK()-without-UNLOCK() pattern in dBASE IV and dBASE Plus where file and record locks in batch update loops are not released on early-exit branches. Visual FoxPro retainers cover the FLOCK() / RLOCK() / UNLOCK() pattern where similar file-server-level locks can be held across a workday if application code does not release them explicitly on every exit path — the same class of “exclusive lock acquired before a business-logic check, not released when the check fails” that characterizes the ADS AdsTable.Edit-without-Cancel bug pattern.

FAQ: Advantage Database developer retainers