Jet with unixODBC and MDB Tools on macOS/Linux: ODBC connection string "must be of type JetConnection"

[MultiCoinCharts]

Using macOS with Access 2002 + ODBC.

<PackageReference Include="System.Data.Odbc" Version="5.0.0" />
<PackageReference Include="EntityFrameworkCore.Jet" Version="3.1.0-alpha.3" />
<PackageReference Include="EntityFrameworkCore.Jet.Odbc" Version="3.1.0-alpha.3" />
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.10" />

ODBC + Drives are installed/working via:

+ brew install unixODBC
+ brew install mdbtools --with-unixODBC

I'm using isql -k "connection-string-goes-here" to make sure Access JET4 database can read from tables or queries.

What am I running into
Inside the OnConfiguring(optionsBuilder) method of my DbContext .UseJetOdbc(connection_string) always produces errors.

optionsBuilder.UseJetOdbc(@"Driver={Microsoft Access Driver (*.mdb)};DBQ=/Users/p_m_/Desktop/access-db/open-data/Electronics-Sales.mdb;")

The error this produces

Could not find DSN nor DBQ in connect string

+ When using `isql` DBQ must be capitalized in order to recognize it as the DBQ
+ Using the string exactly above - breaks in dotnet / but works using isql
+ The Driver is just named as-is-above in the unixODBC configuration files which is why it matches Windows XP

The connection parameter must be of type JetConnection.
To get around this, we tried creating a demo project using only System.Data.Odbc

<PackageReference Include="System.Data.Odbc" Version="5.0.0" />

In that demo, we use OdbcConnectionStringBuilder with OdbcConnection to access our Access Office XP database. This works, even on macOS with .dylib patched in ODBC Drivers.

What can I do to get UseJet()/UseJetOdbc() to use my OdbcConnection?
Here the missing link to Accdb + EF Core + Unix + OData + ASP .NET Core looks to be a connection sanitization bias.

1. Does UseJetOdbc() lowercase attributes like DBQ which are case-sensitive on Unix?
2. EF Core Jet asks for a connection string, filename, or OdbcConnection.
3. Is there a special version of OdbcConnection that doesn't derive from System.Data.Odbc?

I have EntityFrameworkCore.Jet.Odbc in my project - this case is not urgent, please take your time.

[lauxjpn]

Very interesting. I was researching about mdbtools many year ago but was not aware that full ODBC support could be achieved nowadays on Unix systems.

All DBQ options should be generated UPPERCASE by EFCore.Jet. I made sure that this is the case with #72 yesterday, before publishing.

You can try to call UseJetOdbc("/Users/p_m_/Desktop/access-db/open-data/Electronics-Sales.mdb") with just a file name/path to see, if it makes any difference.

I'll take a look at this. If we can get EFCore.Jet successfully to run on Unix-like systems, than this would be fantastic!
The support would still be limited, due to some DAO and ADO/ADOX dependencies we currently have (though we might be able to optionally get rid of them on Unix-like systems) and the fact, that mdbtools can only read from (but not write to) databases. However, there seem to be commercial products out there, that even allow that (see unixODBC: Drivers).

[lauxjpn]

@P-Medicado I don't seem to be able to get unixODBC and mdbtools to work together, even just on the terminal:

meyer@Cider / % brew install unixodbc
Updating Homebrew...
==> Downloading https://homebrew.bintray.com/bottles/unixodbc-2.3.9.catalina.bottle.tar.gz
Already downloaded: /Users/meyer/Library/Caches/Homebrew/downloads/3989e328fb7519d96205845a4b5b52b46e1ccf45f0517308662a802369825fe0--unixodbc-2.3.9.catalina.bottle.tar.gz
==> Pouring unixodbc-2.3.9.catalina.bottle.tar.gz
🍺  /usr/local/Cellar/unixodbc/2.3.9: 46 files, 1.9MB

meyer@Cider / % brew install mdbtools
Updating Homebrew...
==> Downloading https://homebrew.bintray.com/bottles/mdbtools-0.7.1_3.catalina.bottle.tar.gz
Already downloaded: /Users/meyer/Library/Caches/Homebrew/downloads/eeea0ae6a104d7f99ce5f77372525517c1e77baf024aa1fc52b2f219a3d366b5--mdbtools-0.7.1_3.catalina.bottle.tar.gz
==> Pouring mdbtools-0.7.1_3.catalina.bottle.tar.gz
🍺  /usr/local/Cellar/mdbtools/0.7.1_3: 29 files, 556.8KB

