LinuxQuestions.org - MySQL and perl problems

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - MySQL and perl problems (https://www.linuxquestions.org/questions/programming-9/mysql-and-perl-problems-29/)

Ok, here's a code snippet from my perl program phonebook.pl:
sub show_entries {
my ($dbh, $sth, @row);
$dbh = DBI->connect("DBI:mysql:database=$serverDb;host=$serverName;port=$serverPort",$serverUser,$serverPass) ;
$sth = $dbh->prepare("SELECT Name FROM $serverTable");
$sth->execute;

When I http://localhost/cgi-bin/phonebook.pl I get the following error:

Software error:
Can't call method "prepare" on an undefined value at /home/httpd/cgi-bin/phonebook.pl line 22.

Line 22 is the line that begins "$sth=$dbh->prepare...".

I've declared:
my $serverName= "localhost";
my $serverPort = "3306";
my $serverUser = "listing";
my $serverPass = "password";
my $serverDb = "phonebook";
my $serverTable = "listing";

Within the "phonebook" database I have a table correctly setup called "listing" with a column called "Name" with some values in it.

I've also done:
insert into host(Host,Db)values('localhost','phonebook');
insert into user(Host,User,Password) values ('localhost' ,'listing','PASSWORD('password'));

Any idea what's going on?

Try it with the $dbh line as

Code:

$dbh = DBI->connect("DBI:mysql:databasename",$serverUser,$serverPass);

As I look at that code I realize it may not work. I do not code in the exact style you have there, so I am going to show you what I know works;

Code:

use Mysql;



$dbh = Mysql->Connect("localhost","dbname","username","password");



$sql = "SELECT Name FROM $serverTable";

$res = $dbh->Query($sql);

Then you can loop through the results with

Code:

while (@tempary = $res->fetchrow){

    $first_column = $tempary[0];

    #etc, etc, etc.

}

Let me know if that does what you want.

still havin issues

Ok, well I migrated my code to your solution and basically I'm still having the same problem, oddly enough:

Can't call method "Query" on an undefined value at /home/httpd/cgi-bin/phonebook.pl line 24.

Line 23 and 24:
$sql = "SELECT Name FROM $serverTable";
$res = $dbh->Query($sql);

Seeing as how we've gotten the same error doing it two different ways the problem must lay elsewhere. What do I have to do in order to allow connectivity to my database? Is everything I said I did in my previous post enough? How can I check if the user "listing" has access to the database? One curious thing I found when I "telnet localhost 3306" I get the following error:

Host 'localhost.localdomain' is not allowed to connect to this MySQL serverConnection closed by foreign host.

Is that a problem?

Thanks for the help. =)

It may be an access problem. If you type (at a command prompt) mysql -u username -p password databasename (substituting where applicable :) )what happens?

Definitely looks like an access problem. If you receive the "access denied" message, you need to edit the access permissions within mysql to allow yourself to connect to the db. To do this, you have to check the "user" table in the database "mysql". After making changes to the user table, issue the command (from shell prompt) "mysqladmin flush-privileges".

You really should check through the access anyway, just to be sure who has access to your db =)

pretty much working

Thanks, yea it definitely was an access/priveleges problem. I was getting rather frusterated as I had even the user table just as I thought it should be and I still couldn't connect, but as per your post I hadn't flushed the privileges. After I did that it worked like a charm. I'm loving SQL, nuts to that dung from Redmond.