shree has asked for the wisdom of the Perl Monks concerning the following question:

Hi All, In my Perl program I am using Rose::DB for the interaction with Oracle database for my project.
Here in database table I have set of records with the Boolean field which can be ‘T’ or ‘F’.


My Perl program will take the set of records from the table and do some actions on that and after that it will set the flag field as ‘T’, so the same records should not get processed again. After that I am executing the commit.

But the issue is that here even the commit returns the return value 1, its not reflecting on Database Table
and the Perl program will process the same already processed records again and again.

Please anyone help me to solve this issue. 
my $db = RDBO->init_db;

select records from queue where the flag = ‘F’;

process ($rec);

update queue set flag = ‘T’ where records =$ rec;

$db->commit;

[download]
Thanks Shreedhar Naik

Replies are listed 'Best First'.
Re: db commit is not happening properly with Rose::DB
by siracusa (Friar) on May 05, 2011 at 17:22 UTC
    Where do you begin the transaction you think you're committing? Can you post some actual executable code? It's hard to tell what happening with just the pseudocode.
[reply]
Re: db commit is not happening properly with Rose::DB
by davido (Cardinal) on May 05, 2011 at 17:42 UTC

    One reason that we request a runnable snippet that replicates the problem is so that we can see what on earth you're talking about. Another reason is so we can see where the error is (describing what you're doing doesn't usually lead to specific answers). And yet another reason is that many times as you boil your big code down to a small snippet replicating the problem, you yourself will discover what it is that you're doing wrong.

    Without any real code to look at, one thing I can advise is to be sure you're checking for success upon opening the DB, fetching from it, and so on. Sometimes the place where the obvious trouble is happening could have been predicted if some of the less obvious places were error-checked leading up to that point.


    Dave

[reply]
Re: db commit is not happening properly with Rose::DB
by shree (Acolyte) on May 11, 2011 at 07:17 UTC
    Hi below is the code which I have with me. 
    use strict;
use warnings;
use RDBO::QUEUE;

my $db = RDBO::RDBO->init_db;

my $going = 1;
    
    while($going) {
     
        my $queued_ids = Rose::DB::Object::Manager->get_objects_from_s
+ql(
                object_class => 'RDBO::QUEUE',
                sql => 'SELECT id FROM queue WHERE flag=\'F\ ORDER BY 
+id ASC where ROWNUM< 50',
                db => $db
        );

        my @ids2process;
        foreach my $num (@{$queued_ids}) {
          push(@ids2process,  $num->{'id'});
        }

        if(!scalar(@ids2process)) {
                sleep(5);
        }

        if(scalar(@ids2process) > 0 ) {
         $db->begin_work;
         my $count=0;
        
         foreach my $ids (@ids2process){
                $count++;
        my $ret = HandleIds($id);
        next if($ret == -1);

           eval{
                
                my $q_rec = RDBO::QUEUE->new(id => $id, db => $db);
                $q_rec->load;
                $q_rec->flag('T');
                $q_rec->save;
        
           };
           if($@) { print "Unable to update record for $id";}
           else { print "Updated record for $id";}

       
           if(($count % 25  == 0) || ($count == scalar(@ids2process)))
                 {
                    $db->commit;
                    print "COMMIT at $count IDs";
                 }

         }
       }else {
                sleep(5);
        }
    }

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom