#!c:/perl/bin/perl

#
# $Log$
#
#
# Begin commands to execute this file using Perl with bash
# TZ=mdt
# export TZ
# date
# ./select-insert-select-failure.pl
# date
# End commands to execute this file using Perl with bash
#

use strict;
use warnings;
use DBI;
use dbPersist;
use POSIX qw(strftime);
use Time::localtime;
use Date::Manip;
use dice;
use monster;
use hotjobs;
my $database = 'job-search.mdb';        # Access 2000 db
my $DSN = "driver={Microsoft Access Driver (*.mdb)};dbq=$database";
my $dbh = DBI->connect("dbi:ODBC:driver={Microsoft Access Driver (*.mdb)};dbq=$database","Admin","") or die "$DBI::errstr\\n";
$| = 1;

my @sSQLSELECTClauses=(' sJobTitle = ?',' sExperience = ?',' urlOriginal = ?',' sPayRate = ?',' sEmployer = ?',' sEducation = ?',' sJobSiteJobID = ?');
my @sSQLSELECTFieldValues=('SENIOR C# .NET DEVELOPER - Play a key role in building & mentoring a development team:  The Hire Advantage'
     ,'5-10 Years Experience'
     ,'hotjobs.com'
     ,'Unspecified'
     ,'The Hire Advantage'
     ,'Bachelor of Science'
     ,'DEV');
my @sSQLSELECTFieldNames=('sJobTitle','sExperience','urlOriginal','sPayRate','sEmployer','sEducation','sJobSiteJobID');


my @sSQLINSERTNameClauses=(' sJobTitle',' sExperience',' urlOriginal',' fkEmployer',' urlJobEmployerSite',' sPayRate',' urlJobJobSite',' sEmployer',' dtPost',' dtCreate',' sEducation',' sJobSiteJobID');
my @sSQLINSERTValueClauses=(' ?',' ?',' ?',' ?',' ?',' ?',' ?',' ?',' ?',' ?',' ?',' ?');
my @sSQLINSERTFieldValues=('SENIOR C# .NET DEVELOPER - Play a key role in building & mentoring a development team:  The Hire Advantage'
     ,'5-10 Years Experience'
     ,'hotjobs.com'
     ,'2'
     ,'http://hotjobs.yahoo.com/Company-Profiles/T/The-Hire-Advantage_50962'
     ,'Unspecified'
     ,'http://hotjobs.yahoo.com/jobseeker/jobsearch/job_detail.html?job_id=J450630OO&search_url=%2Fjobseeker%2Fjobsearch%2Fsearch_results.html%3Fkeywords_all%3DCSharp%26ulm_input1%3DBoulder%252C%2BCO%26country1%3DUSA%26search_type_form%3Dquick%26updated_since%3Dsixtydays%26basicsearch%3D0%26advancedsearch%3D0%26metro_area%3D1%26search%3DSearch%26kw%3DCSharp&keywords_any=&keywords_all=c%2523&keywords_phrase='
     ,'The Hire Advantage'
     ,'2005-09-17'
     ,'2005-09-19 15:05:19'
     ,'Bachelor of Science'
     ,'DEV');
my @sSQLINSERTFieldNames=('sJobTitle'
     ,'sExperience'
     ,'urlOriginal'
     ,'fkEmployer'
     ,'urlJobEmployerSite'
     ,'sPayRate'
     ,'urlJobJobSite'
     ,'sEmployer'
     ,'dtPost'
     ,'dtCreate'
     ,'sEducation'
     ,'sJobSiteJobID');


