Create table only if it doesn't exist
lazarus / 16-Databases

----------------------------------------------------------------------
DonSanchezDK · 2014-10-16 13:09

Hi all,
I have tried to create a table only if it doesn't exist, using a "not
exists" statement, and the correct System Tables. The code seems to
run without errors in Flamerobin, but the "CREATE TABLE.." part of the
code is never run and the desired table is not created even if it
doesn't exist.

I am using Flamerobin to execute the following DBQ:

Code: [Select] SET TERM !! ;
EXECUTE BLOCK AS BEGIN
if (not exists(SELECT 1 FROM rdb$relations WHERE rdb$relation_name =
'AAA'))
then execute statement
'CREATE TABLE AAA
(
  BOREHOLENO VARCHAR(10) not null,
  DRILLDEPTH FLOAT,
  ELEVATION DECIMAL,
  XUTM DECIMAL,
  YUTM DECIMAL,
  TOP FLOAT not null,
  BOTTOM FLOAT,
  ROCKSYMBOL VARCHAR(2),
  Auto_Q_group INTEGER,
  Final_Q_group INTEGER
);';
END!!
SET TERM; !!
Hope somebody can help me with this.

----------------------------------------------------------------------
BigChimp · 2014-10-16 13:23

Make sure you commit the transaction would be my first guess...

----------------------------------------------------------------------
DonSanchezDK · 2014-10-16 17:01

Hey BigChimp

Well I have tried to search for how to use a COMMIT statement and as
far as I can understand the transaction needs to be called outside the
stored procedure (http://forums.devshed.com/firebird-sql-
development-61/commit-stored-procedure-174130.html). I have tried this
and Flamerobin simply states that "Transaction committed", but the
table still isn't created. Am I using the COMMIT statement in the
wrong place?

I added it as follows:
Code: [Select]SET TERM !! ;
EXECUTE BLOCK AS BEGIN
if (not exists(SELECT 1 FROM rdb$relations WHERE rdb$relation_name =
'AAA'))
then execute statement
'CREATE TABLE AAA
(
  BOREHOLENO VARCHAR(10) not null,
  DRILLDEPTH FLOAT,
  ELEVATION DECIMAL,
  XUTM DECIMAL,
  YUTM DECIMAL,
  TOP FLOAT not null,
  BOTTOM FLOAT,
  ROCKSYMBOL VARCHAR(2),
  Auto_Q_group INTEGER,
  Final_Q_group INTEGER
);';
END!!
SET TERM; !!
COMMIT;

----------------------------------------------------------------------
BigChimp · 2014-10-16 17:05

In Flamerobin, run your statement:
Code: [Select]SET TERM !! ;
EXECUTE BLOCK AS BEGIN
if (not exists(SELECT 1 FROM rdb$relations WHERE rdb$relation_name =
'AAA'))
then execute statement
'CREATE TABLE AAA
(
  BOREHOLENO VARCHAR(10) not null,
  DRILLDEPTH FLOAT,
  ELEVATION DECIMAL,
  XUTM DECIMAL,
  YUTM DECIMAL,
  TOP FLOAT not null,
  BOTTOM FLOAT,
  ROCKSYMBOL VARCHAR(2),
  Auto_Q_group INTEGER,
  Final_Q_group INTEGER
);';
END!!
SET TERM; !!
by pressing the run statement button (F4) then
Commit transaction (F5)
... or the equivalent Statement/... menu commands
then click Tables then right-click, refresh.

Works for me firebird embedded 2.5.x

----------------------------------------------------------------------
rvk · 2014-10-16 17:09

I don't see a refresh for the table-definition in FlameRobin. If you
disconnect from your database and reconnect to it... is your table
still not created? (For me it is)

Quote from: BigChimp on October 16, 2014, 07:05:46 pmthen click Tables
then right-click, refresh.
Mmm, I'm don't have a refresh when doing a right-click when on Tables
in FlameRobin 0.9.2.1850. (Only Create new...)

(B.T.W. The "COMMIT;" does its work. In that case you don't have to
use F4 and F5)

Edit: Aaaah.... FlameRobin 0.9.3 does have a Refresh option in the
right-click on tables !!

----------------------------------------------------------------------
DonSanchezDK · 2014-10-16 18:16

I tried clicking the COMMIT button and in fact it did the job and
created the table named "AAA".

Furthermore, the COMMIT statement does work as long as i refresh.
Since the version of Flamerobin that I am using doesn't have a refresh
button (not even when i right click on "tables) I just have to
disconnect and then connect to refresh..

Thanks for the help all !