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 ]

hex (3272)

  (email not shown publicly)

Perl, RDF and wiki hacker, London, UK. This is my former Perl blog; I now write at Earle's Notebook [].

Journal of hex (3272)

Thursday April 14, 2005
07:04 AM

Problem with numeric scalars in an SQLite DB

[ #24182 ]
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?

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.
  • 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 consider