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

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

That's true, and the point is that since I'm *not* doing a
singleton, I *could* expect that an Insert will be done. I included
this quote in the first place to indicate that the Help file is
pretty ambiguous and wasn't helping that much.

As it turns out, what this means is that the *Insert method* is
invoked on the destination dataset, so that if you're handling your
own data movement, there's something there for you to move it to, as
opposed to an *Insert Statement* being generated to match the
select. This explains the null rows when I was using Select.

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

>

An Insert statement is definitely the way <g>...

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

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

>

I'll look into IB_SQL and into IB_Monitor as well.

>
>
> Can you show the DstLinks that you have set up?
>

I wasn't setting any. Anyway, I just made an interesting
discovery.

This works fine:

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

*UNLESS* there's something in the InsertSQL property. I was using
the properties dialog to change the Select into an appropriate
Insert, then copying the Insert to the main SQL page. Unless the
InsertSQL property is cleared afterwards, you get the error I
reported on the first column name in the Insert, whether it's quoted
or not. I don't know if this a bug or a misunderstanding on my
part, but it was certainly a surprise to me.

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

>
> select
> "Def Status" as DefStatus,
> "Name" as aName,
> ID,
> "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.
>
> DefStatus=DefStatus
> ID=ID
> Accounts=Accounts ....etc.
>

That also works fine. When I tested with smaller tables, I used

Select "Quoted Field Name" as QUOTEDFIELDNAME, BAR2 from FOO;

and

Insert into FOO1("Quoted Field Name", BAR2) Values
(:QUOTEDFIELDNAME, :BAR2).

But then I realized the issue wasn't the quoting, but the presence
of InsertSQL.

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

Both excellent and maybe even superior suggestions. I'm basically
replacing BatchMoves, so I went for the Datapump straight away.

Also, these are in essence temporary tables. A conversation on this
newsgroup observed that there can only be 2^15 tables ever created
without doing a backup/restore (some internal ID or other
overflows).

Since I have a small app. used by just a few people at a time
(usually one, actualy), then I doubt this'll be a real problem. We
can just build in a backup/restore cycle that they can't avoid (a
good plan in any event, since they tend to skip this part and then
call up in a frenzy when their data gets trashed by BDE
corruption).

So my question is, are views subject to the same restriction?

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

And now you see why I'm getting away with keeping the TTable model
in the application until later <g>.

Thanks for your help and patience,

Michael D. Spence
Mockingbird Data Systems, Inc.