| Author |
Message
|
| akil |
 Posted: Thu Dec 10, 2015 5:39 am Post subject: IIB9: ESQL UPDATE |
 |
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Hi
For the following query
| Code: |
UPDATE Database.FUNDS_TRANSFERS
SET STATUS_CODE = COALESCE(statusCode, FUNDS_TRANSFERS.STATUS_CODE),
BANK_REF = COALESCE(bankRef, FUNDS_TRANSFERS.BANK_REF),
FAULT_CODE = COALESCE(faultCode, FUNDS_TRANSFERS.FAULT_CODE),
FAULT_REASON = faultReason,
RECONCILED_AT = CURRENT_TIME
WHERE FUNDS_TRANSFERS.ID = LocalEnvironment.Database.Input.Event.Usr.FUNDS_TRANSFER_ID;
RETURN TRUE; |
The ESQL node seems to be firing the following SQL (with Oracle at the backend)
| Code: |
SELECT STATUS_CODE, BANK_REF, FAULT_CODE, ID FROM FUNDS_TRANSFERS FOR UPDATE OF STATUS_CODE, BANK_REF, FAULT_CODE, FAULT_REASON, RECONCILED_AT
|
Does the DELETE statement of ESQL fire a SELECT for UDPATE? It does not seem to be documented anywhere.
Further, the WHERE condition is missing. This creates a nightmare !.
Is this a defect?
_________________
Regards |
|
| Back to top |
|
 |
| inMo |
| Posted: Thu Dec 10, 2015 6:19 am Post subject: |
|
|
Master
Joined: 27 Jun 2009
Posts: 216
Location: NY
|
| How did you arrive at your conclusion? |
|
| Back to top |
|
|
| akil |
| Posted: Thu Dec 10, 2015 7:02 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Hi
I could figure out the possible reason for a SELECT FOR UPDATE, & that is because of the nature of the query,
When the query is as follows, there is no SELECT FOR UPDATE fired
| Code: |
UPDATE Database.FUNDS_TRANSFERS
SET STATUS_CODE = statusCode,
BANK_REF = bankRef,
FAULT_CODE = faultCode,
FAULT_REASON = faultReason,
RECONCILED_AT = CURRENT_TIME
WHERE FUNDS_TRANSFERS.ID = LocalEnvironment.Database.Input.Event.Usr.FUNDS_TRANSFER_ID;
RETURN TRUE;
|
So i guess the SELECT FOR UPDATE is to satisfy the SET statement.
However, the defect seems to be that the SELECT FOR UPDATE has not WHERE clause!.
_________________
Regards |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Dec 10, 2015 7:10 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
I think the question was more:
"how have you determined the SQL being run on the database side?"
Oracle diagnostics? 3rd party tools? Inference from the result set being returned? Psychic powers?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| akil |
| Posted: Thu Dec 10, 2015 9:47 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
The queries come in the brokers user trace .
They are also visible in oracle's awr reports.
_________________
Regards |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Dec 10, 2015 10:00 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| akil wrote: |
The queries come in the brokers user trace .
They are also visible in oracle's awr reports. |
Seems definitive.
So apparently a defect, so your next move is a PMR.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| inMo |
| Posted: Thu Dec 10, 2015 10:08 am Post subject: |
|
|
Master
Joined: 27 Jun 2009
Posts: 216
Location: NY
|
| Have to ask - You are certain that the statement you highlighted is in fact the statement that is being executed? No chance it is the statement you think is being executed? |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Dec 10, 2015 11:37 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20768
Location: LI,NY
|
| akil wrote: |
Hi
I could figure out the possible reason for a SELECT FOR UPDATE, & that is because of the nature of the query,
When the query is as follows, there is no SELECT FOR UPDATE fired
| Code: |
UPDATE Database.FUNDS_TRANSFERS
SET STATUS_CODE = statusCode,
BANK_REF = bankRef,
FAULT_CODE = faultCode,
FAULT_REASON = faultReason,
RECONCILED_AT = CURRENT_TIME
WHERE FUNDS_TRANSFERS.ID = LocalEnvironment.Database.Input.Event.Usr.FUNDS_TRANSFER_ID;
RETURN TRUE;
|
So i guess the SELECT FOR UPDATE is to satisfy the SET statement.
However, the defect seems to be that the SELECT FOR UPDATE has not WHERE clause!. |
How did you determine that there was no WHERE CLAUSE? Could it have been because LocalEnvironment.Database.Input.Event.Usr.FUNDS_TRANSFER_ID was not populated?
What steps are you taking to avoid an empty / null where clause? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| akil |
| Posted: Thu Dec 10, 2015 9:20 pm Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
For one ESQL statement, the trace shows 2 statements
SELECT FOR UPDATE --- without a where clause
UPDATE --- with a where clause
_________________
Regards |
|
| Back to top |
|
|
| gisly |
| Posted: Fri Sep 13, 2019 2:00 am Post subject: |
|
|
Apprentice
Joined: 10 May 2012
Posts: 29
|
Hi! Sorry for reviving this post but I just wanted to say that we've come across a similar behaviour in IIB v. 10.0.0.13
| Code: |
| UPDATE DOCUMENT AS SET FIELD1=someFunction() WHERE D.FIELD2 = ? |
fires two statements:
| Code: |
| SELECT FIELD2 FROM DOCUMENT FOR UPDATE OF FIELD1 |
and the update itself
whereas
| Code: |
DECLARE myValue CHARACTER someFunction();
UPDATE DOCUMENT AS SET FIELD1=myValue WHERE D.FIELD2 = ? |
fires only one statement (the update itself).
So, the workaround is to avoid using functions in the right-hand side of the statement but I think this is definitely a bug. |
|
| Back to top |
|
|
|
|
