How to sql autotransfer encode?

Zuben45

Greetings,
I have small problem, in company I have database (Firebird) with encode windows-1250 and website with encode UTF-8, and when I want get some data with czech chars, than I must use function

iconv("windows-1250","UTF-8",$myVal)

Is some way how to automatic transfer output to UTF-8 ? Anywhere when I create connection or else ?

In my own website I always use UTF-8 encode for database and for website too, so I didnt have this problem, so but now yes :/.

Company database i couldnt manage (can but only with limit)
Thank you

Last edited by Zuben45 (2016-01-18 09:25)

premek_k

And what about defining charset in connection string? (It's only an idea).

Zuben45

premek_k wrote:

And what about defining charset in connection string? (It's only an idea).

Yes, I tried it, I set charset WIN1250, but I get something this:

Ond�ej �t�bl

and I must set noescape too :/

When I set UTF-8, I get error, than index of product not exists.

Last edited by Zuben45 (2016-01-18 11:47)

Nivin17

SQL-Management Console 2008 and upwards is a UNICODE application. All values entered or requested are interpreted as such on the application level. Conversation to and from the column collation is done implicitly. You can verify this with:

SELECT CAST(N'±' as varbinary(10)) AS Result
This will return 0×B100 which is the Unicode character U+00B1 (as entered in the Management Console window). You cannot turn off “Auto Translate” for Management Studio.

If you specify a different collation in the select, you eventually end up in a double conversion (with possible data loss) as long as “Auto Translate” is still active. The original character is first transformed to the new collation during the select, which in turn gets “Auto Translated” to the “proper” application codepage. That's why your various COLLATION tests still show all the same result.

You can verify that specifying the collation DOES have an effect in the select, if you cast the result as VARBINARY instead of VARCHAR so the SQL Server transformation is not invalidated by the client before it is presented:

SELECT cast(columnName COLLATE SQL_Latin1_General_CP850_BIN2 as varbinary(10)) from tableName
SELECT cast(columnName COLLATE SQL_Latin1_General_CP1_CI_AS as varbinary(10)) from tableName
This will get you 0×F1 or 0×B1 respectively if columnName contains just the character ‘±’

You still might get the correct result and yet a wrong character, if the font you are using does not provide the proper glyph.

Please double check the actual internal representation of your character by casting the query to VARBINARY on a proper sample and verify whether this code indeed corresponds to the defined database collation SQL_Latin1_General_CP850_BIN2

SELECT CAST(columnName as varbinary(10)) from tableName
Differences in application collation and database collation might go unnoticed as long as the conversion is always done the same way in and out. Troubles emerge as soon as you add a client with a different collation. Then you might find that the internal conversion is unable to match the characters correctly.

All that said, you should keep in mind that Management Studio usually is not the final reference when interpreting result sets. Even if it looks gibberish in MS, it still might be the correct output. The question is whether the records show up correctly in your applications.