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 is undefined or 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 InterSystems IRIS Embedded SQL interface, InterSystems IRIS 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. 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, %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. InterSystems 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 %MessageOpens in a new tab 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.LanguageOpens in a new tab.
%ROWCOUNT
An integer counter that indicates the number of rows affected by a particular statement.
-
INSERT, UPDATE, INSERT OR UPDATE, and DELETE 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.
-
TRUNCATE TABLE always sets %ROWCOUNT to –1, regardless of how many rows were deleted or if any rows were deleted. Therefore, to determine the actual number of rows deleted, either perform a COUNT(*) on the table before TRUNCATE TABLE, or delete all the rows in the table using DELETE, rather than TRUNCATE TABLE.
-
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 %ROWCOUNTOpens in a new tab 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
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)
IF SQLCODE<0 {WRITE "SQLCODE error ",SQLCODE," ",%msg QUIT}
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)
IF SQLCODE<0 {WRITE "SQLCODE error ",SQLCODE," ",%msg QUIT}
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:
-
INSERT, UPDATE, INSERT OR UPDATE, or DELETE: 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. TRUNCATE TABLE does not set %ROWID.
-
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 value. 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 %ROWIDOpens in a new tab property returns the RowID value 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()
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. In particular, you should always check SQLCODE<0; if this condition holds true, then there was error while processing the query and your application should respond accordingly.
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 set to the null string. %ROWCOUNT=0.
After any invocation of the &sql() directive, you should check SQLCODE<0. If this condition holds true, then an error arose.
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 SQLCODE Values and Error Messages.
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 %SQLCODEOpens in a new tab property returns SQL error code values.
$TLEVEL
The transaction level counter. InterSystems 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, see $TLEVEL.
