Update table via HANA Procedure

[former_member223074]

Hi,

I am trying to update a table using a procedure and I tried this from both SQL console and also by creating the procedure from Project explorer option.

I see this message "feature not supported: INSERT/UPDATE/DELETE are not supported in the READ ONLY procedure/function", is there a way to create procedure with update functionality too?

Would any of you guide me if this is a access related message or the update feature is not supported via procedures?

Thanks,

Anil

[RichHeilman · Accepted solution]

You also need to enable the ability to create read/write procedures in your HANA system.  From the administration console, click on the configuration tab, then expand indexserver.ini then expand repository.  Change the value for "sqlscript_mode" to UNSECURE

Cheers,

Rich Heilman

[former_member223074 · Accepted solution]

Hi Rich,

I changed the sqlscript_mode as per your suggestion, but still getting the error shown. Any pointers please?

Thanks,

Anil

[RichHeilman · Accepted solution]

You do not appear to have privileges to the table(or schema) in which you want to update.  Add the correct privileges to your user profile and try again.

Cheers,

Rich Heilman

[former_member184768 · Accepted solution]

Can you please try executing the procedure in SQL console and post the error. May be, the procedure code will also help in understanding the issue.

Please check if your procedure has definers or Invokers SQL security mode.

Regards,

Ravi

[former_member223074 · Accepted solution]

Hi Ravi,

I get the below error with this sample code and I am writing this using File --> New --> cerate new procedure where in the procedure is created in the project explorer tab and we can debug this procedure as well.

CREATE PROCEDURE update_proc ( )

    LANGUAGE SQLSCRIPT

    SQL SECURITY INVOKER

    DEFAULT SCHEMA "SYSTEM"

READS SQL DATA AS 

BEGIN

/*****************************

    Write your procedure logic

*****************************/

INSERT INTO "SYSTEM"."cp_Trend" VALUES ('1020','TP1','PF2','TR1','CN1','10','180','10/1/2012');  

END;

2) The below code without SECURITY INVOKER which gives me the privilege error as shown in my previous post:

CREATE PROCEDURE update_proc ( )

     LANGUAGE SQLSCRIPT AS

     --SQL SECURITY INVOKER

     --DEFAULT SCHEMA <schema>

--READS SQL DATA AS 

BEGIN

/*****************************

     Write your procedure logic

  *****************************/

INSERT INTO "SYSTEM"."cp_Trend" VALUES ('1020','TP1','PF2','TR1','CN1','10','180','10/1/2012');  

END;

If I write a procedure out of SQL console (which is not a recommended approach from SAP) I would not be able to debug it by setting up a break point so basically I want to write a procedure with INSERT/UPDATE/DELETE and be able to dug it as well. Appreciate any help around this!

Thanks,

Anil

Thanks,

Anil

[former_member223074 · Accepted solution]

Hi Rich,

I went in to the SAP HANA System --> Security --> Users and selected "SYSTEM" (I logged in as) and added "_SYS_BIC" and granted all the privileges to this. Still getting the same privilege error message.

Is this because I am trying to generate the data in schema "SYSTEM" and it is not added in the privileges section? It says "feature not supported: grantor and grantee are identical"..not sure how to proceed.

Thanks,

Anil

[former_member184768 · Accepted solution]

Hi Anil,

Some comments:

1) Never use SYSTEM user id for application development. This user id is basically for System administration activities and not meant to be used for application development. You are mixing system related information (tables) with application tables (like cp_Trend) and any mistake to insert / update might have impact on your HANA system. So please avoid using SYSTEM for any application related activities.

2) Don't worry about SAP recommendation . These features like repository based development and debugging of procedure is relatively new. Previously (or even now in case of many developers) the SQL code / procedure was created in the SQL console itself. You can see the Database related issues directly there. With repository based development, there could be additional access related issues as all the repository based development is owned by _SYS_REPO user. Hence I'd suggest you to try the code in SQL console also.

3) If you want to insert / update the data, don't use READS SQL DATA. It makes the procedure read only and Insert / updates are not allowed.

4) SQL SECURITY INVOKER, please change it to DEFINER. This way the procedure will use the access rights of the user creating the procedure.

5) In the insert statement, please provide the column names of the table. For ex. Insert into table a (col1, col2) values ('1', 2) ; This will help you understand if there is any data type mismatch or error due to number of values with respect to number of columns.

So my recommendation, is try it in SQL console first, get the basic issues sorted out and then take it to repository for any logic debugging issues.

Regards,

Ravi

[former_member184768 · Accepted solution]

I think the repository based objects are owned by _SYS_REPO. But you can check again, I do not have much experience on repository based development.

Regards,

Ravi

[Former Member · Accepted solution]

Hi Anil,

If you have changed "sqlscript_mode" to UNSECURE and don't use READS SQL DATA in procedure, then you need to check whether you have privilege ( INSERT/UPDATE/DELETE) on this table (cp_Trend) for using system user.

Besids, I also recommend your exec related script on sql console to test it as well, such as insert script.

Regards,

Jerry

[Former Member · Accepted solution]

Hi Ravi,

Do you know how to modify SQLScipt based Calaculation view to update/insert table?

Thanks and have a nice day.

Kevin

[RichHeilman · Accepted solution]

Calculation views, both graphical and scripted, are meant to be side effect free, so you can not use write operations within them.

Cheers,

Rich Heilman

[Former Member · Accepted solution]

Thanks Rich

[former_member223074]

Hi all,

Thanks for all your responses. Summing it up I did the following to make my procedure allow DML statements and be able to debug.

1. Per SAP note your procedure must be active in order to debug which means you (at least I ) cannot debug repository procedures create from SQL console, correct me if I am wrong.

   You have activated your procedures and they must belong to a project on your local workstation.

2. I have granted the privileges to my user id (though it is SYSTEM in my case) to do debug and execute DML statements too.

3. Create the tables in schema in schema other than "SYSTEM" and execute them to make sure the user name and schema name aren't the same.

Finally got it going.. thanks every one again for your time/effort...Feel free to add your inputs if any!

Thanks,

Anil

[Former Member]

Hi Anil,

As explained in here by Ravindra

please try to repalce

LANGUAGE SQLSCRIPT READS SQL DATA

with

LANGUAGE SQLSCRIPT

i.e. by removing "READS SQL DATA"

Regards

Angad

[former_member223074]

Angad,

Thanks for the response, yes I did try with that too after which I was able to check my procedure. But I was getting  a privilege related error message later on..

Thanks,

Anil