Hi,

> I have run into a peculiar issue when performing updates to an existing
> database. I am using embedded FB 1.5 and IBO 4.3a and have built my own
> little update module. After performing a DDL statement to add a field to a
> table, then following it with a DML statement to set the field to a

certain

> value, the query actually sets the wrong field. Unfortunately, it sets the
> ID field of the record which is the unique key generated by a generator
> instead of the new field called WOStatus.

Commit the ALTER, then do the DML statement.

That should fix it.

> As a test, I added a connection Close/ReOpen sequence between statements

to

> disconnect/reconnect to the database and it works fine that way. It's only
> when the two statements are performed without closing the connection to

the

> database in between each one. I also tried using both a TIB_Query and
> TIB_DSQL and both performed the same way.
>
> Adding the connection close/open part solves some of the problem, however,
> if I execute a script using multiple DDL statements and add the DML
> statement to update the field at the end, it changes the wrong field. I
> assume this is because the connection isn't closed between each script
> command.
>
> Is this as designed or could it be a bug? Actually, I'm not even sure if

Well, kind of " as designed " ... -> mixing DDL and DML isn't really
supported. IMO, it should simply raise an exception if you DML something
into not yet comitted DDL. That would solve the immediate problem of
unpredictable results and it would be clear it's not supported.

> it's because of FB or IBO so I thought I'd start here with FB. I know

Helen

> (hint) hangs out in both. :)

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com