Cannot create or read records in a SQLite over ODBC database?

[arjan]

I want to make a SQLite search front-end using LibreOffice Base, but it appears Base can’t read or create data in SQLite tables over ODBC. I have installed the ODBC driver (on Windows 10) and configured a database “Test”, starting with an empty SQLite database Test.db.

I can access the database and create a table from Base. I create a table MyTable, with columns “id” (INTEGER PRIMARY KEY) and “test” (VARCHAR). The table created in Base shows up in other SQLite managers.

But when I try to enter a record from Base, I get this:

Error inserting the new record
SQL Status: HY000
Error code: 1
no such table: D:\Temp\Test.db.MyTable (1)

If I enter a record from another manager and then open the database again from LO and open the table, it shows as empty.

Any clues what’s going on?

Update: I also found the following, could be relevant?

Edit > Database > Connection type > Test connection:
“”" The connection was established successfully."""

Edit > Database > Database properties > Test connection:
“”" A driver is not registered for the URL ~sdbc:odbc:Test.""" (???)

[Ratslinger]

Hello @arjan,

Unfortunately will not be able to help you much as Windows is not available to me. I currently use Linux only. Have gotten SQLite to work in Linux. See these posts:

LibreBase and SQLite/Spatilite

Base: how to connect to an SQLite database? [closed]

I post this to let you know that Base will work with SQLite and ODBC in Linux so it would seem to me this should also work in Windows. There is possibly some incorrect path somewhere within your set up.

[arjan]

What I find puzzling is that the SQLite ODBC drivers must be loaded, and the registered database is also found, because I can open a database as expected, and create a table. It seems odbc .ini files are not meant to be edited by hand on Windows – I guess it uses the register.

[arjan]

Update: the problem can be worked around by unchecking “Respect the result set type from the database driver” (under Edit > Database > Advanced Settings > Special Settings). Via this bug report. Also see this one. Also note that changing this setting does not yet persist upon reopening the database.

Additionally, the character set should be set to UTF-8 under the database connection type.

[arjan]

Update: actually, it does not persist. I was able to write to a table after changing this setting, but upon reopening the .odb file, I’m back to having the same problem with UTF-8 configured.

[arjan]

The UTF-8 config seems unrelated to the main problem (though I can’t explain how it worked at least one time). You should still configure it for correct handling of e.g. characters with diacritics.

[Ratslinger]

@arjan

Just peeked at one of my SQLite test .odb’s. Again a reminder that this is on Linux. In Database Properties I use the browse button and it lists all my ODBC connections available (originates from my odbc.ini system file). I just select one & Select OK button. Then that name stays in the ‘Name’ box even when going to Advanced Settings and back.

Also, in my connection settings I have Character set set to ‘System’.

[arjan]

It’s basically fixed now, I just left the second half of my answer for future reference or to create a bug report about the wizard if there isn’t yet any. I would use strike-through bit it isn’t available.

[Ratslinger]

Strike through should work.

[arjan]

What syntax did you use?

[arjan]

Oh just saw <del>...</del>. It looked off so I deleted it anyway.

[tonydiep]

The error also shows up on LibreOffice Version: 6.2.5.2 on Centos Linux 7 and is a showstopper.

The problem is within LibreOffice.

Notes

1. DBBrowser for SQLite opens the database correctly so the issue is not within SQLite
2. isql also opens, reads, and write to the database correctly so the issue is not within unixODBC

@arjan 's solution works. Thanks @arjan!

[the_watchmann]

@arjan == Yes! it works… many many thanks!