Using a HASH to store a DBI connection

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

Hi, I'm hoping someone can help.

I am writing a script which I want to connect to different schemas and run some common queries. I've decided to use DBI for the connectivity and a hash to store the connectivity detail. My question is does DBI allow you to store all details in a single value or do you need to list each value separately.

Here is the code I am using

 #!/usr/local/bin/perl -w
use strict;
use DBI;                # for database connectivity



my %db_conn = (
   a_manager => "DBI:Oracle:host=hostname1;sid=ABCDB01;port=1300','a_m
+anager','a_manager',",
   b_manager => "DBI:Oracle:host=hostname1;sid=ABCDB01;port=1300','b_m
+anager','b_manager',",
   c_manager => "DBI:Oracle:host=hostname1;sid=ABCDB01;port=1300','c_m
+anager','_manager',",
   d_manager => "DBI:Oracle:host=hostname1;sid=CBADB01;port=1300','d_m
+anager','d_manager',",
   );

for my $owner (keys %db_conn) {

print "Running $owner stats_table creation\n";

my $data_source = $db_conn{$owner};

print "$data_source\n";

my $query = qq (
        select * from cat
               );
my $dbh = DBI->connect($data_source) or die "Couldn't connect to datab
+ase: " . DBI->errstr;

my $sth = $dbh->prepare($query)
                or die "Couldn't prepare statement: " . $dbh->errstr;

$sth->execute() or die "Couldn't execute statement: " . $sth->errstr;

}
[download]

I am currently getting the error ORA-24327: need explicit attach before authenticating a user (DBD ERROR: OCISessionBegin)

If, however, I pass the values as individual values it is successful

Comment on Using a HASH to store a DBI connection Download Code

Replies are listed 'Best First'.
Re: Using a HASH to store a DBI connection by choroba (Cardinal) on Dec 16, 2015 at 23:11 UTC
`connect` needs three arguments: connection string, user name, and password (at least that's how I understand the documentation). You're giving it only one argument. If you want to store the username and password in the hash, too, use a hash of arrays: `my %db_conn = ( a_manager => [ 'DBI:Oracle:host=hostname1;sid=ABCDB01;port=1300', +'a_manager', 'a_manager' ], b_manager => [ 'DBI:Oracle:host=hostname1;sid=ABCDB01;port=1300', +'b_manager', 'b_manager' ], c_manager => [ 'DBI:Oracle:host=hostname1;sid=ABCDB01;port=1300', +'c_manager', 'c_manager' ], d_manager => [ 'DBI:Oracle:host=hostname1;sid=CBADB01;port=1300', +'d_manager', 'd_manager' ], );` [download] And dereference the value in the connection call: `my $dbh = DBI->connect(@$data_source) or ...` [download] Update: Added the example. ($q=q:Sq=~/;[c](.)(.)/;chr(-\|\|-\|5+lengthSq)`"S\|oS2"`map{chr \|+ord }map{substrSq`S_+\|`\|}3E\|-\|`7**2-3:)=~y+S\|`+$1,++print+eval$q,q,a, [download]	[reply] [d/l] [select]
Re: Using a HASH to store a DBI connection by NetWallah (Canon) on Dec 16, 2015 at 23:17 UTC
Try this: my %db_conn = ( a_manager => ['DBI:Oracle:host=hostname1;sid=ABCDB01;port=1300','a_ +manager','a_manager',], b_manager => ['DBI:Oracle:host=hostname1;sid=ABCDB01;port=1300','b_ +manager','b_manager',], c_manager => ['DBI:Oracle:host=hostname1;sid=ABCDB01;port=1300','c_ +manager','c_manager',], d_manager => ['DBI:Oracle:host=hostname1;sid=CBADB01;port=1300','d_ +manager','d_manager',], ); for my $owner (keys %db_conn) { print "Running $owner stats_table creation\n"; my @data_source = @{ $db_conn{$owner} }; print "@data_source\n"; my $dbh = DBI->connect(@data_source) or die "Couldn't connect to da +tabase: " . DBI->errstr; #.... processing } [download] "I can cast out either one of your demons, but not both of them." -- the XORcist	[reply] [d/l]
Re: Using a HASH to store a DBI connection by runrig (Abbot) on Dec 16, 2015 at 23:14 UTC
Store the connection info as array refs, and then dereference it when connecting: `my %db_conn = ( a_manager => [ 'DBI:Oracle:host=hostname1;sid=ABCDB01;port=1300','a +_manager','a_manager' ], ... ); ... my $dbh = DBI->connect(@$data_source) or die ...` [download]	[reply] [d/l]
Re: Using a HASH to store a DBI connection by graff (Chancellor) on Dec 17, 2015 at 02:52 UTC
In case it helps, bear in mind that you can store the database handles (returned by DBI->connect) in a hash as well; they can all be open and accessible simultaneously.	[reply]