Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[BUG] Cursor with fetchSize > 1 results in error on prepared statement #329

Open
ErikJansenIRefact opened this issue Apr 24, 2023 · 7 comments
Open

[BUG] Cursor with fetchSize > 1 results in error on prepared statement #329

ErikJansenIRefact opened this issue Apr 24, 2023 · 7 comments
Labels
confirmed

Comments

@ErikJansenIRefact
Copy link

ErikJansenIRefact commented Apr 24, 2023 •
edited
Loading

Describe your system

  • odbc Package Version: 2.4.7
  • ODBC Driver: Microsoft ODBC Driver Version 18
  • Database Name: SQLServer
  • Database Version: 2019
  • Database OS: Windows
  • Node.js Version: 16.15.1
  • Node.js OS: Mac OS Ventura 13.3

Describe the bug
The ODBC driver returns a "Invalid cursor position" error if a fetchSize > 1 is specified for a prepared statement in statement.execute.

Expected behavior
Regular cursor behaviour when specifying a fetchSize > 1

To Reproduce

  1. Create a simple test table: CREATE TABLE test (id int);
  2. Insert some rows: INSERT INTO test values (1),(2),(3),(4),(5);
  3. Execute the code as specified below. This will result in the described error.

Code

const odbc = require('odbc');

async function connectToDatabase() {
    const connectionConfig = {
        connectionString: 'DSN=MSSQL2019;uid=sa;pwd=iRefact2017;MARS_Connection=no;trustServerCertificate=yes',
        connectionTimeout: 10,
        loginTimeout: 10
    };

    const connection = await odbc.connect(connectionConfig);
    const statement = await connection.createStatement();
    await statement.prepare('select * from IR_TSTGIN.dbo.test;');
    let cursor = await statement.execute({
        fetchSize: 2
    });
    let result = await cursor.fetch();
    await cursor.close();
}

connectToDatabase()
    .catch(error => {
        console.dir(error, {
            depth: null
        });
    });

Additional context

Specifying a fetchSize without a prepared statement does return as expected. So it seems that something goes wrong with a cursor on a prepared statement.
@stale
Copy link

stale bot commented May 24, 2023

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

@stale stale bot added the stale This issue hasn't seen any interaction in 30 days. label May 24, 2023
@ErikJansenIRefact
Copy link
Author

Please do not close.

@stale stale bot removed the stale This issue hasn't seen any interaction in 30 days. label May 25, 2023
@stale
Copy link

stale bot commented Jun 24, 2023

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

@stale stale bot added the stale This issue hasn't seen any interaction in 30 days. label Jun 24, 2023
@ErikJansenIRefact
Copy link
Author

Do not close.

@stale stale bot closed this as completed Jul 9, 2023
@ErikJansenIRefact
Copy link
Author

Please re-open.

@markdirish markdirish reopened this Jul 11, 2023
@stale stale bot removed the stale This issue hasn't seen any interaction in 30 days. label Jul 11, 2023
@FredoMartini
Copy link

Hello,

I have the same problem using odbc from Windows to Informix.

If it can help, here the ODBC trace generated :


dev2            b930-ed6c	ENTER SQLAllocHandle 
		SQLSMALLINT                  1 <SQL_HANDLE_ENV>
		SQLHANDLE           0x0000000000000000
		SQLHANDLE *         0x00007FF8EDAF0FC0

dev2            b930-ed6c	EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  1 <SQL_HANDLE_ENV>
		SQLHANDLE           0x0000000000000000
		SQLHANDLE *         0x00007FF8EDAF0FC0 ( 0x00000107BD05AFC0)

dev2            b930-ed6c	ENTER SQLSetEnvAttr 
		SQLHENV             0x00000107BD05AFC0
		SQLINTEGER                 200 <SQL_ATTR_ODBC_VERSION>
		SQLPOINTER                 3 <SQL_OV_ODBC3>
		SQLINTEGER                  -5 

dev2            b930-ed6c	EXIT  SQLSetEnvAttr  with return code 0 (SQL_SUCCESS)
		SQLHENV             0x00000107BD05AFC0
		SQLINTEGER                 200 <SQL_ATTR_ODBC_VERSION>
		SQLPOINTER                 3 <SQL_OV_ODBC3>
		SQLINTEGER                  -5 

dev2            b930-faec	ENTER SQLAllocHandle 
		SQLSMALLINT                  2 <SQL_HANDLE_DBC>
		SQLHANDLE           0x00000107BD05AFC0
		SQLHANDLE *         0x00000107BF173508

dev2            b930-faec	EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  2 <SQL_HANDLE_DBC>
		SQLHANDLE           0x00000107BD05AFC0
		SQLHANDLE *         0x00000107BF173508 ( 0x00000107BD05B0A0)

