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
