topic Re: Problem in using Native Sql with HANA in Application Development and Automation Discussions

Problem in using Native Sql with HANA

former_member223897 — Thu, 06 Aug 2015 06:50:30 GMT

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

Re: Problem in using Native Sql with HANA

lbreddemann — Thu, 06 Aug 2015 07:24:40 GMT

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

Re: Problem in using Native Sql with HANA

former_member223897 — Thu, 06 Aug 2015 07:47:27 GMT

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

Re: Problem in using Native Sql with HANA

lbreddemann — Thu, 06 Aug 2015 08:30:14 GMT

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

Re: Problem in using Native Sql with HANA

Former Member — Thu, 06 Aug 2015 09:46:11 GMT

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

Re: Problem in using Native Sql with HANA

former_member223897 — Thu, 06 Aug 2015 10:13:49 GMT

Hi Karthik,

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

Shriniwas