Changing Field Data Types - Synergex/SqlReplication GitHub Wiki

It is possible to change the SQL data type used to expose fields. The main use case envisaged is to transform decimal fields into implied decimal fields by adding a number of decimal places in the database data, but there may be other use cases. For example the same mechanism could be used to transforming A1 Y/N fields into Boolean true/false fields in the database, and more.

To transform a field data type a developer must add various special tokens to the repository field definition, and specifically into the fields long description.

Let's take an example of a field that represents the price of a product that is stored in whole cents, in the actual application it is stored as a D6 value, which by default will be represented in the database as a SQL DECIMAL(6) value. When we replicate the data to the database our goal is to transform the value into a dollars and cents value, essentially a D6.2 that will be represented in the database as a DECIMAL(6,2). Here's how to achieve that:

In the repository, add the following information to the fields long description:

CUSTOM_DBL_TYPE=D6.2;
CUSTOM_SQL_TYPE=DECIMAL(6,2);
CUSTOM_CONVERT_FUNCTION=DivideBy100;
CUSTOM_STRING_FUNCTION=DivideBy100ToString;

The CUSTOM_DBL_TYPE value causes the CodeGen templates to use a temporary field of the type specified instead of using the actual original field.

The CUSTOM_SQL_TYPE value overrides the SQL data type that will be used to define the database column associated with the field.

The CUSTOM_CONVERT_FUNCTION value names a Synergy function that can be used to convert the original value to the new value. The function should accept a single parameter which is of the actual data type of the real field, and have a return value which is the custom data type as defined by CUSTOM_DBL_TYPE. Here's an example function that turns a decimal value into an implied decimal value with two decimal places:

function DivideBy100, d.
    required in decimalValue, n
proc
    freturn decimalValue / 100.0
endfunction

The CUSTOM_STRING_FUNCTION value names a Synergy function that can be used to convert the original value to a string representation of the new value; It is used when outputting a CSV file to support the bulk upload function. The function should accept a single parameter which is of the actual data type of the real field, and have a return value which is a string representation of the custom data type as defined by CUSTOM_DBL_TYPE. Here's an example function that turns a decimal value into a string representation of an implied decimal value with two decimal places:

function DivideBy100ToString, string
    required in decimalValue, n
proc
    freturn %string(decimalValue/100.0)
endfunction

With these changes in place the standard CodeGen templates should produce the desired result.

Note that in order to use this feature you must be using CodeGen 5.4.8 (or later).