Database SQL problem....


J

jim.goodman

I have a script that reads a bunch of files and extracts some data....
I then want to take a piece of that data and do a "select" query on the
Db, get a particular result and then use that for a second sql
statement, an insert/update....

here is my code and data.....

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

my $dir="/Users//Wine/files/";
opendir(DIRECTORY, $dir) || die("Cannot open directory");
my @thefiles= readdir(DIRECTORY);
closedir(DIRECTORY);

my $dsn = "DBI:mysql:database=wine;host=domain.com";
my $dbh = DBI->connect($dsn, "user", "password") || die ("Could not
open DB!");
my $n=0, my $m=0;

foreach my $file (@thefiles) {
my (@lines);
unless ( $file =~ /^\./ ) {
$n++;
open FILE, "$dir/$file" or die "Can't open $file : $!";
while( <FILE> ) {
s/\t//; # ignore tabs by erasing them
next if /^(\s)*$/; # skip blank lines
chomp; # remove trailing newline characters
push @lines, $_; # push the data line onto the array
}
close FILE;
$file=$1 if $file =~ /(.*?)\.html/i;
my $chunk = "@lines";
$chunk =~ s/\s\s\s+//g if $chunk;
$chunk=$1 if $chunk =~ /<\!\-\-.Hitbox.variables:.*?<TR
HEIGHT=15><TD><\/TR>(.*?)<\!\-\-.END.PAGE.BODY.\-\->/;
my @data = ( split /<TR HEIGHT=15><TD><\/TR>/, $chunk );
foreach my $data (@data) {
$m++ if $data;
my $winedata=$1 if ($data=~ /<TR>\s*<td
colspan=\"2\">.*?<b>(.*?<)\/B><\/FONT>\s*<\/TD>/isg);
my ($winery, $wine, $vintage)=($1, $2, $3) if
($winedata=~ /(.*?)\&nbsp\;(.*?)\&nbsp\;(.*?)</is);
my $sql = qq{
SELECT winery_id
FROM winery
WHERE winery='$winery'
};
my $sth = $dbh->prepare($sql);
$sth->execute() || die ("Query failed");
my @winery_id = $sth->fetchrow_array();
print "$winery\t$winery_id[0]\n";
my $rows = $dbh->do("UPDATE wine SET
winery_id='$winery_id[0]' WHERE winery='$winery' LIMIT 1");
}
$dbh->disconnect ();
}
}

__DATA__
<!-- Hitbox variables:
blank; blank; blank-->






<TABLE BORDER="0" WIDTH="100%" cellspacing="0" cellpadding="3">


<TR HEIGHT=15><TD></TR>
<TR><td colspan="2"><FONT FACE="helvetica, arial" color="black"
SIZE="2"> <b>AQUILA&nbsp;Merlot Margaret River
&nbsp;1999</B></FONT></TD>
</TR>
<TR><TD><FONT FACE="helvetica, arial" SIZE="2">Score:
<B>83</B></FONT></TD>
<TD valign="top" WIDTH="25%" ROWSPAN=2>
<table border="0" cellspacing="0" cellpadding="0">
<TR>
<TD><FONT FACE="helvetica, arial" SIZE="2">Price:</FONT> <FONT
FACE="helvetica, arial" color="black" SIZE="2"> $16 </FONT></TD>
</TR>
<TR>
<TD><FONT FACE="helvetica, arial" SIZE="2">Country:</FONT> <FONT
FACE="helvetica, arial" color="black"
SIZE="2">Australia<BR></FONT></TD>
</TR>
<TR>
<TD><FONT FACE="helvetica, arial" SIZE="2">Region:</FONT> <FONT
FACE="helvetica, arial" color="black"
SIZE="2">Australia<BR></FONT></TD>
</TR>
<TR>
<TD><FONT FACE="helvetica, arial" SIZE="2">Issue:</FONT> <FONT
FACE="helvetica, arial" color="black" SIZE="2">Web Only
(2002)<BR></FONT></TD>
</TR></table>
</TD>
</TR>
<TR>
<TD valign="top" WIDTH="75%"><FONT FACE="helvetica, arial"
SIZE="2">Supple, graceful style offers pleasant berry and herb flavors,
finishing with a tang of roasted red pepper. Drink now. 2,000 cases
made. <I>(HS)</I><BR>
</TD>
</TR>

<TR HEIGHT=15><TD></TR>
<TR><td colspan="2"><FONT FACE="helvetica, arial" color="black"
SIZE="2"> <b>BODEGAS 1890&nbsp;Tempranillo Jumilla Don
Luciano&nbsp;1998</B></FONT></TD>
</TR>
<TR><TD><FONT FACE="helvetica, arial" SIZE="2">Score:
<B>73</B></FONT></TD>
<TD valign="top" WIDTH="25%" ROWSPAN=2>
<table border="0" cellspacing="0" cellpadding="0">
<TR>
<TD><FONT FACE="helvetica, arial" SIZE="2">Price:</FONT> <FONT
FACE="helvetica, arial" color="black" SIZE="2"> $5 </FONT></TD>
</TR>
<TR>
<TD><FONT FACE="helvetica, arial" SIZE="2">Country:</FONT> <FONT
FACE="helvetica, arial" color="black" SIZE="2">Spain<BR></FONT></TD>
</TR>
<TR>
<TD><FONT FACE="helvetica, arial" SIZE="2">Region:</FONT> <FONT
FACE="helvetica, arial" color="black" SIZE="2">Spain<BR></FONT></TD>
</TR>
<TR>
<TD><FONT FACE="helvetica, arial" SIZE="2">Issue:</FONT> <FONT
FACE="helvetica, arial" color="black" SIZE="2">Dec 15,
1999<BR></FONT></TD>
</TR></table>
</TD>
</TR>
<TR>
<TD valign="top" WIDTH="75%"><FONT FACE="helvetica, arial"
SIZE="2">This light red has a core of sweet cherry, but it tastes
candied and turns bitter on the finish. 50,000 cases made.
<I>(TM)</I><BR>
</TD>
</TR>

<TR HEIGHT=15><TD></TR>
<TR><td colspan="2"><FONT FACE="helvetica, arial" color="black"
SIZE="2"> <b>BODEGAS 1890&nbsp;Jumilla Castillo San
Simón&nbsp;1997</B></FONT></TD>
</TR>
<TR><TD><FONT FACE="helvetica, arial" SIZE="2">Score:
<B>79</B></FONT></TD>
<TD valign="top" WIDTH="25%" ROWSPAN=2>
<table border="0" cellspacing="0" cellpadding="0">
<TR>
<TD><FONT FACE="helvetica, arial" SIZE="2">Price:</FONT> <FONT
FACE="helvetica, arial" color="black" SIZE="2"> $5 </FONT></TD>
</TR>
<TR>
<TD><FONT FACE="helvetica, arial" SIZE="2">Country:</FONT> <FONT
FACE="helvetica, arial" color="black" SIZE="2">Spain<BR></FONT></TD>
</TR>
<TR>
<TD><FONT FACE="helvetica, arial" SIZE="2">Region:</FONT> <FONT
FACE="helvetica, arial" color="black" SIZE="2">Spain<BR></FONT></TD>
</TR>
<TR>
<TD><FONT FACE="helvetica, arial" SIZE="2">Issue:</FONT> <FONT
FACE="helvetica, arial" color="black" SIZE="2">Oct 31,
1998<BR></FONT></TD>
</TR></table>
</TD>
</TR>
<TR>
<TD valign="top" WIDTH="75%"><FONT FACE="helvetica, arial"
SIZE="2">This light, simple red offers pretty strawberry flavors, with
hints of spice. It has very light tannins and finishes short and clean.
<I>(TM)</I><BR>
</TD>
</TR>
<TR HEIGHT=15><TD></TR>
<TR><td colspan="2"><FONT FACE="helvetica, arial" color="black"
SIZE="2"> <b>ABARBANEL&nbsp;Beaujolais-Villages Mevushal Château de la
Salle&nbsp;2000</B></FONT></TD>
</TR>
<TR><TD><FONT FACE="helvetica, arial" SIZE="2">Score:
<B>82</B></FONT></TD>
<TD valign="top" WIDTH="25%" ROWSPAN=2>
<table border="0" cellspacing="0" cellpadding="0">
<TR>
<TD><FONT FACE="helvetica, arial" SIZE="2">Price:</FONT> <FONT
FACE="helvetica, arial" color="black" SIZE="2"> $9 </FONT></TD>
</TR>
<TR>
<TD><FONT FACE="helvetica, arial" SIZE="2">Country:</FONT> <FONT
FACE="helvetica, arial" color="black" SIZE="2">France<BR></FONT></TD>
</TR>
<TR>
<TD><FONT FACE="helvetica, arial" SIZE="2">Region:</FONT> <FONT
FACE="helvetica, arial" color="black"
SIZE="2">Beaujolais<BR></FONT></TD>
</TR>
<TR>
<TD><FONT FACE="helvetica, arial" SIZE="2">Issue:</FONT> <FONT
FACE="helvetica, arial" color="black" SIZE="2">Sep 15,
2001<BR></FONT></TD>
</TR></table>
</TD>
</TR>
<TR>
<TD valign="top" WIDTH="75%"><FONT FACE="helvetica, arial"
SIZE="2">Kosher. Sweet strawberry and red cherry flavors, with a dash
of spice on the finish. Drink now. 1,000 cases made. <I>(JM)</I><BR>
</TD>
</TR>
</TABLE>

<BR><BR>



<!-- END PAGE BODY -->

__END DATA__

when i execute the code... i get the following error...

Use of uninitialized value in concatenation (.) or string at wine_data
v1.2.pl line 43.

when the data that i want is.... i should have the variable $winery and
the variable $winery_id which i have retrieved from the Db.... if i
were to print them (which i do in the code so that i can watch the
output as there is an insert to the Db....) it would look like this....
AQUILA 26
BODEGAS 1890 14
etc....

i know that the code may or may not be pretty, i am learning.... 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.... thanks for the help and
please let me know if i can answer any other questions so as to get a
more complete/full answer.
 
Ad

Advertisements

B

Brian McCauley

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

Advertisements

J

jim.goodman

Brian said:
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;


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
:eek:).... 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


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

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

good question.... i don't know either :eek:). 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.... :eek:)
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 :eek:).

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... :eek:)

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top