node-odbc
node-odbc copied to clipboard
Published
20 hours ago •
markdirish
markdirish
[BUG] Cursor with fetchSize > 1 results in error on prepared statement
Describe your system
-
odbcPackage 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
- Create a simple test table: CREATE TABLE test (id int);
- Insert some rows: INSERT INTO test values (1),(2),(3),(4),(5);
- 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.
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[bot] avatar](https://avatars.githubusercontent.com/in/1724?v=4 "Published by a pub.dev verified publisher"){kind=small}
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.
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)
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)