Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


script with multiple connections and inserts

Posted on 2011-02-24
Medium Priority
Last Modified: 2012-05-11
I would like to ask for help to modify my perl script so that it connects to multiple databases (dev, qa, prod). I just wnat to avoid having 3 separate scripts and just put it all into one.

Right now it just looks at the dev database.

I am pasting a copy of my code.

Thank you very much for your help.


use warnings;
use strict;
use DBI;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft ActiveX Data Objects';

my $objConn = Win32::OLE->new("ADODB.Connection");
my $objCommand = Win32::OLE->new("ADODB.Command");
my $objRecordSet = Win32::OLE->new("ADODB.Recordset");

$objConn->Open( "Driver={Webtrends ODBC Driver}; "
              . "server=myserver; "
              . "Port=7099; "
              . "Database=flrdacbkoji.wct; "
              . "Uid=99999;Pwd=password; "
              . "AccountId=1; "
              . "Language=english; "
              . "ProfileGuid=KlDwrNbKgm6; "
              . "SSL=0;");
$objCommand->{"ActiveConnection"} = $objConn;

$objCommand->{"CommandText"} = "SELECT REPLACE(Titles, 'PowerHouse TV : ', '') as Titles, URLs, Visits "
                             . " FROM Pages"
                             . " WHERE Titles <> ''"
                             . " AND URLs IS NOT NULL"
                             . " AND Titles NOT LIKE '%404 Error Page%'"
                             . " AND Titles NOT LIKE '%302 Found%'"
                             . " AND Titles NOT LIKE '%Search%'"                             
                             . " AND URLs <> 'http://powerhousetv.com/ContactUs/index.htm'"
                             . " AND URLs <> 'http://powerhousetv.com/'"
                             . " AND URLs <> 'http://powerhousetv.com/index.htm'"
                             . " AND URLs <> 'http://powerhousetv.com//index.htm'"
                             . " AND URLs <> 'http://powerhousetv.com/wcm/idcplg/'"
                             . " AND TimePeriod='%start=month;duration=month+1%'"
                             . " ORDER BY Visits"; #DESC LIMIT 6";

$objRecordSet->{"CursorLocation"} = 3; #adUseClient
$objRecordSet->{"CursorType"} = 0;     #adOpenForwardOnly
$objRecordSet->{"LockType"} = 1;       #adLockReadOnly;

$objRecordSet = $objCommand->Execute();

my $dbh = DBI->connect('dbi:Oracle:host=0000.0000.com;sid=0000;port=1521', 
               'powerhouse_most_popular_user', 'bZucuYE2GmN2Ngk2Ecs', 
               { RaiseError => 1, AutoCommit => 0 });

my ($title, $urls, $visits, $cnt);

while (!$objRecordSet->EOF)


	  $title = $objRecordSet->Fields("Titles")->value;
	  $urls = $objRecordSet->Fields("URLs")->value;
	  $visits = $objRecordSet->Fields("Visits")->value;
	  # Contribution Schema
	  ($cnt) = $dbh->selectrow_array("SELECT COUNT(*) FROM wcms_contrib.POWERHOUSEMOSTPOPULAR WHERE TITLE = '$title' AND URL = '$urls' AND VISITS = '$visits'");
	  	unless ($cnt) {
	  				$dbh->do("INSERT INTO wcms_contrib.POWERHOUSEMOSTPOPULAR (TITLE, URL, VISITS, LASTUPDATE) VALUES ('$title','$urls','$visits',SYSDATE)");
	  # Consumption Schema
	  ($cnt) = $dbh->selectrow_array("SELECT COUNT(*) FROM wcms_consum_int.POWERHOUSEMOSTPOPULAR WHERE TITLE = '$title' AND URL = '$urls' AND VISITS = '$visits'");
	  	unless ($cnt) {
	  					$dbh->do("INSERT INTO wcms_consum_int.POWERHOUSEMOSTPOPULAR (TITLE, URL, VISITS, LASTUPDATE) VALUES ('$title','$urls','$visits',SYSDATE)");



Open in new window

Question by:itortu
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4

Expert Comment

ID: 34973830
What would you like to do?

Connect simultaneously to the three databases dev, qa, prod, and compare contents?
Connect to either of the three as choosen by start parameter, and do the same things, but only to one database at a time?
Do something else?

Author Comment

ID: 34973879
To use the same script, adding connections to two other databases and the queries simultaneously, use the same select and insert statements as in the script but for the other two databases. Correct you are.

Thank you.


Accepted Solution

pwust earned 2000 total points
ID: 34974540
The odd way is, multiplying objects $objConn, $objCommand, and $objRecordSet into:
$objConnDev, $objConnQA, $objConnProd
$objCommandDev, $objCommandQA, $objCommandProd
$objRecordSetDev, $objRecordSetQA, $objRecordSetProd
Now Open the three connections to the databases, then after putting the query into a String $Query:
$Query = "SELECT......";
$objCommandDev->{"CommandText"} = $Query;
$objCommandQA->{"CommandText"} = $Query;
$objCommandProd->{"CommandText"} = $Query;

Is this what you mean?
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 34974575
Thank you. Why you say "the odd way".

Expert Comment

ID: 34974875
"odd", since you have three distinctive names for the object variables. A more sophisticated way would be using hash variables, something like:
$objConn['dev'], $objConn['qa'], $objConn['prod'], etc.

This wood look MUCH better in the code, but since I do not do Perl every day, I am not quite sure on correct handling.

You could then loop through the connections by using "each" or "keys".


Author Comment

ID: 34975106
I like the use of variables. I thank you for your comment, I am just going to wait and hope someone makes another comment, hopefully with a more sophisticated solution like the one you are referring to.


Author Comment

ID: 34975112
I meant hash variables.

Author Comment

ID: 34998807
What about the dbh parameter do I have to multiply it too?

my $dbh = DBI->connect('dbi:Oracle:host=0000.0000.com;sid=0000;port=1521', 
               'powerhouse_most_popular_user', 'bZucuYE2GmN2Ngk2Ecs', 
               { RaiseError => 1, AutoCommit => 0 });

Open in new window


Author Comment

ID: 34998834
I want to be able to connect to the 3 databases and execute the queries in a more efficient way.

Author Comment

ID: 34999021
also, the data i get from the first SELECT query, the one that uses the objCommand
should be inserted in the 3 databases. I don't think I need to multiply that part. I just need to multiply the connection string and use the same data to insert in the dev, qa and prod databases.

Expert Comment

ID: 35000307
You will have to muliply the dbh part in the same way you did with the others.

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A short article about problems I had with the new location API and permissions in Marshmallow
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
Introduction to Processes
Loops Section Overview

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question