docs.intersystems.com
Using Caché SQL
Storing and Using Stream Data (BLOBs and CLOBs)
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Search:    

InterSystems SQL supports the ability to store stream data as either BLOBs (Binary Large Objects) or CLOBs (Character Large Objects) within an Caché database. This chapter discusses the following topics:

Stream Fields and SQL
InterSystems SQL supports two kinds of stream fields:
BLOBs and CLOBs
InterSystems SQL supports the ability to store BLOBs (Binary Large Objects) and CLOBs (Character Large Objects) within the database as stream objects. BLOBs are used to store binary information, such as images, while CLOBs are used to store character information. BLOBs and CLOBs can store up to 4 Gigabytes of data (the limit imposed by the JDBC and ODBC specifications).
The operation of the BLOBs and CLOBs is identical in every respect except how they handle character encoding conversion (such as Unicode to multibyte) when accessed via an ODBC or JDBC client: the data in a BLOB is treated as binary data and is never converted to another encoding while the data in a CLOB is treated as character data and is converted as necessary.
If a binary stream file (BLOB) contains the single non-printing character $CHAR(0), it is considered to be an empty binary stream. It is equivalent to the "" empty binary stream value: it exists (is not null), but has a length of 0.
From the object point of view, BLOBs and CLOBs are represented as stream objects. For more information, see the chapter Working with Streams of Defining and Using Classes.
Defining Stream Data Fields
InterSystems SQL supports a variety of data type names for stream fields. These InterSystems data type names are synonyms that correspond to the following:
Some InterSystems stream data types allow you to specify a data precision value. This value is a no-op and has no effect on the permitted size of the stream data. It is provided to allow the user to document the anticipated size of future data.
For data type mappings of stream data types, refer to the Data Types reference page in InterSystems SQL Reference.
For how to define fields (properties) of a table (persistent class), refer to Defining a Table by Creating a Persistent Class and Defining a Table by Using DDL. When defining a stream property of a persistent class, you can optionally specify the LOCATION parameter; see Declaring Stream Properties in the Working with Streams chapter of Defining and Using Classes.
The following example defines a table containing two stream fields:
CREATE TABLE Sample.MyTable (
    Name VARCHAR(50) NOT NULL,
    Notes LONGVARCHAR,
    Photo LONGVARBINARY)
Stream Field Constraints
The definition of a stream field is subject to the following field data constraints:
A stream field can be defined as NOT NULL.
A stream field can take a DEFAULT value, an ON UPDATE value, or a COMPUTECODE value.
A stream field cannot be defined as UNIQUE, a primary key field, or an IdKey. Attempting to do so results in an SQLCODE -400 fatal error with a %msg such as the following: ERROR #5414: Invalid index attribute: Sample.MyTable::MYTABLEUNIQUE2::Notes, Stream property is not allowed in a unique/primary key/idkey index > ERROR #5030: An error occurred while compiling class 'Sample.MyTable'.
A stream field cannot be defined with a specified COLLATE value. Attempting to do so results in an SQLCODE -400 fatal error with a %msg such as the following: ERROR #5480: Property parameter not declared: Sample.MyTable:Photo:COLLATION > ERROR #5030: An error occurred while compiling class 'Sample.MyTable'.
Inserting Data into Stream Data Fields
There are two ways to INSERT data into stream fields:
String data that is inserted as a DEFAULT value or a computed value is stored in the format appropriate for the stream field.
Querying Stream Field Data
A query select-item that selects a stream field returns the fully formed OID (object ID) value of the stream object, as shown in the following example:
SELECT Name,Photo,Notes 
FROM Sample.MyTable WHERE Photo IS NOT NULL
 
