Re^3: Updating or Inserting a database from a txt file

I had some small errors. Now the script is like this:

my ($gamename, $gamedesc, $gamecount);
my $select = $dbh->prepare( "SELECT count(*) FROM games WHERE gamename
+=?" );
my $insert = $dbh->prepare( "INSERT INTO games (gamename, gamedesc, ga
+mecounter) VALUES (?, ?, ?)" );
my $update = $dbh->prepare( "UPDATE games SET gamedesc=?, gamecount=?+
+1 where gamename=?" );

open (FILE, "<../data/games/descriptions.txt") or die $!;
    while (<FILE>) {
        chomp;

        ($gamename, $gamedesc) = split /\t/;

        if ( $select->execute($gamename) ) {
           $update->execute( $gamedesc, $gamecount, $gamename );
        }
        else {
           $insert->execute( $gamename, $gamedesc, 0 );
        }
    }
close (FILE);
[download]

When it comes to updating it alwasy about the last game of the txt file and never the rest of them?
I cant see why!

Comment on Re^3: Updating or Inserting a database from a txt file Download Code

Replies are listed 'Best First'.
Re^4: Updating or Inserting a database from a txt file by bart (Canon) on May 12, 2005 at 07:36 UTC
I don't think `$select->execute($gamename)` returns the first (and in fact only) row. Instead, it returns a "success" value, which will always be true, BTW. You have to ask for the value of the first (and only) row. You can do that, for example, using `$select->execute($gamename); my($rows) = $select->fetchrow_array; # first row $select->finish; # premature abort: no more rows if($rows) { # found $update->execute( $gamedesc, $gamecount, $gamename ); } else { $insert->execute( $gamename, $gamedesc, 0 ); }` [download] The `finish` tells DBI and the DB you no longer need this instance of the statement handle query. You normally have to call this after a `SELECT` query where you don't try to deplete the statement handle, fetching rows, thus until it returns `undef`. BTW untested, I hope I got it right! :)	[reply] [d/l] [select]
Re^4: Updating or Inserting a database from a txt file by Nik (Initiate) on May 12, 2005 at 07:37 UTC
Adams aproach is better than mines and i think ill follow it but iam getting the following errors: called with 1 bind variables when 3 are neededcalled with 1 bind varia +bles when 3 are neededcalled with 1 bind variables when 3 are neededc +alled with 1 bind variables when 3 are neededcalled with 1 bind varia +bles when 3 are neededcalled with 1 bind variables when 3 are neededc +alled with 1 bind variables when 3 are neededcalled with 1 bind varia +bles when 3 are neededcalled with 1 bind variables when 3 are neededc +alled with 1 bind variables when 3 are neededcalled with 1 bind varia +bles when 3 are neededcalled with 1 bind variables when 3 are neededc +alled with 1 bind variables when 3 are neededcalled with 1 bind varia +bles when 3 are neededcalled with 1 bind variables when 3 are neededc +alled with 1 bind variables when 3 are neededcalled with 1 bind varia +bles when 3 are neededcalled with 1 bin [download] Which i dont understand....	[reply] [d/l]