Ask Your Question
0

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

asked 2019-04-18 13:37:40 +0200

arjan gravatar image

updated 2019-04-19 14:11:01 +0200

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.""" (???)

edit retag flag offensive close merge delete

Comments

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.

Ratslinger gravatar imageRatslinger ( 2019-04-18 16:48:48 +0200 )edit

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 gravatar imagearjan ( 2019-04-18 17:30:23 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-04-19 14:09:26 +0200

arjan gravatar image

updated 2019-04-28 21:30:39 +0200

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.

edit flag offensive delete link more

Comments

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 gravatar imagearjan ( 2019-04-19 15:18:23 +0200 )edit

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.

arjan gravatar imagearjan ( 2019-04-28 20:09:53 +0200 )edit

@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'.

Ratslinger gravatar imageRatslinger ( 2019-04-28 20:47:38 +0200 )edit

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.

arjan gravatar imagearjan ( 2019-04-28 21:14:53 +0200 )edit

Strike through should work.

Ratslinger gravatar imageRatslinger ( 2019-04-28 21:19:38 +0200 )edit

What syntax did you use?

arjan gravatar imagearjan ( 2019-04-28 21:23:49 +0200 )edit

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

arjan gravatar imagearjan ( 2019-04-28 21:31:16 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-04-18 13:37:40 +0200

Seen: 101 times

Last updated: Apr 28