KI_START
| Argument | Enumeration | Purpose |
|---|---|---|
| handle | Handle | |
| keys$ | Row's key | |
| path | Path number | |
| 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 when nCols is less | |
| bLock | Lock result set | |
| bSkipMatch | Skip matching row | |
| sql | SQL Where Handle | |
| status | KDB_ERROR_ENUM | Return status |
KI_START
Sets the current sequential pointer for the result set to point before the row indexed by the supplied key, which need not itself be in the index. A subsequent KI_READ_NEXT will read the first row with a key greater than or equal to the supplied key.
If however, path is negative, then the current pointer for the index ABS(path) is set to point after the required key on the key path. A subsequent KI_READ_NEXT on the same negative path will read the last row with a key less than the supplied key. Using KI_START to position with a positive path and then calling 'KI_READ_NEXT with a negative path, will skip the row before the row specified in the start, and then a new call to KI_START should be issued when changing direction.
There are optional parameters for the number of significant key columns to be used, nCols, the maximum number of rows to retrieve, max_rows, whether the result set is open or closed and if insignificant columns should be included in the start key. This allows KI_START to be a superset of KI_START_FIRST and KI_START_ON. If these parameters are not supplied then the column count is assumed to be zero meaning that all the columns are significant in the key, the max_rows parameter will also be zero implying no restriction on the number of rows and the bClosed parameter will be false for an open range start where the upper key is unknown. SQL databases prefer closed range starts as they avoid the optimizer invoking table scans. Setting bClosed to TRUE generates an upper bound key containing the number of significant columns. The range will conclude when the cursor reaches a row that exceeds these columns.
If nCols is set to -1 then no columns are significant and the statement acts like a KI_START_BEG. The key buffer is ignored.
The bUseAllCols parameter is only considered if the nCols parameter is greater than zero and it determines whether the insignificant columns should be included in the start key. They are always excluded from the end key that is implied by having a positive column count when bClosed is TRUE.
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.
KCML 6.60 introduced KI_START_ROW where the key columns are taken from a row buffer which can be defined as a view thus improving performance on SQL databases. KI_START_ROW is now the preferred way to start a sequential rowset.
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
The optional arguments for locking, key columns, maximum rows and closed ranges were 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.