VB 6 num recebe retorno correto de PROC com UPDATE?

[vilmarbr]

VB 6 num recebe retorno correto de PROC com UPDATE??

Algum macete ??

No Sybase faz certinho, mas no VB 6 o campo é recebido sem o UPDATE na tabela no campo DT_INICIO_VIGENCIA temporária.

Eu uso ADO com ODBC, Sybase 11.

Algum macete ??? Alguma dica ?? Alguém já passou por isto ????

Vejam PROC:

create proc migra_001

as 

select distinct
    ba60.cd_ps,
    ba60.tp_clc_prst,
    ba60.cd_ind_cm_sld_dvr,
    pc_desc_prod = isnull(ba60.pc_desc_prod,0),
    dt_inicio_vigencia = replicate(' ',10)
into 
    #ba60_ba30_prod_t
from 
    ba60_cnt_opds ba60,
    bd30_tp_clc_prst ba30 
where
    (ba60.tp_clc_prst = ba30.tp_clc_prst)
group by 
    ba60.cd_ps,
    ba60.tp_clc_prst,
    ba60.cd_ind_cm_sld_dvr,
    ba60.pc_desc_prod
order by
    ba60.cd_ps

/*********************************  
Declara variáveis.  
Obs.: campo_p = sufixo para indicar que a variável esta sendo usado no cursor de produtos. 
No cursor os campos tem o mesmo  
tipo das tabelas de onde os dados são extraídos.  
**********************************/  
declare 
    @cd_ps_p int,
    @tp_clc_prst_p int,
    @cd_ind_cm_sld_dvr_p int,
    @pc_desc_prod_p decimal(3,2),
    @cd_ps_old_p int,
    @dt_inicio_vigencia_p datetime,
    @msgret varchar(100),
    @codret int

begin tran
/*********************************
Declara o cursor a ser utilizado
**********************************/
declare cur_ba60_ba30_prod cursor for 
select
    cd_ps,
    tp_clc_prst,
    cd_ind_cm_sld_dvr,
    pc_desc_prod
from   
    #ba60_ba30_prod_t  
order by
    cd_ps
for read only

/*********************************
Abre e lê primeiro registro do cursor
**********************************/
open cur_ba60_ba30_prod
    fetch    cur_ba60_ba30_prod
   into 
        @cd_ps_p,
        @tp_clc_prst_p,
        @cd_ind_cm_sld_dvr_p,
        @pc_desc_prod_p

/*********************************
Verifica se ainda existe registros a serem percorridos no cursor 
**********************************/
while @@sqlstatus <> 2                                                                                                               
begin                                                                                                                                
    if @@sqlstatus = 1                                                                                                           
    begin                                                                                                                        
        rollback tran                                                                                                        
        select @msgret = 'Procedure executada com ERRO. Problemas no cursor cur_ixdt001.'
        select @codret = -34                                                                                                         
        return -34                                                                                                           
    end                                                                                                                          

    /*Data Início Vigência*/
    if @cd_ps_p <> @cd_ps_old_p
    begin
        select @cd_ps_old_p = @cd_ps_p
        select @dt_inicio_vigencia_p = convert(datetime,'01.01.1900')
    end
    else
        begin
            select @dt_inicio_vigencia_p = dateadd(day, 1, @dt_inicio_vigencia_p)
        end
    
    update #ba60_ba30_prod_t 
    set 
        dt_inicio_vigencia = @dt_inicio_vigencia_p --'TESTE'
    where
        (cd_ps = @cd_ps_old_p) and
        (tp_clc_prst = @tp_clc_prst_p) and
        (cd_ind_cm_sld_dvr = @cd_ind_cm_sld_dvr_p) and
        (pc_desc_prod = @pc_desc_prod_p)
    
    commit tran
    
    if @@transtate = 2 or @@transtate = 3                                                                           
    begin                                                                                                           
        rollback tran                                                                                                
        select @msgret = 'Executada com ERRO. Problemas ao atualizar #ixdt001_t.'
        select @codret = -10
        return -10
    end                                                                                                         
                                                                                                                                     
    /*********************************
    Lê próximo registro do cursor
    **********************************/       
    fetch    cur_ba60_ba30_prod
   into
        @cd_ps_p,
        @tp_clc_prst_p,
        @cd_ind_cm_sld_dvr_p,
        @pc_desc_prod_p
end

commit tran

close cur_ba60_ba30_prod
deallocate cursor cur_ba60_ba30_prod

select 
    cd_ps as cd_ps_f, 
    dt_inicio_vigencia as dt_ini_vig_f
from
    #ba60_ba30_prod_t
order by
    cd_ps

----

GRATO !!

Editado Setembro 22, 2009 por kuroi
Adicionar tag CODE

[vilmarbr]

