How to call GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE() from xsjs

[Former Member]

Hi,

I'm trying to grant an AP from an xsjs procedure, and can't find the right way to parameterize the privilege name so that the statement can be prepared and executed successfully.

I'm using the EPM demo objects, and trying to grant access to sap.hana.democontent.epm.models/AP_PURCHASE_ORDER_PROD_CAT to a role (

dq.hana.ctid.security::PROFILING_USER) I created as a design-time artifact.

If I run this statement in a SQL console window, it works fine.

_SYS_REPO.GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE('"sap.hana.democontent.epm.models/AP_PURCHASE_ORDER_PROD_CAT"', 'dq.hana.ctid.security::PROFILING_USER');

Notice the special quoting around the privilege name. I struggled with that until I found another post that showed an example.

I have tried to call this same method from an xsjs with no luck. I tried building the exact string with no params

     ...

     privilegeStr = "call \"_SYS_REPO\".\"GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE\"('\""

          + analyticPrivilege | "\"', '" + roleName + "')";

     body += "Preparing: " + privilegeStr + "<br>";

     var stmt = orchConn.prepareCall(privilegeStr);

     ...

When the procedure was executed, this was the output:

Preparing: call

"_SYS_REPO"."GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE"('"sap.hana.democontent.epm.models/AP_PURCHASE_ORDER_PROD_CAT"',

'"dq.hana.ctid.security::PROFILING_USER"')

Error: Exception caught

(dberror(CallableStatement.execute): 257 - sql syntax error: zero-length

delimited identifier""": line 1 col 92 (at pos 92) at

ptime/session/eapi/jdbc/ExternalStatement.cc:884)

If I eliminate the double-quotes surrounding the AP name, I get this output:

Preparing: call

"_SYS_REPO"."GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE"('sap.hana.democontent.epm.models/AP_PURCHASE_ORDER_PROD_CAT',

'"dq.hana.ctid.security::PROFILING_USER"')

Error: Exception caught

(dberror(CallableStatement.execute): 257 - sql syntax error: incorrect syntax

near ".": line 1 col 28 (at pos 28) at

ptime/session/eapi/jdbc/ExternalStatement.cc:884)

If I modify it to use parameters with this code:

     privilegeStr = "call \"_SYS_REPO\".\"GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE\"(?, ?)";

     body += "Preparing: " + privilegeStr + "<br>";

     var stmt = orchConn.prepareCall(privilegeStr);

     stmt.setString(1, analyticPrivilege);
     stmt.setString(2, roleName);

     if (stmt.execute())

     ...

I get this output:

     Preparing: call "_SYS_REPO"."GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE"(?, ?)

     Error: Exception caught (dberror(CallableStatement.execute): 257 - sql

     syntax error: incorrect syntax near ".": line 1 col 28 (at pos 28) at

     ptime/session/eapi/jdbc/ExternalStatement.cc:884)

If I try and wrap the AP name parameter in double-quotes before I call setString with it, then I'm back to getting the zero-length delimited identifier.

If nothing else, I believe I'll have to write another SQLScript procedure that calls GRANT_ACTIVATED_ANALYTIC_PRIVILEGE, and just pass it the two values as normal strings, and have it build the exect call string I need and execute it there.

Any tips would be appreciated!

Terry

[Former Member]

Hi Terry,

has this ever been resolved?

thx,

greg