CODEWITHSUNDEEP
delphifirebirdc++builder
topmop75
topmop75

Reputation: 63

Firebird TIBQuery insert with returning ... INTO

I have a firebird 2.x database with Generator and a trigger to generate the key field. I need to get the returned value from below query.

INSERT INTO XXXX (vdate,description) values ('"+ VDate +"','"+ Description +"') returning vno INTO :ParamVoucherNo

I tried several versions of below code but it dont wrok and I get

Dynamic sql error sql error code = -104

Is it really possible to get the return value in delphi using TIBQuery ?

Query1->SQL->Clear();
Query1->SQL->Add("INSERT INTO XXXX (vodate,description) values ('"+ VDate +"','"+ Description +"') returning vno INTO :ParamVoucherNo");

Query1->Params->ParamByName("ParamVoucherno")->ParamType = ptResult;
Query1->Params->ParamByName("ParamVoucherno")->DataType = ftInteger;
Query1->Params->ParamByName("ParamVoucherno")->Value = "";
Query1->Prepare();
Query1->ExecSQL();

Any suggestions?

Upvotes: 3

Views: 14009

Answers (10)

Arioch 'The
Arioch 'The

Reputation: 16065

I wonder if that INSERT can be wrapped into EXECUTE BLOCK command. Would IBX manage EXECUTE BLOCK then?

Hope to try it in both IBX and Unified Interbase in XE2

PS: Even if it does not, I found the library, that tells to work on top of IBX of Delphi XE2 (both x86 and x64) and to add EXECUTE BLOCK support: http://www.loginovprojects.ru/index.php?page=ibxfbutils#eb.

Upvotes: 1

Mamrezo
Mamrezo

Reputation: 1509

From the IBx2 sources, you can do it like this:

//Uses IBSql;
//var   Cur: IResults;
  IBSQL1.SQL.Text := 'delete from tbl_document where id = 120 returning id;';
  IBSQL1.Prepare;
  if IBSQL1.Prepared then
  begin
    Cur := IBSQL1.Statement.Execute(IBTransaction1.TransactionIntf);
    WriteLn(Cur.Data[cou].AsString);
    Cur.GetTransaction.Commit(True);
  end;

IResults interface Code:

  IResults = interface
   function getCount: integer;
   function GetTransaction: ITransaction;
   function ByName(Idx: String): ISQLData;
   function getSQLData(index: integer): ISQLData;
   procedure GetData(index: integer; var IsNull:boolean; var len: short; var data: PChar);
   procedure SetRetainInterfaces(aValue: boolean);
   property Data[index: integer]: ISQLData read getSQLData; default;
   property Count: integer read getCount;
  end;

Test enviroment: Arch Linux X86 Firebird 3 Lazarus 1.9 FPC 3.0.4 Quick note: This works on new Firebird API in the IBX, But I didn't test it in Legacy Firebird API with the IBX.

Upvotes: 0

Bettelbursche
Bettelbursche

Reputation: 443

If you have a table with this 2 Fields: GRP_NO and GROUPNAME and you want to get the new GRP_NO you have to use RET_ as prefix, see example:

procedure TFormDatenbank.Button1Click(Sender: TObject);
var
  q: Uni.TUniQuery;
  ID: Integer;
  GroupName: String;
begin
  GroupName := 'MyGroupName';

  q := TUniQuery.Create(nil);
  try
    q.Connection := Datenmodul.UniConnection;
    q.ParamCheck := true; // the default value of ParamCheck is true.
    q.SQL.Clear;
    q.SQL.Add('SELECT GRP_NO, GROUPNAME FROM GROUPDATA WHERE GROUPNAME = :GROUPNAME');
    q.ParamByName('GROUPNAME').AsString := GroupName;
    q.Open;

    if q.RecordCount > 0 then
      ID := q.FieldByName('GRP_NO').AsInteger
    else
    begin
      // there exist no group with this name, so insert this new name
      q.SQL.Clear;
      q.SQL.Add('INSERT INTO GROUPDATA');
      q.SQL.Add('(GROUPNAME)');
      q.SQL.Add('VALUES');
      q.SQL.Add('(:GROUPNAME)');
      q.SQL.Add('RETURNING GRP_NO;');

      q.ParamByName('GROUPNAME').AsString := GroupName;
      q.Execute;
      ID := q.ParamByName('RET_GRP_NO').AsInteger;
    end;
  finally
    q.Free;
  end;
