I have a data set pulled into ignition tag that updates every 24hrs( I don’t have permission to access that DB and this is the only way I can get that dataset). I would like to save the dataset into my DB and update it every 24hrs to reflect the changes that occurred in the last 24 hrs, what would be the best way to do such thing?
I was thinking maybe a procedure that will delete the old table and write the new data, perhaps there is a better way?
Oh the reason I want that data in my DB is to be able to query it and search it as needed, I don’t have the ability to do that with the original source
Move the data into your own database. Use a tag event script to catch the update every 24 hours and script a single transaction that truncates your copy then inserts all of the new records. Provides full access to your database’s query engine for any further actions. Definitely do this if you wish to use joins with other data.
I like the 2nd option best, I have added your plugin but unsure of the syntax. say I wanted to SELECT TOP 1 from the dataset,I created an expression tag with the following code:
view("SELECT TOP 1",{[NoProvider]StoreRoomInventory})
Its from an SQL Query, I Can’t query that DB every time I want execute a new search. The 2nd repose below is more inline of what I need. Thank you for you reply.
The Pseudo-SQL has a strict format – no “TOP n” or “LIMIT n” clause. But you can use a where clause with the row index to get this behavior:
view("SELECT * WHERE _r==0", {[NoProvider]StoreRoomInventory})
However, for this particular case, where you know the row you wish to return, I would use the faster objectScript() expression function to manually construct a dataset with that row. Like so:
objectScript("system.dataset.toDataSet(args[0].columnNames, [[args[0].getValueAt(0, c) for c in range(args[0].columnCount]])", {[NoProvider]StoreRoomInventory})
How would I go about using column names in a query such as this.
SELECT *
FROM StoreRoomInventory
WHERE
ItemDesc LIKE '%{Root Container.Text Field 2.text}%' AND
ItemDesc LIKE '%{Root Container.Text Field 11.text}%' OR
JDENumber LIKE '%{Root Container.Text Field 2.text}%'
ORDER BY LastMoveDate DESC
I don't follow what pyExpression and how the args would work here.
Select
pyExpression,
pyExpression As ColumnName,
pyExpression As "Column Name" ...
Where pyConditionalExpression
Group By pyExpression, pyExpression ...
Pivot pyExpression For pyStringExpression [In pySequenceExpression]
Having pyConditionalExpression
Order By pyExpression, pyExpression ...
Something like this, as long as LastMoveDate really is a date/time datatype in the source:
view("SELECT * WHERE args[0] in ItemDesc and args[1] in ItemDesc or args[0] in JDENumber ORDER BY -LastMoveDate.time",
{[NoProvider]StoreRoomInventory},
{Root Container.Text Field 2.text},
{Root Container.Text Field 11.text})
Note that text field 2 is passed as a single parameter and referenced twice. I’m not sure whether you’ll need parentheses in your where clause: “(x and y) or z” versus “x and (y or z)”. Note that java “Date” datatypes have a “time” property that is milliseconds since the Unix epoch, which can be conveniently negated in python to make a descending order.
So i can get around using the join statement by using using a Custom propriety containing a dataset(1 column), would I be able too look for matching values in my dataset instead if matching one value at a time?
view("SELECT * where args[0] in ItemNumber " ,{StoreRoomInventory},{Root Container.Power Table.data})
You’d have to flip your expression around a bit, and unwrap the 2nd dataset into a list, since the where clause must use python syntax. Something like this:
view("Select * Where ItemNumber in [x[0] for x in system.dataset.toPyDataset(args[0])]",
{StoreRoomInventory},{Root Container.Power Table.data})
Unfortunately, the dataset => list conversion will happen for every row in your inventory, as there’s no understanding of pure functions in the Pseudo-SQL parser.
But, you can optimize this after setting it up by using the Debug logger to show the generated jython, move the ds=>list conversion ahead of the main loop, and put the new code in a project script function called from objectScript() instead.