Beginner DBI problem

P

poopdeville

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
 
A

A. Sinan Unur

(e-mail address removed) wrote in
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
 
M

Matt Garrish

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
 
M

Matt Garrish

Matt Garrish said:
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;


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
 
P

poopdeville

A. Sinan Unur said:
(e-mail address removed) wrote in


What is an "FM"?

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

"Arrow-foo," compare with kung-fu. :)
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
 
M

Matt Garrish

Gunnar Hjalmarsson said:
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
 
P

Paul Lalli

Matt said:
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
 
M

Matt Garrish

Paul Lalli said:
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
 
G

Gunnar Hjalmarsson

Matt said:
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?
 
M

Matt Garrish

Gunnar Hjalmarsson said:
Okay, thanks.


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
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,733
Messages
2,569,439
Members
44,829
Latest member
PIXThurman

Latest Threads

Top