topic Re: Check if table exist on sql db in Discussions

Check if table exist on sql db

geoff1 — Fri, 09 Jun 2023 18:06:32 GMT

Hello,

I am running some jmpquery on different SQL DB. Because some DB do not have the same tables, I getting the error:

Tables (public.table1(t1)) referenced in the SQL Query were not found in the database{1} in access or evaluation of 'Table' , Table( "table1", Schema( "public" ), Alias( "t1" ) ) /*###*/

How could I check if a table exists on a SQL DB before running a query ?

Thanks

Geof

Re: Check if table exist on sql db

geoff1 — Thu, 02 Dec 2021 13:50:41 GMT

I found using the custom SQL below, it returns 0 or 1 if it exists.

Names Default To Here( 1 );
obj = New SQL Query(
	Connection( "ODBC:DSN=xxxx" ),
	Custom SQL(
		"SELECT EXISTS (
SELECT FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name = 'table1'
);"
	)
);
obj << run;

Re: Check if table exist on sql db

geoff1 — Thu, 02 Dec 2021 13:23:42 GMT

it returns a table, how to get an argument? boolean ?

Re: Check if table exist on sql db

Georg — Thu, 02 Dec 2021 18:18:25 GMT

argument = N Rows(dt);

returns the number of rows of the table. If there is no row, the table was not there

Re: Check if table exist on sql db

pmroz — Thu, 02 Dec 2021 19:41:31 GMT

If this is SQL Server something like this (untested) should work:

Names Default To Here( 1 );

schema_name = "PUBLIC";
table_name  = "TABLE1";

sql = evalinsert(
"SELECT COUNT(*) N
   FROM information_schema.tables
  WHERE table_schema = '^schema_name^'
    AND table_name = '^table_name^'");

dbc = create database connection("ODBC:DSN=xxxx");
dt = execute sql(dbc, sql, invisible);
close database connection(dbc);
ntables = dt:N[1];
if (ntables,
	print("Table " || table_name || " exists");
	,
	print("Table " || table_name || " does not exist");
);
close(dt, nosave);