Blog › ICP guides

Gupta SQLWindows developer on retainer: SqlPrepare FOR UPDATE cursor lock hold, Centura SQLWindows, OpenText Gupta developer on monthly retainer

October 8, 2026 · ~15 min read

A Gupta SQLWindows developer was maintaining a customer billing application built in Centura SQLWindows 5 for a regional commercial electrical contractor. The application managed customer invoices and included a “Validate and Post Invoice” workflow that allowed billing administrators to review open invoices, apply a credit-limit validation check, and post approved invoices to the accounts receivable ledger. The ValidateAndPostInvoice SAL message handler in the TBillingForm window class used SqlPrepare with a FOR UPDATE clause to open an update-intent cursor on the invoices table (targeting the SQL Server backend), then iterated over open invoices using SqlFetchRow, applied a business-rule validation (the invoice amount must not exceed the customer’s remaining credit limit), and on the passing branch executed an UPDATE invoices SET status = 'POSTED' for the current row. The developer tested the validation and posting logic against a set of test invoices where all invoice amounts were well within their respective customers’ credit limits. In production, three customers had open invoices that exceeded their remaining credit limits; the credit-limit check found the violation, called SqlFetchNext to skip to the next invoice row and then returned from the handler to let the billing administrator decide what to do. The SqlPrepare FOR UPDATE cursor remained open, and SQL Server continued holding update-intent (U) and exclusive (X) row locks on all three fetched invoice rows for the duration of the billing session. Other billing administrators attempting to display or modify those three invoices received SQL Server lock-wait timeout errors. Concurrent blocked billing users: 3 → 0 after calling SqlEndFetch on the failure branch before returning from the handler.

The root cause was a missing SqlEndFetch call on the failure branch of the ValidateAndPostInvoice handler. In the Gupta SQLWindows / Centura SQLWindows cursor model, SqlPrepare prepares a SQL statement and binds it to a SqlHandle (a Gupta-defined handle type that encapsulates the database cursor state). When the prepared statement includes a FOR UPDATE clause (or equivalent update-intent syntax for the target database), the cursor requests update-intent locks from the database engine as it fetches rows. For SQL Server, this means SQL Server acquires update (U) locks on rows fetched by the cursor, which upgrade to exclusive (X) locks when an UPDATE statement is executed on the current row within the same transaction scope. For Oracle, the equivalent is a SELECT ... FOR UPDATE cursor that acquires row-level exclusive locks at fetch time. In the Gupta SQLWindows architecture, a SqlPrepare FOR UPDATE cursor that has been opened with SqlExecute and has had rows fetched via SqlFetchRow holds those row locks on the database server until SqlEndFetch is called (which sends a cursor-close message to the database, releasing the associated cursor resources and, if outside an explicit SqlBeginTransaction / SqlCommitTransaction transaction, releasing the row locks). Calling SqlFetchNext to advance the cursor to the next row, or simply returning from the SAL message handler, does not close the cursor or release the row locks. The cursor remains open in the SqlHandle’s state, and all database-level row locks it holds remain active on the SQL Server until either SqlEndFetch is called or the SqlDatabase connection is closed (which releases all cursors and locks).

The interaction between the Gupta SQLWindows SAL message model and cursor lifetime is what makes this pattern particularly subtle. In Gupta SQLWindows, application logic is written in SAL (Table Attribute Language — later renamed Centura Application Language in Centura SQLWindows, and known as OpenText Application Language in OpenText Gupta 7+). SAL is a message-driven, event-based language: SAL code in a window class is organized into message handlers that respond to system messages (wm_create, wm_destroy, wm_startUp, wm_close) and user-defined messages. A SqlHandle variable declared at the class level (in the object’s variable declarations block) persists for the lifetime of the window object — it is not automatically closed when a message handler returns. A SqlPrepare FOR UPDATE cursor that is opened in one message handler (say, a wm_startUp handler or a button OnClick handler) and is not explicitly closed with SqlEndFetch remains open across all subsequent message handler invocations for that window instance. If the billing administrator opens the TBillingForm window, the ValidateAndPostInvoice handler opens the cursor and fetches the first three invoice rows without calling SqlEndFetch, and then the administrator performs other actions (viewing customer details, running a different query, minimizing the form), the cursor remains open and the SQL Server row locks remain held throughout all of those subsequent actions.

