docs.intersystems.com
Home / Using InterSystems SQL / Storing and Using Stream Data (BLOBs and CLOBs)

Using InterSystems SQL
Storing and Using Stream Data (BLOBs and CLOBs)
Previous section           Next section
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 InterSystems IRIS Data Platform™ 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. 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.
Stream Data Types
InterSystems SQL supports a variety of data type names for stream fields. These InterSystems data type names are synonyms that correspond to the following:
For data type mappings of stream data types, refer to the Data Types reference page in InterSystems SQL Reference.
The following example shows stream field definition:
CREATE TABLE MyApp.Person (
    Name VARCHAR(50) NOT NULL,
    Notes LONGVARCHAR,
    Photo LONGVARBINARY)
Querying Stream Field Data
A query 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,Picture,Notes 
FROM Sample.Employee WHERE Picture IS NOT NULL
An OID is a %List formatted data address such as the following: $lb("1","%Stream.GlobalCharacter","^EW3K.Cn9X.S"). The OID value is returned from a query when executed from Dynamic SQL, from the SQL Shell (which executes as Dynamic SQL), and from Embedded SQL.
However, when a query is run from the Management Portal SQL Execute Query interface, the OID is not returned. Instead:
The same values are shown in the Management Portal SQL interface Open Table display of table data.
You can concatenate an empty string to a stream value to return the stream field’s OID as a string, rather than return these stream data values.
DISTINCT, GROUP BY, and ORDER BY
When applied to a stream field in a SELECT 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.Employee 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.Employee 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(Picture) AS PicRows,COUNT(Notes) AS NoteRows
FROM Sample.Employee
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
InterSystems IRIS protects stream data values from concurrent operations by another process by taking out a lock on the stream data.
InterSystems IRIS takes out an exclusive lock before performing a write operation. The exclusive lock is released immediately after the write operation completes.
InterSystems IRIS 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 InterSystems IRIS Methods
You cannot use a BLOB or CLOB value using Embedded SQL or Dynamic SQL directly within an InterSystems IRIS 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 LONG VARCHAR. BLOB fields are represented as having type LONG VAR BINARY. For data type mappings of stream data types, refer to 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.


Previous section           Next section
Send us comments on this page
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-07-19 06:48:23