TIB_Query Object

Unit
IB_Components

Declaration
TIB_Query = class(TIB_BDataset)

Description
It is possible to process ALL InterBase DSQL statements via this component.

It is primarily a buffered scrollable Dataset that is ideal for browsing and providing a randomly scrollable dataset.

This component also has many built-in features to do things like incremental searching, filtering, finding, etc.

Even though the KeyLinks property is not required it normally is defined to uniquely identify each row of the output data. If KeyLinks is undefined then a unique numerical value will be arbitrarily assigned to each row of output.

The KeyLinks entries could be columns from a primary or unique key or you can also use the DB_KEY record pointer if there is neither of these keys available. The dataset will not be insertable if the DB_KEY is used though.

IMPORTANT: It is important to know that now every type of SQL statement can be used in this component. But, pay very close attention to what is going on in the IB_Monitor to see if it will give the dataset that you are expecting.

This biggest gotcha is if you place join criteria in the WHERE clause. IBO does not like this in the buffered query and problems could result.

It may be necessary to set FetchWholeRows to true in order to avoid getting an incorrect result set for a SELECT statement. This makes it so that the SELECT supplied will be used without any manipulation to the SQLSelect portion of the statement. Since FetchWholeRows must be true when selecting from a stored procedure it is ignored if it is set to false.

Another example of a statement that must use FetchWholeRows is:

SQL: SELECT DISTINCT( COL ) FROM TABLE KeyLinks: TABLE.COL

The reason that you will NOT get the expected Dataset with FetchWholeRows as false is because the IB_Query component's Dataset is defined by a special cursor derived by the KeyLinks property. This special cursor for the above configuration would look like this:

SELECT TABLE.COL FROM TABLE

and then the scrolling cursors will look like this:

SELECT DISTINCT( COL ) FROM TABLE WHERE TABLE.COL=?BIND_COL

The scrolling cursors are designed to fetch a single record per each key that is fetched by the special cursor. This is so that the buffering can be optimized to only fetch the whole records that it needs on an as needed basis.

If a user wants to see the last row of their Dataset then the internal cursor fetches in all of the key columns only which is very quick if it is only four bytes wide. Then, when it gets that last one it takes the key and puts it into the input parameter of the scrolling cursor in order to fetch the whole row buffer. Thus, a very narrow series of fetches were performed instead of a whole bunch of wide fetches.

SELECT statements that include a JOIN are possible to use with this component if the JoinLinks and KeyLinks are properly defined. Just remember that it is necessary that each row of the output be uniquely identified by the KeyLinks and that the JoinLinks are applied to both the special cursor for keys and the scrolling cursors used to fetch the whole individual record buffers. Thus, JoinLinks plays an important part of making sure that only individual rows are fetched by the scrolling cursors.

For information on its properties, methods and events see the TIB_Statement, TIB_Dataset, and TIB_BDataset classes for more information.