Database SQL problem....

jim.goodman · May 31, 2006

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><\/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__


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

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

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

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

 



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

Brian McCauley · May 31, 2006

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.

jim.goodman · May 31, 2006

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

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

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

)

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

Click to expand...

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

).

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

)

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

Can someone tell me if this a real tracker? Or is it one designed to show you a different message at certain times, ie. acting like one?	0	Jan 10, 2021
Can anyone please help? HTML - two tables applying different styles	4	Dec 1, 2020
SendGrid email issue in responsive Gmail	1	Nov 4, 2021
HTML Table Issue	1	Aug 29, 2022
I need help fixing my website	2	Oct 15, 2023
Help with my responsive home page	2	Dec 14, 2022
Nested Loop Insert Page Break	1	Nov 5, 2021
Help with Visual Lightbox: Scripts	2	May 3, 2023

Database SQL problem....

jim.goodman

Brian McCauley

jim.goodman

Ask a Question

Similar Threads

Members online

Forum statistics

Latest Threads