Problem in using Native Sql with HANA

[former_member223897]

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

[lbreddemann]

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

[former_member223897]

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

[lbreddemann]

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]

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

[former_member223897]

Hi Karthik,

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

Shriniwas