Select query does not give new added records

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Select query does not give new added records

Koen Amant
I have a table with 9 records. When I run 'SELECT * FROM table;' in my
VB.Net I only get 6 from the 9 records. When I run the query in 'DB Browser'
an SQLite viewer I get them all.

The thing is there is a service running in the background who adds records
to the database (POS system) I can't stop this service and all the new
records that are added I can't see in my query result. It's like the
database is locked for other users and so it gives an old snapshot of the
database... Very Strange.

How can I prevent this?

 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Select query does not give new added records

Simon Slavin-3
On 31 Mar 2018, at 2:04pm, Koen Amant <[hidden email]> wrote:

> there is a service running in the background who adds records
> to the database (POS system) I can't stop this service and all the new
> records that are added I can't see in my query result. It's like the
> database is locked for other users and so it gives an old snapshot of the
> database... Very Strange.

Are the 'missing' records always the most recently added ones ?

First, make sure the database isn't corrupt by executing

<https://sqlite.org/pragma.html#pragma_integrity_check>

If the database passes this, then the problem is that the POS system is not committing its changes immediately.  This speeds things up for the POS since most of the time taken to make changes to a database is taken up by the COMMIT statement, not the INSERT statements.  It also reduces wear and tear on the storage device, which is especially important if it's solid state, e.g. Flash storage.  It is not at all surprising that the POS does this: it's don't in many systems which generate a continuous log because it makes such a difference.

You should be able to cause the POS system to flush all changes to the database by quitting and restarting it, but you have said that you can't do this.

An alternative might be to use

<https://sqlite.org/pragma.html#pragma_read_uncommitted>

However, you may need to not only do this in the connection you're using the read the database but also do it in the connection the POS is using.  And it may be that the only way to do that is to make a change to the POS.

If you can't do this, my only suggestion is to figure out how often the POS system commits changes and trigger it somehow.  So if it does one COMMIT every ten minutes, wait ten minutes.  Or if it does a COMMIT every ten transactions, generate ten dummy transactions and reverse them.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users