OK, let's talk about the doughnut not the hole. Magic xpa supports both implicit and
direct SQL. There are a few things you should consider when working with SQL. In
general, the task that actually opens a transaction should have at least one
table declared from each database that will participate in the transaction. Otherwise,
if no table has been declared, when Magic xpa has to open a transaction (either
task level or record level), then it will default to a situation where the ISAMTransaction keyword is taken into account.
You may not want this, so make sure you declare the needed tables for each
database.
When using Magic xpa with implicit
SQL, Magic xpa allows you to interact with one or more SQL databases at the
same time but still keeps the interaction with the database transparent to the
developer. Magic xpa translates the operations and parameters in your task to a
SQL statement. This behavior enables the use of a specific gateway that allows
Magic xpa to directly interact with each database using the DBMS API. (The
Magic xpa gateway list appears in the MAGIC_GATEWAYS section in the Magic.ini
file. To define the Magic xpa environment when working with SQL databases, you
must know how to define the flags and settings in the Magic.ini file.)
When using Magic xpa with direct SQL
statements, Magic xpa allows you to create programs that contain a free SQL
statement (ANSI SQL). This statement will be sent directly to the database
without Magic xpa checking it. This option allows you to perform database
manipulations that are not otherwise supported using the Magic xpa operations or
will be performed more efficiently using a direct SQL statement (such as
aggregate functions). Keep in mind that in Online tasks, Magic xpa creates a
new table that will store the result set of a statement or store procedure. A
Create table operation is a "heavy" operation and for this reason, it
is not recommended to use Direct SQL tasks frequently in Online mode.
