%OBJECT (SQL)

Synopsis

%OBJECT(stream)

Description

%OBJECT is used to open a stream object and return the oref (object reference) of the stream field.

A SELECT on a stream field returns the fully formed oid (object ID) value of the stream field. A SELECT %OBJECT on a stream field returns the oref (object reference) of the stream field. This is shown in the following example, in which Notes and Picture are both stream fields:

  set myquery = "SELECT TOP 3 Title,Notes,%OBJECT(Picture) AS Photo FROM Sample.Employee"
  set tStatement = ##class(%SQL.Statement).%New()

  set qStatus = tStatement.%Prepare(myquery)
  if $$$ISERR(qStatus) {write "%Prepare failed:" do $SYSTEM.Status.DisplayError(qStatus) quit}

  set rset = tStatement.%Execute()
  if (rset.%SQLCODE '= 0) {write "%Execute failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}

  while rset.%Next()
  {
    write "String field: ",rset.Title,!
    write "Stream field oid: ",rset.Notes,!
    write "Stream field oref: ",rset.Photo,!!
  }
  if (rset.%SQLCODE < 0) {write "%Next failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}
  write !,"End of data"

If stream is not a stream field, %OBJECT generates an SQLCODE -128 error.

%OBJECT can be used as an argument to the following functions:

• CHARACTER_LENGTH(%OBJECT(streamfield)), CHAR_LENGTH(%OBJECT(streamfield)), or DATALENGTH(%OBJECT(streamfield)).

• SUBSTRING(%OBJECT(streamfield),start,length).

You can perform the same operation by issuing a SELECT on a stream field, then opening the stream oid by calling the $Stream.Object.%Open()Opens in a new tab class method, which generates an oref from the oid:

SET oref = ##class(%Stream.Object).%Open(oid)

For information on orefs, see “OREF Basics” in Defining and Using Classes. For information on oids, see “Identifiers for Saved Objects: ID and OID” in the same book.

See Also

• SELECT

• Introduction to the Default SQL Projection in the “Introduction to Persistent Objects”chapter of Defining and Using Classes

• Using Streams with SQL in the “Streams” chapter of Defining and Using Classes

• Storing and Using BLOBs and CLOBs chapter of Using InterSystems SQL