Deploying Database Updates

A common scenario is for a developer to make changes to the database on his own machine and commit the changes to the SQL Server in the development environment. The problem is how to deploy the changes to customer sites. The developer must send the following information to a customer site before updates can go live:

  • His new application

  • Updated INT file(s) and SQL script(s), or procedures for restructuring the database

The Flex2SQL Classic Migration Utility saves restructure code in the Restructure Table > Results > Source Code tab. So, one option is for the developer to create a program that includes this restructuring code, test the code, and then run the program at each customer site.

Alternatively, in the Restructure Table > Results > SQL Script tab, you can create an SQL script containing the required database modifications. Now the developer can ship his new application and INT file(s) along with the SQL script to the customer site. The customer’s DBA (or whomever manages the customer database) can review the SQL script, and then run the script on the live server.

Also in the Restructure Table > Results tab is the option to directly save the INT file for the updated table. The new INT file can be copied directly from the saved location.

Note

The INT file can also be copied from the development system once the changes are committed and the new INT file is generated.

Macro Commands to Create Restructuring Code

The SQL update script and new INT file can also be created programmatically. Mertech provides the following for a developer wishing to write their own restructuring code:

As an example, this sample reads a DEF file, and uses it to save the corresponding SQL script and INT files, without making any actual changes to the SQL database.

Procedure Generate
    Integer iFile
    uChar[] ucInt ucScript

    Set_Attribute f2s_restructure_warning_state of _f2s_ActiveDriverId to False

    // Load the DEF file, the SQL_SCRIPT
    Move 0 to iFile
    Set_Attribute f2s_scripting_mode of _f2s_ActiveDriverId to True
        Structure_Start iFile sMainDriver
            Load_Def 'salesp.def' Onto iFile
        Structure_End iFile
    Set_Attribute f2s_scripting_mode of _f2s_ActiveDriverId to False

    Set_Attribute f2s_restructure_warning_state of _f2s_ActiveDriverId to True

    // Save the SQL script
    Direct_Output “salesp.sql”
        f2s_get_sql_script ucScript
        write ucScript
    Close_Output

    // Save the INT file
    Direct_Output “salesp.int”
        f2s_get_int ucInt
        write ucInt
    Close_Output
End_Procedure