Checking for entry in table?

I'm still trying to check whether a record exist in a table, and act =
accordingly. While I appreciate Garry's suggestion, I want to understand =
it fully, and I'm stuck.

My program has this loop in it:
my $kwsth =3D $dbh->prepare("SELECT keywordid FROM keywords WHERE =
TRIM(keyword)=3D?");
while (<>) { #While there's more lines in the file called on the command =
line, of POPLINE document numbers and keywords
$ln++;
my ($popno, $kws) =3D split("\t"); #split on the tab following the =
POPLINE number
chomp($kws);
print "$popno:\n" if $debug;
my (@keywords) =3D split('\|', $kws); #split on the pipe symbol that =
separates keywords
foreach (@keywords) {
$_ =3D uc($_);
print "\t$_ " if $debug;
my $kwsthrv =3D $kwsth->execute($_) or warn "Problem with execute: =
$DBI::errstr\n";
if (! $kwsthrv) { print "Keyword $_ not found\n" } else {
my $keywordid =3D $kwsth->fetchrow_array or warn "Problem with =
fetch: $DBI::errstr\n";
print "Keyword ID: $keywordid\n" if $debug;
}
} #foreach keyword in the array of keywords
} #while there are more lines of input on the command line

The datafile looks like this:
kevinz@www:~/public_html/orderDB/scripts$ cat ../tmp/w=20
051877 Primary Health Care|Family Planning|Dummy
kevinz@www:~/public_html/orderDB/scripts$=20

The output looks like this:
kevinz@www:~/public_html/orderDB/scripts$ ./loadPOPkeywords.pl ../tmp/w
051877:
PRIMARY HEALTH CARE Keyword ID: 1791
FAMILY PLANNING Keyword ID: 743
Use of uninitialized value in concatenation (.) or string at ./loadPOPkeywo=
rds.pl line 35, <> line 1.
Problem with fetch:=20
Use of uninitialized value in concatenation (.) or string at ./loadPOPkeywo=
rds.pl line 36, <> line 1.
DUMMY Keyword ID:=20
kevinz@www:~/public_html/orderDB/scripts$

I want to detect, at either the execute or fetch phase, whether or not the =
record exists, and then either add it and get the mysql_insertid, or just =
fetch the id of the already inserted record. I don't want the errors that =
show up, or the warn() execution.

Thanks for your help and suggestions.

-Kevin Zembower


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: Checking for entry in table?

Something like this slight modification should work:

while (<>) {
$ln++;

my ($popno, $kws) = split("\t");
chomp($kws);

print "$popno:\n" if $debug;

my (@keywords) = split('\|', $kws);

foreach my $kw (@keywords) {
my $KEYWORD = uc($kw); #Easier to read
print "\t$kw " if $debug;

my $kwsth = $dbh->prepare("SELECT keywordid FROM keywords WHERE TRIM(keyword)='$KEYWORD'");
$kwsth->execute;

my $Found = "";
while (my @data = $sth_GL->fetchrow_array()) {
$Found = $data[0];
}

if ($Found ne "") {
print "Keyword ID: $Found\n" if $debug;
}
else {
print "Record not found;\n";
}


}


}





-------------------------------
--- KEVIN ZEMBOWER wrote:
> I'm still trying to check whether a record exist in a table, and act accordingly. While I
> appreciate Garry's suggestion, I want to understand it fully, and I'm stuck.
>
> My program has this loop in it:
> my $kwsth = $dbh->prepare("SELECT keywordid FROM keywords WHERE TRIM(keyword)=?");
> while (<>) { #While there's more lines in the file called on the command line, of POPLINE
> document numbers and keywords
> $ln++;
> my ($popno, $kws) = split("\t"); #split on the tab following the POPLINE number
> chomp($kws);
> print "$popno:\n" if $debug;
> my (@keywords) = split('\|', $kws); #split on the pipe symbol that separates keywords
> foreach (@keywords) {
> $_ = uc($_);
> print "\t$_ " if $debug;
> my $kwsthrv = $kwsth->execute($_) or warn "Problem with execute: $DBI::errstr\n";
> if (! $kwsthrv) { print "Keyword $_ not found\n" } else {
> my $keywordid = $kwsth->fetchrow_array or warn "Problem with fetch: $DBI::errstr\n";
> print "Keyword ID: $keywordid\n" if $debug;
> }
> } #foreach keyword in the array of keywords
> } #while there are more lines of input on the command line
>
> The datafile looks like this:
> kevinz@www:~/public_html/orderDB/scripts$ cat ../tmp/w
> 051877 Primary Health Care|Family Planning|Dummy
> kevinz@www:~/public_html/orderDB/scripts$
>
> The output looks like this:
> kevinz@www:~/public_html/orderDB/scripts$ ./loadPOPkeywords.pl ../tmp/w
> 051877:
> PRIMARY HEALTH CARE Keyword ID: 1791
> FAMILY PLANNING Keyword ID: 743
> Use of uninitialized value in concatenation (.) or string at ./loadPOPkeywords.pl line 35, <>
> line 1.
> Problem with fetch:
> Use of uninitialized value in concatenation (.) or string at ./loadPOPkeywords.pl line 36, <>
> line 1.
> DUMMY Keyword ID:
> kevinz@www:~/public_html/orderDB/scripts$
>
> I want to detect, at either the execute or fetch phase, whether or not the record exists, and
> then either add it and get the mysql_insertid, or just fetch the id of the already inserted
> record. I don't want the errors that show up, or the warn() execution.
>
> Thanks for your help and suggestions.
>
> -Kevin Zembower
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe: http://lists.mysql.com/perl?unsub=gregmeckes@yahoo.com
>
>



__________________________________
Do you Yahoo!?
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Checking for entry in table?

Something like the following should work:

while (<>) {
$ln++;

my ($popno, $kws) = split("\t");
chomp($kws);

print "$popno:\n" if $debug;

my (@keywords) = split('\|', $kws);

foreach my $kw (@keywords) {
my $KEYWORD = uc($kw);
print "\t$kw " if $debug;

my $kwsth = $dbh->prepare("SELECT keywordid FROM keywords WHERE TRIM(keyword)='$KEYWORD'");
$kwsth->execute;

my $Found = "";
while (my @data = $sth_GL->fetchrow_array()) {
$Found = $data[0];
}

if ($Found ne "") {
print "Keyword ID: $Found\n" if $debug;
}
else {
print "Record not found;\n";
}


}


}


_____________________________________
--- KEVIN ZEMBOWER wrote:
> I'm still trying to check whether a record exist in a table, and act accordingly. While I
> appreciate Garry's suggestion, I want to understand it fully, and I'm stuck.
>
> My program has this loop in it:
> my $kwsth = $dbh->prepare("SELECT keywordid FROM keywords WHERE TRIM(keyword)=?");
> while (<>) { #While there's more lines in the file called on the command line, of POPLINE
> document numbers and keywords
> $ln++;
> my ($popno, $kws) = split("\t"); #split on the tab following the POPLINE number
> chomp($kws);
> print "$popno:\n" if $debug;
> my (@keywords) = split('\|', $kws); #split on the pipe symbol that separates keywords
> foreach (@keywords) {
> $_ = uc($_);
> print "\t$_ " if $debug;
> my $kwsthrv = $kwsth->execute($_) or warn "Problem with execute: $DBI::errstr\n";
> if (! $kwsthrv) { print "Keyword $_ not found\n" } else {
> my $keywordid = $kwsth->fetchrow_array or warn "Problem with fetch: $DBI::errstr\n";
> print "Keyword ID: $keywordid\n" if $debug;
> }
> } #foreach keyword in the array of keywords
> } #while there are more lines of input on the command line
>
> The datafile looks like this:
> kevinz@www:~/public_html/orderDB/scripts$ cat ../tmp/w
> 051877 Primary Health Care|Family Planning|Dummy
> kevinz@www:~/public_html/orderDB/scripts$
>
> The output looks like this:
> kevinz@www:~/public_html/orderDB/scripts$ ./loadPOPkeywords.pl ../tmp/w
> 051877:
> PRIMARY HEALTH CARE Keyword ID: 1791
> FAMILY PLANNING Keyword ID: 743
> Use of uninitialized value in concatenation (.) or string at ./loadPOPkeywords.pl line 35, <>
> line 1.
> Problem with fetch:
> Use of uninitialized value in concatenation (.) or string at ./loadPOPkeywords.pl line 36, <>
> line 1.
> DUMMY Keyword ID:
> kevinz@www:~/public_html/orderDB/scripts$
>
> I want to detect, at either the execute or fetch phase, whether or not the record exists, and
> then either add it and get the mysql_insertid, or just fetch the id of the already inserted
> record. I don't want the errors that show up, or the warn() execution.
>
> Thanks for your help and suggestions.
>
> -Kevin Zembower
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe: http://lists.mysql.com/perl?unsub=gregmeckes@yahoo.com
>
>



__________________________________
Do you Yahoo!?
Yahoo! Sports - Sign up for Fantasy Baseball.
http://baseball.fantasysports.yahoo.com/

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Checking for entry in table?

On Tue, 2005-03-29 at 11:38 -0500, KEVIN ZEMBOWER wrote:
> I'm still trying to check whether a record exist in a table, and act
> accordingly. While I appreciate Garry's suggestion, I want to
> understand it fully, and I'm stuck.

My suggestion is to just insert the row you care about without checking
to see if it is there. If it is already in the table, the insert will
fail on duplicate key (assuming that you have some column(s) defined as
unique or primary key). The failure will produce $sth->err() == 1062,
if the insert is a duplicate. Otherwise, you have your record inserted.

(See the DBI(3) manual page, section "METHODS COMMON TO ALL HANDLES" for
the $h->err() method and see "Chapter 24. Error Handling in MySQL" in
the MySQL manual for a list of the error codes.)

The reason I suggest this approach is that it eliminates a race
condition. Between the time that you select a row which is not found
and the time you insert the record, another process can insert the same
record. You cannot guarantee winning the race.

Another reason to just insert and handle the error is that it is
simpler. There is only one query and the database handles the race
instead of you.

> My program has this loop in it:
> my $kwsth = $dbh->prepare("SELECT keywordid FROM keywords WHERE TRIM(keyword)=?");
> while (<>) { #While there's more lines in the file called on the command line, of POPLINE document numbers and keywords
> $ln++;
> my ($popno, $kws) = split("\t"); #split on the tab following the POPLINE number

The split() function takes a regular expression as its first operand.
You can give it a string and Perl will convert that to a regular
expression, but why not just write it this way to avoid confusion?

split(/\t/);

or simply

split /\t/;

> chomp($kws);
> print "$popno:\n" if $debug;
> my (@keywords) = split('\|', $kws); #split on the pipe symbol that separates keywords

... split /\|/, $kws;

> foreach (@keywords) {
> $_ = uc($_);
> print "\t$_ " if $debug;
> my $kwsthrv = $kwsth->execute($_) or warn "Problem with execute: $DBI::errstr\n";
> if (! $kwsthrv) { print "Keyword $_ not found\n" } else {
> my $keywordid = $kwsth->fetchrow_array or warn "Problem with fetch: $DBI::errstr\n";

The fetchrow_array() method returns a list (even if your select only
asks for one column). You are creating scalar context by assigning it
to a scalar. Although this is defined for a single column query, it is
not what you want.

my ($keywordid) = $kwsth->fetchrow_array;

If there are no rows meeting the select criteria, the fetchrow_array()
method will return an empty list. This is not considered an error (or
problem), but it is a false value. If you get back an empty list the
only way to distinguish no rows from an error is to call $sth->err().

(See "fetchrow_array" and "execute" in the "DBI STATEMENT HANDLE
OBJECTS" section of the DBI(3) manual page.)

[snip]

> I want to detect, at either the execute or fetch phase, whether or not
> the record exists,

The execute() is not the place. See DBI(3).

As mentioned above, fetchrow_array() will return an empty list when
there is no record. That is not an error -- it's expected. But an
empty list will evaluate to false. Your code is assuming that is an
error instead of interpreting it as no record found.

> and then either add it and get the mysql_insertid, or just fetch the
> id of the already inserted record. I don't want the errors that show
> up, or the warn() execution.

--
Garry Williams, Zvolve Systems, Inc., +1 770 813-4934
Cell: +1 404 353-2983


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Checking for entry in table?

On Tue, 29 Mar 2005, Garry Williams wrote:

> My suggestion is to just insert the row you care about without checking
> to see if it is there. If it is already in the table, the insert will
> fail on duplicate key (assuming that you have some column(s) defined as
> unique or primary key). The failure will produce $sth->err() == 1062,
> if the insert is a duplicate. Otherwise, you have your record inserted.

Of course some DBs will abort a transaction in the event of a failed insert
because of the way the SQL spec is written.

> record. You cannot guarantee winning the race.


$dbh->do(q{LOCK TABLE foo})

....

or

$dbh->{AutoCommit} = 0; # use transactions
$dbh->do(q{SET TRANSACTION ISOLATION LEVEL SERALIZABLE}); # paranoid

....

-r


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Checking for entry in table?

On Tue, 2005-03-29 at 17:18 -0500, Rudy Lippan wrote:
> On Tue, 29 Mar 2005, Garry Williams wrote:
>
> > My suggestion is to just insert the row you care about without checking
> > to see if it is there. If it is already in the table, the insert will
> > fail on duplicate key (assuming that you have some column(s) defined as
> > unique or primary key). The failure will produce $sth->err() == 1062,
> > if the insert is a duplicate. Otherwise, you have your record inserted.
>
> Of course some DBs will abort a transaction in the event of a failed insert
> because of the way the SQL spec is written.

I must misunderstand your point. (Which, if you know me is not
surprising. :-) That is exactly what I am advocating. The problem of
insert if not there, retrieve otherwise is a common one. Just insert
and catch the error and make sure it's because of duplicate key. The
MySQL database defines this error as 1062. Catch that error and
retrieve the existing record. If no error, then the insert that you
wanted is done.

At least that's how I understood the original poster's problem.

> > record. You cannot guarantee winning the race.
>
>
> $dbh->do(q{LOCK TABLE foo})
> ...
>
> or
>
> $dbh->{AutoCommit} = 0; # use transactions
> $dbh->do(q{SET TRANSACTION ISOLATION LEVEL SERALIZABLE}); # paranoid

OK, yes I realized that. The original poster was not using
transactions. And I thought that was getting silly just to do an
insert, if and only if the record does not already exist. The insert
must fail, if there's a record there already and it will succeed
otherwise (assuming the disk array didn't catch on fire or
something :-).

--
Garry Williams, Zvolve Systems, Inc., +1 770 813-4934
Cell: +1 404 353-2983


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Checking for entry in table?

On Tue, 29 Mar 2005, Garry Williams wrote:

> > Of course some DBs will abort a transaction in the event of a failed insert
> > because of the way the SQL spec is written.
>
> I must misunderstand your point. (Which, if you know me is not
> surprising. :-) That is exactly what I am advocating. The problem of
> insert if not there, retrieve otherwise is a common one. Just insert
> and catch the error and make sure it's because of duplicate key. The
> MySQL database defines this error as 1062. Catch that error and
> retrieve the existing record. If no error, then the insert that you
> wanted is done.
>

For mysql, yes. But PostgreSQL, for example, would abort the entire transaction
on dup key error. So, with pg after a failed insert, the only thing
that you are allowed to do is rollback() anything else will result in a
Transaction Aborted error ( including commit() ).


-r


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org