CODEWITHSUNDEEP
phpms-accessubuntuodbcmdbtools
Tim S
Tim S

Reputation: 5111

DSN-less connection with PHP ODBC using MDBTools Driver

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.

Upvotes: 10

Views: 7694

Answers (4)

Konrad
Konrad

Reputation: 7237

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

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"));
}

Upvotes: 3

F&#225;bio Galera
F&#225;bio Galera

Reputation: 125

$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", "", "");

Upvotes: 0

Pere
Pere

Reputation: 1075

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=;"

Upvotes: 0

Harald Brinkhof
Harald Brinkhof

Reputation: 4455

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.

Upvotes: 7

Related Questions