Marina, I cannot see where there could be any caching. Here is the steps that I use to reproduce the problem.
Start Netbeans (NB). Start Java DB from NB's Tools->Java DB->Start, Right click on a "build.xml" file and run my "setup-database" target, Run my "setup" application. It will fail with the error that I gave.
If I do this instead: Start NB, Start Java DB, run "setup-database", Stop Java DB, Start Java DB, Run "setup", then the application will work correctly.
Here is a snippet of the "setup-database" target"
<target name="load-core" depends="init">
<sql driver="org.apache.derby.jdbc.ClientDriver"
classpath="${j2ee.appclient.tool.runtime}"
url="jdbc:derby://localhost:${derbyport}/csemdb"
userid="csem"
password="csem"
print="true"
onerror="continue"
caching="no"
autocommit="true">
<![CDATA[
CREATE SCHEMA CSEM_V1;
SET SCHEMA CSEM_V1;
CREATE TABLE AGENT_MANAGED_HARDWARE
(
ID INTEGER NOT NULL PRIMARY KEY,
SNMP_DEVICE_ID INTEGER
);
...
CREATE TABLE HARDWARE
(
ID INTEGER NOT NULL PRIMARY KEY,
SERIAL_NUMBER VARCHAR(16),
MODEL VARCHAR(255),
HARDWARE_VERSION VARCHAR(16),
HARDWARE_TYPE INTEGER
);
...
CREATE TABLE HARDWARE_SOFTWARE
(
HARDWARE_ID INTEGER NOT NULL,
SOFTWARE_ID INTEGER NOT NULL,
CONSTRAINT HARDWARE_SOFTWARE_PK PRIMARY KEY (HARDWARE_ID,SOFTWARE_ID)
);
...
CREATE TABLE SOFTWARE
(
ID INTEGER NOT NULL PRIMARY KEY,
SOFTWARE_VERSION VARCHAR(16) NOT NULL,
SOFTWARE_TYPE INTEGER NOT NULL
);
...
ALTER TABLE HARDWARE_SOFTWARE
ADD CONSTRAINT HARDWARE_SOFTWARE_FK_1
FOREIGN KEY (HARDWARE_ID)
REFERENCES DEVICE_ENTITY(ID);
ALTER TABLE HARDWARE
ADD CONSTRAINT HARDWARE_FK_1
FOREIGN KEY (ID)
REFERENCES DEVICE_ENTITY(ID);
ALTER TABLE HARDWARE_SOFTWARE
ADD CONSTRAINT HARDWARE_SOFTWARE_FK_2
FOREIGN KEY (SOFTWARE_ID)
REFERENCES DEVICE_ENTITY(ID);
...
Obviously I have not put all of the schema creation here but you get the idea. The only thing that I can think of is that the "sql" task of ANT is not closing a connection or something. But is still disconcerting that without changing a line of code, but by stopping the database server process and restarting it, that the application works.
I will try to figure out more ...
[Message sent by forum member 'bbergquist' (bbergquist)]
http://forums.java.net/jive/thread.jspa?messageID=233178