The data pump simply performs the statement in DstStatement for each record
selected in the SrcStatement and aligns the input parameters in the
DstStatement with the fields in the SrcStatement. There is nothing else
going on other than this simple action.

Jason

-----Original Message-----
From: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com] On Behalf
Of Chuck Belanger
Sent: Wednesday, July 24, 2013 10:57 AM
To: IBObjects Listserve
Subject: [IBO] Does the Datapump in an UPDATE, update a record more than 1x?

Hello all:

Is the Datapump component supposed to be updating the destination dataset
records more than one time?


Saw something in the use of the IBO component, Datapump, which I have
never seen before and am wondering if this is correct behavior or not.
(using the latest 5+ version)

Recently adjusting the use of the datapump IBO component for use while
using a separate DB to update a user desktop database.

I am updating a table which is used in a Tree, thus it has record IDs as
well as, Parent IDs to set up the proper treed structure.

When initially pumping over the new information the Parent ID of the
user's DB is replaced with the Parent ID of the import table. It is
incorrect for the user's system and needs to be replaced with a correct
reference to the user's ID which corresponds to the Parent ID. By adding
a field, Source_Id, which contains the import table's ID for that item
used to update the user's table and the Datapump, I am able to correctly
reset the Parent Id of the user's table.

In the Datapump both the SrcDataset and the DstStatement act on the same
table, Registry_Items. The update from the import DB/table has just been
completed before this datapump in question. Now I am trying to reset the
Parent ID to get the correct tree structure for the user.


SrcDataset:

SELECT RI_ID, SOURCE_ID
FROM registry_items
WHERE SOURCE_ID is not Null
and RI_HAS_CHILDREN = 1

DstStatement:

UPDATE Registry_Items
SET RI_PARENTID = :RI_ID
WHERE RI_PARENTID = :SOURCE_ID
AND PT_ID = -1
AND SOURCE_ID is not null

What I am observing is that if I do not have the "and RI_HAS_CHILDREN =
1" added to the source dataset and if by coincidence the Source IDs
(which is the PK, RI_ID, for the Source table that is used to import the
update) have record matches in the destination table, the execution
seems to be updating the RI_PARENTID field at least 2x in such a way
that the first replacement is correct for the record, but then the
Datapump continues, even though the record was updated, to find a match
and replace the RI_ParentID a second time. That second update overwrites
the first update with an erroneous record ID, although coincidentally in
the Source_Id dataset.

A single example:

"Chemical" PK_ID = 24892, ParentID = 17445 Source_ID = 17828

It is the ParentID we are replacing and 17445 corresponds to a record in
the user table that is 18868. That is correct BTW.

But what happens next is that 18868 exists in the Source_ID column and
corresponds with ID = 26352 and it is this ID which
is the final update for the original "Chemical" record, not 18868. The
only way to get to 26352 is in this two step update.


So, my question is:

Is it normal behavior for the Datapump to replace records more than one
time? To continue finding records that match even if already updated?
Without knowing how the datapump component works, I have been assuming
that there would be a single update for each record.My observation of
specific records show that initially the update replaces the RI_ParentID
with the anticipated ID, but then if that ID is in the SrcDataset, it
will be replaced again. This time pointing to an erroneous record.

What I found was by more narrowly defining the records that need to be
updated, then the updates were correct. The above statements are the
ones that I found work.

Thanks for any comments,

Chuck Belanger


------------------------------------

___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !
Yahoo! Groups Links