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?
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


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

Industry Leaders: 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!

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Suggested Courses

764 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