Skip to main content

Adding Queries to a Class

Caché class definitions may contain query definitions. Each query defines an instance of query interface: a set of methods that can be called from a Caché %ResultSetOpens in a new tab object allowing you to iterate over a set of data.

You can use queries in the following ways:

  • Via the %ResultSetOpens in a new tab class in server-based methods.

  • Via the Java ResultSet class that is included with the Caché Java binding.

  • Via the ActiveX ResultSet class that is included with the Caché ActiveX binding.

  • As ODBC or JDBC stored procedures (if you specify that the query should be projected as an SQL stored procedure).

There are two kinds of queries:

The %SQLQueryOpens in a new tab class automatically provides an implementation of the query interface. The %QueryOpens in a new tab class does not; the query definition merely provides a query interface; you must write methods to implement the actual query functionality.

You can add a new query to a class definition in two ways:

  • Edit the class definition using the Class Editor.

  • Using the New Query wizard

To add a query using the Class Editor, position the cursor on a blank line in the Class Editor and enter a query declaration:

Class MyApp.Person Extends %Persistent
{
Property Name As %String;

/// This query provides a list of persons ordered by Name.
Query ByName(ByVal name As %String) As %SQLQuery(CONTAINID = 1)
{
    SELECT ID,Name FROM Person
    WHERE (Name %STARTSWITH :name)
    ORDER BY Name
}

}

Alternatively, you can copy and paste an existing query declaration and then edit it.

For a general introduction to queries, see the chapter “Queries” of Using Caché Objects. For details on query definitions, see “Class Definitions” in the reference “Class Definitions” in Caché Class Definition Reference.

New Query Wizard

You can use the New Query wizard to add a new query to a class definition. You can open the New Query wizard using Class > Add > Query. Alternatively, right-click the Class Inspector and select Add Query or select the New Query icon, generated description: newqueryicon, in the toolbar.

Select Finish at any time; default values are provided for any information you have not specified.

Name, Implementation, and Description Page

The New Query wizard prompts you for the following information (you can later modify any of these values):

Query Name

(required) Name of the new query. This name must be a valid query name and must not conflict with the name of a previously defined query.

For a discussion of names, see the chapter “Caché Classes ” in Using Caché Objects.

Implementation

(required) You must specify if this query is based on an SQL statement (which the wizard generates for you) or on user-written code (in which case you have to provide the code for the query implementation).

Description

(optional) Description of the new query. This description is used when the class documentation is displayed in the online class library documentation.

A description may include HTML formatting tags. For more details, see “Using HTML Markup in Class Documentation” in the chapter “Defining and Compiling Classes” in Using Caché Objects.

Input Parameters Page

A query may take zero or more input parameters (arguments).

You can specify the names, types, default values, and how data is passed by value for these parameters. The arguments are displayed in order in a table. You can add a new item to the argument list using the Add icon generated description: newqueryaddicon located on the side of the table. This displays a popup dialog allowing you to specify the name of the argument, its type, its optional default value. Using up, generated description: uparrowicon, and down arrows, generated description: downarrowicon, you can rearrange the order of items in the list.

Columns Page

For an SQL-based query, you must specify the object properties (columns) that you want included in the result set (this is the SELECT clause of the generated SQL query).

To add a column to the query, select an item from the left-hand list of available properties and move it to the right-hand list using the > (Move To) button.

Conditions Page

For an SQL-based query, you can specify conditions to restrict the result set (the SQL WHERE clause of the generated SQL query).

You can build a set of conditions by selecting values from the set of combo boxes. The expression box can contain an expression (such as a literal value) or a query argument (as an SQL host variable with a prepended : colon character).

Order By Page

For an SQL-based query, you can specify any columns you want to use to sort the result set (the SQL ORDER BY clause of the generated SQL query).

Row Specification Page

For a user-written query, you must specify the names and types of the columns that are to be returned by the query.

The wizard does not prompt for this information for SQL-based queries as the class compiler can determine it by examining the SQL query.

Results of Running the New Query Wizard

After you run the New Query wizard, the Class Editor window is updated to include the new query definition. For example:

/// This is a Person class
class MyApp.Person extends %Persistent 
{

Query ByName(ByVal name As %String) As %SQLQuery(CONTAINID = 1)
{
    SELECT ID,Name FROM Person
    WHERE (Name %STARTSWITH :name)
    ORDER BY Name
}

}

If you want to make further modifications to this query you can do this using either the Class Editor or the Class Inspector.

If you specified a user-written query, the Class Editor contains both the new query definition as well as skeletons of the query methods you are expected to implement:

Class MyApp.Person Extends %Persistent 
{
// ...

ClassMethod MyQueryClose(
        ByRef qHandle As %Binary
        ) As %Status [ PlaceAfter = MyQueryExecute ]
{
    Quit $$$OK
}

ClassMethod MyQueryExecute(
        ByRef qHandle As %Binary,
        ByVal aaa As %Library.String
        ) As %Status
{
     Quit $$$OK
}

ClassMethod MyQueryFetch(
        ByRef qHandle As %Binary,
        ByRef Row As %List,
        ByRef AtEnd As %Integer = 0
        ) As %Status [ PlaceAfter = MyQueryExecute ]
{
    Quit $$$OK
}

Query MyQuery(
        ByVal aaa As %Library.String
        ) As %Query(ROWSPEC = "C1,C2")
{
}

}
FeedbackOpens in a new tab