Perhaps you should start by showing us a sketch of the code you have already. In the mean time, I presume you have discovered DBI, but if not you really want to take a look.

After that you might like to use the database related tricks mentioned by I know what I mean. Why don't you? to put together a small sample app to either play with yourself, or to post here as a starting point for discussing where you are having trouble in a more focused fashion.

hi thanks for your reply. I have been familiarizing myself with some concepts that should be used with DBs and Perl. I also found this piece of code that I can use as basis to do mine. I believe this code (from my limited perl understanding) extracts the data and updates the DB. I am only meant to be extracting data from the 'LES' and 'Twenty' databases and find the differences. From what I have been informed that the term 'point_to_twenty' is the old term for 'LES'. Also i think this code is only concerned with the 'LES' DB. I am very confused... Here is the code below, thank you in advance

#!perl

my $DEFAULT_TWENTY_SITE_UID = -1;

# to compile application
# ppm install DBD-Oracle 
#perlapp --add=DBD::Oracle --add=DBI --dependent rtf.pl

# requires module: DBD-Oracle
#declare all the mods required
use DBI;
use DBD::Oracle;
use Getopt::Std;
use strict;

#my indicates that the variables are private to a function to prevent 
+them from gettting overwritten
my $implementation = "";
my %cfg;

#process the config file which is the input file and check that it ope
+ns correctly
sub ProcessConfigFile {
    my ($configFile) = @_;
    
    my $cantOpenFile = 0;
    open(FILEIN, $configFile) or $cantOpenFile = 1;
    if ($cantOpenFile == 1) {
        logger("ERROR", "Can't open $configFile\n");
        exit;
    }
    
#log files present their data in a special format. they allow one to m
+ake the distinction between stateful and statelss data. the entries i
+n the log file need to be connected/associated in some way ie. assign
+ing a PK    
    my @logData = <FILEIN>;
#declare a private variable 'line'    
    my $line;
    foreach $line (@logData) {
#chomp gets rid of any new line character that is read in from the inp
+ut file
        chomp($line);    
        if ($line !~ /\s.\#/) {
            $line =~ /(\S+)\s+(\S+)/;
            my $var = $1;
            my $val = $2;
    
            if ($var =~ /LES_USER/) {
                $cfg{LES_USER} = $val;
            } elsif ($var =~ /LES_PSWD/) {
                $cfg{LES_PSWD} = $val;
            } elsif ($var =~ /LES_SERVER/) {
                $cfg{LES_SERVER} = $val;
            } elsif ($var =~ /LES_PORT/) {
                $cfg{LES_PORT} = $val;
            } elsif ($var =~ /LES_SID/) {
                $cfg{LES_SID} = $val;
            }
        }
    }
    close(FILEIN);
#checking and verifying the username and password
    if (length ($cfg{LES_USER}) == 0) { print ("ERROR: ", "No LES_USER
+ Specified in config file.\n"); }
    if (length ($cfg{LES_PSWD}) == 0) { print ("ERROR: ", "No LES_PSWD
+ Specified in config file.\n"); }
    if (length ($cfg{LES_SERVER}) == 0) { print ("ERROR: ", "No LES_SE
+RVER Specified in config file.\n"); }
    if (length ($cfg{LES_PORT}) == 0) { print ("ERROR: ", "No LES_PORT
+ Specified in config file.\n"); }
    if (length ($cfg{LES_SID}) == 0) { print ("ERROR: ", "No LES_SID S
+pecified in config file.\n"); }        
}


######################################################################
+#####
# connect_point_to_TWENTY
#
# inputs                  
#                               
# returns       
#
# Algorithm
#
######################################################################
+#####
sub connect_point_db
{
    my ($server, $port, $sid, $user, $passwd) = @_;    
    my ($dbh);
        
    $dbh = DBI->connect("dbi:Oracle:host=$server;port=$port;sid=$sid",
+ $user, $passwd);
    $dbh->{LongReadLen} = 1000;
    $dbh->{LongTruncOk} = 1; 
    
    return $dbh;
}
 
sub create_from_sql
{
    my ($sql, $name, $dbh) = @_;
    my ($sth, $row);
     
    $sth = $dbh->prepare($sql) or die $dbh->errstr;    
    $sth->execute; 

    my $MAX_ROWS = 100;

    my $rows = []; # cache for batches of rows
    my $cols = []; # cache for binded columns
    
    my $i;
    for ($i=1; $i<=$sth->{NUM_OF_FIELDS}; $i++) {
        $sth->bind_col($i, \@$cols[$i]);
    }
    
#fetch a sigle row at a time
    while($row=$sth->fetchrow_hashref) { 
       my $i;
       print OUTFILE "\t<$name>\n";
       for ( $i = 1 ; $i <= $sth->{NUM_OF_FIELDS} ; $i++ ) {
           my $fieldName =  $sth->{NAME}->[$i-1];
           my $fieldValue = @$cols[$i]; #$row->{$fieldName};
           $fieldValue =~ s/&/&amp;/g;
           $fieldValue =~ s/'/&apos;/g;
           $fieldValue =~ s/"/&quot;/g;
           $fieldValue =~ s/</&lt;/g;
           $fieldValue =~ s/>/&gt;/g;
           $fieldValue =~ s/\x96/\x39/g; #replace ` (non xml char) wit
+h '
           $fieldValue =~ s/[\x0-\x1f]||[\xa0-\xff]//g; #remove any no
+n xml characters               
           print OUTFILE "\t\t<$fieldName>$fieldValue</$fieldName>\n";
       }    
       print OUTFILE "\t</$name>\n\n";     
    }  
    $sth->finish;
}

sub main {

    my ($impl) = @_;
    $implementation = $impl;
    
    my $numArgs = $#ARGV + 1;
    if (!($numArgs == 3 || $numArgs == 4)) {
        print "point to file: Extracts data from a point (LES) databas
+e to a file for either MCC or TWENTY Import\n";
        print "rtdf = point To TWENTY File\n";        
        print "rtcf = point To MCC File\n";                
        print "Usage: rtcf|rtdf configFile.cfg fileName.xml timezone_o
+ffset [company]\n";
        exit(0);
    }
        
    ProcessConfigFile($ARGV[0]);
    my $server = $cfg{LES_SERVER};
    my $port   = $cfg{LES_PORT};
    my $sid    = $cfg{LES_SID};
    my $user   = $cfg{LES_USER};
    my $passwd = $cfg{LES_PSWD};
    my $file   = $ARGV[1];
    
    my $d_dbh = connect_point_db($server, $port, $sid, $user, $passwd)
+;
    
#    my $siteValue = "datacenters.dc_id";
#    my $companyValue = "business.business_ID";
    my $companyCondition = "";

    my $includeCompaniesAndSites;
    if ($numArgs == 4) {
        $includeCompaniesAndSites = 1;
        my @companies = split(/,/, $ARGV[3]);
        $companyCondition = "";
        foreach my $company(@companies) {
            #print "$company\n";
            if ($companyCondition eq "") {
                $companyCondition = "AND ((business.business_name = $c
+ompany)"
            }            
            else {
                $companyCondition = $companyCondition . "OR (business.
+business_name = $company)";
            }            
        }        
        if ($companyCondition ne "") {
            $companyCondition = $companyCondition . ")";
        }        
    }        
    
    if (($implementation eq "TWENTY") && ($includeCompaniesAndSites !=
+ 1)) {
        $siteValue = $DEFAULT_TWENTY_SITE_UID;
    }
    
    my $devicesql =  "SELECT distinct(server.system_id) masterid, serv
+er.primary_name hostname, server.arpa_domain domainname,
                           server.os_type os, datacenters.dc_id as sit
+e,
                           business.business_id as company,datacenters
+.dc_timezone as timezone,
                           'nnmserver1.mydomain.com' as PrimaryNNM, 'n
+nmserver1.mydomain.com' as SecondaryNNM, 'itoserver1.mydomain.com' as
+ PrimaryITO, 'itoserver1.mydomain.com' as SecondaryITO,
                           server.status as ServiceStatus, '24x7' as S
+erviceWindow,  server.mgmt_region as Region, 'N/A' as HardwareSupport
+, '0' as RadiaManaged, nvl(monlvl, 0) as EventNotify, TO_CHAR(prod_da
+te, 'YYYY-MM-DD hh:mm:ss') as DateProduction, TO_CHAR(decomm_date, 'Y
+YYY-MM-DD hh:mm:ss') as DateObsolete, '1' as Reported, '$server' as M
+asteredBy,
                           ip_name, nvl(substr(ip_name, 0, instr(ip_na
+me, '.', 1)-1), ip_name) as NatHostName, 
                           substr(ip_name, instr(ip_name, '.', 1)+1) a
+s NatDomainName
                      FROM LES.server,                
                           LES.datacenters,
                           LES.server_customer,
                           LES.business,
                           LES.status_dates,       
                           (select * from LES.ip_info where ip_type = 
+'NAT') ip_info,
                           (select '1' as monlvl, system_id from LES.s
+rs ml where ml.par_cd = 'MON_LEVEL' and ml.SRS_VALUE <> 'None') ml   
                     WHERE     (ml.system_id(+) = server.system_id)
                            AND (datacenters.dc_id = server.dc_id)
                            AND (server.system_id = server_customer.sy
+stem_id)
                            AND (business.business_id = server_custome
+r.business_id)
                            AND (ip_info.system_id(+) = server.system_
+id)
                            AND (server.system_type = 'server')
                            AND (status_dates.system_id = server.syste
+m_id)
                            AND (server.mgmt_region = 'Asia Pacific')
                            
                            AND (monlvl = 1)";
                                                   
    
    my $tzsql =      "SELECT unique(DC_TIMEZONE) as display, DC_TIMEZO
+NE as displaySTD, TO_NUMBER(NVL(substr(DC_TIMEZONE, 4, 5), '0')) * 60
+  as bias
      FROM LES.datacenters";   
    
    my $regionsql =  "SELECT unique(region) FROM ($devicesql) devices"
+;  
    
#   my $devicetypesql = "";  
    
    my $ossql =      "SELECT unique(os)  FROM ($devicesql) devices";  
    
#   my $hwssql =     "";
    
    my $ptsql =      "SELECT unique(PlatformType)  FROM ($devicesql) d
+evices";
    
#    my $svcwinsql = "SELECT SvcWinDesc from tblSvcWindow";
    

    ### limit compaines to specified companies to stop companies which
+ are linked to shared sites from being imported which will cause prob
+lems when switching to all device import
    my $companysql = "SELECT distinct(business_id) as masterid, SUB_BU
+SINESS_NAME as Company,  'nnmserver1.mydomain.com' as PrimaryNNM, 'nn
+mserver1.mydomain.com' as SecondaryNNM, 'itoserver1.mydomain.com' as 
+PrimaryITO, 'itoserver1.mydomain.com' as SecondaryITO,
                             '1' as MCCMaster, '1' as MCCProduction   
+    
                      FROM   LES.business,
                             ($devicesql) devices
                      WHERE devices.company = business.business_id   
                      $companyCondition";

    ### link sites to devices because sites that are shared between co
+mpanies will need to be imported
    my $sitesql =    "SELECT distinct(datacenters.dc_id) as MasterID, 
+datacenters.dc_name as Site, datacenters.dc_country as Country,
                             datacenters.dc_timezone as TimeZone, data
+centers.dc_address as Address, '24x7' as SvcWin, decode(nvl(datacente
+rs.business_name, '-1'), '-1', '-1', devices.company) as Company
                      FROM   LES.datacenters,
                             ($devicesql) devices
                      WHERE devices.site = datacenters.dc_id";
    
    my $countrysql = "SELECT country_def.country, country_def.c_region
+ as Region
                      FROM   LES.country_def
                      WHERE (country_def.c_region = 'Asia Pacific')";
    
#    my $mgmtsvrsql = "SELECT distinct(ito_server) as mgmtServerName, 
+'ITO' as mgmtServerFunction, '1' as AutoLinkDiscovery,
#                             '1' as ActiveNode, '1' as AutoInterfaceD
+iscovery, '0' as AllowSecondaryFailure 
#                      FROM   LES.server,
#                             LES.ovo_info
#                      WHERE (ovo_info.system_id = server.system_id)";
    
    my $computersql = "SELECT distinct(concat(concat(hostname,'.'),dom
+ainname)) as fqdn ,'unknown' as PlatformType
                       FROM ($devicesql) devices";
    
    my $dbsql =       "SELECT distinct(concat(concat(hostname,'.'),dom
+ainname)) as fqdn, ios.instance_name as databasetype, ins_instance_na
+me as DBIdentifier, ins_instance_name as DatabaseName, instance_avail
+ability as SLAuptime,
                              'HA' as dbslatype, '24x7' as SvcWinDesc,
+ ios.instance_id as MasterID 
                       FROM   LES.instance_on_server ios, 
                              LES.instance_definition id, 
                              ($devicesql) devices
                       WHERE (category = 'database') 
                         AND (ios.instance_name = id.instance_name)
                         AND (devices.masterid = ios.system_id)
                         AND (ios.ins_instance_name is not null)";
    
    my $dbtypesql = "SELECT distinct databasetype from ($dbsql) dbs";
    
    my $timezone_offset = $ARGV[2];
    if (!($timezone_offset < 0 || $timezone_offset > 0 || $timezone_of
+fset == 0)) { #if $timezone_offset is not a number
        $timezone_offset = 0;
        print "Timezone offset is invalid or not specified. Defaulting
+ export times to GMT\n";
    }

    my $outages = "SELECT concat(concat(hostname,'.'),domainname) as f
+qdn,  TO_CHAR(startdate, 'YYYY-MM-DD hh:mm:ss') as startdt, TO_CHAR(e
+nddate, 'YYYY-MM-DD hh:mm:ss') as enddt, message as SUPpurpose, 'once
+off' as frequency, '2' as headingID, TO_CHAR(startdate, 'HH24') as st
+artdayhour, TO_CHAR(startdate, 'MI') as starthourmin, TO_CHAR(enddate
+, 'HH24') as enddayhour, TO_CHAR(enddate, 'MI') as endhourmin, infobo
+ard.message_id as MasterID
                   FROM   (select message_id, message, timezone, (end_
+date-($timezone_offset/24)+(nvl(to_number(replace(infoboard.timezone,
+'GMT','')),0)/24)) as enddate, (start_date-($timezone_offset/24)+(nvl
+(to_number(replace(infoboard.timezone,'GMT','')),0)/24)) as startdate
+ from LES.infoboard) infoboard,
                          LES.message_on_system,
                          ($devicesql) devices
                   WHERE  devices.masterid = message_on_system.system_
+id
                     AND infoboard.message_id = message_on_system.mess
+age_id
                     AND infoboard.enddate >= (SYSDATE-(1/24)+(nvl(-1+
+to_number(replace(infoboard.timezone,'GMT','')),0)/24))";
    
    open(OUTFILE, ">$file");
    
    print OUTFILE "<$implementation>\n";
    
    #create_from_sql($mgmtsvrsql, "ManagementServer", $d_dbh);
    create_from_sql($tzsql, "TimeZone", $d_dbh);
    #create_from_sql($regionsql, "Region", $d_dbh);
    create_from_sql($countrysql, "Country", $d_dbh);
    #create_from_sql($devicetypesql, "DeviceType", $d_dbh);
    #create_from_sql($ptsql, "PlatformType", $d_dbh);
    #create_from_sql($ossql, "OperatingSystem", $d_dbh);
    #create_from_sql($hwssql, "HardwareSupport", $d_dbh);
    #create_from_sql($svcwinsql, "ServiceWindow", $d_dbh);
    
    if ($implementation ne "TWENTY" || $includeCompaniesAndSites) {
        create_from_sql($companysql, "Company", $d_dbh);
        create_from_sql($sitesql, "Site", $d_dbh);
    }
    
    create_from_sql($devicesql, "Device", $d_dbh);
    create_from_sql($computersql, "Computer", $d_dbh);
    create_from_sql($dbtypesql, "DatabaseType", $d_dbh);
    create_from_sql($dbsql, "Database", $d_dbh);
    
    if ($implementation eq "TWENTY") {
        create_from_sql($outages, "Outages", $d_dbh);        
    }
    

    
    print OUTFILE "</$implementation>\n";
    close(OUTFILE);
    
    print "Completed Successfully.\n";
    
}

return 1;
[download]

• DBI
• PEP
• cron
• PDF::API2
• Spreadsheet::WriteExcel
• Text::CSV
• Apache FOP
• DBIx::Class
• Learning Perl
• Programming Perl
• Perl Cookbook

I highly recommend the various Database Programming nodes listed on gmax' homenode.

You might also benefit from taking a look at some of the features (including documenting databases) offered by the SQL Fairy (additional site).

The Database Stuff on my scratchpad might also contain some useful information or tools, including:

• ERD Diagram Generation
• RDBMS 'Pretty' Schema

Good luck!

hello again thank you for your comments. I have an additional question. I discovered this module to output the result of my sql query, DBIx-XHTML_Table. due to the fact that I want this report to be emailed automatically, a) which module do I use to achieve this? and b) would this be compatible with the XHTML one? c)is there a better way to do this? thanks again!

Oh, I'll go out on a limb here and suggest perhaps

• MIME::Lite
• Net::SMTP

Learning to use Super Search will serve you well, Grasshopper. ;-)

HTH,

planetscape