Perl DBI Basics

[nationavon]

I was using WIN32::ODBC and decided to try DBI::ODBC. This is basically what I have:

use DBI;
$dbh = DBI->connect('dbi:ODBC:helpdesk');

$sqlstatement = "SELECT USER_PASSWORD FROM USERS WHERE USER_NAME='$username'";

$sth = $dbh->prepare($sqlstatement);
$sth->execute || die "Could not execute SQL statement";
$dbpass = $sth->fetchrow_array;

Needless to say this doesn't work. I guess what I need is the most basic syntax for getting a field from a db and assigning it to a variable.

 

[hmerrill]

First off, if you haven't already done these, do:
1. check the DBI FAQ in this forum
2. make sure you have DBI installed properly
3. make sure you have DBD::ODBC installed properly
4. read the perldocs for the DBI module
5. read the perldocs for the DBD::ODBC module on connection methods

Now that you've done all that, the 1st suggestion I'll make is that you need to do some error checking on your DBI statements - an easy way to do that is to enable the RaiseError attribute when you connect. I do that like this:

my $DSN = "DBI:mysql:database=$DB;host=$HOST;port=$PORT";
my $dbh = DBI->connect("$DSN",
"$USER",
"$PASS",
{ RaiseError => 1, PrintError => 0 }
);

When you set RaiseError on, every DBI statement(like prepare, execute, fetchrow_array, etc.) is automatically error checked - that is, if a DBI error is found, it is automatically raised and will cause the statement to die with a meaningful message. So, if you use RaiseError, and you don't want your script to die if it encounters a DBI error, you'll want to wrap your DBI statements in an eval. There's a great example of using "eval" with DBI statements in the DBI perldocs - on Linux I can see that by doing

perldoc DBI

but I'm not sure how you do that in windows.

HTH.
Hardy Merrill
Mission Critical Linux, Inc.

http://www.missioncriticallinux.com

 

[tsdragon]

I'd also suggest that you try putting your sql statement in lowercase instead of uppercase. It might make a difference. I always use lowercase with the DBI module and MySQL, and I've never had a problem. Tracy Dryden
tracy@bydisn.com

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.