CREATE DBPROC DBADMIN.BILLING(IN ID_KWR INT,IN ID_PAGE INT, IN ID_ZONE
INT,IN PART_SITE 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,OUT VAR_OUT CHAR(512))
AS
VAR ID_CAMP INT;
R_SUM FLOAT;
PAY_TYPE INT;
ID_MOVES INT;
NUM_ROWS INT;
ID_AUC INT;
ID_ACC INT;
ID_REKLAMA INT;
PROMO_TYPE INT;
TYPE_REK INT;
/*
STATISTIC - выполнять ли статистику для объекта
PAYMENT - выполнять ли проплату для объекта
EVENT - событие. Т.е процедура вызывается с:
2 - Клик
3 - Просмтор
*/
IF PART_SITE!=0 THEN
BEGIN
IF PART_SITE = 1 THEN
BEGIN
SELECT BI_REF_KEYWORD.ID
FROM DBADMIN.BI_REF_KEYWORD,
DBADMIN.BI_PAGE_KEYW
WHERE BI_REF_KEYWORD.ID_EO=:ID_KWR AND
BI_REF_KEYWORD.ID=BI_PAGE_KEYW.ID_PAGE AND
BI_PAGE_KEYW.ID_PAGE=:id_page ;
END;
IF PART_SITE = 2 THEN
BEGIN
SELECT BI_REF_KEYWORD.ID
FROM DBADMIN.BI_REF_KEYWORD,
DBADMIN.BI_PAGE_KEYW
WHERE BI_REF_KEYWORD.ID_PB=:ID_KWR AND
BI_REF_KEYWORD.ID=BI_PAGE_KEYW.ID_PAGE AND
BI_PAGE_KEYW.ID_PAGE=:id_page ;
END;
IF PART_SITE = 3 THEN
BEGIN
SELECT BI_REF_KEYWORD.ID
FROM DBADMIN.BI_REF_KEYWORD,
DBADMIN.BI_PAGE_KEYW
WHERE BI_REF_KEYWORD.ID_COMP=:ID_KWR AND
BI_REF_KEYWORD.ID=BI_PAGE_KEYW.ID_PAGE AND
BI_PAGE_KEYW.ID_PAGE=:id_page ;
END;
FETCH INTO :ID_KWR;
END;
/*Ранжируем рекламы для заданной стр, ключ. слова и зоны. Возвращаем
одну из них*/
DECLARE TEMP_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,
BI_PROMO.ID_TYPE_PROMO AS PROMO_TYPE,
BI_AUCTION.ID AS ID_AUC,
BI_REKLAMA.ID_TYPE_REK
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_CAMP,
DBADMIN.INV_ACCOUNTS,
DBADMIN.BI_ZONE,
DBADMIN.BI_PROMO
WHERE ROWNO<=1 AND
BI_REF_KEYWORD.ID=:ID_KWR AND
BI_PAGE.OUT_ID=:ID_PAGE AND
BI_PAGE_ZONE.ID_PAGE=BI_PAGE.ID AND
BI_PAGE_ZONE.ID_ZONE=:ID_ZONE 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_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=:ID_KWR AND
BI_PAGE.OUT_ID=:ID_PAGE AND
BI_PAGE_ZONE.ID_PAGE=BI_PAGE.ID AND
BI_PAGE_ZONE.ID_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_REK_AUC.ID_AUCTION=BI_AUCTION.ID)>=BI_REK_AUC.NUM_VIEW/BI_PZ_AUC.AUC_VIEW)
AND
BI_REK_AUC.R_SUM!=0 AND
BI_REKLAMA.ID=BI_REK_AUC.ID_REKLAMA AND
BI_PROMO.ID_REKLAMA(+)=BI_REKLAMA.ID 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
ORDER BY NUM_VIEW ASC;
SET NUM_ROWS=0;
WHILE NUM_ROWS<=$COUNT DO BEGIN
FETCH TEMP_CURSOR INTO :id_reklama,
:id_acc,:id_camp,:r_sum,:pay_type,:promo_type,:ID_AUC, :TYPE_REK;
IF PROMO_TYPE IS NULL THEN SET PROMO_TYPE=0;
SET VAR_OUT=ID_REKLAMA&'-'&PROMO_TYPE&'-'&TYPE_REK;
/* Если необходимо провести стату */
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,ID_AUCTION=:ID_AUC,HOST=:host,REFERER=:referer,IP=:ip,USER=:r_user,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,ID_AUCTION=:ID_AUC,HOST=:host,REFERER=:referer,IP=:ip,USER=:r_user,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(),AMOUNT=: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;
SET NUM_ROWS=NUM_ROWS+1;
END;
---------------------------------------------------------------------
CALL BILLING(920,1124,802,0,'pc3.nettle.ru','212.17.4.86','no
referer',0,'anonymous',1,1,3,:out)
Everthing work; We use PHP-ODBC. ODBC can't get result from "CALL
BILLING(920,1124,802,0,'pc3.nettle.ru','212.17.4.86','no
referer',0,'anonymous',1,1,3,:out)"
I wrote function CALL_BILLING:
----------------------------------------------------------------------
CREATE FUNCTION DBADMIN.CALL_BILLING (ID_KWR INT,ID_PAGE INT,ID_ZONE
INT,PART_SITE INT,HOST CHAR(250),IP CHAR(100),REFERER
CHAR(1024),ID_USER INT,R_USER CHAR(100),STATISTIC INT,PAYMENT
INT,EVENT INT)
RETURNS varchar AS
VAR VAR_OUT varchar;
CALL DBADMIN.BILLING(:ID_KWR,:ID_PAGE,
:ID_ZONE,:PART_SITE,':HOST',':IP',':REFERER',:ID_USER,':R_USER',:STATISTIC,:PAYMENT,
:EVENT,:VAR_OUT);
RETURN VAR_OUT;
-------------------------------------------------------------------------
Type VAR_CHAR i try CHAR and VARCHAR;
Error:
Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
General error;800 Implicit SERVERDB restart (connection aborted)
SELECT CALL_BILLING(920,1124,802,0,'pc3.nettle.ru','212.17.4.86','no
referer',0,'anonymous',1,1,3) FROM DUAL
I replace in function "CALL DBADMIN.BILLING(:ID_KWR,:ID_PAGE,
:ID_ZONE,:PART_SITE,':HOST',':IP',':REFERER',:ID_USER,':R_USER',:STATISTIC,:PAYMENT,
:EVENT,:VAR_OUT);"->"CALL
BILLING(920,1124,802,0,'pc3.nettle.ru','212.17.4.86','no
referer',0,'anonymous',1,1,3,:var_out);"
Nothing chenged;
I wrote function without subquery:
----------------------------------------------------------------------
CREATE FUNCTION DBADMIN.FUNC_BILLING (ID_KWR INT,ID_PAGE INT,ID_ZONE
INT,PART_SITE INT,HOST CHAR(250),IP CHAR(100),REFERER
CHAR(1024),ID_USER INT,R_USER CHAR(100),STATISTIC INT,PAYMENT
INT,EVENT INT)
RETURNS CHAR(512) AS
VAR ID_CAMP INT;
R_SUM FLOAT;
PAY_TYPE INT;
ID_MOVES INT;
NUM_ROWS INT;
ID_AUC INT;
ID_ACC INT;
ID_REKLAMA INT;
PROMO_TYPE INT;
TYPE_REK INT;
ALL_SUM FLOAT;
VAR_OUT CHAR(512);
/*
STATISTIC - выполнять ли статистику для объекта
PAYMENT - выполнять ли проплату для объекта
EVENT - событие. Т.е процедура вызывается с:
2 - Клик
3 - Просмтор
*/
IF PART_SITE!=0 THEN
BEGIN
IF PART_SITE = 1 THEN
BEGIN
SELECT BI_REF_KEYWORD.ID
FROM DBADMIN.BI_REF_KEYWORD,
DBADMIN.BI_PAGE_KEYW
WHERE BI_REF_KEYWORD.ID_EO=:ID_KWR AND
BI_REF_KEYWORD.ID=BI_PAGE_KEYW.ID_PAGE AND
BI_PAGE_KEYW.ID_PAGE=:id_page ;
END;
IF PART_SITE = 2 THEN
BEGIN
SELECT BI_REF_KEYWORD.ID
FROM DBADMIN.BI_REF_KEYWORD,
DBADMIN.BI_PAGE_KEYW
WHERE BI_REF_KEYWORD.ID_PB=:ID_KWR AND
BI_REF_KEYWORD.ID=BI_PAGE_KEYW.ID_PAGE AND
BI_PAGE_KEYW.ID_PAGE=:id_page ;
END;
IF PART_SITE = 3 THEN
BEGIN
SELECT BI_REF_KEYWORD.ID
FROM DBADMIN.BI_REF_KEYWORD,
DBADMIN.BI_PAGE_KEYW
WHERE BI_REF_KEYWORD.ID_COMP=:ID_KWR AND
BI_REF_KEYWORD.ID=BI_PAGE_KEYW.ID_PAGE AND
BI_PAGE_KEYW.ID_PAGE=:id_page ;
END;
FETCH INTO :ID_KWR;
END;
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=:ID_KWR AND
BI_PAGE.OUT_ID=:ID_PAGE AND
BI_PAGE_ZONE.ID_PAGE=BI_PAGE.ID AND
BI_PAGE_ZONE.ID_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_REK_AUC.ID_AUCTION=BI_AUCTION.ID;
FETCH INTO :ALL_SUM;
/*Ранжируем рекламы для заданной стр, ключ. слова и зоны. Возвращаем
одну из них*/
DECLARE TEMP_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,
BI_PROMO.ID_TYPE_PROMO AS PROMO_TYPE,
BI_AUCTION.ID AS ID_AUC,
BI_REKLAMA.ID_TYPE_REK
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_CAMP,
DBADMIN.INV_ACCOUNTS,
DBADMIN.BI_ZONE,
DBADMIN.BI_PROMO
WHERE ROWNO<=1 AND
BI_REF_KEYWORD.ID=:ID_KWR AND
BI_PAGE.OUT_ID=:ID_PAGE AND
BI_PAGE_ZONE.ID_PAGE=BI_PAGE.ID AND
BI_PAGE_ZONE.ID_ZONE=:ID_ZONE 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_REK_AUC.R_SUM/:ALL_SUM>=BI_REK_AUC.NUM_VIEW/BI_PZ_AUC.AUC_VIEW)
AND
BI_REK_AUC.R_SUM!=0 AND
BI_REKLAMA.ID=BI_REK_AUC.ID_REKLAMA AND
BI_PROMO.ID_REKLAMA(+)=BI_REKLAMA.ID 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
ORDER BY NUM_VIEW ASC;
SET NUM_ROWS=0;
WHILE NUM_ROWS<=$COUNT DO BEGIN
FETCH TEMP_CURSOR INTO :id_reklama,
:id_acc,:id_camp,:r_sum,:pay_type,:promo_type,:ID_AUC, :TYPE_REK;
IF PROMO_TYPE IS NULL THEN SET PROMO_TYPE=0;
SET VAR_OUT=id_reklama&'-'&promo_type&'-'&TYPE_REK;
/* Если необходимо провести стату */
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,ID_AUCTION=:ID_AUC,HOST=:host,REFERER=:referer,IP=:ip,USER=:r_user,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,ID_AUCTION=:ID_AUC,HOST=:host,REFERER=:referer,IP=:ip,USER=:r_user,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(),AMOUNT=: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;
SET NUM_ROWS=NUM_ROWS+1;
END;
IF $RC <> 100 THEN STOP ($RC, 'unexpected error');
RETURN VAR_OUT;
-----------------------------------------------------------------------
Return unexpected error;
What is wrong?
