Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 



Storing and Updating Columns

Description

In a BLOB/CLOB type column, each BLOB/CLOB type value is stored, and if binary or character string data is input, you must explicitly change the types by using each BIT_TO_BLOB( )/CHAR_TO_CLOB( ) function.

If a value is input in a LOB column by using an INSERT statement, a file is created in an external storage internally and the relevant data is stored; the relevant file path (Locator) is stored in an actual column value.

If a record containing a LOB column uses a DELETE statement, a file to which the relevant LOB column refers will be deleted simultaneously. If a LOB column value is changed using an UPDATE statement, the column value will be changed following the operation below, according to whether a new value is NULL or not.

  • If a LOB type column value is changed to a value that is not NULL : If a Locator that refers to an external file is already available in a LOB column, the relevant file will be deleted. A new file is created afterwards. After storing a value that is not NULL, a Locator for a new file will be stored in a LOB column value.
  • If changing a LOB type column value to NULL : If a Locator that refers to an external file is already available in a LOB column, the relevant file will be deleted. And then NULL is stored in a LOB column value.
Example

-- inserting data after explicit type conversion into CLOB type column

INSERT INTO doc_t (doc_id, content) VALUES ('doc-1', CHAR_TO_CLOB('This is a Dog'));

INSERT INTO doc_t (doc_id, content) VALUES ('doc-2', CHAR_TO_CLOB('This is a Cat'));

 

-- inserting data after explicit type conversion into BLOB type column

INSERT INTO image_t VALUES ('image-0', 'doc-0', BIT_TO_BLOB(X'000001'));

INSERT INTO image_t VALUES ('image-1', 'doc-1', BIT_TO_BLOB(X'000010'));

INSERT INTO image_t VALUES ('image-2', 'doc-2', BIT_TO_BLOB(X'000100'));

 

-- inserting data from a sub-query result

INSERT INTO image_t SELECT 'image-1010', 'doc-1010', image FROM image_t WHERE image_id = 'image-0';

 

-- updating CLOB column value to NULL

UPDATE doc_t SET content = NULL WHERE doc_id = 'doc-1';

 

-- updating CLOB column value

UPDATE doc_t SET content = CHAR_TO_CLOB('This is a Dog') WHERE doc_id = 'doc-1';

 

-- updating BLOB column value

UPDATE image_t SET image = (SELECT image FROM image_t WHERE image_id = 'image-0') WHERE image_id = 'image-1';

 

-- deleting BLOB column value and its referencing files

DELETE FROM image_t WHERE image_id = 'image-1010';