topic Re: Native SQL in ABAP in Application Development and Automation Discussions

Native SQL in ABAP

Former Member — Wed, 11 Oct 2006 21:04:09 GMT

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.

clear: v_succ, v_counter, v_totreg.

describe table it_print lines v_totreg.

if descendi = 'X'.

Thanks.

Bty.

Re: Native SQL in ABAP

Former Member — Wed, 11 Oct 2006 21:06:56 GMT

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

Rob

Re: Native SQL in ABAP

Former Member — Wed, 11 Oct 2006 21:19:49 GMT

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

Re: Native SQL in ABAP

Former Member — Wed, 11 Oct 2006 21:22:52 GMT

Hi Rob!!

I have problems with the tables because the dump say:

Table does not exist in database.

Re: Native SQL in ABAP

Former Member — Wed, 11 Oct 2006 21:27:01 GMT

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

Rob

Re: Native SQL in ABAP

Former Member — Wed, 11 Oct 2006 21:47:06 GMT

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

Re: Native SQL in ABAP

Former Member — Wed, 11 Oct 2006 21:59:11 GMT

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

Re: Native SQL in ABAP

Former Member — Wed, 11 Oct 2006 22:05:02 GMT

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

Re: Native SQL in ABAP

Former Member — Thu, 12 Oct 2006 02:39:54 GMT

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

Re: Native SQL in ABAP

Former Member — Thu, 12 Oct 2006 14:44:37 GMT

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

Re: Native SQL in ABAP

Former Member — Thu, 12 Oct 2006 15:27:44 GMT

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

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,

Re: Native SQL in ABAP

Former Member — Fri, 13 Oct 2006 19:30:41 GMT

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

Re: Native SQL in ABAP

Former Member — Mon, 23 Oct 2006 19:33:24 GMT

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.

Re: Native SQL in ABAP

Former Member — Mon, 23 Oct 2006 20:04:46 GMT

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

Re: Native SQL in ABAP

former_member187732 — Tue, 26 Feb 2013 01:12:04 GMT

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

Re: Native SQL in ABAP

Former Member — Mon, 20 Jul 2015 08:51:31 GMT

This message was moderated.

Re: Native SQL in ABAP

Former Member — Mon, 20 Jul 2015 10:09:33 GMT

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