You can call a SQL stored procedure from an OCL expression by using sqlpassthrough; this is for developers who need database-side work from an MDriven model.
Call a stored procedure
sqlpassthrough runs a SQL expression from a class expression. Start the expression with a class, not an object.
- Define the stored procedure in the SQL database.
- Create the OCL expression that calls it with
sqlpassthrough. - Pass the return type as the final argument.
For example, the following expression calls sp_Upd_Losen and returns its return code as Int32:
Anvandare.sqlpassthrough('sp_Upd_Losen ' + self.AnvId.asString + ', \' + self.Anvandarnamn + '\', @aNyttLosenord', Int32)
In this example:
| Part | Meaning |
|---|---|
Anvandare
|
The class that starts the expression. |
sp_Upd_Losen
|
The stored procedure called in SQL. |
self.AnvId.asString
|
A value from the current object, converted to a string for the SQL expression. |
self.Anvandarnamn
|
A string value enclosed in SQL quotes. The OCL escape sequence \' supplies the quote characters.
|
@aNyttLosenord
|
A variable available when the function is called. It is automatically available to the SQL expression. |
Int32
|
The return type. |
String values and variables
Quote string values that are not variables. In the example, self.Anvandarnamn is surrounded by escaped quotes. A variable that is available when the function is called, such as @aNyttLosenord, is automatically available.
Return values and result sets
Use the final type argument to receive a return code. For a query that returns multiple values, sqlpassthrough can create tuples. For example:
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))
For related operators and additional examples, see Documentation:OCLOperators sqlpassthrough and Documentation:SQL.
Database evolution
Persistence mapping describes how MDriven compares old and newly generated mappings when evolving the database schema. This guidance does not define a stored-procedure creation, deployment, or migration workflow. Manage that workflow according to your database deployment process until MDriven-specific procedure migration guidance is available.