KI_WRITE_RAW
| Argument | Enumeration | Purpose |
|---|---|---|
| handle | Handle | |
| rowid | Row to start writing at | |
| rowsin | Number of rows to write | |
| SYM(rowbuf$) | Symbol of row buffer | |
| status | KDB_ERROR_ENUM | Return status |
| rowsout | Number of rows actually written |
KI_WRITE_RAW
This call attempts to write rowsin rows starting at row number rowid. The index is not updated (for KDB tables) and will probably be invalidated. If the buffer is not large enough for rowsin rows, then it is truncated to the number that will actually fit. The number of rows actually written is returned in rowsout.
Row buffers written with the first byte set to HEX(FF) will be considered to be deleted.
For type 7 tables the row number rowid has little meaning and rows are written arbitrarily throughout the table. The first call must have a recno of 1 and subsequent calls should use a rowid greater than one. Any row marked as deleted is not written to the table.
The indices on a KDB7 are not updated by this operation and you will need to rebuild them when done. The quickest way to populate a KDB table is to create it, add the required indices while empty, add the data with KI_WRITE_RAW, finish with a KI_END and finally index it with KI_REBUILD.
You should issue a KI_END on the handle when finished to free resources.
KI_WRITE_RAW will honor USERSTAMP and TIMESTAMP columns using a fixed value for the timestamp set on the original call. If an autoserial column is enabled then it will be autoincremented.
Oracle considerations
KI_WRITE_RAW will attempt to use the OCI Direct Path Loader to bulk load rows. This is much faster than using INSERT statements. Internally the OCI layer holds a buffer which is written to the database when it fills or when you close or issue a KI_END on the handle. Because of this buffering there is no need to use big arrays of rows and you could write one row at a time. Direct loads are not transactional and should not be included in a transaction. Other users will not see the blocks containing the new rows until the buffer is committed to the table by a successful KI_END. It is important to inspect the status code returned by KI_END to check that the update was successful. A failed update will result in no rows being added to the table.
The default size of the OCI direct load buffer is 1024 KB but it can be changed by setting the _KDB_CONN_ATTR_DP_LOAD_BUFSZ connection handle attribute with KI_CONNECT_SET_ATTR. This is in units of kilobytes. Setting the size to 1 will disable the DP loader and INSERTs will be used instead.
The DP loader cannot be used in some cases and KCML will silently fallback to using INSERT statements. These cases include:
KI_WRITE_RAW for Oracle does not overwrite any existing rows in a table but rather it appends rows. You may want to truncate the table with KI_INITIALISE first if you are replacing the contents. If duplicate keys in an index result from an append operation the index will be silently marked as unusable and disregarded by Oracle so it is the applications responsibility to avoid this happening.
Any word search indexes are unaffected by the update and should be rebuilt afterwards.
PostgreSQL considerations
KCML exploits a similar fast load technology for PostgreSQL by using the COPY FROM statement. It is also enabled by default but can be disabled with the connection attribute. COPY FROM is more restrictive in that no other database access is allowed on the connection while a copy is in progress. PosgreSQL does not update any indexes which should be rebuilt after the operation.
BLOBs
Columns bound with KI_BIND_COL are generally not updated from the bound variables but there is special handling for bound BLOB columns. On an Oracle table bound BLOBs are written but all the rows in the buffer will have their BLOB columns updated from the same bound variables. As this is rarely what you want, you should only use one row buffers for a table with BLOB columns. KDB7 makes this explicit and bound BLOB columns are only written if the buffer has exactly the same size as the row length. Otherwise BLOBs are ignored.
Large BLOBs may require you to increase the size of the OCI direct load buffer by changing the _KDB_CONN_ATTR_DP_LOAD_BUFSZ connection handle attribute with KI_CONNECT_SET_ATTR.