OCI_GetSqlIdentifier() returns invalid values for Scrollable Statements when called more than once

[unficyp]

Hi,
using 4.7.6, i noticed a strange behaviour with GetSqlIdentifier and ocilib::Statement::FetchScrollable.
Demo:

#include <iostream>
#include "ocilib.hpp"

int main() {
    try
    {
        ocilib::Environment::Initialize(ocilib::Environment::Threaded);
    }
    catch(ocilib::Exception &ex)
    {
        std::cout << ex.GetMessage() << std::endl;
        exit(-1);
    }

    ocilib::Connection con("db", "user", "pwd");
    std::cout << "Connected to: " << con.GetServerMajorVersion() << "." <<
                                 con.GetServerMinorVersion() << "." << con.GetServerRevisionVersion() << std::endl;
    ocilib::Statement st(con);
    st.SetFetchMode(ocilib::Statement::FetchScrollable);
    st.Prepare("select count(*) from dual connect by level<=:lvl");

    int i = 10;
    st.Bind(":lvl", i, ocilib::BindInfo::In);

    st.ExecutePrepared();
    std::cout << "sql_id_1: " << st.GetSqlIdentifier() << std::endl;

    i = 100;
    st.ExecutePrepared();
    std::cout << "sql_id_2: " << st.GetSqlIdentifier() << std::endl;

    con.Close();
    ocilib::Environment::Cleanup();

    return 0;
}

In m environment i get:

Connected to: 19.18.0
sql_id_1: g5d40udydrfny
sql_id_2: g5d40udydrfny

Process finished with exit code 0

which is correct. If i enable fetch-mode scrollable (line st.SetFetchMode(ocilib::Statement::FetchScrollable);) i get:

Connected to: 19.18.0
sql_id_1: g5d40udydrfny
sql_id_2: ?�

Process finished with exit code 0

sql_id_2 looks wrong - shouldn't this be the same as sql_id_1 ?
Do i miss something ?

thx & regards,
gerald

[vrogier]

Hi,

To be honest, I have never tried this workflow (get sql identifier on scrollable cursors).
I will investigate that asap.

Regards,

Vincent

[vrogier]

Hi,

I did have a look at it.
Every time a statement is executed and if the OCI client supports it, OCILIB retrieves the SQL ID of the just executed statement from the OCI client library (OCILIB behavior could be optimized to only do it after the 1st execution of a given statement but that's another topic).
There is indeed a behavior difference between using or not using scrollable cursors.
When not using scrollable cursors, every time OCILIB asks the OCI client for the SQL ID of the same executed statement, it gets the same values (value content and value size).
When using scrollable cursors, during the 1st attempt to get the SQL ID, OCILIB gets the SQL ID content and size from OCI. After re execution, OCILIB asks again the SQLID content and size. OCI client returns the same size as for the 1st execution but it does not fill the content :(

@cjbj It seems that querying the attribute OCI_ATTR_SQL_ID on the OCI_HTYPE_STMT after a executing statement wirh mode flags OCI_STMT_SCROLLABLE_READONLY has a weird behavior

• first execution : works fine and OCIAttrGet() returns a value and a size
• later execution : same size is returned but value is not !

@unficyp I guess if OCILIB was querying the SQL ID only after the first execution, it will solve the issue (as OCI client has a weird behavior for subsequent calls). I will create a v4.7.7 bfranch with this change.

Regards,

Vincent

[vrogier]

Created v4.7.7 branch with the optimization that computes the SQL ID only once.

I am still questioning the OCI client behavior (the issue could be in OCILIB usage of the OCI API but regarding current documentation of OCI API, I am more thinking that the issue is on OCI side).

Regards,

Vincent

[cjbj]

@vrogier an OCI testcase would help us.

[cjbj]

@unficyp What you are doing with scrollable cursors. How many rows are in your queries? How long do you hold the cursors open? I'm interested to know your usage & design pattern. Sure scrollable cursors have their place, but they do tie up server resources.

[vrogier]

@vrogier an OCI testcase would help us.

@cjbj Here it is :)
When executing this pure OCI test case with exec_mode = OCI_DEFAULT, SQL ID can be retrieved after any (re)execution if the same prepared statement.
When executing it with exe_mode = OCI_STMT_SCROLLABLE_READONLY, SQL ID is retrieved after the 1st execution and then no value returned for subsequent executions of same prepared statement.

I aggree this worflow might not make sense. But is this behavior an expected one ?

test_case_oci_issue_get_sql_id_scrollable_cursor.txt

[unficyp]

@cjbj i discovered this by accident while playing around with prepared statements and resultsets - i don't have any usecase - yet

[cjbj]

We'll send the testcase to our bug-analysis group and let them handle it.