DbNull values in a database cause an exception when fetching

[jmp75]

Hi,

Reproduction information can be found at http://stackoverflow.com/a/21456750/2752565

I'll fork and may see if I can figure out a fix, but I am not very knowledgeable with DBs, so don't count on it.

Nice package; I have some things to learn from it. Keep up the good work,

Cheers

[agstudy]

Thanks for your feedback. For some reason github don't notify me about issues. I accidentally find this (9 days after). For the DBNULL issue I think it is in relation with nullable values that rClr don't manage yet.

[przemyslawlis]

A fix for this would be great. Congrats for the package!

[agstudy]

@przemyslawlis thanks. any solution is welcome also.

[przemyslawlis]

I'm currently using a lame workaround for this. Instead of getting exception, I get default type values when NULL value is in DB by changing Fetch method in SqlDataHelper.cs to:

public int Fetch(int capacity)
        {
            int cnt = 0;
            if (_reader == null) return -1;
            setCapacity(capacity);
            while (_reader.Read())
            {
                // fetch rows and store data by column
                for (int i = 0; i < _reader.FieldCount; i++)
                {
                    if (_reader.GetValue(i) == DBNull.Value)
                    {
                        _resultSet[_cnames[i]].SetValue(null, cnt);
                    }
                    else
                    {
                        _resultSet[_cnames[i]].SetValue(_reader.GetValue(i), cnt);
                    }
                }
                cnt += 1;
                if (cnt >= capacity) return cnt;
            }
            // trim array 
            if (cnt < capacity)
                for (int i = 0; i < _reader.FieldCount; i++)
                    _resultSet[_cnames[i]] = TrimArray(_resultSet[_cnames[i]], cnt, i);
            // set nrows
            _nrows = cnt;
            return cnt;
        }

Might be useful for someone.

[agstudy]

@przemyslawlis thanks. I fixed the bug. It is nearly the same idea but I have to differentiate the case of character and numeric value. For the latter I return a Single.NaN which is intereprted as a missing value in R.

[agstudy]

thank's for subemtting this. I confirm the bug. I create a new issue https://github.com/agstudy/rsqlserver/issues/6 since this one is closed.

[hs3180]

these tricks just work in float type columns. I have encountered type incompatible for NULL value in Int32 columns.