Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

Two ODBC Connections

by drodinthe559 (Monk)
on Aug 18, 2008 at 23:05 UTC ( #705095=perlquestion: print w/replies, xml ) Need Help??
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();

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.

Re: Two ODBC Connections
by Skeeve (Vicar) 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
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");
    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.

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 [$!]";
    you'd be able to say
    die "Could not open connection to $DSN_parameter because of [$!]";
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(...);
    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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://705095]
Approved by olus
help
Chatterbox?
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (5)
As of 2018-07-16 16:41 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    It has been suggested to rename Perl 6 in order to boost its marketing potential. Which name would you prefer?















    Results (344 votes). Check out past polls.

    Notices?