I-DotNET


We have an existing C# application using SQL Server 2000 database. Currently, the app is English-only, however the business now wants to internationalize the app to support multiple languages (French, Spanish, Russian, Polish, etc.). We will have only one database. So, data in multiple languages will be stored in the same database.

My question is about how this affects the ORDER BY clauses in our SQL stored procedures.

I found an article on Microsoft's web site which seemed to imply that we will have to use COLLATE on every single ORDER BY. This seems crazy to me. If this is true, that means we'll have to pass down a collation name to the stored procedures.

Can anyone clear this up for me




Re: SQL Server 2000 and Internationalization

Caddre


The reason is some languages use collation, you are dealing with the latin alphabet so I don't see an issue if you use collation precedence. I am assuming you know you have to create tables for each language if you don't want to run into character conversion issues. Hope this helps.

http://msdn2.microsoft.com/en-us/library/aa258272(SQL.80).aspx







Re: SQL Server 2000 and Internationalization

I-DotNET

> The reason is some languages use collation, you are dealing with the latin

> alphabet so I don't see an issue if you use collation precedence

Well, we might have to support Asian languages, too, so whatever solution we come up with, it will have to be flexible to handle any language.

> I am assuming you know you have to create tables for each language

> if you don't want to run into character conversion issues.

Well, I wasn't planning on creating separate tables for each language. I was thinking of creating a string table that contained all localized strings for the database.

First, I was going to create a reference table with languages/cultures. It would have just 3 columns:

- culture_id (primary key)

- culture_code (nvarchar 5)

- culture_name (nvarchar 50)

Examples of culture_code would be "en-US" and "fr-FR". Examples of culture_name would be "English (US)" and "French (France)".

Then I was going to create a string table. It would have have 4 columns:

- string_id (primary key)

- string_key (not sure yet what datatype this would be)

- culture_id (foreign key)

- string_value (nvarchar 256)

Finally, each table that had a string that needed to be localized would have a foreign key to the string table:

- widget_id (primary key)

- widget_name (foreign key to string table)

So, that's what I was planning.

But I'm new to internationalization, so please let me know if what I'm planning is wrong.






Re: SQL Server 2000 and Internationalization

I-DotNET

I should also point out that this is an ASP.NET 2.0 application, so users across the world will be using it at the same time.



Re: SQL Server 2000 and Internationalization

Caddre

We need to deal with one problem at a time if you plan to support Asian languages like Chinese, Korean and Japanese with more than 2000 character alphabet you have just found why Microsoft added the ORDER BY requirement. And you need a table for each language because that thing you are talking about was what I was told we would use in my last project and it does not work. You are lucky here are the Asian languages and collation in 2000. Remember not to use BIN(binary sort order) the fastest but require case sensitive collation.Hope this helps.

196

Chinese_Taiwan_Stroke_BIN

197

Chinese_Taiwan_Stroke_CI_AS

198

Chinese_PRC_BIN

199

Chinese_PRC_CI_AS

200

Japanese_CS_AS

201

Korean_Wansung_CS_AS

202

Chinese_Taiwan_Stroke_CS_AS

203

Chinese_PRC_CS_AS

196

Binary order, for use with the 950 (Traditional Chinese) character set.

197

Dictionary order, case-insensitive, for use with the 950 (Traditional Chinese) character set.

198

Binary order, for use with the 936 (Simplified Chinese) character set.

199

Dictionary order, case-insensitive, for use with the 936 (Simplified Chinese) character set.

200

Dictionary order, case-sensitive, for use with the 932 (Japanese) character set.

201

Dictionary order, case-sensitive, for use with the 949 (Korean) character set.

202

Dictionary order, case-sensitive, for use with the 950 (Traditional Chinese) character set.

203

Dictionary order, case-sensitive, for use with the 936 (Simplified Chinese) character set.






Re: SQL Server 2000 and Internationalization

I-DotNET

First, thank you very much for your help and view point.

> And you need a table for each language because that thing you are talking

> about was what I was told we would use in my last project and it does not work.

If you have a table for each language, then each table has it's own name, right How do you do your SQL queries Are they dynamic Or do you have a seperate stored procedure for each language





Re: SQL Server 2000 and Internationalization

Caddre

We used stored procs the reason for a table for each language user inserts and select becomes simple and you can use the VS2005 Advanced save as option to save your code related to each language. This reduce the risk for character conversion from your UI affecting static data in the database.






Re: SQL Server 2000 and Internationalization

I-DotNET

Maybe I'm misunderstanding you but if you have a separate table for each language, doesn't that mean you need a separate stored procedure for each language So if you have 10 languages, you need need 10 InsertCustomer sprocs



Re: SQL Server 2000 and Internationalization

Caddre

You understand me for INSERTs and UPDATEs each language means clean inserts and updates but selects meaning reads you can use a CASE statement with all languages. It is a lot of work but if it is simple Datadirect will not earn more than $100 millions a year doing it for big companies. I was lucky to be in a group with someone who writes code for the HP XP support site it comes in 32 languages.






Re: SQL Server 2000 and Internationalization

I-DotNET

OK, good point about the INSERTS and UPDATEs.

> And you need a table for each language because that thing
> you are talking about was what I was told we would use in
> my last project and it does not work.

Why do you say it won't work Can you explain what's wrong with this design





Re: SQL Server 2000 and Internationalization

Caddre

There are some materials out from Microsoft showing you how to do it the way you want it but Microsoft examples deals with only the 26 characters Latin alphabet and not Chinese and Japanese which comes in more than 2000 characters. Take the Asp.net site it tried that automatic with Chinese the pages were blank so it was dropped. Another thing you are in 2000 with less collation related features, the SQL Server and Visual Studio teams keep adding features with each release to make it easier for developers.




Re: SQL Server 2000 and Internationalization

I-DotNET

> There are some materials out from Microsoft showing you how to do it the

> way you want it but Microsoft examples deals with only the 26 characters Latin

> alphabet and not Chinese and Japanese which comes in more than 2000 characters.

If the string table used NVARCHAR, why wouldn't it work with Chinese and Japanese

String table:

- string_id (primary key)

- string_key (not sure yet what datatype this would be)

- culture_id (foreign key)

- string_value (nvarchar 256)





Re: SQL Server 2000 and Internationalization

Caddre

Nvarchar just means unicode and nothing more that is the reason you need to add the correct collation in both DDL(data definition language) and DML(data manipulation language) in 2005. I have helped a lot of people go to the Asp.net site and look at the Chinese it is still in English and run a search for localization and Caddre my profile you will find ready to use solutions. Running tests in your box and live applications taking inserts are not the same.






Re: SQL Server 2000 and Internationalization

I-DotNET

You know, I was thinking about using case statement to determine the collation sequences in the ORDER BY clauses. To me, that seems redundent and error prone. Then I had this idea. What if you moved the sorting out of the stored procedure and into the .NET data access class. If you sort at the .NET level, you could create a common class that handles the sorting based on culture and you wouldn't need all these case statements in your stored procedures. What do you think

(I'm just trying to bounce ideas and get other people's viewpoints. We haven't really started internationalization yet, so there's plent of time to explore our options and figure out what makes the most sense.)





Re: SQL Server 2000 and Internationalization

Caddre

We ANSI SQL writers call the ORDER BY a cursor but nothing in the application layer come close in sorting. When you are ready spend time at the localization forum at Asp.net, many ready to use solutions. Goodluck.