Embedded SQL

Caché SQL supports the ability to embed SQL statements within Caché ObjectScript code. These embedded SQL statements are converted to optimized, executable code at compilation time.

Note:

Embedded SQL is not supported within Basic; To use SQL within Basic code, you can do one of the following: use Dynamic SQL or use embedded SQL with Caché ObjectScript methods and call these methods from Basic.

You can used embedded SQL within methods (provided that they are defined to use Caché ObjectScript) or within Caché ObjectScript .MAC routines. A .MAC routine (or a method using Caché ObjectScript) is processed by the Caché Macro Preprocessor and converted to .INT (intermediate) code which is subsequently compiled to executable code. The Macro Preprocessor replaces all embedded SQL statements it finds with the code that actually executes the SQL statement.

The syntax and use of embedded SQL is described below.

Embedded SQL statements are set off from the rest of the code by the &sql() directive. For example:

Method CountStudents() As %Integer
{
    &sql(SELECT COUNT(*) INTO :count 
        FROM MyApp.Student)
    Quit count
}

The &sql directive is case-insensitive; you can use &sql, &SQL, &Sql, etc. The &sql directive must be immediately followed by an open parenthesis.

The body of an &sql directive should contain a valid SQL statement, enclosed in parentheses. You can format your SQL statements in any way you like: white space and new lines are ignored by SQL. Caché Studio recognizes the &sql directive and uses a SQL-aware colorizer to syntax color SQL statements.

When the Macro Preprocessor encounters an &sql directive, it hands the enclosed SQL statement to the SQL Query Processor. The Query Processor returns the code needed (in Caché ObjectScript INT format) to execute the query). The Macro Preprocessor then replaces the &sql directive with this code (or a call to a label containing the code). From within Caché Studio, you can view the generated code, if you like, by looking at the INT code generated for a class or routine (using the View Other command in the View menu).

If an &sql directive contains an invalid SQL statement (it may have syntax errors or refer to tables or columns that do not exist at compile time), then the Macro Preprocessor will generate a compilation error.

Tables names within embedded SQL statement may include a schema name or not. If not, the table name is resolved (the correct schema name is found) using the Import statements in effect for the class or routine that contains the SQL statement.

See the Packages chapter in the Caché Objects Programming Guide for more details.

