ADS error 7200: There is an error converting Unicode string to or from code page

[wriedmann]

Hello,

in one of my X# applications I'm interfacing a web service with my ADS and DBF based ERP system.
To read and write to ADS (Advantage Database Server) I'm not using the RDD, but the Advantage.Data.Provider.DLL that works very well, even using joins.
But sometimes the webservice returns me UTF8 strings, and using them in a SQL update statement returns the following error:

Code: Select all

Error 7200:  AQE Error:  State = HY000;   NativeError = 5211;  [iAnywhere Solutions][Advantage SQL][ASA] Error 5211:  There is an error converting Unicode string to or from code page string.  Some Unicode characters cannot be converted to code page characters.  AdsCommand query execution failed.

As it seems, the ADS Data Provider does not supports Unicode (and how should it do that when the underlying database is not Unicode capable).
Now I need to change the characters in that string to translate the not convertible characters to others that can be translated by ADS. I'm perfectly conscius that there will be lost some information, but I prefer to have incomplete information.
I have now tried this code:

Code: Select all

static method FixAnsi( cString as string ) as string
local cReturn as string
local oUnicode as System.Text.Encoding
local oAnsi1252 as System.Text.Encoding

oUnicode := System.Text.Encoding.Unicode
oAnsi1252 := System.Text.Encoding.GetEncoding( 1252 )

cReturn := oAnsi1252:GetString( System.Text.Encoding.Convert( oUnicode, oAnsi1252, oUnicode:GetBytes( cString ) ) )

return cReturn

I have tested this code in a small test application but it does not seem to work.

Has someone an idea what I could do?

Wolfgang

[Chris]

Hi Wolfgang,

As I told you in the PM, I am not familiar with using the data provider dll directly, but maybe you can show us the code how you call it and exactly where the error happens, it might give an idea?

[Karl-Heinz]

Hi Wolfgang,

>> But sometimes the webservice returns me UTF8 strings,

What happens if you use a UTF8 encoding ?

oUnicode := System.Text.Encoding.UTF8 // instead of .Unicode

regards
Karl-Heinz

[wriedmann]

Hi Chris,

I'm manually building the update statement like this

Code: Select all

cStmt := String.Format( "update todo set beschr = '{0}' where uniqueid = '{1}', cTitle, cTodoId ) 

Most of the times it works, but sometimes it fails.
I have now added my FixAnsi() call, and I`m waiting if that fixes it.

In XIDE, I was not able to build string that contain characters like the sigma character.

Wolfgang

[wriedmann]

Hi Karl-Heinz,

What happens if you use a UTF8 encoding ?

in my test code exactly the same - in my production code I have not added it until today morning, and I have to wait for an error to show up.
But there could be an error in my test code <g>.

Wolfgang

[Chris]

Hi Wolfgang,

When it fails, can you check what is the string you passed to it? maybe there's some specific character causing the problem?

Also is it failing on receiving the command from you, or is it failing when it tries to give you back the results? Maybe it happens with some specific records, with specific contents in them?

About XIDE, by default is uses the default codepage used by your machine. To change that, you must go to Project Properties, Advanced, and set the "encoding for source files" to Unicode or UTF8. Make sure you backup all your code first!!!

But maybe this is exactly the problem? You maybe are trying to use characters from different codepages, which are not included in the one used by your dbf?

[wriedmann]

Hi Chris,

the entire processing is done in a Windows service, so it is hard to find out what caused that error.Today I have added my error routine to find out the offending strings, but had no more errors since them (maybe my conversion works?).

What fails is the ADS command execution, and that returns only a numeric value (the number of changed records).

Of course the problem is that the webservice returns characters that cannot be represented by the ADS DBF, and what I needed is only a conversion function to remove/replace the offending characters (and I would not like to find out every single of them and write an manual replacing routine - it is better to use the functionality from the .NET framework).

Thank you for the notes about the XIDE character set - I will create a new project only for this as I don't like to "kill" all my sources (I have many, many hours of work in my XIDE projects!! again thanks for such a wonderful tool like XIDE!).

Wolfgang

[ic2]

Hello Wolfgang,

If you search on "advantage database server error 7200" there are quite some different causes for ADS error 7200. E.g. on

https://support.activedbsoft.com/hc/en- ... -dbf-files
https://stackoverflow.com/questions/783 ... sert-error

I have seen errors on ADS which pointed to a clue which had nothing to do with the actual error.

Second: If you log your manually built update strings and know when you have a faulty one, it's a good idea to paste that string in the SQL screen of the Data Architect. What it will do is point to the exact position in your query where it failed to execute. Then it's much easier to vary that string a bit until you discover the (real) cause.

Dick

[ohernandez@sistemas-liasa.com]

Hi wolfgang,
To log errors try sp_EnableQueryLogging(
TableName, Character, 255,
TruncateExistingData, Boolean,
LogOnlyUnoptimizedQueries, Boolean,
MinimumTimeBeforeLogging, Integer,
EncryptionPassword, Character, 20,
Options, Integer ),
with a 1 in options to log only errors.
Or you can try an AEP where use a regular expresion to exclude the characters with a bigger code than 128.

[wriedmann]

Hi all,
thank you for your answers!
Since I put in place my FixAnsi method, I had no more errors. So hopefully it is fixed.

Code: Select all

static method FixAnsi( cString as string ) as string
local cReturn as string
local oUnicode as System.Text.Encoding
local oAnsi1252 as System.Text.Encoding

oUnicode := System.Text.Encoding.Unicode
oAnsi1252 := System.Text.Encoding.GetEncoding( 1252 )

cReturn := oAnsi1252:GetString( System.Text.Encoding.Convert( oUnicode, oAnsi1252, oUnicode:GetBytes( cString ) ) )

return cReturn

If it is not fixed, I will let you know here.
Wolfgang