Solved: Eclipse SQL Console how to have a Double Quotes " ...

P281512 · ‎2022 Sep 23

Question: In Eclipse SQL Console how to have a " DOUBLE QUOTES in string/character literal in OPENSQL
This program works (in SQLGUI)

REPORT zdemo1.

START-OF-SELECTION.

Select  ( fnam~fieldname
          && ' TYPE'
          && ' '
          &&  fnam~rollname
          && '  ,  "'
          && ftxt~ddtext ) AS TEXT
  from dd03l as fnam inner join dd04t as ftxt
  on ( fnam~rollname = ftxt~rollname
  and fnam~as4local = ftxt~as4local
  and fnam~as4vers = ftxt~as4vers
  and ftxt~ddlanguage = 'E'
  and fnam~tabname = 'SPFLI' )
  order by fnam~position
  into table @data(lt_tab).

  cl_demo_output=>display( lt_tab ).
  EXIT.

This fails in Eclipse OpenSQL SQL Console

SELECT  ( fnam~fieldname 
          && ' TYPE'
          && ' '
          &&  fnam~rollname 
          && '  ,  "'
          && ftxt~ddtext ) AS TEXT
FROM dd03l as fnam INNER JOIN dd04t as ftxt
ON ( fnam~rollname = ftxt~rollname
AND fnam~as4local = ftxt~as4local
AND fnam~as4vers = ftxt~as4vers
AND ftxt~ddlanguage = 'E' 
AND fnam~tabname = 'SPFLI' )
ORDER BY fnam~position

If I change " by # then works
https://archer4sap.com/2018/08/23/sql-console-with-eclipse-adt/ has this
Commenting in SQL console: A double quote( “ ) just as ABAP can be used for writing comment in SQL console.

My surmise this comment feature creates a side-effect

Regards
Jayanta

Sandra_Rossi · ‎2022 Sep 23

It's a bug in the backend, class CL_ADT_DP_OPEN_SQL_HANDLER, method GET_INSTANCE, it considers that a double-quote character (") at any position is a comment and removes it (code here is in 7.52):

  METHOD get_instance.
    ...
    IF iv_remove_comments EQ abap_true.

      LOOP AT lt_query_lines INTO lv_query_line.
        IF  lv_query_line IS NOT INITIAL  AND lv_query_line+0(1) NE '*'.  "If the entire line is commented, do not append it to the query
          IF lv_query_line CS '"'.
            lv_query_line = substring_before( val = lv_query_line sub = '"' ). "Ignore the part after " in the query as that is a comment
          ENDIF.
          CONCATENATE lv_query_string lv_query_line INTO lv_query_string RESPECTING BLANKS SEPARATED BY space.
        ENDIF.

      ENDLOOP.

For those who can't wait/can't create a message, and are authorized by their company, they can use the Enhancement Framework to overwrite (enhance the class > insert overwrite method) the method GET_INSTANCE, and do a few changes, e.g. these ones marked ("temporary fix") on my 7.52 Developer Edition:

    DATA lv_keyword_offset   TYPE i.
    DATA lv_last_char_offset TYPE i.
    DATA lv_query_line       TYPE string.
    DATA lv_query_string     TYPE string.
    DATA lv_last_char        TYPE string.
    DATA lt_query_lines      TYPE stringtab.
    data newline type c value cl_abap_char_utilities=>newline.


    lv_query_string = iv_query_string.
