"SQL Error: near 'Table': Syntax error"

[John Newby]

Hi, I've took the following quote from the SQLite website stating that
"Tables names that begin with "*sqlite_*" are reserved for use by the
engine" and if you try to create a table beginning with this it lets you
know about it, but what about tables called "TABLE", this throws back an
error stating "SQL Error: near 'Table': Syntax error"

Does anyone know any reason why SQLite doesnt like tables called "Table" or
is this a standard SQL thing?

Many thanks

John

[Jonathan Ballet]

Post by John Newby
Does anyone know any reason why SQLite doesnt like tables called "Table" or
is this a standard SQL thing?
Many thanks
John

I guess that 'Table' is a reserved keyword, part of the SQL language.
If you _really_ want to have a table, named 'table', you should put the name
between quotes in the SQL query :

sqlite> create table table (value TEXT);
SQL error: near "table": syntax error
sqlite> create table 'table' (value TEXT);
sqlite> .schema
CREATE TABLE 'table' (value TEXT);
sqlite> select * from 'table';
sqlite> select * from table;
SQL error: near "table": syntax error
sqlite>


Cheers,
Jonathan

[D***@public.gmane.org]

Post by John Newby
Does anyone know any reason why SQLite doesnt like tables called "Table" or
is this a standard SQL thing?

It's a reserved word, so if you really, Really, REALLY want to create a table
of that name (you're making it confusing to read, so you really shouldn't),
you can do it using either quotes or square brackets around the table name, as
shown here:

SQLite version 3.2.1
Enter ".help" for instructions
sqlite> create table "TABLE" (i integer);
sqlite> .schema
CREATE TABLE "TABLE" (i integer);
sqlite> .mode line
sqlite> select * from sqlite_master;
type = table
name = TABLE
tbl_name = TABLE
rootpage = 2
sql = CREATE TABLE "TABLE" (i integer)
sqlite> drop table "table";
sqlite> create table [TABLE] (i integer);
sqlite> .schema
CREATE TABLE [TABLE] (i integer);
sqlite> select * from sqlite_master;
type = table
name = TABLE
tbl_name = TABLE
rootpage = 2
sql = CREATE TABLE [TABLE] (i integer)
sqlite>



Derrell

[John Newby]

Hi Jonathan, Derrel, thanks for the info.

I just wanted to know as I am creating an application that interacts with
the SQLite.dll and if a user was to try to create a table beginning with
"sqlite_" the user would get the very detailed erro message, but if for some
reason the user wanted to call the table "Table" they would just get a
simple SQL syntax error and may become stuck in my application.

I'll just put some check on the input from the user and if on the off chance
they decide to call it table, it will change it to "Table".

Is there any other names I need to look out for other than the "sqlite_" and
"table" that SQLite doesn't like as being a table name that anyone knows of?

Thanks again

John

Post by D***@public.gmane.org

Post by John Newby
Does anyone know any reason why SQLite doesnt like tables called "Table"

or

Post by John Newby
is this a standard SQL thing?

It's a reserved word, so if you really, Really, REALLY want to create a table
of that name (you're making it confusing to read, so you really shouldn't),
you can do it using either quotes or square brackets around the table name, as
SQLite version 3.2.1
Enter ".help" for instructions
sqlite> create table "TABLE" (i integer);
sqlite> .schema
CREATE TABLE "TABLE" (i integer);
sqlite> .mode line
sqlite> select * from sqlite_master;
type = table
name = TABLE
tbl_name = TABLE
rootpage = 2
sql = CREATE TABLE "TABLE" (i integer)
sqlite> drop table "table";
sqlite> create table [TABLE] (i integer);
sqlite> .schema
CREATE TABLE [TABLE] (i integer);
sqlite> select * from sqlite_master;
type = table
name = TABLE
tbl_name = TABLE
rootpage = 2
sql = CREATE TABLE [TABLE] (i integer)
sqlite>
Derrell

[Jonathan Ballet]

