Closed MultiCoinCharts closed 3 years ago
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).
@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 .
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.
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.
brew uninstall mdbtools
- we need to reinstall with the --with-unixodbc
option.export HOMEBREW_EDITOR="/usr/bin/nano"
- this is absolutely optional.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 viYou'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
brew install mdbtools --with-unixodbc
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).
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.
OdbcConnection
(even though vscode says UseJetOdbc()
accepts this type - it runtime crashes with a Type kind ArgumentException.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.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:filenameOrConnectionString
my-log
gets createdunixodbc 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.
@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!
@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.
@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.
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.
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 withnew OdbcConnection()
OROdbcFactory.Instance.CreateConnection()
will both produce the error:must be of type JetConnection
.This is because the
UseJetOdbc()
function will simply pass your OdbcConnection toUseJet()
, then UseJet will check to see of it's castible toJetConnection
. 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.
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 :(
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.
hi again! there seems to be a prosibility for using Access + EF Core + Linux but in a different way. On Windows you can use this repo's scaffold tool to map an MDB to C# classes. Then, alternate Linux-compatible DbProvider can be use to realise the classes. There are some Column Type collisions like Access's currency or counter but this can be fixed by hand.
Thank you again so much
@P-Medicado Interesting! Can you drop a link to the DbProvider
you are talking about?
I'm sorry, the developer working on it used the JDBC UCanAccess bridge, this includes too much runtime translation for production use. We decided to stick with Access as a UI for designing databases for other database platforms.
Using Access as a web server database is a powerful experience for developers who don't have a database engineering background. The development experience on Windows using EFC Jet is entirely doable (even Microsoft acknowledged apparently). However we have created a repo for translating mdb/accdb files into MySQL databases which are crossplatform by default.
Here are potential alternatives for Access as a web server database outside of Windows:
Access db to MySQL translator / Example Access + EFCore Jet App repo
@P-Medicado Thanks for listing your research. Especially option 1 looks very promising in the long run and would just need a bit of dedication of someone to port the UCanAccess / Jackcess code to .NET.
Of course migrating projects to a different database is definitely an option as well, as you do with your translation repo.
Using macOS with Access 2002 + ODBC.
ODBC + Drives are installed/working via:
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.The error this produces
The connection parameter must be of type JetConnection.
To get around this, we tried creating a demo project using only
System.Data.Odbc
In that demo, we use
OdbcConnectionStringBuilder
withOdbcConnection
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 myOdbcConnection
?Here the missing link to Accdb + EF Core + Unix + OData + ASP .NET Core looks to be a connection sanitization bias.
UseJetOdbc()
lowercase attributes likeDBQ
which are case-sensitive on Unix?OdbcConnection
.OdbcConnection
that doesn't derive fromSystem.Data.Odbc
?I have
EntityFrameworkCore.Jet.Odbc
in my project - this case is not urgent, please take your time.