How do I implement multi language in SQL server queries

Question

0.00/5 (No votes)

See more:

Hi,
I am working on migrating my back-end database from MS access to SQL server.

My access application is multi-user and multi-language based.

The front end adapts the language of the forms and reports based on a parameter set via an ini file at startup i.e. adapted to the preferences of the end-user.

In access I solved using a translation table in the front-end db and a translation class handling all the translations for forms, reports and popups.

Lookup tables (f.i. document types, contact types, account types ....) have a reference to a row in the translation table and if needed in a query I use a global function handling the call to the language class to return the requested data in the right language.

So some tables containing lookup data (not editable by users - but I want them in the back-end for data integrity) contain besides some parameters to control program flow also the type's name and description reference using to retrieve them in the language table and they sometimes appear in query results.

I am stuck implementing that last part (translation within a query) with an SQL server back-end.

Thought about 3 options:
1. create a view in SQL server, link the view as a table in access front end and then another query in access selecting the view with the extra column with the translate function.
2. same as above but instead of view a pass-through query and then a second query selecting the stored pass through query + translation.
3. Create a stored procedure or table-valued function passing the requested parameter as language and implementing the language lookup in SQL server.

A separate front-end per language is not a solution as it does not solve the lookup table's problem.

What is your advise, is there another solution ?

What I have tried:

Already converted the back-end db filled with test data and wrote and tested code for accessing it, but got stuck with that particular problem

Posted 30-Apr-23 5:17am

Member 8101499

Add a Solution

Comments

[no name] 30-Apr-23 14:57pm

"Translate" is a very broad subject. Substituting one picklist for another is different from translating free form text. Can't tell what patterns you're using.

Member 8101499 1-May-23 8:16am

Gerry,
So sorry my naming was confusing.
My "translating" is in fact just looking up a table containing a unique ID and one column per supported language. The "Translate" function replaces the passed ID with the content of the current language column for that row when called from a query. As you mentioned in your reply it is just a substitution based on a reference pointing to a row in a table and a parameter loaded dynamically when starting the front-end.

[no name] 2-May-23 9:29am

Translation is done for the benefit of a user (i.e. what do they "see"; what do they "want") So, talking about back ends is the cart before the horse.

CHill60 2-May-23 5:35am

It might help if you shared some sample data and what you would expect from that data. I'm struggling to see what the problem is.
I set up some simple tables

declare @languages table(langID int identity(1,1), langName nvarchar(50));
insert into @languages(langName) values
('english'),('french'),('german');

declare @formtext table(itemID int, formtext nvarchar(50), langID int)
insert into @formtext (itemID, formtext, langID) values
(1, 'forename', 1),
(1, 'prénom',2),
(1, 'vorname',3),
(2, 'surname', 1),
(2, 'nom de famille',2),
(2, 'nachname',3);

And then a couple of parameters - these would either be parameters to your query, or write a stored procedure to return the data

declare @userLang nvarchar(50) = 'french';
declare @phraseRequired nvarchar(50) = 'forename'

Then returning the correct word or phrase simply becomes

select @phraseRequired, t1.formtext
from @formtext t1
join @languages t2 on t1.langID = t2.langID and t2.langName = @userLang
join @formtext t3 on t1.itemID = t3.itemID and t3.formtext = @phraseRequired

If I'm completely wide of the mark then you will need to supply more information on your model

Member 8101499 3-May-23 9:51am

Hi Chill60,
thanks for your answer but the problem is not at the SQL query level, the problem lies in the fact that per original setup the language is dynamically set when starting the Access frontend, so the Access frontend code has a language class property with the current language.
The language table has as columns: ID, Object, Field, E, F, N etc... No problem to write the query but to have the language available in the backend SQL server for each opened connection - it's a multi-user application.
Actually as I don't know how to pass that data for every connection separately I solved it by creating 2 queries in Access a pass-through query to pull the needed data from SQL server and another calling the first with a function looking up the language table that I leave in the frontend for now this way:
1. pass-through: Select Name,Amount,LanguageID from dbo.SQLServerTable WHERE ....
2. local: Select Name, Amount, languageData(LanguageID) FROM pass-through;
LanguageData() is a public Function (in fact I named it Translate()) in Access frontend that retrieves the data for the current language.
I wondered if there wasn't a better way.

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)