Dear heLen,

Thanks a lot for your detailed response. Its really
helping me understand this problem.

Regarding the auto commit of DDL statements, its what
I have seen when working with a MySQL or Oracle. The
Firebird experience is a bit different. Its NOT
autocommiting the DDL statements as done in the other
DBs.

> Only in isql and only if autoddl is on.

Yes you are absolutely right. I used isql console for
testing the above scenario. I assumed I will get a
similar behavior when run in Java environment too. As
I'm understanding things now, the autoddl feature is a
isql thing and the way the Java (JDBC) behaves is
different.

> *firebird* doesn't; but the program isql.exe does.

I stand corrected.

> Then there is definitely something in your
> environment that you're not
> telling us about.

Well, what I didn't mention earlier was my
assumptions. I was running the steps with a isql
console and assumed the same behavior in the Java
environment too. Now, I did test the following code:

String createSQL1 = "CREATE TABLE T1 (ID INTEGER NOT
NULL PRIMARY KEY)";
String createSQL = "CREATE TABLE T2 (ID INTEGER NOT
NULL, FOREIGN KEY(ID) REFERENCES T1(ID) ON DELETE
CASCADE)";

Connection conn = DriverManager.getConnection(...);
conn.setAutoCommit(false);

Connection conn1 = DriverManager.getConnection(..);
conn1.setAutoCommit(false);

conn.createStatement().executeUpdate(createSQL1);
conn.commit();
conn.createStatement().executeUpdate(createSQL);
conn.commit();

It throws exception on the second commit(). So, I
suppose, the problem still exists even after the
explicit "commit" is being done.

Thanks once again for your detailed response and your
valuable information.

Thanks,
Suresh.K.V.

--- Helen Borrie <helebor@...> wrote: > At
09:20 AM 28/04/2004 +0000, you wrote:

>
> >Anyways, normally the create statement(or any ddl
> statement for that
> >matter) are auto-commit by nature.
>
> Really? Where did you get that from? AutoDDL is an
> isql setting. It's on
> by default when you start an isql session, or if you
> issue an isql command
> from a command shell; but in isql (and in scripts
> that are run in isql)
> you can vary that behaviour by using SET AUTODDL
> OFF.
>
> What happens under the hood is that isql starts a
> read committed
> transaction for each DDL command it receives, and
> commits it upon execution
> of the single statement (unless there is an
> exception). It's a
> client-initiated thing, just like all transactions.
>
> If you are also running DML statements in the same
> session, they are in a
> separate transaction (by default, in snapshot
> isolation). You have to
> commit DML yourself.
>
> >I checked the behavior in firebird and it works
> this way only.
>
> Only in isql and only if autoddl is on.
>
> >In fact, the firebird offers
> >ways to 'disable' this behavior too (by doing a set
> AutoDDL, which
> >toggles the auto commit of ddl statements).
>
> *firebird* doesn't; but the program isql.exe does.
>
> If you are issuing a DDL command as a regular DSQL
> statement (as you are
> doing in your Java code) then there is *no* autoddl,
> since autoddl is an
> isql thing, it's not part of the DSQL statement set.
>
> >So, I'm not sure whether commit will help solve
> this problem.
>
> >Although I tried it (more out of curiosity) and got
> the same "object
> >is in use" error message (as expected).
>
> Then there is definitely something in your
> environment that you're not
> telling us about.
>
> /heLen
>
>
>
>

____________________________________________________________
Yahoo! Messenger - Communicate instantly..."Ping"
your friends today! Download Messenger Now
http://uk.messenger.yahoo.com/download/index.html