Execute

Calling a stored procedure, in three scenarios: named arguments; positional arguments; a parameter left out, so the procedure's default is used. The call is described with ArxProcedureExpression, the way a query is described with ArxQueryExpression; ARX sends CALL with every value as a parameter.

OUT and INOUT values, in two scenarios: an INOUT goes in and comes back (here filled in by the procedure when it arrives as NULL), and OUT values come back typed in Outputs. Each OUT is declared with its .NET type; ARX passes it as a typed NULL, as PostgreSQL requires (PostgreSQL 14 or later).

Calling a function, in three scenarios: one value (Value<T>()); rows (ResultSets[0]); a function run for its effect (void: no rows). A function is the same ArxProcedureExpression with Function: ARX sends SELECT * FROM instead of CALL. To filter, order or page a function's rows like a table, Retrieve reads it as a row source (RetrieveMultiple's RealCase6, Advanced8).

A procedure that returns tables, in two scenarios. A PostgreSQL procedure returns rows through cursors: each table is an INOUT refcursor parameter, declared with AddCursorParameter. ARX runs the call and reads every declared cursor in one transaction (two round trips), and returns each table as a result set named after its parameter.

Placing an order through a procedure, in two scenarios: sp_place_order writes the order and its lines (from the products' current prices) in one call and returns the new id and the total; the id is then used like any other (Retrieve reads the order back). The application doesn't repeat the pricing rule the procedure owns.

An account page built from one call: sp_account_report returns three tables (the contacts, the orders, a summary row), read together in one transaction, each by its cursor parameter's name. A function that returns its cursors itself (RETURNS SETOF refcursor) is read the same way with ReturnsCursors = true.

Paging what a procedure or function returns, the three ways, in four scenarios. Each sets PageInfo, as a query does, and gets back a result set with CurrentPage, HasNextPage and, when asked, the total. The total is opt-in: ask for it once (page 1), as for RetrieveMultiple.

1. The routine pages itself (best for large data: the database produces only the page). ARX fills its page arguments from PageInfo and reads its total from an OUT parameter.
2. ARX pages a function's rows: LIMIT/OFFSET and the total in one round trip. Cheap for a SQL function (PostgreSQL inlines it, the limit reaches the index); a PL/pgSQL function runs whole first.
3. ARX pages a table the procedure returns (a cursor): MOVE past the earlier rows, FETCH one page and one row more, MOVE ALL to count the rest. The skipped rows aren't sent, but the database still produces them.
4. Which to choose.

Business rules the database enforces, in three scenarios. sp_reserve_credit refuses an amount over the credit limit (RAISE EXCEPTION ... USING HINT) and an account on hold (its own code, AH001). Both arrive as ARX3005 RaisedByDatabase with the routine's message, its SqlState and its Hint, so the application can tell "the database said no" from "something broke", and branch on the routine's own code.

A nightly job that commits as it goes, in two scenarios. sp_recalculate_totals recalculates order totals in batches and commits after each, so a failure late in the run keeps the batches already done, and reports its progress. ARX's own call runs outside any transaction block, which PostgreSQL requires for a procedure that commits; inside an ARX transaction or a batch scope the same call is refused with ARX8001 and the reason.

The messages a routine sends while it runs, in two scenarios: RAISE NOTICE and WARNING come back in Messages, in order, for the application to show or log as it chooses (ARX never writes them to a log); CollectMessages = false leaves them out.

The errors an Execute call can raise, turned into messages, in two scenarios: what the database refuses (a routine that doesn't exist with these argument types, a function called as a procedure, a timeout) and what ARX refuses before it (a positional argument after a named one, an OUT parameter without a type, paging with nothing to page).

Catching an SQL injection attempt on Execute, in three scenarios. A routine name or a parameter name that carries SQL is refused with ARX1017 SuspectedInjection before the database. SQL in a value is a parameter: it's stored as text and runs nothing. Execute never takes SQL text.

Execute inside an ARX transaction, in two scenarios: a Create and a procedure in one transaction, the procedure's writes read back inside it, and everything gone after a rollback; and a business error from the procedure aborting the transaction, so the next call is refused (ARX1015) until the application rolls back.

Execute in a batch scope, in one scenario: an import of three orders through the procedure, one transaction. Each call has its own savepoint (asked for with Savepoints = true), so the order the procedure refuses (a credit limit) is undone alone and the import continues; the others are committed together at the end.

Exporting everything a procedure returns, page by page, in one scenario: sp_products_page pages itself (the first way of paging), the export asks for the total on the first page only, and reads pages until HasNextPage is false. Each page is one call, so the memory is one page's.

A security check around Execute (SecurityGuard, in the project root), in three scenarios: a report run through an allowed list of procedures passes; an endpoint that runs whatever procedure the request names is probed, and the attacker is blocked after three attempts; and a false positive (an old client sending "account report (v1)") is triaged: support unblocks the user and the endpoint maps the old name.

Execute on a service whose schema is preloaded and Strict, in three scenarios: a procedure and a function called as usual, because Strict covers tables and views, not routines; a function read as a row source, with the routine's own columns; and the same service still refusing a table column that wasn't preloaded.