* start of temporary fix
    CL_ADT_DP_OPEN_SQL_HANDLER=>custom_to_upper(
*    custom_to_upper(
* end of temporary fix
      CHANGING
        cv_query_string = lv_query_string
    ).
    SPLIT lv_query_string AT cl_abap_char_utilities=>cr_lf INTO TABLE lt_query_lines .
    CLEAR lv_query_string.


    IF iv_remove_comments EQ abap_true.

      LOOP AT lt_query_lines INTO lv_query_line.
        IF  lv_query_line IS NOT INITIAL  AND lv_query_line+0(1) NE '*'.  "If the entire line is commented, do not append it to the query
* start of temporary fix
replace regex `(^([^']*)|^(?:[^']*('(?:[^']|'')*'))+[^"]*)".+$` in lv_query_line with '$1'.
*          IF lv_query_line CS '"'.
*            lv_query_line = substring_before( val = lv_query_line sub = '"' ). "Ignore the part after " in the query as that is a comment
*          ENDIF.
* end of temporary fix
          CONCATENATE lv_query_string lv_query_line INTO lv_query_string RESPECTING BLANKS SEPARATED BY space.
        ENDIF.

      ENDLOOP.

    ELSE.

      CONCATENATE LINES OF lt_query_lines INTO lv_query_string RESPECTING BLANKS SEPARATED BY space.

    ENDIF.
    SHIFT lv_query_string LEFT DELETING LEADING space.
    SHIFT lv_query_string RIGHT DELETING TRAILING space.

    "clear lv_keyword_offset.
    lv_keyword_offset = 100.
    FIND FIRST OCCURRENCE OF 'SELECT ' IN lv_query_string IGNORING CASE MATCH OFFSET lv_keyword_offset.

    IF lv_keyword_offset  NE 0.
      RAISE EXCEPTION TYPE cx_adt_datapreview_common
        EXPORTING
          textid = cx_adt_datapreview_common=>invalid_query_string.
    ENDIF.

    lv_last_char_offset = strlen( lv_query_string ) - 1.
    lv_last_char = lv_query_string+lv_last_char_offset(1).
    CONDENSE lv_last_char.

    "Remove unsupported keyqords
    CLEAR lv_keyword_offset .
    REPLACE ALL OCCURRENCES OF ' APPENDING ' IN lv_query_string WITH ' INTO ' IGNORING CASE.

    FIND FIRST OCCURRENCE OF REGEX  '\s+FOR\s+UPDATE[\s+,.]' IN lv_query_string MATCH OFFSET lv_keyword_offset IGNORING CASE .

    IF sy-subrc EQ 0.
      REPLACE FIRST OCCURRENCE OF REGEX '\s+FOR\s+UPDATE' IN SECTION OFFSET lv_keyword_offset  OF lv_query_string WITH  space IGNORING CASE.
    ENDIF.

    IF lv_last_char NE '.'.
      CONCATENATE lv_query_string '.' INTO lv_query_string.
    ENDIF.


    CREATE OBJECT ro_open_sql_handler EXPORTING iv_query_string = lv_query_string iv_clnt_string = iv_query_string.
* start of temporary fix
    IF iv_new_format EQ abap_true.
*    IF iv_new_format IS SUPPLIED AND iv_new_format EQ abap_true.
* end of temporary fix
      ro_open_sql_handler->mv_new_format_flag = abap_true.
    ELSE.
      ro_open_sql_handler->mv_new_format_flag = abap_false.
    ENDIF.

Result:

Sandra_Rossi · ‎2022 Sep 23

It looks like an ADT bug (or bug in the corresponding ABAP service). Did you look for a SAP note? What ADT version are you using?

P281512 · ‎2022 Sep 23

Hi Sandra

Thanks for prompt reply

The SQL that fails involves only DD03L DD04T directly and SPFLI indirectly.

These will be there in any version of SAP and hence validation will be a breeze in any version of SAP and Eclipse and ADT!

Objective of this SQL is some code generation

My Eclipse Version

Eclipse IDE for Java Developers (includes Incubating components)

Version: 2022-03 (4.23.0) Build id: 20220310-1457

ABAP Development Tools for SAP HANA (Developer Edition) 3.24.1

I tried in a colleague’s ADT 3.28.1 version and get same error

Regards
Jayanta

SELECT ( fnam~fieldname

&& ' TYPE'

&& ' '

&& fnam~rollname

&& ' , "'

&& ftxt~ddtext ) AS TEXT

FROM dd03l as fnam INNER JOIN dd04t as ftxt

ON ( fnam~rollname = ftxt~rollname

AND fnam~as4local = ftxt~as4local

AND fnam~as4vers = ftxt~as4vers

AND ftxt~ddlanguage = 'E'

AND fnam~tabname = 'SPFLI' )

ORDER BY fnam~position

Sandra_Rossi · ‎2022 Sep 23

FYI, I reproduce the same issue, weird message (but no error if I replace " with something else):

2022-09-23 15:41:06 497 - Query execution failed
Only one SELECT statement is allowed.
SELECT ( fnam~fieldname
&& ' TYPE'
&& ' '
&& fnam~rollname
&& ' , "'
&& ftxt~ddtext ) AS TEXT
FROM dd03l as fnam INNER JOIN dd04t as ftxt
ON ( fnam~rollname = ftxt~rollname
AND fnam~as4local = ftxt~as4local
AND fnam~as4vers = ftxt~as4vers
AND ftxt~ddlanguage = 'E'
AND fnam~tabname = 'SPFLI' )
ORDER BY fnam~position

P281512 · ‎2022 Sep 23

Hi Rossa
I have only a P-id
If feasible please report to ADT support and request a fix
I wasted a lot of time and rarely others too might lose confidence!.
Regards
Jayanta

Sandra_Rossi · ‎2022 Sep 23

It's a bug in the backend, class CL_ADT_DP_OPEN_SQL_HANDLER, method GET_INSTANCE, it considers that a double-quote character (") at any position is a comment and removes it (code here is in 7.52):

  METHOD get_instance.
    ...
    IF iv_remove_comments EQ abap_true.

      LOOP AT lt_query_lines INTO lv_query_line.
        IF  lv_query_line IS NOT INITIAL  AND lv_query_line+0(1) NE '*'.  "If the entire line is commented, do not append it to the query
          IF lv_query_line CS '"'.
            lv_query_line = substring_before( val = lv_query_line sub = '"' ). "Ignore the part after " in the query as that is a comment
          ENDIF.
          CONCATENATE lv_query_string lv_query_line INTO lv_query_string RESPECTING BLANKS SEPARATED BY space.
        ENDIF.

      ENDLOOP.

For those who can't wait/can't create a message, and are authorized by their company, they can use the Enhancement Framework to overwrite (enhance the class > insert overwrite method) the method GET_INSTANCE, and do a few changes, e.g. these ones marked ("temporary fix") on my 7.52 Developer Edition:

    DATA lv_keyword_offset   TYPE i.
    DATA lv_last_char_offset TYPE i.
    DATA lv_query_line       TYPE string.
    DATA lv_query_string     TYPE string.
    DATA lv_last_char        TYPE string.
    DATA lt_query_lines      TYPE stringtab.
    data newline type c value cl_abap_char_utilities=>newline.


    lv_query_string = iv_query_string.
* start of temporary fix
    CL_ADT_DP_OPEN_SQL_HANDLER=>custom_to_upper(
*    custom_to_upper(
* end of temporary fix
      CHANGING
        cv_query_string = lv_query_string
    ).
    SPLIT lv_query_string AT cl_abap_char_utilities=>cr_lf INTO TABLE lt_query_lines .
    CLEAR lv_query_string.


    IF iv_remove_comments EQ abap_true.

      LOOP AT lt_query_lines INTO lv_query_line.
        IF  lv_query_line IS NOT INITIAL  AND lv_query_line+0(1) NE '*'.  "If the entire line is commented, do not append it to the query
* start of temporary fix
replace regex `(^([^']*)|^(?:[^']*('(?:[^']|'')*'))+[^"]*)".+$` in lv_query_line with '$1'.
*          IF lv_query_line CS '"'.
*            lv_query_line = substring_before( val = lv_query_line sub = '"' ). "Ignore the part after " in the query as that is a comment
*          ENDIF.
* end of temporary fix
          CONCATENATE lv_query_string lv_query_line INTO lv_query_string RESPECTING BLANKS SEPARATED BY space.
        ENDIF.

      ENDLOOP.

    ELSE.

      CONCATENATE LINES OF lt_query_lines INTO lv_query_string RESPECTING BLANKS SEPARATED BY space.

    ENDIF.
    SHIFT lv_query_string LEFT DELETING LEADING space.
    SHIFT lv_query_string RIGHT DELETING TRAILING space.

    "clear lv_keyword_offset.
    lv_keyword_offset = 100.
    FIND FIRST OCCURRENCE OF 'SELECT ' IN lv_query_string IGNORING CASE MATCH OFFSET lv_keyword_offset.

    IF lv_keyword_offset  NE 0.
      RAISE EXCEPTION TYPE cx_adt_datapreview_common
        EXPORTING
          textid = cx_adt_datapreview_common=>invalid_query_string.
    ENDIF.

    lv_last_char_offset = strlen( lv_query_string ) - 1.
    lv_last_char = lv_query_string+lv_last_char_offset(1).
    CONDENSE lv_last_char.

    "Remove unsupported keyqords
    CLEAR lv_keyword_offset .
    REPLACE ALL OCCURRENCES OF ' APPENDING ' IN lv_query_string WITH ' INTO ' IGNORING CASE.

    FIND FIRST OCCURRENCE OF REGEX  '\s+FOR\s+UPDATE[\s+,.]' IN lv_query_string MATCH OFFSET lv_keyword_offset IGNORING CASE .

    IF sy-subrc EQ 0.
      REPLACE FIRST OCCURRENCE OF REGEX '\s+FOR\s+UPDATE' IN SECTION OFFSET lv_keyword_offset  OF lv_query_string WITH  space IGNORING CASE.
    ENDIF.

    IF lv_last_char NE '.'.
      CONCATENATE lv_query_string '.' INTO lv_query_string.
    ENDIF.


    CREATE OBJECT ro_open_sql_handler EXPORTING iv_query_string = lv_query_string iv_clnt_string = iv_query_string.
* start of temporary fix
    IF iv_new_format EQ abap_true.
*    IF iv_new_format IS SUPPLIED AND iv_new_format EQ abap_true.
* end of temporary fix
      ro_open_sql_handler->mv_new_format_flag = abap_true.
    ELSE.
      ro_open_sql_handler->mv_new_format_flag = abap_false.
    ENDIF.

Result:

P281512 · ‎2022 Sep 24

Hi Sandra
Thanks for your kind help and solution.
I am now working on 1808 S4HANA
Regards
Jayanta

Sandra_Rossi · ‎2022 Sep 30

If you don't have access to SAP Support, maybe the team of thomasfiedler can take that bug and fix it.

bmayhs · ‎2022 Sep 30

Hi Sandra,

Thanks for you in-depth analysis. This helps us with our solution as well. We will take a lot at the issue and try to provide a solution in our standard tooling itself.

-Shyam

P281512 · ‎2022 Oct 09

Hi Sandra

Please help with my unanswered question
https://answers.sap.com/questions/13731202/eclipse-adt-domain-texts-show-in-german-though-log.html
Regards
Jayanta

Sandra_Rossi · ‎2022 Sep 23

No, sorry, I don't have time for that.

I never understood how developers could possibly work without a S-User, and how it's possible that they are not given one by the employer. You're much less efficient if you can't access the notes from SAP Support.

P281512 · ‎2022 Sep 23

Hi Sandra
I am an independent consultant not employee.
My lesson is in short
if SQL Console fails try good old ABAP..

Regards
-jnc

Sandra_Rossi · ‎2022 Sep 23

Employee or consultant, it's the same thing. Just delegate to the client, that will benefit to them too as they seem to use ADT.

bmayhs · ‎2022 Sep 30

Hi Jayanta,

Thanks for reporting the issue. This is indeed a bug from our side. We will try to provide a solution from our side and provide a Note for this as well, for the lower releases.

-Shyam

P281512 · ‎2022 Oct 09

Hi Bala

Please help with my unanswered question
https://answers.sap.com/questions/13731202/eclipse-adt-domain-texts-show-in-german-though-log.html
Regards
Jayanta

Eclipse SQL Console how to have a Double Quotes " in String Literal ABAP OPEN SQL