Search Forum Home   Visual FoxPro  Client/Server  Lista de id-uri...  Lista de id-uri ca parametru    10/5/2009 1:46:49 PM myself 64 posts Lista de id-uri ca parametru  (N/A) Am o procedura stocata pe server si unul din parametrii pe care ii trimit este o insiruire de id-uri (caracter 36). Cursorul pe care il creez are si conditia "id in (parametrul_meu)" . Daca trimit un singur id nu este nici o problema,problema apare cand trimit mai multe, cursorul intors este gol! Folosesc VFP9 + Firebird. Are cineva vreo idee cum ar trebui sa fie creat parametrul meu? Multumesc!  10/5/2009 1:50:00 PM myself 64 posts Re: Lista de id-uri ca parametru  (N/A) Parametrul din procedura mea este CHAR(2000)  10/6/2009 9:51:10 AM nae racaru 714 posts www.rarom.ro Re: Lista de id-uri ca parametru  (Romania) Conditia trebuie sa arate asa: id in ('SIR1','SIR2','SIR3','SIR4','SIR5') Normal ca conditia id in ('SIR1') functioneaza. VFP 6 si 9 + Oracle  10/6/2009 10:34:51 AM myself 64 posts Re: Lista de id-uri ca parametru  (N/A) Selectul meu este cam asa : "select * from agenda where id_user in (:lcId) " Daca lcId = ('Sir1',Sir2','Sir3') nu imi intoarce nimic, pentru ca il vede ca o singura variabila! Vroiam sa stiu cum ar trebui construita lista mea de id-uri pentru a primi un rezultat corect! Procedura o apelez din Fox.  10/6/2009 10:45:40 AM Dorin Vasilescu 1366 posts Re: Lista de id-uri ca parametru  (N/A) Poti cu EXECUTE STATEMENT in procedura respectiva. Ai definita o variabila VARCHAR sql pe care o construiesti dinamic sql = 'select * from agenda where id_user in (' || :lcId || ')' ; execute statement sql into ... ;  10/6/2009 11:02:41 AM myself 64 posts Re: Lista de id-uri ca parametru  (N/A) Procedura mea arata asa : SET TERM ^ ; CREATE OR ALTER PROCEDURE GETDATECALCFACTSALARII ( id_useri char(2000), an2 numeric(4,0), luna2 numeric(2,0)) returns ( id char(36), id_client char(36), nume_client char(36), cod char(5), arectb numeric(1,0), nrperm numeric(5,0), nrautadm numeric(5,0), nrconcmed numeric(5,0), refacstatnr numeric(5,0), refacstatexpl char(50), refacdeclnr numeric(5,0), refacdeclexpl char(50), nrfluctuatie numeric(5,0), nroreactspec numeric(5,0), nroreactspecexpl char(50), an numeric(4,0), luna numeric(2,0), cineadd char(50), calcadd char(70), sincronizat char(5)) as declare variable contor numeric(15,0); declare "lcCursor" cursor for (select distinct aa.id_client,ab.client,ab.cod from clienti_user aa inner join clienti ab on ab.id = aa.id_client where cast('01.'||rtrim(ltrim(cast(:luna2 as char(2))))||'.'||cast(:an2 as char(4)) as date) >= aa.dela and (cast('01.'||rtrim(ltrim(cast(:luna2 as char(2))))||'.'||cast(:an2 as char(4)) as date) <= aa.panala or (aa.panala is null) ) and ab.id = aa.id_client and ab.deleted = 0 and aa.deleted = 0 and id_ramura = '50B30359-5528-495E-890A-2C11647AB732' and id_subramura = '1E50DE84-CE1D-464B-859D-D751D6D88CFE' and are_unitatea = 1 and aa.id_user in (:id_useri) ); begin select count(distinct aa.id_client) from clienti_user aa where cast('01.'||rtrim(ltrim(cast(:luna2 as char(2))))||'.'||cast(:an2 as char(4)) as date) >= aa.dela And (CAST('01.'||rtrim(ltrim(cast(:luna2 as char(2))))||'.'||cast(:an2 as char(4)) as date) <= aa.panala or (aa.panala is null) ) and aa.deleted = 0 and id_ramura = '50B30359-5528-495E-890A-2C11647AB732' and id_subramura = '1E50DE84-CE1D-464B-859D-D751D6D88CFE' and are_unitatea = 1 and aa.id_user in (:id_useri) into :contor ; open "lcCursor" ; while (contor > 0) do begin fetch "lcCursor" into :id_client,:nume_client,:cod ; luna = luna2; an = an2; select id, arectb, nrperm, nrautadm, nrconcmed, refacstatnr, refacstatexpl, refacdeclnr, refacdeclexpl, nrfluctuatie , nroreactspec, nroreactspecexpl,cineadd, calcadd, sincronizat from datecalculfactsalarii where datecalculfactsalarii.id_client = :id_client and datecalculfactsalarii.luna = :luna and datecalculfactsalarii.an = :an into :id, :arectb, :nrperm, :nrautadm, :nrconcmed, :refacstatnr, :refacstatexpl, :refacdeclnr, :refacdeclexpl, :nrfluctuatie , :nroreactspec, :nroreactspecexpl, :cineadd, :calcadd, sincronizat ; nrperm = coalesce(:nrperm,0); nrautadm = coalesce(:nrautadm,0) ; nrconcmed = coalesce(:nrconcmed,0) ; refacstatnr = coalesce(:refacstatnr,0) ; refacdeclnr = coalesce(:refacdeclnr,0) ; nrfluctuatie = coalesce(:nrfluctuatie,0) ; nroreactspec = coalesce(:nroreactspec,0) ; refacstatexpl = coalesce(:refacstatexpl,' ') ; refacdeclexpl = coalesce(:refacdeclexpl,' ') ; nroreactspecexpl = coalesce(:nroreactspecexpl,' ') ; cineadd = coalesce(:cineadd,' ') ; calcadd = coalesce(:calcadd,' ') ; sincronizat = coalesce(:sincronizat,'A') ; arectb = coalesce(:arectb,0); contor = contor -1; suspend; end end^ SET TERM ; ^ GRANT SELECT ON CLIENTI_USER TO PROCEDURE GETDATECALCFACTSALARII; GRANT SELECT ON CLIENTI TO PROCEDURE GETDATECALCFACTSALARII; GRANT SELECT ON DATECALCULFACTSALARII TO PROCEDURE GETDATECALCFACTSALARII; GRANT EXECUTE ON PROCEDURE GETDATECALCFACTSALARII TO SYSDBA; iar apelarea o fac asa : IF thisform.check1.value = .t. lcUseri = '' SELECT v_subalterni SCAN lcUseri = lcUseri+IIF(EMPTY(lcUseri); ,'||'+v_subalterni.id_subaltern; ,',||'+v_subalterni.id_subaltern) ENDSCAN ELSE lcUseri = v_subalterni.id_subaltern ENDIF lcSql11 = 'Select * From GETDATECALCFACTSALARII' lcSql12 = '(?lcUseri,?lnAn,?lnLuna)' lcRez = SQLExec(goapp.nConnectionHandle,lcSQL11+lcSQL12,"selectie") If lcRez < 0 Aerror(gaError) goapp.ShowSQLErrorMessage(gaError[1,2], lcSQL11+lcSQL12) RETURN ENDIF Ceva in constructia lui lcUseri nu merge! Imi intoarce rezultate corecte daca selectez un singur user si deloc cand selectez mai multi. :)  10/6/2009 11:04:27 AM myself 64 posts Re: Lista de id-uri ca parametru  (N/A) Nu stiu cum sa postez corect pt a pastra formatarea! :)  10/6/2009 11:08:48 AM myself 64 posts Re: Lista de id-uri ca parametru  (N/A) deci.... procedura arata asa: SET TERM ^ ; CREATE OR ALTER PROCEDURE GETDATECALCFACTSALARII ( id_useri char(2000), an2 numeric(4,0), luna2 numeric(2,0)) returns ( id char(36), id_client char(36), nume_client char(36), cod char(5), arectb numeric(1,0), nrperm numeric(5,0), nrautadm numeric(5,0), nrconcmed numeric(5,0), refacstatnr numeric(5,0), refacstatexpl char(50), refacdeclnr numeric(5,0), refacdeclexpl char(50), nrfluctuatie numeric(5,0), nroreactspec numeric(5,0), nroreactspecexpl char(50), an numeric(4,0), luna numeric(2,0), cineadd char(50), calcadd char(70), sincronizat char(5)) as declare variable contor numeric(15,0); declare "lcCursor" cursor for (select distinct aa.id_client,ab.client,ab.cod from clienti_user aa inner join clienti ab on ab.id = aa.id_client where cast('01.'||rtrim(ltrim(cast(:luna2 as char(2))))||'.'||cast(:an2 as char(4)) as date) >= aa.dela and (cast('01.'||rtrim(ltrim(cast(:luna2 as char(2))))||'.'||cast(:an2 as char(4)) as date) <= aa.panala or (aa.panala is null) ) and ab.id = aa.id_client and ab.deleted = 0 and aa.deleted = 0 and id_ramura = '50B30359-5528-495E-890A-2C11647AB732' and id_subramura = '1E50DE84-CE1D-464B-859D-D751D6D88CFE' and are_unitatea = 1 and aa.id_user in (:id_useri) ); begin select count(distinct aa.id_client) from clienti_user aa where cast('01.'||rtrim(ltrim(cast(:luna2 as char(2))))||'.'||cast(:an2 as char(4)) as date) >= aa.dela And (CAST('01.'||rtrim(ltrim(cast(:luna2 as char(2))))||'.'||cast(:an2 as char(4)) as date) <= aa.panala or (aa.panala is null) ) and aa.deleted = 0 and id_ramura = '50B30359-5528-495E-890A-2C11647AB732' and id_subramura = '1E50DE84-CE1D-464B-859D-D751D6D88CFE' and are_unitatea = 1 and aa.id_user in (:id_useri) into :contor ; open "lcCursor" ; while (contor > 0) do begin fetch "lcCursor" into :id_client,:nume_client,:cod ; luna = luna2; an = an2; select id, arectb, nrperm, nrautadm, nrconcmed, refacstatnr, refacstatexpl, refacdeclnr, refacdeclexpl, nrfluctuatie , nroreactspec, nroreactspecexpl,cineadd, calcadd, sincronizat from datecalculfactsalarii where datecalculfactsalarii.id_client = :id_client and datecalculfactsalarii.luna = :luna and datecalculfactsalarii.an = :an into :id, :arectb, :nrperm, :nrautadm, :nrconcmed, :refacstatnr, :refacstatexpl, :refacdeclnr, :refacdeclexpl, :nrfluctuatie , :nroreactspec, :nroreactspecexpl, :cineadd, :calcadd, sincronizat ; nrperm = coalesce(:nrperm,0); nrautadm = coalesce(:nrautadm,0) ; nrconcmed = coalesce(:nrconcmed,0) ; refacstatnr = coalesce(:refacstatnr,0) ; refacdeclnr = coalesce(:refacdeclnr,0) ; nrfluctuatie = coalesce(:nrfluctuatie,0) ; nroreactspec = coalesce(:nroreactspec,0) ; refacstatexpl = coalesce(:refacstatexpl,' ') ; refacdeclexpl = coalesce(:refacdeclexpl,' ') ; nroreactspecexpl = coalesce(:nroreactspecexpl,' ') ; cineadd = coalesce(:cineadd,' ') ; calcadd = coalesce(:calcadd,' ') ; sincronizat = coalesce(:sincronizat,'A') ; arectb = coalesce(:arectb,0); contor = contor -1; suspend; end end^ SET TERM ; ^ GRANT SELECT ON CLIENTI_USER TO PROCEDURE GETDATECALCFACTSALARII; GRANT SELECT ON CLIENTI TO PROCEDURE GETDATECALCFACTSALARII; GRANT SELECT ON DATECALCULFACTSALARII TO PROCEDURE GETDATECALCFACTSALARII; GRANT EXECUTE ON PROCEDURE GETDATECALCFACTSALARII TO SYSDBA; iar procedura de apelare asa : IF thisform.check1.value = .t. lcUseri = '' SELECT v_subalterni SCAN lcUseri = lcUseri+IIF(EMPTY(lcUseri); ,'||'+v_subalterni.id_subaltern; ,',||'+v_subalterni.id_subaltern) ENDSCAN ELSE lcUseri = v_subalterni.id_subaltern ENDIF lcSql11 = 'Select * From GETDATECALCFACTSALARII' lcSql12 = '(?lcUseri,?lnAn,?lnLuna)' lcRez = SQLExec(goapp.nConnectionHandle,lcSQL11+lcSQL12,"selectie") If lcRez < 0 Aerror(gaError) goapp.ShowSQLErrorMessage(gaError[1,2], lcSQL11+lcSQL12) RETURN ENDIF Ceva in constructia lu lcUseri e gresit. Imi intoarce rezultate corecte daca selectez un singur user si deloc cand selectez mai multi.  10/7/2009 11:41:14 AM Dorin Vasilescu 1366 posts Re: Lista de id-uri ca parametru  (N/A) E normal sa faca asa, deoarece trimiti ca parametru un sir cu operatorul de concatenare in el. Firebird nu stie macrosubstitutie Trebuie sa concatenezi id-urile altfel scan      lcUseri = lcUseri+iif(empty(lcUseri);      ,v_subalterni.id_subaltern;      , ','+v_subalterni.id_subaltern) endscan cam asa ar arata procedura ta ... SET TERM ^ ; CREATE OR ALTER PROCEDURE GETDATECALCFACTSALARII ( id_useri char(2000), an2 numeric(4,0), luna2 numeric(2,0)) returns ( id char(36), id_client char(36), nume_client char(36), cod char(5), arectb numeric(1,0), nrperm numeric(5,0), nrautadm numeric(5,0), nrconcmed numeric(5,0), refacstatnr numeric(5,0), refacstatexpl char(50), refacdeclnr numeric(5,0), refacdeclexpl char(50), nrfluctuatie numeric(5,0), nroreactspec numeric(5,0), nroreactspecexpl char(50), an numeric(4,0), luna numeric(2,0), cineadd char(50), calcadd char(70), sincronizat char(5)) as declare variable contor numeric(15,0); declare variable sql_1 varchar(32000) ; declare variable sql_2 varchar(32000) ; --declare "lcCursor" cursor for () ); begin     sql_1 = 'select distinct aa.id_client,ab.client,ab.cod from clienti_user aa             inner join clienti ab on ab.id = aa.id_client             where             cast('01.'||rtrim(ltrim(cast(:luna2 as char(2))))||'.'||cast(:an2 as char(4)) as date) >= aa.dela             and (cast('01.'||rtrim(ltrim(cast(:luna2 as char(2))))||'.'||cast(:an2 as char(4)) as date) <= aa.panala or (aa.panala is null) )             and ab.id = aa.id_client and ab.deleted = 0 and aa.deleted = 0             and id_ramura = '50B30359-5528-495E-890A-2C11647AB732' and id_subramura = '1E50DE84-CE1D-464B-859D-D751D6D88CFE'             and are_unitatea = 1 and aa.id_user in (' || :id_useri || ')' ;     sql_2 = 'select count(distinct aa.id_client) from clienti_user aa             where             cast('01.'||rtrim(ltrim(cast(:luna2 as char(2))))||'.'||cast(:an2 as char(4)) as date) >= aa.dela             And (CAST('01.'||rtrim(ltrim(cast(:luna2 as char(2))))||'.'||cast(:an2 as char(4)) as date) <= aa.panala or (aa.panala is null) )             and aa.deleted = 0 and id_ramura = '50B30359-5528-495E-890A-2C11647AB732'             and id_subramura = '1E50DE84-CE1D-464B-859D-D751D6D88CFE' and are_unitatea = 1 and aa.id_user in (' || :id_useri || ')' ;     execute statement sql_2 into :contor ;     for execute statement sql_1 into :id_client,:nume_client,:cod do     begin         luna = luna2;         an = an2;         select id, arectb, nrperm, nrautadm, nrconcmed, refacstatnr, refacstatexpl, refacdeclnr, refacdeclexpl, nrfluctuatie             , nroreactspec, nroreactspecexpl,cineadd, calcadd, sincronizat from datecalculfactsalarii         where datecalculfactsalarii.id_client = :id_client and datecalculfactsalarii.luna = :luna and datecalculfactsalarii.an = :an             into :id, :arectb, :nrperm, :nrautadm, :nrconcmed, :refacstatnr, :refacstatexpl, :refacdeclnr, :refacdeclexpl, :nrfluctuatie                 , :nroreactspec, :nroreactspecexpl, :cineadd, :calcadd, sincronizat ;         nrperm = coalesce(:nrperm,0);         nrautadm = coalesce(:nrautadm,0) ;         nrconcmed = coalesce(:nrconcmed,0) ;         refacstatnr = coalesce(:refacstatnr,0) ;         refacdeclnr = coalesce(:refacdeclnr,0) ;         nrfluctuatie = coalesce(:nrfluctuatie,0) ;         nroreactspec = coalesce(:nroreactspec,0) ;         refacstatexpl = coalesce(:refacstatexpl,' ') ;         refacdeclexpl = coalesce(:refacdeclexpl,' ') ;         nroreactspecexpl = coalesce(:nroreactspecexpl,' ') ;         cineadd = coalesce(:cineadd,' ') ;         calcadd = coalesce(:calcadd,' ') ;         sincronizat = coalesce(:sincronizat,'A') ;         arectb = coalesce(:arectb,0);         suspend;     end end end^ SET TERM ; ^  10/7/2009 11:47:44 AM Dorin Vasilescu 1366 posts Re: Lista de id-uri ca parametru  (N/A)  myself wrote Nu stiu cum sa postez corect pt a pastra formatarea! :) Se pare ca trebuie sa folosesti Firefox, nici cu IE nu merge Partea cu :contor nu este de fapt necesara la cursoare, poti verifica dupa fetch daca l-a realizat cu ROW_COUNT si sa iesi din bucla        BEGIN          OPEN C;          WHILE (1 = 1) DO          BEGIN            FETCH C INTO :RNAME;            IF (ROW_COUNT = 0) THEN              LEAVE;            SUSPEND;          END          CLOSE C;        END Poti elimina     sql_2 = ...     execute statement sql_2 into :contor ; fara probleme, trebuie sa functioneze si fara ele  Page 1 of 1   Visual FoxPro  Client/Server  Lista de id-uri... Flat View Tree View Oldest To Newest Newest To Oldest