odbc + refcursor + vb6

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
=20
Conn.BeginTrans
Cmd.ActiveConnection =3D Conn
Cmd.CommandText =3D "select=20
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=20
SELECT * FROM cromoadmin.CentroCto CC RIGHT JOIN
(cromoadmin.DetValesAlm Det INNER JOIN cromoadmin.Vales Cab =
ON=20
DEt.Sucr_codigo =3D cab.Sucr_codigo and=20
Det.VALE_NumVale =3D Cab.VALE_NumVale AND =20
Det.ALMA_Codigo =3D Cab.ALMA_Codigo AND=20
Det.TIPO_TabVale =3D Cab.TIPO_TabVale AND =20
Det.TIPO_CodVale =3D Cab.TIPO_CodVale)
ON CC.Cent_cod=3DCab.CENT_Cod =20
INNER JOIN cromoadmin.Catalogos ON=20
Det.SUCR_Codigo =3D Catalogos.SUCR_Codigo AND
Det.PROD_CodProd =3D Catalogos.PROD_CodProd AND=20
Det.ALMA_Codigo =3D Catalogos.ALMA_Codigo INNER JOIN =
cromoadmin.Tipos Tip ON=20
CAb.TIPO_TabVale =3D Tip.TIPO_CodTabla AND=20
Cab.TIPO_CodVale =3D Tip.TIPO_CodTipo =20
LEFT OUTER JOIN cromoadmin.CtaCtePRov CCP on ccp.ccpr_nroTran =
=3D det.ccpr_nroTran and=20
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@H@ck)
MSN Messenger: jjap@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



charset=3Diso-8859-1">

Re: odbc + refcursor + vb6

Jose J. Ayala Pineda wrote:
> 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 = Conn
> Cmd.CommandText = "select
> kardexf('017033','01/04/2009','30/04/2009','data');FETCH ALL IN data;"
> Set Rst = Cmd.Execute

How about inserting a line here
Set Rst = Rst.NextRecordset
?

> 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 = cab.Sucr_codigo and
> Det.VALE_NumVale = Cab.VALE_NumVale AND
> Det.ALMA_Codigo = Cab.ALMA_Codigo AND
> Det.TIPO_TabVale = Cab.TIPO_TabVale AND
> Det.TIPO_CodVale = Cab.TIPO_CodVale)
> ON CC.Cent_cod=Cab.CENT_Cod
> INNER JOIN cromoadmin.Catalogos ON
> Det.SUCR_Codigo = Catalogos.SUCR_Codigo AND
> Det.PROD_CodProd = Catalogos.PROD_CodProd AND
> Det.ALMA_Codigo = Catalogos.ALMA_Codigo INNER JOIN
> cromoadmin.Tipos Tip ON
> CAb.TIPO_TabVale = Tip.TIPO_CodTabla AND
> Cab.TIPO_CodVale = Tip.TIPO_CodTipo
> LEFT OUTER JOIN cromoadmin.CtaCtePRov CCP on ccp.ccpr_nroTran =
> det.ccpr_nroTran and
> ccp.SUCR_Codigo = det.SUCR_Codigo
> WHERE Det.ALMA_Codigo = vCodAlm And
> Det.PROD_CodProd = 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;

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc