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

----------------------------------------------------------------------
mdbs99 · 2018-09-29 19:42

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  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i           
 //...        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: [Select]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

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 · 2018-10-01 00:48

Solved.

----------------------------------------------------------------------
lucamar · 2018-10-01 01:23

Quote from: mdbs99 on October 01, 2018, 02:48:55 amSolved.

Do you mind sharing how and why it happened?

----------------------------------------------------------------------
mdbs99 · 2018-10-01 02:18

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 · 2018-10-01 03:21

I see. Thanks!