At 09:14 AM 21/06/2008, you wrote:

>Helen Borrie wrote:
>> At 05:17 AM 21/06/2008, you wrote:
>>> I need to get the table names, field names and base types for each
>>> table. I just cannot figure out how to do this using IBO. Would someone
>>> give me a quick overview?
>>
>> At design time? or in run-time?
>
>At runtime. I basically want to read a CSV file with field names
>included in the first line, map them to fields in the target table,
>check the target field's base type (bigint, timestamp, integer, varchar,
>etc) and convert the incoming data appropriately.
>
>In the ADO components, the database component can give you a list of
>table names. The table component can give you properties of each table.
>I'm looking for the equivalent using IBO.

Well, as you probably know, IBO isn't an ADO driver. ;-)

What IBO is, is a native data access interface for the Fb and IB APIs. And, as you also know, the API doesn't expose structures for informing the world about "tables". So the API gives you two ways to get this information:

1. You can query the system tables and bring *metadata* across to your client app. This can be quite complicated, to get the full range of information you want for mapping the data in a flat text file.

For getting the full range of the names of "tables" available takes a relatively simple query of one system table (RDB$RELATIONS). So, if your program user needs to select a table from a list, you'll need the output of SELECT RDB$RELATION_NAME FROM RDB$RELATIONS [ORDER BY 1]. This can be done once with a TIB_Cursor, feeding the names into a stringlist in a FOR loop.

2. As you also know, Fb and IB don't deliver "tables". They deliver *sets". The nearest thing you will get to "table properties" will be what the API delivers in response to preparing a SELECT * query of the relation. This is economical, since you will (presumably) be processing one table at a time. IBO exposes all of the information you want as soon as a successful Prepare completes. (Consult the helpfile to see the properties of TIB_Dataset.) You never need to open this query, since you are interested only in the metadata ("table properties" in ADO-talk). Prepare it, read the info and then unprepare it, since you won't need it again. Your ETL stuff will be inserts and updates, which will use TIB_DSQL, prepared once and executed many times.

Now, IBO does have some components that prepackage some of this stuff. Take a look at TIB_Import. You can demo it for yourself since it's available as a utility in IB_SQL. You can view the source code of the IB_SQL project in your IBO root directory. It uses mainly pre-packaged forms that are right there in the IBO sources themselves, since the IB_SQL code is implemented as the design-time editor of TIB_Connection and its descendants.

Also look into IBO's client-side caching capabilities (TIB_Connection, starting with the SchemaCacheDir property). If you're changing metadata in the target database constantly this is probably going to be far too noisy. However, if the metadata is stable, it might be an option to read some of the info you want from the client's local schema cache at one or more points in your workflow. Personally I don't favour this approach for client/server. For me, client/server applications ought to be minimal - "as much as you need and no more". I neither need nor want a local schema cache...but there ya go...it's what a gazillion Delphi users think they need, even all these years since the Death of the BDE! ;-)

Helen