DBError : [2403] : Some character(s) could not be converted into client's character set

[nomorelogix]

Hi all,
I'm new in using zeoslib and I need some help.

I use ZeosLib with Lazarus on linux and my need is to connect to MsSql (2019) so I've FreeTds installed.
Some records contains strange chars as an example "ETIC.TE ADES.A4 ANG ROTONDO Ø".

My code is very simple:

var
   statement:IZStatement;
   s: string;
   ...
begin
   ...
   s:='select Id, Description from Table1 where ....';
   rs := statement.ExecuteQuery(s);
   ...
end

So, when the WHERE clausole detect these strange values, i get an exception in: rs := statement.ExecuteQuery(s);
DBError : [2403] : Some character(s) could not be converted into client's character set. Unconverted bytes were changed to question marks ('?')

The 1st help I need is on how to fix this conversion error.

The 2nd is: why ExecuteQuery raise an exception?
I expected an exception when reading columns but not in the execution.


Thanks in advance
nomorelogix

[marsupilami]

Please make sure to use UTF8 as the connection character set. This should fix the conversion error. If you use the new UTF8 capabilities of SQL Server 2019, you might want to make sure, you use the latest release candidate of FreeTDS because this one has support for these columns.

Regarding ExecuteQuery and exceptions: Why do you expect ExecuteQuery to not raise exceptions? If your SQL is faulty or other things happen, it needs a way to tell you that things go wrong. Exceptions are the way to do that on Zeos

[nomorelogix]

thank you for reply

I think I need investigate on UTF8 in 2019
Actually I used this code but don't works

Code: Select all

  SL := TStringList.Create;
  SL.Add('ClientCodepage=UTF8');
  SL.Add('ControlsCodePage=cCP_UTF8');
  SL.Add('AutoEncodeStrings=OFF');
  FConnection      := DriverManager.GetConnectionWithParams(FConnectionString, SL);
  FreeAndNil(SL);

Regarding exceptions:
I surely agree with an exception in a faulty SQL.
But, in some cases, why not to process "not-wrong-records" while skipping the "wrong-errors"?

Thank you again
nomorelogix

PS: sorry for my english

[marsupilami]

Hello,

Actually I used this code but don't works

Code: Select all

  SL := TStringList.Create;
  SL.Add('ClientCodepage=UTF8');
  SL.Add('ControlsCodePage=cCP_UTF8');
  SL.Add('AutoEncodeStrings=OFF');
  FConnection      := DriverManager.GetConnectionWithParams(FConnectionString, SL);
  FreeAndNil(SL);

You don't tell the driver manager which protocol to use and to which server to connect to. Please read up on ZDBC URLs in the Zeos Documentation collection. Although the information is old, it still is mostly correct.

Regarding exceptions:
I surely agree with an exception in a faulty SQL.
But, in some cases, why not to process "not-wrong-records" while skipping the "wrong-errors"?

There are several reasons. One of them is that we try to protect data integrity. If a client library tells us that there is an error, we raise it.

Best regards,

Jan