DB2 C# OdbcDataReadder index out of range on varchar columns

Question

I'm working on data access in a Unity 3D project connecting to a local DB2 database. I have a small test project that was working fine when the columns were CHAR, but after changing them to VARCHAR I now get index out of range exceptions. Even more interestingly this only seems to happen when running via the Unity 3D editor as my EXE build doesn't have this issue.

The project is pretty basic and I can't figure out why it seems to just happen with VARCHAR columns. We have System.Data.dll and System.EnterpriseServices. Unity is using .NET 2.0 so I wonder if the issue lies there, but I can find nothing to support it.

Code:

 public void MakeQuery(string connectionString, string query)
{
    bool failed = false;
    string errorMessage = "";
    if (string.IsNullOrEmpty(connectionString))
    {
        errorMessage = "Connection string cannot be empty\n";
        failed = true;
    }

    if (string.IsNullOrEmpty(query))
    {
        errorMessage += "Query cannot be empty";
        failed = true;
    }

    if (failed)
    {
        ShowResults(errorMessage);
        Debug.LogError(errorMessage);
        return;
    }
    try
    {
        OdbcConnection odbcCon = new OdbcConnection(connectionString);
        odbcCon.Open();

        OdbcCommand command = new OdbcCommand(query, odbcCon);
        StringBuilder sb = new StringBuilder();
        using (OdbcDataReader reader = command.ExecuteReader())
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                Debug.LogFormat("i: {0}  field: {1}", i, reader.GetName(i));
                sb.Append(reader.GetName(i));
                if (i < reader.FieldCount - 1)
                    sb.Append(",");
            }

            sb.AppendLine();

            while (reader.Read())
            {
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    try
                    {
                        sb.Append(reader.GetString(i).Trim()); // Index out of bounds on VARCHAR columns only...
                        if (i < reader.FieldCount - 1)
                            sb.Append(",");
                    }
                    catch (Exception e)
                    {
                        Debug.LogErrorFormat("i: {0}  exception: {1}", i, e.Message);
                    }

                }
                sb.AppendLine();

            }

            Debug.Log(sb.ToString());
            ShowResults(sb.ToString());
        }
        odbcCon.Close();
    }
    catch (Exception e)
    {
        Debug.LogException(e);
        ShowResults(
            string.Format(
                "EXCEPTION: {0}\nconnectionString: {1}\nquery: {2}", 
                e.Message,
                connectionString, 
                query)
            );

    }
}

Table structure:

 NAME       COLTYPE     LENGTH
 --------   --------    ------
 PID        INTEGER          4
 X          REAL             4
 Y          REAL             4
 NAME       VARCHAR        256
 ON_ENTER   VARCHAR        256
 TYPE       VARCHAR        256
 VIEW       VARCHAR        256

Github project: https://github.com/Naphier/Unity-ODBC-to-IBM-DB2-Example Release v1.0 should suffice, but you can explore the project there if you like.

Any insights are appreciated!

Answer 1

I had similar problems with DB2 some time ago, but i was using OLEDB and not ODBC, it turned-out that the System.Data is not reading all the properties correctly for a given field, this problem also exists when connecting to Oracle, for me the solution was to use the GetValue and do the casting manually... at a later stage is start using the drivers of the database suppliers what solved my initial problem