How to work with MS Access on Linux

Many people use Akses... even in production... even to this day. Therefore, there are moments when someone wants to connect to this database from some unexpected places. For example, with a UNIX server. Of course, to connect want, and to use the data from Acces to the web application. And, without a doubt, you will want to use these data together with information from other, more modern database.

So, I want to describe several approaches to communicate with the entity, called MS Access. Therefore, the initial task is to establish a connection to MS Access from Ruby on Rails application or from PostgreSQL (using FDW) and get access to the data, preferably in real time.

Below I will try to gather all the information related to the above problem and will try to describe a non-trivial cases, and underwater stools. I hope this description will save time for someone... or simply to some extent, amuse distinguished audience.

Immediately tldr for those who are important just the facts and the author's opinion on the matter.

Convert to CSV

For a start, I will describe a simple working solution. It is guaranteed to work on Ubuntu 14.04. Should work on other Linux distributions. And does not require any ~~highest/sacred~~ knowledge, skills, and other magic.

There is such a thing mdbtools. It is very simple:

the

sudo apt-get install mdbtools

Details about dependencies, manual Assembly, package and much more can be found on page GitHub'.

This package provides a bunch of different tools to work with Accessories. The entire pile will not be considered and will focus on one. The same one who knows how to turn mdb files into csv:

the

mdb-export 'mdb-file' 'table-name' > result.csv

The result is a csv file with the contents of the specified table. Further, this file can be subjected to all imaginable and unimaginable treatments and torture, because csv is incredibly simple and widely wide-spread format.

the

Executing queries in MS Access

Now the more difficult task: execute the SQL query with the. mdb file and the machine with anything UNIX -. You might guess that you need to put a couple of packages and create a few configs.

First, you will need ODBC. This is a standard API for communication with the database. In UNIX for this purpose there is a unixODBC. Its installation is very simple:

the

sudo apt-get install unixodbc libmdbodbc1

In the second package contains libmdbodbc.so, which will be needed below.

The next step is to find a suitable ODBC driver for MS Access. The closest available is the driver from mdbtools. Next, you need to dig deeper into the configs: to describe the driver and declare the database.

The driver is described in /etc/odbcinst.ini:

the

[MDBTools]
Description = MDBTools Driver
Driver = libmdbodbc.so
Setup = libmdbodbc.so
FileUsage = 1
UsageCount = 1

And DB is declared in /etc/odbc.ini:

the

[testdb]
Description = test
Driver = MDBTools
Database = /opt/db/MS_Access.mdb

It is worth noting that in "Driver" you need to specify the name of the driver, which is described in odbcinst.ini.

More about odbcinst.ini and odbc.ini can be found here.

So the configuration is finished. Now you can start executing queries. For these purposes we will use the utility isql from the unixODBC package:

the

isql testdb

If done correctly, you should see the console to run query:

the

SQL> SELECT * from "Section"
+------------+-----------------------------------------------------+
| Code | Section |
+------------+-----------------------------------------------------+
| 1 | Documentation |
| 2 | subassemblies |

| 4 | Sets |
+------------+-----------------------------------------------------+
SQLRowCount returns 4
4 rows fetched

Lastly, it is worth noting that there is an analogue of isql with support for Unicode. It's called iusql.

isql Oddities

Honestly, isql utility is quite high. It has a lot of restrictions on the syntax and not friendly and understanding user. For example: put the semicolon at the end of the expression — get the error and try to guess what it is. Any clues, tips and other delights of modern development here. It's not PotgreSQL, who kindly say that you made a mistake in the expression and suggest the correct option. Here you just send and not even inform the reason. Therefore, for at least some aid in isql was created wrapper pyodbc-cli. You can use it to somehow weaken the fight against isql and focus on writing requests.

Exotic encoding, tables and columns

A lot of rumors about the parameter 'Charset', which affects used kolovou page. Here is an example of ispolnitelskaya this parameter are:

the

[testdb]
Description = test
Driver = MDBTools
Database = /opt/db/MS_Access.mdb
Charset = CP1251

