Language - Translation - SQL

[Steve Workings]

I've just started into the language internationalization, but can't figure out how to handle Korean (and probably Japanese and Chinese - also on my list but not there yet).

This may be a SQL question more than anything else, but I can't find the answer.

I'm using the Text_Dictionary table in MS SQL automatically generated by A5. This means that the DATA field is varchar(max). I've offered a couple of my administrators a grid to be used to supply the translations for the DATA field, but all the grid field returns is "??" when we try to insert a Korean-language string. I took it a step further, and now think that the problem may not (necessarily) be in my A5 configuration, but in MS SQL itself. For instance, in MSSMS, I try this:

update text_dictionary set data = '동물' where [key] = 'animals' and language = 'korean'

But the field still just contains "??" (without the quote marks).

What to do about this?

[Davidk]

Re: Language - Translation - SQL

When you create your SQL table, ensure that you include CHARACTER SET utf8 for the field. This will ensure other language characters are correct.

I used utf8 when creating tables for my Alpha / Google Translate Utility : http://youtu.be/BFD1t03ffms

I used MySQL, but I believe it's about the same for MSSQL.

It's been a while since I did this but... for example...

CREATE TABLE `tbltextdictionary` (
  `KEY` varchar(100) NOT NULL,
  `LANGUAGE` varchar(40) NOT NULL,
  `DATA` longtext CHARACTER SET utf8,
  PRIMARY KEY (`KEY`,`LANGUAGE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

[Steve Workings]

Re: Language - Translation - SQL

Nope, not that simple David.

1. The MS SQL DATA field needs to be set to Nvarchar(max), not the varchar(max) that Alpha created.

2. The syntax in MSSMS to update a record is:

update text_dictionary set data = [B][COLOR="#FF0000"]N[/COLOR][/B]'동물' where [key] = 'animals' and language = 'korean'

I can do this without flaw in MSSMS. Still struggling to do this with a grid so I can hand off the translation job to the professionals. Something within the grid is translating the translation and I'm getting garbage back.

I've taken over the SQL with the onBeforeSQLCommandExecute event, and while debug shows I've got it right, I'm still missing something that's interfering:

if "Korean" $ e.argsXML
	e.SQLCommandWasHandled = .t.
	e.cn.PortableSQLEnabled = .f.
	vNewData = extract_string(e.argsXML, "<Data Type=\"C\">", "</Data>",1)
	sql = "UPDATE Text_Dictionary SET DATA = N'" + vNewData + "' WHERE ([KEY] = :PKValue_1 AND [LANGUAGE] = :PKValue_2)"
	dim args as sql::Arguments
	args.xml = e.argsXML
	e.cn.execute(sql, args)
end if

[Steve Workings]

Re: Language - Translation - SQL

Hmm, just can't get it.

As noted I've taken over the SQL. Even trying a connection string forced to UTF-8. And everything in the debug window suggests it's doing everything I want. But when I enter this:

사건

I get this stored and returned instead:

사건

Anyone have an idea?

[Davidk]

Re: Language - Translation - SQL

Can you post of a sample grid and the table you're using with a couple of rows of data?

[Clipper87]

Re: Language - Translation - SQL

Steve,

What do you mean with "Still struggling to do this with a grid" ? If I just copy "동물" from your message & paste that into a grid & submit then it does show ok also in MSSMS. In other words what exactly are you doing ?