Hi,

I briefly tried to store some unicode text (UTF-8) into a Firebird database and got some strange results in the Lazarus DB components. I'm not sure if the issue is in the Sqldb components or in the actual GUI DB components of LCL.

Anyway, can one define a VarChar() field with unicode text? I'm using Firebird 2.1.2

For example:
varchar(10) field definition

- that would allow max. of 10 ansi characters
- what about UTF-8 characters? Some chars can be anything from 1-4 bytes.
- does varchar() use bytes or characters? I believe it's bytes if I'm not mistaken.

If I can't use varchar(), then what? Char() :-( That would waste a lot of space, but if I have no alternative, then I guess it will have to do.

[...just did some more Googling...]

http://www.firebirdsql.org/manual/migration-mssql-data-types.html I found a web page that mentions the migration of SQL Server to Firebird. It mentions NVarChar (sql server specific type for unicode text) to VARCHAR(x) CHARACTER SET UNICODE_FSS (firebird support). So I guess that's what I need to try. But which unicode format is firebird going to use? utf-8, utf-16, etc...

Has anybody successfully used Firebird DB to store Unicode text (preferably UTF-8 text)? Anybody done that with Lazarus?

Regards,
- Graeme -

_______________________________________________
fpGUI - a cross-platform Free Pascal GUI toolkit
http://opensoft.homeip.net/fpgui/

Hmm..that should be very easy with Firebird 2.1.2 though I've never tried yet. Lazarus is UTF-8 based, just set connection setting to UTF8 and create database with UTF8 collation as default.

Then in theory you could forget that it's all unicode.

Boguslaw

Graeme Geldenhuys wrote

database and got some strange results in the Lazarus DB components.

The following could explain the strange results (extra spaces).

http://www.janus-software.com/fbmanual/manual.php?book=PSQL&topic=33

"The VARCHAR(n) datatype contains text of varying length, up to a maximum of n characters. The maximum size is 32,767 bytes, which can be 10,992 to 32,767 characters, depending on the character size (1..3 bytes). You must supply n; there is no default to 1.

Firebird converts from variable-length character data to fixed-length character data by adding spaces to the value in the varying column until the column reaches its maximum length n. In the reverse conversion, trailing blanks are removed from the text."

Firebird pads the characters with spaces which Sqldb or LCL doesn't remove. Or that's how it seems...

Regards,
- Graeme -

_______________________________________________
fpGUI - a cross-platform Free Pascal GUI toolkit
http://opensoft.homeip.net/fpgui/

Op vrijdag 15-05-2009 om 12:12 uur [tijdzone +0200], schreef Graeme Geldenhuys:

Depends. You have to define which character-set you use for each (n)(var)char(x) field on a FB database. But (x) is always the character-count, not the byte-count.

When you don't supply a character-set for your (n)(var)char-field, the default character-set is used which is supplied while creating the database. If you haven't done that either, ansi is used.

The n-prefix makes Firebird store the data in blocks of two bytes. That's made for UCS-16 systems like windows. Then each ucs-16 character fits inside one such block. (But using a nvarchar with a UTF-8 characterset is obviously stupid, use a normal varchar instead) for UTF-16, nvarchar could also be handy, offcourse.

The UNICODE_FSS charset is obsolete. You can still use it, though. But I would simply use UTF-8.

guess that's what I need to try. But which unicode format is firebird going to use? utf-8, utf-16, etc...

That's completely up to you.

Has anybody successfully used Firebird DB to store Unicode text (preferably UTF-8 text)? Anybody done that with Lazarus?

You could do that, but that's not what you want. All text above is not very interesting in this case.

How Firebird STORES the data is irrelevant in this case. What you want is that Firebird sends and recieves the data in the UTF-8 format. When you make a connection with a Firebird-server, you have to tell the server in which character-set you want to talk to it. Then it will do all the necessary conversions for you.

Setting the character-set which should be used to communicate with the server can be done with a TIBConnection parameter or the TIBConnection.charset property.

(The characterset used to store the data is relevant, offcourse, since when it's the same characterset as is used to communicate, no conversion is necessary. But the point is clear, I think)

Joost.

Joost van der Sluis pisze:

Yep.Just create database with UTF-8 and connect using UTF-8, using Lazarus UTF-8. No problems then ,other then fpc RTL ANSI functions ;-)

Boguslaw

Joost van der Sluis wrote

The UNICODE_FSS charset is obsolete. You can still use it, though. But I would simply use UTF-8.

OK, I recreated by database using UTF8 as the default character set.

Setting the character-set which should be used to communicate with the server can be done with a TIBConnection parameter or the TIBConnection.charset property.

I then tried to pass in parameters to the database connection to specify UTF8 for the connection as well, but it seems TIBConnection doesn't read its own Params property.

Params.Text = 'role=m2userrole;lc_ctype=UTF8'

I can't do the following:
IBConnection.Role = '...'
IBConnection.CharSet = '....'
because I don't have direct access to the database component (from my application). I'm using tiOPF, and we normally pass such variables via the Params property.

If I modify the SqlDB-firebird persistence layer to explicitly look for ROLE and CHARSET (or LC_CTYPE) in the Params strings and then set each property on the DB, then it works. But surely the IBConnection component should check it's own Params property for such values? Should I report this as a bug in SqlDB?

Regards,
- Graeme -

_______________________________________________
fpGUI - a cross-platform Free Pascal GUI toolkit
http://opensoft.homeip.net/fpgui/

Graeme Geldenhuys wrote

I then tried to pass in parameters to the database connection to specify UTF8 for the connection as well, but it seems TIBConnection doesn't read its own Params property.

Here is a quick test I did with an one form LCL application.

  IBConnection1 := TIBConnection.Create(self);
  IBConnection1.DatabaseName := '/home/graemeg/programming/data/unicode.fdb';
  IBConnection1.HostName := 'localhost';
  //IBConnection1.CharSet := 'UTF8';
  //IBConnection1.UserName := 'sysdba';
  //IBConnection1.Password := 'masterkey';
  IBConnection1.Params.Text :=
'user_name=sysdba;password=masterkey;lc_ctype=UTF8';
  IBConnection1.Connected := True;

Using Params.Text to set the username, password and character set doesn't work. As far as I know this is allowed in Kylix and Delphi.

Regards,
- Graeme -

_______________________________________________
fpGUI - a cross-platform Free Pascal GUI toolkit
http://opensoft.homeip.net/fpgui/