Thread: odbc + refcursor + vb6

odbc + refcursor + vb6

From

"Jose J. Ayala Pineda"

Date:

04 May 2009, 22:43:11

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
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;

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ú tienes una manzana y yo tengo una manzana y las intercambiamos, entonces ambos aún tendremos una manzana. Pero si tú tienes una idea y yo tengo una idea y las intercambiamos, entonces ambos tendremos dos ideas".
George Bernard Shaw

Re: odbc + refcursor + vb6

From

Hiroshi Inoue

Date:

05 May 2009, 14:58:21

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;