Row as DatabaseRow in RowSet rounds doubles to 3 significant figures

I have both iOS and desktop apps which read and write geographic coordinates to a SQLite database. When I read the coordinates using:

Var rs as RowSet
sql = "SELECT * FROM Locations"
rs = myDB.SelectSQL(sql)
While Not rs.AfterLastRow
latitude = rs.column("latitude").DoubleValue
longitude = rs.column("longitude").DoubleValue
rs.MoveToNextRow
Wend

latitude and longitude are read correctly to many significant figures. For mapping applications, one needs at least 5 or 6 significant figures to the right of the decimal point: 43.123456, -70.123456

If instead, I read the database using the following construct:

Var rs as RowSet
sql = "SELECT * FROM Locations"
rs = myDB.SelectSQL(sql)
for Each row as DatabaseRow in rs
latitude = row.column("latitude").DoubleValue
longitude = row.column("longitude").DoubleValue
next

the values of latitude and longitude are rounded to three significant figures: 43.123, -70.123. This results in an mislocation error of about 10+ meters. As I am using a 1 m digital elevation model (DEM), this is quite a significant error.

Is this a known issue? I’ve just spent the better part of a day getting rid of all the “for each row as databaseRow…” loops in my apps. I’ve known that this construct is slower than the “while not rs.afterlastrow…” way of reading the database, but this is downright inaccurate. There are many possible applications that require more than six significant figures. Hope no one is designing rocket ships with the “for each row as databaseRow in rs” loop!

1 Like

This is a serious issue. Have you raised a f/b case yet?

Here’s some code that demonstrates the issue.

db = New SQLiteDatabase
db.Connect
db.ExecuteSQL("CREATE TABLE test (id INT NOT NULL PRIMARY KEY, lat DOUBLE, lon DOUBLE);")
db.ExecuteSQL("INSERT INTO test (id, lat, lon) VALUES (1, 3.145678909, 41.22559977);")
Var Rows As RowSet = db.SelectSQL("SELECT * FROM test;")

For Each Row As DatabaseRow In Rows
  Var rowlat As Double = Row.Column("lat").DoubleValue
  Var rowslat As Double = Rows.Column("lat").DoubleValue
  Var rowlon As Double = Row.Column("lon").DoubleValue
  Var rowslon As Double = Rows.Column("lon").DoubleValue
  Break
Next Row

The difference is easy to see in the debugger.

image

2 Likes

That is actually 5 significant figures - and 3 decimal places.

seems DatabaseRow use currency!?
especially the database framework need more care and better tests before release.

this definitely needs a feedback entry…

1 Like

Yes, I was sloppy in my title, though in the text of my message I referred to needing 5 significant figures to the right of the decimal point.

Thanks, Wayne! I was going to submit a feedback case once I’d put together a demo project, but you just wrote the project for me!

Feedback will be submitted this morning.

Lots of silly and deadly bugs (as this one) have been found in a “DatabaseRow” iterating a “RowSet” since 2020, it seems a never ending story. This one MUST BE fixed in a dot release soon.

Again, tolerable bugs, for a few weeks, are cosmetic bugs, intolerable bugs, needing immediate intervention, are ANYTHING that causes data corruption (as this one). So, no I/O handling errors, Database handling errors, String handling errors, Math errors, or similar cases corrupting user data, many times silently, can exist in a release, and the vendor must issue any number of dot releases to fix them all as soon as discovered.

3 Likes

Feedback submitted: feedback://showreport?report_id=68363

I agree: data corruption can have deadly consequences and needs an immediate fix. As a scientist and not a professional programmer, I tend to assume that my stupid programmer errors (which are many) are to blame, but I couldn’t make this one go away and a 10 m mislocation error drives me crazy!

1 Like

I wonder if this is a Mac/iOS problem since I don’t see the problem on Windows 11 with Xojo 2021r3.1.

Did you provide a sample code (privately) to the case?

Almost always I see requests to supply a sample project to start reviewing the case, so is better if you did provide one. If not, can you add it? Thanks.

Just added a demo project to the feedback request

1 Like

I found this using 2022r1.0. I just tried 2021r3.1 and it does NOT occur (both are Mac versions). So it appears to be a “feature” of 2022r1.0…

2 Likes

Well that’s even more reason it should be included in the next dot release.

4 Likes