| Subject | RE: [IBO] TIB_Query and InsertSQL |
|---|---|
| Author | Jerry Sands |
| Post date | 2007-04-12T15:27:59Z |
Sorry this is getting so long.
I can send the database and source code if you want to try to duplicate the
problem.
At 12:05 PM 12/04/2007, you wrote:
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 dont 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.
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 dont
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
Agreed but a couple weeks of frustration is getting pretty old
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 dont 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]
I can send the database and source code if you want to try to duplicate the
problem.
At 12:05 PM 12/04/2007, you wrote:
>Well, after almost 2 weeks of pulling my hair out on the select storedThere is no reason why it would have anything to do with blobs if the
>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.
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 dont 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 theIf the stored data were corrupt, you would not be able restore it
>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.
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 dont
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 timesI don't think that is indicated..albeit the evidence is contradictory.
>and that does not seem to help at all.
>
>So I guess I rebuild the database from scratch and re-load.
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 dont 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]