Storing and Using Stream Data (BLOBs and CLOBs)

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:

• SELECT selects a stream field and 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

  

• The IS [NOT] NULL, %CONTAINS, and %CONTAINSTERM predicates can be applied to the data value of a stream field.

  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.

• SELECT cannot apply any function to a stream value field, except the COUNT, %OBJECT, CHARACTER_LENGTH (or CHAR_LENGTH or DATALENGTH), SUBSTRING, CONVERT, %SIMILARITY, 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.

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

  • The %OBJECT function opens a stream object (takes an OID) and returns the oref (object reference), as shown in the following example:

    SELECT Name,Notes,%OBJECT(Notes) AS NotesOref
    FROM Sample.Employee WHERE Notes IS NOT NULL

    

  • The CHARACTER_LENGTH, CHAR_LENGTH, and DATALENGTH functions take a stream field or an oref for a stream field (%OBJECT(streamfield)), as shown in the following example:

    SELECT Name,DATALENGTH(Notes) AS NotesNumChars
    FROM Sample.Employee WHERE Notes IS NOT NULL

    

  • The SUBSTRING function takes a stream field or an oref for a stream field (%OBJECT(streamfield)) and returns the specified substring of the stream field’s value, as shown in the following example:

    SELECT Name,SUBSTRING(Notes,1,10) AS Notes1st10Chars
    FROM Sample.Employee WHERE Notes IS NOT NULL

    

  • The CONVERT function can be used to convert a stream data type to VARCHAR, as shown in the following example:

    SELECT Name,CONVERT(VARCHAR(100),Notes) AS NotesText
    FROM Sample.Employee WHERE Notes IS NOT NULL

    

    CONVERT(datatype,expression) syntax supports stream data conversion; {fn CONVERT(expression,datatype)} syntax does not support stream data conversion.

  • The %INTERNAL function can be used on a stream field, but performs no operation.

• INSERT and UPDATE accept the fully formed OID (object ID) value of the stream object as the new value for a stream field. If a record contains a stream field, an INSERT or UPDATE operation can only modify a single row, not multiple rows. An INSERT can insert a default value for a stream field.

• Stream fields cannot be indexed.

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.

CREATE TABLE MyApp.Person (
    Name VARCHAR(50) not null,
    Notes LONGVARCHAR,
    Photo LONGVARBINARY
)

For data type mappings of stream data types, refer to the Data Types reference page in Caché SQL Reference.

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 %AbstractStreamOpens in a new tab 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
        stream.OutputToDevice()
        PrintLn "</TD></TR>"
    Wend
    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");
    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());

    // ...