Skip to Content
avatar image
Himanshu Pandey

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

Apr 13, 2018 at 08:57 AM | 178 Views

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 ?

SAP HANA
SQL | web ide | hana xsa | web ide for hana | hana cds
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Thomas Jung
    Apr 13, 2018 at 01:39 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Himanshu Pandey
      Apr 16, 2018 at 04:41 AM

      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?