Post by John Newby
Hi Jonathan, Derrel, thanks for the info.
I just wanted to know as I am creating an application that interacts with
the SQLite.dll and if a user was to try to create a table beginning with
"sqlite_" the user would get the very detailed erro message, but if for some
reason the user wanted to call the table "Table" they would just get a
simple SQL syntax error and may become stuck in my application.
I'll just put some check on the input from the user and if on the off chance
they decide to call it table, it will change it to "Table".
Is there any other names I need to look out for other than the "sqlite_" and
"table" that SQLite doesn't like as being a table name that anyone knows of?
Thanks again
John

I think that all SQL commands or operators (create, alter, update, where, from,
select, in, as, etc...) cannot be used.

Just check it by yourself ;)

Jonathan Ballet

[John Newby]

Hi Jonathan, yes you are right, thanks for this, I'll just put a check for
them all as you never know what a user is going to do its better to try and
fix it beforehand.

thanks again

John

Post by Jonathan Ballet

Post by John Newby
Hi Jonathan, Derrel, thanks for the info.
I just wanted to know as I am creating an application that interacts

with

Post by John Newby
the SQLite.dll and if a user was to try to create a table beginning with
"sqlite_" the user would get the very detailed erro message, but if for some
reason the user wanted to call the table "Table" they would just get a
simple SQL syntax error and may become stuck in my application.
I'll just put some check on the input from the user and if on the off chance
they decide to call it table, it will change it to "Table".
Is there any other names I need to look out for other than the "sqlite_" and
"table" that SQLite doesn't like as being a table name that anyone knows of?
Thanks again
John

I think that all SQL commands or operators (create, alter, update, where, from,
select, in, as, etc...) cannot be used.
Just check it by yourself ;)
Jonathan Ballet

[Dennis Cote]

Post by John Newby
Is there any other names I need to look out for other than the
"sqlite_" and
"table" that SQLite doesn't like as being a table name that anyone knows of?

John,

All keywords need to be quoted to use them as identifiers. There is a
comprehensive list of keywords at http://www.sqlite.org/lang_keywords.html

I would also suggest that you stick with the SQL standard method of
quoting identifiers using double quotes (rather than the other
extensions that SQLite accepts for compatibility with other non-standard
database systems).

HTH
Dennis Cote

[John Newby]

Hi Dennis, thanks for this, I've just spent the last 30 mins typing in every
word I could think of that might have come up with a conflict, it was
nowhere near as big as the list in the link.

Thanks for the tips.

John

Post by Dennis Cote

Post by John Newby
Is there any other names I need to look out for other than the "sqlite_" and
"table" that SQLite doesn't like as being a table name that anyone knows of?

John,
All keywords need to be quoted to use them as identifiers. There is a
comprehensive list of keywords at http://www.sqlite.org/lang_keywords.html
I would also suggest that you stick with the SQL standard method of
quoting identifiers using double quotes (rather than the other
extensions that SQLite accepts for compatibility with other non-standard
database systems).
HTH
Dennis Cote

[d***@public.gmane.org]

Post by Dennis Cote

Post by John Newby
Is there any other names I need to look out for other than the
"sqlite_" and
"table" that SQLite doesn't like as being a table name that anyone knows of?

John,
All keywords need to be quoted to use them as identifiers. There is a
comprehensive list of keywords at http://www.sqlite.org/lang_keywords.html
I would also suggest that you stick with the SQL standard method of
quoting identifiers using double quotes (rather than the other
extensions that SQLite accepts for compatibility with other non-standard
database systems).

The keyword list has not been scrubbed lately and might contain
omissions. Also, new keywords are added from time to time.
To be safe, it is best to quote all table and column names, or
else use a prefix on every name that is unlikely to ever be used
as a keyword.
--
D. Richard Hipp <***@hwaci.com>

[John Newby]

Hi Richard, I noticed 'ADD' and 'COLUMN' aren't allowed but aren't on the
list but I haven't came accross any others at the moment.

I have taken your advice and am now putting all identifiers between double
quotes.

Many thanks for your help

John

Post by Dennis Cote

Post by Dennis Cote

Post by John Newby
Is there any other names I need to look out for other than the
"sqlite_" and
"table" that SQLite doesn't like as being a table name that anyone knows of?

John,
All keywords need to be quoted to use them as identifiers. There is a
comprehensive list of keywords at

http://www.sqlite.org/lang_keywords.html

