All the Perl that's Practical to Extract and Report

Selecting reserved word columns in SQLite

waltman on 2008-08-06T01:38:11

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.

Portable quoting?

ddick on 2008-08-06T04:12:44

depending on if you were writing for another database in the future, you might want to take advantage of

$dbh->get_info(29)

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)

There is a good reason to always quote

btilly on 2008-08-06T04:54:26

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.

Re:There is a good reason to always quote

ddick on 2008-08-06T05:54:21

You could also use NAME_lc and friends to specify whether names should be returned as lower or upper case.

Re:There is a good reason to always quote

btilly on 2008-08-06T06:00:50

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.

Careful about the case!

bart on 2008-08-06T21:49:33

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.

Bookmark reserved-word-named column info

mr_bean on 2008-08-07T00:32:36

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