Are Ignition Clients each their own independent SQL clients for the purposes of client or button scripts which use system.db.runQuery?
I’m testing a project which will read from a MySQL database which has several tables with autoincrement columns. In order to maintain consistency in the DB, I need to get the autoincrement values after each insertion, and then insert those values into some other tables. The MySQL docs indicate that the way to do this is by querying LAST_INSERT_ID(), and that this value is client-independent. So, as long as each individual Ignition client sets up its own connection to the database, and acts as an independent SQL client, there shouldn’t be any issues with having multiple clients making insertions and then attempting to retrieve their autoincrement values.
Can anyone speak to this? Is that how Ignition works?
No, that's not how Ignition works. Database connections are made from the gateway. There's a pool of connections and you don't know which one your query will execute on when you run it.
To accomplish what you want, you should use a function such as system.db.runPrepUpdate that supports the parameter getKey and set it equal to 1 to retrieve your newly created key.
Also, would specifying a transaction ID guarantee that the same client was used for the entire transaction? And if I start a transaction, can I do multiple reads and writes in sequence with python processing, or does the entire transaction get committed to the DB at once?