SQL Script Wizard utility template released
An utility template that writes SQL scripts for creation of tables, indexes, foreign key constraints, check constraints, and/or trigger procedures from the information on your dictionary.
SQL Script Wizard
An important part of SQL development is writing the SQL scripts to create all the parts of your database. This includes tables, constraints of various kinds, and triggers. There are a number of options available to the Clarion programmer. We have found however in our own SQL development that the available methods are many times more complicated than what is needed, and sometimes they are a bit restrictive.
With this in mind we decided to develop the SQL Script Wizard extension template. With this extension template you can quickly and easily create SQL scripts to automate your SQL database creation.
Generate SQL scripts to create tables in your database. This includes choosing the most appropiate field type depending on the field type you used in your dictionary and the backend you want to use. The table creation script also includes creation of primary keys, null constraints, and if the database allows it, autoincrement constraints when the key only has one autoincrement field.
Generate SQL scripts to create indexes in your database. The utility template also generates a script to create an index in your SQL backend for each index in your dictionary.
SQL script to generate foreign key contraints. You can easily create any or all of the foreign key constraints you defined in your Clarion dictionary.
SQL script to add check constraints to your database. This allows you to add some of the validity constraints you specify in your Clarion dictionary.
SQL script to add triggers to your database. With this you can add update and delete triggers to your backend to handle referential integrity constraints. For some backends referential integrity may be handled using foreign key constraints (e.g. Sybase Anywhere), but for some others it is best to handle it using triggers.
SQL script to generate update triggers to modify fields not included in the relations between to files.
Generation of SQL scripts is quite flexible in that you can select which scripts to generate, and for which files. Also, you can specify a different backend than the one given in the dictionary.
We spent many days mapping clarion data types to SQL backend data types. However, should you need or prefer to use a different data type mapping, this is configurable via a configuration file. You may also configure it on a field by field basis on the Clarion dictionary's user options.
The following backends are currently supported and tested:
And the following backends are supported, but not fully tested:
The link below downloads a ZIP file containing the utility template, documentation, a sample dictionary, and sample scripts based on that dictionary.