topic Re: Dynamic SQL execution, Parse SQL in Application Development and Automation Discussions

Dynamic SQL execution, Parse SQL

Former Member — Tue, 05 Aug 2008 16:38:35 GMT

Hi Guys,

I need to implement a very complex requeriment in ABAP:

The Problem:

- I receive a String variable, this string contains ANY complex sql statement like as (for example)

*query = u2018SELECT **

FROM sflight AS f

UP TO 10 ROWS

INTO CORRESPONDING FIELDS OF TABLE wa_sflight

WHERE seatsocc < f~seatsmax

*AND EXISTS ( SELECT * FROM spfli*

WHERE carrid = f~carrid

AND connid = f~connid

AND cityfrom = u2018FRANKFURTu2019

AND cityto = u2018NEW YORKu2019 )

AND fldate BETWEEN u201819990101u2032 AND u201820080701u2032.

u2018.

- I mus this string parsed and translate to Open SQL statements and then to execute.

My problem is why can I parse any query and build and perform this dynamic sql ??

Any idea wellcome !

Thanks Guys !

Re: Dynamic SQL execution, Parse SQL

matt — Tue, 05 Aug 2008 16:41:06 GMT

Have you read the ABAP help on dynamic SQL?

matt

Re: Dynamic SQL execution, Parse SQL

Former Member — Tue, 05 Aug 2008 16:45:40 GMT

No easy way that I know of. But you might look at the help for GENERATE SUBROUTINE POOL.

Rob

Re: Dynamic SQL execution, Parse SQL

Former Member — Tue, 05 Aug 2008 16:49:19 GMT

Hi Matt,

Yes, i have read the ABAP help, but not resolve my question because I need this string translate to Open SQL for transparent access to any Data Base technology.

EXEC SQL.

....

ENDEXEC.

Support only native SQL.

Thanks

Re: Dynamic SQL execution, Parse SQL

Former Member — Wed, 06 Aug 2008 11:51:39 GMT

Hi Rob,

Maybe something so:

DATA: query TYPE string.

DATA: tab TYPE STANDARD TABLE OF string,

prog TYPE string,

mess TYPE string,

sid TYPE string.

DATA: result_table TYPE TABLE OF sflight.

fill tab

APPEND 'PROGRAM subpool.' TO tab.

APPEND 'FORM dyncall2 CHANGING result TYPE ANY TABLE.' TO tab.

APPEND 'SELECT * FROM sflight AS f ' TO tab.

APPEND ' INTO CORRESPONDING FIELDS OF TABLE result' TO tab.

APPEND ' WHERE seatsocc < f~seatsmax' TO tab.

APPEND ' AND EXISTS ( SELECT * FROM spfli' TO tab.

APPEND ' WHERE carrid = f~carrid' TO tab.

APPEND ' AND connid = f~connid' TO tab.

APPEND ' AND cityfrom = `FRANKFURT` ' TO tab.

APPEND ' AND cityto = `NEW YORK` ) ' TO tab.

APPEND ' AND fldate BETWEEN `19990101` AND `20080701`.' TO tab.

APPEND 'ENDFORM.' TO tab.

generate dynamic code

GENERATE SUBROUTINE POOL tab NAME prog

MESSAGE mess

SHORTDUMP-ID sid.

perform call

IF sy-subrc = 0.

PERFORM ('dyncall2') IN PROGRAM (prog) CHANGING result_table IF FOUND.

ELSEIF sy-subrc = 4.

MESSAGE mess TYPE 'I'.

ELSEIF sy-subrc = 8.

MESSAGE sid TYPE 'I'.

ENDIF.

The program work very fine, but I need "result_table" for the results (from query) processing....at moment the "result_table" not come with result data. I need this interaction. (query ->Dynamic Program -> Output->other proccess...)

Any idea ?

Thanks !

Edited by: Carlos Baeza on Aug 6, 2008 1:53 PM

