Skip to main content
Previous sectionNext section

Using Embedded SQL

You can embed SQL statements within ObjectScript code. These Embedded SQL statements are converted to optimized, executable code at compilation time.

There are two kinds of Embedded SQL:

  • A simple Embedded SQL query can only return values from a single row. Simple Embedded SQL can also be used for single-row insert, update, and delete, and for other SQL operations.

  • A cursor-based Embedded SQL query can iterate through a query result set, returning values from multiple rows. Cursor-based Embedded SQL can also be used for multiple row update and delete SQL operations.

This chapter discusses the following topics:

Note:

Embedded SQL cannot be input to the 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 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 ObjectScript) or within ObjectScript .MAC routines. A .MAC routine (or a method using 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 an 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 four preprocessor directives for use with Embedded SQL:

  • #SQLCompile Mode specifies the compilation mode for Embedded SQL statements coded after this preprocessor directive in the routine. It supports the following two options: Embedded (the default) — compiles ObjectScript code and Embedded SQL code at compile time, validating that tables, fields, etc. specified in the Embedded SQL exist at compile time. Deferred — compiles ObjectScript code, but defers compiling Embedded SQL code until runtime. This enables you to compile a routine containing SQL that references a table that does not yet exist at compile time.

    • #SQLCompile Mode=Deferred can be used for a Simple (non-cursor) SELECT, INSERT, UPDATE, DELETE, or TRUNCATE TABLE statement.

    • #SQLCompile Mode=Deferred cannot be used for cursor-based SELECT commands. The compiler fails with a 5663 error when you attempt to compile these commands if they specify a table, field, etc. that does not exist at compile time.

    • #SQLCompile Mode=Deferred cannot be used for an UPDATE or DELETE with a WHERE CURRENT OF cursor clause with an undeclared cursor. The compiler fails with an SQLCODE -52 error.

    Note:

    #SQLCompile Mode=Deferred should not be confused with the similarly-name %SYSTEM.SQL.SetCompileModeDeferred() method and the %SYSTEM.SQL.GetCompileMode() method, which are used for a completely different purpose.

  • #SQLCompile Select specifies the format for data display when returned from a SELECT statement, or the required format for data input when specified to an INSERT or UPDATE statement, or a SELECT input host variable. It supports the following six options: Logical (the default), Display, ODBC, Runtime, Text (synonym for Display), and FDBMS (see below). If #SQLCompile Select=Runtime, you can use the $SYSTEM.SQL.SetSelectMode() method to change how the data is displayed.

    Regardless of the #SQLCompile Select option specified, an INSERT or UPDATE automatically converts the specified data value to its corresponding Logical format for storage.

    Regardless of the #SQLCompile Select option specified, a SELECT automatically converts an input host variable value its corresponding Logical format for predicate matching.

    Using#SQLCompile Select for query display is shown in the following examples. These examples display the DOB (date of birth) value, then change the SelectMode to ODBC format, then display the DOB again. In the first example, changing the SelectMode has no effect on the display; in the second example, because #SQLCompile Select=Runtime, changing the SelectMode changes the display:

       #SQLCompile Select=Display
       &sql(SELECT DOB INTO :a FROM Sample.Person)
       WRITE "1st date of birth is ",a,!
       DO $SYSTEM.SQL.SetSelectMode(1)
       WRITE "changed select mode to: ",$SYSTEM.SQL.GetSelectMode(),!
       &sql(SELECT DOB INTO :b FROM Sample.Person)
       WRITE "2nd date of birth is ",b
    Copy code to clipboard
       #SQLCompile Select=Runtime
       &sql(SELECT DOB INTO :a FROM Sample.Person)
       WRITE "1st date of birth is ",a,!
       DO $SYSTEM.SQL.SetSelectMode(1)
       WRITE "changed select mode to: ",$SYSTEM.SQL.GetSelectMode(),!
       &sql(SELECT DOB INTO :b FROM Sample.Person)
       WRITE "2nd date of birth is ",b
    Copy code to clipboard

    For further details on SelectMode options, refer to “Data Display Options” in the “Caché SQL Basics” chapter of this book.

    • #SQLCompile Select=FDBMS is provided to enable Embedded SQL to format data in the same way as FDBMS. If a query has a constant value in the WHERE clause, FDBMS mode assumes it to be a Display value and converts it using DisplayToLogical conversion. If a query has a variable in the WHERE clause, FDBMS mode converts it using FDBMSToLogical conversion. The FDBMSToLogical conversion method should be designed to handle the three FDBMS variable formats: Internal, Internal_$c(1)_External, and $c(1)_External. If a query selects into a variable, it invokes the LogicalToFDBMS conversion method. This method returns Internal_$c(1)_External.

  • #SQLCompile Path (or #Import) specifies the schema search path used to resolves unqualified table, view, and stored procedure names in data management commands such as SELECT, CALL, INSERT, UPDATE, DELETE, and TRUNCATE TABLE. If no schema search path is specified, or if the table is not found in the specified schemas, Caché uses the system-wide default schema. #SQLCompile Path and #Import are ignored by data definition statements such as ALTER TABLE, DROP VIEW, CREATE INDEX, or CREATE TRIGGER. Data definition statements use the system-wide default schema to resolve unqualified names.

  • #SQLCompile Audit is a boolean switch specifying whether or not the execution of Embedded SQL statements should be recorded in the system events audit log. For further details, refer to Auditing 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.

  • Class references class: if you are changing Class B using Studio, when you recompile Class B the Studio compile dependent classes setting should also recompile Class A. How to set the compile dependent classes option is described in Using Studio.

  • Routine references class: if A is a routine that references Class B, you need to recompile Routine A manually.

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:

   NEW SQLCODE,a
   WRITE "Invoking Embedded SQL",!
   &sql(SELECT Name INTO :a FROM Sample.Person)
   WRITE "The SQL error code is ",SQLCODE,!
   IF $DATA(a) {WRITE "The name is ",a}
Copy code to clipboard

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
}
Copy code to clipboard

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
       )
