At 01:27 AM 13/04/2007, you wrote:

>Sorry this is getting so long.
>
>I can send the database and source code if you want to try to duplicate the
>problem.

Yes, do, but not to the list. Send them to my own email address, above.

Meanwhile, I see some things in the monitor
output that don't make sense (so far) and need looking at.

Sorry, can't respond instantly to this - it looks
as though it's going to take some time.

Helen

>At 12:05 PM 12/04/2007, you wrote:
> >Well, after almost 2 weeks of pulling my hair out on the select stored
> >procedure / InsertSQL problem I at least know what will make the problem go
> >away (sort of).
> >
> >I had a varchar field of 1024 chars that when I reduced it to varchar(971)
> >or less the problem disappeared. Maybe that size has something to do with
> >blobs and will trigger something with Jason.
>
>There is no reason why it would have anything to do with blobs if the
>field is a varchar. They are distinctly different data types and are
>handled differently in the API. If you have somehow made a link
>between your problems and blobs, there is more that you haven't told us.
>
>
>
>The link made is after spending a lot of time looking thru code, and
>different interbase / firebird internals something (and I don’t remember
>what) made a reference to blobs. Just for the heck of it I cut the size of
>a Varchar(1024) field in the underlying table down to varchar(254) and it
>started working (and the code compiled with IBO 4.6 quit working). You are
>probably right about having nothing to do with blobs, I was just ready to
>look at anything.
>
>
>Some of your reports seem vague or conflicting...wasn't a
>characteristic of your problem that the query was passing the
>last-fetched row data across to the InsertSQL parameters, instead of
>the new data that was inserted into the Fields array? If that
>observation is accurate, how could reducing the size of a varchar
>field correct it?
>
>
>
>Agreed, and it might be interesting to find out how that could make the code
>compiled with IBO 4.6 quit working.
>
>
>If it is somehow "meaningful" that changing the size of a varchar
>"somewhere" (??? where ???) removes a source of *something* crashing,
>shouldn't you be looking at the definition of the stored procedure?
>
>
>
>I guess but remember, the same TIB_Query handles the UpdateSQL just fine for
>the stored procedure changing most of the same fields including the
>Varchar(1024) field in question. Not necessarily a perfect analogy but the
>stored procedure does not exhibit a problem there.
>
>
>Suppose, for example, that you have an output field defined in the
>RETURNS clause of your SP that is 1024 bytes. Suppose that the table
>your InsertSQL is inserting to has the corresponding field defined as
>varchar (971). Then, picking up the value from the dataset returned
>by the SP and trying to pass it to the input parameter of your
>InsertSQL statement *should* cause an exception in Firebird 1.5.4 or
>lower or any version of InterBase. (In Fb 2.x, varchar sizes are
>evaluated at run-time and will except *only* if the current value
>exceeds the defined size.) That is, you should encounter an
>exception, not crash.
>
>
>
>When I adjusted the size of the field in the table I also adjusted it in the
>stored procedure each time.
>
>
> >The strange part is, with the
> >exact same code, the exact same database with the only change begin IBO 4.6
> >vs IBO 4.7.16, IBO 4.7.16 always works with 971 or less and IBO 4.6 always
> >works with 972 or more. I cut the size down all the way to 16 and IBO 4.6
> >would not work. So I am thinking there is some kind of database corruption
> >causing the problem.
>
>If the stored data were corrupt, you would not be able restore it
>from backup, as you tell us (below) you have done.
>
>Agree!!
>
>At least as far as one can sort the wheat from the chaff here, it has
>the hallmarks of memory corruption....I think it would be interesting
>to look at those data sizes in the SP definition, as a place to start
>looking for sources of memory corruption. So far, it's not possible
>so far even to guess whether one is looking at an IBO bug or a
>pre-existing metadata anomaly (or application reference) that has now
>become significant due to the recent tightening-up in IBO.
>
>
>
>Understood.
>
>
>- Please double-check all your varchar definitions for the SP and
>the table. In fact, if possible, show us the source code of the
>entire SP AND the SQL property of the IB_Query.
>
>
>
>I can do that.
>
>
>- Also try to establish at what point the application is
>encountering an unreferenced object (which is what you indicated
>originally), i.e. does it occur when the app requests a Prepare? or
>is it later, when the actual INSERT statement is passed?
>
>I call insert on the query and with the monitor running the following is
>output:
>
>/*---
>
>PREPARE STATEMENT
>
>TR_HANDLE = 3371056
>
>STMT_HANDLE = 18701632
>
>
>
>SELECT GEN_ID ( MAINTENANCEITEMNUMBER, 1 )
>
>FROM RDB$DATABASE
>
>PLAN (RDB$DATABASE NATURAL)
>
>
>
>FIELDS = [ Version 1 SQLd 1 SQLn 1
>
> GEN_ID = 4294967296 ]
>
>
>
>SECONDS = 0.010
>
>----*/
>
>/*---
>
>EXECUTE2 DSQL
>
>TR_HANDLE = 3371056
>
>STMT_HANDLE = 18701632
>
>PARAMS = [ ]
>
>FIELDS = [ Version 1 SQLd 1 SQLn 1
>
> GEN_ID = 330 ] ß Generated primary Key
>
>
>
>SELECT COUNT: 1
>
>----*/
>
>/*---
>
>EXECUTE STATEMENT
>
>TR_HANDLE = 3371056
>
>STMT_HANDLE = 18709928
>
>PARAMS = [ Version 1 SQLd 1 SQLn 1
>
> ["MLNK_MAINTENANCEITEMNUMBER _0"] = 330 ]
>
>----*/
>
>/*---
>
>OPEN CURSOR
>
>STMT_HANDLE = 18709928
>
>NAME = C13595568194145541
>
>
>
>----*/
>
>/*--- Actionlist is a child table to the table in question. I don’t
>understand why this shows up since the link is the MaintananceItemNumber and
>there are no matches
>
>FETCH
>
>STMT_HANDLE = 18709928
>
>FIELDS = [ Version 1 SQLd 14 SQLn 14
>
> ACTIONLIST.ACTIONITEMNUMBER = 330
>
> ACTIONLIST.ASSIGNEDDATE[ASSIGNEDDATE] = '11 Apr 2007 10:56:23 2500'
>
> ACTIONLIST.COMPLETIONDATE = <NULL>
>
> ACTIONLIST.MAINTENANCEITEMNUMBER = 291
>
> ACTIONLIST.CREATIONDATE = '11 Apr 2007 10:56:23 2500'
>
> ACTIONLIST.MODIFICATIONDATE = '11 Apr 2007 10:56:23 2500'
>
> ACTIONLIST.ISACTIVE = 'T'
>
> [ASSIGNEDBYPERSONNAME] = 'Sheri Lewis'
>
> ACTIONLIST.ASSIGNEDBYPERSONNUMBER = 152
>
> [ASSIGNEDTOPERSONNAME] = 'David Arcand'
>
> ACTIONLIST.ASSIGNEDTOPERSONNUMBER = 121
>
> ACTIONLIST.ASSIGNEDTASK = 'fix this.'
>
> ACTIONLIST.COMPLETEBYDATE = <NULL>
>
> [COMPLETED] = 0 ]
>
>
>
>ERRCODE = 100
>
>----*/
>
>/*---
>
>CLOSE CURSOR
>
>STMT_HANDLE = 18709928
>
>----*/
>
>I put values into the remaining fields. Then I press post on a
>TIB_UpdateBar connected to the query. I have traced the code to the
>following in IB_Components
>
>Line 22909 in IB_Components
>
>procedure TIB_Statement.API_Execute;
>
>var
>
> In_DA: PXSQLDA;
>
> SaveCW: word;
>
>begin
>
> if ParamCount > 0 then
>
> In_DA := FParams.PSQLDA
>
> else
>
> In_DA := nil;
>
> with IB_Session do
>
> begin
>
> asm fstcw [SaveCW] end;
>
> errcode := isc_dsql_execute( @status, ß This code returns an error
>posting an insert but also executes here with no problem posting an update
>
> PtrHandle,
>
> PstHandle,
>
> SQLDialect,
>
> In_DA );
>
> asm fldcw [SaveCW] end;
>
> if errcode <> 0 then HandleException( Self );
>
> end;
>
> flag_rows_affected_invalid := true;
>
> FWasSingleton := false;
>
>end;
>
>After this point (when the SQL Monitor is running and enabled) I will get an
>access violation. After continue the following will show up in the monitor:
>
>/*---
>
>EXECUTE STATEMENT
>
>TR_HANDLE = 3371056
>
>STMT_HANDLE = 18678124
>
>PARAMS = [ Version 1 SQLd 10 SQLn 10
>
> [MAINTENANCEITEMNUMBER] = 309 ß This is the primary key of the row in the
>dataset that was selected when I called insert
>
> [RECEIVEDDATE] = '11 Apr 2007 19:43:39 8280'
>
> [RESOLVEDON] = <NULL>
>
> [CENTERNUMBER] = 23
>
> [BUSINESSUNITNUMBER] = 2
>
> [RECEIVEDBYPERSONNUMBER] = 121
>
> [PRIORITYNUMBER] = 3
>
> [CHECKLISTNUMBER] = 52
>
> [CENTERAREANUMBER] = 8
>
> [SITUATION] = 'iiiuytghbnv' ]
>
>
>
>ERRCODE = 335544665
>
>----*/
>
>
>- At one point you seemed to say that the AV was occurring as a
>result of referring to the IB_Monitor. Do you ever actually make the
>Execute call on the Monitor? Where this question is going is - are
>you trying to reference a Monitor that is not actually active?
>
>
>
>The monitor is running normally for updates, deletes, etc and as you can see
>above it is running before and after the access violation.
>
>Assuming I am not somehow corrupting memory I think it should interest you
>that an access violation is ever happening
>
>
>
> > I have backed up and restored the database many times
> >and that does not seem to help at all.
> >
> >So I guess I rebuild the database from scratch and re-load.
>
>I don't think that is indicated..albeit the evidence is contradictory.
>
>Agreed ­ but a couple weeks of frustration is getting pretty old
>
>
>
> >Any other ideas?
>
>One other thing that comes to me from left-field is the possibility
>that you have your server configured with OldParameterOrdering set to
>True. This *will* stuff things up with parameterised SPs and more
>recent versions of IBO, although that change goes back a fair way,
>possibly to the start of the 4.5 series, if not earlier.....
>
>
>
>Changed that. Breaks both the code compiled with IBO 4.6 and IBO 4.7
>
>
>Also, are we certain that the row we are inserting is not going to
>violate a Foreign Key relationship somewhere?
>
>
>
>It does not when compiled with IBO 4.6. All foreign keys are “’looked up”
>in the application. They work except under the conditions we have been
>discussing.
>
>
>It will be at least interesting to see the SP source and the
>IB_Query's SQL, if just to eliminate the possibility that your
>InsertSQL is inconsistent with the SP output. If that is the case
>then the solution would be a simple matter of detaching the InsertSQL
>from the dataset altogether and running the operation as a separate
>function.
>
>
>
>Thought about detaching the two. I would call that a “workaround”.
>Remember, this program ran fine for us when compiled with IBO 4.6 and only
>broke after compile with IBO 4.7.
>
>
>It would be soooooo much simpler if you were able to reproduce this
>problem in a demo app. Is there any chance you could cook up a demo
>database consisting of just the SP and the tables involved in the
>problem, with a set of demo data installed in it? If you didn't want
>it to be public, you could send it to me directly, or send me a URL
>where I could download it...
>
>
>
>You are so right about making it so much simpler to reproduce this in a demo
>app. I spend a lot of time trying to do just that before ever posting here
>because I know that is by far the best chance for solving the problem. But
>I have not been successful in doing that. It works just fine in every demo
>in which I have tried to make it break. I know I can make a mistake in my
>coding but I would hope you can see by now that I do have an idea on how
>this all works (I have done this kind of thing many times in the past) and
>the fact that I can write demo programs that are not broken should indicate
>that.
>
>I have in my experience in the past found problems that point to some kind
>of database corruption where a backup and restore fixes it. This makes no
>sense.
>
>The reason I have worked so hard on this is obvious. The code broke when I
>moved to IBO 4.7 and there have been problems with the re-write to support
>Firebird 2. I understand that but don’t want this to bite me down the road.
>
>
>Here is the underlying table
>
>/* This table contains each individual maintenance request.
>
>For a maintenance request to be entered it requires a link to the person
>requesting from the Person table,
>
>an assigned business unit from the BusinessUnit table, a Center from the
>Center table,
>
>and at least one action item in the ActionList table.*/
>
>CREATE TABLE MAINTENANCEREQUEST (
>
> MAINTENANCEITEMNUMBER Integer NOT NULL,
>
> RECEIVEDDATE TIMESTAMP NOT NULL,
>
> RESOLVEDON TIMESTAMP,
>
> CENTERNUMBER Integer NOT NULL,
>
> BUSINESSUNITNUMBER Integer NOT NULL,
>
> CREATIONDATE TIMESTAMP NOT NULL,
>
> MODIFICATIONDATE TIMESTAMP NOT NULL,
>
> RECEIVEDBYPERSONNUMBER Integer NOT NULL,
>
> ESTIMATEDLABORCOST Numeric(15,2),
>
> ESTIMATEDMATERIALCOST Numeric(15,2),
>
> ESTIMATEDTRAVELCOST Numeric(15,2),
>
> PRIORITYNUMBER Integer NOT NULL,
>
> CHECKLISTNUMBER Integer NOT NULL,
>
> CENTERAREANUMBER Integer NOT NULL,
>
> SITUATION Varchar(1024) NOT NULL,
>
> CONSTRAINT MAINTENANCEREQUEST_PK PRIMARY KEY (MAINTENANCEITEMNUMBER)
>
>);
>
>
>
>ALTER TABLE MAINTENANCEREQUEST ADD CONSTRAINT BUSINESSUNIT_MAINTENANCEREQ_FK
>FOREIGN KEY (BUSINESSUNITNUMBER) REFERENCES
>BUSINESSUNIT(BUSINESSUNITNUMBER);
>
>ALTER TABLE MAINTENANCEREQUEST ADD CONSTRAINT CENTERAREA_FK FOREIGN KEY
>(CENTERAREANUMBER) REFERENCES CENTERAREA(CENTERAREANUMBER);
>
>ALTER TABLE MAINTENANCEREQUEST ADD CONSTRAINT CENTER_MAINTENANCEREQ_FK
>FOREIGN KEY (CENTERNUMBER) REFERENCES CENTER(CENTERNUMBER);
>
>ALTER TABLE MAINTENANCEREQUEST ADD CONSTRAINT CHECKLIST_MAINTENANCEREQ_FK
>FOREIGN KEY (CHECKLISTNUMBER) REFERENCES CHECKLIST(CHECKLISTNUMBER);
>
>ALTER TABLE MAINTENANCEREQUEST ADD CONSTRAINT PERSON_MAINTENANCEREQ_FK
>FOREIGN KEY (RECEIVEDBYPERSONNUMBER) REFERENCES PERSON(PERSONNUMBER);
>
>ALTER TABLE MAINTENANCEREQUEST ADD CONSTRAINT PRIORITY_MAINTENANCEREQ_FK
>FOREIGN KEY (PRIORITYNUMBER) REFERENCES PRIORITY(PRIORITYNUMBER);
>
>
>
>Here is the stored procedure
>
>
>
>/* Restoring depended objects */
>
>CREATE OR ALTER PROCEDURE GETMAINTENANCEFORPERSON1 (PERSONNUMBER Integer,
>
> REQUESTTYPE Integer)
>
>returns (MAINTENANCEITEMNUMBER Integer,
>
> RECEIVEDDATE TIMESTAMP,
>
> PRIORITYNUMBER Integer,
>
> RESOLVEDON TIMESTAMP,
>
> CENTERNUMBER Integer,
>
> BUSINESSUNITNUMBER Integer,
>
> CREATIONDATE TIMESTAMP,
>
> MODIFICATIONDATE TIMESTAMP,
>
> SITUATION Varchar(1024),
>
> RECEIVEDBYPERSONNUMBER Integer,
>
> ACTIONLISTCOUNT Integer,
>
> CENTERNAME Varchar(30),
>
> RECEIVEDBYPERSONNAME Varchar(43),
>
> OPERSONNUMBER Integer,
>
> ACTIONLISTPENDINGCOUNT Integer,
>
> CHECKLISTNUMBER Integer,
>
> CENTERAREANUMBER Integer,
>
> CENTERAREADESCRIPTION Varchar(50),
>
> CHECKLISTDESCRIPTION Varchar(50),
>
> LATESTPENDINGASSIGNEDTASK TIMESTAMP)
>
>AS
>
> declare variable MyActiveTasks Integer;
>
> declare variable MyActiveRequests Integer;
>
> declare variable MyStalledRequests Integer;
>
> declare variable AllActiveRequests Integer;
>
> declare variable AllStalledRequests Integer;
>
> declare variable PersonNumberActionListPendingCount Integer;
>
>begin
>
>-- 1234567890123456789012345678901
>
> MyActiveTasks = 1;
>
> MyActiveRequests = 2;
>
> MyStalledRequests = 3;
>
> AllActiveRequests = 4;
>
> AllStalledRequests = 5;
>
>
>
>IF (RequestType = MyActiveTasks) THEN
>
>BEGIN
>
> FOR SELECT MaintenanceItemNumber
>
> , ReceivedDate
>
> , ResolvedOn
>
> , SITUATION
>
> , CenterNumber
>
> , ReceivedByPersonNumber
>
> , PriorityNumber
>
> , BusinessUnitNumber
>
> , CreationDate
>
> , ModificationDate
>
> , (SELECT Count(*)
>
> FROM ActionList
>
> WHERE MaintenanceItemNumber =
>MaintenanceRequest.MaintenanceItemNumber)
>
> , (SELECT Count(*)
>
> FROM ActionList
>
> WHERE MaintenanceItemNumber =
>MaintenanceRequest.MaintenanceItemNumber
>
> AND CompletionDate IS NULL
>
> AND AssignedToPersonNumber = :PersonNumber)
>
> , (SELECT Count(*)
>
> FROM ActionList
>
> WHERE MaintenanceItemNumber =
>MaintenanceRequest.MaintenanceItemNumber
>
> AND CompletionDate IS NULL
>
> AND AssignedToPersonNumber = :PersonNumber)
>
> , (SELECT Max(AssignedDate)
>
> FROM ActionList
>
> WHERE MaintenanceItemNumber =
>MaintenanceRequest.MaintenanceItemNumber
>
> AND CompletionDate IS NULL
>
> AND AssignedToPersonNumber = :PersonNumber)
>
> , CenterAreaNumber
>
> , CheckListNumber
>
> FROM MaintenanceRequest
>
> WHERE ResolvedOn IS NULL
>
> INTO MaintenanceItemNumber
>
> , ReceivedDate
>
> , ResolvedOn
>
> , Situation
>
> , CenterNumber
>
> , ReceivedByPersonNumber
>
> , PriorityNumber
>
> , BusinessUnitNumber
>
> , CreationDate
>
> , ModificationDate
>
> , ActionListCount
>
> , ActionListPendingCount
>
> , PersonNumberActionListPendingCount
>
> , LatestPendingAssignedTask
>
> , CenterAreaNumber
>
> , CheckListNumber
>
> DO
>
> BEGIN
>
> IF (PersonNumberActionListPendingCount > 0) THEN
>
> BEGIN
>
> SELECT Name
>
> FROM Center
>
> WHERE CenterNumber = :CenterNumber
>
> INTO CENTERNAME;
>
> SELECT FULLNAME
>
> FROM Person
>
> WHERE PersonNumber = :ReceivedByPersonNumber
>
> INTO ReceivedByPersonName;
>
> SELECT Description
>
> FROM CenterArea
>
> WHERE CenterAreaNumber = :CenterAreaNumber
>
> INTO CenterAreaDescription;
>
> SELECT Description
>
> FROM CheckList
>
> WHERE CheckListNumber = :CheckListNumber
>
> INTO CheckListDescription;
>
> OPersonNumber = PersonNumber;
>
> SUSPEND;
>
> END
>
> END
>
>END
>
>ELSE
>
> IF (RequestType = MyActiveRequests) THEN
>
> BEGIN
>
> FOR SELECT MaintenanceItemNumber
>
> , ReceivedDate
>
> , ResolvedOn
>
> , SITUATION
>
> , CenterNumber
>
> , ReceivedByPersonNumber
>
> , PriorityNumber
>
> , BusinessUnitNumber
>
> , CreationDate
>
> , ModificationDate
>
> , (SELECT Count(*)
>
> FROM ActionList
>
> WHERE MaintenanceItemNumber =
>MaintenanceRequest.MaintenanceItemNumber)
>
> , (SELECT Count(*)
>
> FROM ActionList
>
> WHERE MaintenanceItemNumber =
>MaintenanceRequest.MaintenanceItemNumber
>
> AND CompletionDate IS NULL)
>
> , CenterAreaNumber
>
> , CheckListNumber
>
> FROM MaintenanceRequest
>
> WHERE ResolvedOn IS NULL
>
> AND ReceivedByPersonNumber = :PersonNumber
>
> INTO MaintenanceItemNumber
>
> , ReceivedDate
>
> , ResolvedOn
>
> , Situation
>
> , CenterNumber
>
> , ReceivedByPersonNumber
>
> , PriorityNumber
>
> , BusinessUnitNumber
>
> , CreationDate
>
> , ModificationDate
>
> , ActionListCount
>
> , ActionListPendingCount
>
> , CenterAreaNumber
>
> , CheckListNumber
>
> DO
>
> BEGIN
>
> IF (ActionListPendingCount > 0) THEN
>
> BEGIN
>
> PersonNumberActionListPendingCount = 0;
>
> LatestPendingAssignedTask = ReceivedDate; /* Maintain Request
>ReceivedDate for ordering MR's */
>
> SELECT Name
>
> FROM Center
>
> WHERE CenterNumber = :CenterNumber
>
> INTO CENTERNAME;
>
> SELECT FULLNAME
>
> FROM Person
>
> WHERE PersonNumber = :ReceivedByPersonNumber
>
> INTO ReceivedByPersonName;
>
> SELECT Description
>
> FROM CenterArea
>
> WHERE CenterAreaNumber = :CenterAreaNumber
>
> INTO CenterAreaDescription;
>
> SELECT Description
>
> FROM CheckList
>
> WHERE CheckListNumber = :CheckListNumber
>
> INTO CheckListDescription;
>
> OPersonNumber = PersonNumber;
>
> SUSPEND;
>
> END
>
> END
>
> END
>
> ELSE
>
> IF (RequestType = MyStalledRequests) THEN
>
> BEGIN
>
> FOR SELECT MaintenanceItemNumber
>
> , ReceivedDate
>
> , ResolvedOn
>
> , SITUATION
>
> , CenterNumber
>
> , ReceivedByPersonNumber
>
> , PriorityNumber
>
> , BusinessUnitNumber
>
> , CreationDate
>
> , ModificationDate
>
> , (SELECT Count(*)
>
> FROM ActionList
>
> WHERE MaintenanceItemNumber =
>MaintenanceRequest.MaintenanceItemNumber)
>
> , (SELECT Count(*)
>
> FROM ActionList
>
> WHERE MaintenanceItemNumber =
>MaintenanceRequest.MaintenanceItemNumber
>
> AND CompletionDate IS NULL)
>
> , CenterAreaNumber
>
> , CheckListNumber
>
> FROM MaintenanceRequest
>
> WHERE ResolvedOn IS NULL
>
> AND ReceivedByPersonNumber = :PersonNumber
>
> INTO MaintenanceItemNumber
>
> , ReceivedDate
>
> , ResolvedOn
>
> , Situation
>
> , CenterNumber
>
> , ReceivedByPersonNumber
>
> , PriorityNumber
>
> , BusinessUnitNumber
>
> , CreationDate
>
> , ModificationDate
>
> , ActionListCount
>
> , ActionListPendingCount
>
> , CenterAreaNumber
>
> , CheckListNumber
>
> DO
>
> BEGIN
>
> IF (ActionListPendingCount = 0) THEN
>
> BEGIN
>
> PersonNumberActionListPendingCount = 0;
>
> LatestPendingAssignedTask = ReceivedDate; /* Maintain Request
>ReceivedDate for ordering MR's */
>
> SELECT Name
>
> FROM Center
>
> WHERE CenterNumber = :CenterNumber
>
> INTO CENTERNAME;
>
> SELECT FULLNAME
>
> FROM Person
>
> WHERE PersonNumber = :ReceivedByPersonNumber
>
> INTO ReceivedByPersonName;
>
>
>
> SELECT Description
>
> FROM CenterArea
>
> WHERE CenterAreaNumber = :CenterAreaNumber
>
> INTO CenterAreaDescription;
>
>
>
> SELECT Description
>
> FROM CheckList
>
> WHERE CheckListNumber = :CheckListNumber
>
> INTO CheckListDescription;
>
>
>
> OPersonNumber = PersonNumber;
>
> SUSPEND;
>
> END
>
> END
>
>
>
> END
>
> ELSE
>
> IF (RequestType = AllActiveRequests) THEN
>
> BEGIN
>
> FOR SELECT MaintenanceItemNumber
>
> , ReceivedDate
>
> , ResolvedOn
>
> , SITUATION
>
> , CenterNumber
>
> , ReceivedByPersonNumber
>
> , PriorityNumber
>
> , BusinessUnitNumber
>
> , CreationDate
>
> , ModificationDate
>
> , (SELECT Count(*)
>
> FROM ActionList
>
> WHERE MaintenanceItemNumber =
>MaintenanceRequest.MaintenanceItemNumber)
>
> , (SELECT Count(*)
>
> FROM ActionList
>
> WHERE MaintenanceItemNumber =
>MaintenanceRequest.MaintenanceItemNumber
>
> AND CompletionDate IS NULL)
>
> , CenterAreaNumber
>
> , CheckListNumber
>
> FROM MaintenanceRequest
>
> WHERE ResolvedOn IS NULL
>
> INTO MaintenanceItemNumber
>
> , ReceivedDate
>
> , ResolvedOn
>
> , Situation
>
> , CenterNumber
>
> , ReceivedByPersonNumber
>
> , PriorityNumber
>
> , BusinessUnitNumber
>
> , CreationDate
>
> , ModificationDate
>
> , ActionListCount
>
> , ActionListPendingCount
>
> , CenterAreaNumber
>
> , CheckListNumber
>
> DO
>
> BEGIN
>
> IF (ActionListPendingCount > 0) THEN
>
> BEGIN
>
> SELECT Name
>
> FROM Center
>
> WHERE CenterNumber = :CenterNumber
>
> INTO CENTERNAME;
>
> SELECT FULLNAME
>
> FROM Person
>
> WHERE PersonNumber = :ReceivedByPersonNumber
>
> INTO ReceivedByPersonName;
>
> SELECT Description
>
> FROM CenterArea
>
> WHERE CenterAreaNumber = :CenterAreaNumber
>
> INTO CenterAreaDescription;
>
> SELECT Description
>
> FROM CheckList
>
> WHERE CheckListNumber = :CheckListNumber
>
> INTO CheckListDescription;
>
> OPersonNumber = PersonNumber;
>
> SUSPEND;
>
> END
>
> END
>
> END
>
> ELSE
>
> IF (RequestType = AllStalledRequests) THEN
>
> BEGIN
>
> FOR SELECT MaintenanceItemNumber
>
> , ReceivedDate
>
> , ResolvedOn
>
> , SITUATION
>
> , CenterNumber
>
> , ReceivedByPersonNumber
>
> , PriorityNumber
>
> , BusinessUnitNumber
>
> , CreationDate
>
> , ModificationDate
>
> , (SELECT Count(*)
>
> FROM ActionList
>
> WHERE MaintenanceItemNumber =
>MaintenanceRequest.MaintenanceItemNumber)
>
> , (SELECT Count(*)
>
> FROM ActionList
>
> WHERE MaintenanceItemNumber =
>MaintenanceRequest.MaintenanceItemNumber
>
> AND CompletionDate IS NULL)
>
> , CenterAreaNumber
>
> , CheckListNumber
>
> FROM MaintenanceRequest
>
> WHERE ResolvedOn IS NULL
>
> INTO MaintenanceItemNumber
>
> , ReceivedDate
>
> , ResolvedOn
>
> , Situation
>
> , CenterNumber
>
> , ReceivedByPersonNumber
>
> , PriorityNumber
>
> , BusinessUnitNumber
>
> , CreationDate
>
> , ModificationDate
>
> , ActionListCount
>
> , ActionListPendingCount
>
> , CenterAreaNumber
>
> , CheckListNumber
>
> DO
>
> BEGIN
>
> IF (ActionListPendingCount = 0) THEN
>
> BEGIN
>
> SELECT Name
>
> FROM Center
>
> WHERE CenterNumber = :CenterNumber
>
> INTO CENTERNAME;
>
> SELECT FULLNAME
>
> FROM Person
>
> WHERE PersonNumber = :ReceivedByPersonNumber
>
> INTO ReceivedByPersonName;
>
>
>
> SELECT Description
>
> FROM CenterArea
>
> WHERE CenterAreaNumber = :CenterAreaNumber
>
> INTO CenterAreaDescription;
>
>
>
> SELECT Description
>
> FROM CheckList
>
> WHERE CheckListNumber = :CheckListNumber
>
> INTO CheckListDescription;
>
>
>
> OPersonNumber = PersonNumber;
>
> SUSPEND;
>
> END
>
> END
>
> END
>
>End
>
>
>
>
>
>[Non-text portions of this message have been removed]
>
>
>
>___________________________________________________________________________
>IB Objects - direct, complete, custom connectivity to Firebird or InterBase
> without the need for BDE, ODBC or any other layer.
>___________________________________________________________________________
>http://www.ibobjects.com - your IBO community resource for Tech Info papers,
>keyword-searchable FAQ, community code contributions and more !
>Yahoo! Groups Links
>
>
>