(SOLVED) Lazarus + Firebird + Emoji

[xinyiman]

Hi guys, can anyone explain to me how to insert a string containing emojis into a firebird database?

[Zoran]

Hi guys, can anyone explain to me how to insert a string containing emojis into a firebird database?

Hi.

Are you serious?
How can you expect help when you provided no information about what you have tried?
Xinyiman, you have been active member in this forum for quite a long. You should know better.

Would you mind at least providing the example what does work when string contains only ASCII and does not work with some other characters.

Then,
What is the encoding of the database field you are trying to write into? Is it utf8?
What is the encoding of the connection between your application and database? Is this a gui (LCL dependent) application?

What db connection library do you use (SQLDb, IBX, Zeos, ...)?
What is Firebird server version?

[winni]

s := 'It is 🫖  teatime';

[xinyiman]

Hi guys, can anyone explain to me how to insert a string containing emojis into a firebird database?

Hi.

Are you serious?
How can you expect help when you provided no information about what you have tried?
Xinyiman, you have been active member in this forum for quite a long. You should know better.

Would you mind at least providing the example what does work when string contains only ASCII and does not work with some other characters.

Then,
What is the encoding of the database field you are trying to write into? Is it utf8?
What is the encoding of the connection between your application and database? Is this a gui (LCL dependent) application?

What db connection library do you use (SQLDb, IBX, Zeos, ...)?
What is Firebird server version?

You are definitely right. I have not specified anything because any solution suits me. I did a simple test with firebird 2.5 on linux (utf8 charset). I used zeoslib to do a test. The target field is a set_type text blob. By inserting a simple text of course the insert works. But passing it an emoji fails the SQL execution. I didn't do anything else. And obviously being just an analysis for the feasibility of a project, any solution is fine by me. Sorry again if I didn't explain myself well in the first message.

PS: I love martin mystere and his helper: java

[JuhaManninen]

I did a simple test with firebird 2.5 on linux (utf8 charset). I used zeoslib to do a test. The target field is a set_type text blob. By inserting a simple text of course the insert works. But passing it an emoji fails the SQL execution. I didn't do anything else.

The bug is either in Firebird or in Zeoslib then.
Usually code dealing with UTF-8 encoding works because multi-byte codepoints are so common.
Code dealing with UTF-16 encoding however is often buggy and actually supports only UCS-2.
Emojis typically are outside of Unicode BMP and the bug shows up.
My guess is that somewhere the UTF-8 data is converted to UTF-16 and then handled with sloppy code.
Maybe you can debug Zeoslib. If it passes the data correctly forward, then Firebird is guilty.

[Zoran]

You are definitely right. I have not specified anything because any solution suits me. I did a simple test with firebird 2.5 on linux (utf8 charset). I used zeoslib to do a test. The target field is a set_type text blob. By inserting a simple text of course the insert works. But passing it an emoji fails the SQL execution. I didn't do anything else. And obviously being just an analysis for the feasibility of a project, any solution is fine by me. Sorry again if I didn't explain myself well in the first message.

Zeos connection has two fields -- ControlsCodePage and ClientCodepage (at least it is so with Zeos 7.2, which is rather old, but I still have it).
I don't know if it helps, but make sure they are set to cCP_UTF8 and UTF8.

If not, try to create simple test with SQLDb. If it works, then it is Zeos bug.
Then report it in their forum.

Anyway, paste here the text example which fails, so that we can try the same.

PS: I love martin mystere and his helper: java

 

[xinyiman]

Zoran, I added the two parameters you were telling me. The code no longer failed. He entered the record. But it transformed the emoji 👍 in "=M".

I have searched around and for mysql it seems that utf8 is not enough, it must become utf8mb4. I just don't know how to set it up in firebird when creating the database. I use Flamerobin for db management. Tips?

[devEric69]

I use Flamerobin for db management. Tips?

Hello,

For me, RedExpert "rocks" (nevertheless, I don't preview too many frames arranged at the same time in RedExpert main form; otherwise, some Java widget{s} freeze{s} it; I don't understand which one{s}.), and IBX "rolls" ☺.

[JuhaManninen]

I have searched around and for mysql it seems that utf8 is not enough, it must become utf8mb4. I just don't know how to set it up in firebird when creating the database.

What is utf8mb4? ... OK, I can find it in the net :

UTF-8 is a variable-length encoding. In the case of UTF-8, this means that storing one code point requires one to four bytes. However, MySQL's encoding called "utf8" (alias of "utf8mb3") only stores a maximum of three bytes per code point. ...
...  As of MySQL 5.5.3, the utf8mb4 character set uses a maximum of four bytes per character supports supplemental characters

WTF! They already handle a variable length encoding but then leave the last byte out. Why? Totally stupid.
What is the connection between Firebird and MySQL? I thought they are separate DBs. I am not a DB expert as you can see.

[Zoran]

The Firebird documentation (see here) says that utf8 reserves four bytes.

That utf8mb4 is something specifically created by MySQL, as from the start they didn't support utf8 correctly.

[Zoran]

Zoran, I added the two parameters you were telling me. The code no longer failed. He entered the record. But it transformed the emoji 👍 in "=M".

Does it really transform it or it is that FlameRobin does not display it correctly?
Did you try writting the string it in Lazarus application and reading it in string in that same application?

[devEric69]

Beware: for information, FlameRobin is an abandonware (globally, its database engine's driver has diverged too much from the official Firebird engine features. It can't evolve anymore: bad design choice, AFAIK).

[xinyiman]

I actually reloaded the data from the same program and I see the emoji correctly. Thanks for the time you have dedicated to me.

[Zoran]

I actually reloaded the data from the same program and I see the emoji correctly. Thanks for the time you have dedicated to me.

That was what I expected. I'm glad you solved it.

[valdir.marcos]

Beware: for information, FlameRobin is an abandonware (globally, its database engine's driver has diverged too much from the official Firebird engine features. It can't evolve anymore: bad design choice, AFAIK).

Are you sure?

I have checked 5 releases in the last 6 months, being 2 in the last 10 days:
https://github.com/mariuz/flamerobin/releases