Embedded SQL queries may contain literal values (strings, numbers, or dates). Strings should be enclosed within single (') quotes (double quotes indicate delimited identifiers within SQL):

 &sql(SELECT 'Dr.' || Name INTO :name 
        FROM MyApp.Doctor
        WHERE State = 'NY')

Numeric values can be used directly:

 &sql(SELECT Name INTO :name 
        FROM MyApp.Person
        WHERE Age > 50)

Embedded SQL statements can include host variables in any place that a literal value can be used or within an INTO clause. A host variable is the name of a local variable, preceded by a “:” character, that provides (or receives) any values used by (or returned by) an SQL statement.

Host variable values have the following restrictions:

For further details, refer to Host variable in the Caché SQL Reference.

In the following example, an embedded SQL statement finds the name of a Person and returns it into the local variable name in the INTO clause:

 &sql(SELECT Name INTO :name 
        FROM MyApp.Person
        WHERE %ID = 1)

You can use host variables within the WHERE clause as well:

 Set minval = 10000
 Set maxval = 50000        
 &sql(SELECT Name,Salary INTO :name, :salary
        FROM MyApp.Employee
        WHERE Salary > :minval AND Salary < :maxval)

You can use arrays as host variables:

 &sql(SELECT Name, Title INTO :val(1), :val(2)
        FROM MyApp.Employee
        WHERE %ID = :emp("ID") )

In Caché 5.0 and all subsequent releases, you can also use object properties as host variables:

 &sql(SELECT Name, Title INTO :obj.Name, :obj.Title
        FROM MyApp.Employee
        WHERE %ID = :id )

In this case, obj must be a valid reference to an object that has mutable (that is, they can be modified) properties Name and Title.

Within embedded SQL, data values are in “logical mode”; that is, values are in the native format used by the SQL Query Processor. For string, integers, and other data types that do not define a LogicalToODBC or LogicalToDisplay conversion, this has no effect. The main case where data format matters is with date data types. The %Date and %Time data types provided by Caché use Caché's internal date representation ($HOROLOG format) as their logical format. The %TimeStamp data type uses ODBC date-time format (YYYY-MM-DD HH:MM:SS) for it logical, display, and ODBC format.

For example, consider the following class definition:

Class MyApp.Patient Extends %Persistent [ClassType = persistent]
{
/// Patient name
Property Name As %String(MAXLEN = 50);
/// Date of birth
Property DOB As %String;
/// Date and time of last visit
Property LastVisit As %TimeStamp;
}

A simple embedded SQL query against this table will return values in logical mode. For example, this query:

 &sql(SELECT Name, DOB, LastVisit
        INTO :name, :dob, :visit
         FROM Patient
        WHERE %ID = :id)

will return logical value for the three properties into the host variables name, dob, and visit:

Host Variable	Value
name	"Weiss,Blanche"
dob	44051
visit	"2001-03-15 11:11:00"

Note that dob is in $Horolog format. You can convert this to a display format using the $ZDateTime function:

 Set dob = 44051
 Write $ZDT(dob,3),!

The same consideration as true within a WHERE clause. For example, to find all Patients with a given birthday, you must use a logical value in the WHERE clause:

 &sql(SELECT Name INTO :name
        FROM Patient
        WHERE DOB = 43023)

or, alternatively, using a host variable:

 Set dob = $ZDH("01/02/1999",1)
 &sql(SELECT Name INTO :name
        FROM Patient
        WHERE DOB = :dob)

In this case, we use the $ZDATEH function to convert a display format date into its logical, $HOROLOG equivalent.

You can use a simple SQL statement (a single embedded SQL statement) for a variety of operations including:

Simple SQL statements are also referred to as non-cursor–based SQL statements.

For example, the following statement finds the name of the (one and only) Patient with ID of 43:

 &sql(SELECT Name INTO :name
    FROM Patient
    WHERE %ID = 43)

If you use a simple statement for a query that can return multiple rows, then only the first row is returned:

 &sql(SELECT Name INTO :name
    FROM Patient
    WHERE Age = 43)

Depending on the query, there is no guarantee which row will actually be returned first.

If you wish to use embedded SQL to execute a query that returns multiple rows, then you must use an SQL cursor. An SQL Cursor is DECLAREd and given a name. You then use this name to OPEN, FETCH data from, and CLOSE the cursor.

A cursor name must be unique within a class or routine. The DECLARE statement must occur within a routine before any statements that use the cursor.

The following example, uses a cursor to execute a query and display the results to the principal device:

 &sql(DECLARE C1 CURSOR FOR
    SELECT %ID,Name
    INTO :id, :name
    FROM Sample.Person
    ORDER BY Name
 )
 &sql(OPEN C1)
 &sql(FETCH C1)
 While (SQLCODE = 0) {
     Write id, ":  ", name,!        
    &sql(FETCH C1)
 }
    
 &sql(CLOSE C1)

This example does the following:

The DECLARE statement specifies both the name and SQL SELECT statement that defines the cursor. For example, the following statement declares a cursor called MyCursor:

 &sql(DECLARE MyCursor CURSOR FOR
    SELECT Name, DOB
    FROM Sample.Person
    WHERE Home_State = :state
    ORDER BY Name
 )

A DECLARE statement may include an optional INTO clause that specifies the names of the local host variables that will receive data as the cursor is traversed. For example, we can add an INTO clause to the previous example:

 &sql(DECLARE MyCursor CURSOR FOR
    SELECT Name, DOB
    INTO :name, :dob
    FROM Sample.Person
    WHERE Home_State = :state
    ORDER BY Name
 )

The number of host variables within an INTO clause must exactly match the number of columns within the cursor's SELECT list or you will receive a “Cardinality Mismatch” error when the statement is compiled.

If the DECLARE statement does not include an INTO clause, then the INTO clause must appear within the FETCH statement (q.v.).

The OPEN statement prepares a cursor for subsequent execution:

 &sql(OPEN MyCursor)

Upon a successful call to OPEN, the SQLCODE variable will be set to 0.

You cannot FETCH data from a cursor without first calling OPEN.

Depending on the actual query used for the cursor, the OPEN statement may do very little actual work or it may perform some initialization work for the query.

The FETCH statement fetches the data for the next row of the cursor (as defined by the cursor query):

 &sql(FETCH MyCursor)

You must DECLARE and OPEN a cursor, before you can call FETCH on it.

A FETCH statement may contain an INTO clause that specifies the names of the local host variables that will receive data as the cursor is traversed. For example, we can add an INTO clause to the previous example:

 &sql(FETCH MyCursor INTO :a, :b)

If both the DECLARE and FETCH statements contain an INTO clause, the host variables specified by the FETCH statement will be used.

Upon a successful call to FETCH, the SQLCODE variable will be set to 0; if there is no more data to FETCH, then SQLCODE will be set to 100 (No more data).

Depending on the query, the first call to FETCH may perform additional tasks (such as sorting values within a temporary data structure).

The CLOSE statement terminates the execution of a cursor:

 &sql(CLOSE MyCursor)

The CLOSE statement cleans up any temporary storage used by the execution of a query. Programs that fail to call CLOSE will experience resource leaks (such as unneeded increase of the CACHETEMP temporary database).

Upon a successful call to CLOSE, the SQLCODE variable will be set to 0.

Embedded SQL statements set, as a side effect, the variable SQLCODE to indicate success or failure. An SQLCODE of 0 indicates success. The various SQL error codes are listed in the SQL Error Code reference.

Embedded SQL statements set, as a side effect, the variable %ROWCOUNT to indicate the number of rows affected by a particular statement.

For example, the following code updates the salaries of all employees making less than $50000 per year and prints out the number of employees effected by the change:

 &sql(UPDATE MyApp.Employee 
     Set Salary = (Salary * 1.1)
     WHERE Salary < 50000)
 Write "Employees: ", %ROWCOUNT,!

Keep in mind that all embedded SQL statements (within a given process) modify the %ROWCOUNT variable. If you need the value provided by %ROWCOUNT, be sure to get its value before executing additional embedded SQL statements.

Also note that explicitly rolling back a transaction will not affect the value of %ROWCOUNT. For example, the following will report that changes have been made, even though they have been rolled back:

 TSTART // start an explicit transaction
 &sql(UPDATE MyApp.Employee 
     Set Salary = (Salary * 1.1)
     WHERE Salary < 50000)
 TROLLBACK // force a rollback; this will NOT modify %ROWCOUNT
 Write "Employees: ", %ROWCOUNT,!

Implicit transactions (such as if an UPDATE fails a constraint check) are reflected by %ROWCOUNT.

Embedded SQL INSERT statements set, as a side effect, the variable %ROWID to indicate the value of the row ID (Object ID) assigned to the new row.

For example, the following code inserts a new Person into the database:

 &sql(INSERT INTO Sample.Person 
    (Name,SSN) 
    VALUES ('Swift,Jonathan','111-22-3333'))
 If (SQLCODE = 0) {
     Write "New Person inserted with ID: ", %ROWID,!
 }

Within embedded SQL, the SQL NULL value is represented as an empty string within a host variable. For example, suppose that the Person with ID of 43 has a NULL (never specified) Occupation. After executing this query:

 &sql(SELECT Name, Occupation
    INTO :name, :occupation
    FROM Person
    WHERE %ID = 43)

The host variable, occupation, will be set to "" (an empty string) to indicate a NULL value.

In the rare case that Occupation contains an empty string (such as if an application explicitly set the field to an empty string), then the host variable will contain the special marker value, $C(0) (a string containing only a single, ASCII 0 character).

Note that this NULL behavior is only true within host-mode queries; within ODBC and JDBC, NULL values are represented using the special values defined by the ODBC and JDBC specifications.