Cross-Database Programming (Magic xpa 4.x)

Sometimes it is necessary to create an application in which the customer determines on which RDBMS it will run. For example, the software house may have developed their banking application on MSSQL and sold it to Stones Savings Bank who has an Oracle database. The same application may have also been sold to Stones Loans Bank, which runs on a DB2 database.

There are some guidelines that may make this transition as smooth as possible.

Default Storage

The Default Storage property is defined per column in the table. It takes into account the inherent defaults that Magic xpa has for each data type. This is mainly relevant for tables that are created via the application itself and not via the DBMS. In this case, it is the Magic xpa DBMS gateway that defines the storage that will be created in the database.

As an example, let us look at a numeric field. In some DBMSs this would be defined as INTEGER and in others it may be NUMBER or PACKED DECIMAL. The Magic xpa gateway for that specific DBMS will handle this difference internally.

Magic SQL Clause

Sometimes the developer may need to add SQL statements to the WHERE clause sent by Magic xpa to the DBMS. These are entered as a DB SQL range and sent as entered to the DBMS. When writing cross-database applications, you will often find differences in some of the functions. For example, the function to fetch a substring of a certain string is Substr in DB2 and Oracle, and Substring in MSSQL. This obviously causes a problem.

You can use the Magic SQL range, so that Magic xpa performs a syntax check, and regular Magic xpa functions can also be used. As an example we can take the above example of a subset of a string. Instead of battling with the various nuances of different DBMSs, we simply use the Magic xpa internal function: MID.