Restrictions on Using Direct SQL (Magic xpa 2.x)

A task with a Direct SQL command instead of a Main source is a normal Magic xpa task and has almost the same functionality. There are, however, some restrictions:

• All the tables participating in the SQL command must be from the database that is declared in the Database field of the SQL Command dialog box. Mixing tables from different databases is not allowed.

• In an Online task, do not update a column that belongs to the SQL command’s view (the result table). Such updates are never written back to disk because the result table is deleted when the task terminates. Use Links to other tables to update information.

• Do not use commands that alter the state of a record that is the current view of an ancestor task.

• During batch processing do not update a Group level of a variable. Group Level operations require that the preceding record be refreshed before the Group Level operation. Refreshing records is impossible because the task’s data stream is a one- way stream that cannot be stopped.

• Use task level transactions on batch SQL commands.

• Do not use the COMMIT and ROLLBACK commands in your Direct SQL. If these commands are not generated through Magic xpa’s standard transaction management layers, the results are unpredictable. COMMIT and ROLLBACK functionality is achieved correctly only by choosing the right transaction mode in Magic xpa’s task level table.

• The SQL statement is executed by the underlying database. The command syntax is the developer’s responsibility, and the developer is not prevented from using DBMS-specific extensions. If application portability among various SQL DBMSs is required, be careful not to include DBMS-specific SQL extensions in Direct SQL. In the SQL statements created by Magic xpa this is not a problem. The Magic xpa gateways generate the correct, optimized syntax for each database.

• When using Direct SQL in Online tasks, Magic xpa creates a temporary table. Optionally, the table can be created in the database. Creating the temporary table in the same RDBMS using that RDBMS’s utilities is usually more efficient than having Magic xpa read and write each record.

• Range on a Direct SQL Output argument is not allowed.

• To execute a stored procedure, prefix the name with the word exec as in:
  exec sp_order_update

• The gateway actually executes the generated program statements to recognize the correct form of the result set in the APG. So, if your statement modifies data, the modification will occur.

• DB2 and ODBC cannot be used as the Result Database of a Direct SQL task.

• In MSSQL, returned values can only be the result of a Select statement and not of an output parameter.

• A local database cannot be used in a Direct SQL statement.

• Direct SQL cannot be used in an Offline task.

• When using Direct SQL, raising a View Refresh event with Relocate Mode = 0 will not relocate on the last parked record, since there is no position for the records being shown.