Copy code to clipboard

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. 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 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 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"
Copy code to clipboard
  NEW SQLCODE
  WRITE !,"Entering Embedded SQL"
  &sql(/* SELECT Name INTO :a FROM Sample.Person */)
  WRITE !,"Leaving Embedded SQL"
Copy code to clipboard

&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(...): this directive is functionally equivalent to &sql. It provides an alternative syntax for clarity of code. However, it cannot include marker syntax.

  • &sql<marker>(...)<reversemarker>: this directive allows you to specify multiple &sql directives, identifying each with a user-selected marker character or string. This marker syntax is described in the following section.

&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 adds (at least) 2 to the total number of INT code lines at that point in the routine. Therefore, a single line of Embedded SQL counts as 3 lines, two lines of Embedded SQL count as 4 lines, and so forth. Embedded SQL that invokes other code can add many more lines to the INT code.

    A dummy Embedded SQL statement, containing only a comment counts as 2 INT code lines, as in the following example: &sql( /* for future use */).

  • All lines within Embedded SQL count as line offsets, including comments and blank lines.

You can display INT code lines using the ^ROUTINE global.

Embedded SQL Code

Considerations for writing SQL code in Embedded SQL include the following:

Output 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)
Copy code to clipboard

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)
Copy code to clipboard

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 (SQLCODE=100), executing the query may either result in an undefined host variable, or the host variable containing a prior value.

Schema Name Resolution

