Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

DBI and oracle: How can I specify more than one host in connect string?

by Skeeve (Vicar)
on Dec 02, 2009 at 15:28 UTC ( #810588=perlquestion: print w/ replies, xml ) Need Help??
Skeeve has asked for the wisdom of the Perl Monks concerning the following question:

Is there a way to specify more than one host in a DBI connection string?

use DBI; $dbh=DBI->connect("dbi:Oracle:host=host1.my.do.main;sid=MYDB","me","se +cret");

As I didn't find anything in the documentation, I looked into the source, but couldn't understand it. Maybe someone here knows?

The reason behind this is, that I usually connect by just using the SID. Unfortunately something is wrong with the perl installation on the machine and DBI::connect dumps core (segmentation fault) as soon as I don't supply a host. Usually if I connect with just the SID, it should find a host from tnsnames.ora. I thought, a workaround would be to give all the hosts in the connect string.


s$$([},&%#}/&/]+}%&{})*;#$&&s&&$^X.($'^"%]=\&(|?*{%
+.+=%;.#_}\&"^"-+%*).}%:##%}={~=~:.")&e&&s""`$''`"e

Comment on DBI and oracle: How can I specify more than one host in connect string?
Select or Download Code
Re: DBI and oracle: How can I specify more than one host in connect string?
by mje (Deacon) on Dec 02, 2009 at 16:12 UTC

    You can only connect to one oracle at a time with the connect method so I cannot see any point in allowing more than one host in the connect string.

      The point is: Should the first host fail, the other one can be used. This is how it's done when relying on SID only, isn't it?


      s$$([},&%#}/&/]+}%&{})*;#$&&s&&$^X.($'^"%]=\&(|?*{%
      +.+=%;.#_}\&"^"-+%*).}%:##%}={~=~:.")&e&&s""`$''`"e
Re: DBI and oracle: How can I specify more than one host in connect string?
by roboticus (Canon) on Dec 02, 2009 at 17:11 UTC
    Skeeve:

    mje has it right, you just can't do it that way. However, you could have multiple connection strings. Try the first one, and if it fails, move on to the next one. Stop when you get one that works. Something like the following (untested and probably dreadful):

    use DBI; my $dbh; my @DSNs = [ { DSN='blah1', UID='user1', PWD='password1' }, { DSN='blah2', UID='user2', PWD='password2' }, ]; =DBI->connect("dbi:Oracle:host=host1.my.do.main;sid=MYDB","me","secret +"); my @DSNs = [ 'blah1', 'blah2', 'blah3'); for my $hr (@DSNs) { $dbh = DBI->connect($$hr{DSN}, $$hr{UID}, $$hr{PWD}); last unless defined $dbh; }
    ...roboticus

      Okay :-( Then I'm lost as I'm not allowed to (nor willing to) change all the code we have to cope for this stupid bug I have. I think we "simply" have to find the reasson why per dumps core. After that we can do it the right way.


      s$$([},&%#}/&/]+}%&{})*;#$&&s&&$^X.($'^"%]=\&(|?*{%
      +.+=%;.#_}\&"^"-+%*).}%:##%}={~=~:.")&e&&s""`$''`"e
        Skeeve:

        That would be my suggestion. Understanding and fixing errors is usually much more profitable than engineering band-aids.

        ...roboticus

        I would tend to agree with roboticus on this one. If you are getting a coredump when using a SID something is wrong and although you only know about one thing being broken right now it may turn out you've got other problems you have not found yet.

        However, I'm a little surprised at the comment "not allowed to (nor willing to) change all the code we have to cope for this stupid bug" as a) I find it difficult to see just how much code you'd have to change for a change in connect calling (how many calls to connect can you have scattered around) b) you have not ascertained where the problem is yet and c) the use of "stupid bug" doesn't seem too productive. We don't know where the problem lies; it may be in your perl, DBD::Oracle, OCI, NET8, the way any of them were built or perhaps it is a fixed problem and you are using old versions. Did it ever work on this machine and did you install DBD::Oracle without running the tests or install it after running the tests and some of them failed.

        Step back and check sqlplus out with your SID to check that is working then if that works move on to unpacking DBD::Oracle, building it and running the tests - do they pass? If you still have problems the dbi-users mailing list is a good place to start but make sure you post the relevant information like platforms, perl -V, versions of modules etc.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://810588]
Approved by moritz
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (8)
As of 2014-07-13 15:26 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (250 votes), past polls