connecting to a mySQL db with perl

Dev Shed Forums Sponsor:

March 15th, 2000, 07:58 PM

bydavid

Contributing User

Join Date: Mar 2000

Location: USA

Posts: 67

Time spent in forums: < 1 sec
Reputation Power: 14

Hello, I am trying to connect to a mySQL server with perl so that i can display contents of the database on a web page.

$SQL="SELECT * FROM list";
##########################
#the followig line seems to be giving me the problem
$sth=$dbh->prepare($SQL);
##########################
$sth=$sth->execute;
while($pointer=$sth->fetchrow_hashref)
{
$name=$pointer->{'name'};
print "$name<br>n";
}

what am i doing wrong?
i would appreciate any help possible

March 15th, 2000, 11:59 PM

codemaster

Junior Member

Join Date: Mar 2000

Location: Blackstone, VA, USA

Posts: 5

Time spent in forums: < 1 sec
Reputation Power: 0

How about giving us some error messages.
Thanks.

March 16th, 2000, 12:19 AM

Shiju Rajan

.Net Developer

Join Date: Feb 2000

Location: London

Posts: 987

Time spent in forums: 3 h 26 m 22 sec
Reputation Power: 14

Hi,

following may work for you:

$dbh=DBI->connect('dbi:mysql:databasename','username','pwd');

$sql="SELECT * FROM tblname";
$sth=$dbh->prepare($sql);

#####i think here you made the mistake ####
##$sth=$sth->execute;###
###Return value should not be a statement handle########

$rv=$sth->execute;

while(@row = $sth->fetchrow_array) {
print $row[0];
}

or

while($pointer = $sth->fetchrow_hashref) {
print $pointer->{'name'};
}

[This message has been edited by Shiju Rajan (edited March 15, 2000).]

March 16th, 2000, 06:10 PM

bydavid

Contributing User

Join Date: Mar 2000

Location: USA

Posts: 67

Time spent in forums: < 1 sec
Reputation Power: 14

I tried out the different code you used, but I still seem to have the same problem.
The problem : no resutls show up on the page.
The code seems to stop responding right after the line
<b>$sth=$dbh->prepare($sql);</b>

I tried putting a print statment right after that line, and that print statment would not print.

March 16th, 2000, 06:17 PM

bydavid

Contributing User

Join Date: Mar 2000

Location: USA

Posts: 67

Time spent in forums: < 1 sec
Reputation Power: 14

codemaster:

I do not seem to be getting any errors. THe page just does not load any data. The code seems to stop running after the prepare statement.

March 17th, 2000, 02:34 AM

Shiju Rajan

.Net Developer

Join Date: Feb 2000

Location: London

Posts: 987

Time spent in forums: 3 h 26 m 22 sec
Reputation Power: 14

Are you sure you have some records in the database??????.

try this

$dbh=DBI->connect('dbi:mysql:databasename','username','pwd');

$sql="SELECT * FROM tblname";

print "Database Connectedn";

$sth = $dbh->prepare($sql)
or die "Can't prepare $sql: $dbh->errstrn";

$rv = $sth->execute
or die "can't execute the query: $sth->errstrn";

if ($rv==0){

print "No Recordsn";

}else{
while(@row = $sth->fetchrow_array) {
print $row[0];
}
}

March 17th, 2000, 06:05 PM

bydavid

Contributing User

Join Date: Mar 2000

Location: USA

Posts: 67

Time spent in forums: < 1 sec
Reputation Power: 14

I tried the out the code, and only the "Database Connected" comes out.

I know that I have data in the database because i can log in with a seperate client and extract data with the same SQL statement.

March 18th, 2000, 12:33 AM

Shiju Rajan

.Net Developer

Join Date: Feb 2000

Location: London

Posts: 987

Time spent in forums: 3 h 26 m 22 sec
Reputation Power: 14

hi,

i tested this script in my server And it is working fine.

You may see the test at:

http://www.samakcreations.com/test.cgi

here is the full source code which i am using to fetch a record from a login table(fields are username and password):

#!/usr/local/bin/perl

use CGI;
use DBI;

$q=new CGI;

print $q->header;

print "<html>n";
print "<head><title>database test</title></head>n";
print "<body>n";
print "<h1>Database Test</h1>n";

$dbh=DBI->connect('dbi:mysql:database','usr','pwd');

$sql="SELECT * FROM login";

print "Database Connected<br>n";

$sth = $dbh->prepare($sql);

$rv = $sth->execute;

if ($rv==0){

print "No Recordsn";

}else{
while(@row = $sth->fetchrow_array) {
print "UserName :".$row[0]."<br>n";
print "Password :".$row[1]."<br>n";
}
}
print "</body>n";
print "</html>n";

If it is not fetching the record ,then i think the problem with your data.

try another way also.

after prepare statement just try to execute the statement:

$sth->execute;

while(@row = $sth->fetchrow_array) {
print "FirstField :".$row[0]."<br>n";
}

March 20th, 2000, 05:32 PM

bydavid

Contributing User

Join Date: Mar 2000

Location: USA

Posts: 67

Time spent in forums: < 1 sec
Reputation Power: 14

First of all, i want to say thanks for all help that i have been recieving from you.

i tried out the website link, and what you have there is exactly what i want on my site. i then copied the stuff that you put on the message, but i dont get those results. It says "databaseconnected" but i dont see any data. I checked my database on mysql server, and its all there.

#10

March 21st, 2000, 12:27 AM

Shiju Rajan

.Net Developer

Join Date: Feb 2000

Location: London

Posts: 987

Time spent in forums: 3 h 26 m 22 sec
Reputation Power: 14

Hi David,

why don't you try the same in PHP?.

I don't know ,what is the problem with your script!!.

Are you getting some error when you run the script???.

Which version of mysql you are using??.

#11

March 21st, 2000, 01:14 AM

bydavid

Contributing User

Join Date: Mar 2000

Location: USA

Posts: 67

Time spent in forums: < 1 sec
Reputation Power: 14

Shiju Rajan,

I would turn to PHP, but my web host does not support it. I have done sites similar to this one, but using ASP and so PHP is a goodc hoice, but i dont have hte resource.

Good news, i got it to work , but i used the Mysql module instead of the DBI module, and i got it working the first time.

I dont understand why the example you gave me doesnt work. I have one question about tit though. Where do you specify the server? I did not notice where i would tell the script where to find the sql server. In the other versin that i wrote, i had to specify the adress of the mysql server. HEre is the code i used

#!/usr/local/bin/perl

use Mysql;
use CGI;

print "Content--type:text/htmlnn";

print "<html>n";
print "<head><title>Dynamic Generated List</title></head>n";
print "<font face='arial'>n";

$host="my.sqlserver.com";
$database="dbname";
$user="root";
$password="12345";

$db = Mysql->connect($host, $database, $user, $password);

$db->selectdb($database);

$sql_query="select * from table";

$query = $db->query($sql_query);

@information=$query->fetchrow;

print "<table border='1'>n";
print "<font face='arial'>n";
print "<tr>n";
print "<td>firstname</td>n";
print "<td>lastname</td>n";
print "<td>comments</td>n";
print "</tr>n";

$num=$query->numrows;

$i=0;

while($i<$num)
{
@information=$query->fetchrow;
print "<tr>n";
foreach $i(@information)
{
print "<td>$i</td>n";
}
print "</tr>n";
$i=$i+1;
}

print "<tr><td><input type='submit' value='Submit Purchase'><input type='reset' value='Reset'></td></tr>n";

And thanks for all teh help you have given me, i truly appreciate it.

connecting to a mySQL db with perl

Developer Shed Advertisers and Affiliates