search

oneclick / rubyinstaller2

MSYS2 based RubyInstaller for Windows
https://rubyinstaller.org
BSD 3-Clause "New" or "Revised" License
648 stars 247 forks source link

win32ole: WIN32OLERuntimeError raised from ADODB sql query #108

Open bg opened 6 years ago

bg commented 6 years ago

What problems are you experiencing?

While Ruby 2.2 and 2.3 win32ole can handle all valid queries to an MS Access database, under Ruby 2.4, some queries fail with:

-e:1:in `method_missing': (in OLE method `Open': ) (WIN32OLERuntimeError)
    OLE error code:80040E14 in Microsoft JET Database Engine
      Internal OLE Automation error
    HRESULT error code:0x80020009
      Exception occurred.
        from -e:1:in `<main>'

Steps to reproduce

I have distilled this test case from a more complex SQL query and a real database to a fairly minimal, reproducible case on a test database which I have attached in test.zip. Given a simple db with a single table AvailabilityBlocks with this schema, and containing only 13 records:

CREATE TABLE [AvailabilityBlocks]
 (
        [BlockID]                       Long Integer,
        [ResourceID]                    Integer NOT NULL,
        [LocationID]                    Long Integer
);

If I specify an SQL query with any "WHERE" clause, under Ruby 2.4, but not earlier versions from Rubyinstaller.org, it triggers the above error, e.g.

ruby -rwin32ole -rpp -e "conn=WIN32OLE.new('ADODB.Connection'); conn.open('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb'); sql=ARGV.shift; rs=WIN32OLE.new('ADODB.Recordset'); rs.Open(sql, conn); data=rs.GetRows.transpose; conn.close; pp data" "SELECT * FROM AvailabilityBlocks WHERE 1"

I expected to see this output instead:

[[107508, 26, 14],
 [107513, 26, 16],
 [107518, 26, 20],
 [107523, 26, 14],
 [107544, 26, 14],
 [107561, 21, 1],
 [107574, 21, 1],
 [107587, 21, 13],
 [107592, 21, 13],
 [107609, 36, 9],
 [107622, 28, 8],
 [107639, 28, 12],
 [107652, 28, 16]]

Notes:

What's the output from ridk version?

I tested on two different systems with different Ruby 2.4 releases, and experience the same bug on both systems and both Ruby releases:

My current development system

This is the ridk version output from the system on which all of the above output was produced, a 64-bit Windows 10 Pro VM:

---
ruby:
  path: C:/Ruby243
  version: 2.4.3
  platform: i386-mingw32
ruby_installer:
  package_version: 2.4.3-2
  git_commit: 036e549
msys2:
  path: C:\msys32
  title: MSYS2 32bit
  version: '20161025'
cc: i686-w64-mingw32-gcc (Rev1, Built by MSYS2 project) 7.3.0
sh: GNU bash, version 4.4.19(2)-release (i686-pc-msys)
os: Microsoft Windows [Version 10.0.16299.309]

My old 32-bit Windows 7 Pro VM

On this system, which has a slightly older version of your Ruby 2.4 kit installed on it, the same behaviours described above occur, both for the successful & failing cases. Similarly, Ruby 2.2 and 2.3 do not have the bug on this test system.

---
ruby:
  path: c:/Ruby241
  version: 2.4.1
  platform: i386-mingw32
ruby_installer:
  package_version: 2.4.1-1rc2
  git_commit: 1b324f5
msys2:
  path: c:\msys32
  title: MSYS2 32bit
  version: '20160205'
cc: i686-w64-mingw32-gcc (Rev2, Built by MSYS2 project) 6.3.0
sh: GNU bash, version 4.3.42(5)-release (i686-pc-msys)
bg commented 6 years ago

I tried also Ruby 2.5.0 and then upon discovering you just released 2.5.1, that version as well. Both fail in the same way.

Here's the latest ridk version output for the most recent version I've tested in which the issue still occurs:

---
ruby:
  path: c:/Ruby251
  version: 2.5.1
  platform: i386-mingw32
ruby_installer:
  package_version: 2.5.1-1
  git_commit: 71c9f4e
msys2:
  path: c:\msys64
  title: MSYS2 64bit
  version: '20161025'
cc: i686-w64-mingw32-gcc (Rev1, Built by MSYS2 project) 7.3.0
sh: GNU bash, version 4.4.19(2)-release (x86_64-pc-msys)
os: Microsoft Windows [Version 10.0.16299.309]

You'll note I've switched to msys2 x64 on the advice of upstream since the development system is 64-bit.

larskanis commented 6 years ago

Thanks @bg for reporting this issue!

RubyInstaller-2.4.1 updates both WIN32OLE and the compiler suite. It would be interesting, which of these two is guilty. Could you please try Ruby-2.3.4 compiled with gcc-7.1.0? You can download from the build artifacts here.

bg commented 6 years ago

@larskanis I can confirm the issue also exists in Ruby-2.3.4 compiled with gcc-7.1.0, downloaded from the link you provided, but not Ruby-2.3.3 (installed via Rubyinstaller 1).

Z:\>uru 2.3.4
---> now using ruby 2.3.4-p301 tagged as `2.3.4`

Z:\>ridk version
---
ruby:
  path: C:/Ruby234
  version: 2.3.4
  platform: i386-mingw32
ruby_installer:
  package_version: 2.3.4-1
  git_commit: c02f242
msys2:
  path: C:\msys64
  title: MSYS2 64bit
  version: '20161025'
cc: i686-w64-mingw32-gcc (Rev1, Built by MSYS2 project) 7.3.0
sh: GNU bash, version 4.4.19(2)-release (x86_64-pc-msys)

Z:\>ruby -rwin32ole -rpp -e "conn=WIN32OLE.new('ADODB.Connection'); conn.open('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb'); sql=ARGV.shift; rs=WIN32OLE.new('ADODB.Recordset'); rs.Open(sql, conn); data=rs.GetRows.transpose; conn.close; pp data" "SELECT * FROM AvailabilityBlocks WHERE 1"
-e:1:in `method_missing': (in OLE method `Open': ) (WIN32OLERuntimeError)
    OLE error code:80040E14 in Microsoft JET Database Engine
      Internal OLE Automation error
    HRESULT error code:0x80020009
      Exception occurred.
        from -e:1:in `<main>'

Z:\>uru 2.3.3
---> now using ruby 2.3.3-p222 tagged as `2.3.3`

Z:\>ruby -rwin32ole -rpp -e "conn=WIN32OLE.new('ADODB.Connection'); conn.open('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb'); sql=ARGV.shift; rs=WIN32OLE.new('ADODB.Recordset'); rs.Open(sql, conn); data=rs.GetRows.transpose; conn.close; pp data" "SELECT * FROM AvailabilityBlocks WHERE 1"
[[107508, 26, 14],
 [107513, 26, 16],
 [107518, 26, 20],
 [107523, 26, 14],
 [107544, 26, 14],
 [107561, 21, 1],
 [107574, 21, 1],
 [107587, 21, 13],
 [107592, 21, 13],
 [107609, 36, 9],
 [107622, 28, 8],
 [107639, 28, 12],
 [107652, 28, 16]]
dogatana commented 5 years ago

Is there any progress for this issue?

My app accesses Lotus Notes Database via win32ole, and runs well with ruby2.3.3. Recently I switched ruby from ruby 2.3.3p222(Rubyinstaller 1) to ruby 2.6.3p62(Rubyinstaller 2). After that, my app shows following error.

I noticed that win32ole.so has been changed from Rubyinstaller 2. This seems to affect compatibility between two 1 and 2.

Ruby without win32ole is not pragmatic tool on windows, for me at least. Any suggestions are very welcome.

C:/Ruby26/lib/ruby/gems/2.6.0/gems/notes2html-0.0.13/lib/notes2html/notescontroller.rb:67:in `method_missing': (in OLE method `Export': ) (WIN32OLERuntimeError)
    OLE error code:800411F9 in NotesDXLExporter
      DXL exporter operation failed
    HRESULT error code:0x80020009
      Exception occurred.
DAMisener commented 5 years ago

Still a work in progress but:

Updating the Access Database Engine prevents BG reported error

dogatana commented 5 years ago

Thank you for the comment.

Although I'm not sure the root cause of my case is same as the one of Access DB, here are some additional information.