Slash Boxes
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

waltman (335)

  (email not shown publicly)

Journal of waltman (335)

Tuesday August 05, 2008
08:38 PM

Selecting reserved word columns in SQLite

[ #37108 ]
This week I've been accessing an SQLite database created by a Ruby on Rails app written by one of my labmates. It turned out that one of the tables had a column named "order", and another had a column named "group". These were perfectly reasonable names based on the data; however, they're also reserved words in SQL, which makes them tricky to access.

The trick is to wrap the bad column names in double quotes. In other words, instead of using

SELECT foo, bar, order FROM baz;

which returns an error, use

SELECT foo, bar, "order" FROM baz;

In fact, looking through the schemas it appears that RoR wraps all the column names in quotes. I suppose this must be so that RoR developers can name their fields anything they want without having to worry about the underlying implementation in SQLite.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
More | Login | Reply
Loading... please wait.
  • depending on if you were writing for another database in the future, you might want to take advantage of


    or in longer form ...

    $dbh->get_info($DBI::Const::GetInfoType::GetInfoType{'SQL_IDENTIFIER_QUOTE_CHA R'})

    giving you the correct quoting character for the database (assuming the driver supports it)

  • Many databases have field names that are deliberately case insensitive. Depending on the database you're liable to get your field names back in either upper or lower case. Which one you get depends on the database you have.

    If you quote your field names then you will always get them back in the case you sent them as. This is much more convenient for an automated system.

    • You could also use NAME_lc and friends to specify whether names should be returned as lower or upper case.
      • And now the framework has to translate from the canonical case back to the original case that the programmer specified and is expecting. (Ruby is case sensitive, just like Perl is.)

        While there are other ways to work around it, the simplest way is just to quote the field name and watch problems disappear on you.

  • It probably depends on what database you use... But at least in Oracle, the case of the column names is treated differently for unquoted and for quoted names.
    • An unquoted column name is internally converted to upper case
    • A quoted column name is case preserved

    So you may have to change "order" to "ORDER" to get the same case for both quoted and unquoted column names.

  • This is good to know. I am commenting here
    so I can find it later.