What is the fastest way (at run time) to have an Intersystems Cache database stored procedure return nothing but a BLOB?

Question

This question is specifically about Intersystems-Cache databases.

I'm currently using $$$ResultSet("MySQLQueryText") to select the BLOB from a table, but this is probably writing the BLOB to the table, then reading out from the table, instead of writing directly to the output BLOB.

The .INT code compiles into code that creates a %Library.ProcedureContext object, then calls NewResultSet() on that object. However, the source for NewResultSet has a comment: "Used internally only, do not call directly".

Is there a supported way to efficiently create a result set that is nothing but a single record with a single, BLOB column? Ideally I'd like something like a stream object and write to that directly, and have that go straight to the ODBC (or other) driver without copying the stream. If there is a supported solution using another object that isn't exactly a stream that would also be great.

Answer 1

@psr - Based on the discussion in the comments, I believe that you should be able to use code something like the following:

/// Method that will take in various arguments and return a BLOB as an output argument
ClassMethod GetBLOB(
  arg1 As %String,
  arg2 As %String,
  ...
  Output blob As %Stream.TmpBinary) [ SqlProc ]
{
  // Do work to produce your BLOB
  Set blob = yourBLOB
  Quit
}

Actual support for the BLOB may depend on your client software and whether you are using ODBC or JDBC, but anything reasonably recent should not pose any problems.

You would invoke this stored procedure using syntax like:

CALL Your_Schema.YourClass_GetBLOB('arg1','arg2',?)

The actual method for retrieving the BLOB will then depend on your client software and access method. You can also control the stored procedure name (i.e. the piece after the schema) by adding SqlName = MyGetBLOB next to the SqlProc keyword.