Newsletter supplementary material

-The Perl code explination
 -The code
LVL 11
turn123Asked:
Who is Participating?
 
turn123Author Commented:
Question moved to Productivity Applications and closed.

turn123,
Page Editor -- Productivity Applications
0
 
turn123Author Commented:
Not much to explain this time.  Create a new text document in the same directory as the separate .pl file give in http://www.experts-exchange.com/Q_21188390.html#14117768 -> name it something.pl -> copy and paste the code into it.
0
 
turn123Author Commented:
use strict;
use DBI;
use XML::Simple;

my $filename = "shipping_stuff.mdb";
my $dsn = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=$filename";
my $dbh = DBI->connect("dbi:ODBC:$dsn", "", "")
     or die "Can't open source DB: $!\n";

my $data = XMLin("output.xml",KeyAttr => ["DataExtName"], ForceArray => [ "DataExtRet","InvoiceLineRet","InvoiceRet" ]);
my $control = 0;
while ($data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{RefNumber} =~ /(.+)/) {
      my $sqlstatement = "INSERT into inv VALUES ('";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{RefNumber}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{TimeCreated}','";
      if ($data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{Addr3} =~ /.+/) {
            $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{Addr1}','";
            $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{Addr2}','";
            $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{Addr3}','";
      } elsif ($data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{Addr2} =~ /.+/) {
            $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{Addr1}','";
            $sqlstatement .= "','";
            $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{Addr2}','";
      } else {
            $sqlstatement .= "','";
            $sqlstatement .= "','";
            $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{Addr1}','";
      }
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{Addr4}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{City}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{State}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{PostalCode}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{Country}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{PONumber}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{TermsRef}->{FullName}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{ShipMethodRef}->{FullName}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{SalesRepRef}->{FullName}','";
      if ($data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{Addr3} =~ /.+/) {
            $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{ShipAddress}->{Addr1}','";
            $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{ShipAddress}->{Addr2}','";
            $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{ShipAddress}->{Addr3}','";
      } elsif ($data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{BillAddress}->{Addr2} =~ /.+/) {
            $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{ShipAddress}->{Addr1}','";
            $sqlstatement .= "','";
            $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{ShipAddress}->{Addr2}','";
      } else {
            $sqlstatement .= "','";
            $sqlstatement .= "','";
            $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{ShipAddress}->{Addr1}','";
      }
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{ShipAddress}->{Addr4}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{ShipAddress}->{City}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{ShipAddress}->{State}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{ShipAddress}->{PostalCode}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{ShipAddress}->{Country}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{EditSequence}','";
      $sqlstatement .= "$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{TxnID}')";
      open SQL, ">> sql.txt";
      print SQL $sqlstatement . "\n";
      close SQL;
      my $statementhandle = $dbh->prepare("DELETE from inv where ID = '$data->{QBXMLMsgsRs}->{InvoiceQueryRs}->{InvoiceRet}->[$control]->{RefNumber}'");
      $statementhandle->execute();
      my $statementhandle = $dbh->prepare($sqlstatement) || die "Cannot prepare statement";
      $statementhandle->execute() || die "Could not execute SQL statement, maybe invalid?";
      ++$control;
}
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.