PDA

View Full Version : setData on SQLite Tables with Multiple Connections



aseylys
21st November 2017, 17:30
To start off with, I'm sorry I can't really post my code. To show the bug would require my entire program and it's just too much to attach, so I'll be as specific as possible.

I have a database file, called `TF.db`. In this file, there are 2 tables, `table1` and `table2`. The idea of the program is that `TF.db` is put in a shared network folder, and people on different computers connected to the network can see changes occurring to the database in realtime.

To make sure that the connection to the table is unique, I call addDatabase('QSQLITE', userName) where userName is the username of the user (duh). I then have 2 QSqlTableModel's that are created the same way and are very similar to each other in terms of how they edit and set data: oneModel.setTable('table1') and twoModel.setTable('table2'). The models are then set to their respective QTableView's, but that isn't important in the bug.

When one user implements oneModel.setData() another user can change that new data with oneModel.setData() if they feel. The bug is when one user tries to implement twoModel.setData(), if another user tries to change it, setData sets the entire record to None. But if the same user who initially implemented twoModel.setData() changes the data, they successfully can.

If I test this with just one user accessing the database at a time, everything works fine. It's only when 1+ users are trying to change each other's data

Can anyone help me? Again, I know not attaching the code itself is hard, but I was wondering if someone had any experience with multiple users connecting to a database with more than one table in the database? If you need any more info I'll be glad to provide anything.

Thanks guys.

d_stranz
21st November 2017, 20:12
people on different computers

It sounds like you are trying to use SQLite in a client-server mode. It isn't designed for that. You should read this discussion (https://sqlite.org/whentouse.html) of when to use SQLite vs. a traditional client-server database engine.

aseylys
21st November 2017, 20:53
Thank you d_stranz, I didn't know there was a difference.

Because the computers I'm working on don't have MySQL I think I can go with ODBC. However, I'm not sure how to go about connecting to the database now. With SQLite, I can simply put db.setDatabaseName(database_location) where database_location is the literal location of the database, but with ODBC, I don't think it doesn't work like that. Any suggestions?

d_stranz
21st November 2017, 22:54
I think I can go with ODBC.

ODBC is just an alternative database driver (see here (https://docs.microsoft.com/en-us/sql/odbc/reference/what-is-odbc)); you still must connect to an actual database engine like MySQL, PostgreSQL, or Microsoft SQLServer. If you continue to use an SQLite-based engine, you'll have the same issues you have now.

As the article I pointed to in my first post describes, you can develop a client-server system based on SQLite. You have to implement a server that runs on the machine where the database file lives. That server can use SQLite as its DBMS, but that is hidden. It has to wrap access to the database to prevent simultaneous access by remote clients. The remote clients do not use SQLite, but access the server through a network-based protocol.

If you have only two tables, then you could use a simple protocol like SOAP (there are some Qt SOAP implementations - Google for them) to send SQL queries from client to server and receive replies. You can develop a QAbstractItemModel to map these replies into something you can display in table views in your clients.

It might be easier to just use a true concurrent client-server DBMS.