Forum

General discussion on using the Kognitio Analytical Platform.
Contributor
Offline
User avatar
Posts: 27
Joined: Thu Jun 26, 2014 3:23 pm

drop table/view only if it exists

by William » Tue Jun 20, 2017 12:14 pm

Hi,

I'm just wondering whether there is a way to drop objects only if they exist.
I'm trying to execute Kognitio scripts from a third-party analytics tool (KNIME) and need a way to stop scripts failing due to drop statements.
I see PostgreSQL can use DROP IF EXISTS [TABLE_NAME]. Does Kognitio have a workaround to offer the same functionality?

I know Kognitio Console can ignore certain error types but this obviously doesn't help my third-party tool.
Also I've tried using an IF statement but again this appears to be a console-only solution.

Thanks,

Will
Reply with quote Top
Contributor
Offline
User avatar
Posts: 17
Joined: Mon Dec 01, 2014 11:38 am

Re: drop table/view only if it exists

by michaeld » Tue Jun 20, 2017 1:08 pm

Hi William,

Kognitio SQL scripts can include directives to temporarily ignore errors, for example:

...
whenever sqlerror continue success;
drop table T1;
whenever sqlerror continue failure;
...

In this example, the script will not increment the error count if table T1 doesn't exist, but subsequently the default behaviour will resume (i.e. continue the script on any subsequent error but increment the error counter). Such scripts can be run using the wxsubmit tool.
These error handling directives are described in the Kognitio SQL guide ( part of the documentation found at http://kognitio.com/forums/viewtopic.php?f=2&t=3), on page 314, in the 'wxsubmit scripting' section.

Regards,
Mike
Reply with quote Top
Contributor
Offline
User avatar
Posts: 27
Joined: Thu Jun 26, 2014 3:23 pm

Re: drop table/view only if it exists

by William » Tue Jun 20, 2017 2:17 pm

Hi Mike,

This is useful but I'm executing my scripts via JDBC from a third-party tool which doesn't recognise the scripting syntax.
Are there any other options?

Thanks,

Will
Reply with quote Top
Contributor
Offline
User avatar
Posts: 17
Joined: Mon Dec 01, 2014 11:38 am

Re: drop table/view only if it exists

by michaeld » Tue Jun 20, 2017 2:28 pm

William,

That's correct, the aforementioned method only works when using wxsubmit (a Kognitio command line tool). Kognitio doesn't have any specific 'drop table if exists' syntax, so if you are sending SQL queries to Kognitio from Knime, there are 2 options:

1. Use functionality from Knime to handle errors, for example the error catching described here:

https://tech.knime.org/forum/knime-gene ... ntinuation

2. Use an 'exec' query to drop the table. For example run the following SQL:

exec select case when t is null then '' else 'drop table ' || t end
from ( select b.name || '.' || a.name as t
from ipe_alltable a, ipe_allschema b where a.schema_id=b.id and
b.name='SYS' and a.name='T1') df;

This will drop the table SYS.T1 if it exists, but won't return an error if it doesn't exist. Exec is described in section 2.6 of the Kognitio SQL guide.

Regards,
Mike
Reply with quote Top
Contributor
Offline
User avatar
Posts: 27
Joined: Thu Jun 26, 2014 3:23 pm

Re: drop table/view only if it exists

by William » Tue Jun 20, 2017 3:29 pm

Hi Mike,

The exec solution was exactly what I was looking for.
Problem solved.

Thanks a lot,

Will
Reply with quote Top

Who is online

Users browsing this forum: No registered users and 1 guest

cron