locked
How to preserve cursor over commit with ODBC for Linux? RRS feed

  • Question

  • I've been struggling to keep cursors open over commit with the Microsoft ODBC Driver 13.0 for SQL Server on Linux. We've got a lot of legacy code depending on that cursors are preserved over transaction borders. My test program works fine on Windows, but the same program on Linux (using the same database) fails with HY010 Function sequence error.

    The setting that makes it work on Windows is SQL_COPT_SS_PRESERVE_CURSORS, but that constant is not present in the Linux header files.

    I've been trying a number of things with no luck:

    1. Added the missing #define SQL_COPT_SS_PRESERVE_CURSORS 1204 to my program. Since that didn't work I had to omit the SQLSetConnectAttr call with that attribute.

    2. Executing SET CURSOR_CLOSE_ON_COMMIT OFF (this doesn't have any effect on Windows either!)

    3. Setting SQL_ATTR_CURSOR_TYPE to all possible values

    An odd observation is that regardless of the above settings, SQLGetInfo on Linux always reports that the value of  SQL_CURSOR_COMMIT_BEHAVIOR is 2, meaning SQL_CB_PRESERVE. On Windows I get SQL_CB_PRESERVE with the SQL_COPT_SS_PRESERVE_CURSORS setting ON or SQL_CB_CLOSE when it's OFF.

    So, will SQL_COPT_SS_PRESERVE_CURSORS be added? And shouldn't SET CURSOR_CLOSE_ON_COMMIT OFF do the trick?

    Best regards, Per

    Thursday, October 27, 2016 11:19 AM

All replies

  • Perfry, you can refer to this link:

    https://blogs.technet.microsoft.com/dataplatforminsider/2016/10/25/odbc-driver-13-0-for-sql-server-linux-is-now-released/

    Also you can report an issue to Microsoft here:https://connect.microsoft.com/SQLServer/Feedback

    Friday, October 28, 2016 6:00 AM
  • Thanks a lot, I hadn't noticed that Microsoft has finalised the release with apt packages and all:-)

    There might be a difference in how the released driver works with cursors compared to the beta. But I shouldn't jump the gun; I need to get control over my environment first, so I'm making Docker images now...

    /Per

    Friday, October 28, 2016 2:51 PM
  • You may share the latest progress that may help others. Thanks.
    Monday, October 31, 2016 2:29 AM
  • Sure, these are my findings:

    When I build and run the application on Ubuntu 16.04 cursors do get preserved over commit, if I add the following definitions:

    #define SQL_COPT_SS_PRESERVE_CURSORS 1204
    #define SQL_PC_ON 1L
    #define SQL_PC_OFF 0L

    and set the connection attribute:

    SQLSetConnectAttr(hDbc, SQL_COPT_SS_PRESERVE_CURSORS, (SQLPOINTER)SQL_PC_ON, 0).

    If I instead omit this connection attribute, or if I set it OFF, cursors are closed at commit.

    So, why are these definitions missing in the Linux version of msodbcsql.h? Is this attribute not supported on Linux, even though it seems to be working?

    (I've updated my issue from March on this, but I don't have high hopes for a response: https://connect.microsoft.com/SQLServer/feedback/details/2517331/sql-copt-ss-preserve-cursors-still-missing-in-odbc-driver-for-linux )
    Wednesday, November 2, 2016 3:14 PM
  • I am not using Linux, but I have voted your feedback. Hope it'll be responded.
    Thursday, November 3, 2016 6:04 AM