The effect of this setting on the job isql was observed.In isql I can work with. mdb files containing the Cyrillic alphabet, and with the usual unicodename mdb files. At the same time, the utility iusql regardless of the parameter 'Charset' has given a lot of question marks (that's about such: ��) when you work with Cyrillic. mdb file.

Alternatives to isql

Alternative to isql is mdb-sql of package mdbtools. For this utility is not needed ini files. You just need to set it on a specific. mdb file:

the

mdb-sql /opt/db/MS_Access.mdb

For all questions regarding the use of utilities a good response man page. The only feature: the aforementioned Cyrillic. mdb file utility to swallow I couldn't. With unicodename files was not a problem.

the

the Way Ruby/Rails

It's the middle of 2016, the latest release of MS Access was September 22, 2015. But here's the thing, the last work on the adapter for ActiveRecord dated 2008. Therefore, I have, as is customary, two news: good and bad.

Will start with the good: there is odbc-rails and his reincarnation the activerecord-odbc-adapter.

Now for the bad: as mentioned above, the last commits to the repository adapter dated 2008 and declared support for Rails and ActiveRecord version one and two; therefore, I don't know how to run it on Rails 3+ (is it possible to do this). The reasons for my ignorance about the following. First: the adapter bad documentation (or rather lack of). And secondly, no desire to go into the source code, to understand and to bring them back to life. So if you have enough knowledge, experience and time can platforms and describe how to use it. Good luck to you in this case!

Ruby-ODBC

Again with the adapter still sad you can look in other directions. One of the parties is called ruby-odbc.

Last update of this heme is dated 2011, but, at the moment, it works more or less. To install it you need to perform some simple steps:

the

sudo apt-get install unixodbc unixodbc-dev
gem install ruby-odbc

No package unixodbc-dev compile the native extension will fall off with an error: ERROR: sql.h not found.

Further, we assume that ODBC is configured in the system (ie the files are in odbcinst.ini and odb.ini). In this case, you can open irb and do the following:

the

001 > require 'odbc'
=> true
002 > client = ODBC.connect("testdb")
=> #<odbc::database:0x00000000e38d98>
003 > statement = client.prepare 'SELECT * FROM "Section"'
=> #<odbc::statement:0x00000000e11608 @_a="[]," @_h="{}," @_c0="{}," @_c1="{}," @_c2="{}," @_c3="{}"> 
004 > statement.execute
=> #<odbc::statement:0x00000000e11608 @_a="[]," @_h="{}," @_c0="{}," @_c1="{}," @_c2="{}," @_c3="{}">
005 > first_row = statement.fetch
=> [1, "\xD0\x94\xD0\xBE\xD0\xBA\xD1\x83\xD0\xBC\xD0\xB5\xD0\xBD\xD1\x82\xD0\xB0\xD1\x86\xD0\xB8\xD1\x8F\x00"] 

=> "Documentation\u0000"

More information about syntax and available commands, the gem ruby-odbc can be found in the directory ruby-odbc/test on github.

Mdb gem

This gem provides DSL'ku to work with. mdb files. And it looks pretty sweet. But there is a caveat: the gem is just a Ruby wrapper over the above mdbtools'ohms. That is, the gem converts mdb to csv and processes this csv in memory. No magic and direct calls to the database.

the

Alternative to ODBC driver

There are the commercial version of the ODBC driver for MS Access. But there is no actual information about it. In the optimistic scenario this adapter will help with advanced queries in Access (the driver from mdbtools lot of things are not able to: no LIMIT GROUP, AS, etc.). But this is only speculation. That will actually can be found out only after buying it, or taking a 14 day trial which is available after registration on the website. In addition to this information there was no user feedback or any bug reports, nor any mention of the fact that someone used a driver and he has helped.

the

Path PotgreSQL

For Postgres there is an extension OGR. It is part of the GDAL. Which, in turn, is a huge library for converting a raster and vector geospatial data formats. For our purposes the purpose of the library has absolutely no value. Importantly, stated that she is able to work with the. mdb format.

Installation

First you need to install several dependencies:

the

sudo apt-get install gdal-bin libgdal-dev
sudo apt-get install postgis postgresql-9.3-postgis-2.1

This command drags a ton of dependencies... but that's okay. The first set of packages to ogr_fdw, one for postgis.

Step two: collecting pgsql-ogr-fdw from source. Here is a small manual in the style of the bash:

the

git clone git@github.com:pramsey/pgsql-ogr-fdw.git
cd pgsql-ogr-fdw

sudo apt-get install postgresql-server-dev-9.3
sudo apt-get install checkinstall

make
sudo checkinstall

Yes, you can take a make install, but we don't want cats suffer. In the following dialog from checkinstall is necessary to correct the parameter "version". Need to do it in the format "numbers separated by periods" (e.g. '0.1.0'). Otherwise, with default values, assembling the pack will fall.

Step three: go and put extensions in Postgres:

the

CREATE EXTENSION ogr_fdw;
CREATE EXTENSION postgis;

There is a suspicion that postgis superfluous here, but in the Readme on GitHub says that we need both, so I'll leave this question inquisitive readers.

Step four: time to create a FDW. In ogr_fdw there are two possible ways to work with Access. The first uses the system ODBC. Details about this version can be found here. The second is more interesting, uses the MDB format from OGR, which provides direct access to the file using the Jackcess. Podrobnosti about this option are here. Below I will describe both methods.

Finally, one note: the OGR is an extremely powerful thing; the ability to work with MS Access is a small part of the variety of available formats and, dear reader, can quite reasonably say that firing from a gun on sparrows... but the choice is not great and except for this gun, no other guns to find failed. And Yes, that's list of all supported OGR'om formats.

ORG Format ODBC

This approach uses the system ODBC settings and works by analogy with the above osql and ruby-odbc, but in the database. All the available options to initialize the FDW presented in GDAL ODBC driver. Below I will give a simple usage example.

Actually here it is:

the

postgres=# CREATE SERVER testdb_access
postgres-# FOREIGN DATA WRAPPER ogr_fdw
postgres-# OPTIONS(
postgres(# datasource 'ODBC:testdb',
postgres(# format 'ODBC');

postgres=# CREATE FOREIGN TABLE access_sections (
postgres(# "ID", decimal,
postgres(# "Section" varchar)
postgres-# SERVER testdb_access
postgres-# OPTIONS (layer 'Section');
CREATE FOREIGN TABLE
postgres=# SELECT * FROM access_sections;
ERROR: unable to connect to layer to "Section"
HINT: Does the layer exist?

As I understand from the documentation OGR layer — in our case, is equivalent to a database table.

A list of all the layer s can be obtained using the tool ogrinfo:

the

$ ogrinfo -al 'ODBC:testdb'
geometry_columns is not a table in this database
Got no result for 'SELECT f_table_name, f_geometry_column, geometry_type FROM geometry_columns' command
INFO: Open of `ODBC:testdb'
using driver `ODBC' successful.

Based on this message, it can be assumed that all works, but the target database (i.e. an mdb file) that does not contain the required Geo-data format and OGR stumbles about this unfortunate misunderstanding. I don't know how to wean him forcibly to check the format provided by the database. But some writing that this approach works fine under Windows. In General, if you know how to reason with OGR ODBC to get it to work with any mdb file, please tell me about it, don't keep this knowledge to himself.

Separate question: how PG will work with Cyrillic (and any other non-Latin) names of tables and columns. On the one hand Postgres'have no difference what the name of the table/column, wrap them in double quotes and even special.symbols can be used. On the other hand: who knows if this applies to FDW, and check for the specific example still does not work.

ORG Format MDB

This approach is based on the Java library Jackcess. Since this is Java, and she has her own rich inner world, this approach no connection to the system ODBC and hence the problems with drivers for MS Access for it alien. But there are other features that I will describe below.

I shall warn that, due to the rich documentation throughout the described process, the lack of extensive experience with Java and some monstrous task package, a working version was able to collect in 3 days and ~20 full presporok package. So just say some things:

the

this approach is suitable only for unencrypted mdb files (i.e. files without a password); the

as this package is compiled, all of the following dependencies, path, version, and other attributes just for my specific case and environment, you may not be quite so mindlessly copy-paste command is not recommended.

So, all higenamine is a more expanded version of the original official descriptions GDAL MDB ACCESS database driver.

First: you need to put openjdk-6-jdk.

the

sudo apt-get install openjdk-6-jdk

After briefly thoughtful reading source GDAL', it seems that it supports both openjdk-7-jdk. But I could not get it to work with the 7th version.

Next, you'll need libgdal-dev.

the

sudo apt-get install libgdal-dev

Here you need to remember the version of the package. It is directly connected with the version of the GDAL package. In my case this is a version of 1.10.1.

note: support mdb format starts with a version of 1.9.0.

And lastly, you need to carry the gdal-bin package, as its extended version we are going to build from source.

the

sudo apt-get remove gdal-bin

Second: you need to download several JAR-s (ancient or not), namely: jackcess-1.2.2.jar, commons-lang-2.4.jar and commons-logging-1.1.1.jar; then, put them in lib/ext. In my case, the full path to this directory /usr/lib/jvm/java-6-openjdk-amd64/jre/lib/ext. The aforesaid version of the JAR-s can be found in this tool. For me, it works with any later version of commons-logging (1.*), with any other minor version of commons-lang (2.*) and jackcess (1.*). The error appeared only when using the next major version jaccess (2.1.4).

Third: you need to download and skonfigurowac GDAL.
the

git clone git@github.com:OSGeo/gdal.git
cd gdal/gdal/
1.10 git checkout

Here you need to go to the branch corresponding to the version of the package libgdal-dev, Koy was set in item number one. Otherwise, the assembled binary will be incompatible with the libraries.

Next you need to call configure. There are two ways to call. Simple:

the

./configure --with-java=yes --with-jvm-lib-add-rpath=yes --with-mdb=yes

and with explicit paths:

the

./configure --with-java=/usr/lib/jvm/java-6-openjdk-amd64 \
--with-jvm-lib=/usr/lib/jvm/java-6-openjdk-amd64/jre/lib/amd64/server \
--with-jvm-lib-add-rpath=yes \
--with-mdb=yes

The second option can be useful if you have multiple versions of Java (e.g. openjdk-6-jdk and openjdk-7-jdk) or if the first option did not give the desired result.

After work configure you need to find the magic word 'yes' in front of the MDB format.

Fourth: need to find a Cup of tea/coffee or something stronger and run the package build.

the

sudo checkinstall

Here you need to answer a few simple questions and wait. In my case, had to wait about 10 minutes.

Here it should be noted that the package will turn out heavy, about 300MB. Of course, you can throw out all superfluous, to raise his hands and approach the size of the gdal-bin package from repository (~900Kb), but it is beyond the scope of the narrative and therefore will not be described.

Fifth: if something went wrong building the package fell off, Google and bright mind to help you.

Sixth: if all went well, after checkinstall package was automatically installed and now we need to check whether the resulting binaries support. mdb files:

the

$ ogrinfo --formats | grep MDB
-> "MDB" (readonly)

If in the output of ogrinfo information about mdb not found, then go to the beginning of this section, read the manuals, look at the dependencies, settings, system, moon phase and other attributes that may affect the compilation of the final binary, and try to rebuild all this stuff again.

If the command and output match, then all is well and the turbid part is over. Now ogrinfo can work with mdb files and provide information about their contents:

the

$ ogrinfo /opt/db/test-database.mdb
INFO: Open of `/opt/db/test-database.mdb'
using driver `MDB' successful.
1: closeouts
2: economics

Seventh: you can now configure FDW in Postgres. Here is a small example script with this action:

the

postgres=# CREATE SERVER acc
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource '/opt/db/test-database.mdb',
format'. MDB' );
CREATE SERVER
postgres=# CREATE FOREIGN TABLE economics(
ID integer)
SERVER acc
OPTIONS(layer 'economics');
CREATE FOREIGN TABLE
postgres=# SELECT * FROM economics;
id 
----
1
2
3
4
5
(5 rows)

And, in General, everything. In conclusion, this section will say a few words about "encrypted". mdb files.

If FDW is unable to pull data from Access and ogrinfo swears as follows:

the

Exception in thread "main" com.healthmarketscience.jackcess.UnsupportedCodecException: Decoding not supported.
Please choose a CodecProvider which supports reading the current database encoding.
at com.healthmarketscience.jackcess.DefaultCodecProvider$UnsupportedHandler.decodePage(DefaultCodecProvider.java:115)

it is likely that you have password-protected mdb file. In this case you should look at FAQ from Jaccess and think about dogpile driver OGR Access. As far as I understand, there is a project Jackcess Encrypt. This project provides CryptCodecProvider, which, in turn, provides an implementation of CodecProvider interface for Jackess and supports several encryption formats. mdb files. But, unfortunately, the current driver from GDAL does not know how to work with Jackcess Encrypt and therefore does not support encrypted files. So, there is a good area to work in the camp of open source.

the

other FDW

A list of all available FDW for Postgres can be found at official wiki. There is ZhengYang/odbc_fdw, in which the last commit is dated 2011. And CartoDB/odbc_fdw, which actively develops and supports Postgres 9.5+. So the choice is small.
the

Conclusion

To work with MS Access painful... doubly painful if you need to do it under Linux. So just a good advice: pull akses data from any modern database and get rid of the car problems. If you pull out does not work, then work with ACC in Windows. There is the normal driver provided by Microsoft out of the box, dock Access, and Postgres in Windows there are at least some articles and examples of how to configure and all products of the same company, usually work well with each other. If this is not possible, then you have again two choices: to turn everything into a CSV and work with it or try to directly access the. mdb file. The first option is simple, works out of the box and special skills are not required. The second option is much harder, requires time, nerves, direct hand, has a set of limitations, pitfalls and other nasty things. Therefore, choose wisely.

the

Поиск по этому блогу

computer express