Oninit® SQLMorph — Embedded SQL
SQL inside a function runs through the DataBlade API on the session that called the function, so it sees the same transaction, isolation level and locks as the calling statement.
Supported
SELECT ... INTO,INSERT,UPDATE,DELETE, and DDL passed through unchanged.PREPARE,EXECUTE [USING] [INTO],EXECUTE IMMEDIATE,FREE.DECLARE [SCROLL] CURSOR,OPEN [USING],FETCHin every direction,CLOSE,FOREACHwithEXITandCONTINUE.UPDATE ... WHERE CURRENT OF candDELETE ... WHERE CURRENT OF con aFOR UPDATEcursor.DECLARE c CURSOR FOR INSERT ...withOPEN,PUT c FROM v1, v2(orPUT cwith the variables named in theDECLARE),FLUSHandCLOSE.EXECUTE FUNCTION/EXECUTE PROCEDURE, also asDECLARE c CURSOR FOR EXECUTE FUNCTION f(x)for a function that returns rows.status,NOTFOUNDandsqlca.sqlcode,sqlca.sqlerrdand the othersqlcafields as readable values.WHENEVER ERRORandWHENEVER NOT FOUNDwithCONTINUE,STOP,GOTOandCALL.
Host variables
A variable in an SQL statement ($x, :x, or a bare name that matches a variable and no column)
becomes a ? parameter bound in binary by the variable’s type; results are read back in binary too, so no
text conversion changes a value on the way. A bare name that is also a column name needs --schema to be
resolved; without it the name is taken as the variable.
Errors
An SQL error sets status and sqlca. Unless a WHENEVER ERROR says otherwise it is
raised as an engine exception and the calling statement fails with the original error. After
WHENEVER ERROR CONTINUE the function carries on and tests status itself.
Update cursors and insert cursors
The engine requires a transaction for a FOR UPDATE cursor (error −255 without one), as it does for 4GL
itself. A converted function cannot start one (see below), so the caller opens it: BEGIN WORK before the call,
COMMIT WORK after. PUT runs the prepared INSERT at once, one row per PUT; the
engine does not offer a routine a buffered insert cursor, so FLUSH only checks that the cursor is open.
PUT ... FROM takes variables or rec.*, not expressions.
Local statement ids
A prepared-statement id or a cursor belongs to the function that declared it. Passing one to another function is not supported.
SQL ... END SQL
A SQL ... END SQL block holding one statement converts like the same statement written inline. Host
variables are marked with $, including in SELECT ... INTO $var. An empty block is refused.
Not converted yet
transactions (BEGIN / COMMIT / ROLLBACK WORK): the calling statement owns the
transaction, and a policy for converted code is not decided.