Insert or update returning problem with unidac 5.5.12 and Firebird 2.5.4

[andresantos78]

Hi All,
Good day

I have a problem with insert or update returning routine using firebird 2.5.4, Delphi XE2 Professional Update 4 and UniDac version 5.5.12.

Code: Select all

var
  qry : TUniQuery;
  param_key_colunm : Integer;
begin
  param_key_colunm := 0;
  qry := TUniQuery.create(nil);
  qry.Connection := getConnectionFactory();
  qry.SQL.Add('update or insert into mytable'); 
  qry.SQL.Add('(key_colunm, field_1, field_2, ......)');
  qry.SQL.Add('values');
  qry.SQL.Add('(:key_code, :field_1, : field_2, .....)');
  qry.SQL.Add('matching'); 
  qry.SQL.Add('key_colunm'); 
  qry.SQL.Add('returning'); 
  qry.SQL.Add(':key_colunm'); 

  qry.ParamByName('key_code').DataType    := ftInteger;
  qry.ParamByName('key_code').ParamType := ptInput;
  if getKeyCode > 0 then
     qry.ParamByName('key_code').AsInteger  := getKeyCode
  else
     qry.ParamByName('key_code').value := Null;

  qry.ParamByName('field_1').DataType    := ftString;
  qry.ParamByName('field_1').ParamType := ptInput;
  qry.ParamByName('field_1').AsString      := getField1;

  qry.ParamByName('field_2').DataType    := ftString;
  qry.ParamByName('field_2').ParamType := ptInput;
  qry.ParamByName('field_2').AsString      := getField2;

  qry.ParamByName('key_colunm').DataType    := ftInteger;
  qry.ParamByName('key_colunm').ParamType := ptOutput;

  try
    qry.ExecSQL();
  except
    on E:Exception do 
    begin
        raise Exception.Create(E.Message);
    end;
  end;
  param_key_colunm := qry.paramByName('key_colunm').AsInteger;
end;

Exists one trigger on the event before insert in Mytable that fill primary key (key_column) when the data is not filled by my application;

When I'm trying configure parameter key_colunm, I'm get one exception "Parameter 'key_colunm' not found"

Consulting in the devart own forum and on the web, I noticed that some people are having the same or similar problem, see a few links below.

URL: http://forums.devart.com/viewtopic.php?f=26&t=28733
URL: http://forums.devart.com/viewtopic.php?f=28&t=24247

I've tried many ways and I can not get the expected result.

Is there any solution to this problem?

Am I doing something wrong?

If so, what would be the correct way to use the feature?

I await a return.

Best Regards.

André Geraldo dos Santos

[ViktorV]

For the fields, that are specified after RETURNING, there are automatically generated output parameters with the RET_ prefix. This is described in the documentation: http://www.devart.com/ibdac/docs/index. ... ataset.htm
To solve the problem:
1) Replace the following lines in your sample:

Code: Select all

  qry.SQL.Add('matching'); 
  qry.SQL.Add('key_colunm'); 
  qry.SQL.Add('returning'); 
  qry.SQL.Add(':key_colunm'); 

with the following:

Code: Select all

  qry.SQL.Add('matching'); 
  qry.SQL.Add('(key_colunm')); 
  qry.SQL.Add('returning'); 
  qry.SQL.Add('key_colunm'); 

2) don't create an out-parameter "key_colunm";
3) use

Code: Select all

param_key_colunm := qry.paramByName('RET_key_colunm').AsInteger;

instead of

Code: Select all

param_key_colunm := qry.paramByName('key_colunm').AsInteger;