Is this a bug? solved

Databases
1

I am at a complete loss to see what is wrong with this code: using Xojo version 2015 r2.4

[code] Dim row As New DatabaseRecord
row.Column(“theDate”) = theDate
row.Column(“order”) = theOrder
row.Column(“person”) = Str(selectPersonPopup.ListIndex)
row.Column(“yearID”) = Str(ThisYear)
row.Column(“monthID”) = Str(d.Month)
row.Column(“systolic”) = systolicTextField.Text
row.Column(“diastolic”) = diastolicTextField.Text
row.Column(“heartRate”) = heartRateTextField.Text
row.Column(“pulsePressure”) = Str(pulsePressure)
App.health_db.InsertRecord(“stats”, row)

If App.Health_db.Error Then
  MsgBox("DB Error: " + App.Health_db.ErrorMessage)
End If
 [/code]

When I run this I get a DB.Error in the MsgBox: "DB Error; near “order”: syntax error

I cannot see what is wrong with the code.

Anyway, I stepped through the code in the debugger after setting a breakpoint, and the “order” variable was entered as expected.

I was curious as to what has been stored, so I hit the magnifing glass icon for the field, and Oxjo came up with an error message saying something like an unexpected error had occurred and did I want to report it. Well I did, but I don’t have the latest Feedback app, and can’t seem to find it on the Xojo website.

I have upgraded to El Capitan on my MacBook Pro. (I actually wish I hadn’t!)

Any ideas please?

2

I have found the problem.

I decided to enter the data into the database manually using Paul’s SQLVue. When I hit the return key I got the same error, so I guess that the database has become corrupted somehow. The “order” Field has just been added to the database, and I had trouble getting all the data that I wanted into it, so I guess that was it.

3

“order” is a reserved word in SQL(ite): https://www.sqlite.org/lang_keywords.html
you shouldn’t use them as column names if possible

4

[quote=223529:@Alex von Siebenthal]“order” is a reserved word in SQL(ite): https://www.sqlite.org/lang_keywords.html
you shouldn’t use them as column names if possible[/quote]
the plugin interface should forbid us to do so …

5

Well, it’s not forbidden as far as I know - in SQLite at least. One could use these words as field names in SQL queries when paying attention to proper escaping. But that’s some guessing, to be honest.

I’d report it in feedback and propose to either:

  • forbid those words (which might not be preferable), or
  • ensure proper escaping when the DatabaseRecord class constructs the query behind the scenes (this would probably be considered a bug then).

As a workaround, you may resort to SQL queries and PreparedStatements.

6

Thanks Alex

That was indeed the case. I have created a new Field “theOrder” and changed my code to reflect that, and all is well. The only problem now is that I cannot delete the “order” Field. Still, as it is not referred to, it really isn’t doing much harm (except adding NULL everytime the database has a line added. As soon as I can figure out how to create a new database and populate it with the existing data, I will do that and get rid of the bad Field.

Thanks for both replies.

7

the keywords in sql aren’t reserved in many db’s

have fun with

select from from from from where from = 1

legal in many db’s
confusing as heck though

8

That’s a SQLite “feature”. It doesn’t permit changes to table structures (except for column additions). Simply create a new table without the “order” column and import the data from the old table. Some SQLite editors do the work for you, e.g. the inexpensive Navicat Essentials.