Connecting to MySQL in Perl without a DSN
This script demonstrates connecting to a MySQL database in Perl without a DSN. The script will make the connection and then output all the rows of a specified table. Any lines beginning with a # are comments.
use Win32::ODBC;
This line tells the script to use the Win32::ODBC module, which is needed for the database connection.
print "Content-type: text/html\n\n";
Sends a header so the page will be output as HTML.
$hostname = "creativebrainca.yourwebhostingmysql.com";
Sets a variable to hold the address of the MySQL server, you should change the text inside the quotes to the proper address.
$database = "testdatabase";
Sets a variable to hold the name of the database, you should change this to the name of your database.
$tablename = "testtable";
Sets a variable to hold the name of the table to be output. You should change this to your table name.
$userid = "testuser";
$password = "testpass";
Sets variables to hold the username and password. You should change these to your username and password.
my $DSN = "driver={MySQL};Server=$hostname;database=$database;uid=$userid;pwd=$password";
Sets a variable to hold the connection string.
if (!($db = new Win32::ODBC($DSN))) {
print "Error connecting to $DSN
";
print "Error: " . Win32::ODBC::Error() . "";
} else {
print "Database being searched...";
}
Tries to create a connection. If the connection fails the error will be output, otherwise the "Database being searched" message will be displayed.
$SqlStatement = "SELECT * FROM $tablename";
Sets the variable to hold the SQL query string.
if ($db->Sql($SqlStatement)) {
print "SQL failed.";
print "Error: " . $db->Error() . "";
} else {
Runs the query on the database. If an error occurs then it will be output and the program will skip to the end, otherwise the rows will be output.
print "<TABLE BORDER=1><TR>";
The rows from the database will be put into a HTML table.
$first = 1;
Sets this variable to specify its the first time through the database.
while($db->FetchRow()) {
Will loop through each row in the database.
%Data = $db->DataHash();
Gets the current row and puts the results in the hash %Data.
if($first){
print "<TR>";
foreach $k (keys %Data){
print "<TH>$k</TH>";
}
print "</TR>";
$first = 0;
}
If this is the first row, the script will loop through each of the fields and output the field name as the first row of the HTML table.
print "<TR>";
Creates a new row in the HTML table.
foreach $k (keys %Data){
print "<TH>$Data{$k}</TH>";
}
Goes through each field in the row from the database and puts it into the HTML table.
print "</TR>";
Ends the HTML table.
$db->Close();
Closes the database connection.
print "</body></html>";
Closes the HTML properly.
exit(0);
Exits the script.
--Here is the full script, the filename should be: mysql-dsnless.pl--
use Win32::ODBC;
print "Content-type: text/html\n\n";
print "<html><head>";
print "<title>Sample script for accessing a MySQL database in Perl without a DSN</title>";
print "</head><body>";
#Easy CGI Test script for connecting to an MySQL database in Perl without a DSN
#This script will connect to the database and output the results in an HTML table
# Change these values to point to your database
# --------------------------------------------
# the database server address
$hostname = "creativebrainca.yourwebhostingmysql.com";
# this is the name of the database
$database = "testdatabase";
# the name of the table to display
$tablename = "testtable";
# username and password for the database
$userid = "testuser";
$password = "testpass";
## ----------------------------------------------
#create connection string
my $DSN = "driver={MySQL};Server=$hostname;database=$database;uid=$userid;pwd=$password";
#open database connection
if (!($db = new Win32::ODBC($DSN))) {
print "Error connecting to $DSN";
print "Error: " . Win32::ODBC::Error() . "";
} else {
print "Database being searched...";
}
#create the query string
$SqlStatement = "SELECT * FROM $tablename";
#execute the query
if ($db->Sql($SqlStatement)) {
print "SQL failed.";
print "Error: " . $db->Error() . "";
} else {
#print out the results in a table
print "<TABLE BORDER=1><TR>";
$first = 1;
while($db->FetchRow()) {
%Data = $db->DataHash();
#its the first row so print heading
if($first){
print "<TR>";
foreach $k (keys %Data){
print "<TH>$k</TH>";
}
print "</TR>";
$first = 0;
}
print "<TR>";
#print values
foreach $k (keys %Data){
print "<TH>$Data{$k}</TH>";
}
print "</TR>";
}
}
$db->Close();
print "</body></html>";
exit(0);
--End Script--
<< Back |
