--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> At 10:24 PM 9/08/2006, you wrote:
> >Is there sample code for this component somewhere?
>
> You might like to browse through the Files area at
> http://www.yahoogroups.com/community/ibobjects. There's all sorts

of

> stuff there.
>
> Are you referring to the IBO Help at all?
>

'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."

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
Insert").

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. It does say "Use the AfterFetchRow event to finish
assigning values...", which implies that the other three modes would
proceed on their own.

Hence my question about sample code <g>.

> >
> >For the dest. query, if I try setting the SQL to an insert, then
> >when I do the Prepare, I get "Invalid dml column reference: "Def

Status" ".

> >
> >If I try setting the SQL to a select, then using the TIB_Query's

property

> >editor to generate the insert SQL, I don't get the error above

but nothing

> >happens, either.
>
> Don't try that. Use a properly parameterised Insert statement or,

if

> you have one, an EXECUTE PROCEDURE with input parameters. But

what

> you can do before you scrub the TIB_query is copy the generated
> InsertSQL and use it as the basis for your dest dml statement.
>

See above for why I tried a select. BTW, when I did use a select,
the appropriate number of inserts occurred, but the strings were
null and the integers were zero (not null).

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.

>
> 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:
INSERT INTO CUSTG1
("Def Status", "Name", ID,
....
"Week Tax") VALUES (
:P1,
:P2,
:P3,
....
:P12
)


That gives the same error (still refers to "Def Status"), which
implies that it's objecting to the quoted field names, not the
params.

I'll try using a pair of tables with less exotic names and see what
happens.

>
> >Also, it isn't evident to me how one handles transactions here.

ISTM that

> >by default, AutoCommit is used.
>
> Unless you're mixing up TIBO and TIB_ components, you shouldn't be
> seeing Autocommit as a default setting of the
> DefaultTransaction. TIBODatabase *is* a legal TIB_Connection
> descendant and so is compatible with TIB_ components. However, it
> has the BDE's defaults, including defaulting its embedded

transaction

> to Autocommit. TIB_Connection creates its own DefaultTransaction

if

> you don't do it explicitly and it defaults Autocommit to False

unless

> something has changed.
>

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

> -- What are you using as the connection object[s] for the source

and

> 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.

> -- I strongly recommend using a dedicated transaction for this

kind

> of task (TIBOTransaction for TIBODatabase, TIB_Transaction for

TIB_Connection).

>
> > But suppose I have a very large dataset to move, and I want
> to batch the commits. May use OnAfterFetchRow to do a commit from

time to

> time?
>
> Well, OnAfterFetchRow is at a point where the latest row is

waiting

> to be mapped to the input parameters for the insert or stored
> procedure. This data hasn't been posted into dest yet. Use that

to

> map the dest parameters to the source output. AfterExecuteItems
> would be the place to test some counter that you are maintaining

via

> your OnAfterFetchRow handler and call CommitRetaining if the count

is

> 8000 since the last time it was reset. (You need CommitRetaining

if

> you are going to break a single pump execution into chunks,

because

> you need to hold the cursor on the source output. Just don't

forget

> to call a hard Commit on the last record, e.g. in AfterExecute.)
>
> If the whole datapump isn't going to be larger than around 8000
> records, don't bother to batch. The optimum size for batched

inserts

> is in the range of 8000-10,000, though it will be less if there's

a

> lot of other stuff happening at the same time.
>

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.

> You might also like to refer to the DstStatement's events. The
> DstStatement is a TIB_Statement. AfterExecDML could be useful, I
> suspect, if it precedes the datapump's own AfterExecuteItems.

I'll look at those things. Thanks, Helen.