Using DBD::ODBC with Microsoft drivers
-- Repositories
https://packages.microsoft.com/sles/12/mssql-server-2017/
https://packages.microsoft.com/sles/15/prod/
https://packages.microsoft.com/opensuse/15/prod/
You might be able to get away with just the first, but I also wanted to
test against a local MSSQL database, so I could do tests off-line
Even though the repos do seem to work fine on Tumbleweed, there is a
mismatch beteen TW, SLES 12, SLES 15, and Leap 15, whcih makes me feel
rather uncomfortable
$ sudo zypper in mssql-cli mssql-server mssql-tools msodbcsql17
Here you might be getting away with just msodbcsql17, but it makes
testing a lot easier if you have all.
Then set up you ODBC (just showing the essential parts)
$ cat /etc/unixODBC/odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1
UsageCount=2
$ cat ~/.odbc.conf
[mssql]
Description = A MSSQL databe
Driver = ODBC Driver 17 for SQL Server
Server = fully.qualified.host.name
User = PerlMonk
Password = Password
Database = my_data
I found it to be essential to use the fully qualified hostname
in the configuration (also put it in /etc/hosts for speed) as these
Microsoft ODBC drivers do not work on "localhost", "127.0.0.1",
or "myhostname".
Note that at the moment of writing, this approach only worked for me
with an older perl (5.24.0) compiled against older openssl (1.1.0), as
my current perl (5.28.2 built against openssl-1.1.1) refused to connect.
In perl, now just connect using
use DBI;
my $dbh = DBI->connect ("dbi:ODBC:mssql");
Using DBD::ODBC with FreeTDS
This approach does not require additional repositories. All you need
is available in the base repos.
$ sudo zypper in freetds-{config,devel,doc,tools} lib{ct4,dbi-drivers-
+dbd-freetds,sybdb5,tdsodbc0}
Maybe you do not need all of those and maybe installing just the one you
need will automatically install the rest, but this is the complete list.
Then set up you ODBC (just showing the essential parts)
$ cat /etc/unixODBC/odbcinst.ini
[FreeTDS]
Description=FreeTDS unixODBC Driver
Driver=/usr/lib64/libtdsodbc.so.0
Setup=/usr/lib64/libtdsodbc.so.0
UsageCount=1
$ cat ~/.odbc.conf
[freetds]
Description = A MSSQL databe
Driver = FreeTDS
TDS version = 7.2
Trace = No
Server = fully.qualified.host.name
Port = 1433
User = PerlMonk
Password = Password
Database = my_data
Client Charset = UTF-8
Note that at the moment of writing, this approach worked for me
with the most recent perl (5.30.0) with compiled with -Duseshrplib
against openssl 1.1.1, so I can use all the features of modern perl!
In perl, now just connect using
use DBI;
my $dbh = DBI->connect ("dbi:ODBC:freetds");