Firebird on DBFiddle - lack of error reporting and... lack of text (charsets) support. - Databases

Jack Douglas

We are always open to changing though — but the best place to suggest that is in a meta.ta post…

Jack Douglas replying to Arioch

In my opinon, the SO model 'just works'. I typically scan through the top answers until I'm clearly into the dross — and that way I get vastly more 'signal' to 'noise' that I would in the same amount of time on any sort of forum. We don't want to lose that or water it down at all, just improve on it!

Arioch replying to Jack Douglas

wonder if this "conscious decision" woul;d work in long term. This brings you back to USENET and alike tree-structured forums.  Those that in the end had to invent weekly-broadcasted FAQ's.  
  
SO was focused in quickest possible "hit and run" transactions. Being able to "commit" it and remove from you mental cache was handy. Also, for bad and for good, it helped future visitors (from Google, etc), to make the fastest decision. When SO appeared it looked heretic like shallowing things, reducing visitors to mindless automation pickign the lowest fruit without "turning one's brains on".  OTOH people often "copy-paste" those "proven solutions" from forums and FAQs without least thinking even without those "accepted answer" clues. And clear&fast transaction process on SO was part of its success formula. I wonder if any "middle ground" between SO and USENET won't in the end be lifeless chimera

Arioch

Hen and egg. I can answer some FB 2 questions, i seem to skip FB 3 though.  But, there is no questions. Now, i tried to put "Firebird" tag to my question and it never worked. No errors on "Add tag" , just ignoring my action. SO is predictable with tags, it either works or gives errors.

Jack Douglas

Are you asking bacause you plane to ask (or blog about) some Firebird questions here? Hope so :)

Jack Douglas replying to Arioch

Implied tags we do have — each tag can imply up to one other (no more). I just added `firebird-3` and `firebird` appeared automatically

Jack Douglas

As for 'closed', I've added a tag here like on meta.se and applied it. It should really be specific for 'Meta' type questions but that isn't possible (yet — we might change that)

Jack Douglas replying to Arioch

We've got answer 'labels' on certain question types (like 'concept' here on dba.ta, eg [this one](https://topanswers.xyz/databases?q=1092)) — but that isn't the same thing, and not having accepted answers is a conscious decision

Arioch

Also, don't you think there can be a "Firebird" tag, and perhaps even "Firebird 1/2" and "Firebird 3+" tags, as the changes were large akinf to a watershed. I also wonter if TA has an idea of umbrealla tags, implicitly provided by questions. Say, "Firebird 1/2" tagged question is also considered implicitly tagged with version-agnostic "Firebird RDBMS" tag, for search purposes

Arioch

@Jack, re: [your answer](#a1708),  TA does not have that concept of an answer beign "chosen" or "confirmed" or of an "issue" being "closed", or does it?

Jack Douglas

@Arioch Thanks, it's great to have someone who understand's Firebird digging around these issues!

Arioch

https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=76aecd7ada64a177403fc91cd7fcf9f8  
  
Hopefully, this case is closed now, thanks for quick reaction.

Arioch

Of course, proof is in the pudding, so the primary argument is that the raw ascii_char-based query now fails gracefully, and immediately coerced  one works (priorly i had to do three nested coercions).  Seems be okay now.

Arioch

Well, i can see two changes :-)  
  
`select 0x85, _win1251 '' || ascii_char(0x85) from rdb$database` - this coersion now works! This makes Firebird correctly transliterate into UTF-8 requested by connection, which was not possible before with NONE-charset connection.  
  
And the query against `mon$attachments` quoted above shows the connection charset was applied, meaning your changes to `ibase_connect` are working as intended.

Jack Douglas replying to Arioch

yes good idea, I've tweaked that too now

Jack Douglas replying to Arioch

yes that is planned :)

Jack Douglas replying to Arioch

We are now passing the `charset` parameter to [`ibase-connect`](https://www.php.net/manual/en/function.ibase-connect.php), though I can't see any difference in behaviour

Arioch

One more offtopic. You currently greet FB users with `select rdb$get_context('SYSTEM', 'ENGINE_VERSION') as version from rdb$database;` handshake, to make them know. Can you add there that very `RDB$CHARACTER_SET_NAME` column too?

Arioch

try to find in your lang/lib the code generating DPB, there should be clues how to request it to include lc_ctype attribute set to UTF-8

Arioch

But the very connection made after DB file wascreated still has default NONE charset. Only half the problem fixed. We have to find the conneciton-making code and add UTF-8 attribute there too. in FB C-lang API terms it was `lc_ctype` parameter. From `ibase.h`: `#define isc_dpb_lc_ctype                  48`  DPB stands for "database parameters block", a kind of binary list or array passed with database open requiest is called by app from fbclient.dll (or libfbclient.so)

Arioch

So, we can supposedly see that 1) database creation charset was fixed indeed. Maybe some users would prefer an option to create non-Unicode databases, but i do not think so. They hardly would need more than 8K-letters of text data anyway.

Arioch

Offtopic, I wonder if this chat could auto-detect dbfiddle links and convert them into that fancy wrapping box.

Arioch

https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=1b0a33fdb50f3b354c69f557e7049fb3

Arioch

One more query: `select RDB$CHARACTER_SET_NAME, RDB$CHARACTER_SET_ID  from mon$attachments, RDB$CHARACTER_SETS
where MON$CHARACTER_SET_ID = RDB$CHARACTER_SET_ID`

Arioch

Look at this now: it still fails, but now it does not crash catastrophically. https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=58b291dc36f9117f95a6fc1e2dc5ca78

Arioch

Now.... when i issued `select RDB$CHARACTER_SET_NAME from rdb$database;` i was sure i am quierying the charset of the connection. But perhaps that was a big blunder on my part.

Arioch

Since in your case there is only one such app, dbfiddle itself, it is largely alleviated. Until i jump in and use ascii_char function

Arioch

When any of those charsets is NONE, neutral binary charset, the server can not know and skips transcoding, it is now responsibility of all you applications to work uniformly with binary streams disguised as text data.

Arioch

Now, maybe i am misdiagnosing....  
There are TWO factors in game.  
1) charsets of every single column of char/varchar/{text blob} type. Until overriden by DDL, it is inherited from the database's default one, which should be set by CREATE DATABASE probably.  
2) charset of the very CONNECTION. An application (in your case - dbfiddle front-end) declares to the server which charset it wants texts to be given on select, and to be received on insert/update, then the server engine matches that charset with specific column charset and applies required transcoding (or fails with error)

Jack Douglas replying to Arioch

I set the char set in the CREATE DATABASE command that runs each time you run a fiddle

Jack Douglas

eg `alter character set utf8 set default collation unicode_ci_ai`?

Jack Douglas

Should we set a default collation too?

Jack Douglas replying to Arioch

<>https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=e95d5ba3a2e5e555e539784368f618b0

Arioch

select RDB$CHARACTER_SET_NAME from rdb$database

Arioch

Basically, the second query was exactly asking Firebird "what do you think the encoding of text i am expeciting from you should be". We should arrange that connection to see UTF8 there, not NONE

Arioch

Hello. Sure, there has to be some meaningful text charset declared when doing database connection. Due to backward compatibility by default FB uses "NONE" charset, which means no human readable text, merely binary byte streams. Which is obviously a laandmine waiting to be stepped upon. The details about how to set UTF-8 charset when doing the connection depends upon language+library used, so i can only suggest things in most generic terms - i neither saw code nor i know whether any relevant public documentation exists. I can delve into Firebird fbclient.dll API/ABI and their docs, but i think this in turn would be of little help for you.

Jack Douglas

What is the next step do you think — is there something more rational we could be doing?

Jack Douglas

@Arioch, I only half-understand what I'm doing here but I've applied a fix that checks if a field is valid utf8 and displays an error message if it isn't.

1 Answer