[not possible after creation] set of db's default character set
lazarus / 16-Databases

----------------------------------------------------------------------
Nicole · 2024-01-24 12:12

The default character set in my database is "none".
Since years(!), I want to set it ti "UTF-8".
Whatever I do in all my migrations, - it stays "none".

Can I set it by SQL?
Flamerobin is nearly sure not able to to it or it does not work.

----------------------------------------------------------------------
Zvoni · 2024-01-24 12:57

You have to set the Character Set during Creation of Database.

Not sure, if you can change it later
https://firebirdsql.org/file/documentation/reference_manuals/fbdevgd-
en/html/fbdevg30-db-create.html
https://firebirdsql.org/rlsnotesh/rnfb25-ddl.html#rnfb25-ddl-
altrchrset

...and it's called "UTF8", not "UTF-8"....

----------------------------------------------------------------------
rvk · 2024-01-24 13:51

Quote from: Zvoni on January 24, 2024, 01:57:39 pmYou have to set the
Character Set during Creation of Database.Yep. That's also my
understanding.

Quote from: Zvoni on January 24, 2024, 01:57:39
pmhttps://firebirdsql.org/rlsnotesh/rnfb25-ddl.html#rnfb25-ddl-
altrchrsetI think that this one is only for changing the COLLATION for
that character set. Not for the character set itself.

You can view the character set with
select rdb$character_set_name from rdb$database;

You can even change the character set there but there is A LOT MORE
involved with it.
Namely how ALL fields and domains store their values.
You would need to check and change all those to get a correct database
again.

(The rdb$character_set_name will only effect newly created
fields/domains.)

There are some utilities that can do it but I think they just dump and
pump all data to a new database.

----------------------------------------------------------------------
dsiders · 2024-01-24 14:30

Quote from: rvk on January 24, 2024, 02:51:47 pmQuote from: Zvoni on
January 24, 2024, 01:57:39 pmYou have to set the Character Set during
Creation of Database.Yep. That's also my understanding.

I guess it depends on the Firebird version. The Language Reference for
4.0 has:

Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i ALTER
DATABASE ... SET DEFAULT CHARACTER SET Changes the default character
set of the database. This change does not affect existing data or
columns. The new default character set will only be used in subsequent
DDL commands. 
https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref4
0/fblangref40-ddl.html#fblangref40-ddl-db-alter

----------------------------------------------------------------------
Zvoni · 2024-01-24 14:56

Sooo.... Bottom Line:
If you haven't set a Character-Set at Creation of the Database, your
only way would be to ALTER all Tables (Worst Case: All Columns) and
set the new Character-Set there.
NotaBene (if i read the dox right): No Validation is done for existing
data

----------------------------------------------------------------------
Nicole · 2024-01-24 15:23

This seems to be a lot of work for under the line and in my case - no
advantage.
Just the error messages about the thing were irritating.

Thank you for your comments.
I will put them into the db-directory, not to ask myself after some
years again.

----------------------------------------------------------------------
Zvoni · 2024-01-24 18:02

Considering FlameRobin (and other visual DB-Clients for other DBMS):
It‘s considered good practice to create your database from code rather
than use a „premade“ db from one of those tools.

Yes, by all means don’t stop using it during development, but the
moment you go live do everything from code.
And there you just have to remember to set the character set as the
first thing, and you are good to go

----------------------------------------------------------------------
Nicole · 2024-01-24 18:17

Thank you for the hint.
About "code": I thought, that such tools would generate this code,
don't they?

When I click "DDL" I see something, which looks like a generated code.
Do you suggest just to copy it and paste it into a SQL-editor and
commit it?

My problem is:
When I generated the database I had very little idea what a database
may be at all.
And UTF8 I had not heard at all that time.
The only thing I knew: My ASCII files will not do it any more.

----------------------------------------------------------------------
Zvoni · 2024-01-24 18:43

Yes and no.
Those tools can „generate“ the DDL, yes.
But copy that DDL into your pascal-code, ajust what you need
(character set etc.) and execute it from there.
That way you will never forget to set those pesky attributes you
always forget when using one of those tools.

Speaking from experience here