A Truncation Error Encountered when Importing Data into Table

Products

• Pivotal GemFire XD

Issue/Introduction

Symptoms:

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)

 

Environment

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"].

Resolution

Solution

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