[PERL/SQL] Update/Delete query

[HekkeySE]

Ik heb nu deze code (hij werkt ook nog niet goed omdat hij maar 1x iets toevoegd. Maar goed. Ik wil een controle hebben dat hij checkt of de value niet meer bestaat en als hij nog wel bestaat kijken of het veranderd is.

Hoe ga ik dat aanpakken??

code:

    # Check if router and interface exists
    $query = sprintf (
        "SELECT t1.name, t1.uid, t1.contact, t1.descr, t2.name, t3.LIN_ID, t3.DESCR, t3.IP_ADDRESS, t3.AANSLUIT_POORT, t3.UID, t3.CONTACT
        FROM interface AS t1, router AS t2, xmldump AS t3
        WHERE t1.name = t3.LIN_ID
        AND t1.uid = t3.UID
        AND t1.contact = t3.CONTACT
        AND t1.descr = t3.DESCR
        AND t2.name = t3.IP_ADDRESS" 
    );
    $sth = $dbh->prepare ($query);
    $sth->execute();

    if ( $rv = $sth->fetchrow_hashref() ) {
        print "Router and interface exists\n";
        
    } else {
        my ($rid);

        # Router doesn't exists, add router
        $query = sprintf (
                "INSERT INTO router ( name ) 
                VALUES ( %s )",
                $dbh->quote ( $row->find('IP_ADDRESS')->string_value() )
        );
        $dbh->do ($query);
                
        # Get value of auto_increment column
        $rid = $dbh->{'mysql_insertid'};
        $iid = $rid, $descr, $int, $speed;
        
        print "Router with ID: ". $iid. " has been added\n";

[RvdH]

Welke value heb je het over?

[HekkeySE]

RickJansen schreef op 25 maart 2004 @ 08:46:
Welke value heb je het over?

De values komen uit een xml document.
Misschien handiger om het complete script even neer te zetten

code:

foreach my $row ($xp->findnodes('ROWSET/ROW')) {

    # Declare variables with XML
    my $routerip = $row->find('IP_ADDRESS')->string_value();
    my $int = $row->find('LIN_ID')->string_value();
    my $descr = $row->find('DESCR')->string_value();
    my $poort = $row->find('AANSLUIT_POORT')->string_value();
    my $uid = $row->find('UID')->string_value();
    my $contact = $row->find('CONTACT')->string_value();

    # Import XML file into XMLDUMP
    $query = sprintf (
        "INSERT INTO xmldump (LIN_ID, DESCR, IP_ADDRESS, AANSLUIT_POORT, UID, CONTACT) 
        VALUES(\"$int\", \"$descr\", \"$routerip\", \"$poort\", \"$uid\", \"$contact\")"
    );
    $dbh->do ($query);

    # Check if router and interface exists
    $query = sprintf (
        "SELECT t1.*, t2.*, t3.*
        FROM interface AS t1, router AS t2, xmldump AS t3
        WHERE t2.name = t3.IP_ADDRESS
        AND t1.descr = t3.DESCR" 
    );
    $sth = $dbh->prepare ($query);
    $sth->execute();

    if ( $rv = $sth->fetchrow_hashref() ) {
        print "Router and interface exists\n";
        
    } else {
        my ($rid);

        # Router doesn't exists, add router
        $query = sprintf (
                "INSERT INTO router ( name ) 
                VALUES ( %s )",
                $dbh->quote ( $row->find('IP_ADDRESS')->string_value() )
        );
        $dbh->do ($query);
                
        # Get value of auto_increment column
        $rid = $dbh->{'mysql_insertid'};
        $iid = $rid, $descr, $int, $speed;
        
        print "Router with ID: ". $iid. " has been added\n";

        # Insert interface into table
        $qryInsertTable = sprintf (
            "INSERT INTO interface (name, rid, speed, uid, contact, descr) 
            VALUES(\"$int\", \"$rid\", \"$speed\", \"$uid\", \"$contact\", \"$descr\")"
        );
        $dbh->do ($qryInsertTable);
        print "Interface: ". $int. " had been added\n";

        # Create MIB table
        foreach $mib (keys(%mibs_of_interest_32))
        {
            $qryCreateTable = sprintf (
                "CREATE TABLE ${mib}"."_${rid} (id INT NOT NULL, dtime DATETIME NOT NULL, counter BIGINT NOT NULL, KEY $mib"."_${rid}". "_idx (dtime))"
            );
            $dbh->do ($qryCreateTable);
            $qrySelectInterface = sprintf ("SELECT id FROM interface WHERE rid = \"$rid\"");
            @intid = $dbh->selectrow_array($qrySelectInterface);

            print "Creating table ". $mib. "_". $rid. "\n";
            print CFG "$routerip\t";
            print CFG "". $mibs_of_interest_32{$mib}. "". $poort. "\t";
            print CFG "$bits\t";
            print CFG "$community\t";
            print CFG "${mib}_${rid}\t";
            print CFG "@intid\t";
            print CFG "$descr\t";
            print CFG "\n";
        }
    }
    # Finish statement handler
    $sth->finish();
}