Sybase to MSSQl migration for Perl in Linux RHEL

We have a requirement for the migration project from Sybase to mssql in Linux box which is RHEL

We have bunch of Perl programs which is connecting to Sybase and has to be migrated to MSSQL in the Linux box RHEL.

I see two option

1. Microsoft ODBC driver and DBD:ODBC cpan module along with unixODBC

DBI -> unixODBC -> DBD:ODBC & microsoft's ODBC DRIVER.

2. Using freetds and sybase's DBD::Sybase

My question is which option is more feasible considering my environment

1. Linux RHEL
2. Perl code connecting to Sybase has to be migrated to MSSQL Perl code.
3. One mssql.pm ( just a name given by me, ) module has to connect with around 400 Perl script with minimal code changes in 400 scripts.

***Is anyone here already done this and get successful

Please kindly help .

My research : I hardly seen people connecting to MSSQl using ODBC driver provided by MySQL ( guess it has been introduced recently)

But I was able to set it up using below link

https://github.com/pplu/perl-mssql-server/blob/master/connect.pl

And I see a comment in internet saying::
"Microsoft has a driver for Linux which can be used to connect via Linux. I had tried it and it worked like a charm. I was able to connect to SQL server using Microsoft binary. The real problem is that it cannot be used as Perl module to extend the usability via scripting"

https://www.slideshare.net/mobile/venkivoice/connect-sqlusingperlfromlinux

Im really confused which one to take and which one is feasible for our requirement.

2021-01-24 Athanasius added tags.

[talexb]

I used FreeTDS to connect to an MSSQL database. The documentation is available here.

The configuration file I use (slightly sanitized for obvious reasons) is here:

#   $Id: freetds.conf,v 1.12 2007/12/25 06:02:36 jklowden Exp $
#
# This file is installed by FreeTDS if no file by the same 
# name is found in the installation directory.  
#
# For information about the layout of this file and its settings, 
# see the freetds.conf manpage "man freetds.conf".  

# Global settings are overridden by those in a database
# server specific section
[global]
        # TDS protocol version
;       tds version = 4.2

        # Whether to write a TDSDUMP file for diagnostic purposes
        # (setting this to /tmp is insecure on a multi-user system)
        dump file = /tmp/freetds.log
        debug flags = 0xffff

        # Command and connection timeouts
;       timeout = 10
;       connect timeout = 10

        # If you get out-of-memory errors, it may mean that your clien
+t
        # is trying to allocate a huge buffer for a TEXT field.  
        # Try setting 'text size' to a more reasonable limit 
        text size = 64512

# A typical Microsoft server
[SqlServer]
        database = ABCDAT
        host = 192.0.0.1
        port = 1433
        tds version = 7.4
        client charset = UTF-8

        dump file = /tmp/foo.log
        debug flags = 0x001e

There is a Microsoft driver, but this one worked for me (although it doesn't allow multiple active statement handles). I set this up about three years ago, so I may not be able to explain how I got it working.

If I had time, I'd love to try the Microsoft driver .. but for now, this works.

[Tux]

Do read DBI connecting to MS SQL Server TLS 1.2 and all the replies. It comes very close to your situation.

[LanX]

Hi

Welcome to the monastery.

Please have a look at the following formatting tips, and try to edit your post in a readable way.

This will increase your chances for good replies! :)

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! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, 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, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.

[betmatt]

I don't believe that your post is well written. It is however hard to tell because you have not marked out the question properly, using the HTML markup language tags. I suggest that you use the break command via HTML. Then look to whether you can further improve the layout. While doing that you might find that the answer to your question either comes to you or you find that the logic calls you to make your own enquiries. Just a thought.

[Anonymous Monk]

You need to tell us a lot more about how these 400 scripts were built right now. Did they use ODBC to connect? If so, you might be able to do a lot by changing the ODBC data-source definition itself, which is (by design) centrally managed. Changing the SQL and/or programming within the scripts is more problematic since the two database engines are not quite the same. What we – and you – need right now are details. For instance, can you post the source-code of one small but complete script for our inspection? Before you and your colleagues start to "actually change anything anywhere," you need to plan this project and its deployment quite thoroughly.

[talexb]

Right -- and I didn't tell you everything about how I set this client up. I used a module for credentials and the DSN information like this:

package ABCDB;

our $DSN      = 'Driver=FreeTDS;ServerName=SqlServer;Database=ABCDAT';
our $User     = 'User';
our $Password = 'Password';

our $Attributes =
  { LongReadLen => 65535, LongTruncOk => 1, odbc_query_timeout => 30 }
+;

Then, in each of the scripts I wrote, a connection just uses the boilerplate

my $dbh = DBI->connect ( "dbi:ODBC:$ABCDB::DSN", $ABCDB::User,
  $ABCDB::Password, $ABCDB::Attributes );

And a final note .. this module is in a separate directory specifically so that it's *not* included in the repository. Never include any authentication in a repository, even if it's a private one.