Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > DBD::mysql problem

Reply
Thread Tools

DBD::mysql problem

 
 
Stephen Lake
Guest
Posts: n/a
 
      06-20-2005
Hi all,
i am having a small problem with rows in the DBD::mysql module.

more specifically, what I am trying to do is to insert a value in a mysql
table ONLY if it does NOT exist in there already. Now I know the queries are
working ok but its the num rows thats not evaluating properly and thus I am
getting duplicates in the table that I do not want.

I have read the DBD::mysql doc at CPAN but that didn't solve my problem and
have read the perldoc specifically the sections on Operators and Control
Structures and everything here seems to be in accordance with how its
suppose to be done.

Here is a piece of code that I have and that the problem is manifesting
itself in:
======================================
my $qb = $dbh->prepare("SELECT * FROM stockIndex WHERE pSymbol='$sym'");
$qb->execute;
if($qb->rows()==0) {
if(!$dbh->do("INSERT INTO stockIndex(pSymbol) VALUES('$sym')"))
{
print $dbh->errstr;
}
}
$qb->finish();
=======================================

Can anyone please point out the error in here or give me any idea of what is
going wrong?

Thanx
Steve


 
Reply With Quote
 
 
 
 
Paul Lalli
Guest
Posts: n/a
 
      06-20-2005
> i am having a small problem with rows in the DBD::mysql module.

All of the classes and methods you're using come from the 'use DBI'
statement. It is that module with which you are having difficulty, not
DBD::mysql

> more specifically, what I am trying to do is to insert a value in a mysql
> table ONLY if it does NOT exist in there already.


Independent of the Perl-answer to this question, I'd start to wonder if
your algorithm and database structure are sound. Are you sure you
can't restructure so that you don't attempt to add a duplicate value in
the first place? Are you sure you can't restructure so that the value
is a UNIQUE column, and therefore the database prevents the insertion?

> Now I know the queries are
> working ok but its the num rows thats not evaluating properly and thus I am
> getting duplicates in the table that I do not want.
>
> I have read the DBD::mysql doc at CPAN but that didn't solve my problem


DBD::mysql contains only information specific to the database type
you're using. Your problem is with the Perl DBI in general. You
should look at DBI's docs as well.

> Here is a piece of code that I have and that the problem is manifesting
> itself in:
> ======================================
> my $qb = $dbh->prepare("SELECT * FROM stockIndex WHERE pSymbol='$sym'");
> $qb->execute;
> if($qb->rows()==0) {
> if(!$dbh->do("INSERT INTO stockIndex(pSymbol) VALUES('$sym')"))
> {
> print $dbh->errstr;
> }
> }
> $qb->finish();
> =======================================
>
> Can anyone please point out the error in here or give me any idea of what is
> going wrong?


You are making a false assumption about the rows() method. Please read
up on what it actually does:

http://search.cpan.org/~timb/DBI-1.48/DBI.pm#rows

You should also take a look at the Database-specific method of
determining the number of rows in a statement. Search the relevant
MySQL documentation for the "COUNT" function.


Hope this helps,
Paul Lalli

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem problem problem :( Need Help Mike ASP General 2 05-11-2004 08:36 AM



Advertisments