I have been working with various versions of Ignition now for a couple of years and I have found many good answers to questions that I have had over the years but now I need to ask a question?
I appreciate all of your expert guidance and suggestions.
Since Ignition uses "named" database connections and database things are typically done using native commands like :
system.db.runQuery (query, dbName)
or
system.db.runUpdateQuery (updateQuery, dbName)
How would I go about doing an INSERT INTO .. (SELECT) type query such as this:
INSERT INTO Database2.TableName(Value1, Value2, Value3)
SELECT FirstValue, SecondValue, ThirdValue FROM Database1.TableName;
If the INSERT INTO SELECT syntax doesn't work in the named query space (which is weird that it doesn't - maybe that is considered a sql script instead of a sql statement), I would use a stored procedure to do this and just call that in the named query
Yes, you can. You just need to make sure you use an UPDATE query type. (The named query editor's "auto" mode is not always accurate for such constructs.)
If scripting, other than with NQs, use one of the system.db.*Update*() functions.
Thank you for the suggestion. I have tried that and it appears that I may have left out a bit of detail in my original question [post]. It appear that my real problem stems from the fact that Database1 is on a remote MySQLserver and Database2 is on the local Ignition MysQLServer.
That query runs fine when all databases (and their tables) are on the same HOST/Server but fails when each one is on a different HOST/Server.
Thank you. I was pretty sure that was going to be the case but I wanted to ask the experts first so I didn't have to spend a lot of time chasing nothing. I'm going to break it into separate script routines and do the read from one, parse it line by lien and write it to the other.. Thank you both for your kind and professional assistance in this matter.