At 04:48 PM 05-10-02 +0200, you wrote:

>Hi,
>
>I'm looking for the best approach regarding speed vs. memory
>consumption for the following thing.
>
>I have a table storing metadata information, which looks like:
>
>CREATE TABLE IBLM$COLUMNLOGDEF (
> TABLE_NAME VARCHAR(31) NOT NULL,
> OPERATION VARCHAR(1) NOT NULL,
> COLUMN_NAME VARCHAR(31) NOT NULL,
> CONSTRAINT PK_IBLM$COLUMNLOGDEF PRIMARY KEY (TABLE_NAME, OPERATION,
> COLUMN_NAME)
>);
>
>A record usually looks like:
>
>TABLE_NAME OPERATION COLUMN_NAME
>--------------------------------------------------------------
>TABLE1 D ID
>TABLE1 I ID
>TABLE1 U ID
>
>I'm building a hierarchical view of all tables, the three operations
>DELETE/INSERT/UPDATE for each table and the table columns under each
>of the "operation node".
>
>During the creation of the hierarchical treeview, I have to check
>for each column and the corresponding operation whether a record
>in the above table exists or not.
>
>What's the best way to do that regarding speed, network traffic
>and memory consumption on the client (the result set of the above
>table can be pretty huge)?
>
>Probably there are different possibilities available:
>
>1) Using a parametrized query and executing each time something
>like:
>
>SELECT COUNT(*) AS COLUMN_EXISTS FROM METATABLE
>WHERE TABLE_NAME = ? AND OPERATION = ? AND COLUMN_NAME = ?
>
>2) Using a buffered TIB_Query and fetch all records from the
>above table to the client und use a Locate() to check the existance
>of a record
>
>3) Or any other IBO smarties I'm not aware of ;-)
>
>Any comments/hints appreciated.

Combine an "SQL smartie" with an "IBO smartie":

SELECT 1 AS COLUMN_EXISTS FROM RDB$DATABASE
WHERE (EXISTS
(SELECT 1 FROM METATABLE
WHERE TABLE_NAME = ? AND OPERATION = ? AND COLUMN_NAME = ?))

This will return error 335544374 if no record exists, so this is where you
can capitalise on IBO's OnError event and have a fast, low-memory way to
test for existence.

Helen