How Do I Define the Mapping Between a Magic xpa Field and a Database Column? (Magic xpa 4.x)
Magic xpa does a good job of making it easy to handle the huge variety of data types, by insulating you from the details of the implementation. When you are working in Magic xpa, you only have to deal with a handful of different types -- alpha, numeric, date, time, boolean -- and you don’t have to worry about how those variables are actually stored in memory or in a database.
However, you do have control over the actual database storage. These details are handled in the Properties (Alt+Enter) for each column in the database table.
The details about the data attributes of the field in Magic xpa are contained in the Details section. Here we see this is a large numeric field with 10 digits, which can hold any value from -2,147,483,648 to -2,147,483,648.
In the Storage section, we see exactly how this field is actually implemented. It is stored as a 4-byte signed integer. The Def/Null and SQL sections give further information about the details of the field implementation. These are summarized below, but you can get more information about any one option in Magic xpa by positioning the cursor on it and pressing F1.
Null allowed: If this is set to No, then the next three fields are greyed out and don’t apply to this field; nulls will not be used at all. If Yes, then you can set the values for Null value, Null display, and Null default.
Null value: The value that the field is considered to contain when it is NULL. For instance, you might want to
If this is not specified, then the actual value of NULL is fetched from the DBMS section of the Magic.Ini file. NULL values are often some strange value that would never normally be entered. However, in your programming you don’t have to remember the actual value because you can use the NULL() and ISNULL() functions.
Null display: What the user sees when the value is NULL. For instance, the Null value might be some odd date such as 01/01/1901, but the user could see “Please enter your birthday”.
Null default: If this is Yes, then the default value for the field is NULL. Otherwise, the default value is whatever is entered in the Default value property.
Default value: Magic xpa will automatically initialize fields when they are first created; you don’t have to do it manually. Most fields default to zero or blank, as you would expect. But if you have some specific value you want to use as an initialization value, you can enter it here.
Database default: Magic xpa passes this string to the DBMS in the CREATE TABLE statement. This allows you to set up the default value inside the DBMS. For example, when you create a database default ‘defvalue’ in an MSSQL table, Magic xpa generates the following: CREATE TABLE owner1.table1 (Col1 CHAR(10) NOT NULL DEFAULT ‘defvalue’). Magic xpa adds this string without any additional formatting to the CREATE TABLE statement.
Char.Set: Allows you to choose between ANSI or OEM storage.
Default Storage: If set to Yes, the mapping will be determined by the DBMS, and the Stored As property will be ignored. This is useful if you want to use the same Data Source definition for multiple DBMS’s.
Stored As: This is the actual data storage type. Zoom from this field to view and select the storage type. Note that for SQL columns, this can be further specified in the SQL section Type property.
Modifiable: Specifies if a user can change this field once the record is created. If Modifiable=No, then the user can enter a value when in Create mode, but not in Modify mode.
Size: The number of bytes allocated to the column.
Definition: Normal or String.
Update Style: This only applies to SQL columns when using Deferred transactions. If it is set to Differential, then Magic xpa updates the value based on the difference, rather than the actual value in memory. This is important when multiple users are working with the same field simultaneously, for example, when updating the current amount in stock.
Database Information: You can encode SQL code here to pass to the underlying DBMS.
DB Column name: What the SQL column will be called in the DBMS.
Type: The underlying SQL type. Usually you don’t need to specify this: Magic xpa chooses the type based on the Stored As property. But if you want to specify it directly, you can. Also, if you bring in the definition from the DBMS using Get Definition, you will see the underlying type here.
User Type: Some DBMS’s allow you to create your own “user types” within the DBMS. If you do this, you can specify them here and the user type will be used by Magic xpa in creating the table.
