0

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.

psr
  • 2,870
  • 18
  • 22
  • Is there a reason you don't want your BLOB as an output value from the stored procedure...? And if your code is simply performing a select, it will likely be retrieved when the client requests the content of the result set. – DdP Jan 06 '14 at 22:59
  • @DdP - I'm not sure what you mean. The answer might be (if I understand your comment) that the BLOB doesn't need to be stored in a table. I'm currently doing that just because I can't find another way to get the BLOB to the client. The code is not really simply performing a select, it does lots of stuff to make the BLOB then puts it in a table. I'd like to just pass it directly to the client and never put it into a table. – psr Jan 07 '14 at 00:51
  • I think the key issue here involves the contents of "MySQLQueryText". If that is simply a complex SELECT statement that ends up building your BLOB, then the BLOB should generally be retained in memory without being written to a table. One exception to this is if your query has to build a temp table to ensure that candidate BLOBs are identified correctly, but this may or may not be an issue for you. If you simply have one BLOB, can it not be made an output argument or return value of the stored procedure? If this is still slow, it may be worth contacting InterSystems to work out why that is. – DdP Jan 10 '14 at 17:51
  • @DdP - It's not an SQL query at all. It's object oriented code creating a result set that can be exposed via ODBC (or similar). – psr Jan 10 '14 at 18:24

1 Answers1

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.

DdP
  • 438
  • 2
  • 6
  • It doesn't give you the flexibility that a result set would, and that would be nice, but it is what I asked for and should probably work. – psr Jan 11 '14 at 03:48