A table name, view name, or stored procedure name is either qualified (specifies a schema name) or unqualified (does not specify a schema name). If the name does not specify a schema name, Caché resolves the schema name as follows:

  • Data Definition: Caché uses the system-wide default schema to resolve an unqualified name. If the default schema does not exist, Caché creates the schema and the corresponding class package. All data definition statements use the system-wide default schema; data definition statements ignore the #Import and #SQLCompile Path macro preprocessor directives.

  • Data Management: Caché uses the schema search path specified by the #SQLCompile Path and/or the #Import macro preprocessor directive(s) in effect for the class or routine that contains the Embedded SQL statement. 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. If no schema search path is specified, Caché uses the system-wide default schema 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
Copy code to clipboard

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
Copy code to clipboard

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)
      QUIT:(SQLCODE'=0)
  &sql(FETCH C1)
  WHILE (SQLCODE = 0) {
     WRITE $ZDATE(today)," ",name," has ",retire," eligibility years",!
    &sql(FETCH C1) }
  &sql(CLOSE C1)
Copy code to clipboard

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
Copy code to clipboard

The following workaround succeeds:

  WRITE "Embedded SQL test",!
  &sql(SELECT 'the sequence ##lit(##unique() is restricted' INTO :x)
  WRITE x
Copy code to clipboard

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)
      QUIT:(SQLCODE'=0)
  &sql(FETCH C1)
  WHILE (SQLCODE = 0) {
     WRITE $LENGTH(colors),": ",colors,!
    &sql(FETCH C1) }
  &sql(CLOSE C1)
Copy code to clipboard

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;
}
Copy code to clipboard

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)
Copy code to clipboard

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),!
Copy code to clipboard

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)
Copy code to clipboard

or, alternatively, using a host variable:

 Set dob = $ZDH("01/02/1999",1)

 &sql(SELECT Name INTO :name
        FROM Patient
        WHERE DOB = :dob)
Copy code to clipboard

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 the value of a local variable as an input value into Embedded SQL, or to pass an SQL query result value as an output host variable from an Embedded SQL query.

A host variable cannot be used to specify an SQL identifier, such as a schema name, table name, field name, or cursor name. A host variable cannot be used to specify an SQL keyword.

  • Output host variables are only used in Embedded SQL. They are specified in an INTO clause, an SQL query clause that is only supported 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. This “?” syntax cannot be used in Embedded SQL.

Note:

Caché Basic does not support Embedded SQL. Either use Dynamic SQL to perform SQL operations from Caché Basic, or have Caché Basic call an 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:

  • One or more ObjectScript local variables, such as :myvar, specified as a comma-separated list. A local variable can be fully formed and can include subscripts. Like all local variables, it is case-sensitive and can contain Unicode letter characters.

  • A single ObjectScript local variable array, such as :myvars(). A local variable array can receive only field values from a single table (not joined tables or a view). For details, refer to “Host Variable Subscripted by Column Number”, below.

  • An object reference, such as :oref.Prop, where Prop is a property name, with or without a leading % character. This can be a simple property or a multidimensional array property, such as :oref.Prop(1). It can be an instance variable, such as :i%Prop or :i%%Data. The property name may be delimited; for example :Person."Home City". Delimited property names can be used even when the Support Delimited Identifiers configuration option is not set. Multidimensional properties may include :i%Prop() and :m%Prop() host variable references. An object reference host variable can include any number of dot syntax levels; for example, :Person.Address.City.

    When an oref.Prop is used as a host variable inside a procedure block method, the system automatically adds the oref variable (not the entire oref.Prop reference) to the PublicList and NEWs it.

Host variables should be listed in the 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:

  • Input host variables are never modified by the SQL statement code. They retain their original values even after Embedded SQL has run. However, input host variable values are “lightly normalized” before being supplied to the SQL statement code: Valid numeric values are stripped of leading and trailing zeros, a single leading plus sign, and a trailing decimal point. Timestamp values are stripped of trailing spaces, trailing zeros in fractional seconds, and (if there are no fractional seconds) a trailing decimal point.

  • In FETCH ... INTO statements, the output host variables in the INTO clause are only modified if SQLCODE equals 0, that is, when a valid row is returned; otherwise, they are not modified.

  • In SELECT ... INTO and DECLARE ... SELECT ... INTO statements, the output host variables in the INTO clause are modified if SQLCODE equals 0 (when a valid row is returned), and may have been modified even when SQLCODE is not 0, that is, when no new row was returned.

  • In DECLARE ... SELECT ... INTO statements, do not modify the output host variables in the INTO clause between two FETCH calls, since that might cause unpredictable query results.

