Just ran into a long-existing bug when trying to list the fields of a table.
Consider this valid table creation:
CREATE TABLE 'Order' (
'rowid' INTEGER DEFAULT NULL PRIMARY KEY AUTOINCREMENT,
When trying to get the schema like this, it’ll return nil:
That’s because “ORDER” is also a reserved word, meaning that one needs to escape it when addressing it with SQL commands. Obviously, the Xojo code doesn’t follow this rule properly.
The work-around is to escape the name yourself, e.g. like this:
Or just call it tblOrder
I find it helps to prefix table names with tbl
Why do you consider that a bug? If you use a reserved word, you need to escape it.
there should be an error raised at the time you try to sqlexecute the table creation ?
Correction: Instead of
mDB.FieldSchema ("[Order]") one needs to use
Eli, it’s a but because I am not writing raw SQL code here but am using a high-level function that expects a plain table name. In the same way that TableSchema does not escape the returned names, I should not have to escape them when passing the same names to related functions.
@Jean-Yves Pochez, No, because at time of creation the name was properly escaped as shown in the code above.
this “anomoly” [as I too don’t consider it a ‘bug’] exists in the database ENGINE, not in XOJO…
any other “front end” to the same database would react in the same or similar manner
I’ll now weep silently and turn notifications off.
What database engine are you seeing this in
SQL has VERY few reserved words of its own - but different engines may treat them as “reserved”
IF you define a table as
Create table from ( id integer primary key ,
to integer )
Its legal to write
select from from from where …
and many engines will accept this without complaint
I’ve done this regardless of how icky it looks
Hence my question
Ah … sqlite does NOT like this table definition and barks
Last login: Wed Feb 24 07:54:29 on ttys000
server:~ npalardy$ sqlite3
SQLite version 3.7.13 2012-07-17 17:46:21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE 'Order' (
...> 'rowid' INTEGER DEFAULT NULL PRIMARY KEY AUTOINCREMENT,
...> 'txt' TEXT
...> ) ;
sqlite> select * from order ;
Error: near "order": syntax error
it looks like different vendors “reserve” different words
google for “sql reserved words” and you’ll see there are a lot of vendor specific lists
sqlite> insert into ‘Order’ (rowid, txt) values ( 1, ‘foo’ ) ;
sqlite> select * from ‘Order’ ;
definitely a “sqlite reserved word” but not “sql spec reserved word”
well using SqliteMan
create table 'order'
create table "order"
create table [order]
all worked, and showed up in sqlite_master as just plain order
create table order
of course did not
It seems like the Xojo database api should be handling the escaping. In SQLite, it’s valid to use even spaces in your identifiers. Xojo should handle this correctly.
However, I’d be willing to bet this behavior will not be changed because it would silently break existing code. If you have written your code as FieldSchema("[Order]") and all of the sudden escaping happens for you, this would no longer return the correct result.