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“.
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 [cc lang=”SQL”]SELECT INTO[/cc] 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
Hi Lars,
Just want to add,
If we are working with temporary table, it won’t be in ‘objects’ table ( I didn’t find it there ). Instead, you should use ‘m_temporary_tables’ table to check existance. So we can add this table as union in UDF, or we can do like this:
do begin
if exists( select * from m_temporary_tables where table_name = '#LTT') thendrop table #ltt;
end if;
create local temporary table #ltt as ( select * from dummy);
select * from #ltt;
end
Thanks for sharing this, Nikolay.
Checking for local temporary tables can indeed be a required scenario. If the solution uses both shared connections and local temporary tables, it may be the case, that the table already exists for the same session – but was made by a different application-side code path.
For such a case, I would probably reconsider the way the application uses the local temporary tables. If application-side code path #1 creates & fills the table and code path #2 goes and deletes/drops it, then that can cause one or both of the sessions to work with wrong data.
The whole point of local temporary tables is that these are exclusive to a single session and a single application-side code path. If the goal is to actually share the table structure (but not the data) across many code paths/sessions then a global temporary table would be better suited.
And those global temporary tables do show up in the
OBJECTSsystem view as part of the shared catalog.Thanks for reply, good point!
I thought it may be handy, when calling same procedure several times, within same session, same code path, but with different parameters, like
…
call proc(:p1);
…
call proc(:p2);
…
or even in the for loop.
BR Nikolay
It really depends on what the purpose of the temp. table is.
To keep things simple, I would try to make sure that the creation of the local temp table happens more or less immediately after the session gets connected. As it is a “session owned” object, the responsibilty for creating it should not be scattered around in multiple procedures.
The table will exist as long as the session is active and all the procedures may want to do is to delete the contents.
If the main purpose is to hand over data between procedures then there is no reason for explicitly creating the local temp. table. One can use table typed procedure parameters for that.
Agree.
In BW we have specific process (DataTransferProcess), it allows us to create some logic inside AMDP method. And when this process is triggered, it starts several parallel processes with given AMDP logic.
Reason I tested this case in sandbox, is that I was not sure if these processes will be in one session, or separate sessions.
In my case I ended up using non-temporary table, but, anyway, I want to play with temporary tables in such parallel processing in BW just to clarify it.