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 macro commands for a developer wishing to write their own restructuring code:

  • INIT_INT_SQL_SCRIPT

  • DEINIT_INT_SQL_SCRIPT

  • GET_INT_SQL_SCRIPT_SQL_CHUNK

  • GET_INT_SQL_SCRIPT_INT_CHUNK

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 iOffset iLen
    String sScriptLine

    MERTECH_WARNING_MESSAGE DISABLED

    // Load the DEF file, the SQL_SCRIPT
    Move 0 to iFile
    INIT_INT_SQL_SCRIPT
        Structure_Start iFile sMainDriver
            Load_Def 'salesp.def' Onto iFile
        Structure_End iFile
    DEINIT_INT_SQL_SCRIPT

    // Save the SQL script
    Move 0 to iOffset
    Direct_Output “salesp.sql”
        Repeat
            GET_INT_SQL_SCRIPT_SQL_CHUNK OFFSET iOffset to sScriptLine iLen
            If (iLen > 0) Begin
                Write sScriptLine
                Move (iOffset + iLen) to iOffset
            End
        Until (iLen = 0)
    Close_Output

    // Save the INT file
    Move 0 to iOffset
    Direct_Output “salesp.int”
        Repeat
            GET_INT_SQL_SCRIPT_INT_CHUNK OFFSET iOffset to sScriptLine iLen
            If (iLen > 0) Begin
                Write sScriptLine
                Move (iOffset + iLen) to iOffset
            End
        Until (iLen = 0)
    Close_Output
End_Procedure

See the Command Reference documenation for the INIT_INT_SQL_SCRIPT, DEINIT_INT_SQL_SCRIPT, GET_INT_SQL_SCRIPT_SQL_CHUNK commands for more information.