| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Informix-Cannot EXECUTE a statement that has been DECLAREd. |
« View previous topic :: View next topic » |
| Author |
Message
|
| Gama |
 Posted: Wed Apr 22, 2009 1:21 am Post subject: Informix-Cannot EXECUTE a statement that has been DECLAREd. |
 |
|
Centurion
Joined: 11 Jan 2005
Posts: 103
Location: Portugal
|
Hello all,
When I do the follow select to the Informix DB using the Client-SDK 3.5 TC3, under the WMB6.0.0.8.
select distinct fa.t_nomf, fa.t_nomf2, fa.t_endf, fa.t_endf2, fa.t_cpf, fa.t_locf, pa1.t_dsca t_dsca1, fa.t_ncf, fa.t_suno, fa.t_motr, t_lopd, fo.t_crcc, fa.t_isup, fa.t_invd, fa.t_dued, fa.t_nocp, fa.t_encp, fa.t_cpcp, t_locp, pa2.t_dsca t_dsca2, fa.t_ncmp, fa.t_ttyp, fa.t_docn, fa.t_oip1, fa.t_oip2, fa.t_totd, fa.t_csid, fa.t_tid, fa.t_ivacod1, fa.t_ivades1, fa.t_ivacod2, fa.t_ivades2, fa.t_ivacod3, fa.t_ivades3, fa.t_ivacod4, fa.t_ivades4, fa.t_ccur, fa.t_pvat1, fa.t_base1, fa.t_vat1, fa.t_totl1, fa.t_pvat2, fa.t_base2, fa.t_vat2, fa.t_totl2, fa.t_pvat3, fa.t_base3, fa.t_vat3, fa.t_totl3, fa.t_ntam, fa.t_viva, fa.t_vimp, fa.t_tirp, fa.t_vipr, fa.t_amnt
from ttrcfr005800 as af
inner join ttrcfr004800 as fa on (af.t_ncmp = fa.t_ncmp and af.t_suno = fa.t_suno and af.t_isup = fa.t_isup and af.t_invd = fa.t_invd)
left join ttcmcs010200 as pa1 on (fa.t_paif = pa1.t_ccty)
inner join ttccom020200 as fo on (fa.t_suno = fo.t_suno)
left join ttcmcs010200 as pa2 on (fa.t_pacp = pa2.t_ccty)
where af.t_ncmp = 800 and af.t_suno = ' 1002' and af.t_isup = '1002-109'
I'm sure that the statement is correct, because with the sql editor works fine.
| Code: |
I return that error:
-765
Cannot EXECUTE a statement that has been DECLAREd.
You cannot execute a prepared statement that has been declared. However, to specify output variables for a prepared singleton SELECT statement, use EXECUTE INTO (or EXECUTE PROCEDURE INTO) instead of executing DECLARE, OPEN, and FETCH statements. |
But if I make my select only with the bold part of the select that work’s ok.
Any suggestion?
Thanks
Gama |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Wed Apr 22, 2009 3:39 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
You have shown an SQL select statement.
What mechanism are you using to execute that statement? Are you recoding it into an ESQL select? Are you passing it to a PASSTHRU statement? Are you executing it from within a Java Compute node? Have you coded it into a Mapping node, or a Database* node? |
|
| Back to top |
|
|
| Gama |
| Posted: Wed Apr 22, 2009 3:45 am Post subject: |
|
|
Centurion
Joined: 11 Jan 2005
Posts: 103
Location: Portugal
|
Thanks mqjeff,
I'm executing the statement within a Compute Node with PASSTHRU
Best regards |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Apr 22, 2009 3:48 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
You're sure that the exact same query works correctly when run against the database using exactly the same user that Broker uses to talk to this database?
Any issues with statements under passthru SHOULD be isolated only to the database itself and not caused by Broker at all... |
|
| Back to top |
|
|
| Gama |
| Posted: Wed Apr 22, 2009 5:11 am Post subject: |
|
|
Centurion
Joined: 11 Jan 2005
Posts: 103
Location: Portugal
|
Yes mqjeff I'm sure, it work perfectly on SQL editor (WinSQL) that using the same Informix version driver.
| mqjeff wrote: |
You're sure that the exact same query works correctly when run against the database using exactly the same user that Broker uses to talk to this database?
Any issues with statements under passthru SHOULD be isolated only to the database itself and not caused by Broker at all... |
To work around this problem I split the original statment in two and after join the result of them.
Statment 1
| Code: |
select distinct fa.t_nomf, fa.t_nomf2, fa.t_endf, fa.t_endf2, fa.t_cpf, fa.t_locf, pa1.t_dsca t_dsca1, fa.t_ncf, fa.t_suno, fa.t_motr, t_lopd, fo.t_crcc, fa.t_isup, fa.t_invd, fa.t_dued, fa.t_nocp, fa.t_encp, fa.t_cpcp, t_locp, pa2.t_dsca t_dsca2, fa.t_ncmp, fa.t_ttyp, fa.t_docn, fa.t_oip1, fa.t_oip2, fa.t_totd, fa.t_csid, fa.t_tid from ttrcfr005800 as af
inner join ttrcfr004800 as fa on (af.t_ncmp = fa.t_ncmp and af.t_suno = fa.t_suno and af.t_isup = fa.t_isup and af.t_invd = fa.t_invd)
left join ttcmcs010200 as pa1 on (fa.t_paif = pa1.t_ccty)
inner join ttccom020200 as fo on (fa.t_suno = fo.t_suno)
left join ttcmcs010200 as pa2 on (fa.t_pacp = pa2.t_ccty)
where af.t_ncmp = 800 and af.t_suno = ' 1002' and af.t_isup = '1002-109' |
Statment 2
| Code: |
select distinct fa.t_ivacod1, fa.t_ivades1, fa.t_ivacod2, fa.t_ivades2, fa.t_ivacod3, fa.t_ivades3, fa.t_ivacod4, fa.t_ivades4, fa.t_ccur, fa.t_pvat1, fa.t_base1, fa.t_vat1, fa.t_totl1, fa.t_pvat2, fa.t_base2, fa.t_vat2, fa.t_totl2, fa.t_pvat3, fa.t_base3, fa.t_vat3, fa.t_totl3, fa.t_ntam, fa.t_viva, fa.t_vimp, fa.t_tirp, fa.t_vipr, fa.t_amnt
from ttrcfr005800 as af
inner join ttrcfr004800 as fa on (af.t_ncmp = fa.t_ncmp and af.t_suno = fa.t_suno and af.t_isup = fa.t_isup and af.t_invd = fa.t_invd)
left join ttcmcs010200 as pa1 on (fa.t_paif = pa1.t_ccty)
inner join ttccom020200 as fo on (fa.t_suno = fo.t_suno)
left join ttcmcs010200 as pa2 on (fa.t_pacp = pa2.t_ccty)
where af.t_ncmp = 800 and af.t_suno = ' 1002' and af.t_isup = '1002-109' |
In this case work's ok, but I like to understand where is the problem.
Thanks one more time. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Apr 22, 2009 6:11 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
yep. That's kind of odd...
I've not worked with Informix, so I can't really say. I'd suggest looking at an ODBC trace, either on the Informix side or the Broker side to see what's going on. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Apr 22, 2009 8:18 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Looking at the way SAP used to have it's clustered tables, could it be that Informix does not let you select for extract more than x fields and that what you are hitting is a field number limitation? It would certainly explain why it works when you break it into 2 queries.... Have fun 
_________________
MQ & Broker admin |
|
| 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
|
|
|
|
