Re^6: fetchrow_array loop in perl 5.10

[Anonymous Monk]

DME::DB::db=HASH(0x80861070) trace level set to 0x0/1 (DBI @ 0x0/0) in DBI 1.631-ithread (pid 11755)
<> FETCH('private_dbcharset')= 'UTF8' ('private_dbcharset' from cache) at DB.pm line 298
<- prepare("DELETE FROM nl_asset WHERE asset_block_id = ?", undef)= DME::DB::st=HASH(0x8084b8c8) at DB.pm line 245
<- FETCH('Database')= DME::DB::db=HASH(0x80861148) at DB.pm line 1010
<> FETCH('private_dbcharset')= 'UTF8' ('private_dbcharset' from cache) at DB.pm line 1014
<- HandleSetErr= 0 (err=undef, errstr=undef, state=undef, undef)
<- set_err(undef, undef)= undef at DB.pm line 104
<- HandleSetErr= 1 (err='', errstr='IBMCLI DriverDB2/LINUXZ64 SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000
', state='02000', undef) <- execute(13453)= '0E0' at DB.pm line 1019
<- finish= 1 at nl_asset_insert_test_err.pl line 74
<> FETCH('private_dbcharset')= 'UTF8' ('private_dbcharset' from cache) at DB.pm line 298
<- prepare("SELECT asset_id, field_name, mime_type, text_asset, clob_asset, blob_asset, status, add_user FROM nl_asset WHERE asset_block_id = ? and status = ? order by field_name desc", undef)= DME::DB::st=HASH(0x8020c3d8) at DB.pm line 245
<> FETCH('private_dbcharset')= 'UTF8' ('private_dbcharset' from cache) at DB.pm line 298
<- prepare("insert into nl_asset (asset_id, asset_block_id, field_name, text_asset, clob_asset, blob_asset, mime_type, add_user, add_dttm, status) values (next value for NL_ASSET_SEQ, ?, ?, ?, ?, ?, ?, ?, current timestamp, ?)", undef)= DME::DB::st=HASH(0x8084ba48) at DB.pm line 245
<- FETCH('Database')= DME::DB::db=HASH(0x80861148) at DB.pm line 1010
<> FETCH('private_dbcharset')= 'UTF8' ('private_dbcharset' from cache) at DB.pm line 1014
<- execute(12959, "A")= -1 at DB.pm line 1019
<- FETCH('Database')= DME::DB::db=HASH(0x80861148) at DB.pm line 957
<- fetchrow_array= ( '48044' 'URL_TEXT_1' undef '' undef undef 'A' '1226480' ) 8 items row1 at DB.pm line 962
<> FETCH('private_dbdriver')= 'DB2' ('private_dbdriver' from cache) at DB.pm line 964
<- FETCH('Database')= DME::DB::db=HASH(0x80861148) at DB.pm line 1010 ############## retrieving 1 out of 4 records
<> FETCH('private_dbcharset')= 'UTF8' ('private_dbcharset' from cache) at DB.pm line 1014
<- execute(13453, "URL_TEXT_1", ...)= 1 at DB.pm line 1019 ############## execute insert 1 out of 4 records
<- errstr= undef at nl_asset_insert_test_err.pl line 95
<- FETCH('Database')= DME::DB::db=HASH(0x80861148) at DB.pm line 957
<> FETCH('private_dbdriver')= 'DB2' ('private_dbdriver' from cache) at DB.pm line 964
<- FETCH('Database')= DME::DB::db=HASH(0x80861148) at DB.pm line 1010 ############## retrieving 2 out of 4 records
<> FETCH('private_dbcharset')= 'UTF8' ('private_dbcharset' from cache) at DB.pm line 1014
<- execute(13453, "URL_LINK_1", ...)= 1 at DB.pm line 1019 ############## execute insert 2 out of 4 records
<- errstr= undef at nl_asset_insert_test_err.pl line 95
<- FETCH('Database')= DME::DB::db=HASH(0x80861148) at DB.pm line 957 ############# retrieving 3 out of 4 records --> ERROR occurred -- $sth->SUPER::fetchrow_array();.
<- HandleSetErr= 0 (err='', errstr='', state='00000', undef)
info: '' '' (err#0)
<- DESTROY(DME::DB::st=HASH(0x8084bc58))= undef at nl_asset_insert_test_err.pl line 43
info: '' '' (err#0)
<- DESTROY(DME::DB::st=HASH(0x8084bc10))= undef at nl_asset_insert_test_err.pl line 43
info: '' '' (err#0)
<- DESTROY(DME::DB::st=HASH(0x80861268))= undef at nl_asset_insert_test_err.pl line 43 info: '' '' (err#0)
<> FETCH('private_connected')= 1 ('private_connected' from cache) at DB.pm line 214
info: '' '' (err#0)
<- FETCH('AutoCommit')= 1 at DB.pm line 215
!! The info '''' was CLEARED by call to disconnect method
<- disconnect= 1 at DB.pm line 217
<- STORE('private_connected', 0)= 1 at DB.pm line 218
! <- DESTROY(DME::DB::db=HASH(0x80861148))= undef during global destruction

[mje]

You look to be deleting, inserting and fetching from the same table at the same time and this does not seem to coincide with the code you first showed us. Can we see the actual code that produced that trace.

[Anonymous Monk]

the delete is from the start of the program, which is I think don't matter as even if I comment out the delete part, the loop would still not finish all rows returned.

[Anonymous Monk]

  my $sth; 
    my $row;        
    my $newAssetBlockId = 13453;
                  
    my $assetDelSql = "DELETE FROM  nl_asset WHERE  asset_block_id = ?
+";            
    my $assetDelStmt = $dbh->prepare($assetDelSql);
    $assetDelStmt->execute($newAssetBlockId);
    $assetDelStmt->finish;
                      
    my $assetInsSql = "SELECT  asset_id, field_name, mime_type, text_a
+sset, clob_asset, blob_asset, status, add_user
                       FROM  nl_asset
                      WHERE  asset_block_id = ? and status = ? order b
+y field_name desc";
                      
    my $assetInsStmt = $dbh->prepare($assetInsSql);

 my $assetInsertSql = "insert into nl_asset (asset_id, asset_block_id,
+ field_name, text_asset, clob_asset, blob_asset, mime_type, add_user,
+ add_dttm, status) values (next value for NL_ASSET_SEQ, ?, ?, ?, ?, ?
+, ?, ?, current timestamp, ?)";
 my $assetInsertStmt = $dbh->prepare($assetInsertSql);

    $assetInsStmt->execute(12959, 'A');
    while (my ($assetId, $fieldName, $mimeType, $textAsset, $clobAsset
+, $blobAsset, $status, $addUser) = $assetInsStmt->fetchrow_array()) {
        dme::log_msg("assetId with $fieldName");
        $assetInsertStmt->execute($newAssetBlockId, $fieldName, $textA
+sset, $clobAsset, $blobAsset, $mimeType, $addUser, $status); 
        dme::log_msg($dbh->errstr) if ($dbh->errstr);    
        dme::log_msg("done with $assetId");    
    }

[mje]

So show us the real code that produced that trace.