Subject Re: [IBO] IB_Datapump sample code
Author Helen Borrie
At 07:54 AM 10/08/2006, you wrote:
>'Course I am. I'm sure the section on TIB_DataPump is fine as a
>reference, but it's pretty vague in a lot of places. For example,
>the property DstIsSingleton is described like this:
>"If the destination is a dataset with a select statement it will
>normally consider that it needs to perform an Insert but if the
>select is a singleton then it should bind to the parameters and
>perform a singleton fetch upon execution of each item."

But you're not doing a singleton here...

>This implies to me that if DstIsSingleton is false, then one would
>set up the dest. dataset as a TIB_Query with a Select statement
>describing the fields to be changed, and that the TIB_DataPump code
>knows that it should use the InsertSQL ("it needs to perform an

Your deduction logic's not clear to me....but I don't know that it
matters. I just strongly recommend NOT using a SELECT statement for the Dst.

>There's also not very much information on exactly what happens to
>the fields and who does things (me or the pump) when the data is
>being pumped. Of the four modes (Direct, Assigned, Customized Item
>Handling and AfterFetchRow Event Handling), I'd've thought that
>Direct (and maybe Assigned) would be something that would just
>happen. But the help file never clearly spells this out one way or
>the other.

Actually, if you look at the text for DPItemActions, it's reasonably
clear what each style of assignment means. You can inspect
DPItemActions at run-time to see what the pump has done (or not done)
with each assignment.

There is a fully worked demo of the component in IB_SQL. You have
the source for it in the IBO root. If you want to watch what
happens, you can compile it and try it out; or simply open the exe
that the installer compiled for you into the Tools subdir. (The
datapump tool is launched from the toolbar on the Connection page of IB_SQL).

> It does say "Use the AfterFetchRow event to finish
>assigning values...", which implies that the other three modes would
>proceed on their own.

Assignment happens on a field-by-field basis. DIRECT is the simplest
- it's where the component is able to make a direct assignment from
source fieldname to dest input parameter according to the DstLinks,
because the data types are identical or compatible. With ASSIGN
(which the help text says is the default) it explicitly assigns that
particular field, doing a conversion if it needs to. The other two
need intervention. In the Datapump tool you can see what it has done.

>Hence my question about sample code <g>.

I'm sorry I didn't think to draw your attention to IB_SQL. And this
is also a definite case for availing yourself of an IB_Monitor in your app.

>It's like the mapping doesn't get done, even though the property
>DPItemActions said they were all DIRECT. From the help file, I
>gather that when a column is DIRECT, the dest. dataset just gets the
>value directly from the buffer of the source, without any
>intervention required on my part.

Can you show the DstLinks that you have set up?

> >
> > It's not the database column name that's the issue. In your code,
> > Params are column objects too. But, unlike database columns, they
> > are not passed across the API by name, but by statement
> > position. Parameter names are a local invention, subject to local
> > (Delphi) law. Replace the parameter names with legal Delphi
> > identifiers, e.g. GrandTotal, etc., and map the parameters
> > individually to the columns in the input list.
>If you mean like this:
>("Def Status", "Name", ID,
>"Week Tax") VALUES (
>That gives the same error (still refers to "Def Status"), which
>implies that it's objecting to the quoted field names, not the
>I'll try using a pair of tables with less exotic names and see what

Can I suggest that you use the same table, but extract from the
source into derived fields? This has worked for me in cases where
I'm pumping from an IB 6 table with a "forbidden" field name, such as
TYPE. It sort of rings a bell, that the component doesn't always
play nice with identifiers that were illegal when the component was
written (it predates quoted identifiers by about 8 years).

So your src query would look something like this (though not right,
as I'm supposing some of these output fields are already derived
fields, begging the question why you're giving them difficult identifiers....):

"Def Status" as DefStatus,
"Name" as aName,
"Accounts" as Accounts,
"Grand Total" as GrandTotal,
"Grand Tax" as GrandTotal,
"Year Total" as YearTotal,
"Year Tax" as YearTax,
"Month Total" as MonthTotal,
"Month Tax" as MonthTax,
"Week Total" as WeekTotal,
"Week Tax" as WeekTax
from YourSourceTable

For the input params, use the same identifiers as the derived
fieldnames and just do the simplest DstLinks, viz.

Accounts=Accounts ....etc.

>Whups! Guilty. I'm using a TIBODatabase to connect.

Well, that's OK. Just don't neglect its properties.

> > -- What are you using as the connection object[s] for the source
> > target database[s]? (The plurals here are because you have said
> > anything about the source and dest.)
>Both tables are in the same database. In BDE terms, I'm saving the
>result of a query as a table, so it can be further manipulated.

Why on earth are you using a datapump? There are at least two ways
to do this via SQL. The most obvious is a view (so you don't even
have to *think* about it on the client side, it's just *there*), the
next is the DML construct

insert into atable
(val1, val2, etc)
select val1, val2, etc from btable

>Oh. Well, AFAICT, all of the code that uses this would consider a
>couple of hundred rows a lot, so I guess I'm safe enough with one
>big commit.

Erm, 200 rows is an exceedingly TINY batch. Most sufferers are
seeking help when resources run out on batches of 2 or 3 million inserts.