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

>
> At 01:09 PM 16/08/2006, you wrote:
>
> > > Simply make it an output field in your source query.
> > >
> > > select
> > > fielda,
> > > fieldb,
> > > fieldc,
> > > '12345' as mysessionid
> > > from blah
> > > where.....
> > >
> > > In this example, the constant comes through as a varchar(5).
> >Adapt
> > > your statement to make sure your constant's type is compatible
> >with
> > > the destination column it's mapped to and you're done.
> > >
> > > Helen
> > >
> >
> >This query is constant. Although I don't prepare it and reuse it,
> >and making a new SQL string each time is not really a big deal, I
> >could see that it might be in other situations. The results of

the

> >query go to a temporary table for additional processing, so I

wanted

> >to add a unique, per-instance number in a new column when I pumped
> >the data into the table. It seemed to me to be a more elegant
> >solution to introduce the session ID at that point, since it's

just

> >there to emulate a temp. table.
> >
> >If there's no other way, then I'll just produce a new SQL string

for

> >the query each time, incorporating the session ID, but surely this
> >is what the DataPump's events are for?
>
> No other way? Learn about parameters, dear fellow!

Tried that, couldn't get it to work. It kept complaining
about 'Unknown Type', even though I'd set the parameter type and
various combinations of parameter direction. I figured it was
because I was trying to insert a literal into the select list,
rather than a field name, so I moved on.

>
> >Would making a custom DPItem
> >class be another way to do this?
>
> Creating a mackerel to catch a sprat?
>

Yeah, does seem excessive <g>.

> Here's a useful quote from the help:
> "Use the AfterFetchRow event to finish assigning values to the

input

> parameters that need customized attention. Be sure to NOT refer to
> the Dataset SrcDataset since it is NOT the actual dataset being

used

> to feed the process. It is using an internal cursor that is passed

in

> as a parameter the the event. You should refer to the Statement

though."

>
> So, amongst whatever else,
>
> with MyPump.DstStatement do
> ParamByName('SessionID').AsInteger := SomeValidInteger;
>

Ah. So referring to DstRow (see original question) was the problem
all along.

Macro substitution is what I ended up with, along with ditching some
of the temporary tables (which all had the same format) in favor of
queries on a single table which now includes an appropriate flag. I
can do "Insert into temptable(blah, blah, Session_ID) select blah,
blah, <<SESSIONID>>". I can get the same result as the other tables
by using queries like "select blah, blah from temptable where
Session_ID = <<SESSIONID>> and FLAG = 'S'" (or whatever FLAG should
be). Then, when I'm done and where the temp tables used to be
deleted, I can just do "delete from temptable where Session_ID =
<<SESSIONID>>" and all is well.

Anyway, thanks!


---------------------------
Michael D. Spence
Mockingbird Data Systems, Inc.