Question Re: ADBC Statement not working like similar Native SQL in Technology Q&A

ADBC Statement not working like similar Native SQL

casualCoder — Wed, 08 Feb 2023 10:10:18 GMT

Hi,

currently I am trying to switch from static Native SQl to dynamic one with ADBC.

When I try to execute the same SELECT Statement with cl_sql_statement_>execute_query I receive

"Error: Ressource unavailable"

That was the old code:

  
CONSTANTS: c_con_name         TYPE dbcon_name VALUE 'SAP2PWS'. "Defined in DBCO
EXEC SQL.
    connect TO :c_con_name
  ENDEXEC.

 EXEC SQL .
      OPEN dbcur FOR SELECT
         PERSID,
         PERSNR,
            FROM TEST_DB0T.TS$$004.VEPSS_SAP_MIA
    ENDEXEC.<br>

Statement is working, Result set looks fine.

Now with the dynamic SQL, i am receiving above named Error:

    TRY.
    DATA(conn) = cl_sql_connection=>get_connection( c_con_name ).
    DATA(query) = NEW cl_sql_statement( )->execute_query(
      EXPORTING
        statement   = 'SELECT * FROM TEST_DB0T.TS$$004.VEPSS_SAP_MIA' 
    ).
    CATCH cx_sql_exception     "
          cx_parameter_invalid "
          INTO DATA(error).
          MESSAGE error->get_text( ) TYPE 'E'.
    ENDTRY.

Has anyone got a clue how to resolve this?

Database to connect is DB2 (IBM).

Re: ADBC Statement not working like similar Native SQL

RaymondGiuseppi — Wed, 08 Feb 2023 12:30:51 GMT

Look at report ADBC_DEMO (also execute it)

Re: ADBC Statement not working like similar Native SQL

casualCoder — Wed, 08 Feb 2023 12:38:14 GMT

Hi, I know this Report already, but it doesn't help me though. Am I missing something? In my opinion the Statement has a correct Syntax, it seems like Conflict with the given View Name Construction

Re: ADBC Statement not working like similar Native SQL

RaymondGiuseppi — Wed, 08 Feb 2023 14:00:44 GMT

The reason code 00D31020 would be

An attempt was made to use SNA/APPC communications to create an outbound DRDA protocol connection. However, this DDF, as identified by the corresponding resource-type of 00001000 and its Db2 subsystem ID as the corresponding resource-name, was started with an IPNAME value. This process caused DDF to only start its TCP/IP communications support.

The type of resource 00001000 is

DDF 'LOCATION or SUBSYSTEM ID'

Re: ADBC Statement not working like similar Native SQL

casualCoder — Thu, 09 Feb 2023 06:54:16 GMT

Hi, found the Solution by myself.

I forgot to pass the Connection Object to the Statement, without that it generates a new Connection which is not the intended one.

After the call I then have to bind a workarea to access the Result Set:

TRY.
        DATA(conn) = cl_sql_connection=>get_connection( c_con_name ).
        DATA(result_set) = NEW cl_sql_statement( con_ref = conn )->execute_query(
          EXPORTING
            hold_cursor = 'X'
            statement   = | SELECT | &&
                          | PERSID, PERSNR, GVON | &&
                          | FROM X-X-X |
        ).
        result_set->set_param_struct( struct_ref = REF #( wa_pws ) )." Referenz auf Ausgabevariable
        WHILE result_set->next( ) > 0.
            "Do sth
        ENDWHILE.
        conn->close( ).
      CATCH cx_sql_exception     " Ausnahmeklasse für SQL Fehler
            cx_parameter_invalid " Oberklasse für Parameterfehler
        INTO DATA(error).
        MESSAGE error->get_text( ) TYPE 'E'.
    ENDTRY.

Re: ADBC Statement not working like similar Native SQL

Sandra_Rossi — Thu, 09 Feb 2023 08:49:40 GMT

Didn't ADBC_DEMO program help you to see that your query worked, so it meant that it was your code which was faulty? (also debugging ADBC_DEMO to see the differences between ADBC executed by ADBC_DEMO and by your program)