FYI: Database.FieldSchema doesn't properly escape table name

Databases
#1

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, 'txt' TEXT )

When trying to get the schema like this, it’ll return nil:

mDB.FieldSchema ("Order")

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:

mDB.FieldSchema ("[Order]")
#2

Or just call it tblOrder

I find it helps to prefix table names with tbl

#3

Why do you consider that a bug? If you use a reserved word, you need to escape it.

#4

there should be an error raised at the time you try to sqlexecute the table creation ?

#5

Correction: Instead of mDB.FieldSchema ("[Order]") one needs to use mDB.FieldSchema ("'Order'")

#6

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.

#7

@Jean-Yves Pochez, No, because at time of creation the name was properly escaped as shown in the code above.

#8

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

#9

I’ll now weep silently and turn notifications off.

#10

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 ,
from integer,
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

#11

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
sqlite>

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

#12

sqlite> insert into ‘Order’ (rowid, txt) values ( 1, ‘foo’ ) ;
sqlite> select * from ‘Order’ ;
1|foo

definitely a “sqlite reserved word” but not “sql spec reserved word”

#13

well using SqliteMan

create table 'order'
create table "order"
create table [order]

all worked, and showed up in sqlite_master as just plain order
but

create table order

of course did not

#14

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.

#15