Chapter 3: Make APPX Files Available to SQL
How APPX Files and Fields Become SQL Tables and Columns
When ASQL imports an APPX file, it creates a SQL table definition for it. The records stay in the APPX file; the definition tells the gateway how to read them. The gateway writes the definition the same way in every edition.
| In APPX | In SQL | Example |
|---|---|---|
| A file | A table named for the database, application and file, joined by underscores | File COUNTRY in application DMO and database DMO is the table DMO_DMO_COUNTRY. |
| A field | A column, in the same order as the fields in the record | The field REGION LABEL is the column region_label. |
| A field with occurrences | One column for each occurrence, numbered from 1 | A field AMOUNT with three occurrences becomes amount_1, amount_2 and amount_3. |
| A key | An index named for the table and the key's number, unique unless the key allows duplicates | Key 0 of COUNTRY is the unique index DMO_DMO_COUNTRY_0 on name. |
| A group, substring or synonym field | Nothing | See below. |
Each import also creates a public synonym with the same name as the table.
How field names become column names
- Letters become lower case. Spaces, hyphens and any other character that isn't a letter, digit or underscore become underscores. REGION LABEL becomes
region_label, and CITY-STATE becomescity_state. - Many applications start every field name with the file's name. ASQL drops that part, so in a file named CUSTOMER a field named CUSTOMER NAME becomes
name. It keeps the whole name when what's left would be one of the gateway's keywords or would start with a digit, so CUSTOMER NO stayscustomer_no, because NO is a keyword. - ASQL doesn't check the whole name against the reserved words, and it doesn't change a name that starts with a digit. Either one stops the import. See SQL Name Rules and Reserved Words, then rename the column in the script.
- Two different field names can end up the same, for example CITY-STATE and CITY STATE. The import then stops with
-20010 Duplicate column specification. Rename one of them in the script.
Group, substring and synonym fields don't become columns
These fields describe space in the record that other fields already cover, so ASQL leaves them out.
- A group is a name for several fields together. Each field inside it gets its own column.
- A synonym is another name for the same space as another field, so its data is in that field's column. Adding a synonym field in APPX doesn't add a SQL column. To show a column under another name, rename it in the script or use a view.
- A substring is part of another field. To show that part, use the SUBSTRING function in a query or a view. See SUBSTRING in the ODBC and SQL Reference.
Data types
| APPX field | SQL type |
|---|---|
| Alpha or logic | character(n), where n is the field's length in the record. In this edition a field stored as Unicode takes 4 bytes per character, so a 15-character field is declared character(60). |
| Date | date, time or timestamp, depending on which parts the field stores. Parts the field doesn't store are filled in from the current date and time. |
| Binary number of 1, 2 or 4 bytes | tinyint, smallint or integer |
| Packed number | numeric(p,s), with p digits in all and s of them after the decimal point |
The gateway finds the APPX file from the table's name, so don't change the table name in the script. For a friendlier name, use a view.