How Do I Set Data Source Mappings to Support Working with Multiple DBMSs? (Magic xpa 3.x)
One of the strengths of the high-level data definitions in Magic xpa is that you can use the same data source definition with multiple types of DBMSs. For instance, you can have a definition that works with an MSSQL table, but can also be used by Oracle or MySQL.
There are some restrictions here, because every DBMS has its own way of doing things. Also, you have to be sure that any user-defined entries are the same in the various databases.
However, Magic xpa provides a couple of options to make this easier, by allowing you to define the data at a higher level and allowing Magic xpa to set up the DBMS-specific implementation at runtime.
Column Properties->Storage->Default Storage
If this property is set to Yes, then Magic xpa will ignore the Stored As property and instead use whatever storage works for the particular DBMS in use. For instance, if you have a numeric field, one DBMS might define that as INTEGER while another defines it as NUMBER or PACKED DECIMAL.
The syntax of WHERE clauses can vary between DBMSs. For instance, the function to fetch a substring of a certain string is Substr in DB2 and Oracle, and Substring in MSSQL. Obviously you cannot create a WHERE clause that works for both.
You can avoid this problem by using the Task->Range/Locate->SQL Where->Magic xpa SQL field to encode an expression in Magic xpa. Here, the syntax is purely Magic xpa syntax, but it will be translated to create the underlying SQL WHERE command at runtime.