You must check the SQLCODE value before processing output host variables.

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,! }
Copy code to clipboard

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 ObjectScript example, an Embedded SQL statement uses output host variables to return a name and home state address from an SQL query to 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 !,"SQLCODE=",SQLCODE  
   }
Copy code to clipboard

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 !,"SQLCODE=",SQLCODE
   }
Copy code to clipboard

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 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 !,"SQLCODE=",SQLCODE
   }
Copy code to clipboard

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 !,"Input value is: ",x
  IF SQLCODE = 0 {
    WRITE !,"Name value is: ",a
    WRITE !,"Age value is: ",b }
  ELSE {WRITE !,"SQLCODE=",SQLCODE }
Copy code to clipboard

In the following 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 )
Copy code to clipboard

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.

  • A host variable subscripted array may be used for input in an INSERT, UPDATE, or INSERT OR UPDATE statement VALUES clause. When used in an INSERT or UPDATE statement, a host variable array allows you to define which columns are being updated at runtime, rather than at compile time. For INSERT and UPDATE usage, refer to those commands in the Caché SQL Reference.

  • A host variable subscripted array may be used for output in a SELECT or DECLARE statement INTO clause. Subscripted array usage in SELECT is shown in the examples that follow.

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 !,"SQLCODE=",SQLCODE }
Copy code to clipboard

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 !,"SQLCODE=",SQLCODE }
Copy code to clipboard

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 !,"SQLCODE=",SQLCODE }
Copy code to clipboard

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:

  • A subscripted list can only be used when selecting fields from a single table in the FROM clause. This is because when selecting fields from multiple tables, the SqlColumnNumber values may conflict.

  • A subscripted list can only be used when selecting table fields. It cannot be used for expressions or aggregate fields. This is because these select-list items do not have an SqlColumnNumber value.

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 !,"SQLCODE=",SQLCODE }
Copy code to clipboard

The SQL NULL is equivalent to the ObjectScript "" string (a zero-length string).

If you output a NULL to a host variable, Embedded SQL treats its value as the 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 !,"SQLCODE=",SQLCODE }
Copy code to clipboard

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 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 ObjectScript"
    WRITE !,"ZLS is of length ",$LENGTH(zls)," defined: ",$DATA(zls)
    WRITE !,"NULL is of length ",$LENGTH(spouse)," defined: ",$DATA(spouse) }
   ELSE {WRITE !,"SQLCODE=",SQLCODE }
Copy code to clipboard

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

  • Input host variables are never modified by Embedded SQL.

  • Output host variables are only reliably valid after Embedded SQL when SQLCODE = 0.

For example, the following use of OutVal is not reliably valid:

InvalidExample
   SET InVal = "1234"
   SET OutVal = "None"
   &sql(SELECT Name
        INTO :OutVal
        FROM Sample.Person
        WHERE %ID=:InVal)
   IF OutVal="None" {           ; Improper Use 
   WRITE !,"No data returned"
   WRITE !,"SQLCODE=",SQLCODE }
   ELSE {
   WRITE !,"Name is: ",OutVal }
Copy code to clipboard

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="None" 
      IF OutVal="None" {
      WRITE !,"No data returned"
      WRITE !,"SQLCODE=",SQLCODE } }
   ELSE {
   WRITE !,"Name is: ",OutVal }
Copy code to clipboard

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
  }
