Test if a table exists in Firebird? lazarus / 16-Databases ---------------------------------------------------------------------- cov · 2013-04-08 12:22 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 · 2013-04-08 12:54 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_informatio n which links to the documentation http://www.freepascal.org/docs- html/fcl/sqldb/retrievingschemainformation.html Looks like TSQLConnection.GetTableNames is a likely candidate... ---------------------------------------------------------------------- cov · 2013-04-08 13:42 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 · 2013-04-08 15:17 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 · 2013-04-08 17:10 Sorry, Firebird wasn't started. ---------------------------------------------------------------------- cov · 2013-04-09 21:26 Quote from: BigChimp on April 08, 2013, 02:54:35 pmLooks 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 · 2013-04-10 00:53 yeah, these method return all tables, user tables ans system tables. ---------------------------------------------------------------------- Zoran · 2013-04-10 04:39 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 · 2013-04-10 06:42 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 · 2013-04-10 08:32 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 · 2013-04-10 08:46 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 · 2013-04-10 08:53 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 · 2013-04-10 09:02 Got it. UPDATE is the name of one of my fields. ---------------------------------------------------------------------- BigChimp · 2013-04-10 09:16 Quote from: cov on April 10, 2013, 10:46:56 amThe 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. Quote from: cov on April 10, 2013, 10:46:56 amHowever, 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 · 2013-04-10 09:24 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! ---------------------------------------------------------------------- cov · 2013-04-10 11:08 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