topic Re: Using ADBC with CDS View - Language Session Parameter is Blank in Application Development and Automation Discussions

Using ADBC with CDS View - Language Session Parameter is Blank

martinc_mb38 — Tue, 10 Jan 2023 09:03:55 GMT

Hi All,

I thought I would just post this here before logging an incident with SAP, in case anyone knows more about this.

When reading a CDS view using ADBC (i.e. with CL_SQL_STATEMENT and friends), it seems that when querying a CDS view, the $session.system_language built-in session variable is blank.

The following example shows a CDS view that joins T356 and T356_T and uses the login language of the user to retrieve PM priorities and their associated texts:

define view Z_PRIOK as 
select from t356 as t1
left outer join t356_t as t2
    on t1.artpr = t2.artpr
   and t1.priok = t2.priok
   and t2.spras = $session.system_language
{
    t1.artpr,
    t1.priok,
    t2.priokx,
}<br>

When querying this view in SE16 or in a program with OpenSQL, the PRIOKX field is filled with the description.

However, when using the ADBC classes to query the table with the same query (ensuring the client is the current client), the PRIOKX field returns blank.

One can even extend the view to show the value of the session language variable as follows:

define view Z_PRIOK as 
select from t356 as t1
left outer join t356_t as t2
    on t1.artpr = t2.artpr
   and t1.priok = t2.priok
   and t2.spras = $session.system_language
{
    t1.artpr,
    t1.priok,
    t2.priokx,
    $session.system_language as lang
}<br>

In an ADBC query, the LANG column, containing the language session variable is blank.

Perhaps there is a workaround for this, either by setting something before the ADBC query, or perhaps there is something that must be set in the definition of the CDS view?

Re: Using ADBC with CDS View - Language Session Parameter is Blank

ThorstenHoefer — Tue, 10 Jan 2023 09:25:38 GMT

Please check following example:

https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-us/abencds_f1_session_variable.htm

@AbapCatalog.sqlViewName: 'DEMO_CDS_T100'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_select_t100
as select from
demo_cds_select_t100_langu( p_langu: $session.system_language )
{
*
}    
@AbapCatalog.sqlViewName: 'DEMO_CDS_T100_LG'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_select_t100_langu
with parameters
@Environment.systemField:#SYSTEM_LANGUAGE
p_langu : lang
as select from
t100
{
*
}
where
sprsl = :p_langu
and arbgb = 'SABAPDEMOS'

Re: Using ADBC with CDS View - Language Session Parameter is Blank

martinc_mb38 — Tue, 10 Jan 2023 09:43:20 GMT

Hello Thorsten,

Can you explain a bit what is going on in this example?

I see you are referencing $session.system_language in the view definition, but my whole point is that in an ADBC query it is blank.

Also, the link to the help does not mention ADBC anywhere, so I am at a bit of a loss as to what this example is supposed to show.

Thanks,

Martin

Re: Using ADBC with CDS View - Language Session Parameter is Blank

ThorstenHoefer — Tue, 10 Jan 2023 09:51:46 GMT

Please check following view:

define view demo_cds_select_t100
as select from
demo_cds_select_t100_langu( p_langu: $session.system_language )
{
*
}

try to select the data from the view demo_cds_select_t100_langu directly with

select * from emo_cds_select_t100_langu( p_langu: 'E' )

Re: Using ADBC with CDS View - Language Session Parameter is Blank

martinc_mb38 — Tue, 10 Jan 2023 10:17:28 GMT

Thanks so, if I understand correctly, you are working around the problem by making the language a parameter.

But using ADBC means you are using native SQL, so parameters to CDS views would not be possible.

Moreover, using a parameter means that the consumer of the view must reference the language, which I am trying to avoid.

Re: Using ADBC with CDS View - Language Session Parameter is Blank

jmodaal — Tue, 10 Jan 2023 17:05:25 GMT

Hello,

while trying to reproduce the problem, I fell into a nasty trap. Maybe it is the same issue like in your SQL (you did not post it, so it's a guess only).

With 'Select * from Z_PRIOK...' in native SQL you will also get the MANDT field back, despite it is not defined in your CDS view. If in your definition of the result table there is no field for the MANDT, the target area does not fit to the columns from the table access and you will find values or parts of values of other columns in the columns of your result table.

So with this:

report z_test1.
types: begin of TResult,
         mandt  type mandt,
         artpr  type artpr,
         priok  type priok,
         priokz type char20,
         langu  type char1,
       end of TResult.
data: result_tab type standard table of TResult with empty key.
try.
    DATA(result) = NEW cl_sql_statement( )->execute_query(
                `select * from Z_PRIOK where MANDT = SESSION_CONTEXT('CLIENT')` ).
    result->set_param_table( itab_ref = REF #( result_tab ) ).
    if ( result->NEXT_PACKAGE( ) > 0 ).
      cl_demo_output=>display( result_tab ).
    endif.
  catch CX_PARAMETER_INVALID into data(exParmInvalid).
    write:/ 'Error:', exParmInvalid->get_text( ).
  catch CX_SQL_EXCEPTION into data(exSql).
    write:/ 'Error:', exSql->get_text( ) .
endtry.

the result is fine:

But without the MANDT

types: begin of TResult,
         artpr  type artpr,
         priok  type priok,
         priokz type char20,
         langu  type char1,
       end of TResult.

the result is nonsense (first 2 chars of MANDT in column ARTPR, first char of ARTPR in column PRIOK etc.):

So, maybe the problem is in the target of the projection list instead of the variable $session.system_language...?

Kind regards

Jan

Re: Using ADBC with CDS View - Language Session Parameter is Blank

martinc_mb38 — Wed, 11 Jan 2023 05:05:04 GMT

Hello Jan,

Thank you for taking the time to reproduce my issue! In my testing, I have made sure to select with the client and match the columns, as shown in my screenshot below.

Would you mind telling me on what database and ABAP release you are running? We are on Oracle and 750. I wonder if it is perhaps a database or kernel issue (or a combination of the two).

I have in the meantime logged an incident with SAP as I believe this to be a problem with the standard.

Kind Regards,

Martin

Re: Using ADBC with CDS View - Language Session Parameter is Blank

ThorstenHoefer — Wed, 11 Jan 2023 06:51:36 GMT

If you uning ADBC in ABAP you can use the content of sy-langu to fill the parameter.

SELECT, CDS View with Input Parameters - ABAP Keyword Documentation (sap.com)

select * from demo_cds_select_t100_langu( p_langu: sy-langu )

Re: Using ADBC with CDS View - Language Session Parameter is Blank

jmodaal — Wed, 11 Jan 2023 10:25:39 GMT

Hello martin_mbsa,

tested on

SAP_BASIS 757, SP 0 with HDB 2.00.065.00.1665753120

SAP_BASIS 750, SP 22 with Oracle 19.16.0.0.0
(using 'SESSION_CONTEXT('CLIENT')' does not work in this environment, here I have to use a literal for the client).

Works.

Kind regards

Jan