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

Hi monks

I have been trying to use Perl to make my life easier again. I must take the webpages from a database for which i have their index. The index numbers are in a text file and they are separated by anew line for each. For example: 

5 
7 
9 
. 
. 
.

[download]
I should take webpages with ID's 5,7,9 and save them in a text file. My code is: 
#!/usr/bin/perl
 use warnings;
 use strict;
 
 use DBIx::Simple;
 
 my $db = DBIx::Simple->connect('dbi:SQLite:dbname=mydatabase.db')
        or die DBIx::Simple->error;
        
 open (WEBPAGESFORALL, "<webpages.txt") || die "couldn't open the file
+!";        
 
  my @webpages = <WEBPAGESFORALL>;
  close(WEBPAGESFORALL);
 
 my $webpage_no = @webpages;
  
 my @webpages_for_files;
 
 print @webpages;
 
 for (my $count=0; $count <= $webpage_no; $count++) {
   $webpages_for_files[$count] = $db->query("SELECT webpage FROM webpa
+ges_data WHERE id = $webpages['$count']'")->list;
 }
 
  print @webpages_for_files;
 
 open (WEBPAGES_OUT,">giveit.txt") || die "couldn't open the file!";
 
 foreach my $webp(@webpages_for_files) {
    print $webp ."\n";
 }

 close(WEBPAGES_OUT);

[download]
The script works fine up to where i print the @webpages array and it comes up fine. The problem is that for some reason the data do not get retrieved from the database and into the @webpages_for_files array so when i print that it gives the warning "Use of uninitialized value".

The thing is i have been using the snippet for the database in another file and it worked fine. Also, for some reason when the numbers in the @webpages array are printed the number in the last element is printed after the many "Use of uninitialized value" warnings. What goes wrong?Thanks for the help

Replies are listed 'Best First'.
Re: Script won't retrieve data from database
by eric256 (Parson) on Apr 28, 2006 at 19:25 UTC

    You have a couple of issues that once fixed will probably help you find the problem (or fix the problem. You arn't chomping the lines so you will have '5\n' instead of just '5' which is probably messing up the query. You are also using a C style loop instead of more perlish way that will require less variables ;) So here is your code with some minor changes that shouldn't break anything:

    #!/usr/bin/perl
use warnings;
use strict;

use DBIx::Simple;

my $db = DBIx::Simple->connect('dbi:SQLite:dbname=mydatabase.db')
      or die DBIx::Simple->error;

#switch to 3 arg form, no advantage here but its a good habit ;)
open (WEBPAGESFORALL, "<", "webpages.txt") || die "couldn't open the f
+ile!";
my @webpages = <WEBPAGESFORALL>;
close(WEBPAGESFORALL);

#remove trailing newlines
chomp(@webpages);

my @webpages_for_files;

#print out the id's of the pages we want, seperated by commas
print join(",", @webpages);

#loop over the indexs we just grabbed from the file
for (@webpages) {
 #push this page onto our list of saved pages.
  push @webpages_for_files, $db->query("SELECT webpage FROM webpages_d
+ata WHERE id = ?", $_)->list;
  #change the query to use place holders
}


print join(",", @webpages_for_files);

#three arg form
open (WEBPAGES_OUT,">", "giveit.txt") || die "couldn't open the file!"
+;
foreach my $webp (@webpages_for_files) {
  print $webp ."\n";
}

close(WEBPAGES_OUT);

    [download]

    ___________
    Eric Hodges
[reply]
[d/l]
      Thanks to everyone for the replies. I have found that the problem is the missing chomp command  chomp(@webpages); but its good to see more effective ways of doing things as well. Thanks
[reply]
[d/l]
Re: Script won't retrieve data from database
by InfiniteSilence (Curate) on Apr 28, 2006 at 18:55 UTC
    I think by now one of the other responses may have resolved your problem. My concern here is that your solution doesn't make much sense from a performance perspective. If I absolutely HAD to read from a file, the first thing I would probably do in your case is something like this: 
    use DBI;
my $pathname = qq|webdb.db.db3|;
my $cnstr = qq|DBI:SQLite:dbname=$pathname|;

my $filename = q|afile.txt|;

open(H, qq|afile.txt|) or die $!;
my @lines = <H>;
close(H);

my $IN = '(';
$IN .= join  ',', map { qq|\'$_\'|} grep {chomp} @lines;
$IN = 'select webpage from webpages_data WHERE id in ' . $IN . ')';
print $IN;
#produces : select webpage from webpages_data WHERE id in ('koko.htm',
+'koko1.htm','koko2.htm','koko3.htm')

    [download]
    This will reduce the number of separate calls to the database. Second, I simply do not know why in the world you would reduce the performance of your solution to reading a text file when you already have a database connection open. Just create another table with your indexes and join the two, like: 
    select webpage from webpages_data, mySelectedStuff WHERE webpage.id = 
+mySelectedStuff.id AND ...

    [download]
    Or a sub-select cannot have much worse performance: 
    select webpage from webpages_data where webpage in (select id from myS
+electedStuff WHERE ...)

    [download]

    Celebrate Intellectual Diversity

[reply]
[d/l]
[select]
Re: Script won't retrieve data from database
by HollyKing (Pilgrim) on Apr 28, 2006 at 18:29 UTC

    I believe your quoting of the parameter in query() is wrong. You shouldn't quote $count1 in the string.

    Owl looked at him, and wondered whether to push him off the tree; but, feeling that he could always do it afterwards, he tried once more to find out what they were talking about.

[reply]
[d/l]
      Speaking of which, where does $count1 come from, anyway? Did you (OP) mean $count?

      ---
      It's all fine and dandy until someone has to look at the code.
[reply]
[d/l]
[select]
      Thanks for the comment but it doesn't make any difference. I get the same result
[reply]
Re: Script won't retrieve data from database
by ruzam (Curate) on Apr 28, 2006 at 18:31 UTC 
    $webpages_for_files[$count] = $db->query("SELECT webpage FROM webpages
+_data WHERE id = $webpages['$count1']'")->list;

    [download]
    You've enclosed $count1 inside single quotes. So instead of getting the value of the array element $count1, you're attempting to get the value of the index string '$count1', which will always equate to 0 (besides I think you meant $count, not $count1).

[reply]
[d/l]
Re: Script won't retrieve data from database
by CountOrlok (Friar) on Apr 28, 2006 at 18:33 UTC
    Did you try:
    chomp(my @webpages = <WEBPAGESFORALL>);

    [download]
    Also, shouldn't $webpages['$count1'] be $webpages[$count].
    -imran
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom