In the DataSync Agent, you can truncate string values if you have exceeded a column's max length (UTF-8). If you are using UTF-8 characters and prefer using VARCHAR or a string data type where the specified length is strictly the number of bytes, you will be able to truncate the string value as well.
NOTE: UTF-8 characters may have multiple bytes per character.
If you use multiple UTF-8 characters and the number of bytes exceeds what is allowed in a column, an error will occur. You can prevent this by either adding <byte_padding/> or if you want to keep the original length and truncate the string values, add <truncate_utf8/> directive within your agent.xml configuration file.
If you use a different character encoding, you can instead use <database_truncate_encoding> and set your specified encoding.
NOTE: Currently, the <database_truncate_encoding> directive only allow these encoding values:
- ISO_8859_1
- US_ASCII
- UTF_16
- UTF_16BE
- UTF_16LE
- UTF_8
Prerequisites
First, you will need to set up one of the Perspectium DataSync Agents.
You should also stop running your DataSync Agent before making any Agent configuration changes.
Request the databases.xml file for your DataSync Agent by contacting Perspectium Support.
Procedure
To truncate string values for your DataSync integration, follow these steps:
Navigate to the directory where you saved your agent.xml file when installing your DataSync Agent.
Open your agent.xml file in a text editing application. Then, locate the <agent> directive(s) and add the <truncate_utf8/> directive or add <database_truncate_encoding/> with the encoding value.
Save the changes you've made to your agent.xml and close the file. Your agent.xml should look similar to the example shown below:
<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
<agent>
<truncate_utf8/>
<!-- OR -->
<!-- <database_truncate_encoding>ISO_8859_1</database_truncate_encoding -->
<share/>
<subscribe>
<task instances="1">
<task_name>datasyncforservicenow_to_mysql_subscribe</task_name>
<message_connection password="encrypted:vlOtU71yu8N/EFIJH85SSBtaIt7qEEfvqiqft9VZyYE=" queue="psp.out.replicator.testqueue" user="admin">https://testperspectium.net</message_connection>
<instance_connection password="encrypted:vlOtU71yu8N/EFIJH85SSPN9aF0P5/YViVwPEVFcGW4=" user="test.user">https://myinstance.service-now.com</instance_connection>
<handler>com.perspectium.replicator.sql.SQLSubscriber</handler>
<decryption_key>The cow jumped over the moon</decryption_key>
<database_type>mysql</database_type>
<database_server>localhost</database_server>
<database_port>3306</database_port>
<database_user>root</database_user>
<database_password>encrypted:vlOtU71yu8N/EFIJH85SSMoilKLTeJHQrNZPJ7c5tFU=</database_password>
<database_parms>characterEncoding=UTF-8 & useSSL=false</database_parms>
<database_column_max_size>251</database_column_max_size>
<database>psp_repl</database>
<skip_alter/>
</task>
</subscribe>
<max_reads_per_connect>4000</max_reads_per_connect>
<polling_interval>5</polling_interval>
<skip_message_set_processing/>
</agent>
</config>