Bijan Farhoudi <[EMAIL PROTECTED]> writes:
> A. Pagaltzis wrote:
>> * Bijan Farhoudi <[EMAIL PROTECTED]> [2006-06-25 16:35]:
>>
>>> Thanks for your answer but still I am getting an error message:
>>> sqlite> create table foo(i integer, [order] integer);
>>> sqlite> .sch
>>> CREATE TABLE foo(i integer, [order] integer);
>>> sqlite> insert into foo values(1,2);
>>> sqlite> select order from foo
>>> ...> ;
>>> SQL error: near "order": syntax error
>>>
>>> Any other idea?
>>>
>>
>> .headers on
>> SELECT [order] FROM foo
>>
> But how would you know the name of the col is "order" not "[order]"?
Surround ALL table and column names in square brackets when you reference
them, and that should eliminate the confusion.
sqlite> CREATE TABLE [foo] ([i] INTEGER, [order] INTEGER);
sqlite> INSERT INTO [foo] VALUES (1,2);
sqlite> .mode line
sqlite> SELECT [order] FROM [foo];
order = 2
sqlite> SELECT * FROM [foo];
i = 1
order = 2
sqlite>
Note that the square brackets are not displayed because they are not part of
the column name. They are delimiters to say, "Hey, I really, really know what
I'm doing; I know that my column names may be reserved words and I want to do
this anyway."
Since the square brackets let you put nearly any character in table and column
names, you can do hard to comprehend stuff, like this:
sqlite> CREATE TABLE [[x] (i integer);
sqlite> select * from sqlite_master where type = 'table';
type = table
name = foo
tbl_name = foo
rootpage = 2
sql = CREATE TABLE [foo] ([i] INTEGER, [order] INTEGER)
type = table
name = [x
tbl_name = [x
rootpage = 3
sql = CREATE TABLE [[x] (i integer)
sqlite>
Note that the new table name is "[x" (a square bracket followed by 'x').
Like I said in my first reply to you, using reserved words for column names is
dangerous. You should generally try to avoid it.
Derrell
