You can use SQLPassthrough in an OCL expression or EAL action when you need to execute SQL directly in the database and return scalar values or tuples; it is intended for developers who understand the target database schema.

Syntax

SomeClass.sqlpassthrough('SqlExpression', Type1OfResult, Type2OfResult, ...)

Start the expression with a class, not an object instance. The first argument is the SQL text. Each following argument declares one returned value type.

Part	Meaning	Example
SomeClass	A model class used as the starting point for the OCL operator.	AccountPlan
'SqlExpression'	SQL sent to the database.	'select somekey, sum(somestuff) from ...'
Result type arguments	The expected types of values returned by the SQL expression.	String,Integer,Integer

Choose SQLPassthrough deliberately

Use SQLPassthrough when SQL is the appropriate way to perform work in the database, for example when you need an SQL operation over a large volume of data, call a stored procedure, or execute an update that joins tables.

For queries that you can express in OCL, consider the OCL-based operators instead:

• PSEval returns objects from an OCL persistence-service expression.
• PSEvalValue returns one value from an OCL persistence-service expression.
• PSEvalTuples returns tuples from an OCL persistence-service expression.

SQLPassthrough deliberately bypasses the model-level OCL-to-SQL translation. This can be useful, but it also means that your SQL assumes details of the database schema. Keep that trade-off explicit and test the SQL against the database used by your application.

Call a stored procedure

The following example calls the stored procedure sp_Upd_Losen and declares an Int32 result, which is the SQL Server return code:

Anvandare.sqlpassthrough('sp_Upd_Losen ' + self.AnvId.asString + ', \'\'' + self.Anvandarnamn + '\'\'', @aNyttLosenord', Int32)

In this expression:

• self.AnvId.asString inserts the current object's identifier into the SQL text.
• self.Anvandarnamn is a string value. The generated SQL must contain quotes around a literal string, so the OCL expression uses escaped quotes (\') to produce them.
• @aNyttLosenord is available automatically because it is a variable available when the function is called.
• Int32 declares the stored procedure return code.

Quote string values correctly

SQL string literals need quotes. In an OCL string literal, escape an embedded quote with \'. The stored-procedure example builds the equivalent of a quoted SQL string around self.Anvandarnamn.

Do not treat a variable that is already available to the called function as a quoted literal. The @aNyttLosenord variable in the example is passed as the variable name rather than being surrounded by quotes.

Return query results as tuples

When SQL returns multiple columns, declare one result type for each column. SQLPassthrough returns tuples whose values are available as Part1, Part2, and so on.

This example returns a key and two aggregated values, then creates a transient object for each returned tuple:

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)
)

The declared result types map to tuple parts in their declared order:

Declared type	Tuple value	Example use
First type: String	xtuple.Part1	xobject.Key:=xtuple.Part1
Second type: Integer	xtuple.Part2	xobject.SomeSum:=xtuple.Part2
Third type: Integer	xtuple.Part3	Available for a further assignment when needed

Make the SQL select-list order and the declared type order match. For example, if the first selected column is a key and the second is a sum, declare the key type first and the sum type second.

Update database rows

SQLPassthrough can also execute data-changing SQL. For example, the following update sets the SAPProfitCenterID foreign-key value in SAPPlantMaterial by joining the two tables on matching profit-center values:

SAPPlantMaterial.sqlpassthrough(
  'update SAPPlantMaterial set SAPProfitCenterID=SAPProfitCenter.SAPProfitCenterID from SAPPlantMaterial join SAPProfitCenter on SAPProfitCenter.ProfitCenter=SAPPlantMaterial.ProfitCenterKey',
  Integer
)

The Integer argument declares the returned integer value for this SQL expression. Use direct updates with care: they operate on database tables rather than by changing loaded model objects.

For the data-import scenario that uses this pattern, see Documentation:SQLImport multiple tables with associations.

Use in PSExpressions

You can expose SQLPassthrough results in a ViewModel by using a PSExpression. This is useful when a ViewModel needs database-computed values without loading all involved objects into memory.

A PSExpression column is a ViewModel column whose name starts with PSExpression_ and is configured as an expression column. To execute the database expressions for the current ViewModel from EAL, call:

selfVM.PSExpression_Refresh()

PSExpression Refresh finds the PSExpression columns on the current ViewModel and refreshes their database-computed values. Follow the setup and execution guidance in PSExpression, or how to do things in the DB from MDriven.

Verify results and performance

Before relying on a new SQLPassthrough expression:

1. Run the SQL against the intended database and verify both the returned values and their order.
2. Confirm that each declared OCL result type matches the SQL value returned in that position.
3. Test with representative data volumes.
4. Ensure that the SQL Server has the indexes and performance configuration required by the query.
5. Recheck SQLPassthrough expressions when database tables, columns, or stored procedures change.

See also

• Documentation:PSExpression , or how to do things in the DB from MDriven
• Documentation:OCLOperators PSExpression Refresh
• Documentation:OCLOperators PSEval
• Documentation:OCLOperators PSEvalValue
• Documentation:OCLOperators PSEvalTuples
• Documentation:SQLImport multiple tables with associations