search

alexbrainman / odbc

odbc driver written in go
BSD 3-Clause "New" or "Revised" License
352 stars 140 forks source link

Fix null numbers causing later fields to be empty #56

Closed jechols closed 8 years ago

jechols commented 9 years ago

I don't know if this is actually valuable as-is, but we were getting errors when running against an Access database table where a numeric field was sometimes null. For some reason, on a record with a null number, EVERY field in the select statement that was after the null field would end up set to a blank value.

After a bit of debugging I found that the data was there, the call to the column's Value() method was happening, but the Scan call just wasn't setting things properly for any field that appeared after the null number.

jechols commented 9 years ago

This doesn't fix all situations - we're also running into problems with "short text" and datetime fields. Maybe this PR can help point to a solution, but it almost certainly shouldn't be merged as-is.

alexbrainman commented 9 years ago

@jechols sorry for late review (I didn't watch this repo - it was not set to be watched when I moved from google code). As to your problems, I would like to understand what them better. Would you mind creating an issue (or 2) with some code for me to reproduce. I don't have Access, but I will see what I can gather. Thank you.

Alex

jechols commented 9 years ago

This might take me a while to reproduce. I'll have to build a new Access database to test it out, I think. It shouldn't be hard to reproduce, but the project I was working on is considered "done" (I hacked up a local copy of the ODBC code), so I gotta justify a little extra time to my boss to get you a proper issue.

I don't know if it helps, but I ended up with two local commits you can look at: https://github.com/jechols/odbc/commit/95fae6a5d01ecde71027adddec2c55d83c5b08c1 and https://github.com/jechols/odbc/commit/047a6859113d7852e37f5aa8ef704f983fb511b7

alexbrainman commented 9 years ago

... the project I was working on is considered "done" (I hacked up a local copy of the ODBC code), so I gotta justify a little extra time to my boss to get you a proper issue.

Don't worry about it. I don't use Access myself. I am happy to wait until someone else hit that bug.

I don't know if it helps, but I ended up with two local commits you can look at: jechols@95fae6a and jechols@047a685

It seems like you're having problem with driver returning nil for driver.Value in Rows.Scan (or similar). But, if you look at sql.Scanner documentation, you can see it says - "nil - for NULL values". So I am not sure about your changes. If you had a test demonstrating your problem, I could investigate some more. But given what I have, I will stop here. Sorry.

Alex

jechols commented 8 years ago

I definitely need to get you a test, I'm just not sure when I'll have some extra time :(

The problem isn't that I got the empty value for NULL fields. I could handle that without any problems. The issue is that once a single field got nil, all subsequent fields would be set to their empty value.

e.g., suppose you had this SQL: select name, cost, description, quantity from products. If cost was NULL in the database, the returned description would be "" regardless of the data in the database, and quantity would be 0, again regardless of what was actually in the databse. I suspect something is short-circuiting once NULL is hit in the database, but I never delved that deep to figure it out. Once I hacked in a non-nil response (as in my commits above), the bad fields didn't cause remaining fields to get skipped.

Technically you could work around this in simple cases by reordering the fields, but that doesn't work so well when you are pulling 20+ fields from a legacy database, and several of those fields might trigger the issue.

alexbrainman commented 8 years ago

I definitely need to get you a test,

You do. :-)

I'm just not sure when I'll have some extra time :(

I am going to be away from my computers for a few weeks myself. So no rush.

The issue is that once a single field got nil, all subsequent fields would be set to their empty value.

It is impossible to speculate. For all we know it could be anything. It would be much simpler if you have a test case that I can reproduce here. Would it be enough, if you give my Access file with a sample table? I could write little Go test program that reads rows from it myself. I suppose I need Access driver installed, but that should be easy enough. Mind you, even driver might have a bug. So, I suspect, I need to know Access ODBC driver version you use too.

Alex

jechols commented 8 years ago

I'm closing this - our use case is no longer meaningful (the project has been replaced), so I definitely won't have time to provide a test. Thanks for the discussion all the same.