none
Encoding problem when migrating a legacy tool to a new Windows version with OleDB RRS feed

  • Question

  • We have an odd problem after migrating an old application (>15 yo) written in c# to a new Windows Server. 
    The application uses OleDB to connect to the database which is an Informix database. This database has a table with texts in multiple languages. The application running in a Windows 2003 server works just fine, however in the new Windows 2016 it raises the error:
    "The data value could not be converted for reasons other than sign mismatch or data overflow. For example, the data was corrupted in the data store but the row was still retrievable."

    After some investigation we have found the problem to be in a string that has some unicode characters in it.

    This is the part of the text that is generating the problem (only part of the text to illustrate the problem:

    "17"-Leichtmetallräder ...... Ziffern - Schaltknauf"

    This is a German text and seems ok, the problem is  actually with the "-". Looking at the db record in Hex, the first "-" is coded as "3F", however the second dash is coded as "C296", which corresponds to U+0096 (a dash in unicode)

    The settings for the DB is en_US.819 (which corresponds to ISO-8859-1 to support for all languages that need to be supported).

    Now, the problem is that when running the program in Windows 2003 the result is written in a file correctly like:

    "17"-Leichtmetallräder ...... Ziffern - Schaltknauf"

    However in Windows 2016 the exception above is raised and nothing gets written.

    I worked on some code changes, the first thing I did was to change OleDB for Odbc connection and the exception dissapeared, however the text in the output is incorrect:

    "17"-Leichtmetallräder ...... Ziffern ? Schaltknauf"

    Notice how the same code with odbc connection is unable to understand the unicode dash.

    This is the OleDB code that works in Windows 2003:
    <pre>
    OleDbConnection ConnOleDbIDD = new OleDbConnection("Provider=Ifxoledbc.2;Data Source=db;INFORMIXSERVER=localhost;IFMX_UNDOC_B168163=1;");
    string sConnectTemplateDB = "Data Source=SQLServerDB;Initial Catalog=DB1; Connect Timeout = 28800; Integrated Security=True";

    ConnOleDbIDD.Open();

    sExportSQL = "SELECT * From MyTable";

    OleDbCommand cmdIDD = new OleDbCommand(sExportSQL, ConnOleDbIDD);
    cmdIDD.CommandTimeout = 28800;

    SqlDataAdapter da;
    ConnSchemaIDD = new SqlConnection (sConnectTemplateDB);
    ConnSchemaIDD.Open();
    SqlCommand cmdSQLServerTemplate = new SqlCommand(sExportSQL.Replace("TRIM","LTRIM"), ConnSchemaIDD);
    cmdSQLServerTemplate.CommandTimeout = 28800;
    da = new SqlDataAdapter(cmdSQLServerTemplate);


    OleDbDataReader dr;
    DataSet ds = new DataSet();
    da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
    da.Fill(ds, sSourceTable);
    DataTable dt = ds.Tables[sSourceTable];
    dr = cmdIDD.ExecuteReader()

    iEnCodingFrom = 1252;
    iEnCodingTo = 1252;

    while (dr.Read())
    {
    sValue = "";
    sCurrentValue = "";
    bDelimiterPosition = false;
    foreach (DataColumn cCol in dt.Columns)
    {

    object oval = dr.GetValue(dr.GetOrdinal(cCol.ColumnName));
    string val = Convert.ToString(dr[cCol.ColumnName]);
    sCurrentValue = System.Text.Encoding.GetEncoding(iEnCodingTo).GetString(System.Text.Encoding.Convert(System.Text.Encoding.GetEncoding(iEnCodingFrom), System.Text.Encoding.GetEncoding(iEnCodingTo), System.Text.Encoding.GetEncoding(iEnCodingFrom).GetBytes(val)));

    if (bDelimiterPosition == true)
    {
    sValue = sValue + sDelimiter + sCurrentValue.Trim();
    }
    else
    {
    sValue = sValue + sCurrentValue.Trim();
    }

    bDelimiterPosition = true;

    }

    w.WriteLine(sValue);
    w.Flush();

    }
    dr.Close();
    </pre>
    Assume for this example that "Mytable" has 2 columns, the first is an integer ID and the second is a char(3100).

    As you see the code does some weird things like getting the column description from an schema of the table in a SQLServer database, and a conversion of the db output from CP1252 to CP1252. I am not sure why it was coded that way.
    My workaround for this problem has been doing these changes to the code (using odbc connection instead of oledb):
    <pre>
    iEnCodingFrom = 28591;
    ...
       sCurrentValue = Encoding.GetEncoding(iEnCodingTo).GetString(Encoding.GetEncoding(iEnCodingFrom).GetBytes(val.ToCharArray()));
    ...

    </pre>

    So changing the connection to an ODBC conection to the informix DB which prevents the exception to be raised, and doing a conversion from codepage 28591 (8859-1) to 1252 (CP1252) produces in Windows 2016 the same result as the old code in Windows 2013.

    So I have a workaround and can use it, however I would like to understand why this happens, why can't I keep using OleDB and if there is a way I can make it work in a new Windows environment (fails also in windows 10) without having to change the code.

    Any help would be greatly appreciated.

    Thank you
    Monday, September 30, 2019 10:39 AM

Answers

  • Hi En-coder, 

    Thank you for posting here.

    I note that Informix is a third-party product, so we cannot  guarantee that OLEDB will be fully applicable to this database.

    Besides, I find that Informix database provide an example of database connection. You can try to use it.

    IfxConnection example

    Hope it can help you.

    Note: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; Therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by En-coder Wednesday, October 2, 2019 11:55 AM
    Tuesday, October 1, 2019 7:54 AM
    Moderator

All replies

  • Hi En-coder, 

    Thank you for posting here.

    I note that Informix is a third-party product, so we cannot  guarantee that OLEDB will be fully applicable to this database.

    Besides, I find that Informix database provide an example of database connection. You can try to use it.

    IfxConnection example

    Hope it can help you.

    Note: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; Therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by En-coder Wednesday, October 2, 2019 11:55 AM
    Tuesday, October 1, 2019 7:54 AM
    Moderator
  • Thank you for your reply,

    I found a partial answer to my probem here in an external link, and it is related to a change in the informix driver to support unicode from version 2.8.

    However, given it is an informix driver issue, I still don't understand why oledb crashes while odbc connection doesn't, and I believe there is indeed an oledb problem somewhere. In any case, for me the explanation that I found is enough and this question can me marked as complete. 

    Thanks again

    Wednesday, October 2, 2019 11:55 AM