Re: [sqlite] "SQL Error: near 'Table': Syntax error"
On 7/27/06, Peter van Dijk <[EMAIL PROTECTED]> wrote: 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
Re: [sqlite] "SQL Error: near 'Table': Syntax error"
On Jul 26, 2006, at 5:33 PM, Dennis Cote wrote: John Newby wrote: 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.
Re: [sqlite] "SQL Error: near 'Table': Syntax error"
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 On 26/07/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Dennis Cote <[EMAIL PROTECTED]> wrote: > John Newby wrote: > > > > 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 <[EMAIL PROTECTED]>
Re: [sqlite] "SQL Error: near 'Table': Syntax error"
Dennis Cote <[EMAIL PROTECTED]> wrote: > John Newby wrote: > > > > 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 <[EMAIL PROTECTED]>
Re: [sqlite] "SQL Error: near 'Table': Syntax error"
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 On 26/07/06, Dennis Cote <[EMAIL PROTECTED]> wrote: John Newby wrote: > > 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
Re: [sqlite] "SQL Error: near 'Table': Syntax error"
John Newby wrote: 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
Re: [sqlite] "SQL Error: near 'Table': Syntax error"
John Newby wrote: 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.
Re: [sqlite] "SQL Error: near 'Table': Syntax error"
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 On 26/07/06, Jonathan Ballet <[EMAIL PROTECTED]> wrote: John Newby wrote: > 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
Re: [sqlite] "SQL Error: near 'Table': Syntax error"
John Newby wrote: > 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
Re: [sqlite] "SQL Error: near 'Table': Syntax error"
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 On 26/07/06, [EMAIL PROTECTED] < [EMAIL PROTECTED]> wrote: "John Newby" <[EMAIL PROTECTED]> writes: > 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
Re: [sqlite] "SQL Error: near 'Table': Syntax error"
"John Newby" <[EMAIL PROTECTED]> writes: > 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
Re: [sqlite] "SQL Error: near 'Table': Syntax error"
John Newby wrote: > 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
[sqlite] "SQL Error: near 'Table': Syntax error"
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
