1

I have a script in which I create a temporary table, that I subsequently want to drop.

I simply create the table, fill it using a INSERT INTO statement, but when it comes to dropping it, the script fails stating that the table is in use.

From reading around it would seem to be caused by the transaction managment, but I'm a little confused.

Here is a little script that reproduces the issue:

CREATE TABLE SCRIPT_TEMP (
  NAME VARCHAR(100) NOT NULL,
  USERNAME VARCHAR(150) NOT NULL);
COMMIT WORK;

INSERT INTO SCRIPT_TEMP (NAME, USERNAME)
SELECT NAME, COALESCE(USERNAME, 'empty')
FROM SALESREPS;
COMMIT WORK;

DROP TABLE SCRIPT_TEMP;
COMMIT WORK;

Or, in order to be easily testable to anyone without a SALESREPS table, use this insert statement :o)

INSERT INTO SCRIPT_TEMP (NAME, USERNAME)
SELECT 'Name 1', 'Username 1'
FROM RDB$DATABASE;
COMMIT WORK;

I fail to see what still holds a reference to the SCRIPT_TEMP table by the time the drop call is made. Why would the script's own transaction block it even after the second COMMIT?

If I split the execution in 2 scripts everything is fine.

What am I missing??

Thanks!!

PS: using Firebird 2.5.2, in case that matters

PPS: my script is a bit more involved than this. THe temp table is filled with table names and related constraints that need to be manipulated, but that's not the issue, that part is working well. And the fact is, the issue I want to resolve is easily reproducible with the code sample here, that I got to while debugging. This SO question seems to be about the exact same problem, but the only answer is not helpful to the problem itself

Community
  • 1
  • 1
Nicolas Bourgogne
  • 53
  • 6
  • Are you executing this with ISQL or with another tool? You could try to upgrade to 2.5.5, I recall some bugs were fixed around this (in 2.5.4 I believe), but I currently don't have time to look up the details. You could also try using a WAIT transaction instead of the default NO WAIT. – Mark Rotteveel Nov 20 '15 at 11:38
  • This works fine for me. `Firebird 2.5.4` Script is executed in `IBExpert`. – Val Marinov Nov 20 '15 at 15:06
  • Thanks to both of you. Running the script in FlameRobin worked. It doesn't work with EMS SQL Manager however, which is my tool of choice for everyday checking. I'll contact them. Cheers! – Nicolas Bourgogne Nov 20 '15 at 15:51

1 Answers1

1

Thanks to Val Marinov for pointing me in the right direction.

It appears my issue is due to my SQL Manager - there must be a setting I'm not aware of.

Script runs as expected in FlameRobin.

Community
  • 1
  • 1
Nicolas Bourgogne
  • 53
  • 6