Create local temporary table in procedure

[patrickbachmann]

Hi folks,

I have a procedure (I wrote in the procedure editor in Project Explorer).  In the procedure I'm trying to create a local temporary column table however when I attempt to activate the procedure I get error;

feature not supported: DDL is not supported in the READ ONLY procedure

In the past when I created a procedure via old method (via CONTENT/NEW PROCEDURE) I had the option of choosing READ or READ/WRITE.  I'm not seeing this option in the new procedure editor.  Also I noticed a second tab next to SQLScript tab called LOCAL TABLE TYPES.  I can't seem to place my create local temporary table there either.

I've read in other posts that this is possible... what do I need to do in order to be able to create and update this temp table?

Thanks,

-Patrick

[patrickbachmann · Accepted solution]

Guys I think I finally found the solution to this problem due to some good karma.   This morning I was helping somebody on SCN with a temporal join and created an analytic view using tables in my personal schema.  To my surprise the view failed to 'activate' with 'unauthorized' error.  Since I was using tables in my personal schema i looked at privileges for _SYS_REPO and it indeed already has SELECT privileges against my schema.  So I started looking at _SYS_REPO's privileges against other schemas and noticed another schema (that works) had select but with also 'grantable to others'.  On a whim I added 'grantable to others' on select against my schema for _SYS_REPO and retried my procedure and it worked!

Now I'm curious if anybody knows why _SYS_REPO needs 'grantable to others' and also I'm assuming this means all temp tables are created in the invokers personal schema??  Albeit temporarily.

Thanks all!

-Patrick

[lbreddemann · Accepted solution]

HI Patrick,

_SYS_REPO requires GRANTABLE TO OTHERS (or WITH GRANT OPTION) because it needs to  grant back the permissions to the actual user activating the view.

What surprises me is that this had been the case since the first version of HANA modeller; which fooled me into thinking that this surely wouldn't be the cause for this problem

Anyway, good to hear that it now works for you (even with the *column* store temp table?

- Lars

[patrickbachmann · Accepted solution]

Yeah and I know what you're thinking... how come I didn't notice this problem sooner... but alas all of the 'play' or sandbox type stuff in my personal schema was mostly utilizing real tables in our default schema. 

[justin_molenaur2 · Accepted solution]

Hey Patrick, I have hit this a number of times and left scratching my head - so I have learned to look for this right away if I am using tables in new schemas or my personal schema.

Anything executed out of the repository is executed using the SYS_REPO user and not your own ID. Therefore, SYS_REPO must be able to "pass on" the privileges that he is assigned. This is achieved by making the privileges 'grantable' as you show.

In the case of analytic views, SELECT must be grantable on whatever schemas are involved. In the case of read/write procedures, the INSERT/DELETE/UPDATE must be grantable on whatever schemas are involved.

I can't comment on the local temporary table as I have not used it quite yet.

Regards,

Justin

[vivekbhoj]

In the past when I created a procedure via old method (via CONTENT/NEW PROCEDURE) I had the option of choosing READ or READ/WRITE.  I'm not seeing this option in the new procedure editor. 

-> I still get this option when trying to create a procedure from my package in the content folder -

this option is not available when you try creating a procedure in Project Explorer in your project

[thomas_jung]

Sure you can specify READ or READ/WRITE.  Its just in the syntax toward the beginning right before the BEGIN

For Read Only (the default)

LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  READS SQL DATA AS

BEGIN

For Dynamic or READ/WRITE you remove the READS SQL DATA and just have AS

LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  AS

BEGIN

[vivekbhoj]

Thanks Thomas,

Earlier I tried removing READS SQL DATA AS but I removed the whole line and it gave me error

with keeping AS everything works fine

Regards,

Vivek

[patrickbachmann]

Thomas I tried your suggestion (removing READS SQL DATA) and now when I activate I get this error;

com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [258]: insufficient privilege:  [258] _SYS_BIC.MyPackage/MyProc: line 7 col 1 (at pos 317): [258] (range 3) insufficient privilege exception: insufficient privilege: Not authorized

  at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.createException(SQLExceptionSapDB.java:334)

  at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.generateDatabaseException(SQLExceptionSapDB.java:174)

  at com.sap.db.jdbc.packet.ReplyPacket.buildExceptionChain(ReplyPacket.java:102)

  at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:1142)

  at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:886)

  at com.sap.db.jdbc.CallableStatementSapDB.execute(CallableStatementSapDB.java:489)

  at com.sap.db.jdbc.CallableStatementSapDB.execute(CallableStatementSapDB.java:338)

  at com.sap.db.jdbc.trace.CallableStatement.execute(CallableStatement.java:1142)

  at com.sap.ndb.studio.sqlscript.debugger.core.communication.jdbc.SQLScriptDbgJdbcHandler.executeEditorStoredProcedure(SQLScriptDbgJdbcHandler.java:135)

  at com.sap.ndb.studio.sqlscript.debugger.core.communication.SQLScriptDbgConnectorManager.executeEditorStoredProcedure(SQLScriptDbgConnectorManager.java:33)

  at com.sap.ndb.studio.sqlscript.debugger.core.model.SQLScriptDbgSessionManager.executeEditorStoredProcedure(SQLScriptDbgSessionManager.java:293)

  at com.sap.ndb.studio.sqlscript.debugger.core.threads.ExecuteStoredProcedureThread.run(ExecuteStoredProcedureThread.java:25)

  at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

  at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

  at java.lang.Thread.run(Unknown Source)

