About Text BLOB Syntax

Go Up to Using BLOBs with VARCHAR Data

The general syntax for the SQL SELECT statement with a BLOB data type is:

SELECT CAST (<blob-column-name> as CHAR[<n>]) FROM <table-name>;

To make text blobs interchangeable with VARCHAR data, you can use the following SQL syntax:

INSERT INTO <table-name> values (<text values>, ….);
UPDATE <table_name> set <blob column name> = <text value>;

And:

SELECT CAST (<blob column name> as CHAR[128]) from table;
SELECT * from <table name> where cast (<blob column> as VARCHAR[10]) =
“SMISTRY”;

In addition, store procedures which accept a BLOB can now accept a text value as a parameter and implicitly be converted to a text blob. For example:

CREATE PROCEDURE MYTEST (AINT INTEGER, INBLOB BLOB)
AS
Declare variable var_blob blob;
begin
insert
var_blob

This procedure can now be called using the following syntax:

Execute procedure mytest (1, ‘hello world’);

You can use the SELECT CAST, UPDATE, and INSERT INTO statements with the InterBase Client APIs. In such cases, InterBase returns the values as C structures. Specifically, the returned XSQLVARS would be of the type SQLVARYING, with the length of the text followed by the text data.

The following example demonstrates the use of the new SQL syntax for text BLOBs.

Example:

/* Same syntax to create a table... */
/* Note all sub-types are supported; SUB_TYPE 1 forces conversion */
/* to the column’s character data type. */
CREATE TABLE BLOB_TEST (B_ID INT, BLOB_CL BLOB SUB_TYPE 1);
COMMIT;
/* New functionality for the INSERT statement...
*/
INSERT INTO BLOB_TEST VALUES (1, ‘Fellowship of the Ring’);
INSERT INTO BLOB_TEST VALUES (2, ‘The Two Towers’);
INSERT INTO BLOB_TEST VALUES (3, ‘Return of the Jedi’);
/* New syntax for UPDATE... */
UPDATE BLOB_TEST SET BLOB_CL=’Return of the King’ WHERE B_ID=3;
COMMIT;
/* New syntax for SELECT. The BLOB will be returned as a TEXT string. */
SELECT B_ID, CAST (BLOB_CL AS VARCHAR(25)) FROM BLOB_TEST;

Table 4.5 illustrates the result of these statements in ISQL:

Text BLOB Example Result
B_ID	BLOB_CL
1	Fellowship of the Ring
2	The Two Towers
3	Return of the King

Advance To:

Defining Arrays