Magic xpa SQL Considerations

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.