Test if a table exists in Firebird?

[cov]

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]

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...

[cov]

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]

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]

Sorry, Firebird wasn't started. 

[cov]

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]

yeah, these method return all tables, user tables ans system tables.

[Zoran]

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]

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]

Well, that's sad 

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..

[cov]

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. 

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]

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]

Got it.

UPDATE is the name of one of my fields.

[BigChimp]

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. 

That would be no problem at all - as long as you get your answers

[cov]

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!