Recent

Author Topic: Read MDB in Linux  (Read 566 times)

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1256
Read MDB in Linux
« on: March 15, 2023, 01:41:58 pm »
Is there an up-to-date description on how to read an .MDB database in Linux (Ubuntu 22)?

I found and installed odbc-mdbtools and unixodbc. According to odbcinst I have the following driver installed:

Code: Text  [Select][+][-]
  1. $ cat /etc/odbcinst.ini
  2. [MDBTools]
  3. Description=MDBTools Driver
  4. Driver=libmdbodbc.so
  5. Setup=libmdbodbc.so
  6. FileUsage=1
  7. UsageCount=1

But I don't understand how to configure a TODBCConnection with that.

Zvoni

  • Hero Member
  • *****
  • Posts: 1764
Re: Read MDB in Linux
« Reply #1 on: March 15, 2023, 03:37:37 pm »
Aircode
Code: Pascal  [Select][+][-]
  1. MyODBCConnection.Driver:='MDBTools Driver';
  2. //Set Transaction etc.
  3. MyODBCConnection.Open;
  4.  
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1256
Re: Read MDB in Linux
« Reply #2 on: March 15, 2023, 04:14:40 pm »
Aircode
Code: Pascal  [Select][+][-]
  1. MyODBCConnection.Driver:='MDBTools Driver';
  2. //Set Transaction etc.
  3. MyODBCConnection.Open;
  4.  

I tried that (filled in the properties in the Object inspector):

Code: Text  [Select][+][-]
  1. Could not connect with connection string "DSN=/var/lib/postgresql/data/Bucket/DB.MDB;DRIVER=MDBTools Driver;".
  2. ODBC error details: LastReturnCode: SQL_ERROR; Record 1: SqlState: IM012; NativeError: 0; Message: [unixODBC][Driver Manager]DRIVER keyword syntax error;
  3.  

But the syntax of those connection strings isn't very strict, it sems.

Zvoni

  • Hero Member
  • *****
  • Posts: 1764
Re: Read MDB in Linux
« Reply #3 on: March 15, 2023, 05:45:09 pm »
The DSN points to Postgres??????

What about constructing your own connection-string, and not use something built in the background from those properties?

There is something wrong.
I‘d expect curly brackets around the driver name in the connection string
« Last Edit: March 15, 2023, 05:47:40 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1256
Re: Read MDB in Linux
« Reply #4 on: March 15, 2023, 05:54:53 pm »
That location is just a shared volume.

Yes, it does look strange, but it is what FPC makes of it. I'll experiment.

Zvoni

  • Hero Member
  • *****
  • Posts: 1764
Re: Read MDB in Linux
« Reply #5 on: March 16, 2023, 09:03:26 am »
According to this: https://www.connectionstrings.com/access/ (scroll down to last third of the page)
the Connection String should look like something


Driver={MDBTools Driver};Dbq=/var/lib/postgresql/data/Bucket/DB.MDB;
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1256
Re: Read MDB in Linux
« Reply #6 on: March 16, 2023, 11:50:57 am »
I figured it out. This is my TSpecialConnector:

Code: Pascal  [Select][+][-]
  1. type
  2.   TSpecialConnector = class(TSQLConnector)
  3.   protected
  4.     procedure DoInternalConnect; override;
  5.   end;
  6.  
  7. function CreateSQLConnection(ThisDatabaseLocation: string = '';
  8.   ThisType: DatabaseType = dtSQLite; ThisUser: string = ''; ThisPass: string = ''):
  9.   TSpecialConnector;
  10.  
  11. implementation
  12.  
  13. procedure TSpecialConnector.DoInternalConnect;
  14. begin
  15.   if ConnectorType = 'SQLite3' then TSQLite3Connection(Proxy).OpenFlags :=
  16.     [sofReadWrite, sofCreate, sofFullMutex, sofSharedCache];
  17.  
  18.   inherited DoInternalConnect;
  19. end;
  20.  
  21. function CreateSQLConnection(ThisDatabaseLocation: string;
  22.   ThisType: DatabaseType; ThisUser: string; ThisPass: string): TSpecialConnector;
  23. begin
  24.   Result := TSpecialConnector.Create(nil);
  25.   Result.KeepConnection := False;
  26.  
  27.   case ThisType of
  28.     dtSQLite:
  29.     begin
  30.       Result.ConnectorType := 'SQLite3';
  31.       if Empty(ThisDatabaseLocation) then
  32.         Result.DatabaseName := DefaultDatabase
  33.       else Result.DatabaseName := ThisDatabaseLocation;
  34.       Result.Params.Add('foreign_keys=ON');
  35.       Result.Params.Add('journal_mode=WAL');
  36.     end;
  37.     dtMSSQL:
  38.     begin
  39.       Result.ConnectorType := 'MSSQLServer';
  40.       if Empty(ThisDatabaseLocation) then
  41.         Result.HostName := 'localhost'
  42.       else Result.HostName := ThisDatabaseLocation;
  43.       Result.DatabaseName := 'DefaultDatabase';
  44.     end;
  45.     dtPostgreSQL:
  46.     begin
  47.       Result.ConnectorType := 'PostgreSQL';
  48.       if Empty(ThisDatabaseLocation) then
  49.       begin
  50.         Result.HostName := 'localhost';
  51.         Result.DatabaseName := DefaultDatabaseName;
  52.       end
  53.       else Result.HostName := ThisDatabaseLocation;
  54.     end;
  55.     dtODBC:
  56.     begin
  57.       Result.ConnectorType := 'ODBC';
  58.       {$IFDEF UNIX}
  59.       Result.Params.Add('DRIVER=MDBTools');
  60.       {$ELSE}
  61.       Result.Params.Add('DRIVER=Microsoft Access Driver (*.mdb, *.accdb)');
  62.       {$ENDIF}
  63.       Result.Params.Add('DBQ=' + ThisDatabaseLocation);
  64.     end;
  65.   else
  66.   end;
  67.  
  68.   Result.UserName := ThisUser;
  69.   Result.Password := ThisPass;
  70.  
  71.   Result.Transaction := TSQLTransaction.Create(Result);
  72. end;
  73.  

 

TinyPortal © 2005-2018