10-11-2006 10:04 PM
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.
10-11-2006 10:06 PM
Why do you want to use native SQL for this? Open SQL is faster, easier and portable.
Rob
10-11-2006 10:22 PM
Hi Rob!!
I have problems with the tables because the dump say:
Table does not exist in database.
10-11-2006 10:27 PM
dd02t should be there. Try displaying its contents in SE16.
Rob
10-11-2006 10:47 PM
ok, i can see the content. But i have other question, how declaret 'c' and the fields of 'wa' and 'table'
10-11-2006 10:59 PM
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
10-11-2006 11:05 PM
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
10-12-2006 3:39 AM
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
10-12-2006 3:44 PM
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
10-12-2006 4:27 PM
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,
10-13-2006 8:30 PM
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
10-23-2006 8:33 PM
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.
10-23-2006 9:04 PM
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
02-26-2013 1:12 AM
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
10-11-2006 10:19 PM
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
07-20-2015 9:51 AM
07-20-2015 11:09 AM
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