Read MDB in Linux

[SymbolicFrank]

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]

Aircode

Code: Pascal  [Select][+][-]

1. MyODBCConnection.Driver:='MDBTools Driver';
2. //Set Transaction etc.
3. MyODBCConnection.Open;
4.  

[SymbolicFrank]

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]

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

[SymbolicFrank]

That location is just a shared volume.

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

[Zvoni]

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;

[SymbolicFrank]

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.