end;

Upvotes: 0

Eitan Arbel
Eitan Arbel

Reputation: 66

i know this question was answered a long time ago, but i must write this as clear as possible, for those who need this as i was.

i too, needed the "INSERT..RETURNING" thing. the Delphi drove me crazy for a long time, until i changed my Data access components. i even moved from Delphi XE2, to XE5 only because of that...

conclusion : IBX does NOT support RETURNING! FireDAC is PERFECT for what i need with Firebird.

just move to FireDAC and you'll be able to do everything you need, and with high performance.

Upvotes: 0

Andrej Kirejeŭ
Andrej Kirejeŭ

Reputation: 5481

As I know there should be some changes to IBX made. Internally INSERT ... RETURNING should be treated the same way as a selectable procedure with returning parameters.

Upvotes: 0

berndvf
berndvf

Reputation: 146

Using Delphi 6 I have the ID returning successfully using an EXECUTE BLOCK statement:

EXECUTE BLOCK
RETURNS ( DeptKey INT )
AS
BEGIN
  INSERT INTO DEPARTMENT 
      ( COMPANY_KEY, DEPARTMENT_NAME ) 
      VALUES ( 1, 'TEST1' ) RETURNING DEPARTMENT_KEY INTO :DeptKey;
  SUSPEND;
END;

From Delphi you can do the folliowing:

FQuery.SQL.Text := '<Execute Block Statement>';
FQuery.Open();
ANewKey := FQuery.Fields[0].AsInteger;

Upvotes: 2

Hugh Jones
Hugh Jones

Reputation: 2694

Why not get the next value for VoucherNo first, followed by

"INSERT INTO table1 (vno, vodate,description) VALUES (:VoucherNo,:VoDate,:Description)");

?

Your trigger can then either be dispensed with (which is nice), or modified to detect null (or <= zero can be useful too) and only then populate the vno field.

create trigger bi_mytable
  active before insert position 1
  on mytable
as
begin
  if (new.vno is null)
    then new.vno = next value for gen_VoucherNos;
end

Client-side you can :

select gen_id(gen_VoucherNos, 1) from rdb$database;

By modifying the trigger in this manner you save yourself a headache later on if/when you want to insert blocks of records

Upvotes: 0

Hugues Van Landeghem
Hugues Van Landeghem

Reputation: 6808

IBX is not Firebird ready

you can take a look at FIBPLUS who support Firebird features

FIBPlus also supports FB2.0 insert ... into ... returning. Now you should not bother about getting generator values from the client but leave them in the trigger. You can also use RDB$DB_KEY. New possible variants of work with insert returning and RDB$DB_KEY are shown in the example “FB2InsertReturning”.

Upvotes: 1

oodesigner
oodesigner

Reputation: 1007

From Firebird README.returning:

The INTO part (i.e. the variable list) is allowed in PSQL only (to assign local variables) and rejected in DSQL.

As IBX uses DSQL, you should exclude INTO part from your query.

INSERT ... RETURNING for DSQL looks the same as a call of a stored procedure, which returns result set. So, you have to use Open instead of ExecSQL.

Upvotes: 5

Johan
Johan

Reputation: 76724

Your mixing of dynamic SQL with parameters is just confusing.

Do this instead: 

Query1->SQL->Clear();
Query1->SQL->Add("INSERT INTO table1 (vodate,description) VALUES"+
                 "(:VoDate,:Description) RETURNING vno INTO :VoucherNo ");
Query1->Params->ParamByName("VoDate")->Value = VDate;
Query1->Params->ParamByName("description")->Value = Description;

Query1->Prepare();
Query1->ExecSQL();
VoucherNo = Query1->Params->ParamByName("VoucherNo")->AsInteger;

Upvotes: 3

Related Questions