Caché SQL supports the ability to store BLOBs (Binary Large Objects) and CLOBs (Character Large Objects) within the database. This chapter discusses the following topics:
Caché 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:
Caché 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.
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.
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.
For example, you can use Dynamic SQL to read a stream in a Basic method as follows:
/// Display the memos for all Persons with a given city
/// within an HTML table
ClassMethod DisplayMemo(city As %String = "") [language = basic]
' Define a query to find all the Stream Id values for memo
tStatement = New %SQL.Statement()
tStatus = tStatement.%Prepare("SELECT Name,Memo FROM MyApp.Person WHERE Home_City = ?")
rset = tStatement.%Execute(city)
' iterate over the results
' display the person's name
PrintLn "<TD>" & rset.Name & "</TD>"
' Now open the stream object containing the memo
stream = OpenId %Stream(rset.Memo)
' Write the contents of the stream to the current device
The ODBC specification does not provide for any recognition or special handling for BLOB and CLOB fields. Caché 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 Caché 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.
© 1997-2019 InterSystems Corporation, Cambridge, MA