script with multiple connections and inserts

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.

#!/usr/local/bin/perl

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)");
	  	}

    $objRecordSet->MoveNext();}

$dbh->disconnect;

Open in new window

itortuAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
pwustConnect With a Mentor Commented:
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?
0
 
pwustCommented:
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?
0
 
itortuAuthor Commented:
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.

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
itortuAuthor Commented:
Thank you. Why you say "the odd way".
0
 
pwustCommented:
"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".

-Patric
0
 
itortuAuthor Commented:
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.

0
 
itortuAuthor Commented:
I meant hash variables.
0
 
itortuAuthor Commented:
What about the dbh parameter do I have to multiply it too?

Thanks.
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

0
 
itortuAuthor Commented:
I want to be able to connect to the 3 databases and execute the queries in a more efficient way.
0
 
itortuAuthor Commented:
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.
0
 
pwustCommented:
You will have to muliply the dbh part in the same way you did with the others.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.