Using Two Different Database types in Perl

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

I am trying to execute SQLs from perl in two different databases Vertica and Db2. I connect to vertica using ODBC driver and to Db2 using DB2 driver. I connect to one database at a time and try to execute the same sqls in both database. When I connect to vertica first and execute the sql it works perfeclty then I issue the <my database handle>->disconnect. And try to connect to db2 to execute the same sql. But I get an error saying the odbc driver could not find the data source name. I think the perl program uses the same odbc driver to connect to Db2 eventhough I specially mention to use DB2 Driver. At the same time. When I execute the sql in DB2 first . It works perfectly but when I try to connect to vertica I am getting an error from IBM DB2 driver. Perl is not taking the odbc driver mentioned in the connection string. I use the disconnect after executing the sql in a database. MY doubt is there any constraint I have to use only one database driver type. Is there any limitation in perl in resetting the drivers used in perl program. Is there a way in perl to solve the issue.

Comment on Using Two Different Database types in Perl

Replies are listed 'Best First'.
Re: Using Two Different Database types in Perl by wjw (Priest) on Aug 03, 2014 at 05:37 UTC
An example is provided Here, look at the end of section 4.4.1. Be easier to help if you provide code you are using.... Hope you find that helpful... Copied directly from link above... #!/usr/bin/perl -w # # ch04/connect/ex4: Connects to two database, one Oracle, one mSQL # simultaneously. The mSQL database handle has # auto-error-reporting disabled. use DBI; # Load the DBI module ### Perform the connection using the Oracle driver my $dbh1 = DBI->connect( "dbi:Oracle:archaeo", "username", "password" +) or die "Can't connect to Oracle database: $DBI::errstr\n"; my $dbh2 = DBI->connect( "dbi:mSQL:seconddb", "username", "password" , + { PrintError => 0 } ) or die "Can't connect to mSQL database: $DBI::errstr\n"; exit; [download] ...the majority is always wrong, and always the last to know about it... Insanity: Doing the same thing over and over again and expecting different results... A solution is nothing more than a clearly stated problem...otherwise, the problem is not a problem, it is a facct wjw	[reply] [d/l]
Re: Using Two Different Database types in Perl by erix (Prior) on Aug 03, 2014 at 05:26 UTC
Can you post a self-contained example.	[reply]
Re: Using Two Different Database types in Perl by roboticus (Chancellor) on Aug 04, 2014 at 18:56 UTC
I frequently connect to multiple different types of databases at the same time in perl with DBI, DBD::ODBC, DBD::SQLite, DBD::Oracle, and friends. If you're having trouble, then you're doing something wrong. I find it difficult to believe that you didn't: Give the exact error message Show your connect strings Give a small example of the code that's failing If we had that information we could do something other than tell you that "Yep, you've got a problem. Good luck with that." ...roboticus When your only tool is a hammer, all problems look like your thumb.	[reply]
Re: Using Two Different Database types in Perl by locked_user sundialsvc4 (Abbot) on Aug 04, 2014 at 14:42 UTC
You definitely need to reduce this to a test-case that clearly demonstrates the (minimal) problem. My suspicions of a bug in your program are aroused by what you say ... “the same SQL.” You need to be absolutely certain that you are performing the same steps each time: connect, prepare the SQL query (from a text string), execute the query, close everything, disconnect. Leaving out none of these steps, and not re-using any Perl object. That’s why a short piece of example code is needed.