Beginner DBI problem

Discussion in 'Perl Misc' started by poopdeville@gmail.com, Oct 19, 2005.

  1. Guest

    Hi everybody.

    I'm having a bit of a problem getting DBI to interface with mysql.
    This is my first experience with DBI, so I'm not really sure where to
    start troubleshooting. I've read several FMs, but my -> foo isn't so
    great. Basically, I'm trying to pull out a column of data out of a
    mysql table and printing HTML based on it. A minimal example of code
    that causes trouble is:

    # use DBI;
    #
    # my $dbh = DBI->connect('DBI:mysql:database', 'user', 'pass')
    # or die "Couldn't connect to database: " . DBI->errstr;
    # my $sth = $dbh->do('SELECT title FROM pages') or die
    # "Couldn't query the database: " . DBI->errstr;

    # while(my @title = $sth->fetchrow_array) {
    # print "<option>$title[0]</option>\n";
    # }

    (It's commented to preserve formatting.) I get an error: Can't call
    method "fetchrow_array" without a package or object reference at... the
    line where the while loop starts. Can anybody help?

    Thanks,
    'cid 'ooh
     
    , Oct 19, 2005
    #1
    1. Advertising

  2. wrote in
    news::

    > Hi everybody.
    >
    > I'm having a bit of a problem getting DBI to interface with mysql.
    > This is my first experience with DBI, so I'm not really sure where to
    > start troubleshooting. I've read several FMs, but my -> foo isn't so


    What is an "FM"?

    What is a "-> foo"?

    > great. Basically, I'm trying to pull out a column of data out of a
    > mysql table and printing HTML based on it. A minimal example of code
    > that causes trouble is:
    >
    > # use DBI;
    > #
    > # my $dbh = DBI->connect('DBI:mysql:database', 'user', 'pass')
    > # or die "Couldn't connect to database: " . DBI->errstr;
    > # my $sth = $dbh->do('SELECT title FROM pages') or die
    > # "Couldn't query the database: " . DBI->errstr;

    ....
    > (It's commented to preserve formatting.) I get an error: Can't call
    > method "fetchrow_array" without a package or object reference at...
    > the line where the while loop starts. Can anybody help?


    The do method returns the number of rows affected, not a statement
    handle.

    What you need is a prepare and execute.

    Consult the DBI docs for examples.

    Sinan
    --
    A. Sinan Unur <>
    (reverse each component and
    remove .invalid for email address)

    comp.lang.perl.misc guidelines on
    the WWW:
    http://mail.augustmail.com/~tadmc/clpmisc/clpmisc_guidelines.html
     
    A. Sinan Unur, Oct 19, 2005
    #2
    1. Advertising

  3. Matt Garrish Guest

    <> wrote in message
    news:...
    > Hi everybody.
    >
    > I'm having a bit of a problem getting DBI to interface with mysql.
    > This is my first experience with DBI, so I'm not really sure where to
    > start troubleshooting. I've read several FMs, but my -> foo isn't so
    > great. Basically, I'm trying to pull out a column of data out of a
    > mysql table and printing HTML based on it. A minimal example of code
    > that causes trouble is:
    >
    > # use DBI;
    > #
    > # my $dbh = DBI->connect('DBI:mysql:database', 'user', 'pass')
    > # or die "Couldn't connect to database: " . DBI->errstr;


    Always be explicit:

    my $dbh = DBI->connect('DBI:mysql:database', 'user', 'pass', { RaiseError =>
    1, AutoCommit => 0 } )
    or die "Could not connect to database: " . DBI->errstr;

    > # my $sth = $dbh->do('SELECT title FROM pages') or die
    > # "Couldn't query the database: " . DBI->errstr;


    Please read the documentation for any function you don't understand. No one
    likes to find out there problem is blatantly obvious after wasted time
    debugging:

    <quote for do>
    Prepare and execute a single statement. Returns the number of rows affected
    or undef on error.
    </quote>

    You don't get a statement handle back from do, so you obviously can't call
    any methods on return value.

    my $sth = $dbh->prepare("SELECT title FROM pages") or die DBI->errstr;


    Matt
     
    Matt Garrish, Oct 19, 2005
    #3
  4. Matt Garrish Guest

    "Matt Garrish" <> wrote in message
    news:S5i5f.10550$...
    >
    > <> wrote in message
    > news:...
    >> Hi everybody.
    >>
    >> I'm having a bit of a problem getting DBI to interface with mysql.
    >> This is my first experience with DBI, so I'm not really sure where to
    >> start troubleshooting. I've read several FMs, but my -> foo isn't so
    >> great. Basically, I'm trying to pull out a column of data out of a
    >> mysql table and printing HTML based on it. A minimal example of code
    >> that causes trouble is:
    >>
    >> # use DBI;
    >> #
    >> # my $dbh = DBI->connect('DBI:mysql:database', 'user', 'pass')
    >> # or die "Couldn't connect to database: " . DBI->errstr;

    >
    > Always be explicit:
    >
    > my $dbh = DBI->connect('DBI:mysql:database', 'user', 'pass', { RaiseError
    > => 1, AutoCommit => 0 } )
    > or die "Could not connect to database: " . DBI->errstr;
    >
    >> # my $sth = $dbh->do('SELECT title FROM pages') or die
    >> # "Couldn't query the database: " . DBI->errstr;

    >
    > Please read the documentation for any function you don't understand. No
    > one likes to find out there problem is blatantly obvious after wasted time
    > debugging:
    >
    > <quote for do>
    > Prepare and execute a single statement. Returns the number of rows
    > affected or undef on error.
    > </quote>
    >
    > You don't get a statement handle back from do, so you obviously can't call
    > any methods on return value.
    >
    > my $sth = $dbh->prepare("SELECT title FROM pages") or die DBI->errstr;
    >


    And of course...

    $sth->execute() or die DBI->errstr;

    Matt
     
    Matt Garrish, Oct 19, 2005
    #4
  5. Guest

    A. Sinan Unur wrote:
    > wrote in
    > news::
    >
    > > Hi everybody.
    > >
    > > I'm having a bit of a problem getting DBI to interface with mysql.
    > > This is my first experience with DBI, so I'm not really sure where to
    > > start troubleshooting. I've read several FMs, but my -> foo isn't so

    >
    > What is an "FM"?


    "fucking manuals," compare with "RTFM."
    >
    > What is a "-> foo"?


    "Arrow-foo," compare with kung-fu. :)

    > > great. Basically, I'm trying to pull out a column of data out of a
    > > mysql table and printing HTML based on it. A minimal example of code
    > > that causes trouble is:
    > >
    > > # use DBI;
    > > #
    > > # my $dbh = DBI->connect('DBI:mysql:database', 'user', 'pass')
    > > # or die "Couldn't connect to database: " . DBI->errstr;
    > > # my $sth = $dbh->do('SELECT title FROM pages') or die
    > > # "Couldn't query the database: " . DBI->errstr;

    > ...
    > > (It's commented to preserve formatting.) I get an error: Can't call
    > > method "fetchrow_array" without a package or object reference at...
    > > the line where the while loop starts. Can anybody help?

    >
    > The do method returns the number of rows affected, not a statement
    > handle.
    >
    > What you need is a prepare and execute.
    >
    > Consult the DBI docs for examples.


    Thanks for your help. It works now!

    'cid 'ooh
     
    , Oct 19, 2005
    #5
  6. Matt Garrish wrote:
    >
    > $sth->execute() or die DBI->errstr;


    Shouldn't that be

    $sth->execute() or die $sth->errstr;

    ? Or doesn't it matter?

    --
    Gunnar Hjalmarsson
    Email: http://www.gunnar.cc/cgi-bin/contact.pl
     
    Gunnar Hjalmarsson, Oct 19, 2005
    #6
  7. Matt Garrish Guest

    "Gunnar Hjalmarsson" <> wrote in message
    news:...
    > Matt Garrish wrote:
    >>
    >> $sth->execute() or die DBI->errstr;

    >
    > Shouldn't that be
    >
    > $sth->execute() or die $sth->errstr;
    >
    > ? Or doesn't it matter?
    >


    It doesn't matter. errstr is global to the DBI package, so any valid object
    should be able to access its value. In this case:

    DBI->errstr;
    $dbh->errstr;
    $sth->errstr;

    could all be used to print an error executing the statement. It's probably
    not wise to use the statement handle itself, though, because it's the most
    likely candidate not to have a valid object.

    Matt
     
    Matt Garrish, Oct 19, 2005
    #7
  8. Paul Lalli Guest

    Matt Garrish wrote:
    > my $dbh = DBI->connect('DBI:mysql:database', 'user', 'pass', { RaiseError =>
    > 1, AutoCommit => 0 } )
    > or die "Could not connect to database: " . DBI->errstr;


    The 'or die ...' is redundant. You're setting RaiseError to 1, so as
    soon as DBI->connect() fails to connect, the program will terminate,
    before even processing the second part of the 'or' statement.

    Paul Lalli
     
    Paul Lalli, Oct 19, 2005
    #8
  9. Matt Garrish Guest

    "Paul Lalli" <> wrote in message
    news:...
    > Matt Garrish wrote:
    >> my $dbh = DBI->connect('DBI:mysql:database', 'user', 'pass', { RaiseError
    >> =>
    >> 1, AutoCommit => 0 } )
    >> or die "Could not connect to database: " . DBI->errstr;

    >
    > The 'or die ...' is redundant. You're setting RaiseError to 1, so as
    > soon as DBI->connect() fails to connect, the program will terminate,
    > before even processing the second part of the 'or' statement.
    >


    Depends on your definitiion of redundancy. In most production code I'll turn
    RaiseError off, but there are still instances where I want the messages. I
    find it's easier to live with the redundancy than add the die statements
    later.

    Matt
     
    Matt Garrish, Oct 19, 2005
    #9
  10. Matt Garrish wrote:
    > Gunnar Hjalmarsson wrote:
    >>Matt Garrish wrote:
    >>>
    >>>$sth->execute() or die DBI->errstr;

    >>
    >>Shouldn't that be
    >>
    >> $sth->execute() or die $sth->errstr;
    >>
    >>? Or doesn't it matter?

    >
    > It doesn't matter. errstr is global to the DBI package, so any valid object
    > should be able to access its value. In this case:
    >
    > DBI->errstr;
    > $dbh->errstr;
    > $sth->errstr;
    >
    > could all be used to print an error executing the statement.


    Okay, thanks.

    > It's probably not wise to use the statement handle itself, though,
    > because it's the most likely candidate not to have a valid object.


    Don't follow you there, Matt. If $sth isn't a valid object reference,
    the errstr method won't tell you why $sth->execute() failed, will it?

    --
    Gunnar Hjalmarsson
    Email: http://www.gunnar.cc/cgi-bin/contact.pl
     
    Gunnar Hjalmarsson, Oct 19, 2005
    #10
  11. Matt Garrish Guest

    "Gunnar Hjalmarsson" <> wrote in message
    news:...
    > Matt Garrish wrote:
    >> Gunnar Hjalmarsson wrote:
    >>>Matt Garrish wrote:
    >>>>
    >>>>$sth->execute() or die DBI->errstr;
    >>>
    >>>Shouldn't that be
    >>>
    >>> $sth->execute() or die $sth->errstr;
    >>>
    >>>? Or doesn't it matter?

    >>
    >> It doesn't matter. errstr is global to the DBI package, so any valid
    >> object should be able to access its value. In this case:
    >>
    >> DBI->errstr;
    >> $dbh->errstr;
    >> $sth->errstr;
    >>
    >> could all be used to print an error executing the statement.

    >
    > Okay, thanks.
    >
    >> It's probably not wise to use the statement handle itself, though,
    >> because it's the most likely candidate not to have a valid object.

    >
    > Don't follow you there, Matt. If $sth isn't a valid object reference, the
    > errstr method won't tell you why $sth->execute() failed, will it?
    >


    Sorry, was in a rush this morning. I was thinking you would get both an
    error for trying to call the execute method and an error for trying to call
    errstr (i.e., redundant messages). Obviously that's not the case.

    Matt
     
    Matt Garrish, Oct 19, 2005
    #11
    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. ulloa
    Replies:
    1
    Views:
    530
    Juha Laiho
    Jul 22, 2004
  2. =?Utf-8?B?S3VydCBTY2hyb2VkZXI=?=

    No Class at ALL!!! beginner/beginner question

    =?Utf-8?B?S3VydCBTY2hyb2VkZXI=?=, Feb 2, 2005, in forum: ASP .Net
    Replies:
    7
    Views:
    593
    =?Utf-8?B?S3VydCBTY2hyb2VkZXI=?=
    Feb 3, 2005
  3. Jerome Hauss
    Replies:
    0
    Views:
    174
    Jerome Hauss
    Oct 13, 2004
  4. Asby

    Mason, DBI, and DBI::Pg

    Asby, Jul 24, 2003, in forum: Perl Misc
    Replies:
    0
    Views:
    178
  5. Tim Haynes
    Replies:
    3
    Views:
    143
    Ron Reidy
    Sep 13, 2003
Loading...

Share This Page