Rubyonrails - SQL change request

Discussion in 'Ruby' started by jim@freeze.org, Oct 5, 2004.

  1. Guest

    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
    , Oct 5, 2004
    #1
    1. Advertising

  2. Guest

    On Tue, 5 Oct 2004 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?


    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
    ===============================================================================
    , Oct 5, 2004
    #2
    1. Advertising

  3. Guest

    * <> [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
    , Oct 5, 2004
    #3
  4. Guest

    * <> [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
    , Oct 5, 2004
    #4
  5. Justin Rudd Guest

    > 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/
    Justin Rudd, Oct 5, 2004
    #5
  6. On Tue, Oct 05, 2004 at 11:15:08PM +0900, 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
    Thomas Fini Hansen, Oct 5, 2004
    #6
  7. Jamis Buck Guest

    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

    http://www.jamisbuck.org/jamis
    Jamis Buck, Oct 5, 2004
    #7
  8. 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 <> 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
    >
    > http://www.jamisbuck.org/jamis
    >
    >



    --
    Charles Oliver Nutter
    Charles O Nutter, Oct 5, 2004
    #8
  9. Wes Moxam Guest

    On Wed, 6 Oct 2004 04:00:52 +0900, Jamis Buck <> 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
    Wes Moxam, Oct 5, 2004
    #9
  10. Joey Gibson Guest

    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
    http://www.joeygibson.com/blog/life/Wisdom.html
    Atlanta Ruby User Group http://www.AtlRUG.org
    Joey Gibson, Oct 5, 2004
    #10
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Gully Foyle

    rubyonrails and cgikit comparison

    Gully Foyle, Jul 25, 2004, in forum: Ruby
    Replies:
    49
    Views:
    382
    Thomas Leitner
    Jul 30, 2004
  2. Javier Valencia

    Rubyonrails

    Javier Valencia, Feb 24, 2005, in forum: Ruby
    Replies:
    7
    Views:
    97
    George Moschovitis
    Feb 24, 2005
  3. Arie Kusuma Atmaja
    Replies:
    0
    Views:
    91
    Arie Kusuma Atmaja
    Mar 15, 2005
  4. David M
    Replies:
    0
    Views:
    81
    David M
    Mar 20, 2005
  5. Aquila

    Planet RubyOnRails

    Aquila, Apr 19, 2005, in forum: Ruby
    Replies:
    0
    Views:
    88
    Aquila
    Apr 19, 2005
Loading...

Share This Page