dev2            b930-faec	ENTER SQLSetConnectAttrW 
		SQLHDBC             0x00000107BD05B0A0
		SQLINTEGER                 113 <SQL_ATTR_CONNECTION_TIMEOUT>
		SQLPOINTER               500
		SQLINTEGER                  -5 

dev2            b930-faec	EXIT  SQLSetConnectAttrW  with return code 0 (SQL_SUCCESS)
		SQLHDBC             0x00000107BD05B0A0
		SQLINTEGER                 113 <SQL_ATTR_CONNECTION_TIMEOUT>
		SQLPOINTER               500
		SQLINTEGER                  -5 

dev2            b930-faec	ENTER SQLSetConnectAttrW 
		SQLHDBC             0x00000107BD05B0A0
		SQLINTEGER                 103 <SQL_ATTR_LOGIN_TIMEOUT>
		SQLPOINTER                10
		SQLINTEGER                  -5 

dev2            b930-faec	EXIT  SQLSetConnectAttrW  with return code 0 (SQL_SUCCESS)
		SQLHDBC             0x00000107BD05B0A0
		SQLINTEGER                 103 <SQL_ATTR_LOGIN_TIMEOUT>
		SQLPOINTER                10
		SQLINTEGER                  -5 

dev2            b930-faec	ENTER SQLDriverConnectW 
		HDBC                0x00000107BD05B0A0
		HWND                0x0000000000000000
		WCHAR *             0x00007FF8EDB76140 [      -3] "******\ 0"
		SWORD                       -3 
		WCHAR *             0x00007FF8EDB76140 
		SWORD                       -3 
		SWORD *             0x0000000000000000
		UWORD                        0 <SQL_DRIVER_NOPROMPT>

dev2            b930-faec	EXIT  SQLDriverConnectW  with return code 1 (SQL_SUCCESS_WITH_INFO)
		HDBC                0x00000107BD05B0A0
		HWND                0x0000000000000000
		WCHAR *             0x00007FF8EDB76140 [      -3] "******\ 0"
		SWORD                       -3 
		WCHAR *             0x00007FF8EDB76140 <Invalid buffer length!> [-3]
		SWORD                       -3 
		SWORD *             0x0000000000000000
		UWORD                        0 <SQL_DRIVER_NOPROMPT>

		DIAG [IM006] [Microsoft][Gestionnaire de pilotes ODBC]  chec SQLSetConnectAttr du pilote (0) 

dev2            b930-faec	ENTER SQLGetInfoW 
		HDBC                0x00000107BD05B0A0
		UWORD                       30 <SQL_MAX_COLUMN_NAME_LEN>
		PTR                 0x00000107BF1734F0
		SWORD                        2 
		SWORD *             0x0000000000000000

dev2            b930-faec	EXIT  SQLGetInfoW  with return code 0 (SQL_SUCCESS)
		HDBC                0x00000107BD05B0A0
		UWORD                       30 <SQL_MAX_COLUMN_NAME_LEN>
		PTR                 0x00000107BF1734F0 (128)
		SWORD                        2 
		SWORD *             0x0000000000000000

dev2            b930-faec	ENTER SQLGetInfoW 
		HDBC                0x00000107BD05B0A0
		UWORD                       72 <SQL_TXN_ISOLATION_OPTION>
		PTR                 0x00000107BF1734F8
		SWORD                        4 
		SWORD *             0x0000000000000000

dev2            b930-faec	EXIT  SQLGetInfoW  with return code 0 (SQL_SUCCESS)
		HDBC                0x00000107BD05B0A0
		UWORD                       72 <SQL_TXN_ISOLATION_OPTION>
		PTR                 0x00000107BF1734F8 ( 0x006300650000000B)
		SWORD                        4 
		SWORD *             0x0000000000000000

dev2            b930-faec	ENTER SQLGetInfoW 
		HDBC                0x00000107BD05B0A0
		UWORD                       81 <SQL_GETDATA_EXTENSIONS>
		PTR                 0x00000001D8DFFBF0
		SWORD                        0 
		SWORD *             0x0000000000000000

dev2            b930-faec	EXIT  SQLGetInfoW  with return code 0 (SQL_SUCCESS)
		HDBC                0x00000107BD05B0A0
		UWORD                       81 <SQL_GETDATA_EXTENSIONS>
		PTR                 0x00000001D8DFFBF0 ( 0x000000000000000F)
		SWORD                        0 
		SWORD *             0x0000000000000000

dev2            b930-11564	ENTER SQLAllocHandle 
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           0x00000107BD05B0A0
		SQLHANDLE *         0x00000107801196B0

dev2            b930-11564	EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           0x00000107BD05B0A0
		SQLHANDLE *         0x00000107801196B0 ( 0x00000107BD05CCB0)

