Solved

script with multiple connections and inserts

Posted on 2011-02-24
11
192 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
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?
0
 

Author Comment

by:itortu
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.

0
 
LVL 8

Accepted Solution

by:
pwust earned 500 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?
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

Author Comment

by:itortu
ID: 34974575
Thank you. Why you say "the odd way".
0
 
LVL 8

Expert Comment

by:pwust
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".

-Patric
0
 

Author Comment

by:itortu
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.

0
 

Author Comment

by:itortu
ID: 34975112
I meant hash variables.
0
 

Author Comment

by:itortu
ID: 34998807
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
ID: 34998834
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
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.
0
 
LVL 8

Expert Comment

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

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

This is about my first experience with programming Arduino.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
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…

785 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