Insert with Returning don't support string Field

[wiglan]

Hi.

I'm running tests with devArt and found a bug, currently the fields of type string are not being treated when they are used in the "return" of an insert instruction for example.

Follow the complete code to simulate or problem.

ddl for database:

Code: Select all

CREATE TABLE TEST_TABLE (
    ID_INT     INTEGER NOT NULL,
    ID_STRING  VARCHAR(100) NOT NULL,
    PRICE      DOUBLE PRECISION
);

ALTER TABLE TEST_TABLE ADD CONSTRAINT PK_TEST_TABLE PRIMARY KEY (ID_INT, ID_STRING);

Delphi code:

Code: Select all

procedure TForm6.FormCreate(Sender: TObject);
var
  vsql : TSqldataset;
  intValue: Integer;
  StringValue: string;
begin

   try
      vsql := TSQLDataSet.Create(nil);
      vsql.SQLConnection := SQLConnection1;
      vsql.CommandText := 'insert into TEST_TABLE (ID_INT, '+
                          'ID_STRING, '+
                          'PRICE) '+
                          'values ( '+
                                  ':IN_ID_INT, '+
                                  ':IN_ID_STRING, '+
                                  ':IN_PRICE) '+
                          'returning ID_INT, '+
                                    'ID_STRING   ';


      vsql.params.ParamByName('IN_ID_INT').asinteger := 1;
      vsql.params.ParamByName('IN_ID_STRING').AsString := 'a';
      vsql.params.ParamByName('IN_PRICE').AsCurrency := 1.55;

      with TParam(vSQL.Params.Add) do begin
        Name := 'ID_INT';
        DataType := ftInteger;
        ParamType := ptOutput;
      end;

      with TParam(vSQL.Params.Add) do begin
        Name := 'ID_STRING';
        DataType := ftstring;
        ParamType := ptOutput;
      end;

      vSQL.ExecSQL;

      intValue := vSQL.ParamByName('ID_INT').AsInteger;
      StringValue := vSQL.ParamByName('ID_STRING').AsString;

   finally
      FreeAndNil(vsql);
   end;

end;

problem:

Code: Select all

    StringValue := vSQL.ParamByName('ID_STRING').AsString;

StringValue always is empty;

basically, whenever a parameter is defined of type string, its content is empty.
I believe that this type of data was not treated in the return of the instructions executed in the bank.

How can we fix this?
thanks!

[oleg0k]

Hello,
To get the output value of string parameters, you need to set their size. For example, in your example, for the ID_STRING parameter: Size:=100 :

Code: Select all

  with TParam(vSQL.Params.Add) do begin
        Name := 'ID_STRING';
        DataType := ftString;
        ParamType := ptOutput;
        Size := 100;
  end;

If this doesn't help and your IDE still returns an empty value, it's most likely caused by the bug ( https://quality.embarcadero.com/browse/RSP-13821 ), which was fixed in RAD Studio 10.4.

wbr, Oleg
Devart Team

[wiglan]

Well, just setting the field size didn't work. At least, not with the Xe8 version I'm using.

But, checking the post I was able to solve the problem by implementing the code below:

Code: Select all

function TFSQLDataSet.ExecSQL(ExecDirect: Boolean): Integer;
var
  I: Integer;
begin
  for I := 0 to Params.Count - 1 do
    if (Params[I].ParamType = ptOutput) and (Params[I].DataType in [ftString, ftWideString]) then
      Params[I].Value := StringOfChar(' ', Params[I].Size);
  Result := inherited;
end;

It is strange to be an IDE error, so it worked correctly. Thank you!

[oleg0k]

Hello,
Glad to see that the issue was resolved.
Feel free to contact us if you have any further questions about our products.

wbr, Oleg
Devart Team