Using Caché SQL
Using Embedded SQL
[Back] [Next]
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

You can embed SQL statements within Caché ObjectScript code. These Embedded SQL statements are converted to optimized, executable code at compilation time. This chapter discusses the following topics:

Note:
Embedded SQL cannot be input to the Caché Terminal command line, or specified in an XECUTE statement. To execute SQL from the command line, either use the $SYSTEM.SQL.Execute() method or the SQL Shell interface.
Embedded SQL is not supported within Caché Basic. To execute SQL within Basic code, you can do either of the following: use Dynamic SQL, or use Embedded SQL within Caché ObjectScript methods and then call these ObjectScript methods from Basic.
Compiling Embedded SQL and the Macro Preprocessor
You can use 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 Embedded SQL statements with the code that actually executes the SQL statement.
If an Embedded SQL statement itself contains Caché Macro Preprocessor statements (# commands, ## functions, or $$$macro references) these statements are compiled before the SQL code. The may affect CREATE PROCEDURE, CREATE FUNCTION, CREATE METHOD, CREATE QUERY, or CREATE TRIGGER statements that contain a Caché ObjectScript code body.
An Embedded SQL statement must be able to access all resources necessary for its compilation. If an Embedded SQL statement references a class external to its compilation unit, and that class references data items defined by an #include file, the compilation unit that contains the Embedded SQL statement must also reference the same #include file. For further details, refer to the ObjectScript Macros and the Macro Preprocessor chapter of Using Caché ObjectScript.
The Macro Preprocessor provides three preprocessor directives for use with Embedded SQL:
For further details on these preprocessor directives, refer to the Preprocessor Directives Reference section of Using Caché ObjectScript.
Recompilation Required following Change to Dependent Class
In Embedded SQL, you must recompile a class or routine that references a persistent class if that persistent class is changed.
For example, Class A contains a method with an Embedded SQL query, and that query references persistent Class B. After Class A has been compiled, Class B gets modified (for example, a new property is added to Class B). Class A now needs to be recompiled. The same is true for Routine A that references persistent Class B.
Embedded SQL Syntax
The syntax of the Embedded SQL directive is described below.
The &sql Directive
Embedded SQL statements are set off from the rest of the code by the &sql() directive, as shown in the following example:
   WRITE "Invoking Embedded SQL",!
   &sql(SELECT Name INTO :a FROM Sample.Person)
   WRITE "The SQL error code is ",SQLCODE,!
   WRITE "The name is ",a
 
Results are returned using the INTO clause specifying one or more host variables. In this case, the host variable is named :a. For further details, see the Host Variables section of this chapter, which includes information on interactions between SQLCODE and host variables.
The following example shows Embedded SQL within a method:
Method CountStudents() As %Integer
{
    &sql(SELECT COUNT(*) INTO :count 
        FROM MyApp.Student)

    Quit count
}
The &sql directive is not case-sensitive; you can use &sql, &SQL, &Sql, and so on. The &sql directive must be followed by an open parenthesis, with no intervening spaces, line breaks, or comments. The &sql directive can be used on the same line as a label, as shown in the following example:
Mylabel  &sql(
       SELECT Name INTO :a FROM Sample.Person
       )
  WRITE "The name is ",a
 
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 an SQL-aware colorizer to syntax color the SQL code 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 Code option from the View menu).
If an &sql directive contains an invalid SQL statement, the Macro Preprocessor generates a compilation error. An invalid SQL statement may have syntax errors, or refer to tables or columns that do not exist at compile time.
An &sql directive can contain SQL-style comments anywhere within its parentheses, can contain no SQL code, or contain only comment text. If an &sql directive contains no SQL code or only commented text, the directive is parsed as a no-op and the SQLCODE variable is not defined.
  NEW SQLCODE
  WRITE !,"Entering Embedded SQL"
  &sql()
  WRITE !,"Leaving Embedded SQL"
 
  NEW SQLCODE
  WRITE !,"Entering Embedded SQL"
  &sql(/* SELECT Name INTO :a FROM Sample.Person */)
  WRITE !,"Leaving Embedded SQL"
 
