David Garamond wrote:

> Paul Reeves wrote:
>
>>>yes, that _is_ my problem. why doesn't it fail after the table is
>>>dropped and recreated??? i am deliberately inserting the value "1" so
>>>the second insert would be rejected by the unique index. but after
>>>DROP+CREATE, i can insert duplicate rows.
>>
>>You might try committing after the drop and the create.
>
>
> thanks, paul. i originally did a DROP+CREATE+COMMIT. it turns out that i
> need a DROP+COMMIT+CREATE+COMMIT to get things right.
>

Yeah - that's what I meant - but I can see that it could be read the
other way.

>
>
>>However, the usual practice with DDL
>>statements is to commit after each one is executed.
>
>
> thanks a lot for pointing this out. i am seeing this will be a trap for
> many beginners. just a suggestion, if weird things happen anyway when
> two/more DDL statements are combined, perhaps all DDL's should be made
> autocommitting [by default]?
>

Well, they are, kind of. DDL is usually stored and executed from
scripts. ISQL is the tool that is used to run the scripts and in ISQL
autocommit is on for DDL statements.

Users that use the api (ie you, via kinterbasedb) are expected to be
more savvy about these things. How an api wrapper should handle this is
debatable. The BDE (bless it) just defaulted to autocommit for everything.

A well written component set should actually pick up that the statement
is DDL. There is an api call available that reports just what kind of
statement is being executed and a property could be added to either the
database or transaction class to do autocommit on DDL.


Paul
--

Paul Reeves
http://www.ibphoenix.com
Supporting users of Firebird and InterBase