| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Return code from PASSTHRU statement |
« View previous topic :: View next topic » |
| Author |
Message
|
| petervh1 |
 Posted: Thu Jun 02, 2022 5:39 am Post subject: Return code from PASSTHRU statement |
 |
|
Centurion
Joined: 19 Apr 2010
Posts: 122
|
Hi
I'm using a PASSTHRU statement to delete all rows from a MS SQL server DB.
My code is:
| Code: |
| PASSTHRU 'DELETE FROM Integration.dbo.RegManagerMap'; |
I understand that the statement is literally passed through to the DB server, but I want to know if I can interrogate a return code to check whether the statement was executed successfully by the backend DB.
My trace shows:
| Code: |
2022-06-02 15:20:58.611652 19589 UserTrace BIP2537I: Node 'TestRegionalManagerUpdate.Compute': Executing statement 'DECLARE sql CHARACTER 'DELETE FROM Integration.dbo.RegManagerMap';' at (.TestRegionalManagerUpdate_Compute.Main, 6.3).
2022-06-02 15:20:59.488496 19589 UserTrace BIP2537I: Node 'TestRegionalManagerUpdate.Compute': Executing statement ''DELETE FROM Integration.dbo.RegManagerMap';' at (.TestRegionalManagerUpdate_Compute.Main, 9.3).
2022-06-02 15:20:59.488580 19589 UserTrace BIP2544I: Node 'TestRegionalManagerUpdate.Compute': Executing database SQL statement 'DELETE FROM Integration.dbo.RegManagerMap' derived from (.TestRegionalManagerUpdate_Compute.Main, 9.3); expressions ''; resulting parameter values ''.
2022-06-02 15:20:59.488680 19589 UserTrace BIP12080I: Preparing a database statement for 'IIB_EVENTS'.
Preparing a database statement against data source 'IIB_EVENTS'.
2022-06-02 15:20:59.488912 19589 UserTrace BIP12081I: Prepared a database statement for 'IIB_EVENTS'.
Prepared a database statement against data source 'IIB_EVENTS'.
2022-06-02 15:20:59.488932 19589 UserTrace BIP12074I: Executing a database statement for 'IIB_EVENTS'.
Executing a database statement against data source 'IIB_EVENTS'.
2022-06-02 15:20:59.650208 19589 UserTrace BIP12075I: Executed a database statement for 'IIB_EVENTS'.
Executed a database statement against data source 'IIB_EVENTS'. |
From checking the DB via my client tool, it appears that the DELETE FROM has worked successfully, but I want to check this in ESQL.
Is there any way to see SQL Error or Return codes?
Thanks |
|
| Back to top |
|
 |
