Skip to main content

CREATE QUERY

Creates a query.

Synopsis

CREATE QUERY queryname(parameter_list) [characteristics] 
   [ LANGUAGE SQL ]
   BEGIN
code_body ;
   END

CREATE QUERY queryname(parameter_list) [characteristics] 
    LANGUAGE OBJECTSCRIPT
   { code_body }

Arguments

Argument Description
queryname The name of the query to be created in a stored procedure class. The queryname must be a valid identifier. A procedure name can be qualified (schema.procname), or unqualified (procname). An unqualified procedure name takes the system-wide default schema name. The queryname must be followed by parentheses, even if no parameters are specified.
parameter_list Optional — A list of parameters to pass to the query. The parameter list is enclosed in parentheses, and parameters in the list are separated by commas. The parentheses are mandatory, even when no parameters are specified.
characteristics Optional — One or more keywords specifying the characteristics of the query. Permitted keywords are RESULTS, CONTAINID, FOR, FINAL, PROCEDURE, SELECTMODE. Multiple characteristics are separated by whitespace (a space or line break). Characteristics can be specified in any order.

LANGUAGE OBJECTSCRIPT

LANGUAGE SQL

Optional — A keyword clause specifying the programming language used for code_body. Specify either LANGUAGE OBJECTSCRIPT (for ObjectScript) or LANGUAGE SQL. If the LANGUAGE clause is omitted, SQL is the default.
code_body

The program code for the query.

SQL program code is prefaced with a BEGIN keyword and concludes with an END keyword. The code_body for a query consists of only one complete SQL statement (a SELECT statement). This SELECT statement ends with a semicolon (;).

ObjectScript program code is enclosed in curly braces. ObjectScript code lines must be indented.

Description

The CREATE QUERY statement creates a query in a class. By default, a query named MySelect would be stored as User.queryMySelect or SQLUser.queryMySelect.

CREATE QUERY creates a query which may or may not be exposed as a stored procedure. To create a query that is exposed as a stored procedure, you must specify the PROCEDURE keyword as one of its characteristics. You can also use the CREATE PROCEDURE statement to create a query which is exposed as a stored procedure.

In order to create a query, you must have %CREATE_QUERY administrative privilege, as specified by the GRANT command. If you are attempting to create a query for an existing class with a defined owner, you must be logged in as the owner of the class. Otherwise, the operation fails with an SQLCODE -99 error.

Arguments

queryname

The name of the query to be created as a stored procedure. This name may be unqualified (StoreName) and take the system-wide default schema name, or qualified by specifying the schema name (Patient.StoreName). You can use the $SYSTEM.SQL.DefaultSchema()Opens in a new tab method to determine the current system-wide default schema name. The initial system-wide default schema name is SQLUser which corresponds to the class package name User.

Note that the FOR characteristic (described below) overrides the class name specified in queryname. If a method with this name already exists, the operation fails with an SQLCODE -361 error.

The name of the generated class is the package name corresponding to the schema name, followed by a dot, followed by “query”, followed by the specified queryname. For example, if the unqualified query name RandomLetter takes the initial default schema SQLUser, the resulting class name would be: User.queryRandomLetter. For further details, see SQL to Class Name Transformations in the “Defining and Using Stored Procedures” chapter of Using Caché SQL.

Caché SQL does not allow you to specify a queryname that differs only in letter case. Specifying a queryname that differs only in letter case from an existing query name results in an SQLCODE -400 error.

If the specified queryname already exists in the current namespace, the system generates an SQLCODE -361 error.

parameter-list

A list of parameter declarations for parameters used to pass values to the query. The parameter list is enclosed in parentheses, and parameter declarations in the list are separated by commas. The parentheses are mandatory, even if you specify no parameters.

Each parameter declaration in the list consists of (in order):

  • An optional keyword specifying whether the parameter mode is IN (input value), OUT (output value), or INOUT (modify value). If omitted, the default parameter mode is IN.

  • The parameter name. Parameter names are case-sensitive.

  • The data type of the parameter.

  • Optional: A default value for the parameter. You can specify the DEFAULT keyword followed by a default value; the DEFAULT keyword is optional. If no default is specified, the assumed default is NULL.

The following example creates a query exposed as a stored procedure with two input parameters, both of which have default values. The topnum input parameter specifies the optional DEFAULT keyword; the minage input parameter omits this keyword:

CREATE QUERY AgeQuery(IN topnum INT DEFAULT 10,IN minage INT 20)
   PROCEDURE
   BEGIN
   SELECT TOP :topnum Name,Age FROM Sample.Person
   WHERE Age > :minage ;
   END

The following are all valid CALL statements for this query: CALL AgeQuery(6,65); CALL AgeQuery(6); CALL AgeQuery(,65); CALL AgeQuery().

characteristics

The available characteristics keywords are as follows:

