topic Re: Native SQL with a 'WITH <expression> in Application Development and Automation Discussions

Native SQL with a 'WITH

former_member125661 — Sun, 31 Jan 2016 00:43:31 GMT

I am trying to get a system related info from the Database server and I know that this query works when executed by itself. Now, I am trying to execute this in my program. But i am unable to capture the output in a variable lv_pagesteal which I have declared as a char10. I am not sure if I am putting the 'into :lv_pagesteal' at the right place in the code.. I am getting a DBIF_DSQL2_SQL_ERROR . I copied the same code into ST04, native sql command line without the 'into :lv_pagesteal'. This works fine and I get the % Steals..

I am trying to capture % Page Steals information from the Database server.


data : lv_pagesteal(10).

exec sql.


with dbsnap as
(
select
float(pool_drty_pg_steal_clns) as pg_steal,
pool_drty_pg_steal_clns +  pool_drty_pg_thrsh_clns + pool_lsn_gap_clns  as total_clns
from
table(snap_get_db_v97(CAST (NULL AS VARCHAR(128)),-2))
)
select dec((pg_steal / nullif(total_clns,0))*100,5,2) as "% Steals"
into :lv_pagesteal
from dbsnap


endexec.

Re: Native SQL with a 'WITH

Former Member — Sun, 31 Jan 2016 21:09:32 GMT

1) What database is this?

2) There should be a return code with the SQL error. What was that?

Rob

Re: Native SQL with a 'WITH

former_member125661 — Mon, 01 Feb 2016 02:18:46 GMT

This is a DB2.

Database Interface Information

C 1 *** ERROR in prepareStatement[dbdb6.c, 15184] CON = 0 (BEGIN) [dbsldb6trc.c#1675]

C 1 &+ dsql_db6_exec( SQLExtendedPrepare 😞 [IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpect

C 1 &+ owing "ls" from dbsnap into". Expected tokens may include: "END-OF-STATEMENT". SQLST

C 1 &+

C 1 &+ with dbsnap as ( select float ( pool_drty_pg_steal_clns ) as pg_steal, pool_drty_pg_ste

C 1 &+ h_clns + pool_lsn_gap_clns as total_clns from table ( snap_get_db_v97 ( CAST ( NULL AS

C 1 &+ select dec ( ( pg_steal / nullif ( total_clns, 0 ) )*100, 5, 2 ) as "% Steals" from db

C 1 &+ cursor type=NO_HOLD, isolation=UR, cc_release=YES, optlevel=5, degree=1, op_type=18,

C 1 &+ ABAP location info: 'SAPLZKPI', 971

C 1 &+

C 1 &+ ABAP location info 'SAPLZKPI', 971

C 1 &+ SAP user 'XXXXX', transaction code 'SE37'

C 1 &+

C 1 *** ERROR in prepareStatement[dbdb6.c, 15184] (END) [dbsldb6trc.c#1675]

B E ***LOG BY2=> sql error -104 performing EXE [dbds#672]

B E ***LOG BY0=> SQL0104N An unexpected token "?" was found following "ls" from dbsnap into". Ex

Category Installation Errors

Runtime Errors DBIF_DSQL2_SQL_ERROR

Except. CX_SY_NATIVE_SQL_ERROR

Date and Time 01/31/2016 02:51:56

Name Val.

SY-SUBRC 1

SY-INDEX 1

SY-TABIX 1

SY-DBCNT 1

SY-FDPOS 255

SY-LSIND 0

SY-PAGNO 0

SY-LINNO 1

SY-COLNO 1

SY-PFKEY

SY-UCOMM STRT

Re: Native SQL with a 'WITH

retired_member — Mon, 01 Feb 2016 11:38:21 GMT

Hello Shareen,

Answer:

EXEC SQL does not support an INTO clause with WITH.

There's a small parser in the DBI that tries to pick the ABAP specific INTO clause and that supports this only for the "classic" SQL statement SELECT.

Workaround:

Use ADBC.

That's the recommended way anyhow.

Here is my example:

I will add this behavior to the documentation of EXEC SQL immediately

Best

Horst

PS: If you want to stay with EXEC SQL, you might also try to do something without INTO clause: first a OPEN CURSOR ..., then a FETCH.

EXEC SQL.

open dbcur for

with my_scarr as

( select carrid, carrname from scarr )

select carrid, carrname from my_scarr

ENDEXEC.

EXEC SQL.

FETCH NEXT dbcur into :wa

ENDEXEC.