Gupta SQLWindows’ SqlDatabase transaction model interacts with cursor locking in a way that amplifies the lock-hold duration. A SqlDatabase object in a SQLWindows application represents the connection to the backend database. When SqlBeginTransaction is called on a SqlDatabase object, all subsequent SQL operations on that connection (including cursor fetches and updates) occur within an explicit transaction that is not committed or rolled back until SqlCommitTransaction or SqlRollbackTransaction is called. Within an explicit SqlBeginTransaction / SqlCommitTransaction block, all FOR UPDATE cursor row locks acquired by any SqlPrepare cursor on the same SqlDatabase connection are held for the duration of the transaction — even if SqlEndFetch is called for an individual cursor, the locks it acquired may be retained by SQL Server as transaction-level locks until the transaction is committed or rolled back. In the ValidateAndPostInvoice handler without an explicit transaction, the cursor locks are held until SqlEndFetch or connection close; within an explicit SqlBeginTransaction, they are held until SqlCommitTransaction or SqlRollbackTransaction, regardless of when SqlEndFetch is called. A retainer developer auditing SQLWindows applications for lock-hold bugs must examine both the cursor lifetime (when is SqlEndFetch called) and the transaction scope (whether a SqlBeginTransaction is open, and whether SqlCommitTransaction or SqlRollbackTransaction is called on every exit path).

Gupta SQLWindows, the SAL message model, and the cursor lifecycle

Gupta Technologies (later renamed Centura Software, then acquired by OpenText) developed SQLWindows in the early 1990s as a rapid application development environment for building Windows client/server database applications. SQLWindows applications were written in SAL (Table Attribute Language), a proprietary object-oriented, message-driven language that compiled to a proprietary bytecode format stored in .app source files. The Gupta compiler produced .sqw compiled application files that ran on the SQLWindows runtime. SQLWindows was one of the first Windows RAD tools to provide tight SQL database integration, with the SqlDatabase, SqlHandle, and SqlTable object types designed specifically for building data-entry forms, query result grids, and batch update processes against SQL Server, Oracle, Sybase ASE, and DB2 backends.

The SQLWindows cursor model is based on five primary functions: SqlPrepare(hSql, sSql) prepares a SQL statement and binds it to a SqlHandle variable; SqlExecute(hSql) executes the prepared statement on the database (opening the cursor for SELECT statements); SqlFetchRow(hSql) fetches the next row from the result set into bound output variables; SqlEndFetch(hSql) closes the cursor and releases associated database resources; and SqlRetrieve(hSql, hTable) is a higher-level function that populates a SqlTable object from a query result (used for display-only data binding without a manual fetch loop). For update-intent operations, SqlPrepare is called with a SQL string that includes FOR UPDATE (for SQL Server: SELECT ... WITH (UPDLOCK) or equivalent; for Oracle: SELECT ... FOR UPDATE); after fetching a row with SqlFetchRow, an UPDATE ... WHERE CURRENT OF cursor or a positional update is executed on the same SqlHandle to modify the current row. The cursor lock on the current row is held from the SqlFetchRow call until either the update is committed (via SqlCommitTransaction) or the cursor is closed (via SqlEndFetch).

The SAL message model organizes all window behavior into message handlers within window class definitions. A TBillingForm window class has variable declarations (including SqlHandle hInvoiceCursor, SqlDatabase hDb) and message handlers for each event the form responds to. The wm_startUp or OnLoad handler typically initializes the SqlDatabase connection and may prepare default cursors. An OnClick or custom message handler implements specific user actions (like ValidateAndPostInvoice). The wm_close or OnDestroy handler closes the SqlDatabase connection and any open cursors. A cursor declared as a class-level SqlHandle variable persists across all message handler invocations for the window’s lifetime; a cursor declared as a local variable inside a specific message handler exists only for the duration of that handler’s execution. A FOR UPDATE cursor opened on a class-level SqlHandle in a button’s OnClick handler — without calling SqlEndFetch before the handler returns — remains open and holds its row locks for all subsequent message handler invocations until either SqlEndFetch is explicitly called or the wm_close handler closes the SqlDatabase connection.

