A recurring question of folks using SAP HANA is how to only run a DROP
command for objects that actually exist in the database. One could argue that the effect of executing DROP
does not depend on whether the object to be dropped exists, because after running the command it will not be there either way, but there is a bit more to it.
See, DROP
commands, like say DROP TABLE
, report back an error message when the object cannot be found (e.g. because it does not exist).
drop table bla; Could not execute 'drop table bla' SAP DBTech JDBC: [259]: invalid table name: BLA: line 1 col 12 (at pos 11)
This can be annoying when running scripts to set up a database structure which is a common part of product installation and update procedures.
On MS SQL Server there exists (ha ha ha – extra point for the flattest pun) an extension to the DROP
command (and to other DDL commands): DROP IF EXISTS
SAP HANA up to now (HANA 2 SPS 03) does not provide such a functionality, but it is easy to come up with a workaround.
The following utility function can be used:
drop function obj_exists ; create function obj_exists (in schema_name NVARCHAR(256) , in object_name NVARCHAR(256)) returns obj_exists int as begin declare _exists int := 0; select case when count(*) >0 then 1 else 0 end into _exists from objects where schema_name = IFNULL (:schema_name, current_schema) and object_name = IFNULL (:object_name, ''); obj_exists = :_exists; end;
The function returns 1 if the object exists and 0 if it does not. If the input parameters contain NULL
, then the function still works using the CURRENT schema
but will likely return 0 as output.
select obj_exists (schema_name => NULL , object_name => 'OBJ_EXISTS') as OBJ_EXISTS from dummy; OBJ_EXISTS 1
The above worked because the OBJ_EXISTS
function was created in the current schema. With that one can easily write a SQL Script that “looks before it fires“.
2 Comments
Hi Lars,
Excellent work around. Should I be worry about this warning message:
java.sql.SQLWarning: Not recommended feature: Using SELECT INTO in Scalar UDF
Hi Jamie,
the warning message really is just that: a warning.
The idea here is to discourage the use of
for simple variable assignments.
It’s possible to disable the warning via indexserver configuration parameter (check the documentation on that) but I probably wouldn’t advise for it.
Cheers,
Lars