KI_PREPARE
| Argument | Enumeration | Purpose |
|---|---|---|
| handle | Handle | |
| sql$ | SQL statement | |
| extratext$ | Extra SQL | |
| status | KDB_ERROR_ENUM | Return status |
| colcount | Number of columns in result set |
KI_PREPARE
This parses the SQL statement in sql$ generating an execution plan which is stored against the handle. This can then be executed with KI_EXECUTE. The number of columns in any result set is returned in the optional colcount which will be zero if there is no result set, as in a DELETE or UPDATE.
If a negative handle number is passed then only SELECT statements can be parsed as a security measure.
Note: A KI_PREPARE/KI_EXECUTE/KI_FETCH block must be followed by a KI_CLOSE before the handle can be re-used to parse another SQL statement or open a table.
In most cases where there is only one KI_EXECUTE for each KI_PREPARE then the combination function KI_SQL should be used as this can allow KCML to transparently recover from a database failure in a clustered database.
The SQL grammar passed to the function should match the KDB ODBC driver grammar (ANSI-92 with Microsoft extensions) as it will be parsed by the KDB SQL engine and rewritten to match the actual backend database SQL. You can examine the SQL generated by enabling SQL tracking with a handle attribute KDB_HAND_ATTR_TRACKSQL. If you need to use SQL expressions unique to the backend then you can prefix the string with an ! and the rest of the string will be passed directly to the database without interpretation.
When rewriting SELECT statements KCML will pass through all quoted identifiers as is. It will issue upper case reserved words and will uppercase identifiers other than column names which will be lowercased unless quoted. This assumes that the database is not case sensitive and internally uppercases identifiers, as is the case with Oracle and KDB. As PostgreSQL will lowercase internally table names are always quoted for PostgreSQL backends.
KDB positional parameters represented by ? tokens will be replaced with appropriately numbered parameters for Oracle and PostgreSQL.
SQL functions will be issued in either the ANSI-92 form, if supported by Oracle, or in an Oracle specific form for Oracle otherwise. For example SUBSTRING is not supported by Oracle and SUBSTR will be substituted. PostgreSQL has good ANSI compatibility.
DATE and TIMESTAMP literals will be issued in ANSI-92 format with a type prefix, e.g. DATE '2001-03-29', as this format works with all databases.
On Oracle and Postgres the optional extratext$ parameter will be appended to CREATE TABLE and CREATE INDEX statements, after any default storage and tablespace clauses, to allow custom partitioning schemes to be deployed.