A very valuable post, giving me all the hints I needed

I reply to this old post to share how I got my project to work on openSUSE Tumbleweed where I needed to connect to a remote MSSQL database over a VPN, and updates to my OS caused the connection to fail.

Using DBD::Sybase only cause troubles beyond tears. There were major issues with both UTF-8 handling and transactions, so I dropped that approach.

I got it to work on two different approaches:

I assume you have installed perl, DBI and DBD::ODBC, for which you most likely need:

$ sudo zypper in unixODBC unixODBC-devel

[download]

So, here are my two approaches:

  1. 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/

    [download]

    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

    [download]

    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

    [download]
    $ 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

    [download]

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

    [download]
  2. 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}

    [download]

    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

    [download]
    $ 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

    [download]

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

    [download]

A thing to note, is that the first approach will return uniqueidentifier fields as ASCII (like ECF63B31-2F2B-4B17-94B9-1894E35D01C1), whereas the second approach returns them binary, so you might need to convert them if you need to show them for debugging or export purposes:

use Data::UUID;

my $uuid_conv = Data::UUID->new;
while (my $row = $sth->fetchrow_hashref) {
    $row->{a_uuid} = $uuid_conv->to_string ($row->{a_uuid});
    }

[download]

Enjoy, Have FUN! H.Merijn

In reply to Re: Accessing Microsoft SQL Server from Linux using DBD::Sybase by Tux
in thread Accessing Microsoft SQL Server from Linux using DBD::Sybase by CleverFox

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.