Exporting Fixed‑Length Data Files in GBase 8a

Fixed‑length (or "positional") exports eliminate the need for field delimiters, which is especially useful when your data contains characters that are hard to escape. In a gbase database, you can export query results as fixed‑width records using the SELECT INTO OUTFILE command with specific options.

Syntax for Fixed‑Length Exports

There are three ways to produce a fixed‑length file:

1. Explicit field lengths – SELECT ... INTO OUTFILE '...' FIELDS length(len1, len2, ...)
2. Empty terminators and enclosures – SELECT ... INTO OUTFILE '...' FIELDS TERMINATED BY '' ENCLOSED BY '' ESCAPED BY ''
3. Empty terminators only – SELECT ... INTO OUTFILE '...' FIELDS TERMINATED BY '' ESCAPED BY ''

Important: You cannot mix the length option with TERMINATED BY or ENCLOSED BY – doing so will cause a syntax error.

Method 1: Explicit Field Lengths

Use FIELDS length() to define the exact byte width for each column. Each data type has a minimum width that must be respected.

Data Type	Minimum Length (bytes)
TINYINT	4
SMALLINT	6
INT	11
BIGINT	21
DECIMAL(P,S)	P + 2 (if S > 0) or P + 1 (if S = 0)
VARCHAR(N) (UTF8)	N × 3
VARCHAR(N) (UTF8MB4)	N × 4

Example:

-- Export table t1, where id is INT and f1 is VARCHAR(10) in UTF8
SELECT * FROM t1 INTO OUTFILE '/home/gbase/t1.zxq'
FIELDS length '11,30' WRITEMODE BY OVERWRITES;

If the specified length is smaller than the actual byte size of the data, the content will be truncated. For instance, a VARCHAR(10) column in UTF8 needs at least 30 bytes; providing only 2 bytes would output just the first 2 bytes.

Method 2 & 3: Empty Delimiters and Escape Characters

If you don't want to calculate lengths manually, simply set the field terminator, enclosure, and escape character to empty strings. The engine then outputs each column at its maximum defined width automatically.

-- Option A: Clear all three
SELECT * FROM t1 INTO OUTFILE '/home/gbase/t1.zxq'
FIELDS TERMINATED BY '' ENCLOSED BY '' ESCAPED BY ''
WRITEMODE BY OVERWRITES;

-- Option B: Clear only terminator and escape
SELECT * FROM t1 INTO OUTFILE '/home/gbase/t1.zxq'
FIELDS TERMINATED BY '' ESCAPED BY ''
WRITEMODE BY OVERWRITES;

Both options produce fixed‑length output and are functionally identical. They are more convenient than specifying explicit lengths, but they don't allow you to customise column widths.

Fixed‑length exports are a reliable way to handle complex data exchange scenarios. Choose the method that best balances flexibility and simplicity for your specific gbase database workload.