Creates a query.
Synopsis
CREATE QUERY queryname(parameter_list) characteristics language
code_body
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.
The name of the query to be created. This name may be unqualified (StoreName) or qualified by specifying the class name (Patient.StoreName). The name of the query must be followed by parentheses.
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):
-
A keyword specifying whether the parameter mode is IN (input value), OUT (output value), or INOUT (modify value).
-
-
The datatype of the parameter.
-
Optional: The keyword DEFAULT followed by a default value for the parameter, specified as a string enclosed in single quotes.
If you specify a method keyword (such as PRIVATE or RETURNS) that is not valid for a query, Caché generates an SQLCODE -47 error. Specifying duplicate
characteristics results in a 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 local variable %Runtime. This can be set using the %Library.ResultSet RuntimeMode property. The RUNTIME mode default is LOGICAL. The value that you specify for SELECTMODE is added at the beginning of the Caché ObjectScript class method code as: #SQLCompile SELECT=
mode. For further details, see
#SQLCompile 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)”.
A keyword clause specifying the language you are using for
code_body. Permitted clauses are LANGUAGE OBJECTSCRIPT (for Caché 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.SQLQuery is generated. If the LANGUAGE is OBJECTSCRIPT, a class query of type %Library.Query is generated.
The program code for the query to be created. You specify this code in either SQL or Caché ObjectScript. The language used must match the LANGUAGE clause. However, code specified in Caché 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 and FETCH methods provided by Caché, and may contain CLOSE, FetchRows, FetchODBC, and GetInfo method calls. Caché ObjectScript code is enclosed in curly braces. If EXECUTE or FETCH are missing, an SQLCODE -46 error is generated upon compilation.
If the Caché 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 (for example, the ODBC server). This procedure context handler is automatically generated by Caché (as %qHandle:%SQLProcContext) using the
%sqlcontext object.
The
%sqlcontext object consists of the SQLCODE error status, the SQL row count, and a message, as follows:
SET %sqlcontext.SQLCode=SQLCODE
SET %sqlcontext.RowCount=%ROWCOUNT
SET %sqlcontext.Message=%MSG
There is no need to do anything with these values, but their values will be interpreted by the client. The
%sqlcontext object is reset prior to each execution.
Caché uses the code you supply to generate the actual code of the query.
The following embedded SQL example creates a query named PersonState. It declares no parameters, sets the SELECTMODE
characteristic, and takes the default (SQL) for
language:
WRITE !,"Creating a query"
&sql(CREATE QUERY PersonState() SELECTMODE RUNTIME
BEGIN
SELECT Name,Home_State FROM Sample.Person ;
END)
IF SQLCODE=0 { WRITE !,"Created a query" }
ELSEIF SQLCODE=-361 { WRITE !,"Query already exists" }
ELSE { WRITE !,"SQL error: ",SQLCODE }
You can go to the System Management Portal, select the
Classes option, then select the SAMPLES namespace. There you will find the query created by the above example:
User.queryPersonState.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 example creates a method-based query named SQLCODEList which fetches a list of SQLCODEs and their descriptions. It sets a RESULTS result set characteristic, sets
language as Caché ObjectScript, and calls the EXECUTE, FETCH, and CLOSE methods:
CREATE QUERY SQLCODEList()
RESULTS("SQLCODE" SMALLINT,Description VARCHAR(100))
FOR %Library.SQLCatalog
PROCEDURE
LANGUAGE OBJECTSCRIPT
EXECUTE(INOUT QHandle BINARY(255))
{
SET QHandle=1,%i(QHandle)=""
QUIT ##lit($$$OK)
}
FETCH(INOUT QHandle BINARY(255), INOUT Row BINARY(255), 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)
}