"The database does not support relations" connecting to SQLite database via ODBC

English
#1

I am using Base to connect to a SQLite database via ODBC. My intention was to use Base as a front end to graphically view relations and create and use forms. I am using the Chinook sample database for testing.

When opening the Relations window, I get the message “The database does not support relations.”.

Is what I am attempting feasible?
If so, what am I doing wrong?

I’m very new to databases. Any help appreciated.

Version: 24.8.5.2 (X86_64) / LibreOffice Community
Build ID: fddf2685c70b461e7832239a0162a77216259f22
CPU threads: 12; OS: Windows 11 X86_64 (10.0 build 26100); UI render: Skia/Vulkan; VCL: win
Locale: en-GB (en_GB); UI: en-GB
Calc: threaded

#2

and that is your problem: If Sqlite does not support relations Base can not show them. Use another database.

#3

SQLite does support relations. It’s a standard relational database manager.

#4

Sqlite is a lot, but not “your average standard”. IMHO Sqlite can do relations, but it has to be compiled for this and you can not simply assume any Sqlite-db has this. Have you checked the PRAGMAs?

Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection.

the sentence is cited from here:
https://sqlite.org/foreignkeys.html

#5

Which pragmas do you think would be useful in this situation?

There shouldn’t be a problem with the db as chinook is a commonly used sample db.

I enabled Foreign Keys when configuring the db in ODBC Data Source Admin.

Anyone got any other ideas?

EDIT
Ran pragma foreign_keys =1

#6

If LibreOffice will throw “The database does not support relations” this might be wrong. But LibreOffice doesn’t support to read and write the relations (trough Tools → Relations) in this type of database depending on the created tables and the connection to the database.

#7

Thank you. Just to be clear, are you saying that Base does not allow relationships to be read or written in the Relationships tool?

Does it allow preexisting relationships between tables to update through cascade if data is changed or entered in a form, say?

EDIT
Other tables seem to be updating normally when linked in the Forms window. Base has crashed a few times though, but that could be anything.

EDIT 2
It appears RobertG is correct in saying that the Relationships tool doesn’t work in this situation and so I will mark this solved.

Still haven’t got this to work to satisfaction. I don’t think this is the tool I need for this job.

#8

If you could create the relations through SQL it will work. But it couldn’t bee seen well in Tolls → Relationship.
Its a time ago Base also gives this error when connecting to MySQL/MariaDB. There are MyISAM-tables in this type of database, which couldn’t be linked by foreign keys. When default changed to InnoDB-tables Base could also create the relations.

I only use Firebird (and sometimes old HSQLDB) for databases, which are only used by one user. This 2 types are databases, which work as internal databases with Base.
For connecting to a server I would use MariaDB/MySQL or PostgreSQL. There are drivers directly supported by LibreOffice. So you won’t need ODBC or JDBC for connecting.

1 Like
#9

I’d start with PRAGMA foreign_keys, but you obviously didn’t read the docs on Sqlite.

There is always a problem, if database developers does not know the tools they are using. You can hande chinook db even without graphical shown relations. Features, wich you can not do is
referential integrity or cascading deletions.
.
The question is, why you’ve choosen Sqlite. I use it as a local file based database, with better safety of my data than the embedded databases, besides providing a cli and easy access from python-scripts, if necessary.
.

If you wish visual relations check MySQL/MariaDB. Forms work with Sqlite also.

Have you seen the guide to Base on LibreOffice.org ?
(At the menu “get help → Documentation” )

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.