DSN-less connection with PHP ODBC using MDBTools Driver

Question

I'm attempting to read from an Access database using MDBTools drivers to execute an odbc_connect on Ubuntu 11.10. It's working fine when using the DSN setup in /etc/odbc.ini.

Below are the contents of /etc/odbc.ini:

[logindb]
Description = Microsoft Access Try DB
Driver = MDBToolsODBC
Database = /home/folder1/TestDb.mdb
Servername = localhost

The Driver attribute in odbc.ini references MDBToolsODBC, so, here is my odbc setup in /etc/odbcinst.ini:

[MDBToolsODBC]
Description = MDB Tools ODBC
Driver = /usr/lib/libmdbodbc.so.0
Setup =
FileUsage =
CPTimeout =
CPReuse =

My problem is, when using $conn = odbc_connect('logindb','','');, I have to use the hardcoded value for the database location. Ideally, I would like to specify the first parameter of odbc_connect using a DSN-less connection, so that my database file can be a variable (will be reading from different dbs). Something like:

if ($cond1) {
  $db = "/home/folder1/TestDb.mdb";
} else {
  $db = "/home/folder1/TestDb2.mdb";
}

$conn = odbc_connect("odbc:Driver={MDBToolsODBC};Dbq=$db",'','');

I've also tried it without the odbc: prefix, but it did not work. Can anyone tell me why specifying the DSN works, but when trying to specify it on the fly using what looks like the same attributes, it doesn't work? I'm thinking it has to do with the parameters and contents of the first parameter in the DSN-less connection. As always, any help is greatly appreciated.

Roberts

@Rocket: Following the variable names in /etc/odbc.ini, shouldn't it be Database=$db? – Morez

@eggyal: That didn't help either. It still says: SQL error: [unixODBC][Driver Manager]Data source name not found, and no default driver specified. – Noted

@Rocket: And if you explicitly specify Driver=/usr/lib/libmdbodbc.so.0? – Morez

@eggyal: Yes. $conn = odbc_connect('logindb','',''); works fine. – Noted

@Rocket: Sorry, I meant if you specify it in the DSN: odbc_connect("Driver=/usr/lib/libmdbodbc.so.0;Database=$db",'','')? – Morez

@eggyal: That didn't work either =( – Noted

would $conn = odbc_connect("odbc:Driver={MDBToolsODBC};Dbq=".$db,'',''); work? (concatenating the string)? – Chord

Answer 1

I think it might not support it. Going from the source of the actual driver you see that it loads the params it needs to check, checks wether it has been given a DNS string, checks the ini files next and if it hasn't errored out it sets the params.

for reference from odbc.c latest mdb-tools (mdbtools-0.6pre1)

