DEFQUERY

General Form:

DEFQUERY queryname
<embedded DEFQUERY>

DEFQUERY is intended as an optimization to allow KDB to access SQL databases more efficiently by minimizing the number of round trip calls made to the database server. It allows a programmer to specify an SQL like representation for a subset of the database accesses that will be performed in a program. This is defined by embedded XML buried in the program following the DEFQUERY statement. A graphical editor in the workbench allows the editing of this XML in an intuitive way. When the query object is instantiated with a CREATE statement. A Prepare() method will generate actual SQL using the tables names currently open on handles specified in the XML. An Execute method will cause the SQL to be sent to the database server and a result set generated. Theses rows can be accessed through a new set of object methods or they can be retrieved in a compatibility mode by regular KDB calls.

Compatibility mode

This second mode allows existing programs to be made more efficient by adding the query specification but leaving the existing KDB calls unaltered. Clearly the calls and the SQL must achieve the same result. The handles that are used in a query are marked as linked to that query. START operations are checked against the current query rowset for a linked handle and if plausible the handle is marked as able to use the rowset buffer for subsequent KI_READ_NEXT operations. Similar logic is used to see if KI_READ can be performed using the current row.

The DEFQUERY statement itself is purely declarative and acts as an anchor for the XML query definition. To instantiate a DEFQUERY object you need to use the CREATE verb with a USING DEFQUERY clause e.g.

OBJECT q = CREATE "dynamic","query",USING DEFQUERY queryname
IF (q.Prepare() <> _KE_SUCCESS)
	...
END IF

The first two arguments must be "dynamic" and "query". The name following the USING DEFQUERY is the name used in the DEFQUERY statement itself. The create operation will parse the XML and generate a skeleton SQL statement. The actual SQL is generated at the time of the prepare method when the actual tables will be open on the specified handles and their names can be determined.

Documentation

The methods and properties are documented by the query object here.

Methods all return a KDB error code unless specified otherwise. Tables in a view are numbered from 1.

Placeholders

The CREATE statement may be followed by a list of variables to be substituted into the XML definition where placeholders are found. These are positional in that the placeholder $1 corresponds to the first variable in the list, $2 the second, and so on. This substitution mechanism can be used for handles and bind variables.

Parameters

Parameters can be used in the SQL when it is being prepared. They are positional and are identified by a colon and the position number (starting at one), e.g. :1. When the query is executed the actual values for the parameters can be supplied as arguments to the Execute() method. Alternatively there is a BindParam() method call that can be executed between the Prepare() and the Execute() that allows parameters to be bound. The parameters are held then as part of the query definition and if the query is reexecuted without specifying any new values then these original values will be used. The following example explicitly sets :1 to 'A01' and :2 to 'B01' when executing the query:

IF (q.Execute("A01", "B01") <> _KE_SUCCESS)
	...
END IF

Operation

The SQL is generated and compiled by the Prepare() method. A result set is then generated on the database server by the execution of the Execute() method. The result set is made up of the columns of each table, laid out from left to right in the order of the tables in the XML. The Fetch() method can be used to fetch these columns for a given table in that table's row buffer. Fetch() will always attempt to get the next unique row for that table which may advance the cursor in the whole result set however if all the columns in the row for the table are NULL, as can happen in an outer join, then it will return KE_ENDOFRANGE. It will also advance if the rowid for the table has not changed because there are joined rows in tables to the right. If the rowid on the table to the left changes it will return KE_ENDOFRANGE.

Supported KDB calls in compatibility mode

There is a requirement that all key columns used in a START or a READ must be part of the view in the query. It is not currently enforced.

KI_START_BEG	Has no effect.
KI_START and KI_START_ROW	The handle must be in a query and using the same index path. KCML will check that the current result set row has the same values as the columns in the key specified. In other words it is the same as a KI_START_ON for all the columns of the index.
KI_START_ON	Sends prepared SQL to the database and generates a result. Can be executed more than once with different parameters.
KI_START_BETWEEN and KI_START_ROW_BETWEEN	There is currently no check on the max row count or on reaching the end key.
KI_START_FIRST	There is currently no check on the max row count.
KI_READ_NEXT	There must have already been a KI_START operation performed since the query was executed.
KI_READ and KI_READ_RANDOM	The key required must be compatible with the current row of the query.

If a request cannot be satisfied from the result set buffers then it will be executed directly unless the Strict attribute is set whereupon an error will be thrown.

Points to note

There is limited editor support for the XML. Double clicking the <embedded DEFQUERY> line will bring up the XML in a text editor window which will allow it to be viewed and edited but there is no check that the edited XML is well formed.
You must not mix method calls like FetchByHandle() with KI_READ_NEXT compatibility calls
This is not transactional. KI_READ_HOLD will be executed directly.
A handle to be used in compatibility mode can only appear in one DEFQUERY at a time.
The rowid buffer is currently shared between a query and the handle and may not be reliable if compatibility calls are mixed with KDB calls on the same handle that do not meet the criteria for using the buffered rows.
Only one negative index path per query is supported. It is implemented by adding an ORDER BY ... DESC clause for the columns of the index indicated by a bwd attribute and may impact the performance of the query if the SQL optimizer is unable to exploit an index for this.
If the jointype attribute is omitted from all the tables then a pre-ANSI join syntax is used in which the join clauses are added to the WHERE clause. This allows the use of the deprecated Oracle join syntax to work around bugs in Oracle's implementation of ANSI outer joins.

XML tags

These are subject to radical change

Tag	Level	Purpose
xmlData	0	Root node
defquery	1	Top level node for query specification. Can take a comment attribute which will appear as a comment in the SQL. The id can also be set with a property.
help	2	Optional text for tool tipping in the workbench
tables	2	Surrounds the tables in the query
table	3	One tag for each table or view. Takes attributes.
where	2	Any additional selectivity required in the WHERE clause of the SQL. Takes a single attribute of sql
orderby	2	Any additional sorting required. Added as an ORDER BY clause of the SQL. Takes a single attribute of sql and consists of a comma separated list of column names. Use with care as it may make the database optimizer disregard expected index paths.
hints	2	Optional Oracle optimizer hinting clause. Takes a single attribute of text. If missing the hint of FIRST_ROWS is used which will discourage table scanning. This can also be set or overridden using the hint property of the query object.
comment	2	Optional comment to be included in the SQL, following any hints, to identify it.
limits	2	Optional. Applies constraints. Takes a single attribute of maxrows indicating the maximum number of rows in the result set. If zero it is ignored.
performance	2	Optional. Attribute prefetch can be used to set the number of rows to prefetch from the database in the query. If zero or not set the default of 10 is used.

XML attributes for the <table> tag

Attribute		Purpose
hand	Optional	Variable containing the handle for the table. If missing it must be supplied with a call to the SetHandle() method before the Prepare() method is called. The handle must be open on a table at the time of the prepare.
view	Mandatory (but see rowbuf)	The name of a DEFRECORD or DEFVIEW which defines the columns for the table. Alternatively the view can be specified using the rowbuf attribute.
rowbuf	Mandatory (but see view)	An exclusive alternative to the view attribute. The name of a scalar string row buffer DIMed using the view. This should be the row buffer to be used in accessing that table.
path	Mandatory	The index path (positive and counted from 1) to be used for the join and for compatibility mode access to the table. You need to ensure that the columns of that index are in the view. This may be a numeric constant.
bwd	Optional. May appear on only one table.	If set to true then the index path for this table is traversed backwards using a negative path. An ORDER BY clause is added to the SQL for this purpose so no explicit orderby tag should appear or it will take precedence.
hint	Optional. May appear on only one table.	If set to true then an additional index hint will be generated naming this table and the index implied by the path attribute.
join	Must be present on all tags except the first	A join clause expressed in SQL to be used on the ON clause between the previous table and this table.
jointype	Allowed only in combination with join attribute.	Determines the type of join with previous table. Permitted values are "INNER", "LEFT OUTER" and "RIGHT OUTER". If missing then INNER is presumed. Case insensitive. Proprietary Oracle join syntax is always generated on Oracle connections due to the numerous undocumented restrictions and bugs in their ANSI join syntax support. This assumes simple equi-joins of two columns with the left table column on the left, and the right table column on the right, of the '=' sign.
partcol	The name of the partitioning column in a partitioned table.

Tables will be given aliases of a, b etc. according to their position in the sql. If a table appears more than once its columns will be qualified in the SELECT column list otherwise column names are assumed to be unique. You will need to qualify the columns of such tables in any WHERE clause you add to the XML.

Handling BLOB columns

Include the relevant BLOB columns in the view. Set the FLD (of type BLOBSYM) to the SYM of a string that will receive the BLOB contents. When the row is read the bound variable will receive the column contents. Only scalar string variables can be bound this way. The length of the string will be dynamically sized to the BLOB size. You can set the FLD to zero if you don't need the BLOB on a particular Fetch().

Example XML

<xmlData>
	<defquery>
		<help>Load an order header and its lines</help>
		<tables>
			<table hand="hOrders" view="cf_orders_view" path="1" />
			<table view="cf_lines_view" path="1"  join="pr_order=li_order" />
			<table hand="hParts" view="cf_parts_view" path="1" join="li_prod=pa_partno" />
		</tables>
 		<where sql="pr_order between :1 and :2" />
	</defquery>
</xmlData>

Compatibility

DEFQUERY was introduced in KCML 6.60.