CREATE QUERY (SQL)

Creates a query.

Synopsis

CREATE [OR REPLACE] QUERY queryname(parameter_list)     [ characteristics ] [ LANGUAGE SQL ]     BEGIN code_body ;    
    END  CREATE QUERY queryname(parameter_list) [characteristics]      LANGUAGE OBJECTSCRIPT
   { code_body }

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.

You cannot create a query in a class if the class definition is a deployed class. This operation fails with an SQLCODE -400 error with the %msg Unable to execute DDL that modifies a deployed class: 'classname'.

Arguments

queryname

The name of the query to be created in a stored procedure class. The queryname must be a valid identifier and must be followed by parentheses, even if no parameters are specified. The procedure name may be unqualified (StoreName) and take the default schema name, or qualified by specifying the schema name (Patient.StoreName). You can use the $SYSTEM.SQL.Schema.Default()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 InterSystems SQL.

InterSystems 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.

Include the optional keyword OR REPLACE to modify or replace an existing query without generating an error. CREATE OR REPLACE QUERY has the same effect as invoking DROP QUERY to delete the old version of the query and then invoking CREATE QUERY.

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

An optional argument denoting one or more keywords that specify the characteristics of a query. Characteristics can be specified in any order. 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. InterSystems IRIS 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 InterSystems SQL. The RUNTIME mode default is LOGICAL. For further details on SelectMode options, refer to “Data Display Options” in the “InterSystems IRIS SQL Basics” chapter of Using InterSystems 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 ObjectScript.

The RESULTS clause specifies the results of a query. The SQL data type parameters in the RESULTS clause are translated into corresponding InterSystems IRIS 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

An optional keyword clause specifying the language you are using for code_body. Permitted clauses are LANGUAGE 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 InterSystems IRIS, 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.

InterSystems IRIS uses the code you supply to generate the actual code of the query.

Examples

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

CREATE QUERY DocTestPersonState() SELECTMODE RUNTIME
BEGIN
SELECT Name,Home_State FROM Sample.Person ;
END

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 $$$ISERR(qStatus) {write "%Prepare failed:" do $SYSTEM.Status.DisplayError(qStatus) quit}

  set rset = tStatement.%Execute()
  if (rset.%SQLCODE '= 0) {write "%Unable to call query", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}

  /* Calling the Query */
  write !,"Calling a class query",!
  set cqStatus = tStatement.%PrepareClassQuery("User.queryDocTest","DocTest")
  if $$$ISERR(cqStatus) {write "%PrepareClassQuery failed:" do $SYSTEM.Status.DisplayError(cqStatus) quit}

  set rset = tStatement.%Execute()
  if (rset.%SQLCODE '= 0) {write "Unable to call class query", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}

  write "Query data",!,!
  while rset.%Next()
  {
     do rset.%Print()
  }
  if (rset.%SQLCODE < 0) {write "%Next failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}
  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 InterSystems SQL.

Security and Privileges

The CREATE QUERY command is a privileged operation that requires the user to have %Development:USE permission. Such permissions can be granted through the Management Portal. Executing a CREATE QUERY command without these privileges will result in an SQLCODE -99 error and the command will fail.

Users without proper permissions can still execute this command under one of two conditions:

The command is executed via Embedded SQL, which does not perform privilege checks.
The user explicitly specifies no privilege checking by, for example, calling either %Prepare() with the checkPriv argument set to 0 or %ExecDirectNoPriv() on a %SQL.StatementOpens in a new tab.