I currently have a daily SQL query to drop all rows older than 3 months. Looking to export these rows to a CSV before dropping. Was hoping for something like "DROP TO" that would do both. I haven't been able to find anything about exporting from a query. Would it require a script to run the select query, export to a csv, and then run the drop query?
If you wanted the generic solution, yes.
I'm not familiar with any DROP TO operations in SQL.
Most DBs have some syntax that can select to a file in various formats, where the file is placed in the DB's filesystem, or somewhere it can natively reach. Such operations are technically considered UPDATE queries, as they don't return the result set to the caller. A script that did so, and checked the return value for success, could then call the DELETE query.
If I create an exe that runs command line to export, am I able to call that from an Ignition script?
The exe would be something like
Invoke-Sqlcmd -Query "SELECT * from <database_name>.<schema_name>.<table_name>;" -ServerInstance "<server_instance>" | Export-Csv -Path "file_ destination_path" -NoTypeInformation
That would only work if your Ignition gateway and your DB were the same machine. You aren't doing that, I hope. It would also be tricky to handle errors. I recommend you stick to JDBC calls.
It's a small process with a small budge. Client's specifications. What are the downsides to running Gateway and DB on the same machine?
Ignition has a very different CPU utilization profile from database operations, and the DB will end up dominating in most shared environments. Ignition will then be all over the place on latency. Also true, to a lesser extent, with RAM usage.
This can be mitigated on the same hardware by using VMs and a hypervisor that won't steal the Ignition VM's idle CPUs or RAM to "help" the DB's VM. Commercial hypervisors (looking at you, VMware) make this confusing and difficult and easy to screw up. It is quite easy to set up on Linux with libvirtd and oVirt or virt-manager.
Really, the only way that makes sense to do it is through separate scripting commands. This is the route I went, if anyone else is looking to do something similar. This script is run once every day at 05:00. The select query takes about 4 seconds to return, so there may be a few entries missed, but not a big deal.
today = system.date.format(system.date.now(),"yyyy_MM_dd")
selectStatement = "SELECT * FROM Data WHERE t_stamp < DATEADD(MONTHS, -3, CURRENT_TIMESTAMP)"
oldData = system.db.runQuery(selectStatement, "SQL")
oldDataSet = system.dataset.toDataSet(oldData)
csv = system.dataset.toCSV(oldDataSet)
filePath = "C:\\Old_Data\Data_"+today+".csv"
system.file.writeFile(filePath,csv)
deleteStatement = "DELETE FROM Data WHERE t_stamp < DATEADD(MONTHS, -3, CURRENT_TIMESTAMP)"
system.db.runUpdateQuery(deleteStatement, "SQL")