dev2            b930-11564	ENTER SQLExecDirectW 
		HSTMT               0x00000107BD05CCB0
		WCHAR *             0x0000010780051E90 [      -3] "SELECT num_segm\ aFROM segm\ 0"
		SDWORD                    -3

dev2            b930-11564	EXIT  SQLExecDirectW  with return code 0 (SQL_SUCCESS)
		HSTMT               0x00000107BD05CCB0
		WCHAR *             0x0000010780051E90 [      -3] "SELECT num_segm\ aFROM segm\ 0"
		SDWORD                    -3

dev2            b930-11564	ENTER SQLSetStmtAttrW 
		SQLHSTMT            0x00000107BD05CCB0
		SQLINTEGER                  27 <SQL_ATTR_ROW_ARRAY_SIZE>
		SQLPOINTER               256
		SQLINTEGER                   0 

dev2            b930-11564	EXIT  SQLSetStmtAttrW  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            0x00000107BD05CCB0
		SQLINTEGER                  27 <SQL_ATTR_ROW_ARRAY_SIZE>
		SQLPOINTER               256
		SQLINTEGER                   0 

dev2            b930-11564	ENTER SQLSetStmtAttrW 
		SQLHSTMT            0x00000107BD05CCB0
		SQLINTEGER                  25 <SQL_ATTR_ROW_STATUS_PTR>
		SQLPOINTER          0x00000107809484C0
		SQLINTEGER                   0 

dev2            b930-11564	EXIT  SQLSetStmtAttrW  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            0x00000107BD05CCB0
		SQLINTEGER                  25 <SQL_ATTR_ROW_STATUS_PTR>
		SQLPOINTER          0x00000107809484C0
		SQLINTEGER                   0 

dev2            b930-11564	ENTER SQLRowCount 
		HSTMT               0x00000107BD05CCB0
		SQLLEN *            0x0000010780119730

dev2            b930-11564	EXIT  SQLRowCount  with return code 0 (SQL_SUCCESS)
		HSTMT               0x00000107BD05CCB0
		SQLLEN *            0x0000010780119730 (-1)

dev2            b930-11564	ENTER SQLNumResultCols 
		HSTMT               0x00000107BD05CCB0
		SWORD *             0x0000010780119708

dev2            b930-11564	EXIT  SQLNumResultCols  with return code 0 (SQL_SUCCESS)
		HSTMT               0x00000107BD05CCB0
		SWORD *             0x0000010780119708 (1)

dev2            b930-11564	ENTER SQLSetStmtAttrW 
		SQLHSTMT            0x00000107BD05CCB0
		SQLINTEGER                   5 <SQL_ATTR_ROW_BIND_TYPE>
		SQLPOINTER                 0 <SQL_BIND_BY_COLUMN>
		SQLINTEGER                   0 

dev2            b930-11564	EXIT  SQLSetStmtAttrW  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            0x00000107BD05CCB0
		SQLINTEGER                   5 <SQL_ATTR_ROW_BIND_TYPE>
		SQLPOINTER                 0 <SQL_BIND_BY_COLUMN>
		SQLINTEGER                   0 

dev2            b930-11564	ENTER SQLSetStmtAttrW 
		SQLHSTMT            0x00000107BD05CCB0
		SQLINTEGER                  26 <SQL_ATTR_ROWS_FETCHED_PTR>
		SQLPOINTER          0x0000010780119750
		SQLINTEGER                   0 

dev2            b930-11564	EXIT  SQLSetStmtAttrW  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            0x00000107BD05CCB0
		SQLINTEGER                  26 <SQL_ATTR_ROWS_FETCHED_PTR>
		SQLPOINTER          0x0000010780119750
		SQLINTEGER                   0 

dev2            b930-11564	ENTER SQLDescribeColW 
		HSTMT               0x00000107BD05CCB0
		UWORD                        1 
		WCHAR *             0x00000107808D76C0 
		SWORD                      129 
		SWORD *             0x0000010780062522
		SWORD *             0x0000010780062524
		SQLULEN *           0x0000010780062528
		SWORD *             0x0000010780062530
		SWORD *             0x0000010780062540

dev2            b930-11564	EXIT  SQLDescribeColW  with return code 0 (SQL_SUCCESS)
		HSTMT               0x00000107BD05CCB0
		UWORD                        1 
		WCHAR *             0x00000107808D76C0 [       8] "num_segm"
		SWORD                      129 
		SWORD *             0x0000010780062522 (8)
		SWORD *             0x0000010780062524 (4)
		SQLULEN *           0x0000010780062528 (10)
		SWORD *             0x0000010780062530 (0)
		SWORD *             0x0000010780062540 (0)

dev2            b930-11564	ENTER SQLBindCol 
		HSTMT               0x00000107BD05CCB0
		UWORD                        1 
		SWORD                      -16 <SQL_C_SLONG>
		PTR                0x00000107808E5970
		SQLLEN                     4
		SQLLEN *            0x00000107BCE40160