Any thoughts?

-Patrick

[patrickbachmann]

Guys,

By the way I am still on rev 68 not rev 70 as most of you are on.  I'm trying to build my case for upgrading to 70 here.  I've also noticed the procedure editor is not very stable.  I'm constantly getting freezes in the procedure editor or debug terminating randomly etc.

-Patrick

[patrickbachmann]

Also I think I have isolated the problem to this line of code;

CREATE LOCAL TEMPORARY COLUMN TABLE "#RECEIPTS" ("PO" NVARCHAR(10),

  "POLINE" NVARCHAR(2),

  "DATERECV" NVARCHAR(8),

  "QTYRECV" DECIMAL(13,

  3) CS_FIXED NOT NULL ,

  "QTY" DECIMAL(13,

  3) CS_FIXED) ;

However I can run this fine via SQL editor.  But via procedure I get the insufficient privilege error.  Which makes me wonder if it's not my username privileges that are the problem but some other user??  ie: when calling the procedure who's rights are used?

-Patrick

[patrickbachmann]

Wow, I can't believe I have stumped all you geniuses.  Sigh.  Especially the great powerful Oz (Lars).  Well I created a message with SAP on this so will update this thread hopefully soon. 

-Patrick

[lbreddemann]

Hmm... didn't see your post before,,,

Anyhow, I think the error message is simply misleading and for SQLScript temporary column tables are not supported yet.

Does it work without the column keyword?

- Lars

[patrickbachmann]

Perhaps.  I believe I tried yesterday without the column keyword.  Let me try again...

[patrickbachmann]

Wait actually I used your example which does not have column so yes I tried that.  Thus far SAP has said nothing about version, they are going to logon to our system and try for themselves.

[former_member182302]

Adding to that ,

I just tried Column, and it works.

Regards,

Krishna Tangudu

[patrickbachmann]

Krishna are you also on 70 like Lars?

Thanks

[former_member182302]

No patrick am on 68. have a question to you,,

were you able to activate a Read procedure w/o Temporary table?

And you are getting issues only while creating Read-Write procedure with Temporary table?? ( Also did you try activating a Read Write procedure w/o Temporary Table)??

Regards,

Krishna Tangudu

[patrickbachmann]

Krishna, that's very promising news that you have 68!  I was afraid we would have to upgrade to get this feature.  To answer your question, yes I have created and successfully activated and used other procedures that are doing more simplistic read-only things and I have definitely pinpointed the problem to the specific create temporary table function.

-Patrick

[vivekbhoj]

Hi Patric,

I guess you are using HANA SPS07

In SPS07, the Local Temporary table concept is gone and we don't get tab for Local Table Type

Even the procedure extension has been changed to .hdbprocedure

We have to create hdbdd file to define global table types and then we can use these table types in our procedure

Check the below webcast for changes in HANA SPS07 SQLScript:

SAP HANA SPS 7 Call 2 SQLScript

Regards,

Vivek

[lbreddemann]

Hi Vivek,

as you see in my example above, the local temp. tables are not gone at all.

Not sure where you found the dis-support notice on local temp. tables...

- Lars

[vivekbhoj]

It is my mistake Lars

I mistook Local Table Type as Local Temporary Table

We can't define Local Table Types in hdbprocedure

Creating Temporary Table works for me when I create a Procedure in SQL Console but While trying to create Temporary local table in Project Explorer  as hdbprocedure, it gives me same error as Patrick

Regards,

Vivek

[lbreddemann]

Hi Patrick,

could it be that the sqlscript_mode parameter on your development instance is not set to UNSECURE?

- Lars

[patrickbachmann]

Hi Lars,

I just double checked our configuration and we currently have it set to UNSECURE.  Any other ideas?

-Patrick

[lbreddemann]

Hi Patrick,

that's weired.

I just tested it on rev. 70:

/********* Begin Procedure Script ************/
BEGIN


    create local temporary table #mytemp (id integer, name varchar(20));

    insert into #mytemp values (1, 'Lars');

    select * from #mytemp;
END;
/********* End Procedure Script ************/

call _sys_bic."lars.LBTEST/TEMPTABDEMO"

ID|NAME

1 |Lars

Just worked as expected...

Do you get any other error messages (maybe in the activation logs)?

- Lars

[patrickbachmann]

Lars, also I used your example (although I seem to have to have declarations section at top - maybe you are using hdbprocedure which I dont seem to have in rev68).  Anyway with this code below I get same authorization error;

CREATE PROCEDURE LARSEXAMPLE ( )

LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  AS           

/********* Begin Procedure Script ************/

BEGIN

    create local temporary table #mytemp (id integer, name varchar(20));

    insert into #mytemp values (1, 'Lars');

    select * from #mytemp;

END;

/********* End Procedure Script ************/