searching through multiple sqlite databases

baxy77bax has asked for the wisdom of the Perl Monks concerning the following question:

i'll go right to the point. my code is this:


use DBI;
...


$self->connectdb(connection => "./db/nr1.db");
$self->createtable(table => $joinTable,
                   form => $form1);
                          
my $insert = "insert into GITAXNR(gi,ID,quest) select GITAX.gi, GITAX.
+ID, NRP.quest from GITAX inner join NRP on GITAX.gi = NRP.gi";
$self->do_it(argument => $insert);
[download]

up tu here everything is going fine, it connects to the database nr1.db , creates a table GITAXNR(gi,ID,quest), inserts into it all queried valeus ... ta, ta, ta,...

 
   
    for(my $j = 1; $j<=4; $j++){

        unless($j == 1){ 
            
        my $database = "attach database './db/nr$j.db' as nrdb$j";
        $self->do_it(argument => $database);

        my $insert = "insert into GITAXNR(gi,ID,quest) select GITAX.gi
+, GITAX.ID, NRP.quest from GITAX inner join nr$j.NRP on GITAX.gi = NR
+P.gi";
          $self->do_it(argument => $insert);

}
}
[download]

here the problems start. when i use just the sqlite engine and attach databases the query :

insert into GITAXNR(gi,ID,quest) select GITAX.gi, GITAX.ID, NRP.quest 
+from nr1.GITAX inner join nr2.NRP on GITAX.gi = NRP.gi
[download]

works perfectly. but when i incorporate it into the code it complains that there is no nr$j (nr2) database (DBD::SQLite::db prepare failed: no such table: nr2.NRP(1) at dbdimp.c line 271 a t Lib/NRDB/NrDBI.pm line 70, <NRLOG> line 15.)

so the problem is obviously in attaching or querying the attached databases. so if anyone has an idea on how to do this...

i looked through SQLite module and i couldn't find anything useful

suggestions ... ?

thank you !

Comment on searching through multiple sqlite databases - attach problem Select or Download Code

Replies are listed 'Best First'.
Re: searching through multiple sqlite databases - attach problem by apl (Monsignor) on Nov 13, 2008 at 13:15 UTC
The following code `for(my $j = 1; $j<=4; $j++){ unless($j == 1){ } }` [download] is equivalent to `my $j = 1;` [download] You're looping four times, but doing processing only on the first loop. That's why you don't see the second table you believe you're creating. I'd also suggest adding `use strict; use warnings;`. Revised: to be a little clearer. Revised (again): A better loop, btw, would be: `for my $j ( 1 .. 4 ) {}`	[reply] [d/l] [select]
Re^2: searching through multiple sqlite databases - attach problem by Anonymous Monk on Jul 05, 2011 at 02:03 UTC
Nope: you are looping only three times - you miss `$j == 1`. Instead of writing " `for (my $j == 1; $j <=4; $j++) {...}` [download] " you could write " `for (my $j = 2; $j < 5; $j++) { ... }` [download] "	[reply] [d/l] [select]
Re: searching through multiple sqlite databases - attach problem by Anonymous Monk on Nov 13, 2008 at 12:39 UTC
Please write a self-contained example that demonstrates the problem (something we can test), because its looking more like you have a typo than there is bug with sqlite.	[reply]