ZEOS 7.2.5-rc, MSSQL, and Indy 10.6: Error converting characters into server's

Forum > Databases

(1/1)

mdbs99:
I'm using ZEOS to connect in MSSQL using the "new" sybdb.dll.

IIRC, MSSQL do not work with UTF-8 directly.
However, ZEOS has properties to deal with.

If I use those properties like this...
- ClientCodePage = ISO-8859-1
- ControlsCodePage = cCP_UTF8
- AutoEncodeStrings = true

...in theory, the component will do the conversion automatically.

However, if I use these values, I got wrong characters in varchar(max) type fields.
So, my first question is:
Which parameters should I use do connect on MSSQL?

Well, for my surprise, I've tried to use like that...
- ClientCodePage = UTF-8
- ControlsCodePage = cCP_UTF8
- AutoEncodeStrings = false

...and everything works.
I mean, words have accents - it's Portuguese - and all words looks Ok in my tests.

Going to production code:

I'm using Indy 10.6 to get emails using POP3.
I need to get those emails to persist into MSSQL. So, Indy fields like `msg.FromList.EMailAddresses`, `msg.Recipients.EMailAddresses`, `msg.Subject`, etc are stored Ok. My problem - I believe - is only about this: `TIdText(part).Body.Text`

Putting into a better context, here is part of the code:

--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} --- //... msg.Clear; fPOP.Retrieve(i, msg); email.from := msg.FromList.EMailAddresses; email.address := msg.Recipients.EMailAddresses; email.subject := msg.Subject; email.date.Init(msg.Date); email.msgid := msg.MsgId; for x := 0 to msg.MessageParts.Count-1 do begin part := msg.MessageParts[x]; if part.PartType = mptText then begin if IsHeaderMediaType(part.ContentType, 'text/html') then email.body := TIdText(part).Body.Text else if IsHeaderMediaType(part.ContentType, 'text/plain') then email.plain := TIdText(part).Body.Text; end; end; //...
The variable `email` is a record and the fields that I have problem are `email.body` and `email.plain` - both has the same type, which is RawUTF8 (from mORMot, which is `type AnsiString(CP_UTF8)`). The subject, address, etc also use the same type.

So, using the last configuration above - "all UTF-8" - I got this error:

--- Code: ---Project Postman raised exception class 'EZSQLException' with message:
DBError : [2402] : Error converting characters into server's character set. Some character(s) could not be converted

In file '..\..\src\plain\ZPlainDbLibDriver.pas' at line 723

--- End code ---

BUT, this occur ONLY because these two fields, coming from Indy POP3 component.
Then, I thought that those information aren't UTF-8. However, I can see the emails text pretty well in a grid, ShowMessage, saving into a file, etc - it looks like UTF-8!

Then, changing the properties to use the first configuration above - "ISO-8859-1" - I got NO error. BUT, all characters (subject, address, etc, included) are wrong, without accents, only "?"...

Finally, my environment is:
- Win 10 x86_64-win64
- FPC 3.1.1 39402
- Lazarus 1.9.0 r58537

mdbs99:
Solved.

lucamar:

--- Quote from: mdbs99 on October 01, 2018, 02:48:55 am ---Solved.

--- End quote ---

Do you mind sharing how and why it happened?

mdbs99:
The problem was a combination among target, sybdb.dll, libiconv.dll, connection properties, and encode.
As my program will run on Windows, I need to:
- use sybdb.dll without libiconv.dll static compilation -> that will work in both targets (32bit and 64bit)
- use connection properties like:
* codepage=ISO-8859-1
* AutoEncodeStrings=ON
* controls_cp=CP_UTF8
- for all string fields that I was passing for queries and SP's, I need to convert from UTF-8 to ANSI - using `UTF8ToString` function from mORMot.

Because this program will work on both platforms, I needed to try any combinations to realize that I needed to change the dll and convert manually the values - which is not elegant.

lucamar:
I see. Thanks!

Navigation

[0] Message Index