Easysoft Data Access

How do I connect ActiveState ActivePerl on UNIX and Linux to SQL Server?

Article:
00985
Last Reviewed:
27th June 2012
Revision:
2

Use the SQL Server ODBC driver to connect ActiveState ActivePerl to Microsoft SQL Server 7.0, 2000, 2005, 2008, 2012, 2014, 2016, 2017, 2019 and Express. The SQL Server ODBC driver is available for 32-bit and 64-bit Linux (CentOS, Debian GNU/Linux, Fedora, Mandrake/Mandriva, OpenSUSE/SUSE, RedHat, RedHat Enterprise Linux (RHEL) and Slackware) and UNIX (AIX, HP-UX and Solaris) platforms.

#!/opt/ActivePerl-5.10/bin/perl -w
#
# Example: Connecting ActivePerl on UNIX and Linux to MSSQL
#

use strict;

use DBI;

# Replace datasource_name with the name of your data source.
# Replace database_username and database_password
# with the SQL Server database username and password.
my $data_source = q/dbi:ODBC:datasource_name/;
my $user = q/database_username/;
my $password = q/database_password/;

# Connect to the data source and get a handle for that connection.
my $dbh = DBI->connect($data_source, $user, $password)
    or die "Can't connect to $data_source: $DBI::errstr";

# This query generates a result set with one record in it.
my $sql = "SELECT 1 AS test_col";

# Prepare the statement.
my $sth = $dbh->prepare($sql)
    or die "Can't prepare statement: $DBI::errstr";

# Execute the statement.
$sth->execute();

# Print the column name.
print "$sth->{NAME}->[0]\n";

# Fetch and display the result set value.
while ( my @row = $sth->fetchrow_array ) {
   print "@row\n";
}

# Disconnect the database from the database handle.
$dbh->disconnect;

We accessed SQL Server 2000, 2005 and Express databases from ActivePerl on UNIX and Linux. The examples in this article will also work with earlier (7.0) and later (2008, 2012, 2014, 2016, 2017 and 2019) versions of SQL Server. We tested the SQL Server ODBC driver with ActivePerl 5.10 on Debian GNU/Linux.

  1. Download the ActivePerl distribution from the ActiveState web site.
  2. Copy the distribution file to your Perl machine, unpack and cd into the directory created by unpacking the file. For example:
    $ gunzip ActivePerl-5.10.0.1004-i686-linux-glibc-2.3.2-287188.tar.gz
    $ tar -xvf ActivePerl-5.10.0.1004-i686-linux-glibc-2.3.2-287188.tar
    $ cd ActivePerl-5.10.0.1004-i686-linux-glibc-2.3.2-287188
  3. As root, install ActivePerl:
    # sh install.sh
  4. Download the SQL Server ODBC driver for your ActivePerl client platform. (Registration required.)

    If the SQL Server ODBC driver is not currently available for your platform, check the list of ODBC-ODBC Bridge Client platforms. The ODBC-ODBC Bridge is an alternative SQL Server solution from Easysoft, which you can download from this site.

  5. Install and license the SQL Server ODBC driver on the machine where ActivePerl is installed.

    For installation instructions, see the ODBC driver documentation. Refer to the documentation to see which environment variables you need to set (LD_LIBRARY_PATH, LIBPATH, LD_RUN_PATH, SHLIB_PATH depending on the driver, platform and linker).

  6. Create an ODBC data source in /etc/odbc.ini that connects to the SQL Server database you want to access from ActivePerl. For example:
    [MSSQL-ActivePerl]
    # The DBD::ODBC module included in the ActivePerl distribution
    # is built against the iODBC driver manager. For this reason,
    # the Driver attribute needs to:
    # 1. Specify the SQL Server ODBC driver library directly rather
    #    indirectly through its /etc/odbcinst.ini entry.
    # 2. Load the ANSI only version of the driver.
    Driver          = /usr/local/easysoft/sqlserver/lib/libessqlsrv_a.so
    Server          = my_machine\SQLEXPRESS
    User            = my_domain\my_user
    Password        = my_password
    # If the database you want to connect to is the default
    # for the SQL Server login, omit this attribute
    Database        = Northwind
  7. Use isql to test the new data source. For example:
    cd /usr/local/easysoft/unixODBC/bin
    ./isql -v MSSQL-ActivePerl

    At the prompt, type "help" to display a list of tables. To exit, press return in an empty prompt line.

  8. To test that you can connect to SQL Server from ActivePerl and fetch some data, run the code sample shown at the start of this article. For example:
    ./activeperl-mssql-code-sample.pl
    test_col
    1

    For more Perl SQL Server examples, see Connecting Perl on UNIX or Linux to Microsoft SQL Server — Perl DBI/DBD::ODBC Tutorial Part 3. Note that you will need to change /usr/bin/perl to /opt/ActivePerl-version/bin/perl in the !# entry at the start of the examples. (If you have symbolically linked /usr/bin/perl to /opt/ActivePerl-version/bin/perl, this change will not be necessary.)

Notes

See Also

Applies To

Knowledge Base Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.