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?

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

>
>Here's the insert SQL:
>
>INSERT INTO CUSTG1
>("Def Status", "Name", ID,
>"Accounts", "Grand Total",
>"Grand Tax", "Year Total",
>"Year Tax", "Month Total",
>"Month Tax", "Week Total",
>"Week Tax") VALUES (
>:"Def Status",
>:"Name",
>:ID,
>:"Accounts",
>:"Grand Total",
>:"Grand Tax",
>:"Year Total",
>:"Year Tax",
>:"Month Total",
>:"Month Tax",
>:"Week Total",
>:"Week Tax"
>)
>
>
>The column is quoted, but I'm getting the impression that this isn't the
>issue,
>but that there's some property I haven't set correctly in one of the
>components involved.

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.

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

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

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.

Helen