Re: Database SQL problem....


Brian McCauley wrote:
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?

my error here.... learning and stiching things together from different
sources, including not only those that have worked in the past, but
books, examples, etc.... i know that /g is a global modifier that would
then grab the next piece of data (...?). so again, sorry, no /g
required....


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
}



absolutely correct, don't want to do the work if the match doesn't
work.... i had it backwards, or was doing more work than required
:o).... again i am going to chalk this up to learning... i'll try not
to make the same mistake twice, thanks.... i have also gone and cleaned
up any other matches


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;


i had been using a placeholder at one point.... trying different
itterations to get it working. this just happened to be where is "was"
when i posted it.... again thanks.... i have gone back and changed to
using placeholders....



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

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

good question.... i don't know either :o). this is where i need real
help! what i want is a particular value from the Db.... and i want to
make sure that there is only one. and to be honest, i don't even know
where to start here..... again, i have tried many different iterations
not being able to get any of them to work.... hashes, arrays, etc. Most
of the attempts have been by grabing examples and from books... just
can't figure it out and get the right syntax.... i want to think that i
might have had ti at one point but didn't have the sytax or vise
versa.... :o)


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?


ok, i put in placeholders, etc.... and i put in a $sth->finish;
statement. i have also not actually attempted the "update".... i'm
sorry. i have that commented out, trying to get the select to work
first :o).

in the end, what i am trying to do is to read my data and grab the
variables with the regex.... then i want to take one of the variables
($winery) and search the Db for it's $winery_id (there should only be
one, it's an integer, and it's unique).... if it returns a value, i
then want to take that and insert to another table (wine...) whe $wine
and $winery_id for association... :o)

so any help re: code to grab/do this is grately appreaciated


.... 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.

.