Open sqlite database on network drive
-
@OlivierLdff said in Open sqlite database on network drive:
This code is working as expected anywhere else.
Where is it not working? Or do you mean local path is working?
wrote on 15 Jun 2021, 08:39 last edited by -
wrote on 15 Jun 2021, 08:41 last edited by
@OlivierLdff if you tried what I posted you should have error text in the console. Please paste it.
"more logs" can be obtained by examining the value of QSqlDatabase::lastError() which is of class QSqlError -
@OlivierLdff
SinceaddDatabase(const QString &type, const QString &connectionName = QLatin1String(defaultConnection))please try:const QString path = "/run/user/1000/gvfs/smb-share:server=mynas.local,share=drive/db.sqlite"; auto db = QSqlDatabase::addDatabase("QSQLITE"); db.setDatabaseName(path); if (!db.open()) { qDebug() << db.lastError().text(); }Connection name != database name (or, in case of SQLite, database file name).
@artwaw said in Open sqlite database on network drive:
"/run/user/1000/gvfs/smb-share:server=mynas.local,share=drive/db.sqlite"
I doubt this is a valid file name for a sqlite database.
-
@OlivierLdff if you tried what I posted you should have error text in the console. Please paste it.
"more logs" can be obtained by examining the value of QSqlDatabase::lastError() which is of class QSqlErrorwrote on 15 Jun 2021, 09:28 last edited by OlivierLdff@artwaw So it seems I'm not even allowed to do
touch db.sqliteonce the file have been removed. Maybe this is an issue with my NAS?
Anyway I tried another name to see,foo.sqlite. So you were right, opening work now!Error: QSqlError("10", "Unable to fetch row", "disk I/O error") Error: QSqlError("", "Parameter count mismatch", "")File
foo.sqlitegets created on my NAS but size is 0 bytes.@Christian-Ehrlicher I can open this location with a QFile, and write to it without any problems.
-
@artwaw So it seems I'm not even allowed to do
touch db.sqliteonce the file have been removed. Maybe this is an issue with my NAS?
Anyway I tried another name to see,foo.sqlite. So you were right, opening work now!Error: QSqlError("10", "Unable to fetch row", "disk I/O error") Error: QSqlError("", "Parameter count mismatch", "")File
foo.sqlitegets created on my NAS but size is 0 bytes.@Christian-Ehrlicher I can open this location with a QFile, and write to it without any problems.
@OlivierLdff said in Open sqlite database on network drive:
I can open this location with a QFile, and write to it without any problems.
But sqlite can not handle this path since fopen() will for sure not accept it. Try it on the command line with sqlite3 executable.
-
@OlivierLdff said in Open sqlite database on network drive:
I can open this location with a QFile, and write to it without any problems.
But sqlite can not handle this path since fopen() will for sure not accept it. Try it on the command line with sqlite3 executable.
wrote on 15 Jun 2021, 09:43 last edited by@Christian-Ehrlicher I should have started with that.
sqlite3 /run/user/1000/gvfs/smb-share:server=mynas.local,share=drive/foo.sqlite sqlite> create table test(one varchar(10), two smallint); Error: disk I/O errorSo could this be related to my network drive?
-
@artwaw So it seems I'm not even allowed to do
touch db.sqliteonce the file have been removed. Maybe this is an issue with my NAS?
Anyway I tried another name to see,foo.sqlite. So you were right, opening work now!Error: QSqlError("10", "Unable to fetch row", "disk I/O error") Error: QSqlError("", "Parameter count mismatch", "")File
foo.sqlitegets created on my NAS but size is 0 bytes.@Christian-Ehrlicher I can open this location with a QFile, and write to it without any problems.
wrote on 15 Jun 2021, 09:45 last edited by@OlivierLdff size will be zero until you write anything to the db (create table for example), that is only to be expected.
Like @Christian-Ehrlicher wrote - SQLite driver usesfopen()so the path variable need to conform.For network resources (and I'd like to point out that there is a strong advocacy against using sqlite over network) you might want to consider URI notation https://sqlite.org/uri.html
-
@OlivierLdff size will be zero until you write anything to the db (create table for example), that is only to be expected.
Like @Christian-Ehrlicher wrote - SQLite driver usesfopen()so the path variable need to conform.For network resources (and I'd like to point out that there is a strong advocacy against using sqlite over network) you might want to consider URI notation https://sqlite.org/uri.html
wrote on 15 Jun 2021, 09:56 last edited by OlivierLdff@artwaw I tried reading from an existing sqlite file, no problem.
But writing doesn't seems to work, and always result in todisk I/O error. (even with prependingfile:to the path)So is my issue a known limitation of sqlite?
-
@artwaw I tried reading from an existing sqlite file, no problem.
But writing doesn't seems to work, and always result in todisk I/O error. (even with prependingfile:to the path)So is my issue a known limitation of sqlite?
wrote on 15 Jun 2021, 09:57 last edited by@OlivierLdff said in Open sqlite database on network drive:
So is my issue known limitation of sqlite?
I don't think so, I think you don't have write access.
-
@OlivierLdff said in Open sqlite database on network drive:
So is my issue known limitation of sqlite?
I don't think so, I think you don't have write access.
wrote on 15 Jun 2021, 09:59 last edited by@KroMignon Is there something special to get them?
I'm able to write QFile at this location. -
@KroMignon Is there something special to get them?
I'm able to write QFile at this location.wrote on 15 Jun 2021, 10:07 last edited by@OlivierLdff said in Open sqlite database on network drive:
Is there something special to get them?
I'm able to write QFile at this location.Your file path looks uncommon to me.
I am not sure it can be handled directly.
Does this works?touch /run/user/1000/gvfs/smb-share:server=mynas.local,share=drive/foo.sqlite -
@OlivierLdff said in Open sqlite database on network drive:
Is there something special to get them?
I'm able to write QFile at this location.Your file path looks uncommon to me.
I am not sure it can be handled directly.
Does this works?touch /run/user/1000/gvfs/smb-share:server=mynas.local,share=drive/foo.sqlitewrote on 15 Jun 2021, 10:13 last edited by@KroMignon said in Open sqlite database on network drive:
touch /run/user/1000/gvfs/smb-share:server=mynas.local,share=drive/foo.sqlite
Yes it works.
From what I see this issue isn't Qt related as I tought at the beginning.
-
@artwaw I tried reading from an existing sqlite file, no problem.
But writing doesn't seems to work, and always result in todisk I/O error. (even with prependingfile:to the path)So is my issue a known limitation of sqlite?
wrote on 15 Jun 2021, 10:27 last edited by@OlivierLdff might be this is somehow related to the way SQLite driver operates. I'd either mount the share to be accessible via usual path (like
/media/share) or ditch sqlite in favour of psql/mysql.
Please also note two things:- smb share might have performance issues with sqlite, this might lead to either transfer data loss or file being garbled (at the extreme);
- at the same time psql/mysql might be actually faster than sqlite over smb
Of course, it all depends on how extensively you use that db.
Part of the performance penalties can be alleviated by using nfs instead. In both cases it is recommended to turn off any kind of read/write caching for sqlite over network as caching introduces potential errors in the data. SQLite is designed to compete with file system operations (fopen, read, seek, etc) hence using it over the network is always tricky (I agree that for lightweight use it works most of the time... except where it does not and debugging those issues is never easy). -
wrote on 15 Jun 2021, 10:32 last edited by
@artwaw Thanks a lot for the informations.
For my particular case, I just need to populate a database with some data, then close it.
I think it will be way easier to write the sqlite database in sometmp/folder, then save it on my NAS with a copy operation.Thanks a lot for your support have a nice day.
13/17