Detect if a table exists

In SQL Server you can write SQL to check if a table exists. How can I do that for ADS?

I have a need t开发者_运维问答o write some Delphi code to say if table exists do this else this...

The system procedure sp_GetTables can tell you what tables exist in the directory that you connected to:

EXECUTE PROCEDURE sp_GetTables( NULL, NULL, NULL, 'TABLE' )

A non-SQL solution would be to use the AdsCheckExistence API.

I'm not ADS user, so I can't answer in detail.

See http://devzone.advantagedatabase.com/dz/webhelp/Advantage10.1/index.html

The're is system.tables view with information about tables. I suppose you also can write SQL query to check a table.

I like Peter's answer, but depending on what it is you need to do, you might be looking for a TRY, CATCH, FINALLY statement.

TRY
   // Try to do something with the table
   select top 1 'file exists' from "non_existing_table";

CATCH ADS_SCRIPT_EXCEPTION 

   // If a "7041 - File does not exist" error ocurrs
   IF __errcode = 7041 THEN 
      // Do something else
      select 'file does not exist' from system.iota; 

   ELSE 
      // re-raise the other exception
      RAISE; 

   END IF; 
END TRY;

Delphi code:

function TableExists(AConnection: TADOConnection; const TableName: string): boolean;
var
R: _Recordset;
begin
if AConnection.Connected then
try
  R := AConnection.Execute('Select case when OBJECT_ID(''' + TableName + ''',''U'') > 0 then 1 else 0 end as [Result]', cmdText, []);
  if R.RecordCount > 0 then
   Result := (R.Fields.Items['Result'].Value = 1);

except on E:exception do Result := false;
end;

this simple function use existing TADOConnection

end;