GBase 8s Large Object Handling: Simplifying Large Data Insertion and Management

The ability of a database to handle large objects (LOBs) directly impacts the efficiency and convenience of data storage. GBase 8s offers comprehensive support for both simple and smart large objects. This article explores the storage methods and insertion techniques for large objects in GBase 8s, helping users manage large-scale text and binary data more efficiently.

1. Storage of Simple Large Objects

TEXT

Stores all types of textual data. It can contain both single-byte and multi-byte characters supported by the locale.

BYTE

Stores any type of binary data as an undifferentiated byte stream. Binary data typically consists of digitized information such as spreadsheets, program load modules, and digitized sound patterns, etc.

The database server directly loads simple large objects (BYTE and TEXT columns) from a LOAD FROM file. The syntax diagram for LOAD is shown below:

When loading BYTE and TEXT data, remember the following restrictions:

• You cannot have leading and trailing spaces in the BYTE field.
• Use the backslash \ to escape the special meanings of precise delimiters and backslash characters in the TEXT field.
• Data loaded into BYTE columns must be in ASCII hexadecimal format. BYTE columns cannot contain leading spaces.
• Do not use the following characters as delimiters in LOAD FROM files: digits (0 to 9), letters a to f and A to F, backslash () characters, or NEWLINE (CTRL-J) characters.

Example:

create table test(id int, col1 text, col2 byte);

vi data.txt
1|1234abc|01010101af|
2|1234abc|01010101af|
3|1234abc|01010101af|

load from './data.txt' delimiter "|" insert into test;

> create table test(id int,col1 text,col2 byte);
Table created.

> load from './data.txt' delimiter "|" insert into test;
3 row(s) loaded.

> select * from test;
id   1
col1 
1234abc
col2 <BYTE value>

id   2
col1 
1234abc
col2 <BYTE value>

id   3
col1 
1234abc
col2 <BYTE value>

3 row(s) retrieved.

2. Storage of Smart Large Objects

BLOB

• Stores any type of binary data as randomly accessible chunks. Binary data typically includes saved spreadsheets, program load modules, and digitized sound patterns, etc.
• BLOB columns can be up to 2^42 bytes long, though system resources may impose lower actual limits. The minimum disk space allocation for smart large object data types is 512 bytes.

CLOB

• CLOB data types store any type of textual data as randomly accessible chunks. If this information is also textual (e.g., PostScript, HTML, SGML, XML data), then textual data includes text formatting information.

GBase 8s stores smart large objects in smart large object spaces. A smart large object space is a logical storage area containing one or more chunks that store only BLOB and CLOB data.

Example

Table Structure:

create table test(id int, col1 blob, col2 clob);

GBase Mode

Use the filetoblob, filetoclob functions to insert data.

When using dbaccess, it defaults to GBase mode, but you can also set it with set environment sqlmode 'gbase';.

vi blob.txt
010101001

vi clob.txt
abcdefg

insert into test values(1, filetoblob('blob.txt', 'client'), filetoclob('clob.txt', 'client'));

> create table test(id int, col1 blob, col2 clob);
Table created.

> insert into test values(1, filetoblob('blob.txt','client'), filetoclob('clob.txt', 'client'));
1 row(s) inserted.

> select * from test;
id   1
col1 <SBlob Data>
col2 
abcdefg
1 row(s) retrieved.

Oracle Mode

No need to use functions, insert directly:

set environment sqlmode 'oracle';
insert into test values(1, 'abc', 'abc');

> set environment sqlmode 'oracle';
Environment set.

> create table test(id int, col1 blob, col2 clob);
Table created.

> insert into test values(1, 'abc', 'abc');
1 row(s) inserted.

> select * from test;
ID   1
COL1  <SBlob Data>
COL2 
abc
1 row(s) retrieved.

Note: The database version used in the above example: GBase8sV8.8_3.5.1.

GBase 8s large object handling features provide users with powerful tools for data storage and management. Through the detailed introduction in this article, users should gain a deeper understanding of GBase 8s's capabilities in handling large-scale text and binary data. We hope this article helps users utilize GBase 8s more efficiently, achieving optimized data management and innovative applications.