AW: ArrayIndexOutOfBoundsException when creating view

Hi,

> Did you get the error as soon as you ran the CREATE VIEW command?  Did 
> you alter one of the tables the view is based on?

The error ocuured as soon as i ran the CREATE VIEW command. I did not
alter any involved table.

> Does only the 'SELECT' part of the query work as expected?  eg. the 
> following query;
> 
> SELECT 	DA.SCHLUESSEL AS SCHLUESSEL,
> 		DA.BENUTZERGRUPPE AS BENUTZERGRUPPE,
> 		DA.USERNAME AS USERNAME,
> 		DB.KUNDE AS KUNDE,
> 		DB.STANDZEIT AS STANDZEIT,
> 		DB.EINSATZDAUER AS EINSATZDAUER,
> 		V.VERSION AS VERSION
> FROM	DATEN_ALLG DA, DATEN_BEARB DB, VERSION V
> WHERE 	DA.SCHLUESSEL=DB.DATEN_ALLG_FK
> AND 	DA.SCHLUESSEL=V.REFNR
> AND 	DB.DATEN_ALLG_FK=V.REFNR;

the "SELECT...." part for itself works without problems...

> Could you explain layout of the 'DATEN_ALLG', 'DATEN_BEARB' and 
> 'VERSION' tables so I can try and reproduce the error.

The tables have the following layout:

---------------------------------------------------------------------
CREATE TABLE DATEN_ALLG
(
	"SCHLUESSEL" CHARACTER (29) NOT NULL,
	"BENUTZERGRUPPE" VARCHAR (16) NOT NULL,
	"USERNAME" VARCHAR (16) NOT NULL,
	PRIMARY KEY (SCHLUESSEL)
);

CREATE TABLE DATEN_BEARB
(
	"SCHLUESSEL" INTEGER DEFAULT UNIQUEKEY('DATEN_BEARB') NOT NULL,
	"DATEN_ALLG_FK" CHARACTER (29) NOT NULL,
	"KUNDE" VARCHAR (128),
	"STANDZEIT" SMALLINT,
	"EINSATZDAUER" SMALLINT,
	PRIMARY KEY (SCHLUESSEL),
	CONSTRAINT C02_DATEN_ALLG_FK FOREIGN KEY (DATEN_ALLG_FK) REFERENCES
DATEN_ALLG (SCHLUESSEL) ON DELETE CASCADE
);

CREATE TABLE VERSION
(
	"REFNR" CHARACTER (29) NOT NULL,
	"VERSION" INTEGER NOT NULL,
	"CHANGEDATE" TIMESTAMP NOT NULL,
	PRIMARY KEY (REFNR),
	CONSTRAINT C_CHECK_UNIQUE UNIQUE (REFNR, VERSION, CHANGEDATE)
);

CREATE VIEW V_DATEN_DBA AS
SELECT 	DA.SCHLUESSEL AS SCHLUESSEL,
		DA.BENUTZERGRUPPE AS BENUTZERGRUPPE,
		DA.USERNAME AS USERNAME,
		DB.KUNDE AS KUNDE,
		DB.STANDZEIT AS STANDZEIT,
		DB.EINSATZDAUER AS EINSATZDAUER,
		V.VERSION AS VERSION
FROM	DATEN_ALLG DA, DATEN_BEARB DB, VERSION V
WHERE 	DA.SCHLUESSEL=DB.DATEN_ALLG_FK
AND 	DA.SCHLUESSEL=V.REFNR
AND 	DB.DATEN_ALLG_FK=V.REFNR;
---------------------------------------------------------------------

I tried this with the default schema and with a schema generated by myself.
Didn't work either... Creating a view for a single table (e.g. for
DATEN_ALLG)
works, but creating a view for more two tables or more doesn't work anymore.
Debugging gives me a list [-1,-1,-1,-1,...] for table mapping (i think, this
is the mapping from the virtual table to the physical table references?)

Hope, this helps...

> Klaudio Kaiser wrote:
> > Hi,
> > 
> > while creating a view with mckoi (1.0.3 in Server-mode) i got an
> > ArrayIndexOutOfBoundsException.
> > 
> > SQL-Statement:
> > 
> > CREATE VIEW V_DATEN_DBA AS
> > 	SELECT 	DA.SCHLUESSEL AS SCHLUESSEL,
> > 			DA.BENUTZERGRUPPE AS BENUTZERGRUPPE,
> > 			DA.USERNAME AS USERNAME,
> > 			DB.KUNDE AS KUNDE,
> > 			DB.STANDZEIT AS STANDZEIT,
> > 			DB.EINSATZDAUER AS EINSATZDAUER,
> > 			V.VERSION AS VERSION,
> > 	FROM		DATEN_ALLG DA, DATEN_BEARB DB, VERSION V
> > 	WHERE 	DA.SCHLUESSEL=DB.DATEN_ALLG_FK
> > 	AND 		DA.SCHLUESSEL=V.REFNR
> > 	AND 		DB.DATEN_ALLG_FK=V.REFNR;
> > 
> > I debugged a little and it seems to be a bug in method 
> setColumnMap(col_map,
> > new_column_names) in the QueryPlan-Class.
> > Is there a quick fix for this problem? I hope the view 
> definition is ok.
> > 
> > Greetings
> >   Klaudio
> 
> 
> ---------------------------------------------------------------
> Mckoi SQL Database mailing list  http://www.mckoi.com/database/
> To unsubscribe, send a message to [email protected]

Klaudio Kaiser
Active Group GmbH
Hornbergstraße 49
70794 Filderstadt
Tel +49 (711) 707094-77
Fax +49 (711) 707094-89
mailto:[email protected]
http://www.active-group.de
 


____________
Virus checked by G DATA AntiVirusKit
Version: AVK 15.0.811 from 02.11.2004
Virus news: www.antiviruslab.com



---------------------------------------------------------------
Mckoi SQL Database mailing list  http://www.mckoi.com/database/
To unsubscribe, send a message to [email protected]