OpenText (which acquired Centura Software and the SQLWindows product line) has continued to release OpenText Gupta SQLWindows versions (OpenText Gupta 7, 8, and beyond) that maintain backward compatibility with Centura SQLWindows 5/6 SAL source code while adding support for modern .NET runtime integration, web service calls from SAL code, and updated SQL Server and Oracle client library versions. The core cursor model — SqlPrepare / SqlExecute / SqlFetchRow / SqlEndFetch — is unchanged from Gupta 5 through OpenText Gupta 7+. Applications originally developed in Centura SQLWindows 5 or 6 and migrated to OpenText Gupta 7+ retain the same cursor locking behavior; FOR UPDATE cursor lock-hold bugs present in Centura 5 source code are present in the same form after migration to OpenText Gupta 7. Migration-related regressions tend to arise from renamed functions (Centura used SqlWindow_ prefix functions; OpenText Gupta 7 uses the OWL namespace), updated SQL client libraries that change lock escalation thresholds (a SQL Server 2019 client library may escalate row-level locks to table-level locks at a different row count threshold than a SQL Server 2000 client library), and new isolation level defaults in the SQL Server backend.

Typical Gupta SQLWindows retainer work and what it looks like in a work log

SqlPrepare FOR UPDATE without SqlEndFetch on business-rule failure branches is the canonical Gupta SQLWindows invisible production lock. The pattern is consistent: a SAL message handler uses SqlPrepare with a FOR UPDATE clause to open an update-intent cursor, iterates over result rows with SqlFetchRow, applies a per-row business-rule validation, and on the failure branch calls SqlFetchNext to skip the current row and continues — or returns from the handler entirely — without calling SqlEndFetch. The developer tests with data where all rows pass the validation; on the passing branch, the update is executed and, in some implementations, SqlEndFetch is called at the end of the loop after all rows are processed. In production, a subset of rows hits the failure branch; the handler returns without closing the cursor. The FOR UPDATE cursor on the class-level SqlHandle remains open; SQL Server holds update-intent or exclusive locks on all fetched rows. Three invoice rows locked for the billing session; other billing users blocked with SQL Server lock-wait timeouts. Fix: SqlEndFetch called on the failure branch before returning, and in the exception-handling path. Concurrent blocked users: 3 → 0. Work log: “TBillingForm.ValidateAndPostInvoice: SqlPrepare(hInvoiceCursor, ‘SELECT invoice_id, amount ... FOR UPDATE’); SqlFetchRow fetched 3 rows; credit-limit validation failed on row 2; SqlFetchNext called + Return without SqlEndFetch; 3 invoice rows locked via SQL Server U/X lock; concurrent billing users blocked: 3 → 0; fix: SqlEndFetch(hInvoiceCursor) added on failure branch before Return; 1.5h.”

SqlBeginTransaction without SqlRollbackTransaction on exception exit is the second most common Gupta SQLWindows retainer pattern. A ValidateAndPostInvoice handler that wraps the entire validation-and-posting sequence in an explicit SqlBeginTransaction / SqlCommitTransaction block must call SqlRollbackTransaction on every failure path that returns without committing. In SQLWindows, SAL error handling is implemented using On SqlError handlers in a window class body — a class-level error handler that receives all SQL errors for that class’s SqlDatabase connection. If the On SqlError handler logs the error and returns without calling SqlRollbackTransaction, the transaction remains open with all FOR UPDATE cursor locks held. Because the transaction is open on the class-level SqlDatabase object (not a local variable), it persists across subsequent message handler invocations: all subsequent SQL operations on the same SqlDatabase connection execute within the still-open transaction, accumulating additional locks. Work log: “TBillingForm On SqlError: SqlError -547 (constraint violation) during ValidateAndPostInvoice; handler logged SqlError code and returned without SqlRollbackTransaction; transaction remained open on hDb; 3 invoice rows locked with accumulated transaction locks; 2 subsequent billing operations also executed within the same open transaction; fix: SqlRollbackTransaction(hDb) added in On SqlError handler before return; SqlCommitTransaction confirmed present on normal-completion path; 2h.”

