| Author |
Message
|
| OMG |
 Posted: Wed Apr 04, 2012 1:32 am Post subject: URGENT::Error in accessing Oracle Database From Compute Node |
 |
|
Novice
Joined: 20 Feb 2012
Posts: 18
|
Hi
I am trying to fetch a single value from a Oracle Database Table (TABLE1) with the following code in Compute Node:
| Code: |
DECLARE XXX TIMESTAMP;
SET XXX = (SELECT A.START_DATE FROM Database.SYSTEM.TABLE1 AS A WHERE A.ID = '123');
|
But it's giving error while deployment as :
Error: BIP2497E:: Illegal data type for target. A list field reference is required. The expression supplying the target must evaluate to a value of a suitable type.
Please let me know the way out to resolve the issue. |
|
| Back to top |
|
 |
| stallin |
| Posted: Wed Apr 04, 2012 2:03 am Post subject: |
|
|
Novice
Joined: 11 Jan 2009
Posts: 21
|
When you select from table there can be multiple rows. So the you need to assign the query to LIST. For example Environment.Variables.XXX[] = 'your query' and then you can get the value of the first row with Environment.Variables.XXX[1].ColumnName
Thanks,
Stallin |
|
| Back to top |
|
|
| OMG |
| Posted: Wed Apr 04, 2012 2:14 am Post subject: |
|
|
Novice
Joined: 20 Feb 2012
Posts: 18
|
| But I don't need the whole row. I need only a single column value corresponding to the row. If we run the query in any SQL editor it gives correct value but in WMB it's generating the error. |
|
| Back to top |
|
|
| Esa |
| Posted: Wed Apr 04, 2012 2:19 am Post subject: Re: URGENT::Error in accessing Oracle Database From Compute |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
You need to apply a couple of ESQL extensions. Something like this:
| Code: |
DECLARE XXX TIMESTAMP;
SET XXX = THE (SELECT ITEM A.START_DATE FROM Database.SYSTEM.TABLE1 AS A WHERE A.ID = '123');
|
|
|
| Back to top |
|
|
| OMG |
| Posted: Wed Apr 04, 2012 2:33 am Post subject: |
|
|
Novice
Joined: 20 Feb 2012
Posts: 18
|
Thanks Esa.
The deployment error has been resolved using your suggestion but now it's giving error while debugging as : "[ODBC Driver Manager] Driver does not support this function". |
|
| Back to top |
|
|
| adubya |
| Posted: Wed Apr 04, 2012 3:19 am Post subject: |
|
|
Partisan
Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK
|
| Ditch the "ITEM" keyword in the SQL. |
|
| Back to top |
|
|
| OMG |
| Posted: Wed Apr 04, 2012 4:21 am Post subject: |
|
|
Novice
Joined: 20 Feb 2012
Posts: 18
|
@adubya : then also the error prevails! 
please help!!  |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Apr 04, 2012 8:21 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| OMG wrote: |
| But I don't need the whole row. I need only a single column value corresponding to the row. If we run the query in any SQL editor it gives correct value but in WMB it's generating the error. |
Doesn't matter whether a full row or a single column. The return type is still that of []. Read up on the documentation for the select clause...
try
| Code: |
Environment.Variables.XXX[] = THE (SELECT A.START_DATE FROM Database.SYSTEM.TABLE1 AS A WHERE A.ID = '123');
SET mydatestring = Environment.Variables.XXX[1].START_DATE; |
The return from a DB select always assumes that you may return multiple rows, and multiple fields per row...
Have fun and test, test and test again 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| OMG |
| Posted: Wed Apr 04, 2012 9:34 pm Post subject: |
|
|
Novice
Joined: 20 Feb 2012
Posts: 18
|
@fjb_saper :
I have followed your suggestion but it is generating error while deployment.
The error is : Illegal data type for target. A non-list field reference is required. The expression supplying the target must evaluate to a value of a suitable type.
But if I use a simple query as "SELECT * FROM Database.SYSTEM.TABLE1" then it works fine. Please let me know what should be the syntax for using a query with WHERE clause. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Apr 04, 2012 9:44 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| OMG wrote: |
@fjb_saper :
I have followed your suggestion but it is generating error while deployment.
The error is : Illegal data type for target. A non-list field reference is required. The expression supplying the target must evaluate to a value of a suitable type.
But it I use a simple query as "SELECT * FROM Database.SYSTEM.TABLE1" then it works fine. Please let me know what should be the syntax for using a query with WHERE clause. |
Good point. The "THE" keyword in the select restricts the output to a single row... so having the ROW type instead of ROW[] would potentially be sufficient there.
I don't know if at that point it will matter whether you return a single field or multiple fields.
In case you may want to check the type of field returned and will probably have to cast it to the type you want to use in WMB.
The use of the where clause should make no difference.
Have fun
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| OMG |
| Posted: Wed Apr 04, 2012 11:29 pm Post subject: |
|
|
Novice
Joined: 20 Feb 2012
Posts: 18
|
Yesss!!! I have resolved it!!! 
@fjb_saper:
Without the "THE" keyword deployment was successful but it was failing while debugging with the error : "[ODBC Driver Manager] Driver does not support this function".
I have researched it and found that the problem was with my DSN. I forgot to check "Enable SQLDescribeParam." and after selecting it everything is going fine.
Thanks a lot everyone for the help!!!  |
|
| Back to top |
|
|
|
|