| mgk |
| Posted: Thu Jun 02, 2022 11:17 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1638
|
| Quote: |
| Is there any way to see SQL Error or Return codes? |
Yes, if the "Throw exception on database error" node option is NOT set you can simple use the DB State Functions to get the SQLCODE, SQLSTATE, SQLNATIVEERROR and SQLERRORTEXT, see: https://www.ibm.com/docs/en/app-connect/12.0?topic=functions-esql-database-state
If the "Throw exception on database error" node option IS set you can either allow the default exception handling to happen or you can use a DECLARE [CONTINUE | EXIT] HANDLER block and handle all errors in the block yourself - see: https://www.ibm.com/docs/en/app-connect/12.0?topic=statements-declare-handler-statement
This is all the same in versions 7, 8, 9, 10, 11 and 12.
Hope that helps...
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| petervh1 |
| Posted: Fri Jun 03, 2022 3:45 am Post subject: |
|
|
Centurion
Joined: 19 Apr 2010
Posts: 122
|
Thanks, mgk.
I removed the tick from "Throw exception on database error".
The same code now fails with the following showing in the debug:
| Code: |
RecoverableException
File:CHARACTER:/build/jenkins_swg/slot0/product-build/WMB/src/Credentials/ImbCredentialsManager.cpp
Line:INTEGER:534
Function:CHARACTER:ImbCredentialsManager::lookupCredentialInternal
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2112
Text:CHARACTER:Unable to search for empty name
Insert
Type:INTEGER:5
Text:CHARACTER:Unable to search for empty name
Insert
Type:INTEGER:5
Text:CHARACTER:odbc |
The user trace shows:
| Code: |
2022-06-03 13:37:26.435884 13348 UserTrace BIP2539I: Node '': Evaluating expression 'sql' at (.TestDivisionalManagerUpdate_Compute.Main, 8.13). This resolved to 'sql'. The result was ''DELETE FROM Integration.dbo.DivManagerMap''.
2022-06-03 13:37:26.436238 13348 UserTrace BIP11305I: The Parser of type 'WSRoot' at address '0x7fd45c195420' has been reset ready for re-use. This thread has '26' cached parsers.
2022-06-03 13:37:26.436262 13348 UserTrace BIP11305I: The Parser of type 'MQROOT' at address '0x7fd45c196bc0' has been reset ready for re-use. This thread has '26' cached parsers.
2022-06-03 13:37:26.436272 13348 UserTrace BIP11305I: The Parser of type 'MQROOT' at address '0x7fd45c0e83e0' has been reset ready for re-use. This thread has '26' cached parsers.
2022-06-03 13:37:26.436282 13348 UserTrace BIP11305I: The Parser of type 'Properties' at address '0x7fd45c17e610' has been reset ready for re-use. This thread has '26' cached parsers.
2022-06-03 13:37:26.436388 13348 UserTrace BIP11303I: A Parser of type 'WSPROPERTYPARSER' has been created at address '0x7fd45c183690'. This thread now has '27' cached parsers.
2022-06-03 13:37:26.436412 13348 UserTrace BIP11303I: A Parser of type 'HTTPInputHeader' has been created at address '0x7fd45c169d10'. This thread now has '28' cached parsers.
.
2022-06-03 13:37:32.523434 13348 UserTrace BIP3120E: Exception condition detected on input node 'TestDivisionalManagerUpdate.HTTP Input'.
The input node 'TestDivisionalManagerUpdate.HTTP Input' detected an error whilst processing a message. The message flow has been rolled-back and a fault message returned to the requesting client. Following messages will indicate the cause of this exception.
Check the error messages which follow to determine why the exception was generated, and take action as described by those messages.
2022-06-03 13:37:32.523480 13348 UserTrace BIP2230E: Error detected whilst processing a message in node 'TestDivisionalManagerUpdate.Compute'.
The integration node detected an error whilst processing a message in node 'TestDivisionalManagerUpdate.Compute'. An exception has been thrown to cut short the processing of the message.
See the following messages for details of the error.
2022-06-03 13:37:32.523502 13348 UserTrace BIP2488E: (.TestDivisionalManagerUpdate_Compute.Main, 8.3) Error detected whilst executing the SQL statement 'sql;'.
The integration node detected an error whilst executing the given statement. An exception has been thrown to cut short the SQL program.
See the following messages for details of the error.
2022-06-03 13:37:32.523514 13348 UserTrace BIP2112E: IBM App Connect Enterprise internal error: diagnostic information 'Unable to search for empty name', 'odbc'.
An internal software error has occurred in IBM App Connect Enterprise. Further messages may indicate the effect of this error on the component.
Shutdown and restart the component. If the problem continues to occur, then restart the system. If the problem still continues to occur contact your IBM support center.
2022-06-03 13:37:32.523560 13348 UserTrace BIP11507W: Rolled back a local transaction.
A local transaction has been rolled back for work done on the message flow thread. |
I'm using:
| Code: |
=== Build ib000-L210923.1848 (S000-L210922.10947) - IBM App Connect Enterprise 12.0.2.0 ===
=== Platform Linux / x86_64 / 5.3.18-24.83-default === |
|
|
| Back to top |
|
|
| mgk |
| Posted: Fri Jun 03, 2022 1:21 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1638
|
Hi,
Are you sure that unchecking the "Throw exception on database error" option was the only change you made? The first usertrace you posted was from the node:
| Code: |
| TestRegionalManagerUpdate_Compute.Main |
but the second one was from a different node:
| Code: |
| TestDivisionalManagerUpdate.Compute |
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| petervh1 |
| Posted: Sat Jun 04, 2022 6:37 am Post subject: |
|
|
Centurion
Joined: 19 Apr 2010
Posts: 122
|
You are correct. The two flows are part of the same application.
The behaviour I quoted occurs in both flows. |
|
| Back to top |
|
|
| abhi_thri |
| Posted: Tue Jun 07, 2022 2:56 am Post subject: |
|
|
Knight
Joined: 17 Jul 2017
Posts: 516
Location: UK
|
| hi...As @mgk alluded to, crosscheck whether 'Throw exception on database error' is unchecked for all compute nodes which runs database queries. This should allow to check from ESQL (using SQLCODE, SQLSTATE, SQLNATIVEERROR and SQLERRORTEXT) the status of the sql query. |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Jun 07, 2022 6:07 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1638
|
So whilst I agree with @abhi_thri the error looks like it is failing when trying to get the connection details to make the connection, so it may not help in this case. You could also try running
| Code: |
| mqsicvp INODE -n MyDB |
for each DSN you use in the flow to make sure the credentials are correct (see https://www.ibm.com/docs/en/app-connect/12.0?topic=commands-mqsicvp-command).
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
