[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.ruby

Rubyonrails - SQL change request

Jim Freeze

10/5/2004 2:15:00 PM

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
9 Answers

Ara.T.Howard

10/5/2004 2:38:00 PM

0

Jim Freeze

10/5/2004 3:40:00 PM

0

* 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

Jim Freeze

10/5/2004 3:43:00 PM

0

* 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

Justin Rudd

10/5/2004 5:04:00 PM

0

> 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...

Thomas Fini Hansen

10/5/2004 6:45:00 PM

0

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?


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.

--
Thomas
beast@system-tnt.dk

Jamis Buck

10/5/2004 7:01:00 PM

0

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...

Charles O Nutter

10/5/2004 7:08:00 PM

0

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...
>
>


--
Charles Oliver Nutter
headius@headius.com

Wes Moxam

10/5/2004 7:15:00 PM

0

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
>

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

Joey Gibson

10/5/2004 7:17:00 PM

0

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.joeygibso...
http://www.joeygibso.../life/Wisdom.html
Atlanta Ruby User Group http://www....