%SQL.StatementResult
class %SQL.StatementResult extends %Library.RegisteredObject
For details on using this class, see Using Dynamic SQL.
%SQL.StatementResult is the result descriptor class, returned by the %Execute() and %ExecDirect() methods of %SQL.Statement. Use this class to examine the results of the query.
The content of a statement result object varies depending on the statement type and whether or not the statement was successfully executed. The interface to the result object is the same for all statement types but the content of the result object varies.
When retrieving results, first check for successful execution by examining %SQLCODE. If %SQLCODE is greater than or equal to zero, then the statement was successfully executed. Otherwise, the value of %SQLCODE contains the error code and the %Message property might contain more information about the error. See %SQLCODE for additional comments.
Many statements affect some number of rows. The number of rows affected by the statement execution is contained in the %ROWCOUNT property. For a SELECT statement, if the cursor is positioned after the last row, the value of %ROWCOUNT indicates the number of rows contained in the result set. At any other time, %ROWCOUNT contains the number of rows retrieved thus far. %ROWID is set to the RowID of the last record modified by the most recent INSERT, UPDATE, INSERT OR UPDATE, DELETE, or TRUNCATE TABLE operation. After an INSERT statement, the %ROWID property contains the system-assigned value of the RowID (Object ID) assigned to the new record.
A successfully executed SELECT statement returns a single result set. The number of columns in the result set is contained in %ResultColumnCount. A cursor (iterator) is available to retrieve rows from the result set. To advance the cursor to the next row, call %Next(). The initial position of the cursor is before the first row. %Next() returns 1 to indicate that it is positioned on a row or 0 to indicate that it is positioned after the last row (at the end of the result set). If the cursor is positioned after the last row, the value of %ROWCOUNT indicates the number of rows contained in the result set. At any other time, %ROWCOUNT contains the number of rows retrieved thus far. For more information on the result set interface refer to the %SQL.IResultSet class.
A CALL statement result might contain output values, including a return value and any parameters defined as INPUT-OUTPUT or OUTPUT, as well as a collection of dynamic result sets. The %OutputColumnCount property indicates the number of output values. You can retrieve individual output values by using the output value iterator - %NextOutputValue().
The following is an example of retrieving all of the output values returned by a procedure:
if result.%OutputColumnCount > 0 {
set tPosition = ""
set tValue = result.%NextOutputValue(.tPosition)
while tPosition '= "" {
write !,tPosition," = ",tValue
set tValue = result.%NextOutputValue(.tPosition)
}
} else {
write !,"No output values returned."
}
In the above example, the value of tPosition is the position of the formal parameter whose value is returned in tValue. If the procedure declares a return value then the return value is always at position 0 (zero). Input parameters are not contained in the output values but the presence of an input parameter is reflected as a gap in the output value position.
A CALL statement can also return dynamic result sets as a collection referred to as a result set sequence. There are two mechanisms for accessing result sets - serial binding and parallel binding. Serial binding is automatically used whenever any part of its interface is invoked. The serial binding interface consists of the %ResultColumnCount property, a private property %ProcCursor, and the methods %Next(), %GetRow(), %GetRows(), %MoreResults(), %GetMetadata(), %Get(), %GetData(), %Print() and several internal methods. The first reference to any of the serial binding interface members triggers the first result set from the result set sequence to be bound to the %ProcCursor property and also to the %CurrentResult property. Subsequent references to the serial binding interface operate on that result set. When the currently bound result set is completely processed, you can invoke %MoreResults() to retrieve the next result set from the result set sequence by calling %NextResult() and then binding that result to %ProcCursor.
The parallel binding interface is implemented as the %NextResult() method and the %CurrentResult property. Each call to %NextResult() returns an OREF to the next result set in the collection of dynamic resultsets returned by the procedure. When all result sets have been returned then %NextResult() returns NULL (value = "" in COS). The result returned by %NextResult() is also bound to the %CurrentResult property.
%CurrentResult always references the most recently bound result set and it can be used for direct access to that result set, bypassing the serial binding interface. The user must be careful since both parallel binding (%NextResult()) and serial binding (automatic and %MoreResults()) affects the value of %CurrentResult.
Property Inventory
- %CurrentResult
- %Message
- %OutputColumnCount
- %ROWCOUNT
- %ROWID
- %ResultColumnCount
- %SQLCODE
- %StatementType
- %StatementTypeName
Method Inventory
- %ConstructClone()
- %DispatchMethod()
- %Display()
- %DisplayFormatted()
- %Get()
- %GetData()
- %GetMetadata()
- %GetRow()
- %GetRows()
- %MoreResults()
- %Next()
- %NextOutputValue()
- %NextResult()
- %NormalizeObject()
- %Print()
- %SerializeObject()
- %ValidateObject()
Parameters
Properties
For example:
&sql(UPDATE Person Set Name="Mo" WHERE Age > :number) Set result.%SQLCODE=SQLCODE Set result.%ROWCOUNT=%ROWCOUNT
Note that, in successful cases, the methods %Execute() and %ExecDirect() set the %SQLCODE property to 0, but there is no information yet to indicate the number of rows (which could be 0). To know how many rows there are, it is necessary to call %Next() to examine the results. If your application needs to test for the case where the query has no results, be sure to call %Next at least once, even if you don't otherwise need to iterate through the results; then check to see whether %SQLCODE is 100.
| 1 | SELECT |
| 2 | INSERT (also 'INSERT OR UPDATE') |
| 3 | UPDATE |
| 4 | DELETE |
| 5 | COMMIT |
| 6 | ROLLBACK |
| 7 | GRANT |
| 8 | REVOKE |
| 9 | CREATE TABLE |
| 10 | ALTER TABLE |
| 11 | DROP TABLE |
| 12 | CREATE VIEW |
| 13 | ALTER VIEW |
| 14 | DROP VIEW |
| 15 | CREATE INDEX |
| 16 | ALTER INDEX (Not supported) |
| 17 | DROP INDEX |
| 18 | CREATE ROLE |
| 19 | DROP ROLE |
| 20 | SET TRANSACTION |
| 21 | START TRANSACTION |
| 22 | %INTRANSACTION |
| 23 | %BEGTRANS (Alias for START TRANSACTION) |
| 24 | %INTRANS (Alias for %INTRANSACTION) |
| 25 | GET (Not supported) |
| 26 | SET OPTION |
| 27 | STATISTICS (UPDATE STATISTICS, not supported)) |
| 28 | %CHECKPRIV |
| 29 | CREATE USER |
| 30 | ALTER USER |
| 31 | DROP USER |
| 32 | %CHECKPRIV (SQL Admin Privilege) |
| 33 | GRANT (SQL Admin Privilege) |
| 34 | REVOKE (SQL Admin Privilege) |
| 35 | CREATE FUNCTION |
| 36 | CREATE METHOD |
| 37 | CREATE PROCEDURE |
| 38 | CREATE QUERY |
| 39 | DROP FUNCTION |
| 40 | DROP METHOD |
| 41 | DROP PROCEDURE |
| 42 | DROP QUERY |
| 43 | CREATE TRIGGER |
| 44 | DROP TRIGGER |
| 45 | CALL |
| 46 | SAVEPOINT |
| 47 | LOCK TABLE |
| 48 | UNLOCK TABLE |
| 49 | CREATE DATABASE |
| 50 | DROP DATABASE |
| 51 | USE DATABASE |
| 52 | TUNE TABLE |
| 53 | DECLARE |
| 54 | CREATE MODEL |
| 55 | DROP MODEL |
| 56 | TRAIN MODEL |
| 57 | ALTER MODEL |
| 58 | VALIDATE MODEL |
| 59 | SET ML CONFIGURATION |
| 60 | CREATE ML CONFIGURATION |
| 61 | ALTER ML CONFIGURATION |
| 62 | DROP ML CONFIGURATION |
| 63 | FREEZE PLANS |
| 64 | UNFREEZE PLANS |
| 65 | PURGE |
| 66 | BUILD INDEX |
| 67 | CREATE AGGREGATE |
| 68 | DROP AGGREGATE |
| 69 | LOAD |
| 70 | CREATE SCHEMA |
| 71 | DROP SCHEMA |
| 72 | CREATE SERVER |
| 73 | ALTER SERVER |
| 74 | DROP SERVER |
| 75 | CREATE FOREIGN TABLE |
| 76 | ALTER FOREIGN TABLE |
| 77 | DROP FOREIGN TABLE |
| 78 | CANCEL QUERY |
| 79 | EXPLAIN |
| 80 | THROUGH |
| "" | Anything not listed above |
Methods
Note that even if deep=0 when you clone a parent object in a parent child relationship or a one object of a one to many relationship then it will construct clones of all the child/many objects. This is because a child/many object can only point at a single parent and so if we did not create a clone of these then you would have a relationship with zero items in it. If you really just want to clone the object without these child/many objects then pass deep=-1 to this method.
After the clone is constructed it will call %OnConstructClone(object,deep,.cloned) on the clone if it is defined so that you can perform any additional steps e.g. taking out a lock. This works just the same way as %OnNew() does.
The object is the oref of the original object that was cloned. The cloned array is just used internally when doing a deep clone to prevent recursive loops, do not pass anything in at all for this parameter on the initial call. If you write a %OnConstructClone and from here you wish to call %ConstructClone on another object pass in the cloned array, e.g. 'Do oref.%ConstructClone(1,.cloned)' so that it can prevent recursive loops.
The location is used internally to pass the new location for stream objects.
- pFormat -
The format applied to the result content. This parameter is also used to determine the file name extension.
The supported formats are:
- -1 = %Display() format
- 0 = XML
- 1 = HTML
- 2 = PDF (requires a renderer such as FOP)
- 99 = TXT
- 100 = CSV (actually TAB-separated)
If pFormat is specified as any number not listed above then it will default to TXT.
pFormat can also be specified as XML, HTML, PDF, TXT or CSV.
NOTE: pFormat of CSV/100 is not a true comma-separated value, but instead uses TAB separated values.
- pFileName - The base file name to be used to generate actual file names used for output. If no value is specified then a file name will be generated, using the TEMP folder defined for the InterSystems IRIS instance. This value is not expected to include an extension. An extension is added to this value to form the actual file used. Also, if nested results exist then a number is appended to the file name specified to produce a unique name for each result.
- pMessages - Instance of a system result set class. If no value is passed then the system message result class is instantiated. This parameter is passed by reference. It is up to the caller to process the result set OREF that is returned. pMessages.Count() returns the number of messages contained in the result set. pMessages.%Display() will display the messages on the current device. pMessages.%DisplayFormatted() is also implemented and can be used to display the messages using the selected format.
- pFilesUsed - This pass-by-reference parameter will contain the number of files used to display the result content and the name of each file. pFilesUsed is the number of files and pFilesUsed(file_number) is the name of the file. The sequence of the files is the same sequence as the results are processed. For simple result objects, there is a single file. For context objects that can contain result set sequences, the results are output in the order they are returned and the files used are present in pFilesUsed in that same order.
- pTranslateTable - This is the translate table used for the output files when the format is CSV or TXT. This parameter is optional.
- pDelimiter - The delimiter to use between columns in the export file. This parameter is optional and defaults to tab character if not specified.
If colname is not a valid column name, this method throws a error.
For example, suppose rset.%GetRows(10,.tenrows,.sc) is called:
- If there are more then 10 rows remaining to be fetched from the result set, tenrows=10, tenrows(1)=$lb(first row's contents), ..., tenrows(10)=$lb(tenth row's contents), and %GetRows() will return 1.
- If there are 5 rows remaining to be fetched from the result set, tenrows=5, tenrows(1)=$lb(first row's contents), ..., tenrows(5)=$lb(fifth row's contents), and %GetRows() will return 0.
- If there are 0 rows remaining to be fetched from the result set, tenrows=0 and %GetRows() will return 0.
This implementation is overridden by classes that implement the result set interface.
If the new value of %ProcCursor is a valid result object, then this method returns 1; otherwise it returns 0 to indicate that no more results are available.
The output value at position 0 is always the called routine's return value.
This method is not meant to be called directly. It is called by %Save and by %GetSwizzleObject.
This method validates an object.
The %Save() method of a persistent class calls this method before filing any objects in the database. The %ValidateObject() of a referencing object can call it. You can also call it explicitly at any time.
%ValidateObject() does the following:
- If present, it will call a user-supplied %OnValidateObject() method.
- It checks if any required property values are missing.
- If the PROPERTYVALIDATION class parameter is set to ValidateOnSave, it validates each non-null property value by calling the property method IsValid on each literal property and the %ValidateObject method for each object-valued embedded object property (properties whose type extend %SerialObject).
- If checkserial is 1, it forces the checking of any embedded object properties by calling their %ValidateObject method after swizzling this property.
- If checkserial is 2, it forces the checking of any collections of serial types by iterating over those collections and calling their %ValidateObject() method after swizzling this property, in addition to the validation that occurs when checkserial is 1.
%ValidateObject() returns a %Status indicating success or error. It is up to the caller to process the error value.
%ValidateObject() does not validate object-valued reference properties (properties whose type extends %Persistent) due to the possibility of circular dependencies between objects. The %Save() method of a persistent class automatically detects and handles circular references between objects. If you require the validation of reference properties, you can override this method in a subclass or call %Save() directly.