Recent

Author Topic: Firebird text blob fields update  (Read 1495 times)

Nick N.

  • New member
  • *
  • Posts: 5
Firebird text blob fields update
« on: March 24, 2021, 09:22:12 pm »
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:

Quote
  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:

Quote
  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

GetMem

  • Hero Member
  • *****
  • Posts: 4017
Re: Firebird text blob fields update
« Reply #1 on: March 24, 2021, 10:21:51 pm »
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

  • Hero Member
  • *****
  • Posts: 721
Re: Firebird text blob fields update
« Reply #2 on: March 25, 2021, 03:10:34 am »
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

  • Hero Member
  • *****
  • Posts: 963
Re: Firebird text blob fields update
« Reply #3 on: March 25, 2021, 06:56:44 am »
hello,
have you defined the right subtype for the blob ?
Quote
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
« Last Edit: March 25, 2021, 09:01:24 am by Jurassic Pork »
Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

Nick N.

  • New member
  • *
  • Posts: 5
Re: Firebird text blob fields update
« Reply #4 on: March 25, 2021, 08:27:50 pm »
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.

  • New member
  • *
  • Posts: 5
Re: Firebird text blob fields update
« Reply #5 on: March 25, 2021, 09:06:43 pm »
I look fpc sources, dsparams.inc:

Quote
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:

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

?

Nick N.

  • New member
  • *
  • Posts: 5
Re: Firebird text blob fields update
« Reply #6 on: April 02, 2021, 08:34:19 pm »
My workaround:

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

GetMem

  • Hero Member
  • *****
  • Posts: 4017
Re: Firebird text blob fields update
« Reply #7 on: April 02, 2021, 11:11:46 pm »
Quote
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

  • Hero Member
  • *****
  • Posts: 963
Re: Firebird text blob fields update
« Reply #8 on: April 03, 2021, 01:36:40 am »
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


« Last Edit: April 03, 2021, 01:50:39 am by Jurassic Pork »
Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

egsuh

  • Hero Member
  • *****
  • Posts: 721
Re: Firebird text blob fields update
« Reply #9 on: April 03, 2021, 06:11:46 am »
Quote
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.

GetMem

  • Hero Member
  • *****
  • Posts: 4017
Re: Firebird text blob fields update
« Reply #10 on: April 03, 2021, 08:31:44 am »
@egsuh
Quote
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

  • Hero Member
  • *****
  • Posts: 721
Re: Firebird text blob fields update
« Reply #11 on: April 03, 2021, 12:54:15 pm »
Quote
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.

GetMem

  • Hero Member
  • *****
  • Posts: 4017
Re: Firebird text blob fields update
« Reply #12 on: April 03, 2021, 01:47:04 pm »
OK. I don't want to force my opinion on others.
I detach myself from this thread.
« Last Edit: April 03, 2021, 01:50:05 pm by GetMem »

Nick N.

  • New member
  • *
  • Posts: 5
Re: Firebird text blob fields update
« Reply #13 on: April 04, 2021, 11:17:25 am »
Quote
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!

 

TinyPortal © 2005-2018