Post by Dennis Cote
I would also suggest that you stick with the SQL standard method of
quoting identifiers using double quotes (rather than the other
extensions that SQLite accepts for compatibility with other non-standard
database systems).

The keyword list has not been scrubbed lately and might contain
omissions. Also, new keywords are added from time to time.
To be safe, it is best to quote all table and column names, or
else use a prefix on every name that is unlikely to ever be used
as a keyword.
--

[Peter van Dijk]

Post by Dennis Cote

Post by John Newby
Is there any other names I need to look out for other than the "sqlite_" and
"table" that SQLite doesn't like as being a table name that anyone knows of?

John,
All keywords need to be quoted to use them as identifiers. There is
a comprehensive list of keywords at http://www.sqlite.org/
lang_keywords.html
I would also suggest that you stick with the SQL standard method of
quoting identifiers using double quotes (rather than the other
extensions that SQLite accepts for compatibility with other non-
standard database systems).

Using double quotes to quote identifiers in sqlite is dangerous, as
mistyping
a fieldname will not yield an error. Currently the only safe way to
quote
identifiers is using backticks, as in MySQL.

Cheers, Peter.

[prabhu kumaravelu]

Please unsubscribe me from sqlite

Subject: Re: [sqlite] "SQL Error: near 'Table': Syntax error"
Date: Thu, 27 Jul 2006 08:44:59 +0200

Is there any other names I need to look out for other than the "sqlite_"
and
"table" that SQLite doesn't like as being a table name that anyone knows
of?

John,
All keywords need to be quoted to use them as identifiers. There is a
comprehensive list of keywords at http://www.sqlite.org/
lang_keywords.html
I would also suggest that you stick with the SQL standard method of
quoting identifiers using double quotes (rather than the other extensions
that SQLite accepts for compatibility with other non- standard database
systems).

Using double quotes to quote identifiers in sqlite is dangerous, as
mistyping
a fieldname will not yield an error. Currently the only safe way to quote
identifiers is using backticks, as in MySQL.
Cheers, Peter.

_________________________________________________________________
Sexy, sultry, sensuous. - see why Bipasha Basu is all that and more. Try MSN
Search http://server1.msn.co.in/Profile/bipashabasu.asp

[Dennis Cote]

Post by Peter van Dijk
Using double quotes to quote identifiers in sqlite is dangerous, as
mistyping
a fieldname will not yield an error. Currently the only safe way to
quote
identifiers is using backticks, as in MySQL.

Sadly you are almost correct. :-(

Using the MS Access style square brackets for quoting also produces correct
error message for incorrect column names.

Unfortunately, an sqlite extension causes it to misinterpret non-existent
column names as string literals when they are quoted using SQL standard
double quotes.

The following trace shows the results for various styles of quotes.

SQLite version 3.2.8
Enter ".help" for instructions
sqlite> create table t (a);
sqlite> insert into t values(1);
sqlite> select a from t;
1
sqlite> select c from t;
SQL error: no such column: c
sqlite> select "a" from t;
1
sqlite> select "c" from t;
c
sqlite> select 'a' from t;
a
sqlite> select 'c' from t;
c
sqlite> select `a` from t;
1
sqlite> select `c` from t;
SQL error: no such column: c
sqlite> select [a] from t;
1
sqlite> select [c] from t;
SQL error: no such column: c

I'm sure this extension seemed like a good idea when it was introduced, but
this example shows the dangers of changing standard functionality to
"improve" it.

Now we are stuck using the non-standard quote characters introduced for
compatibility with other non-standard implementations in order to get
reliable error detection. Consequently, the SQL using these non-standard
quotes will not be portable to other standard conforming implementations.

The moral of this story is: stick to the standard unless you have a *VERY*
good reason to deviate.

Dennis Cote

[John Stanton]

Post by John Newby
Hi, I've took the following quote from the SQLite website stating that
"Tables names that begin with "*sqlite_*" are reserved for use by the
engine" and if you try to create a table beginning with this it lets you
know about it, but what about tables called "TABLE", this throws back an
error stating "SQL Error: near 'Table': Syntax error"
Does anyone know any reason why SQLite doesnt like tables called "Table" or
is this a standard SQL thing?
Many thanks
John

TABLE is a reserved word.