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:

>Actually, with a small test case, I can make this work with JayBird.
>However, if I add some con.getMetaData() calls in between that query

for

>the existince of the table name before I create it, I eventually hang

on

>one of the calls to dbMetaData.getTables( null, null,
>getPrimaryTableName(), null ). Hmmmm....

You do need to COMMIT each metadata item in its own transaction before
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.