An OID is a %List formatted data address such as the following: $lb("1","%Stream.GlobalCharacter","^Sample.MyTableS").
Note:
The OID for a stream field is not the same as the OID returned for a RowID or a reference field. The %OID function returns the OID for a RowID or a reference field; %OID cannot be used with a stream field. Attempting to use a stream field as an argument to %OID results in an SQLCODE -37 error.
Use of a stream field in the WHERE clause or HAVING clause of a query is highly restricted. You cannot use an equality condition or other relational operator (=, !=, <, >), or a Contains operator ( ] ) or Follows operator ( [ ) with a stream field. Attempting to use these operators with a stream field results in an SQLCODE -313 error. Refer to Predicate Conditions and Streams for valid predicates using a stream field.
Result Set Display
DISTINCT, GROUP BY, and ORDER BY
Every stream data field OID value is unique, even when the data itself contains duplicates. These SELECT clauses operate on the stream OID value, not the data value. Therefore, when applied to a stream field in a query:
Predicate Conditions and Streams
The IS [NOT] NULL predicate can be applied to the data value of a stream field, as shown in the following example:
SELECT Name,Notes 
FROM Sample.MyTable WHERE Notes IS NOT NULL
 
The BETWEEN, EXISTS, IN, %INLIST, LIKE, %MATCHES, and %PATTERN predicates can be applied to the OID value of the stream object, as shown in the following example:
SELECT Name,Notes 
FROM Sample.MyTable WHERE Notes %MATCHES '*1[0-9]*GlobalChar*'
 
Attempting to use any other predicate condition on a stream field results in an SQLCODE -313 error.
Aggregate Functions and Streams
The COUNT aggregate function takes a stream field and counts the rows containing non-null values for the field, as shown in the following example:
SELECT COUNT(Photo) AS PicRows,COUNT(Notes) AS NoteRows
FROM Sample.MyTable
 
However, COUNT(DISTINCT) is not supported for stream fields.
No other aggregate functions are supported for stream fields. Attempting to use a stream field with any other aggregate function results in an SQLCODE -37 error.
Scalar Functions and Streams
InterSystems SQL cannot apply any function to a stream field, except the %OBJECT, CHARACTER_LENGTH (or CHAR_LENGTH or DATALENGTH), SUBSTRING, CONVERT, XMLCONCAT, XMLELEMENT, XMLFOREST, and %INTERNAL functions. Attempting to use a stream field as an argument to any other SQL function results in an SQLCODE -37 error.
Stream Field Concurrency Locking
Caché protects stream data values from concurrent operations by another process by taking out a lock on the stream data.
Caché takes out an exclusive lock before performing a write operation. The exclusive lock is released immediately after the write operation completes.
Caché takes out a shared lock out when the first read operation occurs. A shared lock is only acquired if the stream is actually read, and is released immediately after the entire stream has been read from disk into the internal temporary input buffer.
Using Stream Fields within Caché Methods
You cannot use a BLOB or CLOB value using Embedded SQL or Dynamic SQL directly within a Caché method; instead you use SQL to find the stream identifier for a BLOB or CLOB and then create an instance of the %AbstractStream object to access the data.
Using Stream Fields from ODBC
The ODBC specification does not provide for any recognition or special handling for BLOB and CLOB fields. InterSystems SQL represents CLOB fields within ODBC as having type LONGVARCHAR (-1). BLOB fields are represented as having type LONGVARBINARY (-4). For ODBC/JDBC data type mappings of stream data types, refer to Integer Codes for Data Types in the Data Types reference page in InterSystems SQL Reference.
The ODBC driver/server uses a special protocol to access BLOB and CLOB fields. Typically you have to write special code within ODBC application to use CLOB and BLOB fields; the standard reporting tools typically do not support them.
Using Stream Fields from JDBC
Within a Java program you can retrieve or set data from a BLOB or CLOB using the standard JDBC BLOB and CLOB interfaces. For example:
    Statement st = conn.createStatement();
    ResultSet rs = st.executeQuery("SELECT MyCLOB,MyBLOB FROM MyTable");
    rs.next();      // fetch the Blob/Clob

    java.sql.Clob clob = rs.getClob(1);
    java.sql.Blob blob = rs.getBlob(2);

    // Length
    System.out.println("Clob length = " + clob.length());
    System.out.println("Blob length = " + blob.length());

    // ...
Note:
When finished with a BLOB or CLOB, you must explicitly call the free() method to close the object in Java and send a message to the server to release stream resources (objects and locks). Just letting the Java object go out of scope does not send a message to clean up the server resources.


Send us comments on this page
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA