Subject Re: [IBO] defective datapump program
Author Helen Borrie
After I read through your reply to my reply, it occurred to me that you need to go back and review exactly what you are trying to do here...

At 02:18 AM 22-01-02 +0100, you wrote:
>using D3 and IBO v. 3.6.Dj, I built my datapump program.
>Actually it is a very simple program that uses 2 TIBOTable and 1 TDatabase.
>The TIBOTables are SrcTab e DstTab.
>SrcTab opens a local DBF e DstTab opens an identical FB table.

This part doesn't make any sense to me. How can you use a TIBOTable with a DBF file? Are you really trying to use a TDatabase with IBO components?

>For each record in SrcTab, the following code is run (StrLst contains the
>field names common to the 2 tables) :
>function TForm1.PumpData:boolean;
> i:integer;
> s:string;
> result:=True;
> try
> Database1.StartTransaction;
> DstTab.Insert;
> for i:=0 to StrLst.Count-1 do
> begin
> {read field name}
> s:=FieldName(StrLst,i); <-- what kind of an object is StrLst?

> {copy field value}
> DstTab.FieldByName(s).Value:=SrcTab.FieldByName(s).Value;
> end;
> DstTab.Post;
> Database1.Commit;
> except
> Database1.Rollback; {1}
> result:=me_ask('continue ?')=mrOk;
> end;

...then [snipped] stuff about commit and rollback (that's a separate issue).

Your datapump program really has to do this:

1. Open a BDE database - so you would use your TDatabase for this, attaching to the BDE alias for the database file containing the DBF table you want to read.
2. Open the DBF table - for this you would use a TTable and select your TDatabase as its Databasename property.

Then, for each record in the BDE table you would
a. Read each field object to get its data type and its data
b. If necessary, massage the data from the fields of the BDE table so that they can be passed, as valid Firebird/InterBase data types, into IBO input parameters (TIB_Column objects)
4. Check that a transaction has started and, if not, start one
5. Check that the IBO component you are going to use for the statement is prepared and, if not, prepare it.
6. Pass these values as parameters to an SQL INSERT statement.
7. Submit the SQL INSERT statement to the database API via a IBO component.
8. Execute the INSERT statement. --> If the server returns an error, handle it by calling Abort (and perhaps logging the key of the offending record into a stringlist).
9. If not end of BDE table, call Next to process the next source record.

Once you reach the end of the BDE table, call Commit.

No TIBOTable required. You have no need for a dataset here. You are just stacking up pending record versions on the server (which is the right place for them to be) waiting until you call Commit.

But you will need a TIBODatabase. Switching off its Autocommit property and take explicit control of its transaction. The IBO component you will need to embody your INSERT statement is a TIB_DSQL. Connect it to the TIBODatabase via its IB_Connection property.

The SQL property of the IB_DSQL will be
INSERT INTO ATABLE(list of field names)
VALUES( :Field1, :Field2, Field3,...... all in exactly the same sequence as the list in the first clause).

If you want an existing tool for this job, see whether the pdox2ib.exe utility will help. It works with either Paradox or DBase databases defined as BDE aliases. You can download it from the TechInfo page of the IBO website.

A TI-sheet on error handling is forthcoming in the next day or so.


All for Open and Open for All
Firebird Open SQL Database ยท