Subject RE: [IBO] TIB_Query and InsertSQL
Author Jerry Sands
>I'm not seeing this at all, so I suggest there might be something
>wonky in your SQL. IBO has had a lot of tightening to comply with
>the corrections in SQL in Firebird 2. This is going to show up a lot
>of off-beam SQL in client apps that used to be forgiven.

>Let's see the following:

>-- The SELECT statement


SELECT MAINTENANCEITEMNUMBER

, RECEIVEDDATE

, RESOLVEDON

, CENTERNUMBER

, BUSINESSUNITNUMBER

, CREATIONDATE

, MODIFICATIONDATE

, RECEIVEDBYPERSONNUMBER

, PRIORITYNUMBER

, CHECKLISTNUMBER

, CENTERAREANUMBER

, SITUATION

, ACTIONLISTCOUNT

, CENTERNAME

, RECEIVEDBYPERSONNAME

, OPERSONNUMBER

, ACTIONLISTPENDINGCOUNT

, CENTERAREADESCRIPTION

, CHECKLISTDESCRIPTION

, LatestPendingAssignedTask

FROM GetMaintenanceForPerson1(:PersonNumber,:RequestType)



>-- The InsertSQL statement


INSERT INTO MaintenanceRequest

( MaintenanceItemNumber

, ReceivedDate

, ResolvedOn

, CenterNumber

, BusinessUnitNumber

, ReceivedByPersonNumber

, PriorityNumber

, CheckListNumber

, CenterAreaNumber

, Situation)

VALUES ( :MaintenanceItemNumber

, :ReceivedDate

, :ResolvedOn

, :CenterNumber

, :BusinessUnitNumber

, :ReceivedByPersonNumber

, :PriorityNumber

, :CheckListNumber

, :CenterAreaNumber

, :Situation)



Here is the update and delete SQL that work do their job correctly



UPDATE MaintenanceRequest

SET ReceivedDate = :ReceivedDate

, RESOLVEDON = :ResolvedOn

, SITUATION = :Situation

, ReceivedByPersonNumber = :ReceivedByPersonNumber

, CenterNumber = :CenterNumber

, CENTERAREANUMBER = :CenterAreaNumber

, CHECKLISTNUMBER = :CheckListNumber

WHERE MAINTENANCEITEMNUMBER = :MaintenanceItemNumber



DELETE FROM MaintenanceRequest WHERE MAINTENANCEITEMNUMBER =
:MaintenanceItemNumber



>-- The KeyLinks for the set



MAINTENANCEITEMNUMBER


>Helen





What bothers me is the fact that the SQL Monitor blows up posting an insert:



Project Maintenance.exe raised exception class EAccessViolation with message
‘Access violation at address 00000000. Read of Addreess 00000000’.



This is what does show up in the monitor screen after clearing the error
message. The MaintenanceItemNumber to be inserted is 497 (IBO fetched the
value after the insert) but the monitor reports before it blows up 491 which
is the value of the MaintenanceItemNumber for the current selected row when
the insert was executed (and the other column values also match that row as
well).



/*---

EXECUTE STATEMENT

TR_HANDLE = 3371056

STMT_HANDLE = 18677972

PARAMS = [ Version 1 SQLd 10 SQLn 10

[MAINTENANCEITEMNUMBER] = 491 ß

[RECEIVEDDATE] = '09 Apr 2007 09:13:53 2550'

[RESOLVEDON] = <NULL>

[CENTERNUMBER] = 14

[BUSINESSUNITNUMBER] = 2

[RECEIVEDBYPERSONNUMBER] = 121

[PRIORITYNUMBER] = 1

[CHECKLISTNUMBER] = 3

[CENTERAREANUMBER] = 4

[SITUATION] = 'The Situation' ]



SECONDS = 0.010



ERRCODE = 335544665

----*/











[Non-text portions of this message have been removed]