CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: DBI: Re: [FishMonger] WHERE has changed to Numeric Comparison: Edit Log



jhumkey
Novice

Jan 21, 2015, 8:15 AM


Views: 28004
Re: [FishMonger] WHERE has changed to Numeric Comparison

Sorry if the concatenation confused you. I meant we're using DBI and DBD for CSV files. (All from cpan. Or from IBM's approved AIX version. So they may be behind actual CPAN.)

These modules loaded :
DBD-CSV-0_1027_tar.gz
DBI-1.622_901
SQL-Statement-0_1020_tar.gz
Text-CSV_XS-0_22_tar.gz
DBI-1_19_tar.gz
DBI-1.622_901.tar.gz
Text-CSV-0_01_tar.gz
Time-modules-2002_1001_tar.gz

The gist of the program is . . . sweep HoursIn file entry lines. If a matching key EXISTS in MasterFile then UPDATE it, if not, then INSERT it.

I've gutted the much longer original program but, this one runs/fails for an example.

Separate/create the three files, and change the $StartDir to wherever you are running it from.



Code
HoursIn 
6313,20150121095900,23147

MasterFile
0006313,20120524072600,1000
6313,20120120072500,2000

doupdate.pl
#!/usr/bin/perl

use DBI;
use strict;
use Cwd;

my ($StartDir);
my ($XMASTER_query, $XMASTER_st, $XMASTER_db);
my ($StartDir,$ProcessMask);
my ($IndiFile);
my ($HOURS_query, $HOURS_st, $HOURS_db);
my ($SerialNumber, $DateTime, $LifeHours);
my ($cYear, $cMon, $cDay, $cHour, $cMin, $cSec);

$XMASTER_db = DBI->connect("DBI:CSV:f_dir=$StartDir")
or die "Cannot connect: " . $DBI::errstr;

$XMASTER_db->{'csv_tables'}->{'XMASTER'} = {
'eol' => "\n", 'sep_char' => ",", 'quote_char' => undef,
'escape_char' => undef, 'file' => 'MasterFile', # <-the CSV file
'col_names' =>
[ "SerialNumber", "DateTime", "LifeHours" ]
};

$StartDir="/home/mes_cron";
$IndiFile="HoursIn";

$HOURS_db = DBI->connect("DBI:CSV:f_dir=$StartDir")
or die "Cannot connect: " . $DBI::errstr;

$HOURS_db->{'csv_tables'}->{'HOURS'} = {
'eol' => "\n", 'sep_char' => ",", 'quote_char' => undef,
'escape_char' => undef, 'file' => $IndiFile, # <-the CSV file
'col_names' =>
[ "SerialNumber", "DateTime", "LifeHours" ]
};

$HOURS_query = "SELECT SerialNumber,DateTime,LifeHours FROM HOURS";

$HOURS_st = $HOURS_db->prepare($HOURS_query);
$HOURS_st->execute();
$HOURS_st->bind_columns(undef, \$SerialNumber, \$DateTime, \$LifeHours);

while ($HOURS_st->fetch)
{
&insert_or_update( $SerialNumber, $DateTime, $LifeHours );
}
$HOURS_st->finish();

$HOURS_db->disconnect();

$XMASTER_db->disconnect();

exit(0);

#------------------------------------------------------------------

sub insert_or_update {
my ($SerialNumber, $DateTime, $LifeHours ) = @_;

if ( &exists_in_master( $SerialNumber ) )
{
update_master($SerialNumber,$DateTime,$LifeHours);
}
else
{
insert_master($SerialNumber,$DateTime,$LifeHours);
}

}

#------------------------------------------------------------------

sub insert_master {
my ($SerialNumber, $DateTime, $LifeHours ) = @_;
my ($col_str, $val_str, $sql_str);

$col_str = "SerialNumber, DateTime, LifeHours";

$val_str = "\'$SerialNumber\', \'$DateTime\', \'$LifeHours\'";

$sql_str = "INSERT INTO XMASTER ( $col_str ) VALUES ( $val_str )";

$XMASTER_db->do($sql_str);
}

#------------------------------------------------------------------

sub update_master {
my ($SerialNumber, $DateTime, $LifeHours ) = @_;

$XMASTER_db->do("UPDATE XMASTER \
SET DateTime='$DateTime', LifeHours='$LifeHours' \
WHERE ( SerialNumber='$SerialNumber' AND \
LifeHours<'$LifeHours' AND \
DateTime<'$DateTime' ) ");
}

#------------------------------------------------------------------

sub exists_in_master {
my ($SerialNumber, $ActionType ) = @_;
my ($retval);

$XMASTER_query = "SELECT SerialNumber FROM XMASTER \
WHERE SerialNumber='$SerialNumber' ";

$XMASTER_st = $XMASTER_db->prepare($XMASTER_query);
$XMASTER_st->execute();
$XMASTER_st->bind_columns(undef, \$SerialNumber );

if ( $XMASTER_st->fetch )
{
$retval = 1;
}
else
{
$retval = 0;
}

$XMASTER_st->finish();

return($retval);
}

#------------------------------------------------------------------



(This post was edited by jhumkey on Jan 21, 2015, 8:50 AM)


Edit Log:
Post edited by jhumkey (Novice) on Jan 21, 2015, 8:50 AM


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives