Dynamic SQL

Caché offers support for dynamic SQL—queries that are prepared and executed at runtime using the %Library.ResultSet class. Dynamic SQL lets you program within Caché in the same way that you would within an ODBC or JDBC application (except that you are executing the query within the same process context as the database engine).

For example, the following Basic code prepares and executes a dynamic SQL statement:

result = New %Library.ResultSet()
result.Prepare("SELECT Name,SSN FROM Sample.Person ORDER BY Name")
result.Execute()
While (result.Next())
    PrintLn result.Data("Name") & ", " & result.Data("SSN")
Wend

The operation of dynamic SQL is described below.

Dynamic SQL versus Embedded SQL

Dynamic SQL differs from embedded SQL in the following ways:

Dynamic SQL queries are prepared at program execution time, not compilation time. This means that the compiler cannot check for errors at compilation time. It also means that programs can create specialized queries in response to user or other input.
Dynamic SQL executes slightly less efficiently than embedded SQL as it does not generate in-line code for queries.
Input parameters within dynamic SQL are indicated using the “?” character; embedded SQL uses host variables (for example, :var).
Output values from dynamic SQL is retrieved using the API of the %Library.ResultSet object (that is, the Data property); embedded SQL uses host variables (for example, :var).
Dynamic SQL can be used within Basic as well as Caché ObjectScript.
Dynamic SQL provides an easy way to find query meta-information (such as quantity and names of columns).
Queries prepared by dynamic SQL are maintained within the Query Cache so that subsequent calls to prepare the same query can reuse previously generated code. Embedded SQL generated inline code at compilation time and does not need to use the query cache.

Dynamic SQL and embedded SQL use the same data representation (logical mode by default, but this can be changed) and NULL handling.

The %Library.ResultSet Class

Dynamic SQL is supported via the %Library.ResultSet class. Applications create an instance of the %Library.ResultSet class and use it to prepare, execute, and iterate over queries.

Creating a %Library.ResultSet Object

Within Basic you can create an instance of the %Library.ResultSet class using the New command:

result = New %Library.ResultSet()

Alternatively, you can do the same within Caché ObjectScript:

 Set result = ##class(%Library.ResultSet).%New()

At this point the %Library.ResultSet object is ready to prepare an SQL statement.

Preparing a Query

You can prepare an SQL statement using the Prepare method of the %Library.ResultSet class. This takes, as its first argument, a string containing an SQL query to prepare for subsequent execution. For example, in Basic:

result.Prepare("SELECT Name FROM MyApp.Person ORDER BY Name")

The Prepare method returns a %Status value indicating success (the query string contains a valid SQL statement) or failure (it does not).

You can specify input parameters (variables for use within a WHERE clause) by using the “? ”character:

result.Prepare("SELECT Name FROM MyApp.Employee WHERE Salary > ?")

The value for each input parameter is specified when the query is executed (see the next section).

You are not limited to SELECT statements within dynamic SQL: you can use the Prepare method to prepare other statements, such as DDL, INSERT, UPDATE, or DELETE statements.

Finding Meta-information about a Query

After preparing a query, you can find meta-information about the query by using the methods of the %Library.ResultSet class. These methods include:

Method	Description
GetColumnCount	Returns the number of columns in the query.
GetColumnName	Returns the name of a specific column, indexed by column number.
GetParamCount	Returns the number of input parameters for the query.

Executing a Query

After preparing a query, you can execute it by calling the Execute method of the %Library.ResultSet class. In the case of a non-SELECT statement, Execute invokes the desired operation (such as performing an INSERT). In the case of a SELECT query, Execute prepares the query for subsequent traversal and data retrieval.

The Execute method takes a variable number of arguments that correspond to any input parameters (indicated by “?”) within the prepared SQL statement. The input parameters correspond to the order in which the “?” characters appear within the SQL statement: the first argument is used for the first “?” and so on.

For example, the following Basic code demonstrates the execution of a query containing 2 input parameters:

result = New %Library.ResultSet()
sql = "SELECT Name FROM MyApp.Employee WHERE Salary > ? AND Salary < ?"
' prepare the query
result.Prepare(sql)
'find everyone with salary between 10000 and 20000
result.Execute(10000,20000)

The Execute method returns a %Status value indicating success or failure.

Fetching Data from a Query

You can iterate over the results of a SELECT query using the Next method of the %Library.ResultSet class:

While (result.Next())
    PrintLn result.Data("Name")
Wend

The Next method fetches the data for the next row within the query results and places this data in the Data property of the %Library.ResultSet object. The Next method returns a true value as long as there are additional rows to fetch; it returns false when it reaches the end of the query results.

The Data property is an array, subscripted by column name, containing the data values fetched for the current row.

The column name is specified by the SQL query. If you have multiple columns with the same name, then you cannot retrieve them both via the Data property. Instead you should provide unique column names by using aliases within your SQL statement:

result = New %Library.ResultSet()
' create the query string
sql = "SELECT A.Name As AName, B.Name As BName "
sql = sql & "FROM MyApp.Table1 A, MyApp.Table2 B "
sql = sql & "WHERE A.Code = B.Code"
' prepare the query
result.Prepare(sql)

The %Library.ResultSet class also provides the GetData method for getting the data for the current row indexed by column number.

Closing a Query

When you are done with a dynamic SQL query you can close it (release any resources used by the query) by either a) destroying the %Library.ResultSet object (such as letting it go out of scope) or b) explicitly calling the Close method of the %Library.ResultSet class:

result.Close()

Calling the Close method allows you to execute and fetch from the same query without having to re-prepare it.

The Query Cache

Caché automatically maintains a cache of frequently used dynamic SQL queries (those created via ODBC, JDBC, or Dynamic SQL).

When you first prepare a dynamic SQL query, the SQL Engine optimizes it and generates a program (a set of one or more Caché routines) that will execute the query. The query text is then placed in the query cache. If you subsequently attempt to prepare the same (or a similar) query, the SQL Engine will find it in the cache and directly execute the code for the query, bypassing the need to optimize and code generate.

The query cache provides the following benefits:

Subsequent execution of frequently used queries is faster. More importantly, this performance boost is available automatically without having to code cumbersome stored procedures. Most relational database products recommend using only stored procedures for database access. This is not necessary with Caché.
The query cache is shared among all database users; if User 1 prepares a query, then User 1023 can take advantage of it.
The Query Optimizer is free to use more time to find the best solution for a given query as this price only has to be paid the first time a query is prepared.

Whenever you modify (alter or delete) a table, any queries based on that table are automatically purged from the query cache.

You can view (and manage) the contents of the query cache using the Caché System Management Portal.

There is no need for a cache for Embedded SQL as embedded SQL statements are replaced with inline code at compilation time.