Lock escalation on SQL Server upgrade breaking previously-working batch operations is the third common Gupta SQLWindows retainer pattern. SQLWindows applications originally developed against SQL Server 2000 or SQL Server 2005 backends with row-level FOR UPDATE cursor locking may encounter changed locking behavior after the SQL Server backend is upgraded to SQL Server 2016 or later. SQL Server’s lock escalation threshold (the row count at which SQL Server automatically escalates row-level locks to a table-level lock) may be configured differently on the upgraded server. A SQLWindows application that previously held 3 row-level U locks during cursor iteration may now trigger a table-level lock escalation at 5,000 rows, blocking all concurrent operations on the entire invoices table rather than just the 3 fetched rows. This appears as a regression introduced by the SQL Server upgrade even though the SQLWindows application code is unchanged. Fix: review the SQL Server lock escalation settings (ALTER TABLE ... SET LOCK_ESCALATION = DISABLE for tables where row-level locking is required for correct multi-user behavior) and confirm that the FOR UPDATE cursor is closed promptly with SqlEndFetch after each operation to limit lock-hold duration. Work log: “SQL Server upgraded 2008 R2 → 2019; TBillingForm.ValidateAndPostInvoice FOR UPDATE cursor: previously held 3 row-level U locks; after upgrade, lock escalation threshold triggered at 5,000 row scan; invoices table: table-level IX lock acquired; all billing users blocked; fix: ALTER TABLE invoices SET LOCK_ESCALATION = DISABLE; reverted to row-level locks; SqlEndFetch confirmed present on all cursor exit paths; 2h.”

How HourTab tracks Gupta SQLWindows developer retainer hours

Gupta SQLWindows SqlPrepare FOR UPDATE cursor lock-hold bugs are invisible by the same mechanism that makes them hard to diagnose: SqlPrepare returns success with no error; SqlExecute opens the cursor with no error; SqlFetchRow fetches the first three rows with no error; the credit-limit validation correctly identifies the violation; SqlFetchNext advances the cursor position with no error; the ValidateAndPostInvoice handler returns normally. The Centura SQLWindows application shows no error dialog. The SQL Server error log shows no error (the locks were legitimately granted). The billing administrator’s session continues normally; they move on to other billing tasks. The only evidence of the problem is on a different billing workstation: a billing administrator attempts to open one of the three locked invoice rows for posting or display and their SQLWindows form receives a SQL Server lock-wait timeout after 30 seconds. Connecting the lock-wait timeout on workstation B to the open FOR UPDATE cursor on workstation A requires either querying SQL Server’s sys.dm_exec_requests or sys.dm_os_waiting_tasks DMVs to see which session holds the lock on the invoices table and which SqlHandle state corresponds to that session, or reading through the TBillingForm SAL source code to find every SqlPrepare FOR UPDATE cursor and every exit path that does not call SqlEndFetch.

The work log must name the mechanism to be auditable: which SQLWindows window class and message handler (TBillingForm.ValidateAndPostInvoice), the SqlPrepare call with the FOR UPDATE clause, the SqlFetchRow loop, the business-rule validation that triggered the failure branch (invoice amount exceeds customer credit limit), the exit path that omitted SqlEndFetch (SqlFetchNext called on current row + Return without SqlEndFetch), the number of rows locked (3 invoice rows), the concurrent blocked billing-user count before and after fix (3 → 0), the fix (SqlEndFetch(hInvoiceCursor) added on the failure branch before Return and in the On SqlError handler), and the verification (tested credit-limit violation scenario with two SQLWindows sessions simultaneously; second session no longer receives SQL Server lock-wait timeout after first session hits the credit-limit failure branch). A log entry that says “fixed cursor locking issue in billing form, 1.5h” is not auditable. A log entry that names TBillingForm.ValidateAndPostInvoice, the SqlPrepare FOR UPDATE cursor, the credit-limit failure branch, the 3 locked invoice rows, and the SqlEndFetch-before-Return fix is auditable and defensible to the electrical contractor client. HourTab gives Gupta SQLWindows developers a public retainer-hours URL they send to clients — electrical contractors, construction firms, manufacturing companies, healthcare organizations, and utilities that built Centura SQLWindows 5/6 or OpenText Gupta 7+ applications in the 1990s and 2000s for billing, service dispatch, work order management, and inventory tracking, maintained today by the original SAL developer or a successor retainer consultant.

Comparative context: Gupta SQLWindows SqlPrepare FOR UPDATE without SqlEndFetch lock-hold bugs are structurally identical to cursor lock-hold patterns in other client/server 4GL environments. Uniface retainers cover the RETRIEVE-without-DISCARD pattern in Compuware Uniface where modification-intent retrieve acquires database row locks through the TCC driver layer that persist until store or discard is called. Genero BDL retainers cover the FOR EACH UPDATE without COMMIT WORK pattern where Informix row locks are held after a batch function returns without committing. Advantage Database retainers cover the Delphi TAdsTable.Edit-without-Cancel pattern in ADS applications where the same class of “record lock acquired before a business-logic check, not released when the check fails” occurs via the Delphi TDataSet state machine rather than the SQLWindows SqlHandle cursor model.

FAQ: Gupta SQLWindows developer retainers