Re: Dynamic SQL execution, Parse SQL

Former Member — Wed, 06 Aug 2008 13:01:42 GMT

In the subroutine pool, you should be able to export the results to memory, but I'm not sure how to import it.

Rob

Re: Dynamic SQL execution, Parse SQL

Former Member — Thu, 07 Aug 2008 08:44:57 GMT

Hi Rob,

Ok, I have export/import to memory teste. Two study case:

1.- Static Form. Work very fine and I receive the data from this query.

DATA: result_table TYPE TABLE OF sflight.

PERFORM dyncall.

IMPORT para = result_table FROM MEMORY ID 'RESULTX'.

...

FORM dyncall.

DATA: id TYPE c LENGTH 10 VALUE `RESULTX`.

DATA: res TYPE TABLE OF sflight.

SELECT *

FROM sflight AS f

UP TO 10 ROWS

INTO CORRESPONDING FIELDS OF TABLE res

WHERE seatsocc < f~seatsmax

AND EXISTS ( SELECT * FROM spfli

WHERE carrid = f~carrid

AND connid = f~connid

AND cityfrom = 'FRANKFURT'

AND cityto = 'NEW YORK' )

AND fldate BETWEEN '19990101' AND '20080701'.

EXPORT para = res TO MEMORY ID id.

ENDFORM. "dynCall

2.- Dynamic Form. No Work and in 'IMPORT para = result_table FROM MEMORY ID id.' the sy-subrc variable is 4.

DATA: result_table TYPE TABLE OF sflight.

fill tab

APPEND 'PROGRAM subpool.' TO tab.

APPEND 'FORM dyncall2.' TO tab.

APPEND ' DATA: id TYPE c LENGTH 10 VALUE `RESULT`.' TO tab.

APPEND ' DATA: result TYPE TABLE OF sflight.' TO tab.

APPEND 'SELECT * FROM sflight AS f ' TO tab.

APPEND ' INTO CORRESPONDING FIELDS OF TABLE result' TO tab.

APPEND ' WHERE seatsocc < f~seatsmax' TO tab.

APPEND ' AND EXISTS ( SELECT * FROM spfli' TO tab.

APPEND ' WHERE carrid = f~carrid' TO tab.

APPEND ' AND connid = f~connid' TO tab.

APPEND ' AND cityfrom = `FRANKFURT` ' TO tab.

APPEND ' AND cityto = `NEW YORK` ) ' TO tab.

APPEND ' AND fldate BETWEEN `19990101` AND `20080701`.' TO tab.

APPEND ' EXPORT para = result TO MEMORY ID id. ' TO tab.

APPEND 'ENDFORM.' TO tab.

generate dynamic code

GENERATE SUBROUTINE POOL tab NAME prog

MESSAGE mess

SHORTDUMP-ID sid.

perform call : DYNAMIC TEST

IF sy-subrc = 0.

PERFORM ('dyncall2') IN PROGRAM (prog) IF FOUND. "CHANGING result_table

IMPORT para = result_table FROM MEMORY ID id. " IGNORING STRUCTURE BOUNDARIES. -> All case tested

ELSEIF sy-subrc = 4.

MESSAGE mess TYPE 'I'.

ELSEIF sy-subrc = 8.

MESSAGE sid TYPE 'I'.

ENDIF.

So comic, What you opine ?

Thank.

Re: Dynamic SQL execution, Parse SQL

Former Member — Fri, 08 Aug 2008 07:35:17 GMT

Hi guys,

Problem resolved !!

Change:

PERFORM ('dyncall2') IN PROGRAM (prog) IF FOUND.

To:

PERFORM ('DYNCALL2') IN PROGRAM (prog).

And work very very fine !

Thnaks for help me !!

Edited by: Carlos Baeza on Aug 8, 2008 9:35 AM

Re: Dynamic SQL execution, Parse SQL

Former Member — Fri, 08 Aug 2008 08:19:06 GMT

Thanks for help me !