SQL RUNTIME EXCEPTION after sending ASYNCHRONOUS Proxy Message

[Former Member]

Hello folks!

I want to send an asynchronous XI Message from an XI ABAP Proxy client within

a select/fetch loop. But the send method obviously closes the cursor, causing an exception

in the next fetch (NO VALID CURSOR).

I did not expect this, since - according the documentation - an asynchronous message ist send

not before the commit.

To collect all lines to an internal table is not a good idea, since the amount of data ist too much.

So how can i avoid this error?

[former_member334189]

Dear friends of XI,

when an asynchronous outbound proxy is called a database commit is executed implicitely ( SQL command COMMIT WORK). This does not contradict Gunnar's statement as both the Proxy Framework and the local Integration Engine ensure that no ABAP command "COMMIT WORK" is executed. It is up to the application to execute that command in ABAP.

The solution I have proposed to Gunnar is to split the single read into a sequence of individual SELECT statemtents where

(a) each SELECT statement reads a suitable subset of data records from the database table and

(b) the sequence of all SELECT statements performs a complete read of that table.

Best regards,

Harald Keimer

XI Development Support

SAP AG, Walldorf

[Former Member]

Your code shoud be something like this..

In below example I am sending only 1000 records at a time.

CLEAR count.
 
 LOOP AT output-mt_test INTO wa_output.
    count = count + 1.

    APPEND wa_output TO itab.

    IF count = 1000.
      TRY.
          CALL METHOD
            zco_miout_test=>execute_asynchronous
            EXPORTING
              output = itab.
        CATCH cx_ai_system_fault .
      ENDTRY.
      CLEAR count.
      CLEAR itab.
    ENDIF.
  ENDLOOP.

[Former Member]

There is no method 'execute_asynchronous' but only

the method with the name of the async.interface.

May be that has something todo with the problem?

[former_member181962]

Hi Gunnar,

It shouldn't be a problem. In newer versions of sap, the method name is the same as the interface name.

How are your checking this program?

Are you in debug mode?

In debug,there will be commit executed after every step, it might be a problem.

But again, why use fetch-endfetch, why not dump into an internal table and loop the internal table and call the asynchrnous method inside the loop?

Regards,

Ravi Kanth Talagana

[Former Member]

Hello Ravi!

The error occurs also in non-debug mode.

The problem with the internal table is the amount of data!

Gues i have to call the sap support...

[former_member181962]

Hi Gunnar,

The amount of data, is it too huge or too less?

If it is too huge also, it is not a problem.

You are sending the data record by record, isn't it?

The design should be something like this.

select * from <your table> into table <internal table> where <your condition>.
loop at <your internal table> into <your work area>.
* fill the interface tables, structures
* call the method.
* clear the variables, interface tables and structures.
endloop.

Regards,

ravi Kanth Talagana

[Former Member]

Hello Ravi!

We want to send the messages packagewise.

Lets say we have 100.000 lines in our DB-Table.

Each async. msg schould have 10.000 lines.

so we want to send 10 msgs.

thats why we fetch with package size.

The amount can even be much more. So an internal table

with all lines is not a good idea.

[former_member181962]

Hi Gunnar,

here, in my example, we are not sending the data at a time.

We are sending one record at a time.

Anyhow i understand, it is not what you want.

But it is still possible to send packets of data using Sarvesh Singh's code.

You have to collect 10000 record at a time inside the loop and send the data, once your itab has 10000 records.

select * from <DB Table> into table output where <your condition>.

LOOP AT output INTO wa_output.
    count = count + 1.
 
    APPEND wa_output TO itab.
 
    IF count = 10000.
      TRY.
          CALL METHOD
            zco_miout_test=>execute_asynchronous
            EXPORTING
              output = itab.
        CATCH cx_ai_system_fault .
      ENDTRY.
      CLEAR count.
      CLEAR itab.
    ENDIF.
  ENDLOOP.

Regards,

Ravi Kanth Talagana

[Former Member]

> The amount can even be much more. So an internal table

> with all lines is not a good idea.

Don't you select all lines in a internal table and when you send a set of lines then delete them from internal table. I don't see a problem here.

Can you just send us the code of your data fetching, because as per my understanding you have to fetch all the data at once into your internal table and then you can play with it.

[Former Member]

Hello Sarvesh!

Here the code:

create object clientProxyPartner.

open cursor c for

select * from ZZESALES_MD_BP.

do.

fetch next cursor c into table t_ZZESALES_MD_BP package size p_pktsz.

if sy-subrc <> 0. " <<---- here raises the exception withthe second fetch

close cursor c.

exit.

endif.

loop at t_ZZESALES_MD_BP into s_ZZESALES_MD_BP.

clear: l_Partner.

move-corresponding s_ZZESALES_MD_BP to l_Partner.

append l_Partner to t_Partner.

endloop.

PartnerOutput-ZZESALES_MD_BP = t_Partner.

try.

call method clientProxyPartner->ZZESALES_MD_BP_OA

EXPORTING

output = PartnerOutput.

clear: t_ZZESALES_MD_BP[].

catch CX_AI_SYSTEM_FAULT.

exit.

endtry.

enddo.

[Former Member]

   create object clientProxyPartner.
   
   open cursor c for
   select * from ZZESALES_MD_BP.
 
  do.
     fetch next cursor c into table t_ZZESALES_MD_BP package size p_pktsz.

    if sy-subrc NE 0.  " <<---- here raises the exception withthe second fetch
     close cursor c.
      exit.
    endif.

   loop at t_ZZESALES_MD_BP into s_ZZESALES_MD_BP.
       clear: l_Partner.
       move-corresponding s_ZZESALES_MD_BP to l_Partner.
      append l_Partner to t_Partner.
    endloop.

  enddo. 

The program code looks ok. Just for testing purpose comment rest of the code except as shown above and then debugg it and see if you are getting the same issue.

Regards,

Sarvesh

[Former Member]

With this coding: no problem, no exception...

So it's prooved that the async call destroys the cursor.

[Former Member]

Yes, that was my feeling too that's why I asked you to test like that. So now you know what to do next )

Regards,

Sarvesh

[Former Member]

Hi,

Havent tried this but can u put commit work after the call method and free the object and then create object in the next fetch again...

Not a very robust design though

Regards

Vijaya

[Former Member]

Hi,

It shouldn't be a problem at all, because we did exactly same kind of mechanism due to large amount of data.

Plz check you code again and clear all variables & work areas after your conditon meets within the loop so that in next loop you shoul not sent those previous values.

Regards,

Sarvesh

[Former Member]

Hi!

The coding is actually very simple:

create object ClientProxy.

open cursor c for select ...

do.

fetch next cursor c into ... package size...

if sy-subrc eq 4.

close cursor c.

exit.

endif.

do some mappings...

try.

call method clientProxy ...

endtry.

The first loop works fine and a can see the uncomitted message in the monitor.

With the next fetcht i get the exception: CX_SY_OPEN_SQL_DB

endo.