Dynamic SQL Error

[former_member625844]

We have a RFC which import SQL clause and return result.

FUNCTION EXTRACT_TABLE_DATA.
*"----------------------------------------------------------------------
*"*"import
*"  TABLES
*"      FIELDS STRUCTURE  ZSQL_CLAUSE_ELEMENTS
*"      FROMCLAUSE STRUCTURE  ZSQL_CLAUSE_ELEMENTS
*"      WHERECLAUSE STRUCTURE  ZSQL_CLAUSE_ELEMENTS
*"      DATA STRUCTURE  ZTABLEROWS
*"----------------------------------------------------------------------
  TYPE-POOLS: abap.
  DATA:
        columnName TYPE SO_TEXT,
        fieldDataDescrRef  TYPE REF TO abap_componentdescr,
        numberFields TYPE i,
        fieldDescr TYPE abap_componentdescr,
        fieldname TYPE string,
        fieldDescrTab TYPE abap_component_tab,
        rowStructDescr TYPE REF TO cl_abap_structdescr,
        rowReference TYPE REF TO data,
        returnRowString TYPE string,
        dataFieldString TYPE string,
        dataline LIKE data,
        fromClauseRow TYPE ZSQL_CLAUSE_ELEMENTS,
        fromClauseString TYPE string,
        whereClauseRow TYPE ZSQL_CLAUSE_ELEMENTS,
        whereClauseString TYPE string,
        fieldsRow TYPE ZSQL_CLAUSE_ELEMENTS,
        FNAME(600) VALUE 'myfile'.

  FIELD-SYMBOLS:
          <datarow> TYPE ANY,
          <datafield> TYPE ANY.


* CREATE DataStructure with field names
* Datatypes are read from fieldnames of FIELDS input table
  DESCRIBE TABLE FIELDS LINES numberFields.
  LOOP AT FIELDS INTO fieldsRow.
    fieldname = SY-TABIX.
* names need to be unique and must start with a char
    CONCATENATE 'string' fieldname INTO fieldname.
    CONDENSE fieldname.
    fieldDescr-name = fieldname.
* for dictionary lookup we need to change columnnames from Open SQL
* to dictionary notation
    columnName = fieldsRow-TEXT.
    REPLACE FIRST OCCURRENCE OF SUBSTRING '~' IN columnName WITH '-' RESPECTING CASE.

    fieldDescr-type ?= cl_abap_typedescr=>describe_by_name( columnName ).
    APPEND fieldDescr TO fieldDescrTab.
  ENDLOOP.

  rowStructDescr = cl_abap_structdescr=>create( fieldDescrTab ).

* now we create the actual data structure in memory
  create data rowReference type HANDLE rowStructDescr.
* finally we assign it to the Field-symbol used by the select statement
  ASSIGN rowReference->* TO <datarow>.
* End Create DataStructure

* to simplify calls we concatenate from and whereclause into strings
* this way caller doesn't need to check word wrappings
  fromClauseString = ''.
  LOOP AT FROMCLAUSE INTO fromClauseRow.
    CONCATENATE fromClauseString fromClauseRow-TEXT INTO fromClauseString.
  ENDLOOP.

  whereClauseString = ''.
  LOOP AT WHERECLAUSE INTO whereClauseRow.
    CONCATENATE whereClauseString whereClauseRow-TEXT INTO whereClauseString.
  ENDLOOP.
*IF sy-uname = 'SIENIT01'.
* break-point.
*ENDIF.
* Now start actual select operation
  SELECT (FIELDS) FROM (fromClauseString) INTO @<datarow> WHERE (whereClauseString).
* we read all fields of the current row, cast it to string and
* concatenate it into a dataline with division chars.
    CLEAR: returnRowString.
    DO numberFields TIMES.
      ASSIGN component sy-index of structure <datarow> to <datafield>.
      dataFieldString = <datafield>.
      CONCATENATE returnRowString '^' datafieldstring INTO returnRowString.
    ENDDO.
    dataline = returnRowString.
* finally dataline is added to the return table.
    INSERT dataline INTO TABLE data.
  ENDSELECT.
open DATASET FNAME FOR APPENDING IN TEXT MODE ENCODING DEFAULT.
TRANSFER whereClauseString TO FNAME.
IF SY-SUBRC <> 0.
  WRITE: 'SY-SUBRC:', SY-SUBRC,
       / 'System Message:'.
ENDIF.
CLOSE DATASET FNAME.
ENDFUNCTION.

The four table parameters fields,fromclause,whereclause,data are all table of string.

I use the following parameter set and it runs ok.

fields: ekko~ebeln
fromclause: ekko 
whereclause: 
data:

But I add another row in fields table and it has error.

fields: ekko~ebeln 
        ekko~lifnr
fromclause: ekko
whereclause: 
data:

The error is in SQL clause. I checked the fields ebeln and lifnr are in the ekko table. So what's the correct way to use dynamic fields in select? Thx.

[Sandra_Rossi]

If you use the strict SQL syntax (automatically activated because of @), everything must be in strict SQL syntax.

Just try to write the SQL statement statically, and the compiler will tell you what the issue is.

NB: you should protect your function against SQL injection attacks.

[former_member625844]

@sandra.rossi. thx. Looks like that's the reason. I remove @ and it work now.

[former_member625844]

@sandra.rossi。 One more question. For the statical SQL you mentioned, do you mean I convert the (fields) table to a long String instead? Thx.

[Sandra_Rossi]

I mean, just to troubleshoot the compiler message, in debug, paste to Notepad the contents of the 3 variables FIELDS, fromClauseString, and whereClauseString (could be for instance, respectively, "EBELN AUART", "VBAK" and "AUART LIKE '%'").

In your ABAP Editor, write the SQL statically like this, based on the contents:

SELECT EBELN AUART FROM VBAK INTO @<datarow> WHERE AUART LIKE '%'.

You should get the same syntax error as with the dynamic form.

Then adapt it until there's no syntax error anymore.

Solution: either you remove @ to use the non-strict syntax, or you write it with the strict syntax (comma between columns):

SELECT EBELN, AUART FROM VBAK INTO @<datarow> WHERE AUART LIKE '%'.

Now that you understand that commas must be used in variable FIELDS with the strict syntax, you can revert back to the dynamic form and change your logic to initialize FIELDS by inserting a comma between columns.

NB: you can also see that you need SELECT SINGLE because you store the data into only one line.