| Author |
Message
|
| msilva18 |
 Posted: Fri Oct 08, 2004 7:51 am Post subject: PASSTHRU Statement |
 |
|
Apprentice
Joined: 25 Aug 2004
Posts: 31
|
Hi all!!!
I have a problem, I need to extract information from database and send the records like a Message to a queue, one row per message. I'm using the PASSTHRU statement but it doesnt work, i dont know if I need something special, or if there is another way to do this. Someone did it before?
The message that i need to send to queue:
<TEF_RECE>
<HEADER>
<SERVICIO>TEF_RECE</SERVICIO>
<USUARIO>Manuel</USUARIO>
<COD_RET></COD_RET>
<DESC_ERROR></DESC_ERROR>
<MSG_ERROR></MSG_ERROR>
<RESERVADO></RESERVADO>
</HEADER>
<BODY>
<TIPO_DE_REGISTRO>34</TIPO_DE_REGISTRO>
<NUMERO_DE_SECUENCIA>234</NUMERO_DE_SECUENCIA>
<CODIGO_DE_OPERACION>54</CODIGO_DE_OPERACION>
<CODIGO_DE_DIVISA>98</CODIGO_DE_DIVISA>
<FECHA_DE_TRANSFERENCIA></FECHA_DE_TRANSFERENCIA>
<BANCO_PRESENTADOR>01</BANCO_PRESENTADOR>
<BANCO_RECEPTOR>02</BANCO_RECEPTOR>
<IMPORTE_OPERACION>10000</IMPORTE_OPERACION>
<USO_FUTURO_CCE>FUTURO</USO_FUTURO_CCE>
<TIPO_DE_OPERACION>38</TIPO_DE_OPERACION>
<FECHA_DE_APLICACION>20041001</FECHA_DE_APLICACION>
<TIPO_CUENTA_ORDENANTE>38</TIPO_CUENTA_ORDENANTE>
<NUMERO_CTA_ORDENANTE>2389</NUMERO_CTA_ORDENANTE>
<NOMBRE_DEL_ORDENANTE></NOMBRE_DEL_ORDENANTE>
<RFC_O_CURP_ORDENANTE></RFC_O_CURP_ORDENANTE>
<TIPO_CUENTA_RECEPTOR>34</TIPO_CUENTA_RECEPTOR>
<NUMERO_CUENTA_RECEPTOR>487</NUMERO_CUENTA_RECEPTOR>
<NOMBRE_RECEPTOR>NOMBRE RECEPTOR</NOMBRE_RECEPTOR>
<RFC_O_CURP_RECEPTOR></RFC_O_CURP_RECEPTOR>
<REF_SERVICIO_EMISOR></REF_SERVICIO_EMISOR>
<NOMBRE_TIT_SERVICIO>TEF_RECE</NOMBRE_TIT_SERVICIO>
<IMPORTE_IVA_OPERACION>1500</IMPORTE_IVA_OPERACION>
<REF_NUMERICA_ORDENANTE>543</REF_NUMERICA_ORDENANTE>
<REF_LEYENDA_ORDENANTE></REF_LEYENDA_ORDENANTE>
<CLAVE_DE_RASTREO>CLAVE RASTREO</CLAVE_DE_RASTREO>
<MOTIVO_DE_DEVOLUCION>78</MOTIVO_DE_DEVOLUCION>
<FECHA_PRESENTACION_INI>230</FECHA_PRESENTACION_INI>
<SOLICITUD_CONFIRMACION>S</SOLICITUD_CONFIRMACION>
<USO_FUTURO_ZONA_BANCO></USO_FUTURO_ZONA_BANCO>
<C_ARCHIVO>1</C_ARCHIVO>
</BODY>
</TEF_RECE>
And my source code:
CREATE PROCEDURE SelectBD() BEGIN
DECLARE Extract REFERENCE TO InputRoot;
DECLARE i INTEGER;
SET i = 1;
SET Extract.XML.BODY[] =
PASSTHRU('SELECT * FROM Database.Table WHERE TIPO_DE_REGISTRO = 34');
WHILE i <= CARDINALITY(Extract.XML."BODY"*) DO
SET OutputRoot = InputRoot;
SET OutputRoot.XML = NULL;
SET OutputRoot.XML."BODY" = Extract.XML."BODY"[i];
PROPAGATE;
SET i = i + 1;
END WHILE;
END;
Thanks a lot.
Regards.
_________________
Ing. Manuel Silva Zaldivar |
|
| Back to top |
|
 |
| Nizam |
| Posted: Fri Oct 08, 2004 8:42 am Post subject: |
|
|
Disciple
Joined: 10 Feb 2004
Posts: 160
|
I think you cannot modify the inputroot, instead copy your inputroot to outputroot and then add the data returned by your passthru to your outputroot's xml tree.
Can you post what is the output and erros if any you are seeing...
Asbitstream may be helpful to copy the xml to your xml tree... |
|
| Back to top |
|
|
| msilva18 |
| Posted: Fri Oct 08, 2004 10:36 am Post subject: |
|
|
Apprentice
Joined: 25 Aug 2004
Posts: 31
|
Hi Nizam:
Im trying in many ways, and the only way that i can do it is:
CREATE PROCEDURE ExtraeBD() BEGIN
DECLARE Extraccion REFERENCE TO OutputRoot;
DECLARE i INTEGER;
SET i = 1;
SET Extraccion.XML.BODY = NULL;
SET Extraccion.XML.BODY[] = (SELECT I.* FROM Database.DD_ABONO_CCE AS I WHERE I.TIPO_DE_REGISTRO = 34);
WHILE i <= CARDINALITY(Extraccion.XML."BODY"[]) DO
SET OutputRoot = InputRoot;
SET OutputRoot.XML.BODY = NULL;
SET OutputRoot.XML."BODY" = Extraccion.XML."BODY"[i];
PROPAGATE;
SET i = i +1;
END WHILE;
END;
In this example I have only one problem, I can extract from Database all records but when the flow try to send the second record make an exception error. Do you know if I need some configuration or something to make the flow returns x times depends the number of records obtained from database?
Thanks.
Regards.
_________________
Ing. Manuel Silva Zaldivar |
|
| Back to top |
|
|
| mayur2378 |
| Posted: Fri Oct 08, 2004 11:23 am Post subject: |
|
|
Apprentice
Joined: 26 May 2004
Posts: 47
|
WHat have u set your RETURN type to ???
couldnt see that in the code that you have provided...
Mayur |
|
| Back to top |
|
|
| Nizam |
| Posted: Fri Oct 08, 2004 11:33 am Post subject: |
|
|
Disciple
Joined: 10 Feb 2004
Posts: 160
|
msilva18,
Instead of using outputRoot and its reference try using environment. Create the xml tree in environment and assign the part of xml to outputroot and propagate.
SET Environment.XML.BODY[] = (SELECT I.* FROM Database.DD_ABONO_CCE AS I WHERE I.TIPO_DE_REGISTRO = 34);
also check issues with propagate.
mayur2378,
If you are talking abou the return type of procedure...
Procedures do not return unlike functions.. |
|
| Back to top |
|
|
| JT |
| Posted: Fri Oct 08, 2004 2:17 pm Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
msilva18,
Also, to improve upon performance the following statement:
| Code: |
| WHILE i <= CARDINALITY(Extraccion.XML."BODY"[]) DO |
..should be coded like this:
| Code: |
DECLARE c INTEGER CARDINALITY(Extraccion.XML."BODY"[]);
WHILE i <= c DO |
This will reduce the number of times the value has to be interpreted and resolved. |
|
| Back to top |
|
|
|
|
