Rubyonrails - SQL change request

J

jim

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?
 
A

Ara.T.Howard

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?

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
--
===============================================================================
| 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
===============================================================================
 
J

jim

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

Justin Rudd

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?
 
T

Thomas Fini Hansen

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

Jamis Buck

Thomas said:
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
 
C

Charles O Nutter

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
 
W

Wes Moxam

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
 
J

Joey Gibson

Jamis said:
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
http://www.joeygibson.com/blog/life/Wisdom.html
Atlanta Ruby User Group http://www.AtlRUG.org
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top