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: 

Problem in using Native Sql with HANA

0 Kudos

Hi,

I am fetching HANA views data using native sql in abap. I have observed a problem that sap is allowing only 29 execute sql commands statements in a row and gives dump for 30th query. My code is like below


  LO_CONN = CL_SQL_CONNECTION=>GET_CONNECTION( CONN1).

  LO_SQL_STMT = LO_CONN->CREATE_STATEMENT( ).



LOOP AT T_RANGE2 INTO WA_RANGE2.

       CONCATENATE 'INSERT INTO  VBELN_TEMP1 VALUES (' ''''WA_RANGE2'''' ')'  INTO LV_SQL_INS.

        TRY.

          LO_RESULT = LO_SQL_STMT->EXECUTE_QUERY( LV_SQL_INS ).

  

        CATCH CX_SQL_EXCEPTION INTO LX_SQL_EXC.

             LV_TEXT = LX_SQL_EXC->GET_TEXT( ).

            MESSAGE LV_TEXT TYPE 'E'.

      ENDTRY.

ENDLOOP.

Here I am passing a value range to VBELN_TEMP1 table of HANA through a loop on T_RANGE2 internal tab.

It works fine till TABX 29 and gives exits the program on 30th iteration.

Please help if anyone has observed same problem and found solution on it.

Thanks,

Shriniwas

1 ACCEPTED SOLUTION

Former Member
0 Kudos

For every loop iteration a new connection has been created with the old one still existing. You can avoid this by using COMMIT WORK at the end of TRY CATCH block.

       CATCH CX_SQL_EXCEPTION INTO LX_SQL_EXC.

             LV_TEXT = LX_SQL_EXC->GET_TEXT( ).

            MESSAGE LV_TEXT TYPE 'E'.

      ENDTRY.

  COMMIT WORK.  "INS 

ENDLOOP.

lo_conn->close( ). "INS

5 REPLIES 5

lbreddemann
Active Contributor
0 Kudos

My guess here would be that - for some reason - with every loop iteration a new connection is actually used and the old one doesn't get closed.

NetWeaver work processes can have a max. of 30 connections (including the default one) open at any given time, so that might be the reason for the problem.

However, as usual, without the actual error message and the information from the work process developer trace file (dev_wXX) all this is just blind guessing while being hand-cuffed in a safe that someone had thrown into a deep river...

- Lars

0 Kudos

Hi Lars,

Your answer sounds logical. Actually loop is exiting without giving any error message so I am not able to understand the problem.

- Shriniwas

0 Kudos

Alright, in that case, I'd say the most likely reason is that the loop condition is FALSE at that point, e.g. your range list had been completely iterated.

Without a reproducible example, that's about what we can tell...

- Lars

Former Member
0 Kudos

For every loop iteration a new connection has been created with the old one still existing. You can avoid this by using COMMIT WORK at the end of TRY CATCH block.

       CATCH CX_SQL_EXCEPTION INTO LX_SQL_EXC.

             LV_TEXT = LX_SQL_EXC->GET_TEXT( ).

            MESSAGE LV_TEXT TYPE 'E'.

      ENDTRY.

  COMMIT WORK.  "INS 

ENDLOOP.

lo_conn->close( ). "INS

0 Kudos

Hi Karthik,

Your solution worked for me. Thanks a lot. Great help..

Shriniwas