Create HANA Procedure from ABAP

[Former Member]

Hi:

I am trying to create a HANA procedure from ABAP using this:

lo_sql = cl_sql_connection=>get_connection( hanasovantazz )->create_statement( ).

DATA lo_resultset TYPE REF TO cl_sql_result_set.

lo_sql->execute_query( 'drop procedure SAPFRA.TEST; create procedure SAPFRA.TEST() as begin select * from SAPFRA."PDM"; end; ' ).

I had the error:

sql syntax error: incorrect syntax near ";": line 1 col 23 (at pos 23)                                                                                                                                                                                                                                                                                                                                                            

I also tried execute_ddl with the same result.

Is mandatory to do it on the ABAP side, regarding the context of the problem.

Thanks!

[yeushengteo · Accepted solution]

Hi,

to manipulate procedure, you should use lo_sql->execute_ddl ().

Regards.

YS

[Former Member · Accepted solution]

Thanks Yeu,

But execute_ddl only works for CREATE TABLE, INSERT and UPDATE.

Complex queries like

drop procedure SAPFRA.TEST; create procedure SAPFRA.TEST() asbegin select * from SAPFRA."PDM"; end;

doesn't works.

[henrique_pinto · Accepted solution]

Did you try to separate the drop and create procedure statements in two execute_ddl() calls?

[Former Member · Accepted solution]

The error is

sql syntax error: incorrect syntax near ";":

How could I avoid to "CREATE PROCEDURE" without using ";" ?

Discard execute_ddl. Is not implemented for this cases.

[yeushengteo · Accepted solution]

Hi,

Split the processing as suggested above? one for drop and one for create.

>>>

data statement string.

statement = 'drop procedure "SAPFRA"' && |.| && '"TEST"'.

lo_sql->execute_query( statement ).

statement = 'create procedure "SAPFRA"' && |.| && '"TEST"' && | |

                   && 'AS' && | |

                   && 'begin' && | |

                   && 'select * from "SAPFRA"' && |.| && '"PDM"' && |;| && | |

                   && 'end' && |;|.

lo_sql->execute_query( statement ).

<<<

Try if it works.

YS

[schneidertho · Accepted solution]

Hi Leonardo,

which release are you on? Quite soon we will offer a much better way to create procedures from ABAP. We plan to make a new feature available with SP5 for ABAP 7.4. This is called ABAP-managed database procedures.You can use methods as wrapper for SQLScript code then. That means you can write SQLScript in the method body. Might be helpful for you.

Best regards

Thorsten

[Former Member · Accepted solution]

Thanks, it helps a lot!

[Former Member · Accepted solution]

Thanks. Release date of it?

[former_member222157 · Accepted solution]

Here comes new feature from SAP

Introduction into ABAP Managed Database Procedu... | SAP HANA

Implementing an ABAP Managed Database Procedure... | SAP HANA

[rindia]

Hi Leonardo,

This document shows you the step by step process for how to create and consume procedure in ABAP.

http://scn.sap.com/docs/DOC-41604

Regards

Raj

[Former Member]

Thanks Raj:

This is not what I am looking for. This document is how to consume procedures, not create them from ABAP.