Using InterSystems SQL
Storing and Using Stream Data (BLOBs and CLOBs)
InterSystems SQL supports the ability to store BLOBs (Binary Large Objects) and CLOBs (Character Large Objects) within an InterSystems IRIS Data Platform™ database. This chapter discusses the following topics:
InterSystems SQL supports stream fields. This type of field is used for data such as a large quantity of text, an image, video, or audio. The following SQL operations support stream fields:
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.
BLOBs and CLOBs have the following restrictions:
Within DDL, CLOB fields are defined using the LONG VARCHAR SQL data type. BLOB fields are defined using the LONG VARBINARY SQL data type.
CREATE TABLE MyApp.Person (
Name VARCHAR(50) not null,
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.
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.
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.
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.
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);
System.out.println("Clob length = " + clob.length());
System.out.println("Blob length = " + blob.length());
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.
Content Date/Time: 2019-04-18 08:27:08