Documentation:Migrating Stored Procedures to MDriven

Migrating Stored Procedures to MDriven

This page was created by Wikiadmin on 2026-07-29. Last edited by Wikiadmin on 2026-07-29.

You can migrate legacy stored-procedure use into MDriven by deciding, procedure by procedure, whether to retain direct SQL through SQLPassthrough or express the work through OCL; this guide is for developers moving an existing SQL-backed application into an MDriven model.

Start with a procedure inventory

List each stored procedure before changing it. Record what it reads or changes, its inputs and result values, and the MDriven use case that calls it.

For example, record a procedure that returns a key and two totals as an aggregation procedure. Record a procedure that updates rows by joining two tables as an update procedure. This makes the database work visible before you decide where it belongs.

Choose where each procedure belongs

Use the following decision table for each procedure.

Procedure characteristic	Migration choice	Example
The work must remain direct SQL, calls an existing stored procedure, operates over a large data volume, or performs an update that joins tables.	Keep the database operation and call it with SQLPassthrough. Start the expression with a class, not an object instance.	Call `sp_Upd_Losen` and declare its `Int32` return code.
The query can be expressed in OCL.	Use an OCL persistence-service operator instead of SQLPassthrough: PSEval for objects, PSEvalValue for one value, or PSEvalTuples for tuples.	Replace a SQL query whose result is objects, one value, or tuples with the corresponding OCL-based operator.
The procedure contains business rules that you want represented in the model.	Identify the rule and move it into model-level logic where it can be expressed in OCL. Retain SQLPassthrough only for the database-specific work that still requires SQL.	Separate a procedure's query or update operation from its rule evaluation, then review the rule for an OCL expression.

Evaluate performance and coupling

SQLPassthrough bypasses model-level OCL-to-SQL translation. Use it when SQL is the appropriate database operation, including large-volume work, stored-procedure calls, and updates that join tables.

This choice also couples the expression to database-schema details. Test the SQL against the database used by your application. Do not assume that replacing SQLPassthrough with OCL, or OCL with SQLPassthrough, improves performance without testing the actual query and result shape.

Migrate common patterns

Aggregations that return tuples

Keep an aggregation in SQLPassthrough when it is an appropriate database operation. Declare one result type for every selected column, in the same order as the SQL select list. SQLPassthrough returns tuples with values in Part1, Part2, and subsequent parts.

AccountPlan.SQLPassthrough(
  'select somekey,sum(somestuff),sum(someotherstuff) from table1,2,3 where ...',
  String,Integer,Integer
)->collect(xtuple|
  let xobject=SomeNewTransient.Create in
    (xobject.Key:=xtuple.Part1;
     xobject.SomeSum:=xtuple.Part2)
)

In this example, somekey maps to xtuple.Part1, and the first sum maps to xtuple.Part2. If the same query can be expressed in OCL, evaluate PSEvalTuples as the OCL-based alternative.

Stored-procedure updates

Keep a legacy update procedure when it must execute as direct SQL. A stored-procedure call passes the current object's identifier, a quoted string literal, and the automatically available @aNyttLosenord variable to a stored procedure.

Use escaped quotes for a string literal that SQL must receive in quotes. Do not quote a variable that is already available to the called function. The Int32 declaration represents the SQL Server return code.

For direct updates that join tables, remember that SQLPassthrough changes database tables rather than loaded model objects. For an example of this pattern in an import scenario, see Documentation:SQLImport multiple tables with associations.

Database changes made outside MDriven

A retained stored procedure can change tables outside normal MDriven object changes. SQL Server change tracking explains that MDrivenServer does not discover direct table changes made by another client or service unless external change handling is configured. Apply change tracking only to tables that can be affected by the non-MDriven system.

Use the migration result in a ViewModel

When a ViewModel needs a database-computed value without loading all involved objects into memory, expose the SQLPassthrough result with a PSExpression. A PSExpression column is an expression column whose name starts with PSExpression_. Refresh the database expressions for the current ViewModel from EAL with:

selfVM.PSExpression_Refresh()

Review each retained procedure

Confirm that the SQL operation still requires direct database SQL.
Confirm that the SQL text, result types, and tuple-part order agree.
Test quoted literals and function variables separately.
Test the procedure against the application's target database.
If the procedure can change data outside normal MDriven object changes, plan external-change handling as described in SQL Server change tracking.

Contents