dev2            b930-11564	EXIT  SQLBindCol  with return code 0 (SQL_SUCCESS)
		HSTMT               0x00000107BD05CCB0
		UWORD                        1 
		SWORD                      -16 <SQL_C_SLONG>
		PTR                0x00000107808E5970
		SQLLEN                     4
		SQLLEN *            0x00000107BCE40160 (0)

dev2            b930-f7e4	ENTER SQLFetch 
		HSTMT               0x00000107BD05CCB0

dev2            b930-f7e4	EXIT  SQLFetch  with return code 0 (SQL_SUCCESS)
		HSTMT               0x00000107BD05CCB0

dev2            b930-f7e4	ENTER SQLSetPos 
		HSTMT               0x00000107BD05CCB0
		SQLSETPOSIROW              1
		UWORD                        0 <SQL_POSITION>
		BOOL                         0 <SQL_LOCK_NO_CHANGE>

dev2            b930-f7e4	EXIT  SQLSetPos  with return code -1 (SQL_ERROR)
		HSTMT               0x00000107BD05CCB0
		SQLSETPOSIROW              1
		UWORD                        0 <SQL_POSITION>
		BOOL                         0 <SQL_LOCK_NO_CHANGE>

		DIAG [HY109] [Informix][Informix ODBC Driver]Invalid cursor position. (-11089) 

dev2            b930-f7e4	ENTER SQLGetDiagFieldW 
		SQLSMALLINT                  3 
		SQLHANDLE           0x00000107BD05CCB0
		SQLSMALLINT                  0 
		SQLSMALLINT                  2 
		SQLPOINTER         0x00000001DA5FF634
		SQLSMALLINT                 -6 
		SQLSMALLINT *       0x0000000000000000

dev2            b930-f7e4	EXIT  SQLGetDiagFieldW  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  3 
		SQLHANDLE           0x00000107BD05CCB0
		SQLSMALLINT                  0 
		SQLSMALLINT                  2 
		SQLPOINTER         0x00000001DA5FF634
		SQLSMALLINT                 -6 
		SQLSMALLINT *       0x0000000000000000

dev2            b930-f7e4	ENTER SQLGetDiagRecW 
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           0x00000107BD05CCB0
		SQLSMALLINT                  1 
		SQLWCHAR *          0x00000001DA5FF638
		SQLINTEGER *        0x00000001DA5FF644
		SQLWCHAR *          0x00000107BCE40970 
		SQLSMALLINT               1024 
		SQLSMALLINT *       0x00000001DA5FF630

dev2            b930-f7e4	EXIT  SQLGetDiagRecW  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           0x00000107BD05CCB0
		SQLSMALLINT                  1 
		SQLWCHAR *          0x00000001DA5FF638 [       5] "HY109"
		SQLINTEGER *        0x00000001DA5FF644 (-11089)
		SQLWCHAR *          0x00000107BCE40970 [      56] "[Informix][Informix ODBC Driver]Invalid cursor position."
		SQLSMALLINT               1024 
		SQLSMALLINT *       0x00000001DA5FF630 (56)

dev2            b930-a470	ENTER SQLCloseCursor 
		SQLHSTMT            0x00000107BD05CCB0

dev2            b930-a470	EXIT  SQLCloseCursor  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            0x00000107BD05CCB0

dev2            b930-a470	ENTER SQLFreeHandle 
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           0x00000107BD05CCB0

dev2            b930-a470	EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           0x00000107BD05CCB0

dev2            b930-ed6c	ENTER SQLFreeHandle 
		SQLSMALLINT                  2 <SQL_HANDLE_DBC>
		SQLHANDLE           0x00000107BD05B0A0

dev2            b930-ed6c	EXIT  SQLFreeHandle  with return code -1 (SQL_ERROR)
		SQLSMALLINT                  2 <SQL_HANDLE_DBC>
		SQLHANDLE           0x00000107BD05B0A0

		DIAG [HY010] [Microsoft][Gestionnaire de pilotes ODBC] Erreur de s quence de la fonction (0)

@FredoMartini
Copy link

Hi,

I found that the error come from this code on odbc_connection.cpp :

        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;
          }
        }

I don't understand why, but SQLSetPos() fail.

If I comment this block the fetchSize is working, except when one of the columns is "long_data" and must be retrieved via SQLGetData()

So I suppose we can use a partial fix in this case, by skipping SQLSetPos() when there are no long_data's columns.
Something like this :

        if (data->has_long_data && set_position && data->get_data_supports.block && data->fetch_size > 1)

@FredoMartini FredoMartini mentioned this issue Mar 28, 2024
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
confirmed
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@markdirish @ErikJansenIRefact @FredoMartini