Advantage Developer Zone

 
 
 

Exporting Tables as Code

Wednesday, January 21, 2009

Advantage Data Architect includes several tools which can assist in creating and deploying databases. One of these tools that is often overlooked is the Export Tables as Code tool. This tool allows a developer to generate Delphi, C++ Builder code or SQL scripts which can be used to create the specified table(s).

The SQL option can be used on both dictionary and free tables and can optionally include all table data. This utility is an enhancement to the Generate SQL Script option available for dictionary objects bringing similar functionality to free tables.

Tables to Code

The Export Tables as Code tool can be opened from the Tools menu (Tools->Export Table Structures as Code…). The utility will generate Delphi, C++ Builder or SQL code for generating the table.

When generating Delphi or C++ Builder code the specified server type(s), will be assigned to a connection component. The generated code will be displayed in a new window. From this window you can save the code for import into your project.

When generating SQL you have the option to include all the data as well as the table structure. This can be particularly useful when you are adding domain (lookup) tables. The generated SQL will be displayed in the SQL Utility by default or it can be written directly to a file (which is faster for large datasets).

 

Using the Utility

The first step when using the utility is to select the table(s) you wish to generate code for. The utility will use the active connection to connect to the tables. The active connection can be to a data dictionary or a directory of free tables. Clicking on the Add Table(s)… button will bring up a dialog containing a list of all the tables available on that connection. Once the tables are selected they will be listed in the grid.

After selecting the table(s) specify any options and click the OK button. The code or SQL will then be generated. In the example below I generated Delphi code for a domain table which contains codes for marital status.

Using the SQL option I can also include the data for the table in a succinct SQL script which can be used from any language. This script will require administrative permissions to add the table in a data dictionary.

The SQL generation option includes support for the latest data types and properties. The Delphi and C++ Builder generation may not include support for all of the data types used in your table. Therefore you may need to modify the generated code to match your table specification. The utility will display a warning message if your table contains datatypes which the code generator does not support.

Summary

The Export Tables as Code utility is a powerful tool included in Advantage Data Architect. It provides the ability to generate Delphi, C++ Builder or SQL scripts. It can be used on both dictionary bound tables and free tables. The SQL option generates a script which can be used from any language and can populate table data as well.