>Sorry, Ed, but it's not being reverted unless you (unwisely or
>inadvertently) have code there that is making it so; or you have a
>user at that site with an admin tool that enables him/her to reset
>generators. The transient nature of the problem report suggests the
>latter more strongly than the former...but DO eliminate the
>possibility that your code can push a negative number into the
>second argument of your Gen_ID() function call sometimes.

I searched my HD for references to GEN_PERSON_ID and Gen_ID just to
check. No forgotten code, nothing causing the problem.

> In a previous posting you said this import routine was doing batch
>inserts. How can this be compatible with retaining the latest
>generator value and applying it to detail inserts.

I don't retain it. Code is below. Note there are two DSQL statements,
one for inserting the record, the other for updating a record that
exists (e.g. updating account balance for an existing value).

Note that I am not trying to hide my code--glad to share it, just try
to keep it in context and not overload with too much information.

The proc below is called each time a person needs to added/updated.

procedure TdmImportTRAKData.AddUpdatePersonInfo(const aFound: boolean;
const aClientID: integer);
var
lPersonID: Integer;
ldsql: TIB_DSQL;
...
begin
//see if we haver person info, only might exist if the
//client existed
lPersonID := -1;
if aFound then
begin
//master record found--see if detail exists.
curLocatePerson.Params[0].AsInteger := aClientID;
curLocatePerson.First;
lFound := not curLocatePerson.EOF;
if lFound then
lPersonID := curLocatePerson.Fields[0].AsInteger;
curLocatePerson.Close;
end
else
lFound := False;

if lFound then
ldsql := dsqlPersonUpdate
else
begin
lPersonID := FIBConn.Gen_ID('GEN_PERSON_ID', 1);
ldsql := dsqlPersonAdd;
end;

...assign param values next, the .ExecSQL, then detail records.

> It seems there's more to this than you are telling us...and
> we're all trying to help capture a bunny-rabbit in a minefield.
>The only code we have seen so far is a snippet showing us how you
>are getting hold of a generator value and poking it into a local
>variable. We haven't seen the SQL for the insert statement, nor
>the Delphi statement where you assign the local variable to a
>parameter.

The SQL statement for the insert is:

<SQL Insert>

insert into PersonInfo

(PERSON_ID, CLIENT_ID, PaycheckValue, PERSON_TYPE_ID, DOB, RETIREAGE,
RETIREMONTH, GENDER_ID, SSMETHOD_ID, DOE, ENTEREDPLANDATE, EMAIL,
DBKNOWNVALUE, SSStartMethod, SSValueMethod, Raises, PensionPlan_ID,
DCPayoutMethod_ID, DCPayoutRate, DCBalance, DCRate, DCEEElective,
DCERPERCENT, DCERMATCHPERCENT, DCERPERCENTMATCHMAX,
DCERDOLLARMATCHMAX, RAISES, RAISECHECKNO, ENCRYPTED, SSPERCENT)

values

(:PersonID, :ClientID, :PaycheckValue, 0, :DOB, :RetireAge,
:RetireMonth, 0, 0, :DOE, :EnteredPlanDate, :EMail, :DBKnownValue,
:SSStartMethod, :SSValueMethod, :Raises, :PensionPlan_ID,
:DCPayoutMethod_ID, :DCPayoutRate, :DCBalance, :DCRate, :DCEEElective,
:DCERPERCENT, :DCERMATCHPERCENT, :DCERPERCENTMATCHMAX,
:DCERDOLLARMATCHMAX, :RAISES, :RAISECHECKNO, :ENCRYPTED, :SSPERCENT)

</SQL Insert>

and here is the before insert trigger for the table:

as BEGIN
IF(NEW.PERSON_ID IS NULL) THEN NEW.PERSON_ID = GEN_ID(GEN_PERSON_ID,1);
IF (NEW.GENDER_ID IS NULL) THEN NEW.GENDER_ID = 0;
execute procedure ClientInfoChanged(New.CLIENT_ID);
END

note that ClientInfoChanged just updates the last modified date/time
for the client (ClientInfo table, the master table for PersonInfo).

> So tell us more. We are trying to help you, not steal your lunch.

I generally don't eat lunch, if I do it's pretty sparse (a consequence
of a sitting job)--so you wouldn't get much. But I'd gladly provide
lunch for you. Let me know the next time you are in Portland, Oregon. :-)

Ed Dressel