ODBC Support for Informix Serial and BigInt Data Types

Question

I have Informix Dynamic Server 11.50 and Informix Client SDK 3.5 installed on my server. I am developing a .NET application to read data from Informix database using ODBC functions.

In the database, i have a table with columns of Serial and BigInt data types defined. My data retrieve function looks like this.

    Dim cmd As New Odbc.OdbcCommand
    Dim da As New Odbc.OdbcDataAdapter
    Dim ds As New DataSet
    Dim sb As New StringBuilder("")

    Try
        Using cn As New Odbc.OdbcConnection(ConfigurationSettings.AppSettings("connString"))
            cn.Open()

            sb.Append("SELECT * FROM InterfaceSP ")
            sb.Append("join Interface on InterfaceSP.InterfaceID = Interface.InterfaceID ")
            sb.Append("left join InterfaceSPAction on InterfaceSP.InterfaceSPID = InterfaceSPAction.InterfaceSPID and action_status = 'ACTV' ")
            sb.Append(" WHERE InterfaceSP.InterfaceID = ? ")
            sb.Append(" ORDER BY InterfaceSP.SPName")

            cmd.Connection = cn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = sb.ToString
            cmd.Parameters.AddWithValue("@InterfaceID", strInterfaceID)

            da.SelectCommand = cmd
            da.Fill(ds, "InterfaceSPList")

        End Using

        Return ds

    Catch ex As Exception
        Throw ex
    End Try

The end result dataset will be passed to a datagrid object. The problem is .NET throws an exception whenever the datagrid is loaded.

Unknown SQL type - -114.
[ArgumentException: Unknown SQL type - -114.]

I've tried to modify the columns with Serial and BigInt data types to Integer. And, everything works fine without modifying a single line of code.

I do need some advice how to overcome this problem as i need the Serial data type column as an incrementing id column. For BigInt data type column, may be we can change it to column with Integer data type instead.

Any advice is welcome.

Answer 1

.NET "knows" about a finite set of data types. The .NET data providers that retrieve data from various data sources must convert the various data types to something that .NET recognizes. The ODBC .NET data provider has a certain number of recognized types built into it. Anything outside that set can result in an error.

To get around this, I suspect you can cast the data type in the SQL SELECT statement that you run. I am not familiar with Informix, but a brief bit of googling seems to indicate that a cast is done with this format columnname::newtype. So you will need to replace the * in the SELECT statement with the specific fields and then cast the problematic columns to a recognized type such as integer: SerialCol::Integer. (I'm guessing at the type).

Answer 2

need to look at your CLIENT_locale and the DB_locale settings of your connection string