datareader error?
Posted by: riccardo poli
Date: February 15, 2006 10:42AM

accessing procedure on database (5.0.18) via connector (1.0.7) as result i receive this:

'there is already an open datareader associated with this connection wich must be close first'.

i'm sure all connection are closed.

these asre the procs:

CREATE PROCEDURE `DISTINTA_BASE_COMPONENTI_TREE_DBC_1`(_COD_DISTINTA VARCHAR(18))
COMMENT 'crea l_albero delle sotto-distinte nella distinta base'
proc:
BEGIN
DECLARE temporary_table_exists BOOLEAN DEFAULT FALSE;
DECLARE error BOOLEAN DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error = TRUE;

DROP TEMPORARY TABLE IF EXISTS temp_dbc;
CREATE TEMPORARY TABLE temp_dbc (
COD_ART_CAPO_DISTINTA varchar(18) NOT NULL,
COD_DISTINTA_SEC varchar(18) NOT NULL,
COD_RIF_DISTINTA varchar(18) NOT NULL,
A_V_G char(1) default NULL,
COD_ART varchar(13) NOT NULL,
DESC_DBCOMPO varchar(50) default NULL,
COD_UM char(2) NOT NULL,
TIPO_ART char(2) default NULL,
COD_DISEGNO_CLI varchar(15) default NULL,
COD_DISEGNO_INTERNO varchar(15) default NULL,
QTA decimal(11,4) default NULL,
COD_MAGA char(3) default NULL,
COSTO decimal(11,4) default NULL,
TEMPO_ESECUZIONE decimal(11,4) default NULL,
UNIQUE KEY COD_ART_CAPO_DISTINTA (COD_ART_CAPO_DISTINTA,COD_DISTINTA_SEC,COD_RIF_DISTINTA,COD_ART) );

IF error THEN SELECT 'CREATE TEMPORARY failed'; LEAVE proc; END IF;
SET temporary_table_exists=TRUE;

CALL DISTINTA_BASE_COMPONENTI_TREE_DBC_2(_COD_DISTINTA);
IF error THEN SELECT 'Call DISTINTA_BASE_COMPONENTI_TREE_DBC_2 failed'; LEAVE proc; END IF;

SELECT * FROM temp_dbc;
DROP TEMPORARY TABLE temp_dbc;
END





CREATE PROCEDURE `DISTINTA_BASE_COMPONENTI_TREE_DBC_2`(_COD_DISTINTA VARCHAR(18))
COMMENT 'sub di tree_dbc_1'
proc:
BEGIN
DECLARE _cod_art_capo_distinta varchar(18);
DECLARE _cod_distinta_sec varchar(18);
DECLARE _cod_rif_distinta varchar(18);
DECLARE _a_v_g char(1);
DECLARE _cod_art varchar(13);
DECLARE _desc_dbcompo varchar(50);
DECLARE _cod_um char(2);
DECLARE _tipo_art char(2);
DECLARE _cod_disegno_cli varchar(15);
DECLARE _cod_disegno_interno varchar(15);
DECLARE _qta decimal(11,4);
DECLARE _cod_maga char(3);
DECLARE _costo decimal(11,4);
DECLARE _tempo_esecuzione decimal(11,4);

DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE error BOOLEAN DEFAULT FALSE;
/* estrae solo i dati delle distinte, senza articoli */
DECLARE c CURSOR FOR SELECT cod_art_capo_distinta,cod_distinta_sec,cod_rif_distinta,a_v_g,cod_art,desc_dbcompo,cod_um,tipo_art,cod_disegno_cli,cod_disegno_interno,qta,cod_maga,costo,tempo_esecuzione FROM distinta_base_componenti WHERE cod_art_capo_distinta = _COD_DISTINTA AND cod_distinta_sec <> _COD_DISTINTA;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error = TRUE;

OPEN c;
IF error THEN SELECT 'OPEN failed'; LEAVE proc; END IF;
REPEAT
FETCH c INTO _cod_art_capo_distinta,_cod_distinta_sec,_cod_rif_distinta,_a_v_g,_cod_art,_desc_dbcompo,_cod_um,_tipo_art,_cod_disegno_cli,_cod_disegno_interno,_qta,_cod_maga,_costo,_tempo_esecuzione;
IF error THEN SELECT 'FETCH failed'; CLOSE c; LEAVE proc; END IF;
/* scrive i dati della distinta trovata */
IF done = FALSE THEN
INSERT INTO temp_dbc
(COD_ART_CAPO_DISTINTA,COD_DISTINTA_SEC,COD_RIF_DISTINTA,A_V_G,COD_ART,DESC_DBCOMPO,COD_UM,TIPO_ART,COD_DISEGNO_CLI,COD_DISEGNO_INTERNO,QTA,COD_MAGA,COSTO,TEMPO_ESECUZIONE)
VALUES
(_cod_art_capo_distinta,_cod_distinta_sec,_cod_rif_distinta,_a_v_g,_cod_art,_desc_dbcompo,_cod_um,_tipo_art,_cod_disegno_cli,_cod_disegno_interno,_qta,_cod_maga,_costo,_tempo_esecuzione);
IF error THEN SELECT 'recursive INSERT failed'; CLOSE c; LEAVE proc; END IF;
CALL DISTINTA_BASE_COMPONENTI_TREE_DBC_2(_cod_distinta_sec);
IF error THEN SELECT 'recursive DISTINTA_BASE_COMPONENTI_TREE_DBC_2() failed'; CLOSE c; LEAVE proc; END IF;
END IF;
UNTIL done=TRUE
END REPEAT;
CLOSE c;
IF error THEN SELECT 'First SELECT failed'; LEAVE proc; END IF;
END


thanks

Options: ReplyQuote


Subject
Written By
Posted
datareader error?
February 15, 2006 10:42AM
March 14, 2006 01:01PM
August 23, 2006 10:13AM
August 31, 2006 08:42AM
August 31, 2006 09:39AM
September 01, 2006 03:44AM
September 01, 2006 04:31AM
September 01, 2006 04:42AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.