Firebird text blob fields update
lazarus / 16-Databases

----------------------------------------------------------------------
Nick N. · 2021-03-24 20:22

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

----------------------------------------------------------------------
balazsszekely · 2021-03-24 21:21

Hi Nick,

Please try this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var  Str:
String;  MS: TMemoryStream;begin  Str := '123áőüäß4';   qTest.Clear; 
qTest.sql.Text := 'update settings set text = :text where id = 1';  MS
:= TMemoryStream.Create;  try    MS.WriteBuffer(Pointer(Str)^,
Length(Str));    MS.Position := 0;   
qTest.ParamByName('TEXT').LoadFromStream(MS, ftBlob)  finally   
MS.Free;  end;  qTest.ExecSQL;  SQLTransaction.CommitRetaining; end;

----------------------------------------------------------------------
egsuh · 2021-03-25 02:10

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 · 2021-03-25 05:56

hello,
have you defined the right subtype for the blob ?
Quote0 - 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. · 2021-03-25 19:27

Quote from: GetMem on March 24, 2021, 10:21:51 pmHi Nick,

Please try this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var  Str:
String;  MS: TMemoryStream;begin  Str := '123áőüäß4';   qTest.Clear; 
qTest.sql.Text := 'update settings set text = :text where id = 1';  MS
:= TMemoryStream.Create;  try    MS.WriteBuffer(Pointer(Str)^,
Length(Str));    MS.Position := 0;   
qTest.ParamByName('TEXT').LoadFromStream(MS, ftBlob)  finally   
MS.Free;  end;  qTest.ExecSQL;  SQLTransaction.CommitRetaining; end;
Thanks, this code work correctly. If I trying

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    
Test.ParamByName('TEXT').AsString := '123áőüäß4'; 
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 ?

Quote from: egsuh on March 25, 2021, 03:10:34 amHow 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. · 2021-03-25 20:06

I look fpc sources, dsparams.inc:

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

QuoteStrMove(PChar(Buffer),PChar(S),Length(S)+0);
?

----------------------------------------------------------------------
Nick N. · 2021-04-02 18:34

My workaround:

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

----------------------------------------------------------------------
balazsszekely · 2021-04-02 21:11

QuoteMy 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 · 2021-04-02 23:36

hello,
Quote from: Nick N. on March 24, 2021, 09:22:12 pmHi. 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button2Click(Sender: TObject);var Query : TSQLQuery;begin 
Query := TSQLQuery.Create(nil);  Query.DataBase := IBConnection1; 
Query.Sql.Text := 'update DeadlyMushrooms set NOTES = :text where ID =
6';  Query.ParamByName('text').AsString := '123áőüäß4 héhé ça
fonctionne !';  Query.ExecSQL;  SQLTransaction1.CommitRetaining; 
Query.Close;  Query.Free;end;
The Blob Text Field NOTES seems to be OK (see attachments).

Friendly, J.P

----------------------------------------------------------------------
egsuh · 2021-04-03 04:11

QuoteI 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 · 2021-04-03 06:31

@egsuh
QuoteI 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 · 2021-04-03 10:54

QuoteBecause 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 · 2021-04-03 11:47

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

----------------------------------------------------------------------
Nick N. · 2021-04-04 09:17

Quote from: GetMem on April 02, 2021, 11:11:46 pmQuoteMy 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TControlF.settingsBeforePost(DataSet: TDataSet);begin 
settingsTEXT.AsString := settingsTEXT.AsString + #0;end;     
Thanks all for the answers!