Converting SQL tables

Table conversion actions for SQL tables are typically performed using pass-through SQL. The appropriate SQL commands are issued by your application, and the work is performed on the server. Refer to Pass-through SQL for more information about using pass-through SQL from within your application.

Performing conversions on SQL tables can be complex. This documentation describes the basic steps necessary for the common table conversions. For specific details about SQL table conversions, or for more complex table conversions, consult Dexterity technical support. The remainder of this section describes how the common table conversions are performed for SQL tables.

Adding fields

To add a field to a table, add the field to the table definition in Dexterity. Use the ALTER TABLE statement in pass-through SQL to add the new column to the table. Be sure the name matches the physical name for the field you added to the table definition in Dexterity.

Use the Table_DropProcedures() function to remove the existing stored procedures for the table, and then use the Table_CreateProcedures() function to re-create them. You will need to grant access to the new auto-generated stored procedures. Refer to Granting access to tables for information about granting access to tables and their auto-generated stored procedures.

Removing fields

To remove a field from a table, remove the field from the table definition in Dexterity. Use the ALTER TABLE statement in pass-through SQL to remove the column from the table.

Use the Table_DropProcedures() function to remove the existing stored procedures for the table, and then use the Table_CreateProcedures() function to re-create them. You will need to grant access to the new auto-generated stored procedures. Refer to Granting access to tables for information about granting access to tables and their auto-generated stored procedures.

Adding, changing, or removing keys

If you add, change, or remove keys for a Dexterity table, you must re-create the auto-generated stored procedures for that table. With the previous version of the table definition, use the Table_DropProcedures() function to remove the existing stored procedures. Then use the Table_CreateProcedures() function with the updated table definition to create new auto-generated stored procedures for the table. You will need to grant access to the new auto-generated stored procedures. Refer to Granting access to tables for information about granting access to tables and their auto-generated stored procedures.

You should also use pass-through SQL to drop the indexes for the table whose keys you are changing. Then use the Table_CreateIndexes() function to re-create the indexes for the table.

Changing a field’s storage size

To change a field’s storage size, change the data type associated with the field whose size is changing. Using pass-through SQL, execute the sp_rename command to rename the existing table to some appropriate placeholder name. Create the new table that contains the field with the new storage size. Use pass-through SQL to bulk copy the data from the previous table to the new table. Once the copy is complete, you can delete the previous table. You will need to grant access to the new table. Refer to Granting access to tables for information about granting access to tables.


Documentation Feedback