Skip to main content

SQL Support

PPJ adapts SAL database calls to ADO.NET providers. Its common API supports connected reading and buffered ResultSet mode, together with bind and into expressions. Provider differences still matter: SQL dialects, parameter syntax, types, transactions, and error codes must be tested against the actual database.

Database Configuration​

CTD used the SQL.INI file to associate a data source name to a particular database. We use the sql.config file (in XML format) to associate a data source name to a specific ADO.NET driver and connection string. See Configuration.

ResultSet mode​

When ResultSet mode is off, the SQL layer in the PPJ Framework uses the fast ADO.NET's DataReader objects to scroll the result set, forward only. This is the connected mode and requires the connection to the database to be kept alive.

When ResultSet mode is on, the PPJ Framework uses ADO.NET's DataSet class to load the entire result set in memory (which also allows the code to scroll the result set backward) when the statement is executed.

Multiple Active Result Sets (MARS)​

The PPJ Framework is ready for both ADO.NET drivers, the ones that support MARS and the ones that don't. When an ADO.NET driver supports MARS, it is possible to execute "nested" sql statements without losing the previous result set. Buffering a result set or using separate connections can avoid a single-reader restriction, but neither is semantically identical to MARS. Separate connections can change transaction boundaries and visibility.

Choosing a Mode​

Forward-only: database → open reader → process row → process next row
Buffered:     database → load result into DataSet → navigate stored rows

With forward-only reading, a nested query may contend with the still-active reader. With buffering, the database result has been materialized, but the stored rows can become stale while another transaction changes the database. Neither mode automatically saves edits made to in-memory values.

For a list of a few hundred records that users navigate backward, buffering may fit the interaction. For a large sequential export, forward-only processing may avoid materializing the whole result. Measure with the actual provider: changing modes can affect when errors occur, how long resources remain held, and peak memory.

Use forward-only reading when rows can be processed sequentially and the connection can remain open. Buffered ResultSet mode supports backward navigation but consumes memory proportional to the result. Buffering does not make later changes automatically persist to the database.

For SQL Server, configure both PPJ's MARS setting and the provider connection string where required. MARS is not parallel command execution and is not a substitute for thread safety. See enabling Multiple Active Result Sets.

Test commit, rollback, nested queries, timeouts, and reconnect behavior with the same handle-sharing settings used in production.

Handles, Connections, and Transactions​

A SAL SQL handle is not necessarily a dedicated physical database connection. Under a sharing configuration, two handles can use one connection. Provider connection pooling is different: it reuses physical connections behind logical opens and closes.

Before adding a second query inside a fetch loop, identify whether it will share the first query's connection and transaction. Enabling separate connections can remove an active-reader conflict but also separate transaction state. Enabling MARS permits particular overlapping operations on a supporting connection; it does not turn the handle into a thread-safe concurrent object.