| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Parameters are not passed to ESQL statements |
« View previous topic :: View next topic » |
| Author |
Message
|
| gteplits |
 Posted: Mon Nov 05, 2001 1:47 pm Post subject: |
 |
|
Apprentice
Joined: 16 Jul 2001
Posts: 29
|
Environmnent:
MQSIv2.0.1, CSD02
AIX4.3
UDB DB2 6.1
MQSeries 5.1, CSD04
Problem:
In computing mode following SELECT statement causes ODBC error:
SET UNIT_NAME = THE (SELECT ITEM T.UNIT_NM FROM Database.TWWCUT AS T WHERE T.CO_CD = "InputBody"."SELLER_CUID_ELE");
Same statement with explicit character value executes with no errors:
SET UNIT_NAME = THE (SELECT ITEM T.UNIT_NM FROM Database.TWWCUT AS T WHERE T.CO_CD = 'AAA');
Trying to use PASSTHRU statement does not bring any luck either:
SET UNIT_NAME = PASSTHRU ('SELECT ITEM T.UNIT_NM FROM Database.TWWCUT AS T WHERE T.CO_CD =' || InputBody.SELLER_CUID_ELE);
And finally, the same expression executes with no problems on NT/DB2 development broker....
Any suggestions are greatly appreciated, George
|
|
| Back to top |
|
 |
| gteplits |
| Posted: Fri Nov 09, 2001 8:02 am Post subject: |
|
|
Apprentice
Joined: 16 Jul 2001
Posts: 29
|
Working around original problem (for which I am opening PMR with IBM), I try PASSTHRU statement:
SET UNIT_NAME = PASSTHRU('SELECT UNIT_NM FROM TWWCUT WHERE CO_CD || UNIT_CD = ?', 'AAA')
This statement generates following error in trace log:
"BIP2556E: An attempt has been made to assign a scalar quantity to a list"
Why this error is generated?
Issuing the same SELECT from db2 command line returns character value 'AAA UNIT'.
Anybody can shed some light on this? - Thank you in advance, Gennadi.
|
|
| Back to top |
|
|
| Outdesign |
| Posted: Mon Nov 12, 2001 4:38 am Post subject: |
|
|
Apprentice
Joined: 16 Sep 2001
Posts: 38
Location: Hampshire, UK
|
George / Gennadi,
With reference to your first post :
===================================
You indicate the following SELECT statement causes an ODBC error, but you do not
supply the details of this ODBC error ?
You have not supplied enough information here.
Are you using the MRM ?
If not, the path to the element would appear to be lacking :
"InputBody"."SELLER_CUID_ELE"
If this is a generic XML message you would be referencing the root and only element
in the message (otherwise would SELLER_CUID_ELE would have child elements).
With reference to your second post :
====================================
SET UNIT_NAME = PASSTHRU('SELECT UNIT_NM FROM TWWCUT WHERE CO_CD || UNIT_CD = ?', 'AAA')
This statement generates following error in trace log:
"BIP2556E: An attempt has been made to assign a scalar quantity to a list"
The following is an invalid ESQL statement and you will get the associated error
message. This is because when you use PASSTHRU as a function (ie, with a select statement)
you will always return a list/array. In this case you are trying to assign the result,
which is a list, to a scalar (ie, UNIT_NAME).
The solution is to use the message body or DestinationList as a temporary storage area.
For example :
SET OutputDestinationList.TempData.UNIT_NAME[] =
PASSTHRU('SELECT UNIT_NM FROM TWWCUT WHERE CO_CD || UNIT_CD = ?', 'AAA')
|
|
| Back to top |
|
|
| bh |
| Posted: Mon Nov 12, 2001 8:22 am Post subject: |
|
|
Acolyte
Joined: 25 Jun 2001
Posts: 61
|
| Can we know wich database and its version you are trying to access from the select made by tour broker ? |
|
| Back to top |
|
|
| gteplits |
| Posted: Mon Nov 12, 2001 9:10 am Post subject: |
|
|
Apprentice
Joined: 16 Jul 2001
Posts: 29
|
Thank you for responses.
Extra info on original problem with parameters:
1. Yes, I am using MRM.
2. Database version is UDB DB2 6.1 with fixpack 31 on AIX.
And here is extract from MQSI trace file:
"1543 RecoverableException BIP2519E: (1, 1) : Error executing SQL statement 'SELECT T.UNIT_NM FROM TWWCUT T WHERE ({fn CONCAT(T.CO_CD,T.UNIT_CD)})=(?)' against datasource 'DB2D' with parameters ''AAA', '.
The following error occurred during execution of a database SQL statement against datasource 'DB2D'. The SQL statement was 'SELECT T.UNIT_NM FROM TWWCUT T WHERE ({fn CONCAT(T.CO_CD,T.UNIT_CD)})=(?)'. The parameters passed were ''AAA', '.
2001-11-09 09:41:57.851516 1543 DatabaseException BIP2321E: Database error: ODBC return code '-1'.
The message broker encountered an error whilst executing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database pertaining to this error.
Use the following messages to determine the cause of the error. This is likely to be such things as incorrect datasource or table names. Then correct either the database or message broker configuration.
2001-11-09 09:41:57.851554 1543 DatabaseException BIP2322E: Database error: SQL State 'IM001'; Native Error Code '0'; Error Text '[MERANT][ODBC lib] Driver does not support this function'.
The error has the following diagnostic information: SQL State 'IM001' SQL Native Error Code '0' SQL Error Text '[MERANT][ODBC lib] Driver does not support this function'
This message may be accompanied by other messages describing the effect on the message broker itself. Use the reason identified in this message with the accompanying messages to determine the cause of the error.
2001-11-09 09:41:57.905536"
2. (Regarding workaround)
I've tried to assign results of PASSTHRU function to array element before, but for some reason it returns array with one element which is NULL.
Here what I've done:
DECLARE UNIT_NAME CHAR;
SET OutputDestinationList.TempData.UNIT_NAME[] = PASSTHRU('SELECT UNIT_NM FROM TWWCUT WHERE CO_CD || UNIT_CD = ''AAA'' ');
SET UNIT_NAME = OutputDestinationList.TempData.UNIT_NAME[1] ;
IF UNIT_NAME IS NULL THEN
SET UNIT_NAME = 'NULL value';
ELSE
END IF;
Thanks again for you time, Gennadi(George).
|
|
| Back to top |
|
|
| ep |
| Posted: Mon Nov 18, 2002 8:15 am Post subject: |
|
|
Newbie
Joined: 18 Nov 2002
Posts: 1
|
I am doing some similar coding and am also getting a NULL returned.
Have you been able to get your PASSTHRU to work?
Thanks! |
|
| 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
|
|
|
|