&sql Alternative Syntax
Because complex Embedded SQL programs may contain multiple &sql directives — including nested &sql directives — the following alternative syntax formats are provided:
&sql Marker Syntax
You can identify a specific &sql directive using user-defined marker syntax. This syntax consists of a character or string specified between “&sql” and the open parenthesis character. The reverse of this marker must appear immediately after the closing parenthesis at the end of the Embedded SQL. The syntax is as follows:
  &sql<marker>( SQL statement )<reverse-marker>
Note that no white space (space, tab, or line return) is permitted between &sql, marker, and the open parenthesis, and no white space is permitted between the closing parenthesis and reverse-marker.
A marker can be a single character or a series of characters. A marker cannot contain the following punctuation characters:
( + - / \ | * )
A marker cannot contain a whitespace character (space, tab, or line return). It may contain all other printable characters and combinations of characters, including Unicode characters. The marker and reverse-marker are case sensitive.
The corresponding reverse-marker must contain the same characters as marker in the reverse order. For example: &sqlABC( ... )CBA. If marker contains a [ or { character, reverse-marker must contain the corresponding ] or } character. The following are examples of valid &sql marker and reverse-marker pairs:
  &sql@@( ... )@@
  &sql[( ... )]
  &sqltest( ... )tset
  &sql[Aa{( ... )}aA]
