use strict;
use Win32::ODBC;
use Win32::OLE;

#i call this to open the connection
sub OpenMTPDB
{
        my $Pwd = 'W*+h0rW/oU;';

        if ($Connection_Type eq 'P'){
                # Server=pgssqlea01; Database=MTPDB
                unless($db = new Win32::ODBC("dsn=MTPDB_P; UID=EAutomation; PWD=$Pwd")){
                        print "DB Connection Error: " . Win32::ODBC::Error() . "\n";
                        exit;
                }
        }
        elsif ($Connection_Type eq 'D'){
                # Server=pgssqlea01; Database=MTPDB
                unless($db = new Win32::ODBC("dsn=MTPDB_D; UID=EAutomation; PWD=$Pwd")){
                        print "DB Connection Error: " . Win32::ODBC::Error() . "\n";
                        exit;
                }
        }
        return $db;
}

# my main part of the code
#Get the FK_lot id
$sql = "SELECT TOP 1 IDX FROM ECAST_LOT ORDER BY IDX DESC ";
                    ## Only get the id once for one summary
                    if ($id eq ""){
                        print "GOTI IN\n";
                        if ($db->Sql($sql)){
                            print "Error executing the sql: $sql\n";
                            $isSqlFailed = 1;
                            last;
                        }
                        else{
                            my %temp;
                            while($db->FetchRow()){
                                %temp = $db->DataHash();
                                $id   = $temp{IDX};
                            }
                            print "ID: $id\n\n";
                        } #end if db->Sql
                    }
                    $id++;
                    
                    #Insert data into the Ecast_Lot table
                    $sql = <<SQL_USAGE;
                    INSERT INTO ECAST_LOT(LOT,OPERATION,SUMMARY,DEVREVSTEP,DATETIME,TIU,TEST_PROGRAM,[TRIGGER],TOTAL_TESTED,TESTER_ID,HANDLER_ID,COLOR)
                    values('$lot','$oper','$sum','$dev','$date','$tiu','$tp','$trigger','$ttested','$tester','$handler','$color')
SQL_USAGE
                    print "SQL: $sql\n";
                    if ($db->Sql($sql)){
                        print "Error executing the sql: $sql\n";
                        $isSqlFailed = 1;
                        last;
                    }