Characteristics Keyword Description
CONTAINID integer Specifies which field, if any, returns the ID. Set CONTAINID to the number of the column that returns the ID, or 0 if no column returns the ID. Caché does not validate that the named field actually contains the ID, so a user error here results in inconsistent data.
FOR className Specifies the name of the class in which to create the method. If the class does not exist, it will be created. You can also specify a class name by qualifying the method name. The class name specified in the FOR clause overrides a class name specified by qualifying the method name.
FINAL Specifies that subclasses cannot override the method. By default, methods are not final. The FINAL keyword is inherited by subclasses.
PROCEDURE Specifies that the query is an SQL stored procedure. Stored procedures are inherited by subclasses. (This keyword can be abbreviated as PROC.)
RESULTS (result_set)

Specifies the data fields in the order that they are returned by the query. If you specify a RESULTS clause, you must list all fields returned by the query as a comma-separated list enclosed in parentheses. Specifying fewer or more fields than are returned by the query results in a SQLCODE -76 cardinality mismatch error.

For each field you specify a column name (which will be used as the column header) and a data type.

If LANGUAGE SQL, you can omit the RESULTS clause. If you omit the RESULTS clause, the ROWSPEC is automatically generated during class compilation.

SELECTMODE mode Specifies the mode used to compile the query. The possible values are LOGICAL, ODBC, RUNTIME, and DISPLAY. The default is RUNTIME.

If you specify a method keyword (such as PRIVATE or RETURNS) that is not valid for a query, the system generates an SQLCODE -47 error. Specifying duplicate characteristics results in an SQLCODE -44 error.

The SELECTMODE clause specifies the mode in which data is returned. If the mode value is LOGICAL, then logical (internal storage) values are returned. For example, dates are returned in $HOROLOG format. If the mode value is ODBC, logical-to-ODBC conversion is applied, and ODBC format values are returned. If the mode value is DISPLAY, logical-to-display conversion is applied, and display format values are returned. If the mode value is RUNTIME, the mode can be set (to LOGICAL, ODBC, or DISPLAY) at execution time by setting the %SQL.StatementOpens in a new tab class %SelectModeOpens in a new tab property, as described in “Using Dynamic SQL” chapter of Using Caché SQL. The RUNTIME mode default is LOGICAL. For further details on SelectMode options, refer to “Data Display Options” in the “Caché SQL Basics” chapter of Using Caché SQL. The value that you specify for SELECTMODE is added at the beginning of the ObjectScript class method code as: #SQLCompile SELECT=mode. For further details, see #sqlcompile select in the “ObjectScript Macros and the Macro Preprocessor” chapter of Using Caché ObjectScript.

The RESULTS clause specifies the results of a query. The SQL data type parameters in the RESULTS clause are translated into corresponding Caché data type parameters in the query’s ROWSPEC. For example, the RESULTS clause RESULTS ( Code VARCHAR(15) ) generates a ROWSPEC specification of ROWSPEC = “Code:%Library.String(MAXLEN=15)”.

LANGUAGE

A keyword clause specifying the language you are using for code_body. Permitted clauses are LANGUAGE OBJECTSCRIPT (for ObjectScript) or LANGUAGE SQL. If the LANGUAGE clause is omitted, SQL is the default.

If the LANGUAGE is SQL a class query of type %Library.SQLQueryOpens in a new tab is generated. If the LANGUAGE is OBJECTSCRIPT, a class query of type %Library.QueryOpens in a new tab is generated.

code_body

The program code for the query to be created. You specify this code in either SQL or ObjectScript. The language used must match the LANGUAGE clause. However, code specified in ObjectScript can contain embedded SQL.

If the code you specify is SQL, it must consist of a single SELECT statement. The program code for a query in SQL is prefaced with a BEGIN keyword, followed by the program code (a SELECT statement). At the end of the program code, specify a semicolon (;) then an END keyword.

If the code you specify is OBJECTSCRIPT, it must contain calls to the Execute()Opens in a new tab and Fetch()Opens in a new tab class methods of the %Library.QueryOpens in a new tab class provided by Caché, and may contain Close()Opens in a new tab, FetchRows()Opens in a new tab, and GetInfo()Opens in a new tab method calls. ObjectScript code is enclosed in curly braces. If Execute() or Fetch() are missing, an SQLCODE -46 error is generated upon compilation.

If the ObjectScript code block fetches data into a local variable (for example, Row), you must conclude the code block with the line SET Row="" to indicate an end-of-data condition.

If the query is exposed as a stored procedure (by specifying the PROCEDURE keyword in characteristics), it uses a procedure context handler to pass the procedure context back and forth between the procedure and its caller.

When a stored procedure is called, an object of the class %Library.SQLProcContextOpens in a new tab is instantiated in the %sqlcontext variable. This is used to pass the procedure context back and forth between the procedure and its caller (for example, the ODBC server).