SQLRETURN SQL_API SQLDriverConnect(
SQLHDBC            hdbc,
SQLHWND            hwnd,
SQLCHAR FAR       *szConnStrIn,
SQLSMALLINT        cbConnStrIn,
SQLCHAR FAR       *szConnStrOut,
SQLSMALLINT        cbConnStrOutMax,
SQLSMALLINT FAR   *pcbConnStrOut,
SQLUSMALLINT       fDriverCompletion)
{
SQLCHAR FAR* dsn = NULL;
SQLCHAR FAR* database = NULL;
ConnectParams* params;
SQLRETURN ret;

TRACE("DriverConnect");

strcpy (lastError, "");

params = ((ODBCConnection*) hdbc)->params;

if (!(dsn = ExtractDSN (params, szConnStrIn)))
{
  LogError ("Could not find DSN in connect string");
  return SQL_ERROR;
}
else if (!LookupDSN (params, dsn))
{
  LogError ("Could not find DSN in odbc.ini");
  return SQL_ERROR;
}
else 
{
  SetConnectString (params, szConnStrIn);

  if (!(database = GetConnectParam (params, "Database")))
  {
 LogError ("Could not find Database parameter");
 return SQL_ERROR;
  }
}
ret = do_connect (hdbc, database);
return ret;

then when you verify in connectparams.c, ExtractDSN specifically looks for the DSN= string

 gchar* ExtractDSN (ConnectParams* params, const gchar* connectString)
 {
  char *p, *q, *s;

  if (!params)
  return NULL;
  /*
   * Position ourselves to the beginning of "DSN"
  */
  p = strstr (connectString, "DSN");
 if (!p) return NULL;
 /*
  * Position ourselves to the "="
  */
 q = strchr (p, '=');
 if (!q) return NULL;

And LookupDSN looks for inifiles or immediately returns with TRUE, depending on the HAVE_SQLGETPRIVATEPROFILESTRING precompiler setting.

So given that

SetConnectString (params, szConnStrIn);

only works on the data it got from the 2 previous functions, I think it doesn't support DSN-less. Only either a proper DSN= string or ini files.

Binucleate

Answer 2

The driver name after installing odbc-mdbtools will be MDBTools you can change it in /etc/odbcinst.ini

Instructions for C#

• sudo apt-get install unixodbc odbc-mdbtools (make sure to install >=1.0.0 version as previous versions can't read decimals properly)
• From your project directory run dotnet add package System.Data.Odbc or install via your favorite IDE

var odbcDriver = RuntimeInformation.IsOSPlatform(OSPlatform.Linux) ? "MDBTools" : "Microsoft Access Driver (*.mdb, *.accdb)";
await using var conn = new OdbcConnection($"Driver={odbcDriver};DBQ={mdbFilePath}"); // NOTE: no semicolon at the end of the string as it causes issues with MDBTools

Limitations

The same code that works on Windows might not work on Linux

• Column names were missing and I had to access columns data by indices instead
• SQL with ; at the end fails to parse and you get ERROR [] Couldn't parse SQL

I'd recommend using this driver only for simple use-cases where you just absolutely need to read some data from the database you don't own otherwise if you own it just don't use MS Access and port to something else.

Free alternatives

• Wrap command mdb-json or mdb-export (faster than json, performance comparable to using odbc but odbc will still be minimally faster ~100ms difference)
• Use UCanAccess or Jackcess directly via IKVM

Jackcess with IKVM example (bonus)

Surprisingly faster by over ~1000ms (reading 200k rows) than above solutions but doesn't support SQL

• Install IKVM.Maven.Sdk NuGet package
• Add MavenReference with Jackcess to your .csproj

<ItemGroup>
    <MavenReference Include="com.healthmarketscience.jackcess:jackcess" Version="4.0.5" />
</ItemGroup>

using com.healthmarketscience.jackcess;

using var database = DatabaseBuilder.open(new java.io.File("Northwind.mdb"));
var table = database.getTable("Customers");
var iter = table.iterator();
while (iter.hasNext())
{
    var row = (Row)iter.next();
    Console.WriteLine(row.getString("CompanyName"));
}

Polystyrene

Answer 3

It is supported in 0.7.1. You can get it from github:

https://github.com/brianb/mdbtools

Regarding the connection string, this works for me:

"Driver=Microsoft Access Driver (*.mdb);DBQ=///file.mdb;UID=;PWD=;"

Anatolia

Are you sure UID and PWD are supported? I can't locate it in the source code github.com/brianb/mdbtools/tree/master/src/odbc – Mintun

@MartinThøgersen, that's probably because you didn't search well. I don't remember where I got my answer from, but if I stated it it's because it was working. If you search for "DBQ" in the 0.7.1 sources you'll find some references, like in connectparams.c (function ExtractDBQ, circa 328, against ExtractDSN, 291 - that should give you at least a hint that using DSN-less connections is supported. Later they make use of it in odbc.c, and they do a do_connect using it. Did you at least try it? It's straightforward... – Anatolia

Answer 4

$driver = "MDBTools"; -- Driver name from /etc/odbcinst.ini
$dbName = "/path/to/database.mdb"; -- Full path of your MDB file
$db = new PDO("odbc:Driver=$driver;DBQ=$dbName", "", "");

Covet