New post
Question
· Jul 18, 2016

How do I execute a "Basic Class Query?"

#Caché #SQL

I have a NewBie Question.

I have been playing around with "Basic CLass Queries."

I  have defined a very simple "Basic Class Query." (see below)

However, I cannot find in the I/S documentation how to execute this query.

Any help is appreciated.

 

Class LastName.BasicClassQuery Extends LastName.Person
{

Query Display() As %SQLQuery (ROWSPEC="ID:%Integer,Name:%String", CONTAINID = 1)
{
SELECT %ID, Name FROM LastName.Person
 ORDER BY DOB
}

Storage Default
{
<Type>%Library.CacheStorage</Type>
}

}
Discussion (8)0
Log in or sign up to continue
Brendan Batchelder · Jul 19, 2016

Basic Class Queries are similar to stored procedures.  You can hard-code some query parameters while allowing others to be passed as arguments.  The query is pre-compiled in the code.  With a dynamic query, when you call the .Prepare method, this causes code to be generated at runtime to run the query.

Embedded SQL is also pre-compiled into the code.  One benefit basic class queries have over embedded SQL is that basic class queries can be exposed to SQL, allowing them to be called like stored procedures.

0 0
Eduard Lebedyuk · Jul 19, 2016

For every query (which can be a simple SQL query or a custom class query, here’s my post about them and their uses) QueryFunc method gets generated:

ClassMethod QueryFunc(Arg1, Arg2) As %SQL.StatementResult

which returns a %SQL.StatementResult used to iterate over the query. For example your Display query for LastName.BasicClassQuery class can be called from object context with this code:

Set ResultSet=##class(LastName.BasicClassQuery).DisplayFunc()
While ResultSet.%Next() { Write ResultSet.Name,! }
4 0
John Hotalen · Jul 22, 2016

If you just want to see data for the class query, then you can also run the query interactively via your Cache Terminal window (or any other type of terminal emulator, such as Putty, Reflections, PowerTerm, etc...).

Here is the command to run the class query from your example:

Do ##class(%ResultSet).RunQuery("LastName.BasicClassQuery","Display")

Note:  If your query accepts input parameters, then you just include them as the 3rd-nth parameters - an example of what that might look like if you needed to pass two input values to your query:    Do ##class(%ResultSet).RunQuery("LastName.BasicClassQuery","Display","value1","value2")

This will list the data in your terminal window and might look something like this:  Notice the first row that is displayed is all your column names (i.e. property names) and all the subsequent rows that are displayed is the data from your table(s).

Code:Name:IsActive:ID:
AF:Archived Forms:1:31:
AR:AdHoc Feedback Report:1:29:
AT:Stain/Request Audit Report:1:11:
CH:Cyto-Histo Correlation:1:23:
CPS:Cytology Population Statistics:1:22:
 

0 0
Mike Kadow · Jul 22, 2016

Ok, if I may, let me give some background of my thinking.

I thought of Embedded Query and Dynamic Query in the same class.

Also, I thought of Basic Class Query and Custom Class Query in the same class.

And Stored Procedure in a class by itself (although Stored Procedure crosses all classes) as well as SQL Shell in a class by itself.

So when I found out that Basic Class Query is run using the same mechanics as Dynamic Query, it crossed wires in my mind.

It seems to me that the Queries, taken individually and taken as a whole, could be classified better. I have the feeling that the queries were just thrown together  in a hodge-podge sort of way with no rhyme or reason. Maybe with some more fore-thought and front-end planning  the whole issue of Queries could be presented better. Well, that is my 2 cents worth.

0 0