Check the existance of a TABLENAME

[Bohse]

Environment:
firebird 1.5 with delphi 7

The Task:
Before creating a table check the database for a existance table with the same name.

used database:the example database 'EMPLOYEE' from firebird

First Try: use of
Zconnection.GetTableNames(<Pattern:String>,<Result:Stringlist>);

Pattern is one of a valid tabletype like 'TABLE','VIEW',....
Output: No Result

Second Try: use of ZSQLMetadata in conjunction with a Datasource
and a DBGrid:
Propertys:MetadataType = mdTables

Output: A Grid with all Tables marked as Table_Type 'VIEW'
and all System Tables marked as Table_Type 'SYSTEM TABLE'.

It would be a satisfying result but I found 'PHONE_LIST' marked as 'VIEW' twice.
If I use IBExpert(the personal Version) then I get Phone_LIST as 'VIEW' but not as 'TABLE'.

Who knows the answer or have a suggestion for my task??

[barko]

I do this:

Code: Select all

try
  Table.Close;
  Table.SQL.text:='select * from mynewtable where id=:id';
  Table.ParamByName('id').AsString:='bla';
  Table.Open;
except // no table found... do create table...
  Table.Close;
  try
   Table.SQL.text:='create table mynewtable...';
   Table.ExecSQL;
   ...
  except end;
end;

[Gerard]

I'm not quite sure if I've misunderstood what you have done with the ZConnection.GetTableName(). The 'Pattern' you pass is used to find matches on the table names. If you pass in an empty string you get a full list of tables.

If you need to find a single table eg. a table called 'employees', set the pattern to 'employees' and you will get a string list returned with one line in it if the table exists. If the table does not exist then you will get an empty string list. It isn't case sensitive so 'employees' and 'EMPLOYEES' return the same result.

[zippo]

I prefer the totally SQL Way..

Query1.SQL.Text := Show tables like "TABLENAME".
Query1.Open;
if Query1.Fields[0].AsString='' then
GoCreate TheTable

This works on every MySQL, but you coud use "if Query1.IsEmpty then..." or something like this..

Advantage: Almost SQL Platform independant (it's SQL standard compliant), component independant (work with Zeos, sciBit,... etc.) Hope it helps.

[btrewern]

I'm not sure if it is SQL spec complient but I shouldn't rely on it as I don't think it works on PostgreSQL, MS SQL Server, DB2 or Oracle.

Ben

[DavidVTaylor]

IMPORTANT NOTE:

If you are using GetTableNames with the IB/FB protocol (maybe others) there is a subtle but important bug (feature?) that may bite you. The Pattern passed to the GetTableNames method treats "_" and "%" as wildcard characters. The choice of these particular characters appears to be because these are also wildcard characters in a SQL LIKE expression. Internally LIKE expressions are used to handle wildcard Patterns.

This is important because it will cause unexpected results when a table name such as "PHONE_LIST" is passed as the pattern. Often you will see multiple matches in the results for what should have been an exact match. This may account for the problems Bohse encountered.

Unfortunately, there is currently no way to escape these characters to make the method treat them as literal. There appears to be a mechanism built into Zeos for this purpose, but it is currently broken, at least for IB/FB protocol. Not sure if this problem exists for other meta data methods or protocols.

BTW - This problem was discovered because it caused strange Evolve problems when using Zeos with InstantObjects. A work around was added to the InstantObjects Zeos broker until this gets fixed properly.

David

[zippo]

Check this out, can be useful:

http://www.ibphoenix.com/main.nfs?a=ibp ... ME='MySQL'

[DavidVTaylor]

Yep, the comments on querying system tables are right in target. This is how Zeos implements GetTableNames. The problem is that the wildcard expressions supported by the LIKE comparison operator conflict with legal table names. This is how the issue came to light, I normally uppercase my table names and separate words with underscores (e.g. "USER_ROLES"). Problem is the underscore is also a wildcard.

I have some ideas on how to fix this issue. Just waiting to see if I can get added to the developer list. I understand the project is in a state of flux.

David

[ChaosBiker]

Is ti just me,

or does GetTableNames ignore the Database property? and just lists ALL ?

[mdaems]

Chaosbiker, what database are you using?
Have a look at the TZConnection.GetTableNames procedure and the TZXXXXXXXDatabaseMetadata.UncachedGetTables function (replace XXXX by your server). Normally the database isn't a parameter as default usage of a connection makes you connect to one specific db. Unless you are using dbless=true (which for mysql makes a connection to the full server with no database activated). So the metadata should in come from one database only. When using multiple schema's in a database (eg. postgres) probably all tables are shown in all schema's unless you add a schema pattern in your GetTableNames call.

Mark

[ChaosBiker]

nvm,
solved by using an sql query instead

was just wanting to abuse ZConnecting gettablenames as a quick and dirty resolution

but it seems to list all tables from all db's..

not jsut the tables from a single db