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

Source: Internet
Author: User
Tags auth documentation dsn microsoft sql server odbc prepare sybase perl script

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.