Page 3 of 3 First 123
  • Jump to page:
    #31
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    18
    Rep Power
    0
    I tried your HoA approach and it works great. I haven't figure out how to proceed to elegantly construct the SQL Insert Into statement, nor the $sth-> execute statement, but I'm planning on buying a good perl book so I can hopefully tackle it. This language is tough, practically nothing about how it works is intuitive for me.

    Meanwhile, I'm experimenting with your suggestion to use an xml parser. This script works when there is just 1 signal, but I get Not A Hash Reference error when there is more than one signal.

    PHP Code:
    #!/usr/bin/perl 
    use strict;
    use 
    warnings;
    use 
    DBI;
    use 
    5.10.1;
    use 
    XML::Simple;
    use 
    Data::Dumper;

    my $dbFile="c:/temp/db1.mdb";
    my $dbh DBI->connect('dbi:ODBC:driver=microsoft access driver (*.mdb);dbq='.$dbFile);

    my $xml = new XML::Simple;
    my $data $xml->XMLin("c:/temp/5signals.xml");
    #print Dumper($data);

    my $strSQL 'INSERT INTO Table1 ('
        
    'SSID,'
        
    'NOMENCLATURE'
        
    ') SELECT ?,?';

    my $sth $dbh->prepare($strSQL);
    $sth->execute(
        
    $data->{SIGNAL_LIST}->{SIGNAL}->{SSID},
        
    $data->{SIGNAL_LIST}->{SIGNAL}->{NOMENCLATURE}
        ) || die 
    "SQL problem"
    This is the XML file with 5 signals. http://garywachs.com/public/5signals.xml
  2. #32
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2012
    Posts
    836
    Rep Power
    496
    Originally Posted by gary84
    This language is tough, practically nothing about how it works is intuitive for me
    No, it is not tough, it is in fact rather easy, and it is far more intuitive than most other programmming languages that I have seen.
  4. #33
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    18
    Rep Power
    0
    I figured out how to use string concatenation to construct the SQL Insert Into statement. This script works, but it throws an error when,
    1: a zero string here:
    Code:
          <NOMENCLATURE></NOMENCLATURE>
    2: the total absense of the following from any one SIGNAL_LIST:
    Code:
          <RANGE>
            <RANGE_MIN>1</RANGE_MIN>
            <RANGE_MAX>10</RANGE_MAX>
            <UNIT>feet</UNIT>
          </RANGE>
    The error is, "Cannot bind a plain reference at code.pl line 23."
    I don't have the option of asking the maintainer of the XML to change anything.
    Any suggestions how to make my script treat non-existent fields and missing fields as empty strings and just roll with it.
    Any other suggestions on grammer and style are appreciated.
    This script does not run as fast as I hoped it would, on large 30,000+ signals lists.

    PHP Code:
    #!/usr/bin/perl 
    use strict;
    use 
    warnings;
    use 
    DBI;
    use 
    XML::Simple;

    xml_to_mdb();

    sub xml_to_mdb {
        
    my $dbFile="c:/temp/db.mdb";
        
    my $dbh DBI->connect('dbi:ODBC:driver=microsoft access driver (*.mdb);dbq='.$dbFile);
        
    my $xmldata XMLin('c:/temp/signals.xml');
        
    my @fields = ('SSID''CALC_SSID''EQUIPMENT_TYPE_SIGNAL_NAME',
            
    'COMPARTMENT_ID''HSC_ID''REQUIREMENT_FUNCTION_TYPE''NOMENCLATURE',
            
    'IS_ALARM''RANGE_MIN''RANGE_MAX''UNIT',
            
    'PRIMARY_ENUMERATION_TYPE_ENUM''SECONDARY_ENUMERATION_TYPE_ENUM');
        
    my $strSQL 'INSERT INTO Table1 (';
        for 
    my $field (@fields) {$strSQL .= "$field,"};
        
    $strSQL substr($strSQL,0,length($strSQL)-1).") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
        
    my $sth $dbh->prepare($strSQL);
        
        foreach 
    my $signal (@{$xmldata->{SIGNAL_LIST}->{SIGNAL}}) { 
            
    $sth->execute(
                
    $signal->{SSID},
                
    $signal->{CALC_SSID},
                
    $signal->{EQUIPMENT_TYPE_SIGNAL_NAME},
                
    $signal->{COMPARTMENT_ID},
                
    $signal->{HSC_ID},
                
    $signal->{REQUIREMENT_FUNCTION_TYPE},
                
    $signal->{NOMENCLATURE},
                
    $signal->{IS_ALARM},
                
    $signal->{RANGE}->{RANGE_MIN},
                
    $signal->{RANGE}->{RANGE_MAX},
                
    $signal->{RANGE}->{UNIT},
                
    $signal->{PRIMARY_ENUMERATION_TYPE_ENUM},
                
    $signal->{SECONDARY_ENUMERATION_TYPE_ENUM}
                ) || die 
    "SQL problem";
        }



    I bought Programming Perl 4th edition (O'Reilly) in case it has a reference someone wants to point me to.
  6. #34
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    18
    Rep Power
    0
    Problem solved. For both cases, zero-string data inside <foo></foo>, and a missing set of <foo></foo>, the problem goes away when I use SuppressEmpty=>''.
    Now, some benchmarking. The execute says,
    $sth->execute_array({},\@value1,\@value2,\@value3,\@value4,\@value5,\@value6,\@value7,\@value8,\@value9,\ @value10,\@value11,\@value12,\@value13,) || die "SQL problem";

    The heart of my home-made xml parser is a function a wrote called EVBT (ExtractValueBetweenTerms).

    I've now written this xml-to-mdb parser in 3 ways:
    1. in vba using EVBT. took 1 minute 15 seconds.
    2. in perl using EVBT. took 1 minute 57 seconds.
    3. in perl using XMLin. took 2 minutes 24 seconds.
    I'm surprised to see my homemade parser in vba is by far the fastest.
Page 3 of 3 First 123
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo