How to returning a field on Insert

[nuthling]

Hi

I’m just wondering if any of you can maybe give me some pointers on how to retrieve the inserted ID on the following table:

Code: Select all

CREATE TABLE "SYSTEMUSERS" (
   "IPKSYSTEMUSERID" INTEGER NOT NULL
  , "DCREATED" TIMESTAMP NOT NULL
  , "DMODIFIED" TIMESTAMP NOT NULL
  , "BDELETED" SMALLINT NOT NULL
  , "SUSERNAME" VARCHAR(256) NOT NULL
  , "SPASSWORD" VARCHAR(256) NOT NULL
  , "SEMAIL" VARCHAR(256) NOT NULL
  , "SIPADDRESS" VARCHAR(256) NOT NULL
  ,PRIMARY KEY ("IPKSYSTEMUSERID")
);

CREATE GENERATOR "G$_IPKSYSTEMUSERID";

SET TERM ^ ;

CREATE TRIGGER "T$_IPKSYSTEMUSERID" FOR "SYSTEMUSERS" ACTIVE
  BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW."IPKSYSTEMUSERID" IS NULL) THEN
  BEGIN
    NEW."IPKSYSTEMUSERID"= GEN_ID("G$_IPKSYSTEMUSERID",1);
  END
END^

I’m using firebird 1.5 and zeoslib dbo 6.1.5. I did read the "The ZeosLib DBOs 6.1.5 - With Delphi 7 and Firebird 1.5" article.

I’m using TZTable and TUpdateSQL to connect to the table and the following object pascal code to insert my record

Code: Select all

function TdmData.InsertSystemUser(aUserName, aPassword,
  aEmail: string): Integer;
begin
  tblSystemUsers.Insert;
  tblSystemUsers.FieldByName('IPKSYSTEMUSERID').Clear;
  tblSystemUsers.FieldByName('DCREATED').Value:= Now;
  tblSystemUsers.FieldByName('DMODIFIED').Value:= Now;
  tblSystemUsers.FieldByName('BDELETED').Value:= 0;
  tblSystemUsers.FieldByName('SUSERNAME').Value := aUserName;
  tblSystemUsers.FieldByName('SPASSWORD').Value := aPassword;
  tblSystemUsers.FieldByName('SEMAIL').Value := aEmail;
  tblSystemUsers.FieldByName('SIPADDRESS').Value:= TCPClient.LocalName;
  tblSystemUsers.Post;
  //tblSystemUsers.CommitUpdates;
  //tblSystemUsers.Refresh;
  try
  Result:= tblSystemUsers.FieldByName('IPKSYSTEMUSERID').Value;
  except
  end;
end;

The exception I get is where I return the 'IPKSYSTEMUSERID', it tells me it can’t cast a NULL to INTEGER. Which is fine for me because that ten to one means it didn’t retrieve the generated ID.

May problem is I need the keep track of the record. Is there a way I can force the DBO components to retrieve the 'IPKSYSTEMUSERID'? Or should I add an extra field e.g. ‘GUID’ where I generate a guid and assign it to the record and use that to keep track of the record?

Is this only a firebird limitation? Meaning if I use another database like PostgreSQL would it return a ID for me?

Any help would be appreciated

[bangfauzan]

Hi nuthling,

I understand what u mean.
Your problem occures coz u disabled the two rows, that are:

//tblSystemUsers.CommitUpdates;
//tblSystemUsers.Refresh;

if u set CachedUpdates property on your tblSystemUsers to true,
to make sure your data stored in the database server,
you have to add this code:

tblSystemUsers.ApplyUpdates; (required)
tblSystemUsers.CommitUpdates; (not required)

but if CachedUpdates value is False, You can use Post method.

Second, coz the ID generated by the server, (not by your application) you
must refresh the tblSystemUsers, to obtain the server generated value.

Note:
Refreshing dataset makes cursor move from the original place.
To handle that, try this code from refreshing data.

var S : TBookMarkStr;
Begin
S := tblSystemUsers.BookMark;
tblSystemUsers.Refresh;
tblSystemUsers.BookMark := S;
End;




Thx.