1

As a follow up to this question, when I use that code and one of the results has a NULL value for a DateTime column, I get the following error:

ERROR [22007] [Cache ODBC][State : 22007][Native Code 22007] [E:!work\projects\dotnet_feed\dotnetFeed] Invalid datetime format

Here is the full code, adapted from the question above:

   public void doQuery(string id, string sql, string ds)
    {
        string connectionString = "DSN=" + ds + ";";
        string queryString = sql;

        payload result = new payload();
        var resultList = new List<Dictionary<string, dynamic>>();
        result.id = id;
        result.timestmap = DateTime.Now;

        using (OdbcConnection connection = new OdbcConnection(connectionString))
        {
            OdbcCommand command = new OdbcCommand(queryString, connection);

            try
            {
                connection.Open();
                OdbcDataReader reader = command.ExecuteReader();
                if (reader.HasRows) //not working when no results.
                {
                    while (reader.Read())
                    {
                        var t = new Dictionary<string, dynamic>();
                        for (var i = 0; i < reader.FieldCount; i++)
                        {
                            t[reader.GetName(i)] = reader[i];
                        }
                        resultList.Add(t);
                    }
                }
                else
                {
                    log("NO RESULTS");
                }
                reader.Close();
                result.data = resultList;
                string output = JsonConvert.SerializeObject(result);
            }
            catch (Exception ex)
            {
                log(ex.Message);
            }
        }
    }

Again, if the result set has no nulls in a datetime column it works fine. It also works fine with nulls in other columns. How can I fix this?

ex.ToString()

"System.Data.Odbc.OdbcException (0x80131937): ERROR [22007] [Cache ODBC][State : 22007][Native Code 22007]\r\n[E:\!work\projects\dotnet_feed\dotnetFeed\]\r\nInvalid datetime format\r\n at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)\r\n at System.Data.Odbc.OdbcDataReader.GetData(Int32 i, SQL_C sqlctype, Int32 cb, Int32& cbLengthOrIndicator)\r\n at System.Data.Odbc.OdbcDataReader.GetData(Int32 i, SQL_C sqlctype)\r\n
at System.Data.Odbc.OdbcDataReader.internalGetDateTime(Int32 i)\r\n
at System.Data.Odbc.OdbcDataReader.GetValue(Int32 i, TypeMap typemap)\r\n at System.Data.Odbc.OdbcDataReader.GetValue(Int32 i)\r\n at System.Data.Odbc.OdbcDataReader.get_Item(Int32 i)\r\n at dotnet_feed.Form1.doQuery(queryObject qo) in E:\!work\projects\dotnet_feed\dotnetFeed\dotnet_feed\Form1.cs:line 92" string

Line 92: t[reader.GetName(i)] = reader[i];

Community
  • 1
  • 1
  • I'm guessing it's because the variable is `dynamic` and the first result is a datetime, so that's what it expects from then on? I don't understand why `null` wouldn't be accepted though. Also, keep in mind this is a SQL interface from a Caché backend. I've had type difficulties before. –  Sep 09 '16 at 13:34
  • I am guessing you need to check [`OdbcDataReader.IsDBNull(i)`](https://msdn.microsoft.com/en-us/library/system.data.odbc.odbcdatareader.isdbnull(v=vs.110).aspx), because the reader is throwing an exception rather than returning `DBNull.Value` for a missing `DateTime`. I.e. `t[reader.GetName(i)] = (reader.IsDBNull(i) ? null : reader[i]);` – dbc Sep 09 '16 at 18:17
  • Also, by converting to a temporary `Dictionary` you are losing the order of your records, since `Dictionary` is unordered. If you need to preserve the record order, you could serialize your `IDataReader` directly to JSON using `DataReaderConverter` from [here](https://stackoverflow.com/questions/33835729/json-net-serialize-directly-from-oledbconnection/33837306#33837306). – dbc Sep 09 '16 at 18:18

0 Answers0