2

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.

carl
  • 33
  • 5

1 Answers1

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.

SSH
  • 2,533
  • 16
  • 21
  • 1
    SSH, I have looked through the referenced documentation, but I can't find the sample code you mention "see Sample.CustomResultSet in the SAMPLES namespace. I think I understand that I have to create a new class with properties that represent the datarow I want to return in the customer query, but I could really use a sample that shows how the pieces fit together. – carl Feb 12 '15 at 16:47
  • 1
    SSH, I found the Sample namespace, and this is perfect, thankyou – carl Feb 12 '15 at 17:07