- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Check if table exist on sql db
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
4 REPLIES 4
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Check if table exist on sql db
Created:
Dec 2, 2021 07:56 AM
| Last Modified: Dec 2, 2021 5:50 AM
(1879 views)
| Posted in reply to message from geoff1 12-02-2021
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Check if table exist on sql db
it returns a table, how to get an argument? boolean ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Check if table exist on sql db
argument = N Rows(dt);
returns the number of rows of the table. If there is no row, the table was not there
Georg
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Check if table exist on sql db
Created:
Dec 2, 2021 02:27 PM
| Last Modified: Dec 2, 2021 11:41 AM
(1830 views)
| Posted in reply to message from Georg 12-02-2021
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);