CODEWITHSUNDEEP
perlsqlitedbi
Darius
Darius

Reputation: 155

Perl DBI SQLite different table names in different perl versions

I have a problem I could not solve on my own by now. I have several machines with perl 5.10.0 and 5.10.1 and one task that should work with both perl versions. The task is to merge 2 or more database-files, so to put all tables in one file. Therefore I am usign the perl DBI module. Here is a part of my code:

my $driver   = "SQLite";
my $database = "$dbb"; 
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh5 = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
                  or die $DBI::errstr;

#getting table name B:
my @tablesb = $dbh5->tables();

Now the array tablesb should have all the tables of one database-file (there is only one table in each database-file). I am doing this step to get the table name. Now the real problem:

-With perl 5.10.1 I get 3 tables: "main.sqlite_master", "temp.sqlite_temp_master", "main.param_48". The "param48" is the "real" name of the table.

-With perl 5.10.0 I get: "sqlite_master", "sqlite_temp_master", "param_48".

Does anyone know, why I get different table names and how I can fix this. Or even better, how can I get the "real" table name? Now I use the third entry of this array and split it on the dot "."

Upvotes: 0

Views: 461

Answers (1)

pilcrow
pilcrow

Reputation: 58741

Try this:

my $sth = $dbh->table_info('%', '%', '%');  # Any catalog, any schema, any object
my @tables;
while (my $r = $sth->fetchrow_hashref()) {
  push @tables, $r->{TABLE_NAME};
}

The "real" table name is the qualified table name from SQLite's perspective (e.g., main.tbl). You just want the unqualified object name, which you can get from table_info().

UPDATE

I think the reason you're seeing different behavior is that your two perls are loading different versions of DBD::SQLite (not of SQLite itself, but of the perl driver). It looks like DBD::SQLite's implementation of table_info() became aware of sqlite's "main" and "temp" schemas sometime around version 1.26. Since tables() is implemented in terms of table_info(), the older driver could not have returned schema.object qualified names.

Upvotes: 1

Related Questions