Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Problem with Firebird BLOBs and Unicode (is it a bug in dbExpress driver)



Permlink Replies: 12 - Last Post: May 30, 2014 10:15 AM Last Post By: Jack Lee
Andrzej Brzezin...

Posts: 6
Registered: 12/8/02
Problem with Firebird BLOBs and Unicode (is it a bug in dbExpress driver)
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 21, 2012 9:10 AM
I am using Delphi XE2 with dbExpress and Firebird 2.5.
I have created UTF8 databases with some text BLOBs.
I have specified the following options in the connection ini file:
ServerCharSet=UTF8
UseUnicode=True
EnableMemos=True

For some reason, VarChar fields work fine and BLOBs return garbage
even though they are correctly created as TWideMemoFields.
Any ideas?

I have reported this problem with Delphi 2010 in the summer of 2010.
Is it still not fixed? I hope I am just missing some option.

Thank you,
Andre

Edited by: Andrzej Brzezinski on Feb 23, 2012 9:05 AM
Andrzej Brzezin...

Posts: 6
Registered: 12/8/02
Re: Problem with Firebird BLOBs and Unicode
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 21, 2012 10:18 AM   in response to: Andrzej Brzezin... in response to: Andrzej Brzezin...
The funny thing is the BLOBs seem to be updated correctly.
In other words writing to BLOBs works and reading from them does not.

Andre

Andrzej Brzezinski wrote:
I am using Delphi XE2 with dbExpress and Firebird 2.5.
I have created UTF8 databases with some text BLOBs.
I have specified the following options in the connection ini file:
ServerCharSet=UTF8
UseUnicode=True
EnableMemos=True

For some reason, VarChar fields work fine and BLOBs return garbage
even though they are correctly created as TWideMemoFields.
Any ideas?

Thank you,
Andre
Victor Saveanu

Posts: 25
Registered: 1/7/10
Re: Problem with Firebird BLOBs and Unicode
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 24, 2012 6:50 AM   in response to: Andrzej Brzezin... in response to: Andrzej Brzezin...
Hi Andre,

Can you gives us a bit more details, I'm mostly interested in: the exact DDL of your table and the code you are using to read/write the BLOB field?

Regards,
~Victor
Andrzej Brzezin...

Posts: 6
Registered: 12/8/02
Re: Problem with Firebird BLOBs and Unicode (is it a bug in dbExpress driver)
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 24, 2012 10:39 AM   in response to: Andrzej Brzezin... in response to: Andrzej Brzezin...
Hi Victor,

Here is my setup:
I am using a TClientDataSet component connected to a TDataSetProvider connected to a TSQLDataSet connected to a TSQLConnection.
I am using the following code to execute a SELECT query:
ClientDataSet1.Close;
SQLDataSet1.CommandText := 'SELECT * FROM mytable';
SQLConnection1.Connected := True;
ClientDataSet1.Open;

