How to use object script in a SQL Proc for an Intersystems Cache database

Question

Intersystems Cache provides interfaces to run SQL relational queries against a cache database. When trying to do aggregate queries across the object data structures is very slow and sometimes practically impossible.

Is there a way to have an object script method return data rows as a SQLProc type without using low performing SQL queries.

I have looked at the rset and %List objects, but I can't seem to connect the dots.

I can return a delimited string, but I need to be able to expose it as a stored procedure to reporting engines such as Crystal etc.

Answer 1

There are several ways to expose custom query results, they all are mentioned in documentation here:

http://docs.intersystems.com/cache20141/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_procedures

As documentation suggests, preferred way is to define each custom query as a separate class using %SQL.CustomQuery class; see Sample.CustomResultSet in the SAMPLES namespace for an example of this approach.

You can also define multiple SQL queries with custom Execute/Fetch/Close methods in a single class. %Dictionary package has a lot of examples of this approach in classes ending with "Query". You can also search for "%Query" in system items for more examples.