wDevil wrote : > > CREATE DBPROC DBADMIN.BILLING(IN ID_KWR INT,IN ID_PAGE INT, IN ID_ZONE > INT,IN HOST CHAR(250),IN IP CHAR(100),IN REFERER CHAR(1024),IN ID_USER > INT,IN R_USER CHAR(100),IN STATISTIC INT, IN PAYMENT INT, IN EVENT > INT) > RETURNS CURSOR > AS > VAR ID_ACC INT; > ID_CAMP INT; > ID_REKLAMA INT; > R_SUM FLOAT; > PAY_TYPE INT; > ID_MOVES INT; > $CURSOR = 'TEMP_CURSOR'; > /* > STATISTIC - выполнять ли статистику для объекта > PAYMENT - выполнять ли проплату для объекта > EVENT - событие. Т.е процедура вызывается с: > 2 - Клик > 3 - Просмтор > GET_PARAMS - выбрать ли параметры объекта(пока не реализовано) > */ > /*Ранжируем рекламы для заданной стр, ключ. слова и зоны. Возвращаем > одну из них*/ > DECLARE :$CURSOR CURSOR FOR > SELECT bi_reklama.id as id_rek, > bi_camp.id_account as id_acc, > bi_camp.id as id_camp, > bi_rek_auc.r_sum, > bi_zone.id_pay_type as pay_type > FROM DBADMIN.bi_reklama, > DBADMIN.bi_rek_auc, > DBADMIN.bi_pz_auc, > DBADMIN.bi_auction, > DBADMIN.bi_ref_keyword, > DBADMIN.bi_page, > DBADMIN.bi_page_zone, > DBADMIN.bi_rank, > DBADMIN.bi_camp, > DBADMIN.inv_accounts, > DBADMIN.bi_zone > WHERE ROWNO<=1 AND > bi_ref_keyword.id=920 AND > bi_page.out_id=1124 AND > bi_page_zone.id_page=bi_page.id AND > bi_page_zone.id_zone=802 AND > bi_zone.id=bi_page_zone.id_zone AND > bi_pz_auc.id_keyword=bi_ref_keyword.id AND > bi_pz_auc.id_page_zone=bi_page_zone.id AND > bi_auction.id_pz_auc=bi_pz_auc.id AND > bi_auction.id=bi_rek_auc.id_auction AND > BI_RANK.ID_PAGE(+)=bi_page.id AND > BI_RANK.ID_REKLAMA(+)=BI_REK_AUC.ID_REKLAMA AND > BI_RANK.ID_AUCTION(+)=BI_AUCTION.ID AND > (BI_REK_AUC.R_SUM/(SELECT SUM(BI_REK_AUC.R_SUM) > FROM > DBADMIN.BI_REK_AUC, > > DBADMIN.bi_auction, > > DBADMIN.bi_ref_keyword, > > DBADMIN.bi_page_zone, > > DBADMIN.bi_page, > > DBADMIN.bi_pz_auc > WHERE > bi_ref_keyword.id=920 AND > > bi_page.out_id=1124 AND > > bi_page_zone.id_page=bi_page.id AND > > bi_page_zone.id_zone=802 AND > > bi_pz_auc.id_keyword=bi_ref_keyword.id AND > > bi_pz_auc.id_page_zone=bi_page_zone.id AND > > bi_auction.id_pz_auc=bi_pz_auc.id AND > > bi_rek_auc.id_auction=bi_auction.id)>= > BI_RANK.NUM_VIEW/(SELECT SUM(NUM_VIEW) > > FROM DBADMIN.BI_RANK > WHERE > BI_RANK.ID_PAGE=bi_page.id OR BI_RANK.ID_PAGE=0 AND > > BI_RANK.ID_AUCTION=BI_AUCTION.ID OR BI_RANK.ID_AUCTION=0 AND > bi_rank.id_reklama=bi_reklama.id OR bi_rank.id_reklama=0) > OR > BI_REK_AUC.R_SUM/(SELECT SUM(NUM_VIEW) > > FROM DBADMIN.BI_RANK > WHERE > BI_RANK.ID_PAGE=bi_page.id OR BI_RANK.ID_PAGE=0 AND > BI_RANK.ID_AUCTION=BI_AUCTION.ID OR > BI_RANK.ID_AUCTION=0 AND > bi_rank.id_reklama=bi_reklama.id OR bi_rank.id_reklama=0)>=1 ) AND > BI_REKLAMA.ID=BI_REK_AUC.ID_REKLAMA AND > BI_CAMP.ID=BI_REKLAMA.ID_CAMP AND > BI_CAMP.IS_ACTIVE=1 AND > DATE(NOW())>=DATE(BI_CAMP.DT_CREATE) AND > DATE(NOW())<=DATE(BI_CAMP.DT_END) AND > DATE(NOW())>=DATE(BI_REKLAMA.DT_CREATE) AND > DATE(NOW())<=DATE(BI_REKLAMA.DT_END) AND > INV_ACCOUNTS.ID=BI_CAMP.ID_ACCOUNT AND > INV_ACCOUNTS.BALANCE>BI_REK_AUC.R_SUM; > WHILE $rc = 0 DO BEGIN > FETCH $CURSOR INTO :id_reklama, > :id_acc,:id_camp,:r_sum,:pay_type; > /* Если необходимо провести стату */ > IF STATISTIC=1 THEN > BEGIN > /* Если событие - клик, региструем клик */ > IF EVENT=2 THEN > BEGIN > INSERT INTO DBADMIN.BI_STAT_CLICK SET > ID_REKLAMA=:id_reklama, > ID_PAGE=:id_page,HOST=:host,REFERER=:referer,IP=:ip,USER=:r_us > er,ID_USER=:id_user; > END; > /* Если событие - просмотр, региструем просмтор */ > IF EVENT=3 THEN > BEGIN > INSERT INTO DBADMIN.BI_STAT_VIEW SET > ID_REKLAMA=:id_reklama, > ID_PAGE=:id_page,HOST=:host,REFERER=:referer,IP=:ip,USER=:r_us > er,ID_USER=:id_user; > END; > END; > /* Если необходимо оплатить */ > IF PAYMENT=1 THEN > BEGIN > /* Если тип оплаты объекта свопадает с событием, > делаем проплаты*/ > IF EVENT=PAY_TYPE THEN > BEGIN > SELECT INV_MOV_SEQ.NEXTVAL FROM DBADMIN.INV_MOVES; > FETCH INTO :id_moves; > INSERT INTO DBADMIN.INV_MOVES SET > ID=:id_moves,ID_ACC_FROM=:id_acc,ID_ACC_TO=1,DATE_EXEC=NOW(),A MOUNT=:r_sum,ID_INV=NULL,BAL_AMOUNT=:r_sum; > INSERT INTO DBADMIN.BI_PAY SET > ID_PAY=:id_moves,ID_REKLAMA=:id_reklama,ID_CAMP=:id_camp; > END; > END; > END; > Error: General error;-4000 POS(5030) Unknown result table. > I replace FETCH $CURSOR INTO - > FETCH CURSOR INTO TEMP_CURSOR > and nothing changed. What ideas? >
For me it looks strange, that the cursor to be returned by the procedure is already fetched inside. This means, that you can't fetch any row from that cursor outside the procedure. Besides I think this also explains the problem. When the procedure is compiled, $CURSOR is unknown and the DECLARE CURSOR statement produces a cursor with a default name. When the FETCH statement is compiled, it does not know how to resolve $CURSOR which results in -4000. I recomment not to return a CURSOR from your procedure. Best Regards, Thomas -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
