Skip to Content
0

HANA: Drop and Create an existing stored procedure in transaction

Jul 24, 2017 at 09:08 AM

144

avatar image

Hi, I'm working on an addon wich applies db updates on start.

I need to update an existing stored procedure. So:

Company.StartTransaction()
rs.DoQuery("DROP PROCEDURE MySp")
rs.DoQuery("CREATE PROCEDURE MySp"...)
Company.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_Commit)

When CREATE PROCEDURE is executed i get this error:
1). [SAP AG][LIBODBCHDB32 DLL][HDBODBC32] General error;329 cannot use duplicate name of function or procedure: MySp

Removing transactions works.
Seems HANA is not able to 'see' the drop when inside a transaction.
I need to use transactions because if create fails for some reasons SP is definitely dropped..

With SQL Server I have no problem.

Please help.

Thank you

Paolo

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

avatar image
Former Member Jul 26, 2017 at 12:59 PM
0

Hi Paolo,

Is ALTER procedure not a possibility?

Pedro Magueija

LinkedIn | Twitter | Blog

Show 4 Share
10 |10000 characters needed characters left characters exceeded

Hi Pedro,

Does HANA support ALTER? Are you sure?

0
Former Member

Well, they have it documented: https://help.sap.com/viewer/de2486ee947e43e684d39702027f8a94/2.0.01/en-US/042ab4636cf34a9cb88dd61c808861a8.html

Please let me know if this doesn't work.

Pedro Magueija

LinkedIn | Twitter | Blog

0

For me doesn't work, gives me

Could not execute 'ALTER PROCEDURE GET_PROCEDURES( OUT procedures TABLE(schema_name NVARCHAR(256), name ...'

SAP DBTech JDBC: [257]: sql syntax error: line 5 col 108 (at pos 224)

Could not execute 'END'

SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "END": line 1 col 1 (at pos 1)

I have this version: 1.00.112.03.1464042351 (fa/newdb100_rel).

I also discovered that HANA gives me wrong error, the problem is not that the procedure already exists, the procedure is calling another procedure which does not exists....

Error text is sometimes truncated when long.

thank you

Paolo


0
Former Member

Hi Paolo,

That could be it. For me it works on 1.00.122.03.1475845474 (fa/hana1sp12) but it does not work on 1.00.102.03.1449674847 (fa/newdb100_maint_rel).

Perhaps the ALTER PROCEDURE was only implemented after your version.

Is updating the system a possibility for you?

Pedro Magueija

LinkedIn | Twitter | Blog

0