Recent

Author Topic: Test if a table exists in Firebird?  (Read 21100 times)

cov

  • Full Member
  • ***
  • Posts: 240
Test if a table exists in Firebird?
« on: April 08, 2013, 02:22:44 pm »
I'm feeling my way around Firebird.

I need to check if a table exists in my newly created Database.

Googling gives me the following suggestion, but I'm not really sure how to use this from Lazarus:

Code: [Select]
EXECUTE BLOCK AS BEGIN
if (exists(select 1 from RDB$PROCEDURES where rdb$Procedure_name = 'SEL_EMPLOYEE')) then
execute statement 'drop procedure SEL_EMPLOYEE;';
END^

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Test if a table exists in Firebird?
« Reply #1 on: April 08, 2013, 02:54:35 pm »
Well... it seems like that code checks for a stored procedure, not a table, and then drops it.

See here though:
http://wiki.lazarus.freepascal.org/Database_metadata#Schema_information
which links to the documentation
http://www.freepascal.org/docs-html/fcl/sqldb/retrievingschemainformation.html

Looks like TSQLConnection.GetTableNames is a likely candidate...
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

cov

  • Full Member
  • ***
  • Posts: 240
Re: Test if a table exists in Firebird?
« Reply #2 on: April 08, 2013, 03:42:51 pm »
Thanks, Big Chimp.

Yes that was one of many suggestions obtained from Google; I should have noticed the 'DROP' SQL command before I pasted it.

I'll try the 'GetTableNames' method. Thank you.

cov

  • Full Member
  • ***
  • Posts: 240
Re: Test if a table exists in Firebird?
« Reply #3 on: April 08, 2013, 05:17:49 pm »
I'm getting an error:

-unable to complete network request to host: "localhost"
-Failed to establish a connection.

I created the Database ok using IBConnection1.Databasename:='localhost:C:\Firebird\test.fdb'

Also, ping localhost gives me four replies:
Reply from 127.0.0.1: bytes=32 time<1ms TTL=128
Reply from 127.0.0.1: bytes=32 time=2ms TTL=128
Reply from 127.0.0.1: bytes=32 time<1ms TTL=128
Reply from 127.0.0.1: bytes=32 time<1ms TTL=128

cov

  • Full Member
  • ***
  • Posts: 240
Re: Test if a table exists in Firebird?
« Reply #4 on: April 08, 2013, 07:10:21 pm »
Sorry, Firebird wasn't started.  :-[

cov

  • Full Member
  • ***
  • Posts: 240
Re: Test if a table exists in Firebird?
« Reply #5 on: April 09, 2013, 11:26:28 pm »
Looks like TSQLConnection.GetTableNames is a likely candidate...

GetTableNames returned a bunch of tables (more than 32), with names like 'MON$DATABASE' and 'MON$IO_STATS'.

otorres

  • Jr. Member
  • **
  • Posts: 94
Re: Test if a table exists in Firebird?
« Reply #6 on: April 10, 2013, 02:53:09 am »
yeah, these method return all tables, user tables ans system tables.

Zoran

  • Hero Member
  • *****
  • Posts: 1665
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: Test if a table exists in Firebird?
« Reply #7 on: April 10, 2013, 06:39:38 am »
According to documentation, GetTableNames has a boolean parameter which determines if system tables will be included. See: http://www.freepascal.org/docs-html/fcl/sqldb/tsqlconnection.gettablenames.html

cov

  • Full Member
  • ***
  • Posts: 240
Re: Test if a table exists in Firebird?
« Reply #8 on: April 10, 2013, 08:42:43 am »
Thanks, guys.

I've managed to find that my tables don't exist in the database. However...

Code: [Select]
SQLQuery1.SQL.Add('CREATE TABLE PATHS(ID INTEGER NOT NULL UNIQUE PRIMARY KEY, PATH VARCHAR(800),INCEPTION DATE,UPDATE DATE,ISSUED DATE)');
SQLQuery1.ExecSQL;

...produces an error:

Code: [Select]
IBConnection1 : PrepareStatement:
-Dynamic SQL Error
-SQL Error code = -104
-Token unknown - line 1, column 93
-UPDATE

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Test if a table exists in Firebird?
« Reply #9 on: April 10, 2013, 10:32:14 am »
Well, that's sad  ;D

If you read the error message, you'll notice there's something wrong about that UPDATE word (it even specifies the position).

Now UPDATE is likely a reserved word - DON'T USE IT IN TABLE/COLUMN/WHATEVER NAMES.

Apart from that, does Firebird allow you to create column names with spaces in it? Edit: it seems so because it's not complaining about the previous INCEPTION DATE. I would avoid spaces though, suggest underscores instead.

When I get these errors in programming, I always run the SQL in a tool like Flamerobin to see whether it works there...

Also, it helps if you post a new thread for each new subject. Helps attract people who know about the subject and helps searches be more accurate as well..
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

cov

  • Full Member
  • ***
  • Posts: 240
Re: Test if a table exists in Firebird?
« Reply #10 on: April 10, 2013, 10:46:56 am »
Hi Big Chimp,

Not sure where I used the keyword 'UPDATE'. I'm assuming that the "CREATE TABLE PATHS" SQL phrase is translated internally into an "UPDATE" directive.

The space between 'INCEPTION' and 'DATE' is because the field name 'INCEPTION' is of the 'DATE' datatype. It's changed to DATETIME in the example below.

As regards starting a new thread: I get your point.

However, it's still roughly on the same topic (Creating tables if the table doesn't exist), so I felt that adding to the existing thread was preferable to me dominating the front page of the forum with millions of questions on programming for Firebird.  :P

Trying a slightly different tack:

Code: [Select]
  IBConnection1.ExecuteDirect('CREATE TABLE PATHS('+
              'ID INTEGER NOT NULL UNIQUE PRIMARY KEY, '+
              'PATH VARCHAR(800), '+
              'INCEPTION DATETIME,'+
              'UPDATE DATETIME,');

as detailed in http://wiki.freepascal.org/Firebird_in_action gives the same error.

cov

  • Full Member
  • ***
  • Posts: 240
Re: Test if a table exists in Firebird?
« Reply #11 on: April 10, 2013, 10:53:35 am »
Looking up the error codes: http://firebirdfaq.sourceforge.net/faq120/

I see

-104 /* Connection reset by peer */

I'm not sure where this occurred.

What should I look out for?

cov

  • Full Member
  • ***
  • Posts: 240
Re: Test if a table exists in Firebird?
« Reply #12 on: April 10, 2013, 11:02:29 am »
Got it.

UPDATE is the name of one of my fields.

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Test if a table exists in Firebird?
« Reply #13 on: April 10, 2013, 11:16:24 am »
The space between 'INCEPTION' and 'DATE' is because the field name 'INCEPTION' is of the 'DATE' datatype. It's changed to DATETIME in the example below.
Oops, sorry, you're completely right.

However, it's still roughly on the same topic (Creating tables if the table doesn't exist), so I felt that adding to the existing thread was preferable to me dominating the front page of the forum with millions of questions on programming for Firebird.  :P
That would be no problem at all - as long as you get your answers ;)
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

cov

  • Full Member
  • ***
  • Posts: 240
Re: Test if a table exists in Firebird?
« Reply #14 on: April 10, 2013, 11:24:05 am »
I find this forum incredibly helpful, not only for the questions I ask here, but also for the huge knowledgebase it contains in previously answered questions.

And the fact that it is very active means that any problem encountered can be resolved very quickly, often without impacting development.

Well done to all of you!

 

TinyPortal © 2005-2018