When selecting a marker character or string, note the following important SQL restriction: the SQL code cannot contain the character sequence “)<reversemarker>” anywhere in the code, including in literal strings and comments. For example, if the marker is “ABC”, the character string “)CBA” cannot appear anywhere in the Embedded SQL code. If this occurs, the combination of a valid marker and valid SQL code will fail compilation. Thus it is important to use care in selecting a marker character or string to prevent this collision.
Embedded SQL and Line Offsets
The presence of Embedded SQL affects ObjectScript line offsets, as follows:
Embedded SQL Code
Considerations for writing SQL code in Embedded SQL include the following:
Host variables, which are used to export data values from Embedded SQL are described later in this chapter.
Simple SQL Statements
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. Cursor-based Embedded SQL is described later in this chapter.
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. Also, if a query includes an INTO statement and no data is returned (that is, that SQLCODE is 100), then executing the query may result in the value of the host variable being modified.
Schema Name Resolution
A table name, view name, or stored procedure name either specifies a schema name (is fully qualified) or does not specify a schema name (is unqualified). If the name does not specify a schema name, Caché resolves the schema name by searching for an existing schema name or creating a new schema name in the current namespace. A schema name is supplied using one or more of the following:
Note that the #Import and #SQLCompile Path directives are mutually independent lists of possible schema names with different functionality. Either or both may be used to supply a schema name for an unqualified table, view, or stored procedure name.
See the chapter Packages in Using Caché Objects for more details on schemas.
Literal Values
Embedded SQL queries may contain literal values (strings, numbers, or dates). Strings should be enclosed within single (') quotes. (In Caché SQL, double quotes indicate a delimited identifier):
  &sql(SELECT 'Employee (' || Name || ')' INTO :name 
       FROM Sample.Employee)
  WRITE name
 
Numeric values can be used directly. Literal numbers and timestamp values are “lightly normalized” before Caché compares these literal values to field values, as shown in the following example where +0050.000 is normalized to 50:
  &sql(SELECT Name,Age INTO :name,:age 
       FROM Sample.Person
       WHERE Age = +0050.000)
  WRITE name," age=",age
 
Arithmetic, function, and special variable expressions can be specified:
  &sql(DECLARE C1 CURSOR FOR 
       SELECT Name,Age-65,$HOROLOG INTO :name,:retire,:today 
       FROM Sample.Person
       WHERE Age > 60
       ORDER BY Age,Name)
  &sql(OPEN C1)
  &sql(FETCH C1)
  WHILE (SQLCODE = 0) {
     WRITE $ZDATE(today)," ",name," has ",retire," eligibility years",!
    &sql(FETCH C1) }
  &sql(CLOSE C1)
 
You can also input a literal value using an input host variable. Input host numeric values are also “lightly normalized.” For further details, see the Host Variables section of this chapter.
In Embedded SQL, a few character sequences that begin with ## are not permitted within a string literal and must be specified using ##lit. These character sequences are: ##;, ##beginlit, ##expression(, ##function(, ##quote(, ##stripq(, and ##unique(. For example, the following example fails:
  WRITE "Embedded SQL test",!
  &sql(SELECT 'the sequence ##unique( is restricted' INTO :x)
  WRITE x
The following workaround succeeds:
  WRITE "Embedded SQL test",!
  &sql(SELECT 'the sequence ##lit(##unique() is restricted' INTO :x)
  WRITE x
 
Data Format
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. Data format affects %List data, and the %Date and %Time data types.
The %List data type displays in Logical mode as element values prefaced with non-printing list encoding characters. The WRITE command displays these values as concatenated elements. For example, the FavoriteColors field of Sample.Person stores data in %List data type, such as the following: $LISTBUILD('Red','Black'). In Embedded SQL this displays in Logical mode as RedBlack, with a length of 12 characters. In Display mode it displays as Red Black; in ODBC mode it displays as Red,Black. This is shown in the following example:
  &sql(DECLARE C1 CURSOR FOR
       SELECT TOP 10 FavoriteColors INTO :colors
       FROM Sample.Person WHERE FavoriteColors IS NOT NULL)
  &sql(OPEN C1)
  &sql(FETCH C1)
  WHILE (SQLCODE = 0) {
     WRITE $LENGTH(colors),": ",colors,!
    &sql(FETCH C1) }
  &sql(CLOSE C1)
 
The %Date and %Time data types provided by Caché use the Caché internal date representation ($HOROLOG format) as their Logical format. A %Date data type returns INTEGER data type values in Logical mode; VARCHAR data type values in Display mode, and DATE data type values in ODBC mode. The %TimeStamp data type uses ODBC date-time format (YYYY-MM-DD HH:MM:SS) for its Logical, Display, and ODBC format.
For example, consider the following class definition:
Class MyApp.Patient Extends %Persistent
{
/// Patient name
Property Name As %String(MAXLEN = 50);

/// Date of birth
Property DOB As %Date;

/// 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, consider the following query:
 &sql(SELECT Name, DOB, LastVisit
        INTO :name, :dob, :visit
         FROM Patient
        WHERE %ID = :id)
This query returns 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 a Patient 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.
Privilege Checking
Embedded SQL does not perform SQL privilege checking. You can access all tables, views, and columns and perform any operation, regardless of the privileges assignments. It is assumed that applications using Embedded SQL will check for privileges before using Embedded SQL statements.
You can use the Caché SQL %CHECKPRIV statement in Embedded SQL to determine the current privileges.
For further details, refer to the Users, Roles, and Privileges chapter of this manual.
Host Variables
A host variable is a local variable that passes a literal value into or out of Embedded SQL. Most commonly, host variables are used to either pass a value set as a local variable in Caché ObjectScript into Embedded SQL code, or (using the INTO clause) pass a query value from Embedded SQL to Caché ObjectScript as a local variable. A host variable cannot be used to pass a field name or keyword into an SQL statement.
Note:
Output host variables are only used in Embedded SQL. Input host variables can be used in either Embedded SQL or Dynamic SQL. In Dynamic SQL, you can also input a literal to an SQL statement using the “?” input parameter.
Caché Basic does not support Embedded SQL. Either use Dynamic SQL to perform SQL operations from Caché Basic, or have Caché Basic call a Caché ObjectScript routine that contains Embedded SQL.
Within Embedded SQL, input host variables can be used in any place that a literal value can be used. Output host variables are specified using an INTO clause of a SELECT or FETCH statement.
To use a variable or a property reference as a host variable, precede it with a colon (:). A host variable in embedded Caché SQL can be one of the following:
Host variables should be listed in the Caché ObjectScript procedure’s PublicList variables list and reinitialized using the NEW command. You can configure Caché to also list all host variables used in Embedded SQL in comment text; this is described in the Comment section of Using Caché SQL.
Host variable values have the following behavior:
When using a comma-separated list of host variables in the INTO clause, you must specify the same number of host variables as the number of select-items (fields, aggregate functions, scalar functions, arithmetic expressions, literals). Too many or too few host variables results in an SQLCODE -76 cardinality error upon compilation.
This is often a concern when using SELECT * in Embedded SQL. For example, SELECT * FROM Sample.Person is only valid with a comma-separated list of 15 host variables (the exact number of non-hidden columns, which, depending on the table definition, may or may not include the system-generated ID (RowId) column). Note that this number of columns may not be a simple correspondence to the number of properties listed in the InterSystems Class Reference.
Because the number of columns can change, it is usually not a good idea to specify SELECT * with an INTO clause list of individual host variables. When using SELECT *, it is usually preferable to use a host variable subscripted array, such as the following:
   NEW SQLCODE
  &sql(SELECT * INTO :tflds() FROM Sample.Person )
   IF SQLCODE=0 {
     FOR i=0:1:25 { 
       IF $DATA(tflds(i)) {
       WRITE "field ",i," = ",tflds(i),! }
     } }
   ELSE {WRITE "SQLCODE=",SQLCODE,! }
 
Note that in this example the field number subscripts are not a continuous sequence; some fields in Sample.Person are hidden and return no data in this example. Using a host variable array is described in Host Variable Subscripted by Column Number, below.
It is good programming practice to check the SQLCODE value immediately after exiting Embedded SQL. Output host variable values should only be used when SQLCODE=0.
Host Variable Examples
In the following Caché ObjectScript example, an Embedded SQL statement uses output host variables to return a name and home state address from an SQL query to Caché ObjectScript:
   &sql(SELECT Name,Home_State
        INTO :CName,:CAddr
        FROM Sample.Person)
   IF SQLCODE=0 {
      WRITE !,"Name is: ",CName
      WRITE !,"State is: ",CAddr
   }
   ELSE {
      WRITE !,"SQL error ",SQLCODE  
   }
 
The Embedded SQL uses an INTO clause that specifies the host variables :CName and :CAddr to return the selected customer’s name in the local variable CName, and home state in the local variable CAddr.
The following example performs the same operation, using subscripted local variables:
   &sql(SELECT Name,Home_State
        INTO :CInfo(1),:CInfo(2)
        FROM Sample.Person)
   IF SQLCODE=0 {
      WRITE !,"Name is: ",CInfo(1)
      WRITE !,"State is: ",CInfo(2)
   }
   ELSE {
      WRITE !,"SQL error ",SQLCODE  
   }
 
These host variables are simple local variables with user-supplied subscripts (:CInfo(1)). However, if you omit the subscript (:CInfo()), Caché populates the host variable subscripted array using SqlColumnNumber, as described below.
In the following Caché ObjectScript example, an Embedded SQL statement uses both input host variables (in the WHERE clause) and output host variables (in the INTO clause):
  SET minval = 10000
  SET maxval = 50000
  &sql(SELECT Name,Salary INTO :outname, :outsalary
       FROM MyApp.Employee
       WHERE Salary > :minval AND Salary < :maxval)
   IF SQLCODE=0 {
      WRITE !,"Name is: ",outname
      WRITE !,"Salary is: ",outsalary
   }
   ELSE {
      WRITE !,"SQL error ",SQLCODE
   }
The following example performs “light normalization” on an input host variable. Note that Caché treats the input variable value as a string and does not normalize it, but Embedded SQL normalizes this number to 65 to perform the equality comparison in the WHERE clause:
  SET x="+065.000"
  &sql(SELECT Name,Age
       INTO :a,:b
       FROM Sample.Person
       WHERE Age=:x)
  WRITE !,"Error code is: ",SQLCODE
  WRITE !,"Input value is: ",x
  WRITE !,"Name value is: ",a
  WRITE !,"Age value is: ",b
 
In the following Caché ObjectScript example, an Embedded SQL statement uses 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. Note that if a query includes an INTO statement and no data is returned (that is, that SQLCODE is 100), then executing the query may result in the value of the host variable being modified.
Host Variable Subscripted by Column Number
If the FROM clause contains a single table, you can specify a subscripted host variable for fields selected from that table; for example, the local array :myvar(). The local array is populated by Caché, using each field’s SqlColumnNumber as the numeric subscript. Note that SqlColumnNumber is the column number in the table definition, not the select-list sequence. (You cannot use a subscripted host variable for fields of a view.)
A host variable array must be a local array that has its lowest level subscript omitted. Therefore, :myvar(), :myvar(5,), and :myvar(5,2,) are all valid host variable subscripted arrays.
In the following example, the SELECT populates the Cdata array with the values of the specified fields. The elements of Cdata() correspond to the table column definition, not the SELECT elements. Therefore, the Name field is column 6, the Age field is column 2, and the date of birth (DOB) field is column 3 in Sample.Person:
   &sql(SELECT Name,Age,DOB
        INTO :Cdata()
        FROM Sample.Person)
   IF SQLCODE=0 {
      WRITE !,"Name is: ",Cdata(6)
      WRITE !,"Age is: ",Cdata(2)
      WRITE !,"DOB is: ",$ZDATE(Cdata(3),1)
     }
   ELSE {
      WRITE !,"SQL error ",SQLCODE  
   }
 
The following example uses a subscripted array host variable to return all of the field values of a row:
   &sql(SELECT * INTO :Allfields()
        FROM Sample.Person)
   IF SQLCODE=0 {
      SET x=1
      WHILE x '="" {
      WRITE !,x," field is ",Allfields(x)
      SET x=$ORDER(Allfields(x)) }
      }
   ELSE {
      WRITE !,"SQL error ",SQLCODE
   }
 
Note that this WHILE loop is incremented using $ORDER rather than a simple x=x+1. This is because in many tables (such as Sample.Person) there may be hidden columns. These cause the column number sequence to be discontinuous.
If the SELECT list contains items that are not fields from that table, such as expressions or arrow-syntax fields, the INTO clause must also contain comma-separated non-array host variables. The following example combines a subscripted array host variable to return values that correspond to defined table columns, and host variables to return values that do not correspond to defined table columns:
   &sql(SELECT Name,Home_City,{fn NOW},Age,($HOROLOG-DOB)/365.25,Home_State
        INTO :Allfields(),:timestmp('now'),:exactage
        FROM Sample.Person)
   IF SQLCODE=0 {
      SET x=$ORDER(Allfields(""))
      WHILE x '="" {
      WRITE !,x," field is ",Allfields(x)
      SET x=$ORDER(Allfields(x)) }
      WRITE !,"date & time now is ",timestmp("now")
      WRITE !,"exact age is ",exactage
      }
   ELSE {
      WRITE !,"SQL error ",SQLCODE
   }
 
