Solved

script with multiple connections and inserts

Posted on 2011-02-24
11
190 Views
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.

#!/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

0
Comment
Question by:itortu
  • 7
  • 4
11 Comments
 
LVL 8

Expert Comment

by:pwust
Comment Utility
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
 

Author Comment

by:itortu
Comment Utility
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
 
LVL 8

Accepted Solution

by:
pwust earned 500 total points
Comment Utility
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
 

Author Comment

by:itortu
Comment Utility
Thank you. Why you say "the odd way".
0
 
LVL 8

Expert Comment

by:pwust
Comment Utility
"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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:itortu
Comment Utility
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
 

Author Comment

by:itortu
Comment Utility
I meant hash variables.
0
 

Author Comment

by:itortu
Comment Utility
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
 

Author Comment

by:itortu
Comment Utility
I want to be able to connect to the 3 databases and execute the queries in a more efficient way.
0
 

Author Comment

by:itortu
Comment Utility
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
 
LVL 8

Expert Comment

by:pwust
Comment Utility
You will have to muliply the dbh part in the same way you did with the others.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

A short article about a problem I had getting the GPS LocationListener working.
Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now