Skip to content

Migrating Existing Databases to New Types

After upgrading from CK5 to CK6, you have a choice whether you want to use the new data types in your existing databases.

Note: You don’t have to change the data types. The Connectivity Kit is fully backward compatible and can keep using the older data types.

If you do want to change any types, you can change them by setting the DF_FIELD_NATIVE_TYPE attribute.

Example: Convert Native Type for Various SQL Server Types

The following code shows an example of how to convert the native type for various SQL Server types:

Procedure ConvertNativeTypeExample
    Integer iNumColumns iColumn iDFType iNativeType
    Handle  hTable
    String  sColumnName sNativeTypeName

    Open OrderHea
    Move OrderHea.File_Number to hTable
    Structure_Start hTable
    Get_Attribute DF_FILE_NUMBER_FIELDS of hTable to iNumColumns

    For iColumn from 1 to iNumColumns
        Get_Attribute DF_FIELD_NAME of hTable iColumn to sColumnName
        Get_Attribute DF_FIELD_TYPE of hTable iColumn to iDFType
        Get_Attribute DF_FIELD_NATIVE_TYPE of hTable iColumn to iNativeType
        Get_Attribute DF_FIELD_NATIVE_TYPE_Name of hTable iColumn to sNativeTypeName

        If (iDFType = DF_DATE) Begin
            If (iNativeType = SQL_TYPE_TIMESTAMP) Begin
                // Convert datetime to date
                Set_Attribute DF_FIELD_NATIVE_TYPE of hTable iColumn to SQL_TYPE_DATE
            End
        End

        If (iDFType = DF_DATETIME) Begin
            If (iNativeType = SQL_TYPE_TIMESTAMP) Begin
                // Convert datetime to datetime2
                Set_Attribute DF_FIELD_NATIVE_TYPE of hTable iColumn to SQL_TYPE_TIMESTAMP2
            End
        End

        If (iDFType = DF_ASCII) Begin
            If (iNativeType = SQL_CHAR) Begin
                // Convert char to varchar
                Set_Attribute DF_FIELD_NATIVE_TYPE of hTable iColumn to SQL_VARCHAR
                // Convert char to nchar
                // Set_Attribute DF_FIELD_NATIVE_TYPE of hTable iColumn to SQL_WCHAR
                // Convert char to nvarchar
                // Set_Attribute DF_FIELD_NATIVE_TYPE of hTable iColumn to SQL_WVARCHAR
            End
        End

        If (iDFType = DF_TEXT) Begin
            If (iNativeType = SQL_LONGVARCHAR) Begin
                // Convert text to varchar(max)
                Set_Attribute DF_FIELD_NATIVE_TYPE of hTable iColumn to SQL_VARCHAR
                // Convert text to nvarchar(max)
                // Set_Attribute DF_FIELD_NATIVE_TYPE of hTable iColumn to SQL_WVARCHAR
            End
        End

        If (iDFType = DF_BINARY) Begin
            If (iNativeType = SQL_LONGVARBINARY) Begin
                // Convert image to varbinary(max)
                Set_Attribute DF_FIELD_NATIVE_TYPE of hTable iColumn to SQL_VARBINARY
                // Convert image to binary
                // Set_Attribute DF_FIELD_NATIVE_TYPE of hTable iColumn to SQL_BINARY
            End
        End
    Loop

    Structure_End hTable
End_Procedure

See Also