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

OK. this loop is mystifying me. I have placed prints (actually a sub I have to kill and send the errors to the browser) all throughout the script. The variables all seem to be defined correctly, it does everything its supposed to do - i.e. all the entries get made in the database, i can even cut it off at the second to last itteration and get the output up to that point. something goes haywire on the last time through and this thing just hangs. it sits there until i kill it. all the entires are in the database correctly and everything is fine from the flow of what should happen, it just never returns any results. it's running under use strict; and -w and, even if you think the code is bad :), it had been working.

I'm stumped. The very last line of the code down there is the sub which takes the message and sends a page with that as the header. It never gets there. I have done everything i can think of to debug this - I have been working on it all day. any ideas at all are appreciated. feel free to -- away because I know how lame this is. 

    while (my @people = $sth->fetchrow_array()) {
        my $person = join ",", @people;
        push @certified_people, $person;
    }

    $sth->finish;
    
    $sth = $dbh->prepare('SELECT TEMP_CLICK_ID.NEXTVAL FROM DUAL')
    or dienice("199 Couldn't prepare statement: " . $dbh->errstr);

    $sth->execute()
    or dienice("202 Couldn't execute statement: " . $sth->errstr);

    while ( my @results = $sth->fetchrow_array()) {$click_id = $result
+s[0]; chomp $click_id;}

    $sth->finish;

    print qq!$javascript_to_select_names_screen_two!;
    
    foreach my $certified_person (@certified_people) {
        my ($fname,$lname,$sc_id) = split ",", $certified_person;
        
        ## Prepare the SQL to get the conflicts
        # What we do is test to see if either the start or end of any 
+appointment falls between the start
        # and end of the proposed appointment.
        my $CONFLICT_STATEMENT = q!SELECT SC_BASE_APPT.CALL_ID, SC_BAS
+E_APPT.STATUS
                                    FROM SC_BASE_APPT , SC_BASE_INFO
                                    WHERE ( (SC_BASE_INFO.TRANS_ID = S
+C_BASE_APPT.TRANS_ID) )
                                    AND SC_BASE_APPT.SC_ID = ? AND
                                    ((to_number(SC_BASE_INFO.AP_START)
+ <= ? AND to_number(SC_BASE_INFO.AP_END) >= ?)
                                    OR
                                    (to_number(SC_BASE_INFO.AP_START) 
+<= ? AND to_number(SC_BASE_INFO.AP_END) >= ?))
                                !;

        my $sth = $dbh->prepare($CONFLICT_STATEMENT)
        or dienice("230 Couldn't prepare statement: $CONFLICT_STATEMEN
+T" . $dbh->errstr);
        
        # now concatenate the values into start and end times
        my $start_time   = sprintf"%02d%02d%02d%02d%02d", $SMONTH,$SDA
+Y,$SYEAR,$SHOUR,$SMINUTE;
        my $start_day    = sprintf"%02d%02d%02d",         $SMONTH,$SDA
+Y,$SYEAR;

        my $end_time     = sprintf"%02d%02d%02d%02d%02d", $EMONTH,$EDA
+Y,$EYEAR,$EHOUR,$EMINUTE;
        my $end_day      = sprintf"%02d%02d%02d",         $EMONTH,$EDA
+Y,$EYEAR;

        $sth->execute($sc_id,$start_time,$start_time,$end_time,$end_ti
+me)
        or dienice("241 Couldn't execute statement: " . $sth->errstr);
        
        if ( $sth->fetchrow_array() ) { next; }

        $sth->finish;
        
        # if we get this far, put in temp records
        my ($trans_id, $call_id);                                     
+#, @returns);                
        my $appt_classes_ref    = \%appt_classes;
        
        #@returns = makeTempRec($click_id, $sc_id, $start_time, $end_t
+ime, $appt_classes_ref);
        
        #($trans_id, $call_id) = @returns;
        
        {
            my ($status, @cols, @vals);

            # connect to database
            my $dbh = DBI->connect( $CONNECT, $USERNAME, $PASSWORD )
            or dienice("336 Cant connect to Oracle database: $DBI::err
+str\n");

            my $getTransId = $dbh->prepare('SELECT MAKE_APPT.NEXTVAL F
+ROM DUAL')
            or dienice("251 Couldn't prepare statement: " . $dbh->errs
+tr);

            $getTransId->execute()
            or dienice("254 Couldn't execute statement: " . $getTransI
+d->errstr);

            while ( my @results = $getTransId->fetchrow_array()) { $tr
+ans_id = $results[0]; }

            $getTransId->finish;

            my $getCallId = $dbh->prepare('SELECT CALL_ID.NEXTVAL FROM
+ DUAL')
            or dienice("341 Couldn't prepare statement: " . $dbh->errs
+tr);

            $getCallId->execute()
            or dienice("344 Couldn't execute statement: " . $getCallId
+->errstr);

            while ( my @results = $getCallId->fetchrow_array()) { $cal
+l_id = $results[0]; }

            $getCallId->finish;

            $status = "SCHEDULING";

            # you need to insert temp records in the different appt ta
+bles
            my $TRANS_ID_TEMP_RECORD = qq!
                INSERT INTO SC_TRANS_ID ( TRANS_ID, CLICK_ID ) VALUES 
+( $trans_id, $click_id )
            !;

            my $BASE_INFO_TEMP_RECORD = qq!
                INSERT INTO SC_BASE_INFO
                ( TRANS_ID, CONTACT, REP_EMAIL, ADD_ST, ADD_CITY, ADD_
+STATE, ADD_ZIP, ADD_FLOOR, CONT_PHN,
                AP_START, AP_END, COMPANY, CON_EMAIL )
                VALUES ( $trans_id, 'contact name', 'email here', 'War
+renville Rd.', 'Naperville', 'IL', 999999999, 'floor', 8003069329,
                '$start_time', '$end_time', 'placeholder', 'jsander\@q
+uest.com' )
            !;

            my $BASE_APPT_TEMP_RECORD = qq!
                INSERT INTO SC_BASE_APPT
                ( TRANS_ID, CALL_ID, SC_ID, STATUS )
                VALUES ( $trans_id, $call_id, $sc_id, '$status' )
            !;

            # the APPT_CLASSES record must be built
            push @cols, "CALL_ID";
            push @vals, "$call_id";

            foreach my $key ( keys %{$appt_classes_ref} ) {
                    push @cols, $key;
                    if (not ref $appt_classes_ref->{$key}) {
                            push @vals, $appt_classes_ref->{$key};
                    } else {
                            push @vals, scalar( join ",", @{$appt_clas
+ses_ref->{$key}} );
                    }
            }

            my $CLASSES_RECORD;

            $CLASSES_RECORD = 'INSERT INTO SC_APPT_CLASSES (' ;
            $CLASSES_RECORD .= (join ",", @cols) ;
            $CLASSES_RECORD .= ') VALUES (' ;
            $CLASSES_RECORD .= (join ",", map {if ($_ !~ /^\d+$/) {spr
+intf "'%s'", $_; } else { sprintf "$_"; } } @vals) ;
            $CLASSES_RECORD .= ')';

            $dbh->do("$TRANS_ID_TEMP_RECORD")
            or dienice("320 Couldn't execute statement: $TRANS_ID_TEMP
+_RECORD" . $dbh->errstr);

            $dbh->do("$BASE_INFO_TEMP_RECORD")
            or dienice("320 Couldn't execute statement: $BASE_INFO_TEM
+P_RECORD" . $dbh->errstr);

            $dbh->do("$BASE_APPT_TEMP_RECORD")
            or dienice("320 Couldn't execute statement: $BASE_APPT_TEM
+P_RECORD" . $dbh->errstr);

            $dbh->do("$CLASSES_RECORD")
            or dienice("320 Couldn't execute statement: $CLASSES_RECOR
+D" . $dbh->errstr);
            

            #my @returns = ($trans_id, $call_id);
            #return @returns;
        }
        
        push @temp_call_ids, $call_id;
        push @temp_trans_ids, $trans_id;
        
        print qq!<OPTION VALUE="$sc_id,$fname,$lname">$fname $lname</O
+PTION>\n!;
        $dummies++;

    }
    dienice("defined @temp_call_ids and @temp_trans_ids");

[download]

"sometimes when you make a request for the head you don't
want the big, fat body...don't you go snickering."
                                         -- Nathan Torkington UoP2K a.k.a gnat

Replies are listed 'Best First'.
Re: loop not ending... (redundant join/ split)
by agoth (Chaplain) on Oct 25, 2000 at 15:48 UTC
    Just a thought, or a pointer, hope you dont mind.
    Use references instead of a redundant join / split combination, that way if your data ever contains commas, it won't end in tears...
    And ->arrayref is theoretically faster than ->array.
    And you're using less variables up. 
    while (my $personref = $sth->fetchrow_arrayref()) {
    # take copy of data in array ref
    push @certified_people, \@{ $personref };
}
# stuff ..
for (@certified_people) {
    my ($fname,$lname,$sc_id) = @{ $_ };
    #stuff....
}

    [download]

[reply]
[d/l]
Re: loop not ending...
by Fastolfe (Vicar) on Oct 25, 2000 at 00:54 UTC
    Perhaps someone can pick up on something I missed on a few cursory passes, but I would try to narrow down the scope of the problem a bit further. You said you sprinkled some debugging code all around to identify its progress through the loop. Were you able to nail it down to a specific spot that was blocking/looping? Were you able to identify a function or other call that was blocking and not returning control? If you're not getting responsive debugging output, be sure $| is set to 1, so that stdio buffering isn't keeping you from seeing something when you should be.
[reply]
[d/l]

      $| is set t 1, got that :)

      the problem i'm having is that the only thing i've been able to determine is that it does not leave the loop. The array it's looping on only has three elements, it makes exactly three entries into the database and then has no database activity after that, it does all the other assignment in the loop and will do everything up to the last brace with no problem. It just won't leave the loop. I've literally gone through and put a die btween every line of code. I have known from the start it had three results to process - i have short-circuited it at two and it's fine with everything before that. I have altered the data coming in so that it will have less and more than three loops to go through, always on the last time through it fails.

      "sometimes when you make a request for the head you don't
      want the big, fat body...don't you go snickering."
                                               -- Nathan Torkington UoP2K a.k.a gnat

[reply]
[d/l]
[select]
        So, breaking it down, your code and debugging looks something like this: 
        for (...) { 
  print "We've started the loop\n";
  # body of loop
  print "We're about to leave the loop\n";
}
print "We've left the loop\n";

        [download]
        Produces this output (with 3 iterations): 
        We've started the loop
We're about to leave the loop
We've started the loop
We're about to leave the loop
We've started the loop
We're about to leave the loop
<hang>

        [download]
        Is this accurate?

        I'm deliberately oversimplifying and ignoring your code for the moment. If you have a line at the very end of the loop's block that is being executed, but the very next line after that is not, that doesn't sound right. Something else is amiss. If you can't reduce your code to that level of simplicity, then some element of complexity between that and your actual code is probably responsible for the behavior.

        See if you can reduce your code in functionality to the bare minimum required to reproduce the flaw. If the bug doesn't become self-evident by this time, post the simplified code and we'll see if we can't take it from there.

[reply]
[d/l]
[select]
RE: loop not ending...
by runrig (Abbot) on Oct 25, 2000 at 01:05 UTC
    Your dienice() doesn't actually print anything, it returns a string, but you're not using the return value in any way...

    Is there a reason you're connecting to a database in every iteration of your loop? That's probably going to be slow. Is there a live database connection at the beginning of the loop? Prepare all your SQL statements OUTSIDE of the loop with separate statement handles, and just execute them INSIDE the loop. Except for the CLASSES_RECORD insert statement, then maybe you could use 'prepare_cached'...

    You CAN use placeholders/bind values ('?') even in the insert statements, you know.

    Also, set RaiseError at least while testing. See 'perldoc DBI'.

    And last, have you tried running it in the debugger?
[reply]

      actually, the DB connection code and everything in that block had originally been in a module of my own. I ended up bringing it back into the main program for debugging so I can watch them interact. I did put the connection in the wrong place when i did so...but speed is far from my concern at the moment. there will never be more than two to five statement to go through, so i sacrifice the prepare for having them all together.

      as for RaiseError, I just prefer to test the results of each operation. Id o this so that things like disconnects or finishes that go wrong don't end up tripping up the rest of the program.

      "sometimes when you make a request for the head you don't
      want the big, fat body...don't you go snickering."
                                               -- Nathan Torkington UoP2K a.k.a gnat

[reply]
        Not to be too nitpicky, but if your code is going to hang as a result of a failed DBI call, then it's never going to get the chance to be tested. I haven't actually looked at the internals of DBI (hold on while I put on my asbestos suit), but I would imagine that there is a sub DESTROY that will clean up after a messy exit. On the converse, if RaiseError is set and the DBI calls are safe, you're not going to ever encounter the issue anyway.

        If I ever have a piece of code that is giving me a DBI related problem, first thing I do is set RaiseError. <shrug>

        ALL HAIL BRAK!!!

[reply]
[d/l]
        I say use placeholders and prepare once whether or not your statements get executed once or a hundred times, not necessarily for performance reasons, but just because its 'the right thing to do(tm)'. You don't have to worry about quoting your values correctly, the database itself might be able to cache the statement, thereby saving some database load if anyone else runs the same statement and/or the same script, and I know of one database that used to have a bug where if the same statement was prepared over and over too many times, it just didn't get prepared successfully anymore (now that was a hard bug to find).

        Ok, if the database is MySQL, then there's no real performance gain, but at least the code is more ready for another database that does benefit from such things. At least use prepare_cached w/placeholders if you can, which is the 'easy way' to prepare once.

        And what PsychoSpunk says above about RaiseError.
[reply]
RE: loop not ending...
by jptxs (Curate) on Oct 25, 2000 at 00:49 UTC

    sorry, the loop that doesn't end is the one that starts: 

    foreach my $certified_person (@certified_people)

    [download]

    "sometimes when you make a request for the head you don't
    want the big, fat body...don't you go snickering."
                                             -- Nathan Torkington UoP2K a.k.a gnat

[reply]
[d/l]
Re: (not loop i guess) loop not ending...
by jptxs (Curate) on Oct 25, 2000 at 18:25 UTC

    it doesnt appear to be that loop. it's the whole block the loop is in which won't end.

    I've set RaiseError, I took agoth's advice, I have literally placed a print to a file bewteen every line of code. It still does not die. The program is sort of a wizard to take you through scheduling an appointment. The other screens come up, collect their info and, more importantly, gexit and send data to the user, with no problem. It's just this one screen. I took ALL the code out of the program except for this. I put all the code from the sub i had pulled in back into my custom module. It still does all the entries to the database correctly. All the testing prints work until the very last - the one right after the final disconnect from the database, but RaiseError doesn't complain.

    I'm SO stumped.

    "sometimes when you make a request for the head you don't
    want the big, fat body...don't you go snickering."
                                             -- Nathan Torkington UoP2K a.k.a gnat

[reply]
Re: (resolution)loop not ending...
by jptxs (Curate) on Oct 27, 2000 at 00:03 UTC

    well, after much hair pulling, it seems the issue is with the Oracle OCI interface and the DBI on Linux. It's one of those pesky hit and miss things, though. So there has been no way to nail it down so that it happens every time. But I've been doing a lot of reading and testing and that seems to be the only thing which can account for what was happening.

    "sometimes when you make a request for the head you don't
    want the big, fat body...don't you go snickering."
                                             -- Nathan Torkington UoP2K a.k.a gnat

[reply]
      Do you have a support contact with Oracle? I would be very interested in hearing the cause of this...
[reply]

Back to Seekers of Perl Wisdom