Firebird text blob fields update

[Nick N.]

Hi. I am a new Lazarus user, now trying migrate my hard project from Delphi

I have a problem while trying update text blob field by query with params. Field value in database stores incorrected.
I use ODBC connection, charset set as UTF8, charset in databse UTF8

Working code:

  test.Clear;
  test.sql.Text := 'update settings set text = ''1234'' where id = 1';
  test.ExecSQL;
  SQLTransaction.CommitRetaining;

Field text value is '1234';

if I try with parameter:

  test.Clear;
  test.sql.Text := 'update settings set text = :text where id = 1';
  test.ParamByName('text').AsString := '1234';
  test.ExecSQL;
  SQLTransaction.CommitRetaining;

Field value is incorrect, additional characters are added. In each case different character
1234 stores as 1234, 123456 stores as 123456^ etc

Whats wrong ?

Thanks

[balazsszekely]

Hi Nick,

Please try this:

Code: Pascal  [Select][+][-]

1. var
2.   Str: String;
3.   MS: TMemoryStream;
4. begin
5.   Str := '123áőüäß4';
6.  
7.   qTest.Clear;
8.   qTest.sql.Text := 'update settings set text = :text where id = 1';
9.   MS := TMemoryStream.Create;
10.   try
11.     MS.WriteBuffer(Pointer(Str)^, Length(Str));
12.     MS.Position := 0;
13.     qTest.ParamByName('TEXT').LoadFromStream(MS, ftBlob)
14.   finally
15.     MS.Free;
16.   end;
17.   qTest.ExecSQL;
18.   SQLTransaction.CommitRetaining;
19. end;

[egsuh]

How do you check the way data is stored in database table?  I have used Firebird for almost 10 years, but had no such problem programmatically. But sometimes FlameRobin, a desktop GUI tool for Firebird, displayed wrong content (like your case).

[Jurassic Pork]

hello,
have you defined the right subtype for the blob ?

0 - binary data (image, video, audio, whatever)
1 - text (basic character functions work)
2 - BLR (used for definitions of Firebird procedures, triggers, etc.)

Example for my firebird3 database deadlyMushrooms where NOTES is a text blob and PICTURE is an image blob (see attachments).
Lazarus project database components : TIBConnection, TSQLTransaction, TSQLQuery.

Friendly, J.P

[Nick N.]

Hi Nick,

Please try this:

Code: Pascal  [Select][+][-]

1. var
2.   Str: String;
3.   MS: TMemoryStream;
4. begin
5.   Str := '123áőüäß4';
6.  
7.   qTest.Clear;
8.   qTest.sql.Text := 'update settings set text = :text where id = 1';
9.   MS := TMemoryStream.Create;
10.   try
11.     MS.WriteBuffer(Pointer(Str)^, Length(Str));
12.     MS.Position := 0;
13.     qTest.ParamByName('TEXT').LoadFromStream(MS, ftBlob)
14.   finally
15.     MS.Free;
16.   end;
17.   qTest.ExecSQL;
18.   SQLTransaction.CommitRetaining;
19. end;

Thanks, this code work correctly. If I trying

Code: Pascal  [Select][+][-]

1.     Test.ParamByName('TEXT').AsString := '123áőüäß4';
2.  

Its not working. Is a bug in last version? I have Lazarus 2.0.12 r64642 FPC 3.2.0 i386-win32-win32/win64. May be try downgrade ?

How do you check the way data is stored in database table?  I have used Firebird for almost 10 years, but had no such problem programmatically. But sometimes FlameRobin, a desktop GUI tool for Firebird, displayed wrong content (like your case).

I have a simple program now. Grid, SQLQuery and TDBMemo. In DBMemo i see a result, and additionaly checked in IBExpert
After editing text in dbmemo value saved in database incorrect (please see screenshot)
Text field is "BLOB SUB_TYPE 1 SEGMENT SIZE 8192 CHARACTER SET UTF8"

[Nick N.]

I look fpc sources, dsparams.inc:

Procedure TParam.GetData(Buffer: Pointer);

Var
  P  : Pointer;
  S  : String;
  ws : WideString;
  l  : Integer;
