SQL Collation for multiple character sets

[Ubaidullah]

Hi Bruce,

What would be the correct approach to allow multiple character sets to be stored and displayed correctly in a Nettalk Web application?

I have a table where I need to be able to allow different languages like Spanish, Arabic in text fields. The text fields are of type nvarchar.

I insert some data into the table as below:

insert into Fruits (GUID, enFruitName, esFruitName, arFruitName)
values('126091FF-8301-42', N'Banana',N'el plátano, la banana', N'موز')

After the insert, it displays correctly in Management Studio as below:


But in my Nettalk web application, the fields display incorrectly as below:


Then I edit the data in my web application as below:


But the data still shows incorrectly now both in the web application and Management Studio:


In WebServer settings,
Charset is set to 'utf-8'
Store Data As is set to utf-8

What am I doing wrong and how can I store and display different character sets correctly?

Thanks
&
Regards,
Ubaidullah Nubar.

[Bruce]

>> Charset is set to 'utf-8'
>> Store Data As is set to utf-8

These would be the two that matter the most. Indeed should be all that is necessary.

So I guess the next possibility is that the clarion file driver is changing something when reading or writing the record? I'm not the SQL expert so others will need to help you there.

tip: you can inspect the value you are _saving_ by doing
 str.setvalue(fru:esFruitName)
 str.PeekRam(st:hex)

then the actual bytes are visible in debugview.

after the record is saved, load the record, and so the same to see if th value is being changed "in storage".

Cheers
Bruce

[Ubaidullah]

Bruce, thanks for the reply.

I did some further digging and found something curious.

Tested on SQL Server 2019/Win and it seems to exhibit better behaviour (previous test was on SQL Server 2017/Linux).

If I do an insert as above, it doesn't display correctly in the NetWeb app.

But, if I copy the text from Management Studio and paste into the NetWebForm, it displays correctly in the NetWeb app now.

So, I can see it as: el plátano, la banana

But, this is the curious thing:

After pasting as above, Management Studio now shows the text like this:
el plátano, la banana
instead of this:
el plátano, la banana

I am not too concerned about how it displays in Management Studio at this point.

Now, I need to figure out how to insert using T-SQL so the data displays correctly.

Obviously, inserting N'el plátano' is not working.

Does someone know how can I insert 'el plátano' and get 'el plátano' in Management Studio so it shows up correctly in the NetWeb app?

Thanks
&
Regards,
Ubaidullah Nubar.

[ntnewbies]

Hi Ubaidullah,
Not sure if it works for you. Try this and see:

you need to put a line of code before and after the field that requires support of multiple char sets. e.g:

routine -> fruitname field -> 4 value routine

put this embed:
 p_web.site.HtmlCharset = 'DB2312'


then another embed point:

routine -> fruitname field -> 5 Inside Value Routine

put this embed:
 p_web.site.HtmlCharset = 'utf-8' 


regards,
jason
nt11.50
c11

[Bruce]

Hi Jason,

It's working in NetTalk with the correct charset (utf-8) and Storeas (utf-8) settings.
The problem Ubadullah is now having is with MsSql Enterprise Manager, not NetTalk.

cheers
Bruce