Então, pessoal, num tivemos como comprovar se a versão de ODBC está com problemas ou mesmo ADO, pois aqui só podemos a versão corrente nas máquinas, assim como só pode usar esta versão de ADO, eheheh.

Como aqui tudo é hiperburocrático, acabamos revendo a lógica da stored procedure, e ao invés de fazer um UPDATE tendo 2 tabelas temporárias, estamos fazendo INSERTS usando 3 tabelas temporárias.

Desta forma as datas calculadas chegaram corretamente no VB 6.0.

TRECHO ALTERADOS:

create procedure migra_001

as

/*********************************

Cria tabelas temporárias.

**********************************/

/*....TRECHO QUE NÃO MUDOU....*/

create table #produto

(

cd_ps int null,

tp_clc_prst int null,

cd_ind_cm_sld_dvr int null,

pc_desc_prod decimal(5,2) null,

dt_ini_vig datetime null

)

create table #prod_dt_ini_vig

(

cd_ps int null,

tp_clc_prst int null,

cd_ind_cm_sld_dvr int null,

pc_desc_prod decimal(5,2) null,

dt_ini_vig datetime null

)

/*********************************

Declara variáveis.

**********************************/

declare

@cd_ps_p int,

@cd_ps_old_p int,

@tp_clc_prst_p int,

@cd_ind_cm_sld_dvr_p int,

@pc_desc_prod_p decimal(5,2),

@dt_ini_vig_p datetime

declare

@cd_mld_ps int,

@cd_ps int,

@tp_clc_prst int,

@cd_ind_cm_sld_dvr int,

@pc_desc_prod decimal(3,2),

@tx_ds_fma_clc_prst varchar(100),

@tx_ds_fma_clc_prst_f varchar(1),

@dt_ini_vig_f datetime,

@cd_bas_calc varchar(1),

@ind_exi_bon varchar(1),

@perc_bon varchar(5),

@ind_cap_enc_car varchar(1),

@ind_poss_ir_cus varchar(1)

declare

@msgret varchar(100),

@codret int

select @cd_ps_old_p = 0

insert into #produto

select distinct

b60.cd_ps,

b60.tp_clc_prst,

b60.cd_ind_cm_sld_dvr,

isnull(b60.pc_desc_prod,0),

dt_ini_vig = null

from

ba60_cnt_opds b60,

bd30_tp_clc_prst b30

where

(b60.tp_clc_prst = b30.tp_clc_prst)

group by

b60.cd_ps, b60.tp_clc_prst, b60.cd_ind_cm_sld_dvr, b60.pc_desc_prod

order by

b60.cd_ps

begin tran

/*********************************

Declara o cursor a ser utilizado

**********************************/

declare cur_ba60_ba30_prod cursor for

select distinct

cd_ps,

tp_clc_prst,

cd_ind_cm_sld_dvr,

pc_desc_prod

from

#produto

for read only

/*********************************

Abre e lê primeiro registro do cursor

**********************************/

open cur_ba60_ba30_prod

fetch cur_ba60_ba30_prod

into

@cd_ps_p,

@tp_clc_prst_p,

@cd_ind_cm_sld_dvr_p,

@pc_desc_prod_p

/*********************************

Verifica se ainda existe registros a serem percorridos no cursor

**********************************/

while @@sqlstatus <> 2

begin

if @@sqlstatus = 1

begin

rollback tran

select @msgret = 'Procedure executada com ERRO. Problemas no cursor .'

select @codret = -34

return -34

end

/*Data Início Vigência*/

if @cd_ps_p <> @cd_ps_old_p

begin

select @cd_ps_old_p = @cd_ps_p

select @dt_ini_vig_p = convert(datetime,'01.01.1900')

end

else

begin

select @dt_ini_vig_p = dateadd(day, 1, @dt_ini_vig_p)

end

insert into #prod_dt_ini_vig

(

cd_ps,

tp_clc_prst,

cd_ind_cm_sld_dvr,

pc_desc_prod,

dt_ini_vig

)

values

(

@cd_ps_p,

@tp_clc_prst_p,

@cd_ind_cm_sld_dvr_p,

@pc_desc_prod_p,

@dt_ini_vig_p

)

if @@transtate = 2 or @@transtate = 3

begin

rollback tran

select @msgret = 'Executada com ERRO. Problemas ao atualizar .'

select @codret = -10

return -10

end

fetch cur_ba60_ba30_prod

into

@cd_ps_p,

@tp_clc_prst_p,

@cd_ind_cm_sld_dvr_p,

@pc_desc_prod_p

end

commit tran

close cur_ba60_ba30_prod

deallocate cursor cur_ba60_ba30_prod

/*....TRECHO QUE NÃO MUDOU....*/

Grato pela visita.