Re: [sqlite] problem with creating a table

[DJ Anubis]

A. Pagaltzis a écrit :
> * Bijan Farhoudi <[EMAIL PROTECTED]> [2006-06-25 17:05]:
>   
>> A. Pagaltzis wrote:
>> 
>>>.headers on
>>>SELECT [order] FROM foo
>>>   
>> But how would you know the name of the col is "order" not
>> "[order]"?
>> 
>
> That’s what `.headers on` was supposed to demonstrate.
>
> Regards,
>   
BTW, this just demonstrate one should never use standard SQL reserved
words as column or table names :)

Wouldn't it be a better strategy to rename this order column to
something not reserved such as "ordering", "sorting" or anything else
not being a resreved word.

Re: [sqlite] problem with creating a table

[A. Pagaltzis]

* Bijan Farhoudi <[EMAIL PROTECTED]> [2006-06-25 17:05]:
> A. Pagaltzis wrote:
> >.headers on
> >SELECT [order] FROM foo
>
> But how would you know the name of the col is "order" not
> "[order]"?

That’s what `.headers on` was supposed to demonstrate.

Regards,
-- 
Aristotle Pagaltzis // 

Re: [sqlite] problem with creating a table

[John Stanton]

Bijan Farhoudi wrote:

Hi,
I am trying to create table and I would like to name one of the columns 
order, but pysqlite does not like it. I do not want to have order__ or 
any thing like that.

for example the following command does not work:
cur.execute('create table foo(i integer, order integer)')

How can I fix this problem?

Regards,
   Bijan

By only using ORDER as an SQL keyword.

Re: [sqlite] problem with creating a table

[Derrell . Lipman]

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

Re: [sqlite] problem with creating a table

[Bijan Farhoudi]

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]"?

Re: [sqlite] problem with creating a table

[A. Pagaltzis]

* 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

Regards,
-- 
Aristotle Pagaltzis // 

Re: [sqlite] problem with creating a table

[Bijan Farhoudi]

[EMAIL PROTECTED] wrote:

Bijan Farhoudi <[EMAIL PROTECTED]> writes:

  

I am trying to create table and I would like to name one of the columns
order, but pysqlite does not like it. I do not want to have order__ or any
thing like that.  for example the following command does not work:
cur.execute('create table foo(i integer, order integer)')



You're working on dangerous ground when you use reserved words as your column
names.  'order' is a reserved word, as it is used for the ORDER BY clause.

You can do it, though, like this:

  create table foo(i integer, [order] integer)

Placing column names in square brackets lets you use reserved words as column
names.

Derrell

  

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?

Cheers,
   Bijan

Re: [sqlite] problem with creating a table

[Derrell . Lipman]

Bijan Farhoudi <[EMAIL PROTECTED]> writes:

> I am trying to create table and I would like to name one of the columns
> order, but pysqlite does not like it. I do not want to have order__ or any
> thing like that.  for example the following command does not work:
> cur.execute('create table foo(i integer, order integer)')

You're working on dangerous ground when you use reserved words as your column
names.  'order' is a reserved word, as it is used for the ORDER BY clause.

You can do it, though, like this:

  create table foo(i integer, [order] integer)

Placing column names in square brackets lets you use reserved words as column
names.

Derrell