At 02:59 PM 30/09/2004 -0400, you wrote:

>Laurent Duperval wrote:
>
> >No. I tested it under DbVisualizer and running the straight SQL query
> >produces the same result.... Actually, I notice now that even using
> >isql.exe does not cause a problem. I thought it was a problem with my
> >syntax so I tried:
> >
> >CREATE TABLE Vehicle
> >(
> > Id char(10),
> > Name varchar(32) NOT NULL ,
> > CONSTRAINT PK_Vehicle PRIMARY KEY (Id),
> > CONSTRAINT UNQ_VEHICLE_NAME UNIQUE (NAME)
> >);
> >
> >but it still accepts duplicate names. It isn't a Jaybird problem.
> >
>
>Ok, I investigated this further. To recap, I'm having an issue where
>Firebird lets me add duplicate values for columns declared as unique --
>I've even seen it for primary keys. It looks like this is a Jaybird
>problem after all. Here's what I tried:
>
>- If I run the script that creates the tables in Jaybird, then I can add
>duplicates with IBOConsole, with my java application and with isql.exe.
>- If I run the script to create the tables in isql.exe, I cannot add
>duplicates, whether I'm using isql.exe, my Java application or IBOConsole.
>
>Has anyone else noticed something like this?
>
>I am using Firebird 1.5.1 on Win2K. I am also Jaybird 1.5.1 (although
>the manifest says "Implementation-Version: 1.5.0JDK_1.4 (build:
>CVSTag=HEAD date=20040916 2348)");

Rule number one is DON'T TRY TO PERFORM DML ON AN UNCOMMITTED OBJECT!! The
results will be at best, unpredictable and, often, will cause data to be
logically corrupted.

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.

FYI, SET AUTODDL ON forces isql to treat DDL statements in scripts the same
way it treats interactive DDL commands: it creates a discrete transaction
for each DDL operation with the equivalent of Jaybird's ServerAutocommit.

Don't forget to commit your DML statements.

./heLen