db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Piet Blok" <pbh...@wanadoo.nl>
Subject Table not found: a bug?
Date Mon, 08 May 2006 11:18:39 GMT
Hi,

I encountered a strange problem when trying to do the following:

Create a schema
Create a table
Create a view

Drop the view
Drop the table
Drop the schema

The first time I execute this sequence all goes well.

The second time however when I try to execute the code, it fails on the drop 
view statement:
ERROR X0X05: Table 'TEST_SCHEMA.V1' does not exist.

When I leave out the create and drop of a view, all goes well, so it seems 
to have something to do with the view.


The following ij script demonstrates the problem:

-- *******************************************************
-- Setup connection
-- *******************************************************
connect 'jdbc:derby://localhost/TestData;create=true;';
-- *******************************************************
-- The first time
-- *******************************************************
CREATE SCHEMA TEST_SCHEMA;
CREATE TABLE TEST_SCHEMA.T1 (TABLE_COLUMN LONG VARCHAR);
CREATE VIEW TEST_SCHEMA.V1 AS SELECT TABLE_COLUMN AS VIEW_COLUMN FROM 
TEST_SCHEMA.T1;

DROP VIEW TEST_SCHEMA.V1;
DROP TABLE TEST_SCHEMA.T1;
DROP SCHEMA TEST_SCHEMA RESTRICT;
-- *******************************************************
-- The second time
-- *******************************************************
CREATE SCHEMA TEST_SCHEMA;
CREATE TABLE TEST_SCHEMA.T1 (TABLE_COLUMN LONG VARCHAR);
CREATE VIEW TEST_SCHEMA.V1 AS SELECT TABLE_COLUMN AS VIEW_COLUMN FROM 
TEST_SCHEMA.T1;

DROP VIEW TEST_SCHEMA.V1; -- This statement fails
DROP TABLE TEST_SCHEMA.T1;
DROP SCHEMA TEST_SCHEMA RESTRICT;
-- *******************************************************
-- Cleanup
-- *******************************************************
disconnect;
connect 'jdbc:derby://localhost/TestData;shutdown=true;';

I get the following console output from ij:

ij version 10.1
ij> -- *******************************************************
-- Setup connection
-- *******************************************************
connect 'jdbc:derby://localhost/TestData;create=true;';
ij> -- *******************************************************
-- The first time
-- *******************************************************
CREATE SCHEMA TEST_SCHEMA;
0 rows inserted/updated/deleted
ij> CREATE TABLE TEST_SCHEMA.T1 (TABLE_COLUMN LONG VARCHAR);
0 rows inserted/updated/deleted
ij> CREATE VIEW TEST_SCHEMA.V1 AS SELECT TABLE_COLUMN AS VIEW_COLUMN FROM 
TEST_SCHEMA.T1;
0 rows inserted/updated/deleted
ij> DROP VIEW TEST_SCHEMA.V1;
0 rows inserted/updated/deleted
ij> DROP TABLE TEST_SCHEMA.T1;
0 rows inserted/updated/deleted
ij> DROP SCHEMA TEST_SCHEMA RESTRICT;
0 rows inserted/updated/deleted
ij> -- *******************************************************
-- The second time
-- *******************************************************
CREATE SCHEMA TEST_SCHEMA;
0 rows inserted/updated/deleted
ij> CREATE TABLE TEST_SCHEMA.T1 (TABLE_COLUMN LONG VARCHAR);
0 rows inserted/updated/deleted
ij> CREATE VIEW TEST_SCHEMA.V1 AS SELECT TABLE_COLUMN AS VIEW_COLUMN FROM 
TEST_SCHEMA.T1;
0 rows inserted/updated/deleted
ij> DROP VIEW TEST_SCHEMA.V1;
ERROR X0X05: Table 'TEST_SCHEMA.V1' does not exist.
ij> -- This statement fails
DROP TABLE TEST_SCHEMA.T1;
ERROR X0Y23: Operation 'DROP TABLE' cannot be performed on object 'T1' 
because VIEW 'V1' is dependent on that object.
ij> DROP SCHEMA TEST_SCHEMA RESTRICT;
ERROR X0Y54: Schema 'TEST_SCHEMA' cannot be dropped because it is not empty.
ij> -- *******************************************************
-- Cleanup
-- *******************************************************
disconnect;
ij> connect 'jdbc:derby://localhost/TestData;shutdown=true;';
ERROR 08006: DERBY SQL error: SQLCODE: -1, SQLSTATE: 08006, SQLERRMC: 
Database 'TestData' shutdown.
ij>

This is the sysinfo output:

------------------ Java Information ------------------
Java Version:    1.5.0_06
Java Vendor:     Sun Microsystems Inc.
Java home:       C:\Program Files\Java\jre1.5.0_06
Java classpath: 
.;C:\PROGRA~1\JMF21~1.1E\lib\sound.jar;C:\PROGRA~1\JMF21~1.1E\lib\jmf.jar;C:\PROGRA~1\JMF21~1.1E\lib;C:\WINDOWS\java\classes;C:\WorkSpace\PB

3.0\bin;C:\WorkSpace\Filipe\bin;C:\Documents and Settings\Piet\My 
Documents\Resources\Jars\ImgrRdr130.jar;C:\Documents and Settings\Piet\My 
Documents\Resources\Jars\lucene-1.4.1.jar;C:\Documents and Settings\Piet\My 
Documents\Resources\Jars\Multivalent20040415.jar;C:\Workspace\Database\bin;
OS name:         Windows XP
OS architecture: x86
OS version:      5.1
Java user name:  Piet
Java user home:  C:\Documents and Settings\Piet
Java user dir:   C:\StickFolder
java.specification.name: Java Platform API Specification
java.specification.version: 1.5
--------- Derby Information --------
JRE - JDBC: J2SE 5.0 - JDBC 3.0
[/org/apache/derby/info/DBMS.properties] 10.1.2.1 - (330608)
[/org/apache/derby/info/tools.properties] 10.1.2.1 - (330608)
[/org/apache/derby/info/net.properties] 10.1.2.1 - (330608)
[/org/apache/derby/info/dnc.properties] 10.1.2.1 - (330608)
------------------------------------------------------
----------------- Locale Information -----------------
Current Locale :  [Nederlands/Nederland [nl_NL]]
Found support for locale: [de_DE]
  version: 10.1.2.1 - (330608)
Found support for locale: [es]
  version: 10.1.2.1 - (330608)
Found support for locale: [fr]
  version: 10.1.2.1 - (330608)
Found support for locale: [it]
  version: 10.1.2.1 - (330608)
Found support for locale: [ja_JP]
  version: 10.1.2.1 - (330608)
Found support for locale: [ko_KR]
  version: 10.1.2.1 - (330608)
Found support for locale: [pt_BR]
  version: 10.1.2.1 - (330608)
Found support for locale: [zh_CN]
  version: 10.1.2.1 - (330608)
Found support for locale: [zh_TW]
  version: 10.1.2.1 - (330608)
------------------------------------------------------

Piet Blok



Mime
View raw message