If I use system.db.runNamedQuery in a script when a button is pushed or if I do an event binding to the names query when the window opens (button will open next window) how do I know my gateway name query has finished?
I will have several users running vision clients that will execute these named queries at different intervals so I am concerned about the time to get the data back, not using stale data. Is one method preferable to the other for this?
Should I also be concerned about collisions from these clients firing queries to same gateway/database? Does the gateway queue the queries and only respond with the data requested to the client requesting it?
I can't simulate real world conditions until we're in production and I don't want to find out then that I've made a bad architectural choice.
You know the query completed because the script function returns with results. It won't return early.
Ignition does not limit parallelism across clients. If you need locking out one client while another makes updates, you will need more code, likely in a gateway message handler, and probably a jython re-entrant lock. For typical applications, this is way overkill.
Most database connections default to a pool of eight simultaneous connections. So, until you have that many running together, there's no queuing.
Share more details about your application requirements to get more detailed answers.
Is there any chance client one ask and client two data gets mixed up if requests launch at same time? If so, can I tell if there is an in-progress query to hold off until it completes? I expect to have 7-10 clients running at once, though only 2 -4 will access the same db table at the same time.
Yes, and No. But any client that performs a NQ that updates the table can follow that with a broadcast message to all other clients reporting what they did. Clients that receive the message, and are displaying part of the affected table, can refresh their bindings or otherwise requery.
(In general, you should be use bindings, not scripts to pull NQ data into your user interfaces. That lets you use the native system.db.refresh() method to cause such bindings to update.)
Appreciate your patience as I am trying to look around the corner. I have a read table and a write table. I want to make sure all client reads are clean even if they each ask to read at same time for different rows.
I want to make sure all writes are clean, they are writing a whole row of data to table with identifiers so I will know which client sent it. The writing seems like less of an issue. If there is a chance of collisions, I could create separate tables for each to read and write, More hassle to setup but if needed for data integrity, I would do it.
Databases themselves struggle with what you seem to want, and solve it with "transaction isolation". You should simply apply those techniques and not have Ignition get in the middle.
(Maintaining separate tables for reads and writes seems like a disaster. Share more details about your workflow and the UI that supports it, and what you expect to happen in the DB.)
I have one table with build information (read), one table to record build data (torque values, test data) multiple manufacturing folks will be running clients, working at their pace, advance to next build step, save build data. Based on natural pace, wouldn't expect them to all hit next or save at same time but firm believer in murphy's law. so trying to see if I need to build my own 'semaphore' blocking to prevent those collisions. Not a SQL power user but looking up the transaction isolation techniques now.
I think you're imagining a problem where there isn't one. Generally you make sure that your (one) table has an id column which is auto-incrementing. That way, even if two users write identical records they are both saved but with different id numbers. In your case you will also have a client identifier.
Reads are not a problem - no matter how many clients read simultaneously (until you hit some performance limit).
Put all the data in one table unless you have some need to perform a lookup such as client description based on client id or similar in which case you would use a JOIN statement in your query. e.g.,
SELECT proddata.sequencenum, users.username
FROM proddata
INNER JOIN users
ON proddata.userid = users.userid
The whole purpose of an SQL database is to read and write data from multiple clients; 100's, 1000's, sometimes millions of them given the right hardware and configuration.
My real concern was less the database itself and the ignition clients all hitting the gateway because the gateway connects to the database. The clients don't have that direct connection unless I'm mistaken about what is happening in the background.
Correct. This is the same architecture that most other platforms use, where it's the server making the database connection rather than the clients.
The main concerns with clients connecting to a database directly are resource overhead on the SQL host, and security.
I wouldn't be concerned with clients executing queries unless it's at an absurd rate
