How Perl connects to SQL Server databases under Windows and Linux systems _perl

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 ();