KI_START_ROW
| Argument | Enumeration | Purpose |
|---|---|---|
| handle | Table handle | |
| path | Key path | |
| SYM(row$)> | Row buffer | |
| nCols | Number of significant columns in a compound key | |
| max_rows | Max rows to return | |
| bClosed | Closed range flag | |
| bUseAllCols | Use all columns of key even if nCols is non-zero | |
| bLock | Lock result set | |
| bSkipMatch | Skip matching row | |
| sql | SQL Where Handle | |
| status | KDB_ERROR_ENUM | Return status |
KI_START_ROW
This is a superset of KI_START, KI_START_ON and KI_START_FIRST which defines the initial key using the row buffer itself, thus avoiding the use of KI_BUILD, and which can limit the columns in the result set if the row buffer was DIMmed using a DEFVIEW. Using a view can lead to significant performance improvements with SQL databases by avoiding the fetching from the database server of columns that are not required.
If the significant column count nCols is zero then KCML will assume all the columns of the key are present in the row buffer. If non-zero and less than the actual number in the key, then KCML will notionally initialize the missing trailing columns to ALL(00) for a forward path or ALL(FF) for a backward path. Make sure that the bUseAllCols flag is FALSE, the default, on the first call.
For legacy reasons zero has a special meaning for nCols. TO have no significant columns, i.e. to simulate a KI_START_BEG, you should set nCols to -1. Then the row buffer is ignored and the bUseAllCols and bSkipMatch parameters are ignored.
The number of rows in the result set can be constrained by setting the max_rows parameter to a non-zero value. KCML wil then return KE_ENDOFRANGE when the number of rows has been read. Setting this can be advantageous on SQL databases to avoid open result sets.
If the bClosed parameter is TRUE then KCML will limit the result set to rows which exactly match the leading columns supplied in the key. This avoids an open ended result set which may cause an SQL database to disregard the index and do a table scan and sort to build the result set before returning.
If the optional bLock flag is set to TRUE then on an SQL database the rows of the result set are immediately locked with a write lock preventing other processes from taking a similar lock. Locks can only be taken within a transaction and are held for the duration of the transaction. The lock may fail and the transaction may be automatically retried if a deadlock is detected.
Similarly on a native KDB table the handle is marked as referring to a locked result set and a subsequent KI_READ_NEXT will lock each row read at the point of the read. No locks are taken at the point of the start. These locks must be released explicitly by a KI_REWRITE, KI_DELETE or a KI_UNLOCK call or by closing the table.
A KI_READ_HOLD_NEXT can be used to traverse the locked result set but this usage is now deprecated.
If the optional bSkipMatch flag is set to TRUE then the first rows in the result set are not returned if they match the supplied key. For positive path values a subsequent KI_READ_NEXT will read the first row with a key greater than the supplied key. For negative path values a subsequent KI_READ_NEXT a subsequent KI_READ_NEXT on the same negative path will read the last row with a key less than the supplied key.
The optional sql parameter can be used to pass SQL directly to the SQL database. The SQL passed here is inserted as part of any WHERE clause. To constuct the SQLHandle we need to use the #WHERE statement.
Always succeeds if table is open, the path exists and any explicit column count does not exceed the number of columns in the key for that index path.
Compatibility
This function was introduced in KCML 6.60.
The optional argument for SQL handle was introduced in KCML 7.07.
It was common practice with KDB to make columns insignificant by setting them to ALL(00) or ALL(FF), depending on direction. Because HEX(FF) is not a legal UTF-8 character this may cause problems with SQL databases and is the practice is strongly deprecated. See connection attributes for information about how the _KDB_CONN_ATTR_KEY_PADDING attribute can be used to control how padding is handled.