Charset UNICODE_FSS problem [SOLVED]

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for InterBase & Firebird in Delphi and C++Builder
Post Reply
trindade
Posts: 12
Joined: Fri 23 Jul 2010 20:30
Location: Chapecó, SC / Brazil

Charset UNICODE_FSS problem [SOLVED]

Post by trindade » Thu 29 Jul 2010 17:46

Greetings!

My application was in Delphi 2007 using DbxIda 2.40. Worked fine. Was upgraded to Delphi 2010 and now uses DbxIda 2.50.0.23.

When I log in and my application retrieve some data from the DB, I receive the error:
"COLLATION PT_BR for CHARACTER SET UNICODE_FSS is not defined'

I used previously the character set ISO8859_1, wich contains this collate, "PT_BR", very useful here in Brazil. My database was designed using this character set, and I still want to use it.

In the TSQLConnection, on the Devart driver options, under "Charset" I put iso8859_1 and nothing happens, the connection is still made using the UNICODE_FSS charset. Tried to put it in the TSQLConnection's parameters, with no luck. Tried to set iso8859_1 by code on the Delphi's BeforeConnect method, with no success.

The option "UseUnicode" of the driver is not set, I've verified it.

What I'm doing wrong? I need the driver to connect using charset "ISO8859_1" no matter what, not "UNICODE_FSS", and I can't.

Thanks in advance!
Last edited by trindade on Mon 02 Aug 2010 18:02, edited 1 time in total.

trindade
Posts: 12
Joined: Fri 23 Jul 2010 20:30
Location: Chapecó, SC / Brazil

Post by trindade » Thu 29 Jul 2010 19:18

Updating...

If I use the native Delphi 2010 Firebird driver, the TSQLConnection respects the "Charset" setting and effectively connects using charset "ISO8859_1", avoiding me the error mentioned above.

The SQL that causes the error, just in case, is:
SELECT * FROM usuarios WHERE usuario = 'admin' COLLATE PT_BR

All the character fields in my database and the database's charset are ISO8859_1.

trindade
Posts: 12
Joined: Fri 23 Jul 2010 20:30
Location: Chapecó, SC / Brazil

Post by trindade » Fri 30 Jul 2010 18:12

I forgot to mention. I use Firebird 2.1.3.

Some hints on my case? I didn't find a solution here yet, and don't want to use Delphi's native Firebird driver, because I've detected some annoying bugs on it.

Any help would be welcome, I'm stuck here.

Dimon
Devart Team
Posts: 2910
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Mon 02 Aug 2010 08:26

In Delphi 2010 the UseUnicode connection parameter is set to True by default.
To solve the problem set the proper parameter of TSQLConnection to True, like this:

Code: Select all

  SQLConnection.Params.Values['UseUnicode'] := 'False';

trindade
Posts: 12
Joined: Fri 23 Jul 2010 20:30
Location: Chapecó, SC / Brazil

Post by trindade » Mon 02 Aug 2010 12:15

Dimon wrote:In Delphi 2010 the UseUnicode connection parameter is set to True by default.
To solve the problem set the proper parameter of TSQLConnection to True, like this:

Code: Select all

  SQLConnection.Params.Values['UseUnicode'] := 'False';
Nope. Thanks, but I've tested and I've got same error. Using "True" or "False".

I've read in the documentation, and read when UseUnicode is true, DbxIde forces the use of the charset UNICODE_FSS on the connection. That's precisely what I'm NOT needing. I've told on my previous post that UseUnicode is not set, precisely to avoid DbxIda to connect using UNICODE_FSS charset.

I just wanna connect using charset ISO8859_1 instead of UNICODE_FSS. With Delphi's native Firebird driver it works, with DbxIda not.

My suggestion is: the Charset property in TSQLConnection must have precedence over UseUnicode property. The programmer must have the responsibility of setting it properly, unless it's left blank. This should give the most flexibility for international apps.

In United States charset isn't important, because English words are simple, but on the rest of the world, where people use more complex language, with special characters, it's critical.

I'm still stuck here. Any other suggestions?

Dimon
Devart Team
Posts: 2910
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Mon 02 Aug 2010 13:55

I can not reproduce the problem.
Please, try to compose a small sample to demonstrate the problem and send it to dmitryg*devart*com, including script to create database objects.

trindade
Posts: 12
Joined: Fri 23 Jul 2010 20:30
Location: Chapecó, SC / Brazil

Post by trindade » Mon 02 Aug 2010 14:14

Dimon wrote:I can not reproduce the problem.
Please, try to compose a small sample to demonstrate the problem and send it to dmitryg*devart*com, including script to create database objects.
To reproduce the problem, you'll need a database with iso8859_1 fields and collate pt_br. Is just to apply the SQL query that I exemplified on my second post and the error will pop out.

But, most of all, you must have to verify under what charset the connection has been made. This is crucial, because in this resides my whole problem.

But OK, I'll gather the needed data and will send it to the e-mail specified. Will post the solution here later.

Thanks for the attention!

trindade
Posts: 12
Joined: Fri 23 Jul 2010 20:30
Location: Chapecó, SC / Brazil

Post by trindade » Mon 02 Aug 2010 18:01

OK, I've solved the problem.

Writing an application from scratch to demonstrate the problem to you, I discovered that my new application gave no errors. So, must not be DbxIda's fault.

Upon further investigation, I've discovered that in my existing app, I must specify the "collate" so special characters must be considered correctly on search queries. In my new app, it's irrelevant to specify the "collate" setting, all results are the same. Why this behavior, I've not surely identified yet.

But, at the end, the problem was my application loads DB settings from an INI file, and an obsolete setting was "ServerCharSet" to specify the DB charset, and the updated setting is "CharSet". Even specifying this new setting on TSQLConnection connection and in code, upon loading of INI file, this updated setting was overriden, the charset specified was not set and I got the error "COLATION PT_BR NOT DEFINED FOR CHARSET UNICODE_FSS". I've redesigned the INI parameter reading routine, and now appears that the charset is defined properly, since I still use "collate" and I've got this error no more.

Thanks for the help and attention, now development must go on. :wink:

Dimon
Devart Team
Posts: 2910
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Tue 03 Aug 2010 08:43

It is good to see that this problem has been solved. If any other questions come up, please contact me.

Post Reply