David
I was tripped up for a period of time when Rails was complaining
about an update to a table. The problem SQL query was:
UPDATE
group = 'test'
WHEN id='1'
The problem is that (I guess) group is a keyword. So I fixed this by
changing the name of the group field:
UPDATE
grp = 'test'
WHEN id='1'
However, since rails seems to be catering to us 'dummies', it would be nice
if rails had generated the following SQL query:
UPDATE
posts.group = 'test'
WHEN id='1'
where posts is the name of the table.
This would have insulated me from having to avoid using
'bad' column names.
Does this sound like a reasonable change to make?
···
--
Jim Freeze
does rails support sqlite?
jib:~ > sqlite db 'create table t(x);insert into t values (42);update t set t.x=42;'
SQL error: near ".": syntax error
-a
···
On Tue, 5 Oct 2004 jim@freeze.org wrote:
David
I was tripped up for a period of time when Rails was complaining
about an update to a table. The problem SQL query was:
UPDATE
group = 'test'
WHEN id='1'
The problem is that (I guess) group is a keyword. So I fixed this by
changing the name of the group field:
UPDATE
grp = 'test'
WHEN id='1'
However, since rails seems to be catering to us 'dummies', it would be nice
if rails had generated the following SQL query:
UPDATE
posts.group = 'test'
WHEN id='1'
where posts is the name of the table.
This would have insulated me from having to avoid using
'bad' column names.
Does this sound like a reasonable change to make?
--
EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov
PHONE :: 303.497.6469
A flower falls, even though we love it;
and a weed grows, even though we do not love it. --Dogen
===============================================================================
* jim@freeze.org <jim@freeze.org> [2004-10-05 23:15:08 +0900]:
UPDATE
posts.group = 'test'
WHEN id='1'
You know, this should probably read:
UPDATE
posts.group = 'test'
WHEN posts.id='1'
···
--
Jim Freeze
No the right solution would be to quote the column names:
UPDATE
`group` = 'test'
WHEN `id`='1'
That ` is annoying on keyboards with dead keys though.
···
On Tue, Oct 05, 2004 at 11:15:08PM +0900, jim@freeze.org wrote:
David
I was tripped up for a period of time when Rails was complaining
about an update to a table. The problem SQL query was:
UPDATE
group = 'test'
WHEN id='1'
The problem is that (I guess) group is a keyword. So I fixed this by
changing the name of the group field:
UPDATE
grp = 'test'
WHEN id='1'
However, since rails seems to be catering to us 'dummies', it would be nice
if rails had generated the following SQL query:
UPDATE
posts.group = 'test'
WHEN id='1'
where posts is the name of the table.
This would have insulated me from having to avoid using
'bad' column names.
Does this sound like a reasonable change to make?
--
Thomas
beast@system-tnt.dk
* Ara.T.Howard@noaa.gov <Ara.T.Howard@noaa.gov> [2004-10-05 23:45:24 +0900]:
does rails support sqlite?
jib:~ > sqlite db 'create table t(x);insert into t values (42);update t set
t.x=42;'
SQL error: near ".": syntax error
I have heard that there was a recent release that enabled rails to work with
sqlite. Don't know if all the updated are in gems yet.
···
--
Jim Freeze
Thomas Fini Hansen wrote:
No the right solution would be to quote the column names:
UPDATE
`group` = 'test'
WHEN `id`='1'
That ` is annoying on keyboards with dead keys though.
Seems to me the "standard" (as if there were really a reliable SQL standard) is to double quote column and table names (if quoting is needed), and single quote string literals:
UPDATE the_table
SET "group" = 'test'
WHEN id=1
- Jamis
···
--
Jamis Buck
jgb3@email.byu.edu
http://www.jamisbuck.org/jamis
I have heard that there was a recent release that enabled rails to work with
sqlite. Don't know if all the updated are in gems yet.
I believe that it supports the Ruby/SQLite 1.3.x bindings, not the
newer 2.1.x bindings.
Another question, is this even valid SQL-92?
UPDATE
group = 'test'
WHERE
id = 1
At a minimum, it seems that the update should be
UPDATE
posts
SET
group = 'test'
WHERE
id = 1
Is the former some kind of weird shorthand?
···
--
Justin Rudd
http://seagecko.org/thoughts/
Generally I believe it's best to avoid using reserved words as table
names (obviously GROUP is one of those on any database because of
GROUP BY, as is SELECT, WHERE, and so on). Usually the recommendation
I've heard is to choose a different name for the table or column,
rather than trying to sneak around the reserved word by quoting or
qualifying that name. It eventually comes back to bite you, no matter
what you do.
That said, it would still probably be nice if rails fully-qualified
the names of database objects to avoid collisions.
- Charlie
···
On Wed, 6 Oct 2004 04:00:52 +0900, Jamis Buck <jgb3@email.byu.edu> wrote:
Thomas Fini Hansen wrote:
> No the right solution would be to quote the column names:
>
> UPDATE
> `group` = 'test'
> WHEN `id`='1'
>
> That ` is annoying on keyboards with dead keys though.
>
Seems to me the "standard" (as if there were really a reliable SQL
standard) is to double quote column and table names (if quoting is
needed), and single quote string literals:
UPDATE the_table
SET "group" = 'test'
WHEN id=1
- Jamis
--
Jamis Buck
jgb3@email.byu.edu
http://www.jamisbuck.org/jamis
--
Charles Oliver Nutter
headius@headius.com
I've always done it like this:
UPDATE [the_table]
SET [group] = 'test'
WHEN [id]=1
This may not be the standard, but it works with MS Access and MS SQL
Server. Any databound tools that I write escape all table and field
names this way.
-- Wes
···
On Wed, 6 Oct 2004 04:00:52 +0900, Jamis Buck <jgb3@email.byu.edu> wrote:
Thomas Fini Hansen wrote:
> No the right solution would be to quote the column names:
>
> UPDATE
> `group` = 'test'
> WHEN `id`='1'
>
> That ` is annoying on keyboards with dead keys though.
>
Seems to me the "standard" (as if there were really a reliable SQL
standard) is to double quote column and table names (if quoting is
needed), and single quote string literals:
UPDATE the_table
SET "group" = 'test'
WHEN id=1
Jamis Buck wrote:
Seems to me the "standard" (as if there were really a reliable SQL standard) is to double quote column and table names (if quoting is needed), and single quote string literals:
UPDATE the_table
SET "group" = 'test'
WHEN id=1
SQL Server also support putting the column name (or anything else, for that matter) in brackets:
UPDATE [mydb].[dbo].[the_table]
SET [group = 'test'
WHEN [Id] = 1
and the like...
···
--
She drove a Plymouth Satellite
Faster than the Speed of Light...
http://www.joeygibson.com/blog
Atlanta Ruby User Group http://www.AtlRUG.org