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 ?
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.
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?