Description: After upgrading to 6.1.3 (from 6.1.2) the unixODBC connection to a sqlite database isn't working anymore. I tried the connection with isql client, and I can execute queries succesfully. Therefore I think it's a LO problem. I also tried to create a fresh new LO database linked to the same DSN: "test connection" button says the connection was successful, but no data is showed opening tables or executing queries. Steps to Reproduce: 1. Create a sqlite database, a table inside it and populate the table with some records. 2. Create a new unixODBC DSN pointing to the sqlite DB 3. Create a new base database linked to the ODBC DSN just created 4. Open the base database and open the table you populated in step 1. Actual Results: No record showed Expected Results: All records showed Reproducible: Always User Profile Reset: Yes OpenGL enabled: Yes Additional Info: Version: 6.1.3.1 Build ID: 1:6.1.3~rc1-1 CPU threads: 4; OS: Linux 4.18; UI render: default; VCL: gtk3; Locale: it-IT (it_IT.UTF-8); Calc: group threaded
When testing this on Mac, I get a rather unhelpful "could not connect" error message and nothing else. Tested with the libslqite3odbc driver obtained from Christian Werner's site on my own master 620alpha build. If I use iodbctest from the terminal, I can connect to the sqlite3 database. Seems indeed that there is a problem somewhere.
(In reply to verlata from comment #0) > Description: > After upgrading to 6.1.3 (from 6.1.2) the unixODBC connection to a sqlite > database isn't working anymore. I guess we can mark it as regression then...
I could reproduce this but have no idea what the cause may be, can’t help here.
Just updated to Version: 6.1.3.1 Build ID: 1:6.1.3~rc1-2 (debian testing). Bug still there. Anybody can suggest what could be done to detect the cause of the problem? TIA
Since I can't anymore use queries and reports, I would mark this bug as "critical". Thanks
I tested access to a sqlite3 db from Excel on MacOS Mojave using Christian Werner's ODBC driver. It still fails, but at least I get a more helpful error message : file system sandbox blocked open() of '/usr/local/lib/libsqlite3odbc-0.9994.dylib' So, at least for OSX, the reason for failure to even access the db seems to be that the dylib is sandboxed by the system.
(In reply to Alex Thurgood from comment #6) > I tested access to a sqlite3 db from Excel on MacOS Mojave using Christian > Werner's ODBC driver. It still fails, but at least I get a more helpful > error message : > > file system sandbox blocked open() of > '/usr/local/lib/libsqlite3odbc-0.9994.dylib' > > So, at least for OSX, the reason for failure to even access the db seems to > be that the dylib is sandboxed by the system. This looks like Excel is sandboxed, and is not allowed to load the ODBC driver.
Same happens with 6.1.3 on Ubuntu with unixODBC sqlite3 databases: they all can be opened, test connection is successful, but all all lack any record, all tables show empty. With isql I can access the same DSN and get all records. So LO suggests that all records have been lost, fortunately this is not the case. But most people will believe LO killed all their data. As this is pretty visible and existing databases cannot be used any more with the current version, a solution is dire needed.
Just updated to 6.1.3.2: problem still there.
It appears that the problem might be more general than just sqlite, see also bug 122306
updated to 6.1.4.2: no solution yet.
Is the right version marked here? If I read all the descriptions it seem the bug appears with LO 6.1.3, not LO 6.1.2. I have switched the version to LO 6.1.3.1 to mark it as the first version the bug appears.
@Robert: you are right. I choose 6.1.2 because at the time I opened the bug report 6.1.3 was not availabable in the version dropdown list. Still waiting for an hint to help me debug and fix the problem...
Confirming that this works on LO 6073 Build ID: 1:6.0.7-0ubuntu0.18.04.2 Threads CPU : 4; OS : Linux 4.15; UI Render : par défaut; VCL: gtk3; Locale : fr-FR (fr_FR.UTF-8); Calc: group
I can still access and write to a sqlite db over ODBC using my own master build Version: 6.2.0.0.alpha0+ Build ID: 965ac9915280e3d570d7b32ff20799507f4e42eb CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; Locale: fr-FR (fr_FR.UTF-8); Calc: threaded In all of my testing the ODBC datasource was a system declared DSN and not a user one (if that makes any difference). These tests were carried out on Linux Mint Tara (19)
Confirming this bug has also affected me on two separate machines running Windows 7. ODBC connection to sqlite3 data works, but with no records populating in the tables in base .odb file. After reverting to 6.0.7.3 with no other changes, records & data are displaying properly (on both machines).
Just installed 6.2.3 version (from deb packages): Version: 6.2.3.2 Build ID: aecc05fe267cc68dde00352a451aa867b3b546ac CPU threads: 4; OS: Linux 4.18; UI render: default; VCL: gtk2; Locale: it-IT (it_IT.UTF-8); UI-Language: en-US Calc: threaded Still not able to retrieve table contents. Tried also deleting libreoffice profile.
(In reply to Alex Thurgood from comment #15) > I can still access and write to a sqlite db over ODBC using my own master > build > > Version: 6.2.0.0.alpha0+ > Build ID: 965ac9915280e3d570d7b32ff20799507f4e42eb > CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; > Locale: fr-FR (fr_FR.UTF-8); Calc: threaded > Tried to install 6.2.0.0.alpha1 (alpha0+ isn't available anymore). Renamed profile folder: still no joy...
Problem fixed! After trying to reinstall sqlite odbc driver, I looked into edit -> database -> Advanced settings -> Special Settings: un-flagged "Respect the result set type from the database driver" et voilà: records are back visible!!! Maybe the version upgrade changed this setting without me to notice? I don't understand the purpose of this setting, so I cannot say wether this is a clean solution: any explanation will be greatly appreciated.
@verlata : there have been some changes to the code of the config settings of the Advanced Properties, which is why it probably now works. However, as we don't know precisely which changes might have caused that, setting as RESOLVED WORKSFORME
To be clear: the bug is STILL PRESENT in the latest Libreoffice 6.2.3 on Windows 10 64bit: when opening an odb file that has a ODBC database connection to an sqlite3 database file via Ch. Werner's sqliteodbc driver, all tables appear empty. But: the workaround unchecking "Respect the result set type from the database driver" works. So: not the code has changed, the bug is still present - and not only with unixODBC, with Windows ODBC also So please: Either uncheck "Respect the result set type from the database driver" by default in all ODBC databases, or fix the bug. Reopening ...
Just(In reply to Heinz Repp from comment #21) > To be clear: the bug is STILL PRESENT in the latest Libreoffice 6.2.3 on > Windows 10 64bit: when opening an odb file that has a ODBC database > connection to an sqlite3 database file via Ch. Werner's sqliteodbc driver, > all tables appear empty. > An additional note: the "uncheck Respect the result set type from the database driver" workaround works only inside a session: even if you uncheck and save, next time you reopen the odb file all tables appear empty and you have to uncheck again. (using 6.2.4 amd_64 on debian)
(In reply to verlata from comment #22) > > An additional note: the "uncheck Respect the result set type from the > database driver" workaround works only inside a session: even if you uncheck > and save, next time you reopen the odb file all tables appear empty and you > have to uncheck again. > > (using 6.2.4 amd_64 on debian) Seems there is something going wrong with saving this in the content.xml of the *.odb-file. Could someone post here a simple *.odb-file, which should include a connection to sqlite by ODBC?
Have tested this now again with LO 6.3.2.2. on OpenSUSE 15 64bit rpm Linux Created a ODBC connection to a SQLite database. Created a table in this database. Added some content to the table. Closed the database and LO (after saving all) and reopened LO and the database. The content is shown in the table. "Respect the result set type from the database driver" isn't set. So I have checked "Respect the result set type from the database driver". The content in the table isn't shown any more. Saved it, closed LO, reopened ... Content of the table isn't shown, so I unckecked "Respect the result set type from the database driver". Content in the table appears, could be edited ... Saved this, closed LO, reopened and had a look at the tables: Content is shown. So I looked in the *.odb-file, content.xml. After checking "Respect the result set type from the database driver" apperars: db:data-source-setting-name="RespectDriverResultSetType" After unchecking this entry has been deleted. So I couldn't find any buggy behaviour here.
The pb was brought with: https://cgit.freedesktop.org/libreoffice/core/commit/?id=3208fcb3a36d75d6290d9c548430682f153b09db author Julien Nabet <serval2412@yahoo.fr> 2018-09-13 22:57:58 +0200 committer Julien Nabet <serval2412@yahoo.fr> 2018-09-20 23:06:41 +0200 commit 3208fcb3a36d75d6290d9c548430682f153b09db (patch) tree af6d363ce1d2aaffa3e2c479c5f79909ba1d1ba5 parent 2739dee0eab2edbba898cb7115579e43ea89399d (diff) tdf#119743: add Features entries in Properties block Drivers.xcu in order to save special options of a database file. Regression from d6fce54c82868b82bd6fa190db6047d69bbb3ecf Read https://bugs.documentfoundation.org/show_bug.cgi?id=119743#c5 for detailed explanation then fixed with: https://cgit.freedesktop.org/libreoffice/core/commit/?id=f33eb08a2b74e5de033af9b5f5283b220ac58ded author Julien Nabet <serval2412@yahoo.fr> 2019-03-09 17:39:44 +0100 committer Mike Kaganski <mike.kaganski@collabora.com> 2019-03-10 06:25:44 +0100 commit f33eb08a2b74e5de033af9b5f5283b220ac58ded (patch) tree 62aa41c03f593a3f42bc3100eaa30e39e10c360b parent 59f58b432f6998cf1255dd4439a20c9369d01187 (diff) tdf#121092: synchronize defaults for ODBC settings The defaults for EnableSQL92Check, PreferDosLikeLineEnds, RespectDriverResultSetType in connectivity/registry/odbc/org/openoffice/Office/DataAccess/Drivers.xcu made consistent with respective defaults in ODatabaseModelImpl::getDefaultDataSourceSettings When inconsistent, the settings don't ever get written to the ODB's content.xml (likely they are filtered out as default at different levels). This change allows the non-default to be written. Since the defaults in ODatabaseModelImpl::getDefaultDataSourceSettings are there at least since 2006 (commit 0eb026293c88d422d701d1a06735d9cce5457ca9), so that in any existing ODB the settings are absent if they are equal to those defaults, they are not changed. It should be fixed in last stable LO version 6.3.5 or brand new 6.4.3. However it will work only for brand new odb files. For old files, you must uncheck the option manually.
Reproducible with 24.2.6 on Ubuntu 24.04. I am connecting to an Access database using unixodbc. isql works but libreoffice displays no tables. During the Database Wizard the "Test Connection" procedure reports a success. When we advance to the creation of the database no error is shown but no data is shown either.
(In reply to gustavo from comment #26) > Reproducible with 24.2.6 on Ubuntu 24.04. > > I am connecting to an Access database using unixodbc. isql works but > libreoffice displays no tables. > > During the Database Wizard the "Test Connection" procedure reports a > success. When we advance to the creation of the database no error is shown > but no data is shown either. This bug is about SQLite and UNIXODBC. Has nothing to do with Access. Please write a new bug for what you detected with Access.