DBD::mysql problem

Discussion in 'Perl Misc' started by Stephen Lake, Jun 20, 2005.

  1. Stephen Lake

    Stephen Lake Guest

    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
    Stephen Lake, Jun 20, 2005
    #1
    1. Advertising

  2. Stephen Lake

    Paul Lalli Guest

    > 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
    Paul Lalli, Jun 20, 2005
    #2
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. JL
    Replies:
    0
    Views:
    1,133
  2. Andrew Crook

    DBD::CSV questions and is there a DBD::XML?

    Andrew Crook, Sep 2, 2003, in forum: Perl Misc
    Replies:
    2
    Views:
    159
    Andrew Crook
    Sep 11, 2003
  3. Mike Solomon

    DBD::mysqlPP v DBD::mysql

    Mike Solomon, Feb 16, 2004, in forum: Perl Misc
    Replies:
    1
    Views:
    198
    James Willmore
    Feb 16, 2004
  4. meneg
    Replies:
    2
    Views:
    108
    meneg
    Nov 17, 2004
  5. Jim Cochrane
    Replies:
    0
    Views:
    109
    Jim Cochrane
    Aug 25, 2007
Loading...

Share This Page