Execute

Calling a stored procedure, in three scenarios: named arguments; positional arguments; a parameter left out, which MySQL refuses (its procedures have no default values). The call is described with ArxProcedureExpression, the way a query is described with ArxQueryExpression; ARX sends CALL with every value as a parameter, and checks named arguments against the procedure's own names.

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. An OUT starts as NULL inside the procedure; an INOUT carries the value given. Each is declared with its .NET type, and MySqlConnector reads them after the call's last result set, in the same round trip.

Calling a function, in three scenarios: one value (Value<T>()); rows, which on MySQL come from a procedure (ResultSets[0]); and an effect, also a procedure's job. A function is the same ArxProcedureExpression with Function: ARX sends SELECT fn(…) instead of CALL. MySQL functions return one value, never rows (RetrieveMultiple's RealCase6 and Advanced8 show what to read instead).

A procedure that returns tables, in two scenarios. A MySQL procedure returns a table by selecting it: each SELECT it runs is a result set. ARX runs the call in one round trip and returns the result sets by position (ResultSets[0], ResultSets[1] …), in the order the procedure sends them.

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 round trip, each by its position: the order of the procedure's SELECTs.

Paging what a procedure returns, 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 procedure 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. A function's rows: MySQL functions return one value, never rows, so paging one is refused before anything is sent (ARX8001). Page the table with RetrieveMultiple instead: LIMIT/OFFSET and the total in one round trip.
3. ARX pages a table the procedure returns (a result set): MySQL has no cursor to move, so ARX reads past the earlier rows, keeps one page and one row more, and counts the rest. Every row crosses the network.
4. Which to choose.

Business rules the database enforces, in three scenarios. sp_reserve_credit refuses an account on hold (SIGNAL SQLSTATE '45000' … MYSQL_ERRNO = 50101) and an amount over the credit limit (MYSQL_ERRNO = 50102). Both arrive as ARX3005 RaisedByDatabase with the procedure's message and its error number as NativeCode, so the application can tell "the database said no" from "something broke", and branch on the procedure's own number.

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 what it did as a note. ARX's own call runs outside any transaction, which a procedure that commits needs. MySQL lets a procedure COMMIT inside the caller's transaction too, and that COMMIT commits everything the transaction did before it, without an error ARX could see: call such procedures outside ARX transactions and batch scopes.

The messages a procedure sends, in two scenarios. A MySQL procedure reports with a warning: SIGNAL SQLSTATE '01000' (class 01 is a warning, not an error, and the procedure goes on). MySQL keeps only the warnings of the procedure's last statement, so a procedure reports one note when it ends rather than progress while it runs. It comes 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 procedure that doesn't exist, arguments of the wrong type, 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, a cursor parameter).

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 procedure's result set, with the routine's own columns; and the same service still refusing a table column that wasn't preloaded.