Class Reference
IRIS for UNIX 2024.1.2
|
Class Reference
IRIS for UNIX 2024.1.2
|
|
|
Private
Storage
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
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
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
Many statements affect some number of rows.
The number of rows affected by the statement execution is contained in the
A successfully executed SELECT statement returns a single result set. The number of columns in the result
set is contained in
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
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
The parallel binding interface is implemented as the
|
|
|
|
| Properties | |||
|---|---|---|---|
| %CurrentResult | %Message | %OutputColumnCount | %ROWCOUNT |
| %ROWID | %ResultColumnCount | %SQLCODE | %StatementType |
| %StatementTypeName | |||
| Subclasses | ||
|---|---|---|
| %Library.IProcedureContext | %SQL.FDW.Abstract | %SQL.IResultSet |
|
|
|
|
Holds the OREF of the most recently bound result set. This property is set automatically by%NextResult . Note that%NextResult is also invoked by%MoreResults . Users should not ever set this property. This property is public for backward compatibility reasons.
Contains additional information about an error reported as a negative value in%SQLCODE . Additional information is not always available.
The number of columns defined as RETURNVALUE, INPUT-OUTPUT or OUTPUT (method return value, byref and output parameters) by an SQL invoked routine. This value is always zero unless the DYNAMIC_FUNCTION is CALL (%StatementType = 45).
Contains the number of rows affected by executing the statement. It is the responsibility of the stored procedure author to explicitly set %ROWCOUNT.
For example:&sql(UPDATE Person Set Name="Mo" WHERE Age > :number) Set result.%SQLCODE=SQLCODE Set result.%ROWCOUNT=%ROWCOUNT
The value of the %ROWID. This may or may not be relevant. It is primarily used when a dynamic INSERT statement has been executed. In that case, %ROWID contains the rowid of the last row inserted into the database by executing the statement.
The number of columns in the result set. This value is always zero unless the current result is a RESULTSET object (instance of%SQL.IResultSet ) or a procedure context object (instance of%Library.IProcedureContext ). For a CALL statement result, this value represents the column count of the result set currently bound using serial binding.
Indicates whether or not the statement was executed successfully. If %SQLCODE is not negative, then the statement was successfully executed. Specifically, the successful completion values are 0 (successful completion) and 100 (the operation has either found no data or reached the end of the data). Otherwise, the value of %SQLCODE contains the error code (a negative number) and the%Message property might contain more information about the error.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.
The dynamic statement type number as assigned by SQL. See%StatementTypeName for statement type values.
The statement type name, one of the following:
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 "" Anything not listed above
|
|
Clone the current object to a new object. If deep is 1 then this does a deep copy which will also copy any subobjects and if deep is 0 then it will create another reference to any subobjects and increment the reference count appropriately. It returns the new cloned object.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.
Is used to implement an unknown method call. It is also used to resolve an unknown multidimensional property reference (to get the value of a property) because that syntax is identical to a method call.
Displays the contents of this object on the current device.
Displays the contents of the result object. If formatted display is available, then this method formats the results using the requested format and, if appropriate, opens the formatted results using the host OS. The output is directed to one or more files and messages are placed in a result set object. All file names used are returned in an array. The arguments are as follows:
- 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.
Returns the value of the column with the name colname in the current row of the result set.If colname is not a valid column name, this method throws a error.
Returns the value of column colnbr in the current row of the result set.
Returns the result set metadata as a %SQL.StatementMetadata instance.
Advances to the next row in the result referenced by%ProcCursor . Returns 0 if the cursor is at the end of the result set. An optional argument contains a%Library.Status value on return. This %Status value indicates success or failure of the%GetRow call.%SQLCODE is also set by%GetRow . The row is returned in $List format by reference in theRow argument. If %SQLCODE'=0, Row will be null (""). This implementation is overridden by classes that implement the result set interface.
Advances the nextCnt rows in the result referenced by%ProcCursor . Returns 0 if the cursor is at the end of the result set. Note, when 0 is returned, it is likely there are rows returned in theRows array. An optional argument contains a%Library.Status value on return. This %Status value indicates success or failure of the%GetRows call.%SQLCODE is also set by %GetRows. The rows are returned in an array of $List values in theRow array argument. If %SQLCODE'=0 for a row, that will be the end of the results.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.
Invokes%NextResult to return the next result object from a statement result object (aka 'context object') and updates%ProcCursor with that value. This is called "serial binding".%ResultColumnCount is updated to reflect the number of columns contained in the newly bound result. If a%ProcCursor was previously set to a result object then the reference to that result object was removed. Under normal circumstances the previously bound result set is destructed.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.
Advances to the next row in the result referenced by%ProcCursor . Returns 0 if the cursor is at the end of the result set. An optional argument contains a%Library.Status value on return. This %Status value indicates success or failure of the %Next call.%SQLCODE is also set by %Next. This implementation is overridden by classes that implement the result set interface.
Returns the output value from the position following pPosition. If the caller passed pPosition by reference then it will contain the position corresponding to the output value returned. If the pPosition argument is NULL, then no values exist beyond the value passed by the caller and the value returned is also NULL.The output value at position 0 is always the called routine's return value.
Returns the next result object from a statement descriptor (aka 'context object'). Typically, multiple results are only returned from an SQL invoked procedure. Such results are dynamic result sets whose metadata is not available until retrieval time. %NextResult() for non-CALL statements simply returns NULL, indicating that no more results are available. For an SQL invoked procedure that returns a single preplanned result set and for SELECT statements (which return a single result set when successfully executed) this method returns the current result set object when called for the first time. Subsequent calls return a null OREF.
Normalizes all of an object's property values by invoking the data type Normalize methods. Many data types may allow many different representations of the same value. Normalization converts a value to its cannonical, or normalized, form.
This is an abstract method. Refer to%SQL.IResultSet for the concrete method implemented for result set results.
This method retrieves all of the serial values for referenced objects and places them into the instance variables, Validates, Normalizes, and serializes the object (with a save of the persistent image if persistent).
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.