dctechie has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I'm working with a web based perl application that's currently coded to use ODBC. A sysadmin here recommended recoding the site to use DBI since the database connection's failing in the application, even though the ODBC connection test works. This is 32 bit code running on windows 2008 64 bit. I've searched for some examples of DBI w/ SQL Server but they all used ODBC. How can I get rid of the ODBC code and use DBI to connect directly to the database? 
my $curdir = cwd()."";
require "$curdir/common.pl"; 

my (%g_emails,@dwd_to,$db2_table,$db2_stepcourse);
my $server = &server_name;
#&debug($server);
my $g_n2_url = "http://$server/nihits.pl";
$g_cookiepath = "/";
if (($server eq "nominate-test.**.***.net") or ($server eq "nihitstest
+.**.***.net")){
    $dsn = "NIHITSDSN";
    $dbuser = "nihits";
    $dbpass = "********";    
}elsif ($server eq "nominate.**.***.net"){
    $dsn = "NIHITSdsn";
    $dbuser = "nihits";
    $dbpass = "********";
}
my $dbname = "DBI:ODBC:". $dsn;
my $g_delivery = &current_website;

[download]

Replies are listed 'Best First'.
Re: Switching from ODBC to DBI
by locked_user sundialsvc4 (Abbot) on Apr 11, 2011 at 16:11 UTC

    I cordially recommend that you put the sysadmin’s advice to tough, objective, fact-finding scrutiny before taking any action.   You need to get to the bottom of exactly why “ODBC is failing in the application.”   You are contemplating a massively pervasive change to this application which might be tantamount to a complete rewrite.   (In any case it will, to some degree, de-stabilize everything that is now there.)   If the “last mile” to the database consists of ODBC, and if the root cause of the problem is (as I frankly suspect...) not a flaw in the Perl code, then no amount of Perl wrapping or re-wrapping will address the core issue, which undoubtedly will remain.   Get yourself to a point where you have a test case that proves the existence of the problem, and another one which proves that your contemplated solution will solve it.

Re: Switching from ODBC to DBI
by InfiniteSilence (Curate) on Apr 11, 2011 at 17:54 UTC
    I agree with what sundialsvc4 said but I'd add a few things:
    • When things start breaking and you start down the merry path of fact-finding don't do it with blinders on. When you see other problems with the code flag them as well. An ounce of preventative maintenance is worth a pound of cure and please tell me that you are using some kind of software versioning system before you do anything...even just look...
    • While you are at it, why not take a few scripts down the the bare fetch/update/whatever you are doing with the DB and run some benchmarks? You may find that switching to DBI saves clock cycles and works better (or not).
    • Looks to me like some incremental rewriting of your code is probably in order anyway. You could move things to packages for one thing instead of requiring .pl files methinks.

    Celebrate Intellectual Diversity

[reply]
Re: Switching from ODBC to DBI
by thargas (Deacon) on Apr 11, 2011 at 18:44 UTC

    Not to disagree with previous comments (far from it), but...

    At the end of your code snippet, it looks like you're constructing a DBI DSN which uses ODBC. The names of some variables at the top indicate that you're trying to talk to a DB2 database. I.E. it looks to me as though you may already be using DBI, talking to a DB2 database through the DBD::ODBC driver. Assuming that I've guessed correctly, your job may be as simple as constructing the correct DSN using DBD::DB2. Look at the docs for DBD::DB2

    If you don't have DBD::DB2, building it requires access to the appropriate DB2 client libraries, which may be an issue.

[reply]
Re: Switching from ODBC to DBI
by mje (Curate) on Apr 12, 2011 at 07:47 UTC
[reply]
      VERY informative post. This is exactly what I'm going through now. Just using ODBC in a 64 bit environment is beginning to be more trouble than it's worth. Most likely we'll re-image the server and install Windows 2008 32 bit. At this point, we just don't have the bandwidth to do a code rewrite.
[reply]
Re: Switching from ODBC to DBI
by dctechie (Initiate) on Apr 11, 2011 at 19:43 UTC
    Hey guys. thanks for the responses. Actually there's nothing wrong with this app when it's run on a 32 bit OS. this issue only started happening last week when I copied the code to a windows 2008 64 bit server to begin testing. I may just push them to migrate to windows 2003 instead of windows 2008. the only variable is the 64 bit OS. Other than that, the code works fine. Have you guys had to make any changes to PERL code that runs fine on 32 bit systems to enable it to run on 64 bit systems?
[reply]

      /me nods...
      I expected that.

      The place to begin is to carefully test all of the other, non-Perl applications to determine if any of them have difficulties regarding ODBC.   Even if “you are sure” that it “must be” okay, and even though you may feel, “oh, that’s ridiculous, how could there be?” ... well, as I have heard it said, “Trust, but Verify.™”

      The next thing to investigate is whether you have the 64-bit version of whatever driver-toolchain(s) your application is now using.   (This is where you are for the first time specifically investigating this application’s technologies and interfaces.)   The most likely culprit is, as I have also heard it said, “a ‘bumpy thunky’ problem.”   That is to say, 64-vs.-32-bit incompatibilities.   They pop up all over the place but they are centrally located and thus centrally fixed.

      In any case, while you may find that you have to make some changes to an application, it is much more likely to expect that you will simply need to reconfigure and/or upgrade some specific piece(s) of middleware... drivers and such.   Arguments in favor of a “Cowabunga!!” approach to a problem like this are IMHO not sustainable.

      I had an issue on a customer site - the guys over there installed a 32 bit Perl on a 64 bit system.

      However: Write some tests with debugging and tracing enabled to track down the issues.

      HTH,
      Thomas

[reply]

Back to Seekers of Perl Wisdom