This article will provide some examples of Perl connections to Microsoft SQL Server databases. Perl scripts run on Windows and Linux platforms.
Windows platform
If you run a Perl script under the Windows platform, it is recommended that you use a two modular package that relies on DBI to provide a standard database interface module.
Dbd::odbc
Dbd::ado
Using DBD::ODBC
If you choose Dbd::odbc, the following example code shows how to connect to a SQL Server database:
Copy Code code as follows:
Use DBI;
# DBD::ODBC
My $dsn = ' dbi:odbc:driver={sql Server} ';
My $host = ' 10.0.0.1,1433 ';
My $database = ' my_database ';
My $user = ' sa ';
My $auth = ' s3cr3t ';
# Connect via Dbd::odbc by specifying the DSN dynamically.
My $dbh = Dbi->connect ("$DSN; server= $host;D atabase= $database ",
$user,
$auth,
{raiseerror => 1, autocommit => 1}
) || Die "Database Connection not made: $DBI:: Errstr";
#Prepare a SQL statement my $sql = "SELECT ID, Name, phone_number from Employees";
My $sth = $dbh->prepare ($sql);
#Execute the statement
$sth->execute ();
My ($id, $name, $phone _number);
# Bind The results to the local variables
$sth->bind_columns (undef, \ $id, \ $name, $phone _number);
#Retrieve values from the result set
while ($sth->fetch ()) {
Print "$id, $name, $phone _number\n";
}
#Close the connection
$sth->finish ();
$DBH->disconnect ();
You can also connect using a System DSN that you set up beforehand. To establish a System DSN, you can access the Control Panel-> management tool-> data source.
Using the System DSN connection, you need to change the connection string. As shown below:
Copy Code code as follows:
# Connect via DBD::ODBC using a System DSN
My $dbh = Dbi->connect ("Dbi:ODBC:my_system_dsn",
$user,
$auth,
{
RaiseError => 1,
Autocommit => 1
}
) || Die "Database Connection not made: $DBI:: Errstr";
Using Dbd::ado
If you select the Dbd::ado module, the following example shows how to connect to a SQL Server database.
Copy Code code as follows:
Use DBI;
My $host = ' 10.0.0.1,1433 ';
My $database = ' my_database ';
My $user = ' sa ';
My $auth = ' s3cr3t ';
# Dbd::ado
$DSN = "PROVIDER=SQLOLEDB; Trusted connection=yes; ";
$dsn. = "server= $host;D atabase= $database";
My $dbh = Dbi->connect ("Dbi:ado: $dsn",
$user,
$auth,
{raiseerror => 1, autocommit => 1}
) || Die "Database Connection not made: $DBI:: Errstr";
#Prepare a SQL statement
My $sql = "SELECT ID, Name, phone_number from Employees"; My $sth = $dbh->prepare ($sql);
#Execute the statement
$sth->execute ();
My ($id, $name, $phone _number);
# Bind The results to the local variables
$sth->bind_columns (undef, \ $id, \ $name, $phone _number);
#Retrieve values from the result set
while ($sth->fetch ()) {
Print "$id, $name, $phone _number\n";
}
#Close the connection
$sth->finish ();
$DBH->disconnect ();
Linux Platform
If you are running a Perl script under a Linux platform, you need to use the Dbd::sybase package to connect to the SQL Server database.
Installing the SQL Server Support Library
The Sybase dbd pack relies on the FreeTDS driver.
FreeTDS Download Address: www.freetds.org
Install FreeTDS-driven documentation see: Http://www.freetds.org/userguide/config.htm
The drive is not used to ODBC.
Configure a data source
Modifying the freetds.conf file includes SQL Server database information, as follows:
Copy Code code as follows:
[ss_my_db]
host = 10.0.0.1 # or host name port = 1433
TDS Version = 7.0
Install Sybase DBD module
This module documentation see: HTTP://SEARCH.CPAN.ORG/~MEWP/DBD-SYBASE/SYBASE.PM
In addition, you need to set the SYBASE environment variable to the FREETDS installation path, export Sybase=/usr/local/freetds
Using Sybase DBI and SQL Server DSN instances
Copy Code code as follows:
# Load the DBI module
Use DBI;
Use Dbd::sybase;
My $database = "my_database";
My $user = "sa";
My $auth = "s3cr3t";
BEGIN
{
$ENV {SYBASE} = "/usr/local";
}
# Connect to the SQL Server Database
My $dbh = Dbi->connect ("dbi:sybase:server=ss_my_db;database= $database",
$user,
$auth
{raiseerror => 1, autocommit => 1}
) || Die "Database Connection not made: $DBI:: Errstr";
#Prepare a SQL statement
My $sql = "SELECT ID, Name, phone_number from Employees";
My $sth = $dbh->prepare ($sql);
#Execute the statement
$sth->execute ();
My ($id, $name, $phone _number);
# Bind The results to the local variables
$sth->bind_columns (undef, \ $id, \ $name, $phone _number);
#Retrieve values from the result set
while ($sth->fetch ()) {print "$name, $title, $phone \ n";
}
#Close the connection
$sth->finish ();
Undef $sth; # This fixes a segfault bug with certain versions of Dbd::sybase
$DBH->disconnect ();