Now, I can access a memo field from the TClientDataSet using:
s := ClientDataSet1.FieldByName('MyMemo').AsString; {it doesn't matter if I use AsTWideString}
The type of this field is correct - TWideMemoField;
If I change it and use ClientDataSet1.ApplyUpdates(0) it seems to update the database
correctly.

I have added the following Params to TSQLConnection :
ServerCharSet=UTF8
UseUnicode=True
EnableMemos=True

If you need any more information, please let me know.

Thank you,
Andre
Andrzej Brzezin...

Posts: 6
Registered: 12/8/02
Re: Problem with Firebird BLOBs and Unicode (is it a bug in dbExpress driver)
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 4, 2012 6:32 PM   in response to: Andrzej Brzezin... in response to: Andrzej Brzezin...
It looks like either nobody uses Unicode with Firebird, or it simply
is unusable. I am really disappointed. As far as I am concerned
if I cannot use Unicode with Firebird memo fields the entire
Unicode support in Delphi XE2 is useless for me at this point.

Andre
Victor Saveanu

Posts: 25
Registered: 1/7/10
Re: Problem with Firebird BLOBs and Unicode (is it a bug in dbExpress driver)
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 5, 2012 6:47 AM   in response to: Andrzej Brzezin... in response to: Andrzej Brzezin...
Andre,

A BLOB is just a binary storage, you can take full control on how values are stored in it. For example, if I want to use the "Unicode" encoding:

Table DDL:
CREATE TABLE "ATABLE" ("ID" INTEGER, "ABLOB" BLOB SUB_TYPE TEXT);


Write value:
var
  buffer: TArray<Byte>;
...
  buffer := TEncoding.Unicode.GetBytes('Привет мир!');  // Hello World!
  query.ParamByName('ABLOB').AsBlob := buffer;


Read value:
var
  buffer: TArray<Byte>;
...
  bytes := ATABLE.FieldByName('ABLOB').AsBytes;
  Memo1.Lines.Add(TEncoding.Unicode.GetString(bytes, 0, Length(bytes)));


or

  Memo1.Lines.Add(ATABLE.FieldByName('ABLOB').AsString);

For the system to work correctly you need to write and read the string using the same encoding and that the storage doesn't alter it.Please show me exactly how you are writing your data to the database.

Regards,
~Victor

Andrzej Brzezin...

Posts: 6
Registered: 12/8/02
Re: Problem with Firebird BLOBs and Unicode (is it a bug in dbExpress driver)
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 6, 2012 2:32 PM   in response to: Andrzej Brzezin... in response to: Andrzej Brzezin...
Victor,

Thanks for answering. If you look at this thread I have described how I was
writing information to a Firebird database. The fact is that as long as I write
to a memo field of a ClientDataSet the value seems correct (I can write
to a MEMO field and read from it and it is the same text).
When I use ClientDataSet.ApplyUpdates(0) the memo field information
seems to be written correctly to the database because when I use a Firebird table viewer
I can see it (for example, non-english characters are written correctly).
But when I read from it again into a ClientDataSet the memo field is showing garbage.

Thank you,
Andre
mohamed-yacine ...

Posts: 8
Registered: 10/28/07
Re: Problem with Firebird BLOBs and Unicode (is it a bug in dbExpress driver)
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 20, 2012 11:15 PM   in response to: Andrzej Brzezin... in response to: Andrzej Brzezin...
Hi Andre,

Yes, you are right !
I have the same behavior with D2010 Pro + FireBird 2.5 and CheeYang dbExpress (also with Upsene production dbExpress).

I tried many options but no thing !

Nevertheless, with FlamRobin I see the BLOB correctly in Arabic Font, but not in my Delphi DBMemo.
So, my solution was to change the BLOB Text in Varchar

Yes, I confirm this Problem !

yacine
Andrzej Brzezin...

Posts: 6
Registered: 12/8/02
Re: Problem with Firebird BLOBs and Unicode (is it a bug in dbExpress driver)
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 23, 2012 4:24 PM   in response to: Andrzej Brzezin... in response to: Andrzej Brzezin...
Thanks Yacine!

Thank you for confirming this. It is very disappointing that Embarcadero hasn't fixed
such an obvious and serious bug for so long. I wouldn't complain so much if
I could find some sort of a workaround. Any suggestions?

Andre
Jack Lee

Posts: 14
Registered: 9/26/02
Re: Problem with Firebird BLOBs and Unicode (is it a bug in dbExpress driver)
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 27, 2013 8:43 AM   in response to: Andrzej Brzezin... in response to: Andrzej Brzezin...
Andrzej Brzezinski wrote:
Thanks Yacine!

Thank you for confirming this. It is very disappointing that Embarcadero hasn't fixed
such an obvious and serious bug for so long. I wouldn't complain so much if
I could find some sort of a workaround. Any suggestions?

Andre

I have tested this problem with D2010, Xe, Xe2, XE3, confirming the same action.

FireBird 2.1. A database which char-set is UTF8, with a table with a BLOB field which char-set is utf8.

SQLConnection1, ServerCharSet='utf8'. SQLDataSet.commandtext:= 'select * from MyTable';

A DataSetProvider, A ClientDataSet, a DataSource1. a DBGrid and a DBMemo linked to the Blob field.

Input some string into DBMemo and ApplyUpdates the ClientDataSet. And check the blob field's content by database tool flamrobin, it show correct UTF8 string.

But, when reload data from database, DBMemo1 shows wrong string.

Yes, if I use this code below, I can show correct string in Memo:

procedure TForm1.Button3Click(Sender: TObject);
var
B: TBytes;
begin
B := DataModule2.CldUserID.FieldByName('Info').AsBytes;
Memo1.Lines.Add(TEncoding.UTF8.GetString(B, 0, Length(B)));
end;

So, through DBMemo, you can input string and applyupdates it into UTF8 Blob field correct, but you can not get it back with DBMemo or with ClientDataSet.FieldByName('MyMemo').AsString;

Again, I have tested, this code can work: DataModule2.ClientDataSet1.FieldByName('MyBlob').AsString := Memo1.Lines.Text;
but, this code cann't work correct: Memo1.Lines.Add(DataModule2.ClientDataSet1.FieldByName('MyBlob').AsString);

This problem is exists in D2010, XE, XE2, XE3.

Edited by: Jack Lee on Apr 27, 2013 9:11 AM
Jack Lee

Posts: 14
Registered: 9/26/02
Re: Problem with Firebird BLOBs and Unicode (is it a bug in dbExpress driver)
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 30, 2014 10:15 AM   in response to: Jack Lee in response to: Jack Lee
I test this problem in XE6 , and the problem is still there.

But, I test FireDAC in XE6, to connect FireBird DataBase with CharSet UTF8.

A Table named "TEST", which has tow field:
A varChar Field , CharSet = UTF8;
A Blob Field, CharSet = UTF8;

To use FireDAC, FDTable1, with a DBMemo1 to connect Blob Field, I can input Chinese and Japanese char in DBMemo, and reload data from FireBird, show it in DBMemo correct.

In XE6 FireDAC, there is a small bug: if you create field in FDTable in design time, the Blob field that IDE created is TMemoField. whith this type of field, it cann't show UTF8 content correct. But if there is no design time field in FDTable1, at run time, DBMemo1 can show right char. If you "New" a field in design time and set the field is TWideMemoField, it works fine.
diego rigoni

Posts: 1
Registered: 11/2/01
Re: Problem with Firebird BLOBs and Unicode (is it a bug in dbExpress driver)
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 3, 2013 5:38 AM   in response to: Andrzej Brzezin... in response to: Andrzej Brzezin...
... XE4 have the same bug....

the problem is open since 2010 in embarcadero, without any reloution.
Dmitry Burov

Posts: 2
Registered: 3/20/04
Re: Problem with Firebird BLOBs and Unicode (is it a bug in dbExpress driver)
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 29, 2014 9:43 AM   in response to: diego rigoni in response to: diego rigoni
diego rigoni wrote:
... XE4 have the same bug....

the problem is open since 2010 in embarcadero, without any reloution.

That is probably their way to make at least any reason to purchase Interbase

I think for the next upgrade we would have to purchase Delphi Pro + some 3rd-party Firebird library, which would not have those problems
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02