use of native SQL-select statment

[Former Member]

hi,

in my z-abap i use a native sql, saying the following:

EXEC SQL.

  select agzdau into :gf_agzdau from APZD08/AG
                where AGMAID = '77732' and
                      AGJAHR = 2008 and
                      AGMON  = 09 and
                      AGSPAL = 'ZASALD'
                      %_HINTS AS400 'HARD_CLOSE'
ENDEXEC.

before that i make a connection to the server where the table is stored.

APZD08 is the library and AG is the filename (on a AS/400-machine)

BUT: the file is not always in APZD08-lib, it depends on the employee.

how can i make the lib-name and file flexible:

i have tried it with

select...............from :gf_file

and i have put a value for gf_file before.

but with that i got an SQL-error -104 when executing.

i can't use a hostvariable there it seems.

any ideas ?

reg, Martin

[Former Member]

Hi,

Try this:

DATA: code TYPE TABLE OF rssource-line.

DATA:STRPR TYPE STRING.

data:i type i.

data:ci type c.

data:cstr(200) type c.

APPEND 'REPORT ZTESTING2.' TO CODE.

APPEND 'EXEC SQL.' TO CODE.

i = 8.

ci = i.

concatenate 'select agzdau into :gf_agzdau from APZD0'ci '/AG' into cstr.

append cstr to code.

APPEND ' where AGMAID = ''77732'' and' to code.

APPEND ' AGJAHR = 2008 and' to code.

APPEND ' AGMON = 09 and' to code.

APPEND ' AGSPAL = ''ZASALD'' to code.

APPEND ' %_HINTS AS400 ''HARD_CLOSE''' to code.

ENDEXEC.

INSERT REPORT 'ZTESTING' FROM code.

SUBMIT ZTESTING AND RETURN.

[Former Member]

Ytamar,

this is a workaround, but a GOOD one !!! thanks alot. i haven't tried it, but i am 99% sure

that this will work that way. !

full points for you !

reg, Martin

[JanStallkamp]

Hi.

I would use ADBC instead of EXEC SQL. Then you can concatenate the SQL statement in the way you want it. But in that case you should use host variables in the where condition to get better performance.

So something like the following (untested, no syntax check yet, pseudo code...):

DATA: p_con_name   TYPE dbcon-con_name, 
      p_con_ref    TYPE REF TO cl_sql_connection,
      p_stmnt      TYPE REF TO cl_sql_statement,
      p_result_set TYPE REF TO cl_sql_result_set,
      query        TYPE string. 

p_con_ref = cl_sql_connection=>get_connection( 'YOURCON' ).

LOOP AT it_employees

* CASE your logic
CONCATENATE 'select agzdau from '  table_name 
            'where  AGMAID = ? and AGJAHR = ?' 
       INTO statement SEPARATED BY SPACE.
* ENDCASE

TRY.
  p_stmnt = p_con_ref->create_statement( ).
  GET REFERENCE OF p_agamaid INTO p_paramref.
  p_stmnt->set_param( p_paramref ).
  GET REFERENCE OF p_agjahr INTO p_paramref.
  p_stmnt->set_param( p_paramref ).
  p_result_set = p_stmnt->execute_query( query ).
  GET REFERENCE OF gf_agzdau INTO p_resultref.
  p_result_set->set_param( p_resultref ).
  p_result_set->next( ).
  CATCH CX_SQL_EXCEPTION.
* error handling needed!
  ENDTRY.

ENDLOOP.

p_con_ref->close( ).

Oh, I was assuming that there is only one result. If there can be more (and better style anyway) do a while-loop until next( ) is different from zero.

Best regards,

Jan