Re: Database SQL problem....

jim.goodman@xxxxxxxxx wrote:

my $winedata=$1 if ($data=~ /wibble/isg);

Never use my() in a stement with a postcondition. Perl really should
throw an error or at the very least a warning if you do but it doesn't
(yet).

What do you think that /g is doing?

I try to avoid $1 etc wherever possible.

If you want only one match:

my ($winedata) = $data=~ /wibble/is;

If you want all matches then you can't easily avoid using $1:

while ( $data=~ /wibble/isg ) {
my $winedata=$1;

my ($winery, $wine, $vintage)=($1, $2, $3) if
($winedata=~ /(.*?)\&nbsp\;(.*?)\&nbsp\;(.*?)</is);

Likewise don't use my() with if postcondition.

You probably don't want to do that if the match failed, so move the
rest inside of the if.

if ( my ($winery, $wine, $vintage)= $winedata=~
/(.*?)\&nbsp\;(.*?)\&nbsp\;(.*?)</is ) {
# do stuff with $winery, $wine, $vintage
}

Alternatively if you believe the patern match can't reasonably fail:

my ($winery, $wine, $vintage)= $winedata=~
/(.*?)\&nbsp\;(.*?)\&nbsp\;(.*?)</is or die;
# do stuff with $winery, $wine, $vintage

my $sql = qq{
SELECT winery_id
FROM winery
WHERE winery='$winery'
};
my $sth = $dbh->prepare($sql);
$sth->execute() || die ("Query failed");

You really should use a placeholder in your SQL so that it doesn't
matter if $winery contains quote characters.

my $sql = qq{
SELECT winery_id
FROM winery
WHERE winery=?
};
my $sth = $dbh->prepare($sql) or die $dbh->errstr;
$sth->execute($winery) or die $sth->errstr;


my @winery_id = $sth->fetchrow_array();

Why are you using an array when you want a scalar?

print "$winery\t$winery_id[0]\n";
my $rows = $dbh->do("UPDATE wine SET
winery_id='$winery_id[0]' WHERE winery='$winery' LIMIT 1");

You should use placeholders again.

You haven't finished $sth. Are you sure your database supports
mulitple concurrent statements? Did do() return an error condition? If
so what was it?

.... but
where i am having problems is the code towards the end, post the SQL
statement, where i am trying to get a value from the Db and store it
then use it as a variable, along with other variables that i have
gotten from extracting text from the file....

OK, that's _where_ you are having problems.

please let me know if i can answer any other questions so as to get a
more complete/full answer.

Perhaps you could mention _what_ problems you are having.

.