#WHERE

General Form:

     numeric_receiver = #WHERE(format$, [parameter [, parameter] ...]) [USING pool]

Where:
     parameter = :number[type]
     type = J or X

#WHERE() takes the valid SQL passed as part of the format$ string and constructs a SQL Handle for use with KI_START statements. The given SQL becomes part of any SQL WHERE filter that the KI_START constructs. The return value is of type SQLHandle

Parameters

The optional parameters passed to the #WHERE statement must match parameter placeholders in the format$ string. A placeholder is represented using :number. Where number is the index of the parameter passed to #WHERE call.

The database will support more data types than KCML. So its possible to optionally supply some extra type information for the parameter. See below:

Code	Description	Example
J	The input is a Julian date. Convert it to database DATE type.	#WHERE("fdate = :1J",nDate)
X	The input is a Hexadecimal string. Convert it to the database Hexadecimal/Raw type.	#WHERE("fhex = :1X", sHex$)
T	The input is a packed KCML timestamp. Convert it to the database timestamp type.	#WHERE("ftime = :1T", ts$)

Lifetime

The SQL handle returned will be tied to a pool allocation; by default, belonging to the pool of the current function. This means that to write wrapper functions around #WHERE it is necessary to pass a pool handle to become a parent of the new handle (see examples below).

Examples

 00010 LOCAL DIM pSQL AS SQLHandle
     : pSQL = #WHERE(".prefix_evalue2 = :1", 5)
     : pSQL = #WHERE(".prefix_edate < :1J", #DATE)

 00010 DEFSUB 'WrappedWhere(destination AS PoolHandle, sql$, VARARGS bindings) AS SQLHandle
     :     RETURN #WHERE(sql$, *bindings) USING destination
     : END SUB