[SQLite] Strange error at CREATE TABLE

Databases
1

The error I get is (from my MsgBox reporting error at CREATE TABLE time):

Save: Add a Data Base Table DB Error occured: near "Index": syntax error

The only Column name that can leads into an error is… “Index” (without curly quotes).

OK: I found the answer here lang_createtable.html .

I send this entry just as a reminder for other users, just in case they fall into the same trap.

In my case, the column holds… an index that is used to sort the list; it can be sorted too by the Date Column, but having on the first column an index was as evident as breathing to live.
Think at a line number - for those who love line numbers in Xojo :wink: - you will saw in bills, bank account reports, etc.

Now I have to find a synonym and replace Index with that word in my code.

2

Index is a reserved word. You can’t use that.

3

Thanks Greg.

4

Index is absent from the list of reserved words .

5

That is a list of Xojo reserved words.
SQLite, mySQL, Oracle, Postgres … etc… all have their own unique list of words.
Remember once you execute an SQL statement you are in “another language”, it just happens to be facilitated by Xojo

6

Its not a Xojo reserved word
Its a SQLite reserved word (because you’re telling SQLite, not Xojo, to create a table)

Last login: Wed Jun 7 09:34:17 on ttys000 server:~ npalardy$ sqlite3 SQLite version 3.8.10.2 2015-05-20 18:17:19 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table foo (bar , index ) ; Error: near "index": syntax error sqlite>

http://sqlite.org/lang_keywords.html

keyword #60 is index

7

Hi Norman,

I was reading SQL for Dummies 8th Edition. It was hard to reach the reserved word INDEX. I found an occurence at DROP INDEX… had enough searching (from the paper 2001 edition, printed in 2002 and 8th edition).

BTW: I was already tired 18 hours ago :frowning:
(and it’s midnight ! [00:45])

Edit: Sorry and Thanks.

8

Emile, if you’re really stuck with the index table name, you can wrap it around grave accents like this:

sqlite> create table foo (bar , `index` ) ;

Still, using reserved words is not recommended. :wink:

9

Thanks to all.