Storing and swapping between different languages

V5 Tutorial
1

:warning: Only works with SQL Express or SQL LocalDB databases (does not work with SQL Compact Edition (CE) databases).

I know a lot of people are using SambaPOS in their own language by changing the Button Header text of Automation Commands, amongst other things. I had a thought of being able to store Button Header text in different languages and being able to swap between those languages when needed.

I haven’t investigated into how this could be integrated directly within SambaPOS, but I have created a couple of SQL scripts you can run on your database using SQL Server Management Studio. Right now, they only work for Automation Commands, however it could be extended in the future to support other object types.

:warning: Always BACKUP your SambaPOS database before running any SQL scripts!

The Add / Update script will create a new table ObjectTranslations if it doesn’t exist already, and this is where all translated values are stored.

SQL Scripts

Add / Update Script:

-- Change below line to the name of your SambaPOS database
USE [SambaPOS5];

-- Change below line value to the language name you wish to use
DECLARE @language VARCHAR(50) = 'en';

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
SET NOCOUNT ON; 
 
DECLARE 
    @currentObjectName NVARCHAR(4000),
    @currentTranslatedValue NVARCHAR(4000);


-- Create ObjectTranslations table if not exists
IF NOT (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = 'ObjectTranslations'))
BEGIN
    CREATE TABLE [dbo].[ObjectTranslations](
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[ObjectType] [nvarchar](50) NOT NULL,
		[ObjectName] [nvarchar](4000) NOT NULL,
		[Language] [varchar](50) NOT NULL,
		[TranslatedValue] [nvarchar](4000) NOT NULL,
	 CONSTRAINT [PK_ObjectTranslations] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
END


-- Update existing values in ObjectTranslations table
BEGIN
	UPDATE OT SET
		TranslatedValue = AC.ButtonHeader
	FROM ObjectTranslations OT
	INNER JOIN AutomationCommands AC ON AC.Name = OT.ObjectName
	WHERE OT.ObjectType = 'AutomationCommand' AND OT.Language = @language;
END


-- Insert new values in ObjectTranslations table
BEGIN

	DECLARE curAC CURSOR 
		LOCAL FORWARD_ONLY STATIC READ_ONLY FOR 
		SELECT AC.Name, AC.ButtonHeader
		FROM AutomationCommands AC
		WHERE AC.Name NOT IN (
			SELECT OT.ObjectName
			FROM ObjectTranslations OT
			WHERE OT.ObjectType = 'AutomationCommand' AND AC.Name = OT.ObjectName AND OT.Language = @language
		) AND AC.ButtonHeader IS NOT NULL;
 
	OPEN curAC; 

	FETCH NEXT FROM curAC INTO @currentObjectName, @currentTranslatedValue; 
 
	WHILE (@@FETCH_STATUS = 0) 
	BEGIN 
		INSERT INTO ObjectTranslations
		(ObjectType, Language, ObjectName, TranslatedValue)
		VALUES
		('AutomationCommand', @language, @currentObjectName, @currentTranslatedValue);
		
		FETCH NEXT FROM curAC INTO @currentObjectName, @currentTranslatedValue; 
	END 
 
	CLOSE curAC; 
	DEALLOCATE curAC; 
END



SET ANSI_PADDING OFF;

Set Script:

-- Change below line to the name of your SambaPOS database
USE [SambaPOS5];

-- Change below line value to the language name you wish to use
DECLARE @language VARCHAR(50) = 'en';

-- Set AutomationCommands values from ObjectTranslations table
BEGIN
	UPDATE AC SET
		ButtonHeader = OT.TranslatedValue
	FROM AutomationCommands AC
	INNER JOIN ObjectTranslations OT ON AC.Name = OT.ObjectName
	WHERE OT.ObjectType = 'AutomationCommand' AND OT.Language = @language;
END

Usage Instructions

To Add / Update Current Values as a Language

In the Add / Update script, change this variable to your language (can be any value you like - I prefer using ISO_639-1 Language Codes) - e.g change 'en' to 'fr':

DECLARE @language VARCHAR(50) = 'en';

(the first time you run it, you likely will want to save the default English values before you start any translation work)

Run the Add / Update script. Values will be stored in the ObjectTranslations table.

To Set Values to a Different Stored Language

In the Set script, change this variable to the (already stored) language you want to change all values to - e.g 'fr':

DECLARE @language VARCHAR(50) = 'en';

Run the Set script. Values for the specified language stored in the ObjectTranslations table will replace the current Button Header values.

I’ve found the script will work when SambaPOS is running, but you will have to logout and login again to refresh the cache. To ensure you don’t get any unexpected issues, it is recommended you close SambaPOS before running the above scripts.

4 Likes