Pivotal Knowledge Base

Follow

A Truncation Error Encountered when Importing Data into Table

Applies to

SQLFIre 1.0.x to 1.1.x

Purpose

This document describes one method to resolve an exception due to truncation error when importing data into table by SYSCS_UTIL.IMPORT_TABLE procedure.

Symptom

The import data procedure fails with the following exception when trying to import data in UTF8-BOM format (Byte Order Mark) of the form ["000","sample"]:

SQLF>Create Table APP.SAMPLE_TABLE
>(
>SAMPLE_ID varchar(3),
>SAMPLE_NAME varchar(6)
>);
0 rows inserted/updated/deleted
SQLF>CALL SYSCS_UTIL.IMPORT_TABLE (
> 'APP',
> 'SAMPLE_TABLE',
> '/home/user1/apps/data/SAMPLE_TABLE.data',
> ',',
> '"',
> 'UTF8',
> 1
>);
ERROR XIE0R: SQLSTATE=XIE0R,SEVERITY=-1: (Server=jackycentos64b[1548],Thread[DRDAConnThread_16,5,sqlfire.daemons])
Import error on line 1 of file /home/user1/apps/data/SAMPLE_TABLE.data:
A truncation error was encountered trying to shrink VARCHAR '"000"' to length 3.
Caused by: SqlException: SQLSTATE=XIE0R,SEVERITY=-1: (Server=jackycentos64b[1548],Thread[DRDAConnThread_16,5,sqlfire.daemons])
Import error on line 1 of file/home/user1/apps/data/SAMPLE_TABLE.data: A truncation error was encountered trying to shrink VARCHAR '"000"' to length 3.
at com.vmware.sqlfire.internal.client.am.Statement.completeExecute(Statement.java:1727)

Root Cause

Since the data file is in UTF8-BOM format, there are bytes that represent the Unicode code point U+FEFF BYTE ORDER MARK at the start of the file that cause the import data procedure to incorrectly count the length of the first field ["000"].

Solution

Put the data in unicode format without BOM, or replace the first double quotation ["] with a correct double quotation without "FFFE" bytes.

Comments

Powered by Zendesk