Perl, MS SQL, DBI and catching errors.

[jez]

Hi all,

I am trying to do something fairly simple with a DB insert.
I would like to run the query. If there is a primary key violation, then the data has gone into the DB already and i want to move on.

The purpose of the script is to read a folder and enter some details for each file into a database.

Now the downside, its on windows, using MS SQL.


I don't seem to be able to catch errors. When the script runs, it dies on the PK violation and no more sql can be run.

Here is my code, any suggestions?

my $statusCode = 1;
            $sql = qq{ INSERT INTO $mainTable (Media_Identifier, Status) values ('$mediaID', $statusCode);};

 my $con = $sqllib::dbh->prepare( $sql );
    $result = eval {
        $con->execute();
        $sqllib::dbh->commit();
    };
        
    
    if($@){
        warn "Database error: $DBI::errstr\n";
        $sqllib::dbh->rollback()
        }
    $con->finish();
    $sqllib::dbh->disconnect();

When it is run with 3 files in the folder to be processed, i get this error message

DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK_VO_script_delivery'. Cannot insert duplicate key in object 'dbo.VO_script_delivery'. (SQL-23000) [state was 23000 now 01000]
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (SQL-01000) at VO_delivery_xml_config.pm line 85.
Database error: [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK_VO_script_delivery'. Cannot insert duplicate key in object 'dbo.VO_script_delivery'. (SQL-23000) [state was 23000 now 01000]
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (SQL-01000)
DBD::ODBC::db prepare failed: Cannot allocate statement when disconnected from the database at VO_delivery_xml_config.pm line 83.
DBD::ODBC::db prepare failed: Cannot allocate statement when disconnected from the database at VO_delivery_xml_config.pm line 83.

Any tips greatly appreciated.

 

[jez]

Possibly fixed:

I am doing another query to see if the primary key is there and if so then skipping the insert query...

 $sqlCheck = qq{ SELECT Media_Identifier FROM $mainTable WHERE Media_Identifier = '$mediaID'; };
            $sql = qq{ INSERT INTO $mainTable (Media_Identifier, Status) values ('$mediaID', $statusCode);};

if($sqlCheck){
            my $sth = $sqllib::dbh->prepare( $sqlCheck );
            $sth->execute();
            my $Media_Identifier;
            $sth->bind_columns(\$Media_Identifier);
            while( $sth->fetch() ) {
# record exists, so drop out of the sub routine.
                return;
            }
    }

 

[PaulTEG]

If primary key present, then update, not insert?

Paul
------------------------------------
Spend an hour a week on CPAN, helps cure all known programming ailments ;-)