Linking Base to a SQLite database

English
#1

I’m trying to link (connect) Base to an SQLite database that I’ve created with python.

For this purpose, I’m using the ODBC driver that I’ve installed in my PC:
SQLite ODBC Driver
32-bit driver
64-bit driver
My PC is a 64-bit system, but somewhere I read that the 32-bit driver could work as well.

image

When I try to link to my SQLite database, I encounter 2 problems:

1- my SQLite database has an “.db” extension whereas Base expects to find an “.odb” extension

2- I get an Out of Memory error although my system has plenty of it

Could you please provide some guidelines as to how to connect to the SQLite database?

Thank you

#3

It has to fit the program. If you use OpenOffice ore LibreOffice-32bit use the 32-bit driver. If you installed LibreOffice-64 use the 64bit-driver. Same as with Java.

PS: To create a system DSN you need Admin-Rights, as a normal user you can create a user-DSN

#4

Thank you Wanderer, I’ll then use the 64-bit driver

#15

Connecting to databases is explained in Chapter 2 Creating a Database

#2

You connect a Base document (.odb) to the SQLite database (.db).
Once you have set up a system DSN, you start LibreOffice and call
menu:File>New>Database…
Connect to existing database of type ODBC and specify the ODBC name. That’s it.

#5

Thank you Villeroy,
I no longer have the error that I use to have.

However, by linking my Base database to the SQLite database, I expected to be able to see the tables and other data that I have put in it by means of a python program, but I see nothing.

Once the connection is established, how couls I see and read the data inside SQLite?

image
image562×740 22.7 KB

As you can see in the image, the ODBC seems active, with a SQLite3 datasource, but no tables in it

Thank you for your support

#6

I found a test database “chinook.db” online. This is my connection via Unix ODBC on Linux:

chinook_sqlite.odb
chinook_sqlite.odb518×576 52 KB

#7

JDBC is another option: Download Sqlite JDBC Driver | Connect to Sqlite

#8

Have not gotten this or any other JDBC connection to work with SQLite on Linux. Always errors trying to read table:
Screenshot at 2022-03-13 07-56-06

#9

I’m using several sqlite-Files on Win10 with the odbc-driver by ch-werner without any problems. Tables appear usually imediately after loading or (if I switch the database) after clicking “away” to reports and back to tables.
But I’m using 32bit and selected the last line in the odbc-options: the sqlite3-driver. (Now I didn’t install the support for the older driver.)

#10

Solution for the error message about wrong cursor type:
menu:Edit>Database>Advanced Properties…
[X] Respect the result set type from the database driver

1 Like
#11

Thank you. Much appreciated.

#12

However, the forward-only cursor has implications. Subforms fail with error “SQLite JDBC: inconsistent internal state /home/buildslave/source/libo-core/connectivity/source/drivers/jdbc/Object.cxx:189”. Listboxes work. The ODBC connection plays well with Base queries, forms and reports.

#13

Have used the ODBC connection for some years now for tests. Thanks for the JDBC info. Nice to have for testing and see what does/doesn’t work.

#16

Did you notice, in the first screen capture (ODBC connections), that you’ve got two of them ?
Namely SQLite Datasource 64 bits and SQLite3 Datasource 64 bits.

Dunno whether this is of importance (never tried using SQLite instead of SQLite3 on my side) but I’d suggest you should select the SQLite3 version, since you’re using the SQLite3 DB engine, aren’t you?

#14

Just to let you know that it is possible to use SQLite in Base thanks to the SQLite JDBC driver and two extensions:

enjoy…

1 Like

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