At 01:27 AM 13/04/2007, Jerry Sands wrote:

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

Still waiting for this.

>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.

In both of those scenarios, the exact parameters
for the DML statement are read from the
dataset. Where we need to look for the insert is
why the Insert call is passing the wrong values
to the params for the INSERT statement.

>At least as far as one can sort the wheat from the chaff here, it has
>the hallmarks of memory corruption....

...in the application. Question: what is
interfering with IBO's formation of the params
array for the INSERT statement. Given the AV, we
can't overlook the possibility that the code is
trying to assign values to a params array that has not been prepared.

>- 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 ]

At this point the monitor output is anomalous
compared to what I see on a GEN_ID prepare:

FIELDS = [ Version 1 SQLd 1 SQLn 30
"GEN_ID" = <NIL> ]
----*/

>
>
>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 ]
>
>----*/

This looks as though you have your GeneratorLinks
set up as part of a Master-Detail
relationship. You haven't mentioned this so far.

a) From what you *have* mentioned, we should not be seeing this

b) If indeed you do have a dependency implement
somewhere, then one element of the misbehaviour
might indeed stem from the tightening of parser
rules in IBO 4.7B16. If you add up the tally of
characters in the identifier that IBO creates for
the masterlinks key here, including the
double-quotes, it comes to 30. As far as I
recall, this is either 2 or 3 over the limit for
parameter identifiers and long fieldnames have
always caused problems in this area. I don't
understand why the parser now double-quotes
things it didn't used to double-quote....but this
might be a new byway of the parser changes that has not been tested.

Since you say that your InsertSQL used to work
(and assuming that the masterlinking does
actually belong there), you might solve this by
aliasing that field with a shorter fieldname in
the SELECT statement (or, better, giving a
shorter name to the output field in the actual SP
definition). If that fixes the problem then
Jason will need to look at it, viz. that long
field names that were 'marginal' in the past are now in the red area.

>/*---
>
>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

It would figure, if you have that field
masterlinked from a field in Actionlist in your
setup. The memory structure
["MLNK_MAINTENANCEITEMNUMBER _0"] = 330 ] would
referring to the child record that is linked to
the current record in the SP output, not to the
new record that you are trying to insert....in
which case we might be looking at a horrible
mess....masterlinking links live datasets and a
selectable SP is no-way a live dataset. It's OK
to link non-live datasets for display purposes
but the automatic M/D behaviour would have to be
disabled for any DML based on the SP output.

Even though you haven't told us about it, that
Actionlist dataset that is masterlinking through
the SP's datasource just has to be there, for IBO
to have detected the relationship. IBO would
otherwise have no knowledge of the link. The AV
could well be occurring around a method call on *that* dataset.

Another thing to look at would be whether you
have (inappropriately) set a KeyRelation property
on the SP's dataset. That is definitely a
No-No. If IBO is not ignoring that then it would
set off a chain of anomalies. KeyRelation should
be ignored for SPs....if you have one there and
it is not being ignored then that's another thing for Jason to explore.

>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.

But the monitor shows that the parameters are
being applied to a different statement. So - you
need to review that whole environment and find
out what's really going on when you press Post.

>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

And I'd be very nervous that your updates and
deletes are not doing what you think they are!

>The monitor is running normally for updates, deletes, etc and as you can see
>above it is running before and after the access violation.

It doesn't look normal to me, for what you have
said is supposed to be happening....

>Assuming I am not somehow corrupting memory I think it should interest you
>that an access violation is ever happening

It interests me insofar as it is a clue to
locating where stuff is going wrong. One of the
most common causes of an "unreferenced object" AV
(i.e. address zero) with IBO comes from
attempting to apply values to params in an
unprepared statement. I think you have to find
out why the Insert call on the SP's dataset is
finding Actionlist and trying to apply the
Fields[] values of the current SP output record to that dataset.

I wrote:

>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

That answer doesn't say anything useful. The
useful part would be WHAT HAPPENS when the
OldParameterOrdering is reverted to zero.

>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.

What does "looked up" mean? That you have
KeySource/KeyLinks relationships between the SP
output and some tables in the database?

>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.

With the apparent cross-threads in the DML logic
here, that you haven't put on the table, I'm not
convinced that these operations ever worked the
way you thought they did, albeit they used to
"work" in the sense that they didn't cause exceptions or blow up.

From your point of view, the "best case"
scenario would be that the logic of master-detail
inserts has somehow been tightened up, creating a
byway in logic that now produces an "effect" in
your application code where, in the past, it was
treated as a no-op. In this "best case", one has
to hope that, somehow, you have avoided logical
corruption from updates and deletes based on this
SP and its tributaries in your application.

Again, from your point of view, the "worst case"
would be that you have a database full of
undetectably lost and anomalous data that
occurred because of false assumptions about your
dataset logic, viz. ActionList records that have
been reassigned to the wrong MAINTENANCEREQUEST
record (in the case of updates and inserts) or
not deleted when you thought they were (in the case of some deletes) .

In between, are possibilities like that you have
found a bug in IBO's logic (good from everyone's
POV, provided it can be found) or that the parser
changes have found bugs in your application logic
(for which the only "good" part for you is that
it has made you aware of something you should
fix...existing data would remain unreliable, though).

Ironically, if you are not planning a move to Fb
2, you don't actually need the increased rigour
of the IBO 4.7 upgrade. If IBO 4.7 really has
found a flaw in your application, it would have
to be counted as a "plus" to be thus alerted.

That said, it's not an ideal state to be using
such an old version of Fb 1.5 and there *is* that
need to avoid using the client library that was distributed with Fb 1.5.2.

Helen