Note that the non-array host variables must match the non-column SELECT items in number and sequence.
The use of a host variable as a subscripted array is subject to the following restrictions:
For further details on using a host variable array, refer to the INTO clause in the Caché SQL Reference.
NULL and Undefined Host Variables
If you specify an input host variable that is not defined, Embedded SQL treats its value as NULL.
   NEW x
   &sql(SELECT Home_State,:x
        INTO :a,:b
        FROM Sample.Person)
   IF SQLCODE=0 {
     WRITE !,"The length of Home_State is: ",$LENGTH(a)
     WRITE !,"The length of x is: ",$LENGTH(b) }
   ELSE {WRITE !,"The SQL error code is: ",SQLCODE }
 
The SQL NULL is equivalent to the Caché ObjectScript "" string (a zero-length string).
If you output a NULL to a host variable, Embedded SQL treats its value as the Caché ObjectScript "" string (a zero-length string). For example, some records in Sample.Person have a NULL Spouse field. After executing this query:
 &sql(SELECT Name,Spouse
    INTO :name, :spouse
    FROM Sample.Person
    WHERE Spouse IS NULL)
  IF SQLCODE=0 {
    WRITE !,"Name: ",name," of length ",$LENGTH(name)," defined: ",$DATA(name)
    WRITE !,"Spouse: ",spouse," of length ",$LENGTH(spouse)," defined: ",$DATA(spouse) }
  ELSE {WRITE !,"The SQL error code is: ",SQLCODE }
 
