Use the SQL Server ODBC driver to connect ActiveState ActivePerl to Microsoft SQL Server. The SQL Server ODBC driver is available for 32-bit and 64-bit Linux 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;
$ 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
# sh install.sh
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.
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).
[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
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.
./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.)
Driver
entry in the data source. Change the Driver
entry so that it specifies the ODBC drivers shared object path rather than its /etc/odbcinst.ini entry. For example:
[MY_SQL_SERVER_ODBC_DRIVER_DSN] #Driver = Easysoft ODBC-SQL Server Driver = /usr/local/easysoft/sqlserver/lib/libessqlsrv_a.so . . . [MY_ODBC_ODBC_BRIDGE_DSN] #Driver = ODBC-ODBC Bridge Driver = /usr/local/easysoft/oob/client/libesoobclient.so.1 . . .
This change is necessary because the iODBC Driver Manager is unable to load an ODBC driver by looking up its shared object path in odbcinst.ini.
Note that the unixODBC Driver Manager understands both Driver = odbcinst_ini_entry
and Driver = shared_object_path
formats. This change will not affect the use of the data source with applications that are linked against the unixODBC Driver Manager.
SQLConnectW
). This is the method that ODBC defines for reporting Unicode support to a Driver Manager.
The DBD::ODBC module included in the ActivePerl distribution is a non-Unicode version, and does not make wide function calls. However, the iODBC Driver Manager detects that the SQL Server ODBC driver supports wide functions and uses them. The iODBC Driver Manager passes 4 byte Unicode characters to the wide functions, instead of the standard 2 byte characters, which the SQL Server ODBC driver expects. Because the strings passed to SQLConnectW
are not in the expected format, the connection fails.
To work around this, use the non-Unicode version of the driver, which does not support wide function calls (and therefore does not export SQLConnectW
). To do this, edit the Driver
in your data source. For example:
[MY_SQL_SERVER_ODBC_DRIVER_DSN] Driver = /usr/local/easysoft/sqlserver/lib/libessqlsrv_a.so . . .
# cd /opt/ActivePerl-5.10/lib/auto/DBD # mkdir ODBC.original # mv ODBC/* ODBC.original $ cd /tmp $ tar -xvf DBD-ODBC-1.17.tar $ cd DBD-ODBC-1.17 $ ODBCHOME=/usr/local/easysoft/unixODBC $ DBI_DSN="dbi:ODBC:MY_SQL_SERVER_ODBC_DRIVER_DSN" $ DBI_USER="my_domain\my_user" $ DBI_PASS=my_password $ export ODBCHOME DBI_DSN DBI_USER DBI_PASS $ /opt/ActivePerl-5.10/bin/perl Makefile.PL $ make $ make test # make install
All Easysoft ODBC driver distributions include the unixODBC Driver Manager, and unixODBC is supplied with many Linux distributions.
For more information about building DBD::ODBC, see the README file in the DBD::ODBC distribution and Enabling ODBC support in Perl with Perl DBI and DBD::ODBC.