Defining and Using Class Queries

Introduction to Class Queries

A class query is a tool — contained in a class and meant for use with dynamic SQL — to look up records that meet specified criteria. With class queries, you can create predefined lookups for your application. For example, you can look up records by name, or provide a list of records that meet a particular set of conditions, such as all the flights from Paris to Madrid.

By creating a class query, you can avoid having to look up a particular object by its internal ID. Instead, you can create a query that looks up records based on any class properties that you want. These can even be specified from user input at runtime.

If you define a custom class query, your lookup logic can use ObjectScript and can be arbitrarily complex.

There are two kinds of class queries:

• Basic class queries, which use the class %SQLQueryOpens in a new tab and an SQL SELECT statement.

• Custom class queries, which use the class %QueryOpens in a new tab and custom logic to execute, fetch, and close the query. These are discussed on another page.

Note that you can define class queries within any class; there is no requirement to contain them within persistent classes.

Using Class Queries

Before looking at how to define class queries, it is useful to see how you can use them. In server-side code, you can use a class query as follows:

1. Use %New() to create an instance of %SQL.StatementOpens in a new tab.

2. Call the %PrepareClassQuery() method of that instance. As arguments, use the following, in order:

   1. Fully qualified name of the class that defines the query that you want to use.

   2. Name of the query in that class.

   This method returns a %StatusOpens in a new tab value, which you should check.

3. Call the %Execute() method of the %SQL.StatementOpens in a new tab instance. This returns an instance of %SQL.StatementResultOpens in a new tab.

4. Use methods of %SQL.StatementResultOpens in a new tab to retrieve data from the result set. For details, see Dynamic SQL.

The following shows a simple example that uses the ByName query of Sample.Person:

  // classquerydemo
  set statement = ##class(%SQL.Statement).%New()
  set status = statement.%PrepareClassQuery("Sample.Person","ByName")
  if $$$ISERR(status) {
     write "%Prepare failed:" do $SYSTEM.Status.DisplayError(status)
     quit
  }

  set rset = statement.%Execute()
  if (rset.%SQLCODE '= 0) {
    write "%Execute failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message 
    quit
  }

  while rset.%Next()
  {
    write !, rset.%Get("Name")
  }
  if (rset.%SQLCODE < 0) {
    write "%Next failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message 
    quit
  }

You can also invoke the query as a stored procedure, thus executing it from an SQL context. See Defining and Using Stored Procedures.

Defining Basic Class Queries

To define a basic class query, define a query as follows:

• (For simple class queries) The type should be %SQLQueryOpens in a new tab.

• Optionally specify parameters of %SQLQueryOpens in a new tab, such as ROWSPEC. The ROWSPEC parameter for a query provides information on the names, data types, headings, and order of the fields in each row. For more information, see Parameters of the Class Query.

• In the argument list, specify any arguments that the query should accept.

• In the body of the definition, write an SQL SELECT statement.

  In this statement, to refer to an argument, precede the argument name with a colon (:).

  This SELECT statement should not include an INTO clause.

• Include the SqlProc keyword in the query definition.

• Optionally specify the SqlName keyword in the query definition, if you want the name of the stored procedure to be other than the default name.

  These are compiler keywords, so include them in square brackets after any parameters, after the query type (%SQLQueryOpens in a new tab).

Parameters of the Class Query

This section provides more details on the parameters you specify within a custom class query.

About ROWSPEC

The ROWSPEC parameter for a query provides information on the names, data types, headings, and order of the fields in each row. It is a quoted and comma-separated list of variable names and data types of the form:

ROWSPEC = "Var1:%Type1,Var2:%Type2[:OptionalDescription],Var3"

The ROWSPEC specifies the order of fields as a comma-separated list. The information for each field consists of a colon-separated list of its name, its data type (if it is different than the data type of the corresponding property), and an optional heading.

The number of elements in the ROWSPEC parameter must match the number of fields in the query. Otherwise, InterSystems IRIS® data platform returns a Cardinality Mismatch error.

For an example, the ByName query of the Sample.Person class is as follows:

Query ByName(name As %String = "") 
    As %SQLQuery(CONTAINID = 1, ROWSPEC = "ID:%Integer,Name,DOB,SSN", SELECTMODE = "RUNTIME") 
   [ SqlName = SP_Sample_By_Name, SqlProc ]
{
        SELECT ID, Name, DOB, SSN
            FROM Sample.Person
            WHERE (Name %STARTSWITH :name)
            ORDER BY Name
}

Here, the CONTAINID parameter specifies that the row ID is the first field (the default); note that the first field specified in the SELECT statement is ID. The ROWSPEC parameter specifies that the fields are ID (treated as an integer), Name, DOB, and SSN; similarly, the SELECT statement contains the fields ID, Name, DOB, and SSN, in that order.

Example

The following shows a simple example:

Query ListEmployees(City As %String = "") 
   As %SQLQuery (ROWSPEC="ID:%Integer,Name:%String,Title:%String", CONTAINID = 1) [SqlProc, SqlName=MyProcedureName]
{
SELECT ID,Name,Title FROM Employee
 WHERE (Home_City %STARTSWITH :City)
 ORDER BY Name
}

Maximum Length of String Parameters

If you call a class query using ADO.NET, ODBC, or JDBC, any string parameters will be truncated to 50 characters by default. To increase the maximum string length for a parameter, specify a MAXLEN in the signature, as in the following example:

Query MyQuery(MyParm As %String(MAXLEN = 200)) As %SQLQuery [SqlProc]

This truncation does not occur if you call the query from the Management Portal or from ObjectScript.

See Also

• Defining Custom Class Queries

• Defining Classes