The host variable, spouse, will be set to "" (a zero-length string) to indicate a NULL value.
In the rare case that a table field contains an SQL zero-length string (''), such as if an application explicitly set the field to an SQL '' string, the host variable will contain the special marker value, $CHAR(0) (a string of length 1, containing only a single, ASCII 0 character), which is the Caché ObjectScript representation for the SQL zero-length string. Use of SQL zero-length strings is strongly discouraged.
The following example compares host variables output from an SQL NULL and an SQL zero-length string:
 &sql(SELECT '',Spouse
    INTO :zls, :spouse
    FROM Sample.Person
    WHERE Spouse IS NULL)
  IF SQLCODE=0 {
    WRITE "In Caché ObjectScript"
    WRITE !,"ZLS is of length ",$LENGTH(zls)," defined: ",$DATA(zls)
    WRITE !,"NULL is of length ",$LENGTH(spouse)," defined: ",$DATA(spouse) }
  ELSE {WRITE !,"The SQL error code is: ",SQLCODE }
 
Note that this host variable NULL behavior is only true within server-based queries (Embedded SQL and Dynamic SQL). Within ODBC and JDBC, NULL values are explicitly specified using the ODBC or JDBC interface.
Validity of Host Variables
For example, the following use of OutVal is not reliably valid:
InvalidExample
   SET InVal = "1234"
   SET OutVal = "?"
   &sql(SELECT Name
        INTO :OutVal
        FROM Sample.Person
        WHERE ID=:InVal)
   IF OutVal="?" {           ; Improper Use 
   WRITE !,"No data returned"
   WRITE !,"SQL error code ",SQLCODE }
   ELSE {
   WRITE !,"Name is: ",OutVal }
