Lazarus

Programming => Databases => Topic started by: cov on April 08, 2013, 02:22:44 pm

Title: Test if a table exists in Firebird?
Post by: cov 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^
Title: Re: Test if a table exists in Firebird?
Post by: BigChimp 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...
Title: Re: Test if a table exists in Firebird?
Post by: cov 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.
Title: Re: Test if a table exists in Firebird?
Post by: cov 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
Title: Re: Test if a table exists in Firebird?
Post by: cov on April 08, 2013, 07:10:21 pm
Sorry, Firebird wasn't started.  :-[
Title: Re: Test if a table exists in Firebird?
Post by: cov 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'.
Title: Re: Test if a table exists in Firebird?
Post by: otorres on April 10, 2013, 02:53:09 am
yeah, these method return all tables, user tables ans system tables.
Title: Re: Test if a table exists in Firebird?
Post by: Zoran 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
Title: Re: Test if a table exists in Firebird?
Post by: cov 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
Title: Re: Test if a table exists in Firebird?
Post by: BigChimp 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..
Title: Re: Test if a table exists in Firebird?
Post by: cov 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.
Title: Re: Test if a table exists in Firebird?
Post by: cov 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?
Title: Re: Test if a table exists in Firebird?
Post by: cov on April 10, 2013, 11:02:29 am
Got it.

UPDATE is the name of one of my fields.
Title: Re: Test if a table exists in Firebird?
Post by: BigChimp 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 ;)
Title: Re: Test if a table exists in Firebird?
Post by: cov 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!
Title: Re: Test if a table exists in Firebird?
Post by: cov on April 10, 2013, 01:08:18 pm
In the interests of helping anyone who has similar problems creating Tables for a Firebird Database, there are several errors to the code I posted above:

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

Should be:
Code: [Select]
  IBConnection1.ExecuteDirect('CREATE TABLE PATHS('+
              'ID INTEGER NOT NULL PRIMARY KEY, '+
              'PATH VARCHAR(800), '+
              'INCEPTION TIMESTAMP,'+
              'DWGUPDATE TIMESTAMP)');

So, to reitterate, use the following to check if a table exists in the database:
Code: [Select]
  tableList:= TStringList.Create;
  IBConnection1.GetTableNames(tableList,False);
  s:='MyTable';// Whatever the name of the table is.
  i:=0;
  while i<tableList.Count do
  begin
    if tableList.Strings[i]=s then
    begin
       break;
     end;
    inc(i);
  end;
  if i<tableList.Count then
  // implement the IBConnection1.ExecuteDirect code above.
  SQLTransaction1.Commit;
Title: Re: Test if a table exists in Firebird?
Post by: BigChimp on April 10, 2013, 01:23:13 pm
Thanks for posting that update!

FYI:
Code: [Select]
  tableList:= TStringList.Create;
  IBConnection1.GetTableNames(tableList,False);
  s:='MyTable';// Whatever the name of the table is.
// have a look at .IndexOf, something like (check docs though, this is air code)
  if tableList.IndexOf(s)=-1 then
  // implement the IBConnection1.ExecuteDirect code above.
  SQLTransaction1.Commit;
TinyPortal © 2005-2018