zeWEBHOST Knowledge Base - MSSQL Databases Connection Strings in PERL Without DSN

zeWEBHOSTAffordable Web Hosting Services Beyond Your Expectations

Home Web Hosting Guide Compare Hosting Plans Basic Hosting Plan Professional Hosting Plan Extreme Hosting Plan Email Only Hosting Web Forwarding Advanced Features Hosting Control Panel SSL Certificates Hosting Order Page

WISDOM

The Essence of Knowledge is Having it to Apply it.

Confocious

Knowledge Base IP Location Contact Us Login Order Free Toolbar
KNOWLEDGE BASE / CONNECTION STRINGS / CONNECTING TO DATABASES IN PERL

The Full Script

CONNECTING TO MSSQL IN PERL WITHOUT A DSN

This script demonstrates connecting to a MSSQL 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 = "mssqlX.hcpanel.com";
Sets a variable to hold the address of the MSSQL 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={SQL Server};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: mssql-dsnless.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 without a DSN</title>";
print "</head><body>";

#zeWEBHOST Test script for connecting to an MSSQL 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 = "mssqlX.hcpanel.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={SQL Server};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--

To Top