Skip to Content
0

How to create and use temp table from node module in HANA XSA

Apr 13 at 08:57 AM

49

avatar image

hi,

I need to create a temp table while running some operation in node.js.

Using below query to create local temp table resulted in error: Command Error: invalid table name: Could not find table/view temp_table in schema <schema_name>

`CREATE LOCAL TEMPORARY TABLE "${tempTable}" (some_columns)`

`insert into "${tempTable}" (some_columns)`

And when i tries using global temp table I got Error: insufficient privilege: Not authorized

`CREATE GLOBAL TEMPORARY TABLE "${tempTable}" (some_columns)`

How we can create a temp table in HANA XSA dynamically (means the name of table will vary according to some inputs and at a time we will be creating multiple tables and querying on them) and use them ?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Thomas Jung
Apr 13 at 01:39 PM
0

The application runtime user for the container will not have DDL rights by default. You have a few options. You could create a stored procedure with definer rights (which means it will run as the container object owner technical user of the container) and that stored procedure does the create temporary table when called. The other option is to adjust the roles automatically granted to the technical users via adding a default_access_role to your container. You could give the technical user schema privileges for CREATE TEMPORARY TABLE.

https://github.com/SAP/com.sap.openSAP.hana5.example/blob/hana2_sps03/core_db/src/roles/admin.hdbrole

https://github.com/SAP/com.sap.openSAP.hana5.example/blob/hana2_sps03/core_db/src/defaults/default_access_role.hdbrole

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Thomas,

Thanks for the answer. Yes, if you want a global temp table then we should do above steps. But for local temp table I added the current schema as prefix to the temp table name and it worked.

utils.runDbQuery(dbClient, 'SELECT CURRENT_SCHEMA FROM DUMMY', [])
	.then(rs => {
	    //create temp table
	    tempTable = `"${rs[0].CURRENT_SCHEMA}"."#${tempTable}"`;
	    let tmpQuery = `CREATE LOCAL TEMPORARY TABLE ${tempTable} (SOME COLs)

One question here, when res.end() or res.send() is triggered will the temp table be dropped automatically or shall I drop it manually?

0