%sqlcontext consists of several properties, including an Error object, the SQLCODE error status, the SQL row count, and an error message. The following example shows the values used to set several of these:

  SET %sqlcontext.%SQLCODE=SQLCODE
  SET %sqlcontext.%ROWCOUNT=%ROWCOUNT
  SET %sqlcontext.%Message=%msg

The values of SQLCODE and %ROWCOUNT are automatically set by the execution of an SQL statement. The %sqlcontext object is reset before each execution.

Alternatively, an error context can be established by instantiating a %SYSTEM.Error object and setting it as %sqlcontext.Error.

Caché uses the code you supply to generate the actual code of the query.

Examples

The following embedded SQL example creates a query named DocTestPersonState. It declares no parameters, sets the SELECTMODE characteristic, and takes the default (SQL) for LANGUAGE:

  &sql(CREATE QUERY DocTestPersonState() SELECTMODE RUNTIME
       BEGIN
       SELECT Name,Home_State FROM Sample.Person ;
       END)
  IF SQLCODE=0 { WRITE !,"Created a query" }
  ELSEIF SQLCODE=-361 { WRITE !,"Query exists: ",%msg }
  ELSE { WRITE !,"CREATE QUERY error: ",SQLCODE }

You can go to the Management Portal, select the Classes option, then select the SAMPLES namespace. There you will find the query created by the above example: User.queryDocTestPersonState.cls. From this display you can delete this query before rerunning the above program example. You can, of course, use DROP QUERY to delete created queries.

The following Embedded SQL example creates a method-based query named DocTestSQLCODEList which fetches a list of SQLCODEs and their descriptions. It sets a RESULTS result set characteristic, sets LANGUAGE as ObjectScript, and calls the Execute(), Fetch(), and Close() methods:

  &sql(CREATE QUERY DocTestSQLCODEList()
  RESULTS (SQLCODE SMALLINT,Description VARCHAR(100))
    PROCEDURE
    LANGUAGE OBJECTSCRIPT
  Execute(INOUT QHandle BINARY(255))
    {
     SET QHandle=1,%i(QHandle)=""
     QUIT ##lit($$$OK)
    }
  Fetch(INOUT QHandle BINARY(255), INOUT Row %List, INOUT AtEnd INT)
    {
     SET AtEnd=0,Row=""
     SET %i(QHandle)=$o(^%qCacheSQL("SQLCODE",%i(QHandle)))
     IF %i(QHandle)="" {SET AtEnd=1 QUIT ##lit($$$OK) }
       SET Row=$lb(%i(QHandle),^%qCacheSQL("SQLCODE",%i(QHandle),1,1))
     QUIT ##lit($$$OK)
    }
  Close(INOUT QHandle BINARY(255))
    {
     KILL %i(QHandle)
     QUIT ##lit($$$OK)
    }
  )
  IF SQLCODE=0 { WRITE !,"Created a query" }
  ELSEIF SQLCODE=-361 { WRITE !,"Query exists: ",%msg }
  ELSE { WRITE !,"CREATE QUERY error: ",SQLCODE }

You can go to the Management Portal, select the Classes option, then select the SAMPLES namespace. There you will find the query created by the above example: User.queryDocTestSQLCODEList.cls. From this display you can delete this query before rerunning the above program example. You can, of course, use DROP QUERY to delete created queries.

The following Dynamic SQL example creates a query named DocTest, then executes this query using the %PrepareClassQuery()Opens in a new tab method of the %SQL.StatementOpens in a new tab class:

  /* Creating the Query */
  SET myquery=4
    SET myquery(1)="CREATE QUERY DocTest() SELECTMODE RUNTIME "
    SET myquery(2)="BEGIN "
    SET myquery(3)="SELECT TOP 5 Name,Home_State FROM Sample.Person ; "
    SET myquery(4)="END"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  IF SQLCODE=0 { WRITE !,"Created a query",! }
  ELSEIF SQLCODE=-361 { WRITE !,"Query exists: ",%msg }
  ELSE { WRITE !,"CREATE QUERY error: ",SQLCODE }
  /* Calling the Query */
  WRITE !,"Calling a class query",!
  SET cqStatus = tStatement.%PrepareClassQuery("User.queryDocTest","DocTest")
    IF cqStatus'=1 {WRITE "%PrepareClassQuery failed:" DO $System.Status.DisplayError(cqStatus)}
  SET rset = tStatement.%Execute()
  WRITE "Query data",!,!
  WHILE rset.%Next() {
     DO rset.%Print() } 
  WRITE !,"End of data"
  /* Deleting the Query */
  &sql(DROP QUERY DocTest)
  IF SQLCODE=0 { WRITE !,"Deleted the query" }

For further details, refer to the Dynamic SQL chapter of Using Caché SQL.

See Also

FeedbackOpens in a new tab