Question Re: Create local temporary table in procedure in Technology Q&A

Create local temporary table in procedure

patrickbachmann — Mon, 27 Jan 2014 21:29:41 GMT

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

Re: Create local temporary table in procedure

lbreddemann — Mon, 27 Jan 2014 21:42:37 GMT

Hi Patrick,

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

- Lars

Re: Create local temporary table in procedure

patrickbachmann — Mon, 27 Jan 2014 21:50:11 GMT

Hi Lars,

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

-Patrick

Re: Create local temporary table in procedure

lbreddemann — Tue, 28 Jan 2014 08:53:30 GMT

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

Re: Create local temporary table in procedure

vivekbhoj — Tue, 28 Jan 2014 08:54:19 GMT

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

Re: Create local temporary table in procedure

lbreddemann — Tue, 28 Jan 2014 09:47:21 GMT

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

Re: Create local temporary table in procedure

vivekbhoj — Tue, 28 Jan 2014 10:25:37 GMT

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

Re: Create local temporary table in procedure

vivekbhoj — Tue, 28 Jan 2014 10:41:19 GMT

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

Re: Create local temporary table in procedure

thomas_jung — Tue, 28 Jan 2014 10:48:00 GMT

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

BEGIN

Re: Create local temporary table in procedure

vivekbhoj — Tue, 28 Jan 2014 10:58:09 GMT

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

Re: Create local temporary table in procedure

patrickbachmann — Tue, 28 Jan 2014 16:21:47 GMT

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

Re: Create local temporary table in procedure

patrickbachmann — Tue, 28 Jan 2014 18:21:47 GMT

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

Re: Create local temporary table in procedure

patrickbachmann — Tue, 28 Jan 2014 19:29:28 GMT

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

Re: Create local temporary table in procedure

patrickbachmann — Tue, 28 Jan 2014 19:41:13 GMT

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

/********* 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 ************/

Re: Create local temporary table in procedure

patrickbachmann — Thu, 30 Jan 2014 14:14:24 GMT

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

Re: Create local temporary table in procedure

lbreddemann — Thu, 30 Jan 2014 14:30:02 GMT

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

Re: Create local temporary table in procedure

patrickbachmann — Thu, 30 Jan 2014 14:37:14 GMT

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

Re: Create local temporary table in procedure

patrickbachmann — Thu, 30 Jan 2014 14:40:05 GMT

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.

Re: Create local temporary table in procedure

former_member182302 — Thu, 30 Jan 2014 14:44:39 GMT

Adding to that ,

I just tried Column, and it works.

Regards,

Krishna Tangudu

Re: Create local temporary table in procedure

patrickbachmann — Thu, 30 Jan 2014 14:46:20 GMT

Krishna are you also on 70 like Lars?

Thanks