MSSQLSERVER plugin doesn't read/write unicode data in text column

Databases
1

Hello everyone,
It seems that the MSSQL plugin doesn’t read (via SQLSelect) or write (via InsertRecord, .Update) Unicode (Greek) characters. I haven’t tested it with English to tell you the truth, but I suppose that there it works.
This is only true if the column is of type text. Varchar and nVarChar works as is should!

Is it true or am I doing something wrong?

2

This should be under databases. Can someone change it please?
Thank you

3

Did you call DefineEncoding on the string you got from the database?

4

Yes Tim, I tried both DefineEncoding, ConvertEncoding and combining both.
I must note that varchar & nvarchar work perfectly and without DefineEncoding.
I also tried ntext with no success as well…

Any ideas?

5

I’m not using MS SQL but mySQL but perhaps the plug ins have the same issues. Here’s what helped me:

  1. When connecting to the database, do not specify names and charset directly but later by sending separate “set names=utf8” and set charset=utf8".

  2. Plus, make sure you enter the your data into the database through your application (not directly by using a separate interface). If you Encode / Decode all fields using xojo, then it works. I ran into this problem during early stages of development, when not all user interfaces were ready for use.

Again, this is about mySQL and the plugin, but perhaps the effects are similar.

6

Hi Andreas, and thank you for your input. I sometimes read data that exist already, so, they are already in the database.
I will try your approach even though at the moment I don’t know exactly how these commands apply to MSSQL.

However, what is strange is that the problem is only with the “text” type column. Varchar types work perfectly… I tend to think that this is a bug…

In my case the application will connect to either MSSQL Server, PostgreSQL or SQLite. In all cases but the text columns in MSSQL the respective plugins work fine. Also I note that the MSSQL plug-in has a bug that crashes if you perform a SELECT * FROM Table. However this doesn’t bother me as my classes build the fields for the select statement from metadata…

7

Fotis,

I would always suggest not to use “select * from” something. It is a piece of additional work, but by specifying the fields you actually want to read. Two reasons:

  1. Your application uses a certain table layout. This layout may be subject to change. Any change you make to the database may have an impact on your ‘select *’. You simply have those impacts under better control if your application specifies the fields you are reading.

  2. Response time over network. You often don’t need all the fields in your application, but if you always read them all, your connection between the application and the database will be flooded with unnecessary bytes, kilobytes, or even megabytes. I don’t care about bandwidth cost, but bandwidth also influences reponse time. Just think about a ‘select *’ that returns a number of rows, and each row includes an unnecessary picture or PDF.

8

Andreas I agree with you. Both reasons are valid, especially the first one, that is why I don’t really care about this bug.
However the Unicode characters of the text column not showing correctly is a problem.
I try to avoid text data type columns but sometimes I cannot avoid it. It seems I need to provide a different way to access this data.