We have a database connection in ignition that randomly is giving us some errors.
Main problem is that, sometimes, a query does not find a row that exists.
The query is so simple, is "select * from table where col='something'". (We have checked that the row exists).
Here some info that hope helps to find out whats happening:
- Database is access, mdb. Connection is valid in server and the querys are being executed. No errors are found in logs
- Database has up to 58.000 rows, but number of rows found are 28.001
- Not only last rows are missing, also random rows in between.
- This error happens and solves randomly, maybe after couple of hours or next day
Has this happened anyone? What could I check to have more information about what is happening?
thanks,
Never had an issue where Ignition was giving me different than expected results and the problem wasn't some error on my part. It essentially just passes your query along. Can you show how you are actually calling this query in Ignition? Perhaps you are doing it in problematic way.
We have a project that uses this table in bindings and so. For testing, I've done some straightforward querys. with a query in a table and with a named query.
Both results are empy and I'm sure this row exists (I've taken this id_number from the table).
MS_ACCESS? 
The only times I've seen non-deterministic evaluation of queries is when something is affecting one collection out of Ignition's "pool" of connections (by default, 8).
If the affected connection is randomly picked from the pool, you'll get weird results, and then the next connection will work fine.
Are you running any stored procedures? Is this actual MS Access? How are you connecting to it, if so - a dedicated JDBC driver or via a connection wrapper through e.g MSSQL?
2 Likes
I hope I can give you correct info about that, not an expert in this topic.
Yes, We are consulting and access database from ignition, we created a database connection. This is type of connection to the database:
what could affect pool connections? Should I uncheck option "test on borrow" from configuration?
thanks!
This won't do anything.
The main thing I've seen do this is stored procedures. But you're using a third-party JDBC driver I have no direct experience with, so I really couldn't tell you.
You're best long term solution here is to transfer over to a real database. As I recall there is some path from MS Access to SQL Server that shouldn't be overly difficult.
2 Likes