Copy code to clipboard

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.

  • A cursor-based query uses DECLARE cursorname CURSOR FOR SELECT to select records and (optionally) return select column values into output host variables. The FETCH statement iterates through the result set, using these variables to return selected column values.

  • A cursor-based DELETE or UPDATE uses DECLARE cursorname CURSOR FOR SELECT to select records for the operation. No output host variables are specified. The FETCH statement iterates through the result set. The DELETE or UPDATE statement contains a WHERE CURRENT OF clause to identify the current cursor position in order to perform the operation on the selected record. For further details on cursor-based DELETE and UPDATE, refer to the WHERE CURRENT OF page in Caché SQL Reference.

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)
      QUIT:(SQLCODE'=0)
 &sql(FETCH C1)

 While (SQLCODE = 0) {
     Write id, ":  ", name,!        
    &sql(FETCH C1)
 }
    
 &sql(CLOSE C1)
Copy code to clipboard

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
 )
Copy code to clipboard

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
 )
Copy code to clipboard

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)
Copy code to clipboard

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)
Copy code to clipboard

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)
Copy code to clipboard

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 an SQLCODE -76 “Cardinality Mismatch” error when the statement is compiled.

Commonly, the INTO clause is specified in the DECLARE statement, not the FETCH statement. If both the SELECT query in the DECLARE statement and the FETCH statement contain an INTO clause, the host variables specified by both statements are set.

If FETCH retrieves data, the SQLCODE variable is set to 0; if there is no data (or no more data) to FETCH, SQLCODE is set to 100 (No more data). Host variable values should only be used when SQLCODE=0.

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)
Copy code to clipboard

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 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), the system 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 user-defined message from a trigger when trigger code explicitly sets %ok=0 to abort 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.

%ROWCOUNT

An integer counter that indicates the number of rows affected by a particular statement.

  • INSERT, UPDATE, INSERT OR UPDATE, DELETE, and TRUNCATE TABLE set %ROWCOUNT to the number of rows affected. An INSERT command with explicit values can only affect one row, and thus sets %ROWCOUNT to either 0 or 1. An INSERT query results, an UPDATE, or a DELETE can affect multiple rows, and can thus set %ROWCOUNT to 0 or a positive integer.

  • SELECT with no declared cursor can only act upon a single row, and thus execution of a simple SELECT always sets %ROWCOUNT to either 1 (single row that matched the selection criteria retrieved) or 0 (no rows matched the selection criteria).

  • DECLARE cursorname CURSOR FOR SELECT does not initialize %ROWCOUNT; %ROWCOUNT is unchanged following the SELECT, and remains unchanged following OPEN cursorname. The first successful FETCH sets %ROWCOUNT. If no rows matched the query selection criteria, FETCH sets %ROWCOUNT=0; if FETCH retrieves a row that matched the query selection criteria, it sets %ROWCOUNT=1. Each subsequent FETCH that retrieves a row increments %ROWCOUNT. Upon CLOSE or when FETCH issues an SQLCODE 100 (No Data, or No More Data), %ROWCOUNT contains the total number of rows retrieved.

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.

