Using Caché SQL
Storing and Using Stream Data (BLOBs and CLOBs)
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   

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:

Stream Fields and SQL
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:
The same restrictions apply to stream fields. From the object point of view, BLOBs and CLOBs are represented as stream objects. For more information, see the chapter Working with Streams of Using Caché Objects.
Defining Stream Fields Using DDL
Within DDL, CLOB fields are defined using the LONG VARCHAR SQL data type. BLOB fields are defined using the LONG VARBINARY SQL data type.
    Name VARCHAR(50) not null,
For data type mappings of stream data types, refer to the Data Types reference page in Caché SQL Reference.
Empty BLOBs
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.
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.
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
    PrintLn "<TABLE>"
    While (rset.%Next())
        PrintLn "<TR>"
        ' display the person's name
        PrintLn "<TD>" & rset.Name & "</TD>"

        ' Now open the stream object containing the memo
        stream = OpenId %Stream(rset.Memo)
        Print "<TD>"

        ' Write the contents of the stream to the current device
        PrintLn "</TD></TR>"
    PrintLn "</TABLE>"
Using Stream Fields from ODBC
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.
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");;      // 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());

    // ...
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.