my $sTableName='JobPosting';
my $sIndent = '       ';
my $nMaxLength = 255;

      if(@sSQLSELECTClauses){
        $sTableName =~ s/^h//; # I decided to not use hungarian on the table names.
        my $sSelect = "SELECT id FROM $sTableName WHERE ".join(" AND ", @sSQLSELECTClauses)."";
        print sprintf("%-3.3d",__LINE__)."$sIndent r=$sSelect\n";
        my $sthSelect = $dbh->prepare($sSelect);
        unless ($sthSelect){
          die "No sth: ".sprintf("%-3.3d",__LINE__)." statment=$sSelect ".$dbh->errstr;
        }
        for (1..@sSQLSELECTFieldValues) {
#           $sSQLSELECTFieldValues[$_-1] =~ s/\s+/ /g;
           my $len = length $sSQLSELECTFieldValues[$_-1];
           if ($len > $nMaxLength) {
             $sSQLSELECTFieldValues[$_-1] = substr($sSQLSELECTFieldValues[$_-1],0,$nMaxLength);
           }
          print sprintf("%-3.3d",__LINE__)."$sIndent Bind ($_) length=".(length $sSQLSELECTFieldValues[$_-1])." $sSQLSELECTFieldNames[$_-1] = '$sSQLSELECTFieldValues[$_-1]'  ".$sSQLSELECTClauses[$_-1]."\n" ;
          # $sthSelect->bind_param($_, $sSQLSELECTFieldValues[$_-1]);
        }
        unless ($sthSelect->execute(@sSQLSELECTFieldValues)){ # @sSQLSELECTFieldValues
          die sprintf("%-3.3d",__LINE__)." no select: '$sSelect' ". $sthSelect->errstr;
        }
        my $bFound = 0;
        my $row; my $id;
        if ($row=$sthSelect->fetch) {
          $bFound++;
          $id = $row->[0];
          print sprintf("%-3.3d",__LINE__)."$sIndent found: id=$id\n";
          #my $urlJobEmployerSite = $obj->{urlJobEmployerSite};
          #UpdateLastSeen(dbh=>$dbh, lastSeenTables=>$sLastSeenTables, table=>$sTableName, dtCreate=>$dtCreate, id=>$id, urlJobEmployerSite=>$urlJobEmployerSite, fkEmployer=>$obj->{fkEmployer} );
        } else {
          #  Did not find it, so insert it
          my $iInsert = "INSERT INTO $sTableName (".join(",", @sSQLINSERTNameClauses).") VALUES (".join(",", @sSQLINSERTValueClauses).")";
          print sprintf("%-3.3d",__LINE__)."$sIndent r=$iInsert\n";
          my $sthInsert = $dbh->prepare($iInsert);
          unless ($sthInsert){
            die "No sth: ".sprintf("%-3.3d",__LINE__).$dbh->errstr;
          }
          for (1..@sSQLINSERTFieldValues) {
#           $sSQLINSERTFieldValues[$_-1] =~ s/\s+/ /g;
           my $len = length( $sSQLINSERTFieldValues[$_-1]);
           if ($len > $nMaxLength) {
             $sSQLINSERTFieldValues[$_-1] = substr($sSQLINSERTFieldValues[$_-1],0,$nMaxLength);
           }
            print sprintf("%-3.3d",__LINE__)."$sIndent Bind ($_) length=".(length $sSQLINSERTFieldValues[$_-1])." $sSQLINSERTFieldNames[$_-1] = '$sSQLINSERTFieldValues[$_-1]'  \n" ;
            $sthInsert->bind_param($_, $sSQLINSERTFieldValues[$_-1]);
          }
          $dbh->begin_work;
          unless($sthInsert->execute()){ # @sSQLINSERTFieldValues
            print __LINE__. "no insert: '$iInsert' ". $sthInsert->errstr;
          }
        # Accommodate brain damaged MSAccess
          my $sSelectMax = "SELECT MAX(id) FROM $sTableName";
          print sprintf("%-3.3d",__LINE__)."$sIndent r=$sSelectMax\n";
          my $sth = $dbh->prepare($sSelectMax);
          unless ($sth){
            die "No sth: ".sprintf("%-3.3d",__LINE__).$dbh->errstr;
          }
          unless ($sth->execute){
            die "no select: '$sSelectMax' ". $sth->errstr;
          }
          if ($row=$sth->fetch) {
            $bFound++;
            $id = $row->[0];
          }
          $dbh->commit;

          # Check our work
          unless ($sthSelect->execute(@sSQLSELECTFieldValues)){ # @sSQLSELECTFieldValues
            die sprintf("%-3.3d",__LINE__)." no select: '$sSelect' ". $sthSelect->errstr;
          }
          $bFound = 0; my $id2=0;
          if ($row=$sthSelect->fetch) {
            $bFound++;
            $id2 = $row->[0];
            print __LINE__. " Found it: \$id = $id \$id2=$id2\n";
          } else {
            print __LINE__. " Where did the record go that we just inserted?\n";
          }
          # End check our work
        }
      }


