Odd behavior with cl_sql_statement's 'execute_query' method

[Former Member]

Greetings:

I'm trying to connect to an Oracle database and run a simple query. I'm connecting OK but, oddly, no matter what I send to the DB via "execute_query", no rows are returned and no exceptions are thrown. I could send a totally bogus query, e.g., a misspelled column name, and I'll get the same result, i.e., no exception, no rows. Very peculiar.

I'm wondering if anyone else has seen this problem before and, if so, could offer some solution.

Thanks!

[Private_Member_212937]

Hello Steve King ,

I am a newbie to ADBC statements and running into the same issue. Wondering if you are able to get resolution to this.

Thanks,

Rama

[Sandra_Rossi]

You should better ask the world rather than one guy only, probably many forum guys encountered this issue without posting...

You should run the standard program ADBC_DEMO, and check whether SELECT_INTO_VARIABLES and SELECT_INTO_STRUCTURE subroutines read any rows. If yes, then ADBC and the connection work perfectly, and you need to correct your custom code (take the standard code as an example).

[Private_Member_212937]

Thank you Sandra for the quick response. Next time , I will open a fresh posting.

I found the fix for the solution. My problem is specific to the statement select count(*) from <table>.

It appears even though the statement would return a single value , I have to declare internal table for returned value. Also for Oracle database , it appears we have to execute next_package command at least once to get the result. I found the solution at the following link.

http://stackoverflow.com/questions/30578267/how-to-get-the-row-count-via-adbc-native-sql

[Sandra_Rossi]

well done! BUT I could make it work without an internal table:

  DATA:
    l_stmt         TYPE string,
    l_stmt_ref     TYPE REF TO cl_sql_statement,
    l_res_ref      TYPE REF TO cl_sql_result_set,
    lr_count       TYPE REF TO data,
    l_count        TYPE i.
  DATA go_conn TYPE REF TO cl_sql_connection.

  CREATE OBJECT go_conn.

* create a statement object
  l_stmt_ref = go_conn->create_statement( ).

  l_stmt = 'select count(*) from t000'.
  l_res_ref = l_stmt_ref->execute_query( l_stmt ).

* set output variables
  GET REFERENCE OF l_count INTO lr_count.
  l_res_ref->set_param( lr_count ).

  WRITE : / l_count. " always zero
* read first result line
  l_res_ref->next( ).
  WRITE : / l_count.

  l_res_ref->close( ).

[Private_Member_212937]

Thank you Sandra !  It clears the concepts.