Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

comment on

( [id://3333]=superdoc: print w/replies, xml ) Need Help??

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

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/

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

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

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

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":



  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others browsing the Monastery: (4)
As of 2024-03-29 05:50 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found