Detect if a table exists

Question

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

I have a need to write some Delphi code to say if table exists do this else this...

score 4 · Accepted Answer · answered Jul 08 '11 at 14:14

4

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.

answered Jul 08 '11 at 14:14

Peter Funk

695
4
5

1

Thanks! Any ideas if you can return a 0/1 or 'TRUE/FALSE'? – Jon Jul 08 '11 at 14:27
1

In ADS 10.x you can SELECT FROM a procedure like so: SELECT * FROM (EXECUTE PROCEDURE sp_GetTables( NULL, NULL, NULL, 'table' )) a WHERE TABLE_NAME = 'TEST.ADT' I would then just check the result record count, 1 is TRUE 0 is FALSE. – Peter Funk Jul 08 '11 at 14:31
1

A bit simpler to just pass the table name into sp_GetTables: EXECUTE PROCEDURE sp_GetTables( NULL, NULL, 'TEST.ADT', 'TABLE' ) – Peter Funk Jul 08 '11 at 14:57

score 1 · Answer 2 · answered Jul 08 '11 at 13:21

1

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.

answered Jul 08 '11 at 13:21

kolchanov

2,018
2
14
32

Thanks. Just had a look and it seems that system.tables is part of a data dictionary which I dont have. – Jon Jul 08 '11 at 13:59

score 0 · Answer 3 · answered Jul 09 '11 at 06:55

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;

score 0 · Answer 4 · answered Aug 10 '11 at 05:37

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;

Detect if a table exists

4 Answers4