Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Native SQL in ABAP

Former Member
0 Kudos

Hi all!!!!

Can some one tell me how use native sql in ABAP.

I don`t know nothing about this. I have only a report of this kind, but i need convert it with native sql sentences, exec sql..., read database tables,etc.

report.

start-of-selection.

select tabname ddtext from dd02t into table tab

where tabname in tabname

and ddlanguage eq sy-langu.

sort tab by tabname.

clear: v_succ, v_counter, v_totreg.

describe table tab lines v_totreg.

loop at tab.

perform visualiza_por.

clear: it_print, it_table_catalog_info.

move tab-tabname to table_n.

check it_table_catalog_info-size >= size.

clear it_index_keys.

refresh it_index_keys.

it_print-name = it_table_catalog_info-name.

it_print-tspace = it_table_catalog_info-tsname.

orde-name = it_print-name.

orde-size = it_print-size.

append orde.

loop at it_index_keys.

at new ixname.

it_print-ixname = it_index_keys-ixname.

clear: it_index_partition_info,alloc.

refresh it_index_partition_info.

read table it_index_partition_info index 1.

it_print-space = alloc.

it_print-statstime1 = it_index_info-statstime.

append it_print.

endat.

endloop.

endloop.

clear: v_succ, v_counter, v_totreg.

describe table it_print lines v_totreg.

if descendi = 'X'.

Thanks.

Bty.

16 REPLIES 16

Former Member
0 Kudos

Why do you want to use native SQL for this? Open SQL is faster, easier and portable.

Rob

0 Kudos

Hi Rob!!

I have problems with the tables because the dump say:

Table does not exist in database.

0 Kudos

dd02t should be there. Try displaying its contents in SE16.

Rob

0 Kudos

ok, i can see the content. But i have other question, how declaret 'c' and the fields of 'wa' and 'table'

0 Kudos

You don't have to declare C. SQL takes care of it. For the others:


data:wa_tab LIKE dd02t,
     i_tab LIKE TABLE OF dd02t,

Rob

0 Kudos

Good!!

I`m testing the report, but return the dump :

SQL error 170 occurred when executing EXEC SQL

then the thum is :

Invalid interruption of a database selection.

and say that occurs in the part: FETCH NEXT

Message was edited by: Prospectiva Prospectiva

Message was edited by: Prospectiva Prospectiva

0 Kudos

As I said, native SQL has different rules and is more difficult to use. In a DB2 environment, this works:

REPORT ztest MESSAGE-ID 00.

TABLES dd02t.

SELECT-OPTIONS tabname FOR dd02t-tabname.

DATA:wa_tab LIKE dd02t,
     i_tab LIKE TABLE OF dd02t.

EXEC SQL.
  OPEN C FOR
  SELECT *
  FROM  dd02t
  WHERE ddlanguage <b>=</b> :sy-langu
ENDEXEC.

DO.
  EXEC SQL.
    FETCH NEXT C INTO :wa_tab
  ENDEXEC.
  IF sy-subrc <> 0.
    EXIT.
  ENDIF.
  APPEND wa_tab TO i_tab.
ENDDO.

EXEC SQL.
  CLOSE C
ENDEXEC.

SORT i_tab BY tabname.
DELETE i_tab WHERE NOT
  tabname IN tabname.

Rob

0 Kudos

Having said that, I can't see any reason why this code won't work. Please try it:


REPORT ztest MESSAGE-ID 00.

TABLES dd02t.

SELECT-OPTIONS tabname FOR dd02t-tabname.

DATA:wa_tab LIKE dd02t,
     i_tab LIKE TABLE OF dd02t.

DATA: BEGIN OF tab OCCURS 0,
        tabname LIKE dd02t-tabname,
        ddtext  LIKE dd02t-ddtext,
      END   OF tab.

SELECT tabname ddtext
  FROM dd02t
  INTO TABLE tab
  WHERE tabname IN tabname
    AND ddlanguage EQ sy-langu.

SORT tab BY tabname.

It's faster and easier to understand.

Rob

0 Kudos

ok!, the last code can work. but i don`t know what happen with "fetch next" or with other thing....

because now the problem is when call the function: DB02_GET_TB_DETL_ANALYSIS_DB2.

this send a dump:

Table does not exist in database.

The termination occurred in line 26 of the source code of the (Include)

program "LDB02_DB2U20"

of the source code of program "LDB02_DB2U20" (when calling the editor 260).

Processing was terminated because the exception "CX_SY_NATIVE_SQL_ERROR"

occurred in the

procedure "DB02_GET_TB_DETL_ANALYSIS_DB2" "(FUNCTION)" but was not handled

locally, not declared in the

RAISING clause of the procedure.

The procedure is in the program "SAPLDB02_DB2 ". Its source code starts in line

7

of the (Include) program "LDB02_DB2$20 ".

and indicate this part in the code of the function:

000240 ?

000250 ? EXEC SQL.

> ? SELECT NAME, CREATOR,

000270 ? TYPE, DBNAME,

000280 ? TSNAME, COLCOUNT,

000290 ? NPAGES, KEYCOLUMNS,

0 Kudos

Have you resolved this?

If you are trying to run DB02_GET_TB_DETL_ANALYSIS_DB2, then the problem isnot with DD02TF, but the system table SYSIBM.SYSTABLES used by this FM.

Rob

0 Kudos

Hi!

What can i do?? the problem is with the table sysibm.systables, i don´t know how can i work with function DB02_GET_TB_DETL_ANALYSIS_DB2, as my code calls this function, but show me a dump, for the system table.

0 Kudos

Bear in mind that this is an RFC enabled FM. So it is meant to be run from other systems. So perhaps it would run on a CRM or SRM system. But if the table doesn't exist (and that's what the error says), then, there's not much you can do.

Rob

0 Kudos

Hello Rob,

Sorry to bother you. But since you know  a lot about ABAP and SQL youcould help me.

This is what I am trying to do. I have created ZTABLES in SAP and also our ORACLE DBA have created tables in ORACLE backend. Our client using .net and wants to log into oracle database and upload data from ORACLE tables.

I want to insert data from ZTABLES in our ABAP program to backend ORACLE tables. How can I do this in native SQL? There will be multiple records. I want to loop thru each record in ZTABLE and insert into the ORACLE table.

I appreciate your help.

Thanks,

James

Former Member
0 Kudos

In any event, you'll have to use a cursor:


  EXEC SQL.
    OPEN C FOR
    SELECT *
    FROM  dd02t
    WHERE ddlanguage eq :sy-langu  
  ENDEXEC.

  DO.
    EXEC SQL.
      FETCH NEXT C INTO :wa_tab
    ENDEXEC.
    IF sy-subrc <> 0.
      EXIT.
    ENDIF.
    APPEND wa_tab TO i_tab.
  ENDDO.

  EXEC SQL.
    CLOSE C
  ENDEXEC.

sort i_tab by tabname.
delete i_tab where not
  tabname in tabname.

Rob

0 Kudos

This message was moderated.

0 Kudos

Hi all,

I tried to execute the same code but its not working for me. Am getting Database error 102 and short dump. Could you pls help me on this.

Regards

Raghu