How do I write data in SQLite

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

Hi monks I have read some tutorials on SQLite and i have tried to do some work with it. My code is below:

 
$v = 0; # The v variable will now be used with the database
 my $dbh = DBI->connect( "dbi:SQLite:dbname=mydatabase.db", "", "" )||
+ die "Cannot connect: DBI";

 # If the file exists delete the information in it
 eval 
        {
        local $dbh->{PrintError} = 0;
        $dbh->do("DROP TABLE webpages_data");
    };
    
 # Create a table
 $dbh->do( "CREATE TABLE webpages_data (id INTEGER PRIMARY KEY, page, 
+text, bigr)");

 my $a = 1;

 while(@links) {
     
          
     $dbh->do( "INSERT INTO webpages_data VALUES('$a', '$_', '$text[$v
+]', NULL)");
               
     $a++;
     $v++;            
     }
 
 
 # Disconnect when finished using the database
 $dbh->disconnect();
[download]

I have an array @text so the $v variable is to get the string inside each element of array and try to put it in the database table. The $a variable is to give a unique ID to each row in the table. These two variables work by adding to them 1 every time the loop is due to start again. I also want to keep the 4th column with a NULL value

The problem is i get an error "Use of uninitialized value in concatenation (.) or string". This comes from the INSERT INTO line. I have found previous posts that had this error but none of the solutions seem to help with this situation.

I have also downloaded the SQLite analyser program to see what is in my table but it doesn't bring up anything so i presume the information doesn;t get written in the database file

Any ideas? Thanks

Comment on How do I write data in SQLite Download Code

Replies are listed 'Best First'.
Re: How do I write data in SQLite by jZed (Prior) on Feb 03, 2006 at 23:55 UTC
I think Joost has given the reason for your undefined values warnings. Let me suggest that you use DBI placeholders also: `my $sth = $dbh->prepare(" INSERT INTO webpages_data VALUES(?,?,?,?) "); for(@links) { $sth->execute($a,$_,$text[$v],undef); $a++; $v++; }` [download] Also, stay away from variables named "$a" or "$b" since those have a special meaning for sort().	[reply] [d/l]
Re: How do I write data in SQLite by Joost (Canon) on Feb 03, 2006 at 23:37 UTC
`while(@links) { $dbh->do( "INSERT INTO webpages_data VALUES('$a', '$_', '$text[$v +]', NULL)"); $a++; $v++; }` [download] This loop will never stop unless @links is empty, so somewhere `$text[$v]` will be undefined - when $v exceeds the highest index number in @text (NOT @links, @links is never even used in the loop). update: really, use strict. "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply] [d/l] [select]
Re^2: How do I write data in SQLite by lampros21_7 (Scribe) on Feb 03, 2006 at 23:42 UTC
Well, @links has around 30 elements and the same goes for @text. I thought @links would stop when it has processed all of the elements in @links(in this case it would mean making 30 iterations).	[reply]
Re^3: How do I write data in SQLite by Joost (Canon) on Feb 03, 2006 at 23:45 UTC
It won't because `while (@links)` will continue until @links is empty. You're not doing anything to empty it in the loop - so the loop is endless. "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply] [d/l]
Re^4: How do I write data in SQLite by lampros21_7 (Scribe) on Feb 03, 2006 at 23:59 UTC
Re: How do I write data in SQLite by GhodMode (Pilgrim) on Feb 04, 2006 at 00:00 UTC
Since you know it's on the alert line, you know that the problematic variable is $_ (the current element of @links), or $text[$v]. You know it's not $a or $v because you set them explicitly before the loop starts. Use some print statements on separate lines for each of those variables to see what they contain. When you try to print the one that's uninitialized, you'll get the error message again and you'll know which one is causing the problem. What to do after you find the problematic variable depends on your needs. You could just initialize them all to "" at the beginning of the loop and that would make the error message go away, but you might not want any of those fields to be blank. Here's another method `while(@links) { $_ \|\|= ""; $text[$v] \|\|= ""; $dbh->do( "INSERT INTO webpages_data VALUES('$a', '$_', '$text[$v] +', NULL)"); $a++; $v++; }` [download] `\|\|=` says, for example: `if ( ! $text[$v] ) { $text[$v] = "" };` -- -- GhodMode	[reply] [d/l]
Re^2: How do I write data in SQLite by chromatic (Archbishop) on Feb 04, 2006 at 03:56 UTC
...you know that the problematic variable is $_ (the current element of @links) But it's not the current element of `@links`; that's the problem!	[reply] [d/l]
Re^2: How do I write data in SQLite by lampros21_7 (Scribe) on Feb 04, 2006 at 00:58 UTC
Thanks to everyone for their answers!! Update: For some reason the SQLite Analyser program for showing the contents of the database wouldn't bring up the table properly. I have used SQLite Plus and that works perfectly.	[reply]
Re: How do I write data in SQLite by chargrill (Parson) on Feb 04, 2006 at 00:56 UTC
I think what you REALLY want to do is change your `while` loop to a `for` loop ... `while(@array){}` doesn't set your default iterator (`$_`) to each element of `@array` (and it looks like that's what you want it to do), but a `for` loop will. Update: in this version: `while($s >= $a) { $dbh->do( "INSERT INTO webpages_data VALUES('$a', '$_', '$text[$v +]', NULL)"); $a++; $v++; }` [download] ... I'm not even sure what value `$_` might have during each iteration. --chargrill `$/ = q#(\w)# ; sub sig { print scalar reverse join ' ', @_ } + sig map { s$\$/\$/$\$2\$1$g && $_ } split( ' ', ",erckha rlPe erthnoa stJu +" );` [download]	[reply] [d/l] [select]