How Is The Best Way to Update Customer's DataBase Structures?

Currently you can update your database through:

1. Flex2SQL
2. dbBuilder
3. DataFlex restructure commands
4. Directly on the SQL Server and manually changing INT files

It is common to have a DBA that wants to review all schema changes to the database, which makes options 1-3 difficult, or outright prohibited by the DBA. Option 4 is manual and prone to errors.

For this Mertech introduces new macro commands in version 11 of their drivers that allows you to use DataFlex restructure commands to generate your new INT and SQL scripts, without committing them to the server.

The new commands are ENABLE_SCRIPTING and DISABLE_SCRIPTING, which wrap around your restructuring code.

After going through the restructuring code (that will not modify your underlying database or INT files), the new INT and SQL scripts may be retrieved using GET_INT_CHUNK and GET_SQL_SCRIPT_CHUNK macro commands.

Mertech's Flex2SQL Migration tool also has new options that will allow you to do the restructuring graphically, and get copies of the INT and SQL scripts, again without actually committing the changes.

This makes it a great option for safely generating your database changes and a transparent way to propagate your database changes to your customer installation base.

Example

 Use Mertech.inc

Procedure CreateINTAndSQLFiles
Local String sDriverRevision

Login 'MertechData' 'Mertech' 'Mertech' 'SQL_DRV'

Set_Database_Name to "framework"
Sql_Use_Database "framework"

MERTECH_WARNING_MESSAGE DISABLED

Send RestructureCustomerTable
Send OutputSQLCustomerTable
Send OutputINTCustomerTable

Send CreateTable
Send OutputSQLSalespTable
Send OutputINTSalespTable
End_Procedure

Procedure RestructureCustomerTable
Local Integer iFile iField

Showln 'Restructuring CUSTOMER Table'

open "customer.int" as customer
move customer.file_number to iFile

ENABLE_SCRIPTING
Structure_Start iFile 'SQL_DRV'
Move 0 to iField
Create_Field iFile At iField
Set_Attribute DF_FIELD_NAME Of iFile iField To "max_limit"
Set_Attribute DF_FIELD_TYPE Of iFile iField To DF_BCD
Set_Attribute DF_FIELD_LENGTH Of iFile iField To 10
Set_Attribute DF_FIELD_PRECISION Of iFile iField To 2
Set_Attribute DF_FIELD_NULL Of iFile iField To 1
Structure_End iFile
DISABLE_SCRIPTING
End_Procedure

Procedure OutputSQLCustomerTable
Local Integer iOffset iLen
Local String sScriptLine

Move 0 to iOffset

Direct_Output "customer.sql"

Repeat
GET_SQL_SCRIPT_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

Procedure OutputINTCustomerTable
Local Integer iOffset iLen
Local String sScriptLine

Move 0 to iOffset

Direct_Output "customer.int"

Repeat
GET_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

Procedure CreateSalespTable
Local Integer iFile

Move 0 to iFile

ENABLE_SCRIPTING
Structure_Start iFile 'SQL_DRV'
Load_Def 'salesp.def' Onto iFile
Structure_End iFile
DISABLE_SCRIPTING
End_Procedure

Procedure OutputSQLSalespTable
Local Integer iOffset iLen
Local String sScriptLine

Move 0 to iOffset

Direct_Output "salesp.sql"

Repeat
GET_SQL_SCRIPT_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

Procedure OutputINTSalespTable
Local Integer iOffset iLen
Local String sScriptLine

Move 0 to iOffset

Direct_Output "salesp.int"

Repeat
GET_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

Send CreateINTAndSQLFiles

Showln
Showln 'Done ...'
Inkey Pageend

Any restructuring command are supported by this new feature, including creating completely new tables.