SAP HANA Student Trial - Retrieving data from a schema database

[Former Member]

Hi,

I created my first helloworld.xsjs file in SAP HANA trail version and was successfully able to retrieve data from DUMMY table.

Then I created a TEST1 table in NEO_45GGLHBRNEL4HTIE2H0ZR0LUY schema and inserted test values in this table. When  I tried to access the data in this TEST1 table through .xsjs file, I got an error as

Found the following errors: =========================== 

InternalError: dberror(Connection.prepareStatement): 258 - insufficient privilege: Not authorized at ptime/query/checker/query_check.cc:2422 (line 12 position 0 in /p1873072661trial/hihanatrial/helloworld/helloworld.xsjs)

Could you please let me know how can I get access to this table from .xsjs file

The .xsjs code:

$.response.contentType = "text/html";

var output = "Hello World!<br><br>";

//Open a database connection

var conn = $.db.getConnection();

//var pstmt = conn.prepareStatement("insert into DEV_3NNM3EIKNP8VVHWQNM9CPLF1I.TEST values (1,'A')");

//var rs = pstmt.executeQuery();

//Prepare a simple SQL statement on the system table "DUMMY"

var pstmt1 = conn.prepareStatement("select * from NEO_45GGLHBRNEL4HTIE2H0ZR0LUY.TEST1");

//Execute the query

var rs = pstmt1.executeQuery();

//Check the query result

if (!rs.next()) {

    //Something went wrong: Return an error

    $.response.setBody("Failed to retrieve data");

    $.response.status = $.net.http.INTERNAL_SERVER_ERROR;

} else {

    //All went fine: Return the Query result

    output = output + "This is the response from my SQL: " + rs.getString(1);

}

//Close the database connection

rs.close();

pstmt.close();

conn.close();

//Return the HTML response.

$.response.setBody(output);

Thanks,

Krishna

[Former Member · Accepted solution]

Hi Sandeep,

Just have to include this statement in the roles folder -> role access file:

catalog sql object "NEO_XXXXXXXXX"."TABLE NAME": SELECT;

It worked for me. Please let me know if even you are able to access your table data from .xsjs file.

Thanks,

Krishna

[sandeep_mekala · Accepted solution]

Hi,

Thank you for your response. Usually roles will be in security folder, but we don't have access to security folder right. Can you please tell me where exactly I can find this role access file.

[Former Member · Accepted solution]

Hi Sandeep,

I am using student trial account and have created roles folder in the same path as .xsjs file. Please find the screenshot below. I think you can create a similar .hdbrole file and use the following code:

role pXXXXXXtrial.hihanaxs.hihanaxs.roles::model_access

{catalog sql object "NEO_7KXXXXXXXXXXXXXU"."TEST1": SELECT;}

[sandeep_mekala · Accepted solution]

Thank you Krishna:)

It is working now.:)

[Former Member · Accepted solution]

Thanks Sandeep, good to know that. Could you mark the comment as correct if it has helped you

[former_member328455 · Accepted solution]

hi Krishna，

I created similar role file in the same folder of helloworld.xsjs, and also put your code into the role file, however, it doesn' work...

helloworld.xsjs:

err:

Found the following errors:

===========================

InternalError: dberror(Connection.prepareStatement): 258 - insufficient privilege: Not authorized (line 8 position 1 in /i074178trial/stock/helloworld.xsjs)

confused...

[former_member328455 · Accepted solution]

well, i resolved it by one more step calling statement in sql console:

call "HCP"."HCP_GRANT_ROLE_TO_USER"('i074178trial.stock::query','i074178');

thanks

[sandeep_mekala · Accepted solution]

Yeah. you have to do that.

[sandeep_mekala]

Hi, I am also able to retrieve data from DUMMY table but it is giving "insufficient Privilege" when I tried to retrieve from my table. Did you find the issue. Please help me with this.

[Former Member]

Hi Krishna,

You got solution to the above problem. How you have resolved it. Can you please brief me about the same.

Regards,

Pallavi

[Former Member]

Hi Pallavi,

Student trail accounts do not have the permissions to write to database. so you would need an account with database write access privileges to insert the data into tables.

Regards,

Krishna

[Former Member]

Hi Krishna,

Thank you for your response.

Am using SAP HANA Trail account as how you have used in the sample code. Is HANA trail is student trial account which you would like to say, will it doesn't work if we have trial account as specified. Hope the above code which you have shared worked fine for you without trial account.

Regards,

Pallavi

[xu_xiang]

Hi Krishna,

... trial accounts do not have the permissions to write to database....

this is not true.

The issue here is that you are trying to insert data into the wrong database schema DEV_3NNxxxxxxxxxF1I. This is the user/schema which you can access the DB with. Your data should be stored in the schema NEO_45GxxxxxxxxxxxLUY.

Best regards

Xu

[former_member185671]

This is not true.

You can easily insert data through a DB tunnel with node.js

Best,

Christoforos

[Former Member]

Hi Xu,

I am using student trail version and when I click on SAP HANA Web-based Development Workbench link it directly takes me to DEV_DK49xxxxxxxxxxxxxxx7QG. I think the access to NEO_45xxxxxxxxxxx might be restricted now. Could you tell me how to access NEO schema

Kind regards,

Krishna

[Former Member]

Hi Christoforos,

Could you share any useful links that would guide me how to use DB tunnel with node.js

Thanks,

Krishna

[Former Member]

Hi Pallavi,

I have tried the above code only with trail version.

I think to work with trail version below link is very useful. I was able to complete all the steps as specified in the link:

Kind regards,

Krishna

[former_member185671]

Hi Krishna,

You can find NEO_ Schema right here:

[former_member185671]

For setting up the DB Tunnel, you can follow my blog

Best, Christoforos

[Former Member]

Thanks for the info Christoforos!

[former_member185671]

Did it work for you ?

[Former Member]

It gives me an error:

ERROR; Not a valid SAP HANA Cloud Platform landscape host: services.hanatria

l.ondemand.com

Could you help me to resolve this issue.

Thanks!

[former_member185671]

Can you please print Screen it ?

[Former Member]

Hi Christoforos,

I am able to open the tunnel now. It was a version issue.

Now i have to look into the part 2 of your blog.

Thanks for the info!

[Former Member]

Hi Christoforos,

Can I use the db tunnel to set up a jdbc connection in xsjs file. If so could you guide me.

Thanks,

Krishna

[former_member185671]

Hi Krishna,

No - connecting to an .xsjs using node.js has no actual meaning, because JDBC and ODBC connections are for databases.

Also, connecting to a file makes no sense.

But, the .xsjs connects to a database using the following command: var conn = $.db.getConnection();

Best,

Christoforos

[sandeep_mekala]

Hey, I am using similar kind of code but it is give same error.. Have you guys succeeded in fixing this problem. If yes, please help me out with this.

[Former Member]

Hi Pallavi,

Just try to include this statement in your roles folder -> role access file:

catalog sql object "NEO_XXXXXXXXX"."TABLE NAME": SELECT;

It worked for me. Please let me know if this works for you.

Thanks,

Krishna