Roy,

<< I'm still impressed. >>

Thank you.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

<<<<<
The best (and easiest) way is to do as Roy suggests, and define your primary
key column as an IDENTITY column.  You can return IDENTITY columns as output
parameters from INSERT statements, so you can always easily retrieve the
value generated during the INSERT:

http://www.elevatesoft.com/newsgrp?action=openmsg&group=16&msg=8697&page=1#msg8697
>>>>

How do I get the value of the new ID field back from the script?

I would like to use something like this...

function AddRecdGetID(const SSQL: string): integer;
const
SCRADDID = 'SCRIPT (OUT LastIdentity INTEGER)'+
           ' BEGIN'+
           ' DECLARE TempStmt STATEMENT;'+
           ' PREPARE TempStmt FROM ''INSERT INTO "Address" (LocationID, ClientID) VALUES (?, ?)'' ;'+
           ' EXECUTE TempStmt USING LastIdentity, 444;'+
           ' END';
var aScript: TEDBScript;
begin
Result  := -1;
aScript := TEDBScript.Create(nil);
try
 aScript.SessionName  := DM.EDBDatabase1.SessionName;
 aScript.DatabaseName := DM.EDBDatabase1.DatabaseName;
 aScript.SQL.Add(SCRADDID);
 aScript.Prepare;
 try
  aScript.ExecScript;
 except
  raise;
 end;
 Result  := aScript.FieldByName('AddressID').AsInteger;<<<<<<<<<<<<<<<<<
finally
 aScript.Free;
end;
end;

...but of course there is no field by that name. It IS in the table, which has AddressID, LocationID and ClientID integer fields.

The SQL works, and the AddressID field is incremented, but I would like it to return the value of the new AddressID as the resultof the AddRecdGetID function.

Thanks for your help

Regards

Peter

Peter


Check this out

http://www.elevatesoft.com/newsgrp?action=openmsg&group=17&msg=2164&page=1#msg2164


Roy Lambert [Team Elevate]

Peter,

<< How do I get the value of the new ID field back from the script? >>

Use this:

function AddRecdGetID(const SSQL: string): integer;
const
SCRADDID = 'SCRIPT (OUT LastIdentity INTEGER)'+
          ' BEGIN'+
          ' DECLARE TempStmt STATEMENT;'+
          ' PREPARE TempStmt FROM ''INSERT INTO "Address" (LocationID,
ClientID) VALUES (?, ?)'' ;'+
          ' EXECUTE TempStmt USING LastIdentity, 444;'+
          ' END';
var aScript: TEDBScript;
begin
Result  := -1;
aScript := TEDBScript.Create(nil);
try
aScript.SessionName  := DM.EDBDatabase1.SessionName;
aScript.DatabaseName := DM.EDBDatabase1.DatabaseName;
aScript.SQL.Add(SCRADDID);
aScript.Prepare;
try
 aScript.ExecScript;
 Result  := aScript.ParamByName('LastIdentity').AsInteger;<<<<<<<<<<<<<<<<<
except
 raise;
end;
finally
aScript.Free;
end;
end;

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

The Parameter LastIdentity is always NULL, after the aScript.ExecScript. The row is added to the table just fine, but the resulting new ID is elusive.

Should the LastIdentity Param be formally created?

I am using 2.03 in D7.

Regards

Peter

Peter,

<< The Parameter LastIdentity is always NULL, after the aScript.ExecScript.
The row is added to the table just fine, but the resulting new ID is
elusive.

Should the LastIdentity Param be formally created? >>

No, it will be created automatically.

I just noticed that you're trying to return the AddressID as the IDENTITY
column, but you're specifying the OUT parameter in the script for the
LocationID column.  Is that incorrect ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

<<<<
I just noticed that you're trying to return the AddressID as the IDENTITY
column, but you're specifying the OUT parameter in the script for the
LocationID column.  Is that incorrect ?>>>>>>

No, My intention is the have the new AddressID returned.

Regards

Peter

Peter,

<< No, My intention is the have the new AddressID returned. >>

I understand, but you're referring to the LocationID column in the INSERT
statement, hence your problem.  What you need is this instead:

SCRIPT (OUT LastIdentity INTEGER)
BEGIN
  DECLARE TempStmt STATEMENT;
  PREPARE TempStmt FROM 'INSERT INTO "Address" (AddressID,ClientID) VALUES
(?, ?)';
  EXECUTE TempStmt USING LastIdentity, 444;
END

Notice the use of AddressID instead of LocationID in the INSERT.

--
Tim Young
Elevate Software
www.elevatesoft.com