Queries problems using IBM Informix ODBC Driver in C#

Question

I ask my question after searchs many times on web about my problem.

To my job, I need to do queries in C# on a Informix database using IBM Informix ODBC Driver (v 3.70). Engine's database return the following message "ERROR [HY000] Invalid byte in codeset conversion input" when I want to extract the character Ø (diameter).

I think to a DB_LOCALE or CLIENT_LOCALE mismatch but I'm not sure.

locales settings: - DB_LOCALE : en_US.1252 - LIENT_LOCALE : en_US.1252

Thank's in advance for your help.

jsagrera jsagrera · Accepted Answer · 2017-07-27T10:33:52

Double check that the database is 1252, and also what you have in the table. Maybe the code for that particular character within the database is not really valid in CP1252 In 1252, the O-slash correspond to:

Ø 0xd8 Latin Capital Letter O With Stroke
ø 0xf8 Latin Small Letter O with Stroke

A quick test with a 1252 database:

D:\infx\ids12>set DB_LOCALE=en_US.1252
D:\infx\ids12>set CLIENT_LOCALE=en_US.1252
D:\infx\ids12>dbaccess enus1252 -
Database selected.
> drop table t1;
Table dropped.
> create table t1(c1 char(10));
Table created.
> load from o.txt insert into t1;
1 row(s) loaded.
>
Database closed.

D:\infx\ids12>od -x o.txt
0000000000      F8D8
0000000002

Using oncheck to see what's really in the page

D:\infx\ids12>oncheck -pp enus1252:t1 256
addr             stamp    chksum nslots flag type         frptr frcnt next     p
rev
1:16444          725726638 ded2   1      1    DATA         34    4054  0
0
        slot ptr   len   flg
        1    24    10    0
slot   1:
    0: d8 f8 20 20 20 20 20 20 20 20                     Xx        ......

D:\infx\ids12>

And now from C#

-----
D:\Infx\work\cs>cat s.cs
using System;
using System.IO;
using System.Data;
using System.Text;
using IBM.Data.Informix;
using System.Windows.Forms;

class sample {
    static void Main(string[] args) {

try
{
     using (IfxConnection conn = new IfxConnection("Server=ids1210;Database=enus1252;uid=informix;pwd=ximrofni;DB_LOCALE=en_US.1252"))
     {
          conn.Open();
          using (IfxCommand cmd = conn.CreateCommand())
          {
              cmd.CommandText = "select * from t1";
              IfxDataReader rd = cmd.ExecuteReader();
              rd.Read();
              do
              {
                   if (rd.HasRows)
                        Console.WriteLine("c1= {0}", rd[0]);
              } while (rd.Read());
          }
      }
  }
  catch (IfxException exc)
  {
       Console.WriteLine("Update: {0}", exc.Message);
       foreach (IfxError error in exc.Errors)
           Console.WriteLine("Error: ({1}): {0}", error.Message,  error.NativeError);
  }
}
}

D:\Infx\work\cs>csc.exe /R:D:\infx\csdk410tc8w2\bin\netf20\IBM.Data.Informix.dll /nologo s.cs /platform:x86

Both characteres are returned as they should:

D:\Infx\work\cs>s
c1= Øø

D:\Infx\work\cs>

Maybe the data in the table is not really from 1252. Do a unload or a dbexport with CLIENT_LOCALE=DB_LOCALE (so there is no GLS conversion done) and check if the Ø is 0xd8 or 0xF8 (upper/lower) if is not, it means that 'Ø' was not inserted using the correct locales.

edit:

If you have a 0x9D in the table, you may be using 850 and not 1252 as your client codeset. In 850 (which in some windows is the default codeset for a cmd) 'Ø' is 0x9D and not 0xD8

D:\Infx>chcp 1252
Active code page: 1252

D:\Infx>echo Ø | od -x
0000000000      20D8    0A0D
0000000004

D:\Infx>chcp 850
Active code page: 850

D:\Infx>echo Ø | od -x
0000000000      209D    0A0D
0000000004

D:\Infx>

If you have that in the table:

D:\infx\ids12>dbaccess enus1252 -
Database selected.
> truncate t1;
Table truncated.
> insert into t1 values ('Ø');
1 row(s) inserted.
>
Database closed.
D:\infx\ids12>oncheck -pp enus1252:t1 256
addr             stamp    chksum nslots flag type         frptr frcnt next     p
rev
1:16444          725727918 d1d2   1      1    DATA         34    4054  0
0
    slot ptr   len   flg
    1    24    10    0
slot   1:
0: 9d 20 20 20 20 20 20 20 20 20                     .         ......

D:\infx\ids12>

C# will give you an error as there is conversion from 0x9D (0x9D should not be used in 1252)

D:\Infx\work\cs>s
Update: ERROR [HY000] [Informix .NET provider]Invalid byte in codeset conversion input.
Error: (21000): [Informix .NET provider]Invalid byte in codeset conversion input.

D:\Infx\work\cs>

Queries problems using IBM Informix ODBC Driver in C#

1 Answers