D3 ODBC II - SQL-CREATE-TABLE
Spectrum Nov/Dec '97
Nathan Rector
Natec Systems
nater@northcoast.com
http://www.northcoast.com/~nater/
Phone: (707)443-6716
In the Sept/Oct '97 Issue of Spectrum, I discussed how to setup the client portion found on the Windows workstation for ODBC. There is another step to work with ODBC in D3. That step is mapping the D3 database into the "normalized" format that ODBC and SQL require in order to access and update the data.
D3 has two commands that allow a user to map their database into something that will work. One is SQL-CREATE-TABLE which is a TCL command. The other is CREATE TABLE which is used in the SQL sentence.
SQL-CREATE-TABLE is used on existing D3 files where as CREATE TABLE is used to create new D3 files that will work with SQL and ODBC. Do not use the CREATE TABLE command with an existing file or it destroys any data in that file.
SQL-CREATE-TABLE is very simple to use if you followed the standard dictionary definition parameters. This standard states that all primary dictionary items are 'A' types. All synonyms or specially modified dictionary items are 'S' types, and all dictionary items that you don't want the user to have access to are 'X', or hidden, types.
If your dictionaries are setup with this standard, all you need to do is type SQL-CREATE-TABLE and the file name to create the SQL table. There are a few options that can be applied to this TCL command.
b - maintain backward compatibility with D3 version below 7.1
c - Command line override of an SQL Macro. This will be explain a little later on in this article.
o - override existing table
p - output to printer
s - include synonym or substitute attributes.
x - include protected or hidden attributes.
z - displays the information on how and what is converted when this command is run.
There are a few things to keep in mind when creating SQL tables. One is naming conventions. When creating the SQL table and columns, the names of the tables and columns may change slightly compared to the original names found in the D3 file. Use the 'z' option to see these name changes.
If there are any controlling and dependent fields, the SQL-CREATE-TABLE maps this information into standard SQL associations with the needed multiple tables for each association.
Since SQL uses rows to represent each record, and columns to represent each attribute of the item, SQL tables don't have the room or ability to create multi-value items. To get SQL tables to work similar to multi-values, SQL requires tables to be nested together.
One thing to keep in mind when using SQL tables, fixed length records are used. When the columns are created for the table, it uses the attribute length found in the dictionary. This restriction is foreign to the Multi-value community. We are used to not having to care about the length of the data being longer than what the dictionary is defined to. If the information is longer than the defined dictionary length, the data is likely to be truncated at that length when accessing information through ODBC.
One last thing to keep in mind is that all D3 conversions are supported except 'A' correlatives that use the 'n' naming operator. For example, 'a1 :" ": 2' will work, but 'an(first.name) :" ": n(last.name)' will not.
SQL-CREATE-TABLE CUST
At times you may not want to map your complete D3 dictionary into a SQL table. If you only map the information that is needed outside of D3, then you are able to control your data better and keep users from changing information they shouldn't be changing. You create just that much more secrity.
To do this, you use the 'c' option along with the list of fields you want to be mapped into an SQL table.
SQL-CREATE-TABLE CUST NAME ADDRES (C
This creates an SQL table for the CUST file with only NAME and ADDRESS accessible through ODBC. No other fields will be available through ODBC unless the program that is using ODBC has 'pass-through' available and an ALL (standard D3 ACCESS) command could be used in place of SQL.
SQL-CREATE-TABLE maps multi-value fields into the needed nested files without extra help as along as these fields are controlling/dependent fields. The problem is that not all the multi-value fields are controlling/dependent fields. For example, a customer file may have a multi-value field for the phone number that isn't a controlling field, nor is it a dependent field.
Files that have these types of fields require the SQL mapping to be done manually. You still use the SQL-CREATE-TABLE to create the mapping, but you have to list the fields and how they are associated in command line.
If the first attribute in a multi-value list has unique values, then place all the fields that are dependent on each other between []. If the first field does not have unique values, then place the list of fields that are dependent on each other between {}.
SQL-CREATE-TABLE CUST CUST.NO NAME ADDRESS [PHONE] [SIGNER_LIST {BEG_DATE END_DATE}]
This statement creates the SQL tables needed for the customer file. It creates a nested table for the phone with the assumption that the phone will always be unique. It also creates a nested table for the SIGNER_LIST that again assumes the data is unique. Another table nested with the SIGNER_LIST table is also created to keep track of the BEG_DATE and END_DATE fields, but allows the table to not have unique IDs.
Table: CUST
CUST_NO
NAME
ADDRESS
Table: CUST_PHONE
PHONE
CUST_NO
Table: CUST_SIGNER_LIST
SIGNER_LIST
CUST_NO
Table: CUST_SIGNER_LIST_DATE
BEG_DATE
END_DATE
CUST_NO
SIGNER_LIST
SQL-CREATE-TABLE can be easy to use, or can get rather complex depending on the complexity of the files and data you want to map. Be sure you know how you want it mapped. If you don't map the table the way you want to the first time, you have to create the table from scratch all over again. There is currently no way to change or add to a SQL table once it's created without recreating it from scratch each time.
If you want to view how a current table is created then use the command SQL-DISPLAY-TABLE.
:SQL-DISPLAY-TABLE CONTACTS
===============================================================================
TABLE NAME: CONTACTS
PRIMARY KEY: REC_NO
MACRO: REC_NO COMPANY_NAME
ATTRIBUTE COLUMN-ID DATA-TYPE COL# SIZE CORRELATIVE
--------- --------- --------- ---- ---- -----------
REC.NO REC_NO VARCHAR 0 6
COMPANY.NAME COMPANY_NAME VARCHAR 1 30
===============================================================================
TABLE NAME: CONTACTS_PHONE
PRIMARY KEY: REC_NO,PHONE
NESTED KEY: REC_NO
REFERENCES: CONTACTS
MACRO: PHONE REC_NO
ATTRIBUTE COLUMN-ID DATA-TYPE COL# SIZE CORRELATIVE
--------- --------- --------- ---- ---- -----------
PHONE PHONE VARCHAR 9 13
REC_NO REC_NO VARCHAR 0 6