TIBCQuery and RETURNING-Statemant

[-ChrisE-]

Hello,

sorry, but I can't find anything in the Help-File or the demos for this "easy" problem.

I use an TIBCQuery to insert data into an Table

Code: Select all

MyTable
ID: BigInt;
Name: VarChar(100);

The table has an generator for the ID.

The InsertCode is:

Code: Select all

FIBCQuery.SQL.Clear;
FIBCQuery.SQL.Add('INSERT INTO mytable (NAME)');
FIBCQuery.SQL.Add('VALUES (:NAME)');
FIBCQuery.SQL.Add('RETURNING ID');

FIBCQuery.ParamByName('NAME').AsString := AName;

FIBCQuery.Prepare;
FIBCQuery.Execute;
if FIBCQuery.RecordCount > 0 then
begin
  result := FIBCQuery.ParamByName('ID').Value;
end else
begin
  result := -1;
end;

How can I get the return value?

I have tried several things:

Code: Select all

FIBCQuery.Options.ReturnParams := TRUE;
//---
FIBCQuery.DMLRefresh := TRUE;

What is wrong?

I use Delphi 2007 with IBDAC 3.50.19

Chris

[calou]

Hello,

Look here:

http://www.devart.com/forums/viewtopic. ... ight=retid

Regards

[-ChrisE-]

Hello calou,

thx for help

For all the other who need a solution here is the full code

Code: Select all

FIBCQuery.SQL.Clear; 
FIBCQuery.SQL.Add('INSERT INTO mytable (NAME)'); 
FIBCQuery.SQL.Add('VALUES (:NAME)'); 
FIBCQuery.SQL.Add('RETURNING ID');

with TParam(FIBCQuery.Params.Add) do
begin
  ParamType := ptResult;
  DataType := ftLargeint;
  Name := 'RET_ID';
end;

FIBCQuery.ParamByName('NAME').AsString := AName; 

FIBCQuery.Prepare; 
FIBCQuery.Execute; 
result := FIBCQuery.ParamByName('RET_ID').Value; // Need Value because ID is BigInt/LargInt/Int64

Chris

[AndreyZ]

Hello,

You shouldn't create the RET_ID parameter manually, IBDAC does this automatically. You should use the following code:

Code: Select all

  FIBCQuery.SQL.Clear;
  FIBCQuery.SQL.Add('INSERT INTO mytable (ID, NAME)');
  FIBCQuery.SQL.Add('VALUES (NEXT VALUE FOR GEN_MYTABLE_ID, :NAME)');
  FIBCQuery.SQL.Add('RETURNING ID');
  FIBCQuery.Prepare;
  FIBCQuery.ParamByName('NAME').AsString := 'test';
  FIBCQuery.Execute;
  result := FIBCQuery.ParamByName('RET_ID').AsLargeInt;

[-ChrisE-]

Hello AndreyZ,

thx for reply. Work's perfect

Because I have an Before-Insert-DB-Trigger for the generator it's a little simpler:

Code: Select all

FIBCQuery.SQL.Clear; 
FIBCQuery.SQL.Add('INSERT INTO mytable (ID, NAME)'); 
FIBCQuery.SQL.Add('VALUES (:NAME)'); 
FIBCQuery.SQL.Add('RETURNING ID'); 
FIBCQuery.Prepare; 
FIBCQuery.ParamByName('NAME').AsString := 'test'; 
FIBCQuery.Execute; 
result := FIBCQuery.ParamByName('RET_ID').AsLargeInt;

THX for support

Chris

[AndreyZ]

Feel free to contact us if you have any further questions about IBDAC.