The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Programming Languages
> Perl Programming
|
connecting to a mySQL db with perl
Discuss connecting to a mySQL db with perl in the Perl Programming forum on Dev Shed. connecting to a mySQL db with perl Perl Programming forum discussing coding in Perl, utilizing Perl modules, and other Perl-related topics. Perl, the Practical Extraction and Reporting Language, is the choice for many for parsing textual information.
|
|
 |
|
|
|
|

Dev Shed Forums Sponsor:
|
|
|

March 15th, 2000, 07:58 PM
|
|
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
|
|
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
|
 |
.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
|
|
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
|
|
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
|
 |
.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
|
|
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
|
 |
.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
|
|
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.
|

March 21st, 2000, 12:27 AM
|
 |
.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??.
|

March 21st, 2000, 01:14 AM
|
|
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.
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|