| Author |
Message
|
| mona |
 Posted: Wed Jul 25, 2012 2:00 am Post subject: Receive data from DB |
 |
|
Novice
Joined: 14 Mar 2012
Posts: 19
|
Hi
How can i receive data from Database(oracle) and Save in variable, for example
| Code: |
Set ?? = (SELECT D.Name FROM Database.table AS D WHERE D.ID = '10');
|
|
|
| Back to top |
|
 |
| Vitor |
| Posted: Wed Jul 25, 2012 2:57 am Post subject: Re: Receive data from DB |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mona wrote: |
How can i receive data from Database(oracle) and Save in variable, for example
| Code: |
Set ?? = (SELECT D.Name FROM Database.table AS D WHERE D.ID = '10');
|
|
By putting that code (with some specific DB names) in a Compute node with a data source associated with it. Seriously. That's it.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| akidase |
| Posted: Wed Jul 25, 2012 3:04 am Post subject: |
|
|
Centurion
Joined: 10 Jan 2011
Posts: 124
|
| Code: |
| DECLARE Name CHARACTER THE(SELECT ITEM D.Name FROM Database.table AS D WHERE D.ID = 10); |
You could try with this. |
|
| Back to top |
|
|
| mona |
| Posted: Wed Jul 25, 2012 3:20 am Post subject: |
|
|
Novice
Joined: 14 Mar 2012
Posts: 19
|
Thanks
I use these codes
| Code: |
DECLARE M CHARACTER;
SET M = (SELECT D.Name FROM Database.table AS D WHERE D.ID = '10');
|
also
| Code: |
SET OutputLocalEnvironment.Destination.MQDestinationList.DestinationData[] = (SELECT D.Name FROM Database.table AS D WHERE D.ID = '10');
|
But i can not receive data. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Jul 25, 2012 4:24 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mona wrote: |
| But i can not receive data. |
We're going to need a few more specifics here. Do you get an error? An empty result set? A note of protest from the WMB demanding extra pay for using a database?
What does the user trace say when that statement is executed?
Have you verified the data source with mqsicvp?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Jul 25, 2012 6:01 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| mona wrote: |
Thanks
I use these codes
| Code: |
DECLARE M CHARACTER;
SET M = (SELECT D.Name FROM Database.table AS D WHERE D.ID = '10');
|
also
| Code: |
SET OutputLocalEnvironment.Destination.MQDestinationList.DestinationData[] = (SELECT D.Name FROM Database.table AS D WHERE D.ID = '10');
|
But i can not receive data. |
Change that return value declaration.
Make the return type a row not char...
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| mona |
| Posted: Wed Jul 25, 2012 11:15 am Post subject: |
|
|
Novice
Joined: 14 Mar 2012
Posts: 19
|
Thank you for all friends, I use above code and also ROW type but i get following error
| Code: |
BIP2488E: ('.fetch_from_db.Main', '8.3') Error detected whilst executing the SQL statement ''DECLARE Name CHARACTER THE (SELECT ITEM COLUMN(0) FROM DATABASE());''.
The message broker 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.
BIP2321E: Database error: ODBC return code '-1' using ODBC driver manager ''odbc32.dll''.
The message broker encountered an error when processing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database concerning this error.
Use the following messages to determine the cause of the error. Typical problems are an incorrect datasource or table names. Correct either the database or message broker configuration.
BIP2322E: Database error: SQL State ''HYC00''; Native Error Code '0'; Error Text ''[IBM][ODBC Oracle Wire Protocol driver]Optional feature not implemented.''.
The error has the following diagnostic information: SQL State ''HYC00'' SQL Native Error Code '0' SQL Error Text ''[IBM][ODBC Oracle Wire Protocol driver]Optional feature not implemented.''
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.
|
|
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Jul 25, 2012 11:23 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Vitor wrote: |
| Have you verified the data source with mqsicvp? |
the answer to this is obviously NO.
You have not configured the DSN correctly. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Jul 25, 2012 11:27 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Your select statement is not aligning right with the database. Try a more simple select statement such as the one fjb_saper suggested. Use explicit column names.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Jul 25, 2012 11:29 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| lancelotlinc wrote: |
| Your select statement is not aligning right with the database. Try a more simple select statement such as the one fjb_saper suggested. Use explicit column names. |
That's not going to change the fact that the DSN is not configured correctly. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Jul 25, 2012 11:38 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Jul 25, 2012 11:52 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| lancelotlinc wrote: |
| True. I was assuming mqsicvp was operated successfully. |
And the error message shows explicitly that it could not have been. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Jul 25, 2012 12:46 pm Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
That's not crystal clear to me, as I could see this error for other causes. But I defer to your experience on the matter since I 'outsource' my database operations to SOAP nodes. 
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Jul 25, 2012 12:48 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
It's an oracle datasource.
Optional feature not installed means that the wrong things are specified in the DSN config. |
|
| Back to top |
|
|
| mona |
| Posted: Thu Jul 26, 2012 10:30 am Post subject: |
|
|
Novice
Joined: 14 Mar 2012
Posts: 19
|
Thank's a lot
after run mqsicvp , i get success
also , i can insert data to Database |
|
| Back to top |
|
|
|
|
