topic Native SQL failing to fetch data from external database. in Application Development and Automation Discussions

Native SQL failing to fetch data from external database.

Former Member — Mon, 24 Aug 2015 12:49:52 GMT

The Native SQL used for fetching data from external database is not working. The select statement is neither giving any dump nor fetching anything. It is executing for very long time giving time out dump when run in foreground. We have other reports where the same SQL is working fine with same fields fetched from the view used in DB link. Only for one such report we are facing this issue.

We have checked the view from the external database end and it seems to be fine.

Please help to solve this issue with your valuable inputs.

Re: Native SQL failing to fetch data from external database.

Former Member — Mon, 24 Aug 2015 15:46:20 GMT

can you provide the statement you are using for fetching and the database you are using

Re: Native SQL failing to fetch data from external database.

Former Member — Mon, 24 Aug 2015 15:53:05 GMT

Hi Prakash,

This is the statement i am using.

EXEC SQL.

select hr_id, lastname, firstname, smtp_extern, unique_id,
              prop_addr1, prop_addr3, hr_db_name
              from peda_gddb into :gs_gddb
               where hr_db_pers_id = :lf_pernr
ENDEXEC.

Re: Native SQL failing to fetch data from external database.

Former Member — Mon, 24 Aug 2015 16:07:20 GMT

is it a cluster table

Re: Native SQL failing to fetch data from external database.

Former Member — Mon, 24 Aug 2015 16:27:43 GMT

YOU DINT MENTIONED WHICH DATABASE YOU ARE USING. IS THE DATABASE IS OF YOU SAP SYSTEM OR OUT SIDE SAP. IF ITS OUT SIDE HOW YOU HAVE PROVIDED THE CREDENTIALS

Re: Native SQL failing to fetch data from external database.

Former Member — Mon, 24 Aug 2015 16:52:48 GMT

Hi Prakash,

peda_gddb is a view created in a DB link to connect our SAP system to one non sap external database.

We are trying to fetch data using this DBlink.

Re: Native SQL failing to fetch data from external database.

Former Member — Mon, 24 Aug 2015 17:02:35 GMT

have you tested the connections between the database and sap. Are you able to get data from other tables

Re: Native SQL failing to fetch data from external database.

Former Member — Mon, 24 Aug 2015 17:09:17 GMT

if connection is ok try your staments like this. this will give whether its going in infinite loop due to wrong config of view or due to indexing problem of tables in other database.

types: begin of ty_lfa1,

lifnr type lfa1-lifnr,

name1 type lfa1-name1,

end of ty_lfa1.

data: wa_lfa1 type ty_lfa1.

EXEC SQL PERFORMING loop_output.

SELECT LIFNR, NAME1

INTO :WA_LFA1

FROM LFA1;

ENDEXEC.

form loop_output.

write: / wa_lfa1-lifnr.

endform.

Re: Native SQL failing to fetch data from external database.

raphael_almeida — Mon, 24 Aug 2015 17:50:20 GMT

Prakash,

Please, don't use UPPERCASE when you writing.

Warm regards,

Raphael Pacheco.

Re: Native SQL failing to fetch data from external database.

raphael_almeida — Mon, 24 Aug 2015 17:56:40 GMT

Hi Nithin,

This is a third party table right?

I suggest the utilization of classes CL_SQL* instead EXEC_SQL.

For examples, please open in SE38 the program ADBC_DEMO.

Warm regards,

Raphael Pacheco.

Re: Native SQL failing to fetch data from external database.

Former Member — Tue, 25 Aug 2015 04:44:38 GMT

Hi Raphael,

Thanks for your suggestions. I started activities from few days only. I have written Caps only in code.

Re: Native SQL failing to fetch data from external database.

raphael_almeida — Tue, 25 Aug 2015 04:52:13 GMT

Oks

Re: Native SQL failing to fetch data from external database.

Former Member — Tue, 25 Aug 2015 06:26:25 GMT

Hi Pacheco,

Thanks for your inputs,

But this EXEC_SQL was working fine all this while and it was implemented in 2007.

And also surprisingly the same query is working fine in UAT and development systems and these two are also using the same DB_link as production to the external system.

Re: Native SQL failing to fetch data from external database.

raphael_almeida — Tue, 25 Aug 2015 06:41:00 GMT

Great!

I put an example he used when I used the EXEC_SQL, I believe this will help you.

Warm regards,

Raphael Pacheco.

Re: Native SQL failing to fetch data from external database.

Former Member — Tue, 25 Aug 2015 14:09:37 GMT

Hi Raphael,

Actually i tried ST05 for tracing the SQL. I found that the FETCH statement is missing and ROLLBACK WORK ON CONNECTION is taking very long time.

Please help me in identifying the root cause here.