Problem with numeric scalars in an SQLite DB

hex on 2005-04-14T12:04:15

My module Locale::Object comes with an accompanying SQLite database that contains a bunch of locale information. Since I released it, SQLite has had a major upgrade from v2.8 to v3.0 and the database format has completely changed. So, I'm in the process of making a new release with a db file in the new format. (I should have done this earlier, actually, since I've had a couple of emails about it. My apologies to anyone I've inconvenienced.)

Thing is, I've hit a problem. The process of dumping the old database and reading it into the new format by doing sqlite OLD.DB .dump | sqlite3 NEW.DB worked just fine. However, some of my data changed.

Some of the tables in the db contain a bunch of three-digit ISO codes used to identify countries and so on. Quite a few of these codes start with a zero. On checking the newly created database with sqlite, though, I discovered that all the leading zeroes had vanished.

Talking to DrForr on #perl, I discovered that SQLite now has a concept of column types, and so I recreated the tables in question, changing the columns to be TEXT char(3), thinking that this restriction would force it not to consider the values as numbers. But that didn't work:

foreach (@rows)
{
  my ($code_alpha2, $code_alpha3, $code_numeric, $name, $dialling_code) = @{$_};
 
  # Yes, this is crap, but I don't care right now:
 
  $code_numeric = '0' . $code_numeric if length($code_numeric) == 2;
  $code_numeric = '00' . $code_numeric if length($code_numeric) == 1;
 
  print "country: $code_alpha2 / $code_numeric\n" if $code_numeric =~ /^0/;
 
  my $sth_update = $dbh_new->prepare("INSERT INTO country VALUES (?,?,?,?,?)");
 
  $sth_update->execute($code_alpha2, $code_alpha3, $code_numeric, $name, $dialling_code);
}


The kind of stuff that gets printed to STDOUT is country: bm / 060, but when I check the database in sqlite, it says bm|bmu|60|Bermuda|1441. Still stripping the zeroes!

I've tried a few different approaches, but none have worked. Leon suggested overloading "" to force stringification(?), but I'm finding it extremely hard to understand either what perldoc overload or the Camel have to say about overloading.

Can any of you help?

---

Maybe

phillup on 2005-04-14T12:59:52

Warning: I know nothing about SQLite!

However, sometimes I've had something similar happen when using DBI and trying to convince MySQL to store data a specific way. (For example, when you have an enum column type with a value of '1' as a possible entry. You can also deal with enum columns by position and sometimes MySQL thinks you want the first enum value.)

How I overcome it is by "forcing" the datatype like so:

  $string . ''

Use this instead of $string where you want it to specifically be considered text.

Likewise, something like this:

  $number + 0

Seems to force the value of $number to be treated as a number.

HTH

Re:Maybe

hex on 2005-04-14T16:11:23

No dice. Thanks for the idea though.