begin
  case FDataType of
    ftUnknown  : DatabaseErrorFmt(SUnknownParamFieldType,[Name],DataSet);
    ftSmallint : PSmallint(Buffer)^:=AsSmallInt;
    ftWord     : PWord(Buffer)^:=AsWord;
    ftInteger,
    ftAutoInc  : PInteger(Buffer)^:=AsInteger;
    ftCurrency : PDouble(Buffer)^:=AsCurrency;
    ftFloat    : PDouble(Buffer)^:=AsFloat;
    ftBoolean  : PWordBool(Buffer)^:=AsBoolean;
    ftString,
    ftMemo,
    ftAdt,
    ftFixedChar:
      begin
      S:=AsString;
      StrMove(PChar(Buffer),PChar(S),Length(S)+1);
      end;
    ftWideString,
    ftWideMemo: begin
      ws := GetAsWideString;
      l := Length(ws);
      if l > 0 then
        Move(ws[1], Buffer, Succ(l)*2)
      else
        PWideChar(Buffer)^ := #0
    end;
    ftTime     : PInteger(Buffer)^:=DateTimeToTimeStamp(AsTime).Time;
    ftDate     : PInteger(Buffer)^:=DateTimeToTimeStamp(AsTime).Date;
    ftDateTime : PDouble(Buffer)^:=TimeStampToMSecs(DateTimeToTimeStamp(AsDateTime));
    ftBlob,
    ftGraphic..ftTypedBinary,
    ftOraBlob,
    ftOraClob  :
      begin
      S:=GetAsString;
      Move(PChar(S)^, Buffer^, Length(S));
      end;
    ftBytes, ftVarBytes:
      begin
      if VarIsArray(FValue) then
        begin
        P:=VarArrayLock(FValue);
        try
          Move(P^, Buffer^, VarArrayHighBound(FValue, 1) + 1);
        finally
          VarArrayUnlock(FValue);
        end;
        end;
      end;
    ftFmtBCD   : PBCD(Buffer)^:=AsFMTBCD;
  else
    If not (DataType in [ftCursor, ftArray, ftDataset,ftReference]) then
      DatabaseErrorFmt(SBadParamFieldType, [Name], DataSet);
  end;
end;

maybe need it like this:

StrMove(PChar(Buffer),PChar(S),Length(S)+0);

?

[Nick N.]

My workaround:

test.ParamByName('text').AsString := '1234' + #0;

[balazsszekely]

My workaround:
test.ParamByName('text').AsString := '1234' + #0;

For binary blobs there is no workaround, you must use the method mentioned above. For text blob, "#0" might work but is still the wrong approach in my opinion.

[Jurassic Pork]

hello,

Hi. I am a new Lazarus user, now trying migrate my hard project from Delphi

I have a problem while trying update text blob field by query with params. Field value in database stores incorrected.
I use ODBC connection, charset set as UTF8, charset in databse UTF8

Maybe you have some troubles with the ODBC Driver. In my Lazarus  Firebird3 Database Example , I use a TIBConnection to communicate with my Firebird3 Database.
It seems that all is good while updating a Blob Text with SQLQuery with parameters.
With this code :

Code: Pascal  [Select][+][-]

1. procedure TForm1.Button2Click(Sender: TObject);
2. var Query : TSQLQuery;
3. begin
4.   Query := TSQLQuery.Create(nil);
5.   Query.DataBase := IBConnection1;
6.   Query.Sql.Text := 'update DeadlyMushrooms set NOTES = :text where ID = 6';
7.   Query.ParamByName('text').AsString := '123áőüäß4 héhé ça fonctionne !';
8.   Query.ExecSQL;
9.   SQLTransaction1.CommitRetaining;
10.   Query.Close;
11.   Query.Free;
12. end;

The Blob Text Field NOTES seems to be OK (see attachments).

Friendly, J.P

[egsuh]

I use a TIBConnection to communicate with my Firebird3 Database.
It seems that all is good while updating a Blob Text with SQLQuery with parameters.

I agree with this. I've used TIBConnection in many applications and had no problem in treating BLOB fields, even with multi-line contents. That is,

           Query1.Params[1].AsString:= MyStringList.Text;
           MyStringList.Text:= Query1.Fields[1].AsString;

etc.

[balazsszekely]

@egsuh

I agree with this. I've used TIBConnection in many applications and had no problem in treating BLOB fields, even with multi-line contents. That is,

Because you are using plain text, which is just a subset of what BLOB(Binary large object) can do.

[egsuh]

Because you are using plain text, which is just a subset of what BLOB(Binary large object) can do.

No. Multibyte characters would look like "123áőüäß4 héhé ça fonctionne !" if displayed in ASCII characters.

[balazsszekely]

OK. I don't want to force my opinion on others.
I detach myself from this thread.

[Nick N.]

My workaround:
test.ParamByName('text').AsString := '1234' + #0;

For binary blobs there is no workaround, you must use the method mentioned above. For text blob, "#0" might work but is still the wrong approach in my opinion.

I have TODBCConnection and TSQLQuery, TDBMemo for edit text blob field. After editing data stores in blob incorrected. I cannot use IBX in this project. My workaround is simple:

Code: Pascal  [Select][+][-]

1. procedure TControlF.settingsBeforePost(DataSet: TDataSet);
2. begin
3.   settingsTEXT.AsString := settingsTEXT.AsString + #0;
4. end;      
5.  

Thanks all for the answers!