Stored Procedures - SQL Considerations (Magic xpa 4.x)
Stored procedures provide a very powerful way to move parts of the application logic to the server. They can be called from within the Magic xpa environment by using the Direct SQL feature.
When Magic xpa sends a COMMIT statement, all connections are committed.
The syntax of stored procedures differs by database, so using this feature may conflict with the application’s portability requirements.
Magic xpa supports returns in stored procedures for the SQL Server.
To implement this, the statement that you enter in the SQL Command dialog box, should contain the following syntax:
? =
For example: EXEC ? = proc_name
You also need to specify a Virtual variable in the Return Code field.
If the return code is BOOLEAN or the statement does not contain the syntax specified above or a return code, then Magic xpa will only display the status of the stored procedure (i.e. Success/Failure).
Note: The stored procedure will return a blank return code if it has no output. To get the return code in such cases, define a dummy Select statement in the stored procedure.
Magic xpa enables the developer to determine which Select statement to return (in SQL Servers).
To implement this, you need to add the following syntax to the SQL statement in the SQL Command dialog box:
/*=== ROWSET=n ===*/
where n is the number of the Select statement you want returned.
For example,
/*=== ROWSET=5 ===*/ will return the fifth result set.
Specifying a non-existing rowset number will return an error message.
Magic xpa enables the developer to use a Cursor for DSQL Statements in SQL Server Stored Procedures.
Magic xpa supports using a cursor for DSQL statements that return a result set.
This can be done by adding “/*===CURSOR=Y ===*/” to the DSQL statement.
See also Cursors - SQL Considerations.
Magic xpa supports DB2 stored procedures with Select statements, IN/OUT parameters, or both.
When using the APG button, the virtuals created will first represent the OUT/INOUT parameters and then the result columns of any existing Select statements.
Procedures without INOUT or OUT parameters must be called as a PL/ SQL block:
begin procname (par1 int, par2 int); end;
If the procedure accepts more than one parameter, separate the parameters with commas. If the Oracle parameters are inout or out, the APG button must be selected before executing the procedure. Do not enter an out parameter in a stored procedure. Instead, use a comma as a placeholder. Do not add any punctuation at the end of the procedure.
A single argument sent to a stored procedure in Oracle is limited to 32K characters. Since version: 3.1
