BTW the end of the updateSchedule() function looks like this
} catch (SQLException e) {
log.error("WeeklyScheduleDAO.updateWeeklySchedule ()
failed", e);
connection.rollback();
throw new Exception(e);
} catch (Exception e) {
log.error("WeeklyScheduleDAO.updateWeeklySchedule ()
failed", e);
connection.rollback();
throw new Exception(e);
} finally {
try { csDelete.close(); } catch (Exception e) {};
try { csInsert.close(); } catch (Exception e) {};
try { csNote.close(); } catch (Exception e) {};
releaseConnection(connection);
}
}
On Thu, Mar 27, 2008 at 9:21 AM, Greg Morphis <[EMAIL PROTECTED]> wrote:
> I have this app that is used to schedule employees, for a while we
> we're using timestamps to record start and end times of shifts.
> We started having issues with daylight savings and the client's
> time/server's time being different.
> Anyways we recently changed that to numbers so for example 5pm = 17,
> such-and-so-forth.
> Everything is working fine with that.
> But, I was informed of an error, if you try to schedule someone at one
> location when they are scheduled another during the same time it's
> suppose to warn the user and reload the schedule. However the
> application just hangs..
>
> We generate the error in Oracle and bubble it up..
>
> Looks like this:
> --first we will validate that this record can be inserted.
> validation involves checking for time overlaps
> FOR cr_row IN c_rowcheck LOOP
> BEGIN
> IF( (v_starttime BETWEEN cr_row.starttime_num AND
> cr_row.endtime_num)
> OR (v_endtime BETWEEN cr_row.starttime_num AND
> cr_row.endtime_num )
> OR (v_starttime < cr_row.starttime_num AND v_endtime
> > cr_row.endtime_num) ) THEN
> --the start42 and end42 are for the UI
> errDay :=
> to_char(getDateFromNum(cr_row.day,cr_row.week,cr_row.year),
> 'Day');
> errMsg := 'START42 Employee ' || emplName || ' is
> already on the schedule on ' || errDay || ' from ' ||
> militaryToStandardTime(cr_row.starttime_num) || ' to ' ||
> militaryToStandardTime(cr_row.endtime_num) || ' at location ' ||
> cr_row.descr || ', ' || cr_row.city || ', ' || cr_row.state ||
> 'END42';
> RAISE_APPLICATION_ERROR(-20002,errMsg );
> END IF;
> END;
> END LOOP;
>
> I believe this part of the code to be working fine. I can run this
> manually, plug in some variables in Oracle and bam, I get an error
> message if the user is elsewhere.
> The problem I'm having is when you do this in Flex.. you get the error
> message
>
> Mar 26, 2008 5:07:32 PM com.dao.WeeklyScheduleDAO updateWeeklySchedule
> SEVERE: WeeklyScheduleDAO.updateWeeklySchedule () failed
> java.sql.SQLException: ORA-20002: START42 Employee Bob Hope is already
> on the schedule on Sunday from 10:45 to 7:45 at location 1234 ST,
> Sumcity, ALEND42
> ORA-06512: at "AOPSCHEDULE.INSERTSCHEDULEENTRY_NEW", line 38
> ORA-06512: at line 1
>
> The user sees the error message they click okay and we have a popup
> "Processing..." when it should load the schedule.. It hangs there..
> With "Processing..." stuck on the screen, the user never regains
> control of the screen and has to reload the page.
> I do not see where the error is coming from.
> So I'm trying to follow the code up to Flex and see where this is bombing..
> So where does the code go from the DAO? The BO? Then to the Delegate?
> Then off to Flex? I tried to google a few times but was getting no
> where.
> If someone can explain the path of code using Cairngorm I'd appreciate it.
>
> The logs stop dead at:
>
> [Flex] 03/27/2008 09:15:31.438 Serializing AMF/HTTP response
> Version: 3
> (Message #0 targetURI=/7/onStatus, responseURI=)
> (Typed Object #0 'flex.messaging.messages.ErrorMessage')
> rootCause = (Typed Object #1 'java.lang.Exception')
> localizedMessage = "java.sql.SQLException: ORA-20002: START42
> Employee Bob Hope is already on the schedule on Sunday from 10:45
> to 7:45 at location 1234 ST, City ALEND42
> ORA-06512: at "AOPSCHEDULE.INSERTSCHEDULEENTRY_NEW", line 38
> ORA-06512: at line 1
> "
> message = "java.sql.SQLException: ORA-20002: START42 Employee
> Bob Hope is already on the schedule on Sunday from 10:45 to 7:45 at
> location 1234 ST, City ALEND42
> ORA-06512: at "AOPSCHEDULE.INSERTSCHEDULEENTRY_NEW", line 38
> ORA-06512: at line 1
> "
> cause = (Typed Object #2 'java.sql.SQLException')
> localizedMessage = "ORA-20002: START42 Employee Bob Hope is
> already on the schedule on Sunday from 10:45 to 7:45 at location
> 1234 ST, City ALEND42
> ORA-06512: at "AOPSCHEDULE.INSERTSCHEDULEENTRY_NEW", line 38
> ORA-06512: at line 1
> "
> message = "ORA-20002: START42 Employee Bob Hope is already
> on the schedule on Sunday from 10:45 to 7:45 at location 1234 ST,
> City ALEND42
> ORA-06512: at "AOPSCHEDULE.INSERTSCHEDULEENTRY_NEW", line 38
> ORA-06512: at line 1
> "
> SQLState = "72000"
> nextException = null
> cause = null
> errorCode = 20002
> destination = "SchedulerServiceRO"
> headers = (Object #3)
> correlationId = "E75EC4AF-451B-1518-E773-F097DB89D3B8"
> faultString = "java.lang.Exception : java.sql.SQLException:
> ORA-20002: START42 Employee Bob Hope is already on the schedule on
> Sunday from 10:45 to 7:45 at location 1234 ST, City ALEND42
> ORA-06512: at "AOPSCHEDULE.INSERTSCHEDULEENTRY_NEW", line 38
> ORA-06512: at line 1
> "
> messageId = "E362EA84-EF08-E524-E1CF-49EA11BB63DE"
> faultCode = "Server.Processing"
> timeToLive = 0.0
> extendedData = null
> faultDetail = null
> clientId = null
> timestamp = 1.206627331423E12
> body = null
>
>
>
>
> Thanks
>
