Skip to main content

Defining and Using Class Queries

This topic discusses class queries, which act as named queries that are part of a class structure and that can be accessed via dynamic SQL.

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:

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

Important:

Do not define a class query that depends upon the results of another class query. Such a dependency is not supported.

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.

  • 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).

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

FeedbackOpens in a new tab