[BUG] Actian/Pervasive cursor fails with specific queries following upgrade from 2.4.7

[NGrey5]

Describe your system

• odbc Package Version: 2.5.0
• ODBC Driver: Pervasive ODBC Interface
• Database Name: Actian Zen / Pervasive
• Database Version: 14
• Database OS: Windows Server 2019
• Node.js Version: 24.18.0
• Node.js OS: Windows 11

Describe the bug
Following an upgrade of odbc from version 2.4.7 , cursors for some queries are resulting in the following error:

[Zen][ODBC Client Interface][LNA][Zen][SQL Engine]Invalid cursor position; no keyset defined.

After looking through the commit history, I saw that 2.4.8 removed the setting of static cursor mode to increase performance. This is not playing nice with this database. Do we have the option to add the specification of a cursor mode?

Expected behavior
Query to successfully run within a cursor

[NGrey5]

After further review, cursors do not work following 2.4.8+ with Actian Zen / Pervasive v14 driver.

Example:

8091 total rows

fetchSize = 10,000 succeeds
fetchSize = 8000 failed on 1st fetch
fetchSize = 4000 failed on 2nd fetch
fetchSize = 2000 failed on 4th fetch

[kadler]

Do we have the option to add the specification of a cursor mode?

I don't see any code that sets the cursor type anymore, so there's unfortunately no immediate workaround.

According to the ODBC spec it should default to a forward-only cursor. Looking at the cursor characteristics, we can see the differences between the two cursor types:

A forward only cursor is non-scrollable and no other attributes are specified
A static cursor is scrollable and either insensitive or unspecified, depending on whether concurrency is set to read-only or not.

The default values are:

• SQL_ATTR_CONCURRENCY => SQL_CONCUR_READ_ONLY
• SQL_ATTR_CURSOR_SCROLLABLE => SQL_NONSCROLLABLE
• SQL_ATTR_CURSOR_SENSITIVITY => SQL_UNSPECIFIED
• SQL_ATTR_CURSOR_TYPE => SQL_CURSOR_FORWARD_ONLY

The query doesn't work with a forward only cursor? We don't do any scrolling anyway (always use SQLFetch which is always forward) so scrollability shouldn't matter. We're not doing inserts, updates, or deletes with the cursor using SQLBulkOperations, so read-only shouldn't matter.

The only issue I can think of is the cursor sensitivity. Is something else changing a row while it's being fetched that's causing the failure? It looks like this reliably happens with some row between rows 6000 and 8000.

We could always add a way to specify the cursor type, but I think it would be good to know more about why this problem is happening in the first place - why is a forward-only cursor problematic here? Can you check with Actian on this?

[NGrey5]

Thank you for the response! I'll answer as much as I can:

Nothing is changing the cursor while it's being fetched, no other queries on the database are being run. This isolated completely.

The query I'm running is very simple SELECT * FROM table_name

When I run this simple query, it's giving me the error of [Zen][ODBC Client Interface][LNA][Zen][SQL Engine]Row value was out of range. with code HY107
This happens on any table every time the fetch size is less than the total row count of that table. It always fails on the second-to-last fetch call.

Before I ask the Actian forums, i want to note that I wrote the exact same simple script using python's pyodbc package, using cursor and same fetch size with no errors. I'm not sure if that's helpful or not.

The error of my original post was happening when I was using a more complex query.

Thank you again for taking the time to help with this!

[NGrey5]

I think i've figured out the incompatibility with the driver. I will preface by saying I'm not at all an expert on this. This is my first time diving into c++ code.

I believe it has to do with SQLSetPos() being called during batched cursor fetches.

I temporarily disabled the SQLSetPos() call in fetch_and_store(), leaving everything else unchanged. The exact same query then successfully streamed all rows with any fetchSize.

It looks like the relevant statement is:

        if (set_position && data->get_data_supports.block && data->fetch_size > 1)
        {
          // In case the result set contains columns that contain LONG data
          // types, use SQLSetPos to set the row we are transferring bound data
          // from, and use SQLGetData in the same loop.
          return_code =
          SQLSetPos
          (
            data->hstmt,
            (SQLSETPOSIROW) row_index + 1,
            SQL_POSITION,
            SQL_LOCK_NO_CHANGE
          );

          if (!SQL_SUCCEEDED(return_code))
          {
            return return_code;
          }
        }

The queries im running do not contain any LONG columns, so SQLSetPos() isn't actually needed. It appears it's entering this path simply because the driver reports block SQLGetData support.

I changed the condition to also require that the result set actually contains LONG data. That fixed the issue for the normal-column query.

I also tested this with an actual LONGVARCHAR column containing ~10 KB of data. In that case, SQLSetPos() was still executed, and the query completed successfully. So the change doesn't appear to break the existing LONG-data handling that requires SQLSetPos().

This also fixed the original error of this post when using a more complex query. I do not seem to have any errors for various queries with these changes made.

I opened PR #490 for you to take a look at the changes I made!

Please let me know what you think. Thanks again.

[NGrey5]

One final follow up. I'm now getting errors with some queries containing LONG data. I am out of ideas at this point, but it's something to do with SQLSetPos even though node-odbc is calling it correctly as far as I can tell. If i bypass the code handling LONG data completely, which is what calls SQLSetPos everything seems to work just fine.

[kadler]

So, regarding pyodbc working fine that is because pyodbc does not not do block fetch. It has block insert (which you have to opt-in to using fast_executemany instead of executemany), but no block fetch. Therefore it does not not need to worry about SQLGetData inside block fetch.

I agree with your change that we should not need to call SQLSetPos with block fetch if we're not using SQLGetData so that looks good, but I'm not sure how to resolve your issue with the driver when using long data that requires SQLGetData.

SQLGetData says

SQL_GD_BLOCK. If this option is returned by SQLGetInfo for the SQL_GETDATA_EXTENSIONS InfoType, the driver supports calls to SQLGetData when the rowset size is greater than 1 and the application can call SQLSetPos with the SQL_POSITION option to position the cursor on the correct row before calling SQLGetData.

The only way we should be getting into this code is if the driver claims to support SQLGetData within a block fetch. And the docs say that this requires the application to call SQLSetPos with SQL_POSITION to tell the driver which row we're calling SQLGetData for. This seems like a bug in the Actian driver. I think the only way around this for now is to not specify fetchSize so that rows are fetched one at a time, which does not require SQLSetPos be called.

[NGrey5]

Can confirm it's a driver issue. I was able to use a free evaluation version of their latest release and had no errors, even without my PR applied.

I will see if Actian has any ideas!

If you would like to use my PR, please let me know. If not, or if you'd like to implement this in a different way you can close it.

Thank you!