Data Type Conversion

The type of a column in the Source Database is not always the type of the column in the Target Database: a date that the Source Database holds as text and the Target Database as a datetime, a bit and a boolean, a long string and a short column, a float and an integer. This page says what Repstance does with such a value and how to convert it with a rule of the Apply Process.

Who converts the value

Repstance passes the values that it reads from the trail files to the Target Database as text. It writes a date in the layout that the Target type needs and a bit as 1 and 0 or as true and false, but it does not change a value from one kind of type to another: it does not read a string as a date, a number as a boolean, shorten a string or round a number. The Target Database does it by its own rules of implicit conversion, or refuses the value with its own message. The message is in the status of the Apply Process as ERROR = [...] SQL = [...] LSN = [...], and SQL shows the table and the columns of the statement. The rows that a SQL Server Target Database gets in bulk (the Initial Load, and the runs of rows that reach the parameter bulkthreshold) are the exception: the driver of Repstance converts them and is stricter than SQL Server, see the notes below.

When the conversion of the Target Database is not the one that you need, change the value in the statement with the attribute DATA of a rule of the Apply Process. The text of DATA is an expression of the Target Database, and Repstance puts it into the statements that it writes.

How to write the rule

A rule is the parameter map of the Apply Process. The left side names the column of the Source Database, the right side names the column of the Target Database and has the attribute DATA. In DATA the character % stands for the value of the column, and C:n for the value of the n-th column of the table (see Objects Mapping and Possible Transforms). The names on the right side are used as they are written, so write them as the Target Database has them, in capital letters for Oracle. This rule converts the text of the column order_date, written as day/month/year, to the datetime of the Target column of a SQL Server Target Database:

• The whole argument is between double quotes, and the double quotes inside it are written as \". The single quotes of SQL are kept inside the double quotes. Spaces are allowed in DATA, but not after the semicolon between two attributes.
• Repstance does not check the SQL of DATA. The Target Database reads it when it writes the first row of the table, and its error is in the status of the Apply Process. Test a rule on a copy of the table first. With preview=1 in the command alter the tables that the rule applies to are listed and nothing is saved; a table that is not listed does not match the rule.
• The Apply Process reads its rules when it is run: stop it, change the rule and run it again. A transaction that failed is applied again with the new rule.
• Put the rule in the Apply Process. A rule in the Capture Process is written into the trail files, and a transaction that is captured already keeps the old value.
• A table that has a rule is written to SQL Server through a staging table, so the Initial Load and the bulk rows of such a table are converted by SQL Server itself.

A date that is held as text

The Source column is a string, for example 31/12/2026 14:30:00, and the Target column is a date or a datetime. A text in the form 2026-12-31 14:30:00 is usually converted by the Target Database without a rule (for SQL Server see the note below). For another layout the Target Database refuses the value, for example Conversion failed when converting date and/or time from character string (SQL Server), ORA-01861: literal does not match format string (Oracle), invalid input syntax for type timestamp (PostgreSQL) or Incorrect datetime value (MySQL). Convert the text with a function of the Target Database and the layout of your text:

The format of STR_TO_DATE has the % character, and Repstance replaces every % in DATA by the value of the column, so the format for MySQL is built with CHAR(37), the percent sign. For Oracle do not set a date type with TYPE on the right side of the rule: Repstance would put the value into its own TO_DATE or TO_TIMESTAMP with a fixed layout.

SQL Server: the bulk rows are converted by the driver of Repstance, which takes a text as a date only in the forms YYYY-MM-DD, YYYY-MM-DD hh:mm:ss.fffffffff followed by Z or a time zone offset (+hh:mm), and YYYY-MM-DDThh:mm:ssZ (RFC 3339). Another text, also 2026-12-31 14:30:00 without a time zone, stops the Apply Process with bulkcopy: bulk: unable to convert string to date: parsing time .... A rule for the column makes Repstance write the table through a staging table, and then SQL Server converts the text, with the style that you give.

A bit and a boolean

Repstance writes a SQL Server bit as 1 or 0 to the Target Databases other than SQL Server, and as true or false to SQL Server, where a bit column takes only these. Oracle and MySQL convert 1 and 0 into a NUMBER or a tinyint column by themselves. Two cases need a rule.

A number into a SQL Server bit. The Source column is a number, for example Oracle NUMBER(1,0), and the Target column is a bit. Without a rule the Apply Process stops with bulkcopy: mssql: invalid type for bit column: int64 1. Tell Repstance that the Target column is a bit. The mask of the type on the left side is compared with the full name of the Source type, with precision and scale:

A bit or a number into a PostgreSQL boolean. Repstance declares the value with the type of the Source column (bit for a SQL Server bit, smallint for NUMBER(1,0)) and creates a column of the type bit for a SQL Server bit. PostgreSQL has no conversion from bit or from a number to boolean, and the message has the form column "is_paid" is of type boolean but expression is of type .... Convert the value in DATA:

A string that is too long for the Target column

Repstance does not shorten a string, and the Target Database refuses a value that does not fit: String or binary data would be truncated (SQL Server), ORA-12899: value too large for column (Oracle), value too long for type character varying(50) (PostgreSQL) or Data too long for column (MySQL). The best remedy is a wider Target column. When the long values must be cut, cut them in DATA, here to 50 characters:

The cut value differs from the Source value, so Data Comparison reports the row as different. Give the compare task the same cut with its parameter maptypes, for example maptypes=substr(%VARCHAR%,1,50) (the function has to be valid in both databases, see Create Compare Task). A Redshift Target Database is the exception: the Initial Load and the bulk rows are loaded with the COPY option TRUNCATECOLUMNS, and Redshift cuts a string that is too long by itself.

A float or a decimal in an integer column

Most Target Databases convert a number with a fraction when they write it into an integer column: they round it or cut the fraction by their own rules. A SQL Server Target Database does it for single statements, but the bulk rows (the Initial Load, and the runs of rows that reach bulkthreshold) stop the Apply Process with bulkcopy: bulk: unable to convert string to int: strconv.ParseInt: parsing "12.5": invalid syntax, and a value that is too big for the column with bulkcopy: mssql: value 5000000000 out of range for int column (-2147483648 to 2147483647). Decide the rounding yourself in DATA:

Other conversions

DATA takes any expression of the Target Database: UPPER(LTRIM(RTRIM(%))) trims a string and changes its case on SQL Server, C:2+C:3 joins the values of two columns on SQL Server, a text in single quotes is a constant. The same attribute puts a value into a column that the Source table does not have (see Objects Mapping and Possible Transforms).

The type of a table that Repstance creates

When Repstance creates a Target table (the Initial Load with the load option C or R, a replicated CREATE TABLE) it chooses the type of every column from its own table of types. A rule can choose another type with TYPE on the right side. This rule makes a varchar(64) of every UNIQUEIDENTIFIER column of the tables of the schema public:

A TYPE on the right side changes the column that Repstance creates and the way it writes the value. It does not change a table that exists already: for that table change the column in the Target Database.