If a SELECT query returns only aggregate functions, every FETCH sets %ROWCOUNT=1. The first FETCH always completes with SQLCODE=0, even when there is no data in the table; any subsequent FETCH completes with SQLCODE=100 and sets %ROWCOUNT=1.

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)
      QUIT:(SQLCODE'=0)
   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
Copy code to clipboard

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,!
Copy code to clipboard

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,!
Copy code to clipboard

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:

  • INSERT, UPDATE, INSERT OR UPDATE, DELETE, or TRUNCATE TABLE: After a single-row operation, the %ROWID variable contains the system-assigned value of the RowID (Object ID) assigned to the inserted, updated, or deleted record. After a multiple-row operation, the %ROWID variable contains the system-assigned value of the RowID (Object ID) of the last record inserted, updated, or deleted. If no record is inserted, updated, or deleted, the %ROWID variable value is unchanged.

  • Cursor-based SELECT: The DECLARE cursorname CURSOR and OPEN cursorname statements do not initialize %ROWID; the %ROWID value is unchanged from its prior value. The first successful FETCH sets %ROWID. Each subsequent FETCH that retrieves a row resets %ROWID to the current RowID. FETCH sets %ROWID if it retrieves a row of an updateable cursor. An updateable cursor is one in which the top FROM clause contains exactly one element, either a single table name or an updateable view name. If the cursor is not updateable, %ROWID remains unchanged. If no rows matched the query selection criteria, FETCH does not change the prior the %ROWID value (if any). Upon CLOSE or when FETCH issues an SQLCODE 100 (No Data, or No More Data), %ROWID contains the RowID of the last row retrieved.

    Cursor-based SELECT with a DISTINCT keyword or a GROUP BY clause does not set %ROWID. The %ROWID value is unchanged from its previous value (if any).

    Cursor-based SELECT with an aggregate function does not set %ROWID if it returns only aggregate function values. If it returns both field values and aggregate function values, the %ROWID value for every FETCH is set to the RowID of the last row returned by the query.

  • SELECT with no declared cursor does not set %ROWID. The %ROWID value is unchanged upon the completion of a simple SELECT statement.

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 ObjectScript using the following method call:

  WRITE $SYSTEM.SQL.GetROWID()
Copy code to clipboard

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

After running an embedded SQL Query, you must check the SQLCODE before processing the output host variables.

If SQLCODE=0 the query completed successfully and returned data. The output host variables contain field values.

If SQLCODE=100 the query completed successfully, but output host variable values may differ. Either:

  • The query returned one or more rows of data (SQLCODE=0), then reached the end of the data (SQLCODE=100), in which case output host variables are set to the field values of the last row returned. %ROWCOUNT>0.

  • The query returned no data, in which case the output host variables are undefined. %ROWCOUNT=0.

If a query returns only aggregate functions, the first FETCH always completes with SQLCODE=0 and %ROWCOUNT=1, even when there is no data in the table. The second FETCH completes with SQLCODE=100 and %ROWCOUNT=1. If there is no data in the table or no data matches the query conditions, the query sets output host variables to 0 or the empty string, as appropriate.

If SQLCODE is a negative number the query failed with an error condition. 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. Within trigger code, setting SQLCODE to a nonzero value automatically sets %ok=0, aborting and rolling back the trigger operation.

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 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 ObjectScript $USERNAME special variable. The username can be used as the system-wide default schema or as an element in the schema search path.

Auditing Embedded SQL

Caché supports optional auditing of Embedded SQL statements. Embedded SQL auditing is performed when the following two requirements are met:

  1. The %System/%SQL/EmbeddedStatement system audit event is enabled system-wide. By default, this system audit event is not enabled. To enable, go to Management Portal, System Administration, select Security, then Auditing, then Configure System Events.

  2. The routine containing the Embedded SQL statement must contain the #SQLCompile Audit macro preprocessor directive. If this directive is set to ON, any Embedded SQL statement following it in the compiled routine is audited when executed.

Auditing records information in the Audit Database. To view the Audit Database, go to the Management Portal, System Administration, select Security, then Auditing, then View Audit Database. You can set the Event Name filter to EmbeddedStatement to limit the View Audit Database to Embedded SQL statements. The Audit Database lists Time (a local timestamp), User, PID (process ID), and the Description, which specifies the type of Embedded SQL statement. For example, SQL SELECT Statement.

By selecting the Details link for an event you can list additional information, including the Event Data. The Event Data includes the SQL statement executed and the values of any input arguments to the statement. For example:

SELECT TOP :n Name,ColorPreference INTO :name,:color FROM Sample.Stuff WHERE Name %STARTSWITH :letter 
Parameter values: 
n=5 
letter="F"

Caché also supports auditing of Dynamic SQL statements (Event Name=DynamicStatement) and ODBC and JDBC statements (Event Name=XDBCStatement).