Subject Re: [firebird-support] Re: [] Creating unique column entries
Author Helen Borrie
At 11:35 AM 1/10/2004 -0400, you wrote:

>Ok, let me show my ignorance, here. :-) What is DML?

SQL language is split into two kinds of statements: those that define your
schema (data definition language, or DDL) and those that manipulate data
(data manipulation language, or DML).

When you use a DDL statement (like CREATE, ALTER, etc.), the operation
performed is actually DML on the system tables - Firebird stores its entire
schema in ordinary Firebird tables. So you can understand why its
necessary to complete the transaction that creates the new records (or
alters existing ones) in the system tables, before you start trying to
stuff data into them with your own user DML.

> >The difference in behaviour you observe is probably due to Jaybird using
> >ServerAutocommit on each statement; whereas you are trying to run a script
> >in isql without autoddl.
> >
> >You can force isql to do ServerAutocommit on the DDL statements *only* by
> >starting the script with SET AUTODDL ON; Actually, I never rely on autoddl
> >- probably just superstition - so I explicitly call COMMIT on any object in
> >the rare event that I want to follow up a DDL operation with a DML
> >operation on the object.
> >
>So you're saying that after each statement, I should add a "commit"?

No. COMMIT is a statement in its own right, that works in ISQL and in
scripts (+ one other place where you won't encounter it). A data access
layer like Jaybird implements a method that surfaces in Java an API
function call that does the same thing as COMMIT achieves in isql or a
script. The purpose of COMMIT is to end a transaction and make the pending
work that you posted in your statements permanent and visible to other

When you are performing DDL - yes, if you don't really understand what you
are doing then it won't hurt to commit after every DDL statement until you
get your head around which sorts of DDL can be committed in batches. When
performing DML (SELECT, INSERT, UPDATE, DELETE) you often don't want to
commit after every statement.

Just be safe and keep DDL and DML separate.

>Whenever I put acommit in my sql code, I get an answer from jaybird
>saying that I should have a valid transaction. I guess there's some
>setup that needs to occure on the Java side before I can use this properly.

Ask in the Jaybird list. From observation, I think Jaybird makes a lot of
use of autocommit transactions.

>Sorry, I have no idea what you are talking about, here. :-( I'm pretty
>new to Firebird. It seems like I should know what DML, DDL,
>ServerAutocomit and all that good stuff is.

Yes, it helps a lot to brief yourself on what's really going on.