node-odbc: [BUG] MS Access mdb file fails to connect: "Error getting information about available transaction isolation options from the connection"

Describe your system

  • odbc Package Version: 2.4.0
  • ODBC Driver: MDBTools
  • Database Name: MS Access mdb database file
  • Database OS: PopOS 20.04 LTS (Ubuntu derivative)
  • Node.js Version: v14.17.4
  • Node.js OS: PopOS 20.04 LTS (Ubuntu derivative)

Describe the bug Attempting to connect to an MDB file fails with “Error getting information about available transaction isolation options from the connection”.

There was a fix to ignore that problem in #181, which was included with the 2.4 release, but something still seems to be amiss.

Expected behavior I would expect to be able connect without issues.

To Reproduce

const odbc = require('odbc');

async function connectToDatabase() {
  const connection = await odbc.connect('DSN=E2Test')
  console.log('w00t!')
}

connectToDatabase().catch(e => console.log(e));

Running the program fails with the following error:

$ node test-connect.js
[Error: [odbc] Error getting information about available transaction isolation options from the connection] {
  odbcErrors: []
}

Here is the trace file output:

[ODBC][761201][1628543228.273146][__handles.c][460]
		Exit:[SQL_SUCCESS]
			Environment = 0x5bfa510
[ODBC][761201][1628543228.273207][SQLSetEnvAttr.c][189]
		Entry:
			Environment = 0x5bfa510
			Attribute = SQL_ATTR_ODBC_VERSION
			Value = 0x3
			StrLen = -5
[ODBC][761201][1628543228.273222][SQLSetEnvAttr.c][381]
		Exit:[SQL_SUCCESS]
[ODBC][761201][1628543228.280610][SQLAllocHandle.c][377]
		Entry:
			Handle Type = 2
			Input Handle = 0x5bfa510
[ODBC][761201][1628543228.280644][SQLAllocHandle.c][493]
		Exit:[SQL_SUCCESS]
			Output Handle = 0x7fe508000d40
[ODBC][761201][1628543228.280672][SQLDriverConnect.c][748]
		Entry:
			Connection = 0x7fe508000d40
			Window Hdl = (nil)
			Str In = [DSN=E2Test][length = 10 (SQL_NTS)]
			Str Out = 0x7fe5258d65c0
			Str Out Max = 2048
			Str Out Ptr = (nil)
			Completion = 0
		UNICODE Using encoding ASCII 'ANSI_X3.4-1968' and UNICODE 'UCS-2LE'

[ODBC][761201][1628543228.286653][SQLDriverConnect.c][1727]
		Exit:[SQL_SUCCESS]
			Connection Out [[][length = 0 (SQL_NTS)]]
[ODBC][761201][1628543228.286685][SQLGetInfo.c][236]
		Entry:
			Connection = 0x7fe508000d40
			Info Type = SQL_MAX_COLUMN_NAME_LEN (30)
			Info Value = 0x5bfb3e0
			Buffer Length = 2
			StrLen = (nil)
[ODBC][761201][1628543228.286709][SQLGetInfo.c][236]
		Entry:
			Connection = 0x7fe508000d40
			Info Type = SQL_TXN_ISOLATION_OPTION (72)
			Info Value = 0x5bfb3e8
			Buffer Length = 4
			StrLen = (nil)
[ODBC][761201][1628543228.286722][SQLGetInfo.c][236]
		Entry:
			Connection = 0x7fe508000d40
			Info Type = SQL_GETDATA_EXTENSIONS (81)
			Info Value = 0x7fe5258dae24
			Buffer Length = 0
			StrLen = (nil)
[ODBC][761201][1628543228.286742][SQLGetDiagField.c][948]
		Entry:
			Connection = 0x7fe508000d40
			Rec Number = 0
			Diag Ident = 2
			Diag Info Ptr = 0x7fe5258da5c0
			Buffer Length = -6
			String Len Ptr = (nil)
[ODBC][761201][1628543228.286760][SQLGetDiagField.c][968]
		Exit:[SQL_SUCCESS]

Additional context I have verified I can connect to the DSN with isql:

$ isql E2Test
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

From the prompt, I can query successfully.

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Comments: 23 (9 by maintainers)

Most upvoted comments

2.4.1 is now available! Feel free to reopen the issue if it is still a problem!

Sorry guys, busy days. I am looking to get the PR reviewed and merged in ASAP, then get a new version spun out to fix the issue.

@frankdugan3 I resolved this last problem that I had there. I have Debian 10, and thus, I had a rather old version of mdbtools (0.7) when the current last version is 1.0. I had to do a manual compilation and instalation of mdbtools and now it works.

Thanks for all your work for the community

@AlejandroFNadal You can specify a git branch to use in your package.json:

"dependencies": {
    "odbc": "github:markdirish/node-odbc#issue214"
  }

Shoot. Well, I have downloaded the driver, found a dummy .mdb file, and have replicated the issue on my own machine. Will debug and try to get to the bottom of it.

That’s what I was afraid of. I rewrote the result set binding logic to use column-wise binding, and it sets the fetch size to 1… I could let it fall through again, but I think it would just fail when trying to bind by column:

return_code =
  SQLSetStmtAttr
  (
    data->hstmt,
    SQL_ATTR_ROW_BIND_TYPE,
    SQL_BIND_BY_COLUMN,
    IGNORED_PARAMETER
  );

  return_code =
  SQLSetStmtAttr
  (
    data->hstmt,
    SQL_ATTR_ROWS_FETCHED_PTR,
    (SQLPOINTER) &data->rows_fetched,
    IGNORED_PARAMETER
  );

Going to take a little bit, but I am going to write a code path that does the simplest, most straight-forward binding if these functions fail to set. That should give an off ramp for a driver that doesn’t implement these attributes.