Apache ASP/PERL/MySQL

I'm attempting to set up a MySQL database on an Apache server using Apache::ASP, I've never used PERL before in my life, but I am used to C++/PHP/VB syntaxes so I expect it shouldn't be to difficult to wade through. My question is how do I connect to my MySQL database and output the data? I've tried three different coding sniplets, and have come up with this so far:

Code:

---

<%
use DBI;

my $db = 'DBI:mysql:DBName:localhost';
my $username = 'user';
my $pass = 'pass';
my $db_object = DBI->connect($db, $username, $pass);
my $sql = qq{ SELECT name, age FROM names };
my $sth = $dbh->prepare($sql);
$sth->execute;
my @array = $sth->fetchrow_array;
$dbh->disconnect();
%>

---

(I've changed the DBName/user/pass to the real values in my code)
The server is telling me the $dbh variable is not globally defined so I can't use it, changing it to "my $dbh" brings an error saying it is undefined.

Hey! Something I have a little experience with and can maybe help some!

In the code you posted you don't have "$dbh" defined and I don't believe that the "qq" syntax is correct, at least I've never used that method before. Perhaps you can try this code (hasn't been tested but should work):

Code:

---

use DBI;

my $dbh = DBI->connect("DBI:mysql:databaseName:databaseIpa","databaseUserName","databasePassword");

my $sql = "SELECT name, age FROM names";

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

$sth->execute;

my ($name, $age) = $sth->fetchrow_array;

$dbh->disconnect();

---

A few notes here:

For loaclhost connections you don't have to specify that either by saying "localhost" or providing the loopback ipa of "127.0.0.1"

In your sql statments be sure you use single quotes around any relevant field values - i.e. "Select name, age FROM names WHERE age>'50'". If you prefer to use the qq syntax it would be "qq~SELECT name, age FROM names~;" but I prefer to directly quote the string myself avoiding the confusion sometimes caused by the qq method which occasionally adds unwanted backticks....

Also you'll note that I used "my ($name, $age)" rather and "my @array" as that will return the data in a more human readable form.

Like I said earlier, I havn't actually tested this code myself and since I have slept at least twice since writing any perl->mysql code my little mind is confused therefore you may actually need to use "fetchrow_arrayref" instead of "fetchrow_array".

I also, at least during development, like to add some debug code to my database code. This can be accomplished by adding a logging sub to the script similar to:

Code:

---

sub writeToLog {
  open(LOG, ">>path/logFileName");
  print LOG "$_\n";
  close(LOG);
}

---

You would then modify the other lines along these lines:

Code:

---

my $sth = $dbh->prepare($sql) || writeToLog  $sth->errstr;

---

This will help you identify and correct any database connection or statement errors - when you are connecting to a remote MySql server it will only allow a certain number of errors from any remote IPA before blocking all request from that IPA. MySql's docs say you can use a flush server command in your script but I've never had any luck actually getting that to work therefore the only actual method I've found reliable to reset MySql's error count is to restart the MySql service (my MySql experience is on Lynux so perhaps you can actually flush the server on a Win server)

Thank you for the response, unfortuneatly I decided to recode my existing site to PHP, through which I have since gotten MySQL to work.

Once again I greatly appreciate your help, and I may test out your code just to see if it does work in case I'm forced into PERL one day.