Two ODBC Connections

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

Gurus - In keeping with a good Perl programming style, is it better to have two ODBC connections to different databases declared twice? Or, is it better to have one declaration put in an array or hash that will loop through? I'm launch a sciprt that will check the status of two databases, see the below code.

#!/usr/bin/perl -w
use strict;

use Win32::ODBC;

my $DSN = "DSN=DATABASE1;UID=sa;PWD=password;";
my $connection = new Win32::ODBC("$DSN"); 

if (!$connection){die "Could not open connection to DSN because of [$!
+]";}

my $SQL = "SELECT Field1, Field2 FROM table1";

if ($connection->Sql($SQL))
        {   print "SQL failed.\n";
            print "Error: " . $connection->Error() . "\n";
            print $connection->Error() . "\n";
            $connection->Close();
            die;}
        #Report header.
        print "Field 1" . "\t\t\t" . "Field 2" . "\n";
        
while ($connection->FetchRow())
    {   my %dataRow =$connection->DataHash();       
        print $dataRow{field1} . "\t\t" . $dataRow{field2} . "\n";}

$connection->Close();

my $DSN2 = "DSN=DATABASE2;UID=sa;PWD=password;";
my $connection2 = new Win32::ODBC("$DSN"); 

if (!$connection2){die "Could not open connection to DSN because of [$
+!]";}

my $SQL2 = "SELECT * FROM TABLE2";

if ($connection2->Sql($SQL2))
        {   print "SQL failed.\n";
            print "Error: " . $connection->Error() . "\n";
            print $connection->Error() . "\n";
            $connection->Close();
            die;}
        
while ($connection2->FetchRow())
    {   my %dataRow2 =$connection2->DataHash();       
        print $dataRow2{field1} . "\t\t" . $dataRow2{field2} . "\n";}

$connection->Close();
[download]

Comment on Two ODBC Connections Download Code

Replies are listed 'Best First'.
Re: Two ODBC Connections by olus (Curate) on Aug 18, 2008 at 23:22 UTC
In keeping with a good Perl programming style Since you are showing concerns on this matter, take a look a perlstyle - Perl style guide Answering your question, my preference would be to give descriptive names to the connections instead of `$connection` and `connection2`.	[reply] [d/l] [select]
Re: Two ODBC Connections by Skeeve (Parson) on Aug 19, 2008 at 08:56 UTC
I think it's not a question of style or Perl, it's a question of: What is your programm supposed to do? Is it a general tool for monitoring databases? Then you should probably put all the configuration stuff into a separate file or some separate files and your program should retrieve the connection information from there. That way you can easily add new databases or change existing ones without touching your program. Is the main purpose something else and you have check upfront whether the given 2 databases work, I would use a loop if the tests are identical. If the tests are not identical, I'd use two connections tests (and descriptive names). `s$$([},&%#}/&/]+}%&{});#$&&s&&$^X.($'^"%]=\&(\|?{%` `+`.+=%;.#_}\&"^"-+%*).}%:##%}={~=~:.")&e&&s""`$''`"e	[reply] [d/l] [select]
Re: Two ODBC Connections by gone2015 (Deacon) on Aug 19, 2008 at 09:22 UTC
I guess there is some application-specific reason that you rejected the notion of a subroutine as a way of simplifying this. I assume that: `my $DSN2 = "DSN=DATABASE2;UID=sa;PWD=password;"; my $connection2 = new Win32::ODBC("$DSN");` [download] isn't quite what was intended... and illustrates something. If the script dies "`Could not open connection to DSN because of xxxx`" it's not clear to me how one can tell which of the two databases is problematic. It may be deliberate that the printing of the selected rows gives no extra indication of which database they come from. If it is ever necessary to check three databases, another cut/paste/edit/scrutinize-carefully job will be required. I suggest that your code fragment would be unlikely to win prizes for style, in any language -- even Perl, for all its quirky wonderfulness.	[reply] [d/l] [select]
Re: Two ODBC Connections by apl (Monsignor) on Aug 19, 2008 at 11:29 UTC
Whenever you feel the need to cut and paste a block of code, it's generally a better idea to turn that block of code into a subroutine. Further, when reporting an error, it's good to report as much relevant information as possible. Rather than saying `die "Could not open connection to DSN because of [$!]";` [download] you'd be able to say `die "Could not open connection to $DSN_parameter because of [$!]";` [download]	[reply] [d/l] [select]
Re: Two ODBC Connections by pajout (Curate) on Aug 19, 2008 at 13:59 UTC
TIMTOWDI :>) In principle, the application have to know, which connection it wants to use. My recently used solution is, __briefly__: `#configuration my $conf = " <MyConfPool> <connection id="firstconn"> ...DSN credentials </connection> <connection id="secondconn"> ...DSN credentials </connection> </MyConfPool>"; #create object holding connections use MyConnPool; my $conn_pool = MyConnPool->new(conf=>$conf); ... #use some connection my $conn1 = $conn_pool->get("firstconn"); $conn1->Sql(...); my $conn2 = $conn_pool->get("secondconn"); $conn2->Sql(...);` [download] The goal is to implement whole maintenance of connections in that slightly described module, other parts of application will just use it, not carrying if connection is already opened e.t.c.	[reply] [d/l]