This is a multi-part message in MIME format.
------=_NextPart_000_00BD_01C9CCDC.FD8E90A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi everybody, i have migrated one vb6 app from mssql2000 to postgresql =
8.3, my database have a lot of stored functions.. i use refcursor type =
to return data from it. when i use the oledb driver it's ok.. well with =
few records ok.. but with thousands its very slow.. so i try the odbc =
driver.. and it works fine, but, not recognize my refcursor type only =
record type. With refcursor type I only receive the name of my =
refcursor. what is the solution to fetch the records in my refcursor.
I use tha next vb6 code for example
Conn.BeginTrans
Cmd.ActiveConnection =3D Conn
Cmd.CommandText =3D "select
kardexf('017033','01/04/2009','30/04/2009','data');FETCH ALL IN data;"
Set Rst =3D Cmd.Execute
Do Until Rst.EOF
Me.List1.AddItem Rst.Fields(0) 'Rst!Movimiento
Rst.MoveNext
Loop
Rst.Close
Conn.CommitTrans
CREATE OR REPLACE FUNCTION "public"."dvalss_kardexf" (vcodalm char, =
vcodprod char, vfechaini timestamp, vfechafin timestamp, out data =
"pg_catalog"."refcursor") RETURNS "pg_catalog"."refcursor" AS
$body$
DECLARE
--data refcursor;
BEGIN
OPEN data FOR
SELECT * FROM cromoadmin.CentroCto CC RIGHT JOIN
(cromoadmin.DetValesAlm Det INNER JOIN cromoadmin.Vales Cab =
ON
DEt.Sucr_codigo =3D cab.Sucr_codigo and
Det.VALE_NumVale =3D Cab.VALE_NumVale AND
Det.ALMA_Codigo =3D Cab.ALMA_Codigo AND
Det.TIPO_TabVale =3D Cab.TIPO_TabVale AND
Det.TIPO_CodVale =3D Cab.TIPO_CodVale)
ON CC.Cent_cod=3DCab.CENT_Cod
INNER JOIN cromoadmin.Catalogos ON
Det.SUCR_Codigo =3D Catalogos.SUCR_Codigo AND
Det.PROD_CodProd =3D Catalogos.PROD_CodProd AND
Det.ALMA_Codigo =3D Catalogos.ALMA_Codigo INNER JOIN =
cromoadmin.Tipos Tip ON
CAb.TIPO_TabVale =3D Tip.TIPO_CodTabla AND
Cab.TIPO_CodVale =3D Tip.TIPO_CodTipo
LEFT OUTER JOIN cromoadmin.CtaCtePRov CCP on ccp.ccpr_nroTran =
=3D det.ccpr_nroTran and
ccp.SUCR_Codigo =3D det.SUCR_Codigo
WHERE Det.ALMA_Codigo =3D vCodAlm And
Det.PROD_CodProd =3D vCodProd And
Cab.VALE_FecEmi BetWeen vFechaIni AND vFechaFin And
Cab.VALE_Estado <>'X' ORDER BY Cab.VALE_FecEmi, =
Cab.VALE_Efecto, Cab.TIPO_CodVale Desc;
RETURN;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
Thanks a lot.
---
Jose J. Ayala Pineda (a.k.a. 1nk [at] H [at] ck)
MSN Messenger: jjap [at] hotmail.com
URL/Blog: http://inkahack.blogspot.com
"Si t=FA tienes una manzana y yo tengo una manzana y las intercambiamos, =
entonces ambos a=FAn tendremos una manzana. Pero si t=FA tienes una idea =
y yo tengo una idea y las intercambiamos, entonces ambos tendremos dos =
ideas".
George Bernard Shaw
------=_NextPart_000_00BD_01C9CCDC.FD8E90A0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.6000.16825" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi everybody, i have migrated one vb6 =
app from
mssql2000 to postgresql 8.3, my database have a lot of stored =
functions.. i use
refcursor type to return data from it. when i use the oledb driver it's =
ok..
well with few records ok.. but with thousands its very slow.. so i try =
the odbc
driver.. and it works fine, but, not recognize my refcursor type only =
record
type. With refcursor type I only receive the name of my refcursor. what =
is the
solution to fetch the records in my refcursor.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>I use tha next vb6 code for =
example</FONT></DIV>
<DIV><FONT face=3DArial size=3D2><FONT face=3D"Times New Roman"
size=3D3> <BR> Conn.BeginTrans<BR> Cmd.ActiveConnect=
ion =3D
Conn<BR> Cmd.CommandText =3D "select
<BR>kardexf('017033','01/04/2009','30/04/2009','data');FETCH ALL IN
data;"<BR> Set Rst =3D Cmd.Execute<BR> Do Until =
Rst.EOF<BR>
Me.List1.AddItem Rst.Fields(0)
'Rst!Movimiento<BR> Rst.MoveNext<BR> Loop<BR> Rst.Close<BR=
> Conn.Comm</FONT></FONT><FONT
face=3DArial size=3D2><FONT face=3D"Times New Roman"
size=3D3>itTrans<BR></FONT></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><FONT face=3D"Times New Roman" =
size=3D3><FONT
face=3DArial size=3D2></FONT></FONT></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>CREATE OR REPLACE FUNCTION
"public"."dvalss_kardexf" (vcodalm char, vcodprod char, vfechaini =
timestamp,
vfechafin timestamp, out data "pg_catalog"."refcursor") RETURNS
"pg_catalog"."refcursor" AS<BR>$body$<BR>DECLARE<BR> --data
refcursor;<BR>BEGIN<BR> OPEN data FOR <BR> =
SELECT * FROM cromoadmin.CentroCto CC RIGHT
JOIN<BR> =
(cromoadmin.DetValesAlm
Det INNER JOIN cromoadmin.Vales Cab ON
<BR>
DEt.Sucr_codigo =3D cab.Sucr_codigo and
<BR>
Det.VALE_NumVale =3D Cab.VALE_NumVale AND
<BR>
Det.ALMA_Codigo =3D Cab.ALMA_Codigo AND
<BR>
Det.TIPO_TabVale =3D Cab.TIPO_TabVale
AND
<BR>
Det.TIPO_CodVale =3D
Cab.TIPO_CodVale)<BR> &nbs=
p;
ON CC.Cent_cod=3DCab.CENT_Cod
<BR> INNER
JOIN cromoadmin.Catalogos ON
<BR>
Det.SUCR_Codigo =3D Catalogos.SUCR_Codigo
AND<BR> =
Det.PROD_CodProd =3D Catalogos.PROD_CodProd AND
<BR> &nb=
sp;
Det.ALMA_Codigo =3D Catalogos.ALMA_Codigo INNER JOIN
cromoadmin.Tipos Tip ON
<BR> &nb=
sp;
CAb.TIPO_TabVale =3D Tip.TIPO_CodTabla AND
<BR> &nb=
sp;
Cab.TIPO_CodVale =3D Tip.TIPO_CodTipo
<BR> LEFT OUTER JOIN
cromoadmin.CtaCtePRov CCP on ccp.ccpr_nroTran =3D det.ccpr_nroTran and
<BR> &nb=
sp;
ccp.SUCR_Codigo =3D =
det.SUCR_Codigo<BR>
WHERE Det.ALMA_Codigo =3D vCodAlm
And<BR> Det.PROD_CodProd =
=3D
vCodProd And<BR> =
Cab.VALE_FecEmi
BetWeen vFechaIni AND vFechaFin
And<BR> Cab.VALE_Estado
<>'X' ORDER BY Cab.VALE_FecEmi, Cab.VALE_Efecto, =
Cab.TIPO_CodVale
Desc;<BR> RETURN;<BR>END;<BR>$body$<BR>LANGUAGE
'plpgsql'<BR>VOLATILE<BR>CALLED ON NULL INPUT<BR>SECURITY =
INVOKER<BR>COST
100;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2><FONT face=3D"Times New Roman" =
size=3D3><FONT
face=3DArial size=3D2></FONT></FONT></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><FONT face=3D"Times New Roman" =
size=3D3><FONT
face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT><BR>Thanks a =
lot.</FONT></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><FONT face=3DArial =
size=3D2></FONT></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>---<BR>Jose J. Ayala Pineda (a.k.a. <A
href=3D"mailto:1nk [at] H [at] ck">1nk [at] H [at] ck</A>)<BR>MSN Messenger: <A
href=3D"mailto:jjap [at] hotmail.com">jjap [at] hotmail.com</A><BR>URL/Blog: <A
href=3D"http://inkahack.blogspot.com">http://inkahack.blogsp ot.com</A><BR=
>"Si t=FA
tienes una manzana y yo tengo una manzana y las intercambiamos, entonces =
ambos
a=FAn tendremos una manzana. Pero si t=FA tienes una idea y yo tengo una =
idea y las
intercambiamos, entonces ambos tendremos dos ideas".<BR>George Bernard
Shaw<BR></DIV></FONT></BODY></HTML>
------=_NextPart_000_00BD_01C9CCDC.FD8E90A0--
