Xojo data corruption (Database Framework)

[Rick.A]

Reported 2 days ago:
67133 - Severe data corruption tied to values processing wrongly under some locales
feedback://showreport?report_id=67133

[npalardy]

basically you need to get EVERY person who uses locales affected by this to put this as their TOP priority and also subscribe to the case to get it fixed

0 points = no priority
only “1 reporter” = no priority (see Web 2.0 date picker on WebDialog problem - #5 by Jay_Menna - Web - Xojo Programming Forum)

FYI use the HTTP links in here

http://feedback.xojo.com/case/67133

Click “subscribe” on an open case to add your “me too” to any case

[Rick.A]

This is a severe case, possible data loss pointed out. It should not be a case of “subscribers” begging, but for the triage team set an urgent priority. If they don’t want to work as anyone should be, with the QA team setting some cases as severe, and urgent, the bad luck is all on them. The risk of going out of business due to millionaire damages inflicted on others, due to known neglected issues, too. I made the best for them. Geoff is aware. I called him out when we’ve found that Xojo bug. Robin is more than aware, and I’m pretty sure more Xojo engineers that were around in the forum too. Options and priorities…

[JeffT]

I looked at the case…
What does this currency issue in SQLLite have to do with testing Delphi?
(This should be a split topic?)

The issue is primarily one of (Xojo?) not being specific in the text-to-number conversion that is used when you provide a hard coded value in a string.

(eg it probably should be be using Val and expecting YOU to provide 5.2,
or should it be using Cdbl and interpreting what you throw at it, because a command like this is usually generated as the result of something someone typed in, despite the SQL Injection risk.
In which case if someone uses your USA formatted value of 5.2 on a Spanish system, they get the wrong value)

The command used was

db.ExecuteSQL(CreateIt + “INSERT INTO test (”“kk6000"”) VALUES (5.2);")

So, knowing that YOU want to store a real number of 5.2 in the db, and knowing that the app is running on a Spanish setup, wouldn’t it make sense to either
store the localised value of 5,2 here,
or use a parameterised query?

This is a severe case, possible data loss pointed out. It should not be a case of “subscribers” begging, but for the triage team set an urgent priority.

I seem to recall that SQLLite stores everything as a string.
That being the case, if a db is shared between a US and EU company, one of them is going to see incorrect values for 5.2
Is this ALSO a SQLLite ‘issue’ ?

[Rick.A]

So many wrong assumptions and fallacies, but I’ll need to expose A BIT of light over it…

@JeffT

What does this currency issue in SQLLite have to do with testing Delphi?

Well, this XOJO issue, not SQLite, is focused on the topic, not Delphi, and it is just to remember people that Xojo is, kind of, Silver, not Gold.

@JeffT

The issue is primarily one of (Xojo?) not being specific in the text-to-number conversion that is used when you provide a hard coded value in a string.

Please, stop saying nonsense without examining the case that was recognized as a “BUG IN THE FRAMEWORK” by Robin.

@JeffT

The command used was

db.ExecuteSQL(CreateIt + “INSERT INTO test (”“kk6000"”) VALUES (5.2);")

So, knowing that YOU want to store a real number of 5.2 in the db, and knowing that the app is running on a Spanish setup, wouldn’t it make sense to either
store the localised value of 5,2 here,
or use a parameterised query?

Do you have some notion on how this all you wrote is nonsense?

@JeffT

I seem to recall that SQLLite stores everything as a string.
That being the case, if a db is shared between a US and EU company, one of them is going to see incorrect values for 5.2
Is this ALSO a SQLLite ‘issue’ ?

More nonsense.

Please, next time, check the case with care.

Let’s just say that if we retrieve that SQLLite floating point value (not string) this way, c1 comes as 5.2 correctly:

Var c1 As Currency = rs.Column("kk6000").CurrencyValue

But, if we try using the iterators like

For Each row as DatabaseRow in rs

Var d1 As Double = row.Column(“kk6000”).DoubleValue   // 5.2
Var c1 As Currency = row.Column(“kk6000”).CurrencyValue  // -0.5808

The framework destroys the value. It’s not a SQLite problem, it was arisen when DatabaseRow was involved.

[JeffT]

@Rick.A

Do you have some notion on how this all you wrote is nonsense?

I will politely disagree.
Wouldn’t you expect a coder in Spain to formulate the same statement as

db.ExecuteSQL(CreateIt + “INSERT INTO test (”“kk6000"”) VALUES (5,2);")
?

You can store any type of data in any field in SQLLite.
To me, that sounds like text storage and variants. They call it type affinity.
If you want to say that is wrong or misrepresentative, I don’t mind.
You’re the expert.

I see you have amended the reply to remove a comment about me being aggressive.
I wasn’t intending to be so (I don’t have any issue with you, just comments on the situation) , but my goodness, have you read your bug report?
I could hear you shouting as I read it.

I wrote DB engines, I wrote an Operating System for an Industrial Computer.

Thats good.
But out of curiosity, did you try a parameterised query?
Did you try using 5,2 ?

I won’t bother responding any more here.
I understand your frustration, but I can live without being part of it.

[Rick.A]

@JeffT

Wouldn’t you expect a coder in Spain to formulate the same statement as

db.ExecuteSQL(CreateIt + “INSERT INTO test (”“kk6000"”) VALUES (5,2);")

No. Nobody does it unless they are some student learning and committing some massive error.

Do you realize, that that code I attached to the case is not a real code, but just a sample tailored to show the bug? Including the value, correctly put in the string, that is part of the “as you can see”.

@JeffT

have you read your bug report?

More than read, I wrote it. What’s wrong with it?

@JeffT

I could hear you shouting as I read it.

Hardly you will see me shouting.

Yes, I redacted what I was writing because I always read, and reread, what the other says, and what I’m writing, trying to find the best tone avoiding misunderstandings.

I’ve read your comment in a bad tone, unnecessarily lost the patience, but later I shrunk the arguments to the minimum after.

[JeffT]

OK, since you ask…

[Rick.A]

And that’s completely correct. If I owned Xojo, R2021R3.2 would be out next day just due to it.
I must advise readers that those huge exaggerations and zoom are from Jeff, not me. The text is ok and in place in the case.

[npalardy]

@Rick.A

This is a severe case, possible data loss pointed out. It should not be a case of “subscribers” begging

shouldnt be but it is frequently noted “there are not many reporters”

[Rick.A]

It’s a Framework bug, related to DB. And silent one. Probably not a SQLite one, probably affects more DBs. Needs deeper analysis. There are more ignored reports. I do remember something related to PostgreSQL, but when people say “The workaround is…” it’s neglected there.

As I said. Any math error, or I/O bug, or DB bug, potentially causing data corruption, is a severe bug signaling a “let’s stop everything and fix this” without any additional cry.

[Rick.A]

Seems related, PostgreSQL:

61761 - abnormalities between rowset and datarow with currency type from @MarkusWinter

feedback://showreport?report_id=61761

[ivan]

@JeffT

To me, that sounds like text storage and variants. They call it type affinity.

Why dont you TRY and VERIFY the problem instead of just writing the nonsense that you think it sounds like.

SQLite is storing the real as a DOUBLE (not as the other nonsense you think of storing all on plain text). Why are you so confused talking about the inserts?

@JeffT

The issue is primarily one of (Xojo?) not being specific in the text-to-number conversion that is used when you provide a hard coded value in a string.

So you are not even understanding what the problem is but blindy justifying the bug

Just read it carefully, it is not so difficult, Xojo retrieves that DOUBLE and INTERNALLY uses a intermediate string to convert that DOUBLE to a Currency. It has NOTHING to do with user input nor hard coded values in the code. Xojo could skip the string conversion but they decide to use it and screw up not making it regional aware.

[ivan]

@Rick.A

This is a severe case, possible data loss pointed out. It should not be a case of “subscribers” begging, but for the triage team set an urgent priority.

@Rick.A

The risk of going out of business due to millionaire damages inflicted on others, due to known neglected issues, too.

Calm down!!! you look like a newcomer to Xojo.

Last year I reported a bug where a silent change in the framework corrupted all the files saved. Same “millionaire damages inflicted on others, due to known neglected issues”… No answer from them, just a little note in the documentation to clarify the NEW behavior but not even noting there was a change, an attitude of it is not a bug, you are using it wrong look at the documentation.

So expect MAYBE a silent fix… But just look at case 61761, more than a year and still there.

[JeffT]

@ivan

Why dont you TRY and VERIFY the problem

Sigh.
Why dont you?

Xojo retrieves that DOUBLE and INTERNALLY uses a intermediate string to convert that DOUBLE to a Currency.

I believe that is what I suggested may be happening.

The issue is primarily one of (Xojo?) not being specific in the text-to-number conversion that is used when you provide a hard coded value in a string.

I’ve had enough of this forum.

[Rick.A]

@ivan

Xojo retrieves that DOUBLE and INTERNALLY uses a intermediate string to convert that DOUBLE to a Currency

What Xojo did, no one except Xojo knows, but for sure, what they did is wrong and gets affected by the locale and should not.

[ivan]

@JeffT

Sigh.
Why dont you?

I did when they made the original post on the other forum. Test to create the Database both in english locale and spain locale, both store the 5.2 as a double. The problem is NOT storing, is retrieving.

@JeffT

The issue is primarily one of (Xojo?) not being specific in the text-to-number conversion that is used when you provide a hard coded value in a string.

@JeffT

Wouldn’t you expect a coder in Spain to formulate the same statement as
db.ExecuteSQL(CreateIt + “INSERT INTO test (”“kk6000"”) VALUES (5,2);")

Of course NOT (At least if they know SQL) Are you aware that your code is NOT a valid SQL??? you CAN’T use a comma like that.

But sure its the forum

[npalardy]

One question
If instead of a hard coded insert of 5.2 you create a variable, say a double, and insert using a prepared statement then retrieve it, does the same issue ensue ?
something like

Var lDouble As Double = 5.2
Var db As New SQLiteDatabase
db.DatabaseFile = SpecialFolder.Desktop.Child("test-delete-it.sqlite")
db.CreateDatabase

Const createIt As String = "DROP TABLE IF EXISTS test; CREATE TABLE test (""kk6000"" real); "
db.ExecuteSQL(CreateIt)
db.ExecuteSQL("INSERT INTO test (""kk6000"") VALUES (?);", lDouble)

Var rs As RowSet = db.SelectSQL("SELECT * FROM test")

For Each row As DatabaseRow In rs
  
  Var d1 As Double = row.Column("kk6000").DoubleValue
  Var c1 As Currency = row.Column("kk6000").CurrencyValue
  Var s1 As String = row.Column("kk6000").StringValue
  Var sc1 As String = c1.ToString
  Var sd1 As String = d1.ToString
  
  Break // c1 is DESTROYED !
  
Next row

with a locale that doesnt use , and . as the separators is there still an issue ?
I tried this locally but dont think setting my locale worked as just a dimple debuglog of lDouble.tostring showed USA format still

makes me wonder if this is both a sqlite issue and how it determines what type affiinity that literal being inserted should have and xojo’s conversions

EDIT : had to go look this up but it is possible as well to figure out what type affinity sqlite thinks is correct for a column - so we COULD for 100% certain see if it inserts the literal 5.2 as double by running a query like

SELECT typeof(kk6000) FROM test;

In any of the test to see if SQLItes affinity is causing issue
And if not rule that out entirely
My gut suspicion is that its not the issue

[JeffT]

sure its the forum

No, it’s the people on the forum.
I just don’t get the attitudes round here.
Its so toxic.
There is nothing wrong with hypothesising, and it often triggers trains of thought that may not have occurred.

you CAN’T use a comma like that.

Fine.
But to me, sending a SQL command contained in a string to SQLLite in this manner, makes it SQLLite’s responsibility to interpret and store correctly.
Isn’t that what Xojo does?

In much the same way as I would expect sending a command to a DOS command line, a unix bash shell, or an HTTP POST… once sent, the responsibility is at the other end.
I don’t feel this is a Xojo bug.

If instead of a hard coded insert of 5.2 you create a variable, say a double, and insert using a prepared statement then retrieve it, does the same issue ensue ?

I asked that twice,

or use a parameterised query?

But out of curiosity, did you try a parameterised query?

but it’s probably nonsense , so…

[npalardy]

@JeffT

But to me, sending a SQL command contained in a string to SQLLite in this manner, makes it SQLLite’s responsibility to interpret and store correctly.
Isn’t that what Xojo does?

except that ISN’T legal sql occording to sqlite
see 3. Literal Values (Constants) on SQL Language Expressions

so i’d say its NOT sqlites issue and that insert is fine - yours wouldnt be

and we CAN test that the affinity (or TYPE) of that column IS a double

if Xojo isnt reading that back correctly then its in Xojo’s side of things I’d think