
jhumkey
Novice
Jan 21, 2015, 8:15 AM
Post #3 of 20
(26573 views)
|
Re: [FishMonger] WHERE has changed to Numeric Comparison
[In reply to]
|
Can't Post
|
|
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.
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)
|