Firebird UTF8 memo 'Dynamic SQL Error SQL error code = -303 Malformed string'

[Marius2]

Hello,

One of our testcases is complaining when updating a memo in Firebird-2.5.4.26856-0_x64 UTF8, Dialect 3. The memo is defined as 'BLOB SUB_TYPE 1 SEGMENT SIZE 80'. The connection is defined with the specificoptions as specified below. I'm overlooking something obvious, but what?

connection.specificoptions

Code: Select all

InterBase.CharSet=UTF8
'InterBase.UseUnicode=True
InterBase.Protocol=TCP
InterBase.SQLDialect=3

code for testcase

Code: Select all

var AStringStream: TStringStream;
  AQuery: TUniQuery;
begin
  AQuery := TUniQuery.Create(nil);
  AStringStream := TStringStream.Create;
  try
    //Some rubbisch string > 65k with unicode
    while AStringStream.Size < 256000 do begin
      AStringStream.WriteString('Documents can contain non ASCII characters, like Norwegian æ ø å , or French ê è é');
    end;

    AQuery.Connection := MyConnection;
    AQuery.Sql.Add('update image set memo=:memo where image=:image');
    AQuery.ParamByName('memo').AsMemo := AStringStream.DataString;
    AQuery.ParamByName('image').AsInteger := x; //a valid identifier
    AQuery.ExecSQL;[b][u] //<-- Crashes here with 'malformed string'[/u][/b]

    CheckEquals(....
  finally
    AStringStream.Free;
    AQuery.Free;
  end;
end;

Thanks,
Marius

[ViktorV]

To solve the problem, try to replace your code with the following:

Code: Select all

var AStringStream: TStringStream;
  AQuery: TUniQuery;
begin
  AQuery := TUniQuery.Create(nil);
  AStringStream := TStringStream.Create('', TEncoding.UTF8);
  try
    //Some rubbisch string > 65k with unicode
    while AStringStream.Size < 256000 do begin
      AStringStream.WriteString('Documents can contain non ASCII characters, like Norwegian æ ø å , or French ê è é');
    end;

    AQuery.Connection := MyConnection;
    AQuery.Sql.Add('update image set memo=:memo where image=:image');
    AQuery.ParamByName('memo').AsMemoRef.IsUnicode := True;
    AQuery.ParamByName('memo').AsMemo := AStringStream.DataString;
    AQuery.ParamByName('image').AsInteger := x; //a valid identifier
    AQuery.ExecSQL;[b][u] //<-- Crashes here with 'malformed string'[/u][/b]

    CheckEquals(....
  finally
    AStringStream.Free;
    AQuery.Free;
  end;
end;

[Marius2]

Thanks Victor,

Code: Select all

AQuery.ParamByName('memo').AsMemoRef.IsUnicode := True;

Question; why can't UniDac do that automaticly based on:
-the field-metadata from the database (not sure if that is available)
-the InterBase.CharSet=UTF8 in the specificoptions (good default for IsUnicode := True)
-the InterBase.UseUnicode=True in the specificoptions (good default for IsUnicode := True)
I think there are enough clues for UniDac to automate that a lot more, would that be possible (in the future)?

Thanks,
Marius

[ViktorV]

Question; why can't UniDac do that automaticly based on:
-the field-metadata from the database (not sure if that is available)
-the InterBase.CharSet=UTF8 in the specificoptions (good default for IsUnicode := True)
-the InterBase.UseUnicode=True in the specificoptions (good default for IsUnicode := True)
I think there are enough clues for UniDac to automate that a lot more, would that be possible (in the future)?

Thanks,
Marius

We will investigate this question. As soon as we get any results, we will let you know.