RE: [GRASS-user] RE: Problem querying layers other than '1' in gi s.m

GRASS GRASS user
1

I tried renaming within sqlitebrowser to no avail. I posted the error within
my message a few posts ago. I'm unfamiliar with a lot of the SQL syntax, but
I tried:

ALTER TABLE Video_Benthos
RENAME Rumbolt_Video_Benthos_Uncorrected_UTC_NR.ID ID ;

But I'm most likely getting the syntax incorrect. Any ideas?

~ Eric.

-----Original Message-----
From: Moritz Lennert
To: Patton, Eric
Cc: ''Dylan Beaudette ' '; ''grassuser@grass.itc.it' '
Sent: 9/25/2006 11:23 AM
Subject: Re: [GRASS-user] RE: Problem querying layers other than '1' in gi
s.m

Patton, Eric wrote:

Could the error I'm getting be caused by the length of the new field
names?

I think the problem is rather the period in the name.

From the Postgresql documentation:

"SQL identifiers and key words must begin with a letter (a-z, but

also

letters with diacritical marks and non-Latin letters) or an

underscore

(_). Subsequent characters in an identifier or key word can be

letters,

underscores, digits (0-9), or dollar signs ($)."

Moritz

Strange then that Sqlite writes these column names itself?!

No time to check this now, but probably sqlite allows such column names,

but GRASS tries to enforce SQL standards which probably don't allow
them.

I exported the
tables as csv, opened it in OOffice, and just renamed the columns. It

seems

to be behaving now.

Couldn't you modify them in sqlite directly ?

Moritz

2

Eric,

One way to avoid this problem in the future is with a SQL like:

CREATE TABLE new_table AS
SELECT table_a.really_long_column_name as id, table_b.some_column as temp
from table_a join table_b
on table_a.key = table_b.key ;

i.e. rename the columns inline with the query.

cheers,

Dylan

On Monday 25 September 2006 09:13, Patton, Eric wrote:

I tried renaming within sqlitebrowser to no avail. I posted the error
within my message a few posts ago. I'm unfamiliar with a lot of the SQL
syntax, but I tried:

ALTER TABLE Video_Benthos
RENAME Rumbolt_Video_Benthos_Uncorrected_UTC_NR.ID ID ;

But I'm most likely getting the syntax incorrect. Any ideas?

~ Eric.

-----Original Message-----
From: Moritz Lennert
To: Patton, Eric
Cc: ''Dylan Beaudette ' '; ''grassuser@grass.itc.it' '
Sent: 9/25/2006 11:23 AM
Subject: Re: [GRASS-user] RE: Problem querying layers other than '1' in gi
s.m

Patton, Eric wrote:
>>> Could the error I'm getting be caused by the length of the new field
>>> names?
>>
>> I think the problem is rather the period in the name.
>>
>>From the Postgresql documentation:
>>
>> "SQL identifiers and key words must begin with a letter (a-z, but

also

>> letters with diacritical marks and non-Latin letters) or an

underscore

>> (_). Subsequent characters in an identifier or key word can be

letters,

>> underscores, digits (0-9), or dollar signs ($)."
>>
>> Moritz
>
> Strange then that Sqlite writes these column names itself?!

No time to check this now, but probably sqlite allows such column names,

but GRASS tries to enforce SQL standards which probably don't allow
them.

> I exported the
> tables as csv, opened it in OOffice, and just renamed the columns. It

seems

> to be behaving now.

Couldn't you modify them in sqlite directly ?

Moritz

--
Dylan Beaudette
Soils and Biogeochemistry Graduate Group
University of California at Davis
530.754.7341

3

On Mon, September 25, 2006 18:13, Patton, Eric wrote:

I tried renaming within sqlitebrowser to no avail. I posted the error
within
my message a few posts ago. I'm unfamiliar with a lot of the SQL syntax,
but
I tried:

ALTER TABLE Video_Benthos
RENAME Rumbolt_Video_Benthos_Uncorrected_UTC_NR.ID ID ;

But I'm most likely getting the syntax incorrect. Any ideas?

ALTER TABLE Video_Benthos RENAME
Rumbolt_Video_Benthos_Uncorrected_UTC_NR.ID ID ;

I'm not sure sqlite supports renaming a column.

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

Doesn't mention the possibility.

In Postgresql the query would have to be

ALTER TABLE Video_Benthos RENAME
Rumbolt_Video_Benthos_Uncorrected_UTC_NR.ID TO ID ;

i.e. you have to add the 'TO'.

Moritz