topic Re: Odd behavior with cl_sql_statement's 'execute_query' method in Application Development and Automation Discussions

Odd behavior with cl_sql_statement's 'execute_query' method

Former Member — Thu, 01 Dec 2011 15:37:38 GMT

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!

Re: Odd behavior with cl_sql_statement's 'execute_query' method

Private_Member_212937 — Sat, 02 Apr 2016 14:47:55 GMT

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

Re: Odd behavior with cl_sql_statement's 'execute_query' method

Sandra_Rossi — Sat, 02 Apr 2016 15:16:52 GMT

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).

Re: Odd behavior with cl_sql_statement's 'execute_query' method

Private_Member_212937 — Sat, 02 Apr 2016 19:21:01 GMT

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

Re: Odd behavior with cl_sql_statement's 'execute_query' method

Sandra_Rossi — Sat, 02 Apr 2016 20:26:30 GMT

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( ).

Re: Odd behavior with cl_sql_statement's 'execute_query' method

Private_Member_212937 — Tue, 05 Apr 2016 14:39:47 GMT

Thank you Sandra ! It clears the concepts.