Subject | RE: [Firebird-Java] OBJECT in use during Foreign key creation |
---|---|
Author | Robert DiFalco |
Post date | 2003-03-26T20:04Z |
I figured out what the problem is, I will also post this to ib-support
since it probably belongs there. It has nothing to do with
dbMetaData.getTables() or transactions, etc. I think it also has nothing
to do with JayBird. Check this out....
If I have a connection to the database, connection "A".
Then another process connects to the database (connection "B").
-- "B" CAN create TABLES, but....
-- "B" can NOT create any foreign keys between tables.
Try this. Bring up two instances of IBExpert or some other management
tool. Have both connect to a Firebird database. Then create two tables
and have the second table have a foreign key to the first table. The
foreign key creation will fail!
Any suggestions for a solution? Is this fixed in FB 1.5?
Basically, I have a peer-to-peer system. One process created some tables
(using a connection pool), and another process created tables. This was
fine. But as soon as one process tried to create a table with a foreign
key, it bailed with:
"unsuccessful metadata update; Object <mytablename> is in use"
Fwiw, these were the statements I was executing:
CREATE TABLE ClassInfo (
id INTEGER NOT NULL,
classname BLOB SUB_TYPE 1 NOT NULL,
strategy BLOB SUB_TYPE 1 NOT NULL,
codebase BLOB SUB_TYPE 1 NOT NULL,
PRIMARY KEY(id) );
CREATE PROCEDURE insertClassInfo(
nId INTEGER,sClassname BLOB SUB_TYPE 1,sStrategy BLOB SUB_TYPE
1,sCodebase BLOB SUB_TYPE 1)
AS BEGIN
INSERT INTO ClassInfo(id,classname,strategy,codebase)
VALUES (:nId,:sClassname,:sStrategy,:sCodebase); END
So far no problem....
CREATE TABLE Named (
id INTEGER NOT NULL,
name VARCHAR(64),
description BLOB SUB_TYPE 1,
version INTEGER NOT NULL,
classInfoId INTEGER NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY (classInfoId) REFERENCES ClassInfo(id) );
Kaplooey!!!
Note that it makes no difference whether or not I create a different
connection for each statement, put the foreign key constraint in a
different ALTER TABLE statement, or whatever. It doesn't matter whether
Connection A is from Java or another program. The only thing that makes
any difference is if connection "B" is THE ONLY CONNECTION TO THE
DATABASE!! Obviously, this cannot be done in a distributed system.
Any suggestions? Am I missing something fundamental? Probably.
Robert
-----Original Message-----
From: Robert DiFalco
Sent: Wednesday, March 26, 2003 9:40 AM
To: Firebird-Java@yahoogroups.com
Subject: RE: [Firebird-Java] OBJECT in use during Foreign key creation
I don't think that is the problem because I do perform a commit after
the call to dbMetaData.getTables(). I've also tried setting the
connection to autocommit but something seems to be keeping a lock on the
relations table.
R.
-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Wednesday, March 26, 2003 1:21 AM
To: Firebird-Java@yahoogroups.com
Subject: RE: [Firebird-Java] OBJECT in use during Foreign key creation
At 06:11 PM 25/03/2003 -0800, you wrote:
you
can do other stuff with/to it...
Helen
Yahoo! Groups Sponsor
To unsubscribe from this group, send an email to:
Firebird-Java-unsubscribe@yahoogroups.com
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
Yahoo! Groups Sponsor
ADVERTISEMENT
To unsubscribe from this group, send an email to:
Firebird-Java-unsubscribe@yahoogroups.com
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
since it probably belongs there. It has nothing to do with
dbMetaData.getTables() or transactions, etc. I think it also has nothing
to do with JayBird. Check this out....
If I have a connection to the database, connection "A".
Then another process connects to the database (connection "B").
-- "B" CAN create TABLES, but....
-- "B" can NOT create any foreign keys between tables.
Try this. Bring up two instances of IBExpert or some other management
tool. Have both connect to a Firebird database. Then create two tables
and have the second table have a foreign key to the first table. The
foreign key creation will fail!
Any suggestions for a solution? Is this fixed in FB 1.5?
Basically, I have a peer-to-peer system. One process created some tables
(using a connection pool), and another process created tables. This was
fine. But as soon as one process tried to create a table with a foreign
key, it bailed with:
"unsuccessful metadata update; Object <mytablename> is in use"
Fwiw, these were the statements I was executing:
CREATE TABLE ClassInfo (
id INTEGER NOT NULL,
classname BLOB SUB_TYPE 1 NOT NULL,
strategy BLOB SUB_TYPE 1 NOT NULL,
codebase BLOB SUB_TYPE 1 NOT NULL,
PRIMARY KEY(id) );
CREATE PROCEDURE insertClassInfo(
nId INTEGER,sClassname BLOB SUB_TYPE 1,sStrategy BLOB SUB_TYPE
1,sCodebase BLOB SUB_TYPE 1)
AS BEGIN
INSERT INTO ClassInfo(id,classname,strategy,codebase)
VALUES (:nId,:sClassname,:sStrategy,:sCodebase); END
So far no problem....
CREATE TABLE Named (
id INTEGER NOT NULL,
name VARCHAR(64),
description BLOB SUB_TYPE 1,
version INTEGER NOT NULL,
classInfoId INTEGER NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY (classInfoId) REFERENCES ClassInfo(id) );
Kaplooey!!!
Note that it makes no difference whether or not I create a different
connection for each statement, put the foreign key constraint in a
different ALTER TABLE statement, or whatever. It doesn't matter whether
Connection A is from Java or another program. The only thing that makes
any difference is if connection "B" is THE ONLY CONNECTION TO THE
DATABASE!! Obviously, this cannot be done in a distributed system.
Any suggestions? Am I missing something fundamental? Probably.
Robert
-----Original Message-----
From: Robert DiFalco
Sent: Wednesday, March 26, 2003 9:40 AM
To: Firebird-Java@yahoogroups.com
Subject: RE: [Firebird-Java] OBJECT in use during Foreign key creation
I don't think that is the problem because I do perform a commit after
the call to dbMetaData.getTables(). I've also tried setting the
connection to autocommit but something seems to be keeping a lock on the
relations table.
R.
-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Wednesday, March 26, 2003 1:21 AM
To: Firebird-Java@yahoogroups.com
Subject: RE: [Firebird-Java] OBJECT in use during Foreign key creation
At 06:11 PM 25/03/2003 -0800, you wrote:
>Actually, with a small test case, I can make this work with JayBird.for
>However, if I add some con.getMetaData() calls in between that query
>the existince of the table name before I create it, I eventually hangon
>one of the calls to dbMetaData.getTables( null, null,You do need to COMMIT each metadata item in its own transaction before
>getPrimaryTableName(), null ). Hmmmm....
you
can do other stuff with/to it...
Helen
Yahoo! Groups Sponsor
To unsubscribe from this group, send an email to:
Firebird-Java-unsubscribe@yahoogroups.com
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
Yahoo! Groups Sponsor
ADVERTISEMENT
To unsubscribe from this group, send an email to:
Firebird-Java-unsubscribe@yahoogroups.com
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.