Programming Articles

Connecting to MSSQL in Perl with a DSN

This script demonstrates connecting to a MSSQL database in Perl with 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 DSN connection.

print "Content-type: text/html\n\n";
Sends a header so the page will be output as HTML.

$dsnname = "testdsn";
Sets a variable to hold the name of the DSN, you should change the text inside the quotes to the name of your DSN.

$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 = "DSN=$dsnname;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: mssql-dsn.pl--

use Win32::ODBC;

print "Content-type: text/html\n\n";

print "<html><head>";
print "<title>Sample script for accessing a MSSQL database in Perl with a DSN</title>";
print "</head><body>";

#Easy CGI Test script for connecting to a MSSQL database in Perl with 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 DSN name
$dsnname = "testdsn";
# the name of the table to display
$tablename = "testtable";
# username and password for the DSN
$userid = "testuser";
$password = "testpass";
## ----------------------------------------------

#create connection string
my $DSN = "DSN=$dsnname;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--
1/5/2008 2:13:09 AM Category Database Connection Strings Comments 0

Back