The value of OutVal set before invoking Embedded SQL should not be referenced by the IF command after returning from Embedded SQL.
Instead, you should code this example as follows, using the SQLCODE variable:
ValidExample
   SET InVal = "1234"
   &sql(SELECT Name
        INTO :OutVal
        FROM Sample.Person
        WHERE ID=:InVal)
   IF SQLCODE'=0 { SET OutVal="?" 
      IF OutVal="?" {
      WRITE !,"No data returned"
      WRITE !,"SQL error code ",SQLCODE } }
   ELSE {
   WRITE !,"Name is: ",OutVal }
 
The Embedded SQL sets the SQLCODE variable to 0 to indicate the successful retrieval of an output row. An SQLCODE value of 100 indicates that no row was found that matches the SELECT criteria. An SQLCODE negative number value indicates a SQL error condition.
Host Variables and Procedure Blocks
If your Embedded SQL is within a procedure block, all input and output host variables must be public. This can be done by declaring them in the PUBLIC section at the beginning of the procedure block, or by naming them with an initial % character (which automatically makes them public). You must also declare SQLCODE as public. For further details on the SQLCODE variable, see below.
In the following procedure block example, the host variables zip, city, and state, as well as the SQLCODE variable are declared as PUBLIC. The SQL system variables %ROWCOUNT, %ROWID, and %msg are already public, because their names begin with a % character. The procedure code then performs a NEW on SQLCODE, the other SQL system variables, and the state local variable:
UpdateTest(zip,city)
  [SQLCODE,zip,city,state] PUBLIC {
  NEW SQLCODE,%ROWCOUNT,%ROWID,%msg,state
  SET state="MA"
  &sql(UPDATE Sample.Person
       SET Home_City = :city, Home_State = :state
       WHERE Home_Zip = :zip)
  QUIT %ROWCOUNT
  }
SQL Cursors
A cursor is a pointer to data that allows an Embedded SQL program to perform an operation on the record pointed to. By using a cursor, Embedded SQL can iterate through a result set. Embedded SQL can use a cursor to execute a query that returns data from multiple records. Embedded SQL can also use a cursor to update or delete multiple records.
You must first DECLARE an SQL cursor, giving it a name. In the DECLARE statement you supply a SELECT statement that identifies which records the cursor will point to. You then supply this cursor name to the OPEN cursor statement. You then repeatedly issue the FETCH cursor statement to iterate through the SELECT result set. You then issue a CLOSE cursor statement.
Note that a cursor cannot span methods. Therefore, you must declare, open, fetch, and close a cursor within the same class method. It is important to consider this with all code that generates classes and methods, such as classes generated from a .CSP file.
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
    WHERE Name %STARTSWITH 'A'
    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:
  1. It declares a cursor, C1, that returns a set of Person rows ordered by Name.
  2. It opens the cursor.
  3. It calls FETCH on the cursor until it reaches the end of the data. After each call to FETCH, the SQLCODE variable will be set to 0 if there is more data to fetch. After each call to FETCH, the values returned are copied into the host variables specified by the INTO clause of the DECLARE statement.
  4. It closes the cursor.
