Unable to create users in Hana Database Using execBatch()

[former_member697339]

I have an XSA System and XSA Database User. This XSA DB user will be used to create new Hana database users in XSA. I am trying to create a batch of users with the help of execBatch() functionality. I have a cap with node.js project.

I am executing sql queries in my node.js file. Below is the code.

createusersarr is the array which contains the list of users to be created. The array looks like this -

[ [ 'SAC_XSA_HDB_USER_ABC1' ], [ 'SAC_XSA_HDB_USER_ABC2' ], [ 'SAC_XSA_HDB_USER_ABC3' ], [ 'SAC_XSA_HDB_USER_ABC4' ], [ 'SAC_XSA_HDB_USER_ABC5' ], [ 'SAC_XSA_HDB_USER_ABC6' ], [ 'SAC_XSA_HDB_USER_ABC7' ], [ 'SAC_XSA_HDB_USER_ABC8' ], [ 'SAC_XSA_HDB_USER_ABC9' ], [ 'SAC_XSA_HDB_USER_ABC10' ] ]

When I run my node app,

It doesnt create the users and the error shown is undefined as below -

ERROR - Targeting User Creation

undefined

I am not sure why this is failing? It runs fine when I use exec() instead of execBatch() and provide user name one by one in the create user query (basic loop mechanism).

console.log("Targeting User Creation")
            try{
                let createuserstatement = await xsaDbConn.preparePromisified("CREATE USER ? PASSWORD AbcYes NO FORCE_FIRST_PASSWORD_CHANGE")
                await createuserstatement.execBatch(createusersarr)
                console.log(`Users created.`) 
            }catch(err){
                console.log(err.stack)
            }

[former_member697339 · Accepted solution]

Placeholder (?) is used in queries to substitute the parameters(data) of an object. An object can be a table, view, columns, user, role, etc. It is an entity which is part of your model or part of a system, which you can "use" (call, select from, create, alter etc). It is part of a syntax.

Parameter means values of an object. The data is the content which you can manipulate, and it can be substituted with placeholders.

So, in the above case for example -

CREATE USER or INSERT INTO

both will take a name (username, tablename respectively). As username or tablename is an object, placeholder will not work in that case.

Therefore in the above question, the query fails because placeholder(?) can't substitute an object name that has to be persisted as an entity.

Placeholders can only be used where you need to substitute it with some data. For ex

SELECT * FROM USERS WHERE USER_NAME = ?

Here, we provide user_name as data to the USER_NAME column of USERS table. Therefore placeholder will work.

While creating user -

CREATE USER USERNAME PASSWORD PWD

Here, USERNAME itself is an object/entity that has to be created. USERNAME will exists as an entity not as data in the database. Therefore placeholder will not work.

[michal_majer]

Hello Shreyasi,

I am afraid execBatch is not compatible with CREATE USER statement.
Basic LOOP seems to be one possible solution in that case.

Best,
Michal

[former_member697339]

majer.michal

Any reason why create user statement is not compatible with execBatch()?

[michal_majer]

Well it is probably issue related more to the hana-client library than execBatch method.

We couldn't use the JavaScript to substitute all values in query.
For example, we couldn't substitute table name:

INSERT INTO ? (DESCRIPTION) VALUES (?)

And this leads to error:
sql syntax error: incorrect syntax near "?"

[former_member697339]

majer.michal

Thanks for your response. I have provided an answer, so you can refer to it and find out your issue.