Skip site navigation (1) Skip section navigation (2)

odbc + refcursor + vb6

From: "Jose J(dot) Ayala Pineda" <jayala(at)cromotex(dot)com(dot)pe>
To: <pgsql-odbc(at)postgresql(dot)org>
Subject: odbc + refcursor + vb6
Date: 2009-05-04 22:23:07
Message-ID: A02ED620BEA84DAD9CA94AB6B73AFABD@depsis01 (view raw or flat)
Thread:
Lists: pgsql-odbc
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(at)H@ck)
MSN Messenger: jjap(at)hotmail(dot)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

Responses

pgsql-odbc by date

Next:From: Hiroshi InoueDate: 2009-05-05 14:48:40
Subject: Re: Unsupported Type UUID
Previous:From: postgresqlodbc.domain.thewild_codataDate: 2009-05-04 10:30:41
Subject: INSERT on MSAccess linked ODBC table : out of memory

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group