The DECLARE Cursor Statement
The DECLARE statement specifies both the cursor name and the SQL SELECT statement that defines the cursor. The DECLARE statement must occur within a routine before any statements that use the cursor.
A cursor name must be unique within a class or routine. For this reason, a routine that is called recursively cannot contain a cursor declaration. In this situation, it may be preferable to use Dynamic SQL.
The following example declares a cursor named 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 INTO clause may contain a comma-separated list of host variables, a single host variable array, or a combination of both. If specified as a comma-separated list, the number of INTO clause host variables 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.
The OPEN Cursor Statement
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 Cursor Statement
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.
The INTO clause may contain a comma-separated list of host variables, a single host variable array, or a combination of both. If specified as a comma-separated list, the number of INTO clause host variables 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.
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 Cursor Statement
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 is set to 0. Therefore, before closing a cursor you should check whether the final FETCH set SQLCODE to 0 or 100.
Embedded SQL Variables
The following local variables have specialized uses in Embedded SQL. These local variable names are case-sensitive. At process initiation, these variables are undefined. They are set by Embedded SQL operations. They can also be set directly using the SET command, or reset to undefined using the NEW command. Like any local variable, a value persists for the duration of the process or until set to another value or undefined using NEW. For example, some successful Embedded SQL operations do not set %ROWID; following these operations, %ROWID remains set to its prior value.
These local variables are not set by Dynamic SQL. (Note that the SQL Shell and the Management Portal SQL interface execute Dynamic SQL.) Instead, Dynamic SQL sets corresponding object properties.
The following Caché ObjectScript special variables are used in Embedded SQL. These special variable names are not case-sensitive. At process initiation, these variables are initialized to a value. They are set by Embedded SQL operations. They cannot be set directly using the SET or NEW commands.
As part of the defined Caché Embedded SQL interface, Caché may set any of these variables during Embedded SQL processing.
If the Embedded SQL is in a class method (with ProcedureBlock=ON), Caché automatically places all of these variables in the PublicList and NEWs the SQLCODE, %ROWID, %ROWCOUNT, %msg, and all non-% variables used by the SQL statement. It does not NEW the %ok variable. You can pass these variables by reference to/from the method; variables passed by reference will not be NEWed automatically in the class method procedure block.
If the Embedded SQL is in a routine, it is the responsibility of the programmer to NEW the %msg, %ok, %ROWCOUNT, %ROWID, and SQLCODE variables before invoking Embedded SQL. NEWing these variables prevents interference with prior settings of these variables. To avoid a <FRAMESTACK> error, you should not perform this NEW operation within an iteration cycle.
%msg
A variable that contains a system-supplied error message string. Caché SQL only sets %msg if it has set SQLCODE to a negative integer, indicating an error. If SQLCODE is set to 0 or 100, the %msg variable is unchanged from its prior value.
This behavior differs from the corresponding Dynamic SQL %Message property, which is set to the empty string when there is no current error.
In some cases, a specific SQLCODE error code may be associated with more than one %msg string, describing different conditions that generated the SQLCODE. %msg can also take a user-defined message string. This is most commonly used to issue a specific message when an SQL error or trigger code sets %ok=0, aborting the trigger.
An error message string is generated in the NLS language in effect for the process when the SQL code is executed. The SQL code may be compiled in a different NLS language environment; the message will be generated according to the runtime NLS environment. See $SYS.NLS.Locale.Language.
%ok
A variable used in trigger code. When %ok is set to 0 (zero), the trigger code aborts and is rolled back. %ok can be set to zero explicitly by trigger code, or implicitly by Caché. If during trigger execution an SQLCODE error is issued, Caché sets %ok to zero.
%ok is unchanged from its prior value upon the completion of a non-trigger code SELECT, INSERT, UPDATE, or DELETE statement. %ok is only defined by the execution of trigger code.
%ROWCOUNT
An integer counter that indicates the number of rows affected by a particular statement.
This SELECT behavior differs from the corresponding Dynamic SQL %ROWCOUNT property, which is set to 0 upon completion of query execution, and is only incremented when the program iterates through the result set returned by the query.
The following Embedded SQL example declares a cursor and uses FETCH to fetch each row in the table. When the end of data is reached (SQLCODE=100) %ROWCOUNT contains the number of rows retrieved:
   SET name="LastName,FirstName",state="##"
   &sql(DECLARE EmpCursor CURSOR FOR 
        SELECT Name, Home_State
        INTO :name,:state FROM Sample.Person
        WHERE Home_State %STARTSWITH 'M')
   WRITE !,"BEFORE: Name=",name," State=",state
   &sql(OPEN EmpCursor)
   FOR { &sql(FETCH EmpCursor)
        QUIT:SQLCODE  
        WRITE !,"Row fetch count: ",%ROWCOUNT
        WRITE " Name=",name," State=",state
 }
   WRITE !,"Final Fetch SQLCODE: ",SQLCODE
   &sql(CLOSE EmpCursor)
   WRITE !,"AFTER: Name=",name," State=",state
   WRITE !,"Total rows fetched: ",%ROWCOUNT
 
The following Embedded SQL example performs an UPDATE and sets the number of rows affected 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. Depending on how Embedded SQL is invoked, you may have to NEW the %ROWCOUNT variable before entering Embedded SQL.
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
  NEW SQLCODE,%ROWCOUNT,%ROWID
 &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.
%ROWID
When you initialize a process, %ROWID is undefined. When you issue a NEW %ROWID command, %ROWID is reset to undefined. %ROWID is set by the Embedded SQL operations described below. If the operation is not successful, or completes successfully but does not fetch or modify any rows, the %ROWID value remains unchanged from its prior value: either undefined, or set to a value by a previous Embedded SQL operation. For this reason, it is important to NEW %ROWID before each Embedded SQL operation.
%ROWID is set to the RowID of the last row affected by the following operations:
In Dynamic SQL, the corresponding %ROWID property returns the RowID of the last record inserted, updated, or deleted. Dynamic SQL does not return a %ROWID property value when performing a SELECT query.
You can retrieve the current %ROWID from Caché ObjectScript using the following method call:
  WRITE $SYSTEM.SQL.GetROWID()
 
Following an INSERT, UPDATE, DELETE, TRUNCATE TABLE, or Cursor-based SELECT operation, the LAST_IDENTITY SQL function returns the value of the IDENTITY field for the most-recently modified record. If the table does not have an IDENTITY field, this function returns the RowID for the most-recently modified record.
SQLCODE
The SQLCODE variable contains the Caché SQL error code value. Successful completion values are 0 (successful completion) and 100 (the operation has either found no data or reached the end of the data). Error condition values are negative numbers. For a list of these error codes and additional information, refer to the SQLCODE Values and Error Messages chapter of the Caché Error Reference.
Depending on how Embedded SQL is invoked, you may have to NEW the SQLCODE variable before entering Embedded SQL. With trigger code, setting SQLCODE to a nonzero value automatically sets %ok to zero.
In Dynamic SQL, the corresponding %SQLCODE property returns SQL error code values.
$TLEVEL
The transaction level counter. Caché SQL initializes $TLEVEL to 0. If there is no current transaction, $TLEVEL is 0.
You can also use the %INTRANSACTION statement to determine if a transaction is in progress.
$TLEVEL is also set by Caché ObjectScript transaction commands. For further details, refer to the $TLEVEL special variable in the Caché ObjectScript Reference.
$USERNAME
The SQL username is the same as the Caché username, stored in the Caché ObjectScript $USERNAME special variable.
If your process is configured so that the default SQL schema is _CURRENT_USER, the name of the default schema is the first part of the $USERNAME special variable. $USERNAME contains the fully qualified schema name, for example: Deborah@Jupiter.com. SQL uses as its default schema the username part: Deborah. Note that two different $USERNAME values that have the same username part will have the same default schema name.