Why does CREATE TABLE appear to succeed but then fail under DBX?

Question

I've got a DBExpress connection connected to a Firebird database, running Firebird Embedded. Everything works fine so far, but something very strange is going on.

I've got a data module that contains the connection and a handful of TSimpleDataset objects representing different tables. But when I try to add a new table, it appears to work but then fails:

procedure Update(module: TdmDatabase);
const
   SQL = 'CREATE TABLE NEW_TABLE (blah blah blah)';
   SQL2 = 'ALTER TABLE NEW_TABLE ADD CONSTRAINT PK_NEW_TABLE PRIMARY KEY (blah)';
   SQL3 = 'DROP TABLE NEW_TABLE';
begin
   module.connection.ExecuteDirect(SQL);      //succeeds
   module.connection.ExecuteDirect(SQL2);     //succeeds
   try
     module.New_TableDataset.Active := true;  //fails
   except
     module.connection.ExecuteDirect(SQL3);   //succeeds
     raise;
   end;
end;

When I try to create the table, it appears to work, and I can ALTER and DROP it just fine, but when I try to open a dataset that runs a SELECT against it, I get an "invalid table name" error. If I run it under the debugger and kill the program immediately after the CREATE TABLE statement runs, then inspect the database, the new table isn't there.

Anyone know what could cause that, and how I can fix it?

Answer 1

1. That looks like a pure transaction issue, where SQL, SQL2 and SQL3 are executing in one (or in many) transaction. And the transaction remains acive at least after SQL. And New_TableDataset is operating in different transaction, which of course does not see uncommited changes of the first transaction.
2. The issue is not a DataSnap / dbExpress specific, but rather the driver implementation specific. So, good to know what is the driver. And optionally to contact the driver vendor technical support.
3. What to do (pure speculation):
   • Try to surround the command executions into explicit transaction control. That will (may) guarantee, that the transaction is finished after required step.
   • Try to execute COMMIT after SQL and/or SQL2.
   • Try to use TSQLQuery instead of ExecuteDirect. Hopefully all commands will run in a single transaction.

PS: Finally consider to use different dbExpress driver or even data access library.