HANA: Drop and Create an existing stored procedure in transaction

[paolo_fornari3]

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

[pedro_magueija]

Hi Paolo,

Is ALTER procedure not a possibility?

Pedro Magueija

LinkedIn | Twitter | Blog

[paolo_fornari3]

Hi Pedro,

Does HANA support ALTER? Are you sure?

[pedro_magueija]

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

Please let me know if this doesn't work.

Pedro Magueija

LinkedIn | Twitter | Blog

[paolo_fornari3]

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

[pedro_magueija]

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