meyer@Cider / % odbcinst -j
unixODBC 2.3.9
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/meyer/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

meyer@Cider / % mdb-sql -p ~/RiderProjects/JetIssues/Issue76/Issue76/Northwind.accdb
1 => select CompanyName from Customers where CustomerID = 'ALFKI'
2 => go

CompanyName
Alfreds Futterkiste
1 Row retrieved

meyer@Cider / % isql -k "DBQ=/Users/meyer/RiderProjects/JetIssues/Issue76/Issue76/Northwind.accdb"
[ISQL]ERROR: Could not SQLDriverConnect

The /usr/local/etc/odbcinst.ini and /usr/local/etc/odbc.ini files are empty and the /Users/meyer/.odbc.ini file does not exist.

If I run odbcinst -q -d, I get the following output:

meyer@Cider / % odbcinst -q -d
odbcinst: SQLGetPrivateProfileString failed with .

[MultiCoinCharts]

Thank you so much in looking into this! Trying just the pathname did produce different results, but first, using brew install for these packages seems to make it difficult for unixodbc + mdbtools to see each other.

mdbtools + unixodbc

Did brew install mdbtools include ODBC drivers?
Run brew info mdbtools to find the exact location of your installation.
We need to find out of your installation has lib/libmdbodbc.dylib (on macOS) or lib/libmdbodbc.so (on Linux)
Here is mine:

> brew info mdbtools
...
/usr/local/Cellar/mdbtools/0.7.1_3

Next, you can cd the_path_printed_above
Once there, go into lib then ls to see if you have this file libmdbodbc.dylib.
If you do, great; the next step will manually link unixodbc with this driver file.

Adding Access drivers to unixodbc
Copy the path to your libmdbodbc.dylib file in the last step (if you're in the folder, use pwd to get most the path).
Now, use nano or vscode to edit /usr/local/etc/odbcinst.ini
This is where we add our drivers, for .mdb or .accdb this works for me:

[ODBC]
Trace = yes
TraceFile = ./my-log

[Microsoft Access Driver (*.mdb)]
Driver=/usr/local/Cellar/mdbtools/0.7.1_3/lib/libmdbodbc.dylib
FileUsage=2

To test it, I ran isql -v -k -v giving more output in case of errors.

> isql -v -k "Driver={Microsoft Access Driver (*.mdb)};DBQ=/Users/p_m_/Desktop/access-db/open-data/Electronics-Sales.mdb"

If you used the same Trace configuration as I did above, this also will have created a file called my-log in the same folder as you're in. After opening the log, I can see the text-encoding used, as well as the exact connection string which was passed. In the directory of my C# project, there is a my-log file also. That one shows the encoding + connection string used by EntityFrameworkCore.Jet (with a separate dir for the demo project with System.Data.Odbe; only.

Installing mdbtools with ODBC drivers
Use this if homebrew did not install mdbtools with a copy of libmdodbc.dylib.

The following instructions are adapted from this mdbtools github issue.

1. brew uninstall mdbtools - we need to reinstall with the --with-unixodbc option.
2. export HOMEBREW_EDITOR="/usr/bin/nano" - this is absolutely optional.
3. brew edit mdbtools - the homebrew install instructions doesn't have support the --with-unixodbc flag yet.
   after running brew edit it will open the install instructions of mdbtools - possibly in vi

You'll see something like this in your file:

  bottle do
    cellar :any
    sha256 "0b35d9f656db9764cf1d52232e23e3f229e6884b657ae7f30ef4a16b6a6d4b27" =...
    sha256 "3c14e11a6603273676d09141b8da9fed42bacd992dbb7d82979c1279ed488ba4" =...
    sha256 "7ba58781f1d60f4b5ea1e9af6f75d52be36a7cfec10fef414e1e99d447ad10e5" =...
    sha256 "57bc1d0d1df78a20881b0d0340a302ec3a7d359a80eaffe78d809bf4dc150521" =...
    sha256 "1e1f75dc87ac2f423ecbf993a118220fe8d309ad179ec9986d099b98f959f216" =...
  end

Delete everything after that end. Create a new line. Paste the following:

  option "with-unixodbc", "Enable ODBC connections"

  depends_on "autoconf" => :build
  depends_on "automake" => :build
  depends_on "libtool" => :build
  depends_on "pkg-config" => :build
  depends_on "glib" => :build
  depends_on "readline" => :build

  depends_on "bison"
  depends_on "flex"

  depends_on "unixodbc" => :optional

  def install
    ENV.deparallelize

    args = %W[
      --prefix=#{prefix}
      --disable-man
    ]

    args << "--with-unixodbc=#{Formula["unixodbc"].opt_prefix}" if build.with? "unixodbc"

    system "autoreconf", "-i", "-f"
    system "./configure", *args
    system "make"
    ENV.deparallelize # Or fails to install on multi-core machines
    system "make", "install"
  end
end

4. brew install mdbtools --with-unixodbc
5. brew link --overwrite mdbtools

This script snippet was taken from a solution provided at the link to the mdbtools issue.
Doing this doesn't seem to configure mdbtools with unixodbc in odbcinst.ini but it does create libmdodbc.dylib!
Now you can manually configure unixodbc to use the mdbtools MS Access driver (instructions were shown earlier here).

EntityFrameworkCore.Jet / EFCore.Jet Odbc

Let's visit the override void OnConfiguring(optionsBuilder) method in DbContext. Here's how mine looks.

      var path = "/Users/p_m_/Desktop/access-db/Electronics-Sales.mdb"
      var builder = new OdbcConnectionStringBuilder
      {
          Driver = "Microsoft Access Driver (*.mdb)"
      };
      builder.Add("DBQ", path);
      // var connection = new OdbcConnection(builder.ConnectionString);
      // var connection = path;
      // var connection = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" + path;
      optionsBuilder.UseJetOdbc(connection);

Each connection was tried, here's what happens with each.

1. If you use type OdbcConnection (even though vscode says UseJetOdbc() accepts this type - it runtime crashes with a Type kind ArgumentException.
2. If you use the raw string path, it never connects to the database (just runtime crashes when the context is ever used) since my-log is either not created or not modified AKA EF Jet doesn't know about the mdbtools driver in unixodbc - which it shouldn't need to anyway since I can specify which driver to ask for.
3. If you use the last one you get the closest to working in EF Jet! Even though the first option works against System.Data.Odbc, with Jet it crashes before trying to connect using it. If you use the string specifying Driver + DBQ EF Jet will attempt to connect with it! Here's what happens:

• Uses your string as filenameOrConnectionString
• Determines is connection string
• Correctly initiates unixodbc
• my-log gets created
• unixodbc return "Could not find DSN nor DBQ in connect string"

unixodbc it's right there; how are you not seeing it!
What does our log say? Here's ours:

[ODBC][2469][1606664248.680274][SQLDriverConnectW.c][290]
		Entry:
			Connection = 0x7f9fa40a4e00
			Window Hdl = 0x0
			Str In = [Driver={Microsoft Access Driver (*.mdb)};dbq=/Users/p_m_/Desktop/access-db/Electronics-Sales.mdb;extendedansisql=1][length = 116 (SQL_NTS)]
			Str Out = 0x0
			Str Out Max = 0
			Str Out Ptr = 0x700010242a00
			Completion = 0

This looks like good news, somewhere, DBQ is being changed to dbq. Also, a ;extendedansisql=1 was added in all lowercase. As a result, "DBQ" isn't found in the string. I noticed if I used a relative path instead of the full path, the log still showed the fullpath - in the string here - so someone is also rebuilding the path to be an absolute one.

This might not even be a EntityFrameworkCore.Jet problem now, let me know when you can.

[lauxjpn]

@P-Medicado Awesome, thanks! I needed to do both steps, mdbtools + unixodbc and Installing mdbtools with ODBC drivers to get it working.

For anybody who is also trying this in the future, I also manually installed the ✘-marked dependencies in the following output:

meyer@Cider / % brew info mdbtools
mdbtools: stable 0.7.1 (bottled)
Tools to facilitate the use of Microsoft Access databases
https://github.com/brianb/mdbtools/
/usr/local/Cellar/mdbtools/0.7.1_3 (29 files, 556.5KB) *
  Poured from bottle on 2020-11-29 at 16:37:59
From: https://github.com/Homebrew/homebrew-core/blob/HEAD/Formula/mdbtools.rb
License: GPL-2.0
==> Dependencies
Build: autoconf ✘, automake ✘, libtool ✔, pkg-config ✘, glib ✔, readline ✔
Required: bison ✔, flex ✔
Optional: unixodbc ✔
==> Options
--with-unixodbc
        Enable ODBC connections
==> Analytics
install: 421 (30 days), 1,143 (90 days), 4,964 (365 days)
install-on-request: 303 (30 days), 833 (90 days), 3,022 (365 days)
build-error: 0 (30 days)

With the Jet/ODBC part working now, I will take a look at the actual issue in the evening!

[lauxjpn]

@P-Medicado Okay, this issue is actually in OdbcConnectionStringBuilder, that returns the DBQ option in lower case letters and is internally used.

Looks like MDB Tools only case sensitively checks the DBQ and DSN options (see connectparams.c). While the case sensitivity should be fixed upstream, I'll implement a fix in EFCore.Jet for now.

[lauxjpn]

@P-Medicado So the #79 PR fixes the issue. However, I did some test with MDB Tools and they seems to be extremely limited. So I don't think they can really be used in practical applications. So its questionable whether the will actually be needed in the end. Maybe you can report back to us on what if anything at all you are able to accomplish with the MDB Tools and the #76 PR (just clone it, build it locally, and reference the locally build assemblies in your project instead of the NuGet package).

I haven't checked the Easysoft ODBC-Access Driver yet, but since it is a commercial product, I assume that it works much better.

[MultiCoinCharts]

Thank you for looking into this! We started an experimental project comparing Access XP (2002) to our modern SQLite or MySQL typical db setup. In .Net Core we use Microsoft's OData to let users run custom where, sort, or even sub-select clauses against EF Core at run-time which translates them to real SQL queries compatible with SQLite or MySQL.

In this case, all this runs on Unix with dotnet, after compiling your PR it absolutely worked. This repo is great. However, even some commercial ODBC Access drivers do not support simple SQL queries generated in our small test cases.

When using UseJetOdbc() one of the overloads asks for a type: OdbcConnection. Using a connection built with new OdbcConnection() OR OdbcFactory.Instance.CreateConnection() will
both produce the error: must be of type JetConnection.

This is because the UseJetOdbc() function will simply pass your OdbcConnection to UseJet(), then UseJet will check to see of it's castible to JetConnection. However, this could never not fail. It doesn't seem to be possible to pass a true OdbcConnection to the Jet provider even though it would create one itself internally.

If this were possible, Jet could separate some of the concerns of niche connection issues. Instead, using client-code snippets to setup odd connections or separate libraries.

What about the Access experiment? It's still on-going, a fun experiment, hopefully soon to be documented + open sourced. But for now Access + Open Data API will have to be built on Windows.

[lauxjpn]

Thank you for looking into this! We started an experimental project comparing Access XP (2002) to our modern SQLite or MySQL typical db setup. In .Net Core we use Microsoft's OData to let users run custom where, sort, or even sub-select clauses against EF Core at run-time which translates them to real SQL queries compatible with SQLite or MySQL.

In this case, all this runs on Unix with dotnet, after compiling your PR it absolutely worked. This repo is great. However, even some commercial ODBC Access drivers do not support simple SQL queries generated in our small test cases.

@P-Medicado Thanks for letting us know. If this does work for you, then we will merge #79 since it should not have any negative impact on other users.

However, the actual bug needs to be fixed in MDB Tools at some point. Do you want to open an issue on their repo, or do you want me to do it?

When using UseJetOdbc() one of the overloads asks for a type: OdbcConnection. Using a connection built with new OdbcConnection() OR OdbcFactory.Instance.CreateConnection() will
both produce the error: must be of type JetConnection.

This is because the UseJetOdbc() function will simply pass your OdbcConnection to UseJet(), then UseJet will check to see of it's castible to JetConnection. However, this could never not fail. It doesn't seem to be possible to pass a true OdbcConnection to the Jet provider even though it would create one itself internally.

Thanks for reporting! That is indeed a bug that we will fix shortly.

[MultiCoinCharts]

hi! I would a lot appreciate if you could ask them to handle lowercase DBQ I forked the repo but just to change the readme to focus on this project. The mdbtools we installed here was not not the renent one. I writed how to install the mdbtools in developing right now

In isql i can get mdbtools to work v good by typing:

select * from mypartstable
or
SELECT sku, name FROM mypartstable

But it seem to break on the generator sql from EF Core Jet

SELECT `m`.`sku`, `m`.`name` FROM `mypartstable` AS `m`

In isql using ` syntaxes or using AS will be syntax error for the SQL driver.
I just wanted to make sure these are valid Access db feature bc if so then mdbtools
has not much of chance to be able to work as access driver :(

[lauxjpn]

In isql using ` syntaxes or using AS will be syntax error for the SQL driver.
I just wanted to make sure these are valid Access db feature bc if so then mdbtools has not much of chance to be able to work as access driver :(

That is the conclusion I came to as well, after trying a couple of simple queries. MDB Tools supports only very simple queries and on top of that is very picky (not Jet compliant) with the supported syntax. I think using it with EFCore.Jet is way over its head. But I will take a look at the commercial drivers to see how they are performing.