insert fail after table creation

[phelz]

I have a strange behaviour in a procedure i'm writing for automatic update of a database.
If i run multiple query one after the other inside a transaction it seems i can't insert data in a table create within the same transaction

this is an extremely simplified code to show what i'm talking about, i'm testing this on a firebird 2.5 database:

Code: Select all

procedure databaseUpdate;
var
  DB: TUniConnection;
  Q: TUniQuery;
begin
  DB := TUniConnection.Create(nil);
  Q := TUniQuery.Create(nil);
  Q.Connection := DB;
  try
    // some code here to initialize database connection parameters: provider, host, database, user, password
    DB.Open;
    DB.StartTransaction;
	
	Q.SQL.Clear;
	Q.SQL.Add('CREATE TABLE TEST_TABLE (ID INTEGER, FIELD_01 INTEGER)');
	Q.Execute;
	
	Q.SQL.Clear;
	Q.SQL.Add('INSERT INTO TEST_TABLE (ID, FIELD_01) VALUES (1, 117)');
	Q.Execute; // <== here comes the error
	
	Q.Commit;
  finally
    Q.Close;
	Q.Free;
	DB.Close;
	DB.Free;
  end;
end;

when executing the code, i get this error when the program try to insert some data in the created table:

Code: Select all

Dynamic SQL Error
SQL error code = -204
Table unknown
TEST_TABLE
At line 1, column 14

it can't fine the table, but the table has just been created in the previous script (and i can see it if i check the database with an external tool)

i think it has something to do with transaction management, does anyone has a clue about this?

[invent]

Hello,

you're right, the problem is the transaction control. Take away StartTransaction and Commit and the code works.

I thought about the same problem with InterBase and I found no reason to make a transaction around "Create Table".

And imho it's logical, because every change after StartTransaction is send to the Database with Commit.

BTW: Are you sure that you saw the table with external tools before commit? Or did you checked this after closing your program?

Kind regards,
Gerd Brinkmann
invent GmbH

[phelz]

Thak you for your suggestion, removing transaction control solved the problem and now everything works as expected.

I didn't write it clearly, but i saw the table created with an external tool after closing my program, not before commit

Again thank you