I have set up tag historian partitioning, but now I would like to query the most recent data table. I can easily SELECT * FROM sqlt_data_1_2021_10, but now it is November, so I need sqlt_data_1_2021_11. These partition names are all stored in the sqlth_partitions table.
To get the latest partition table name I use: SELECT pname FROM sqlth_partitions ORDER BY start_time DESC LIMIT 1
Essentially, I want to use: SELECT * FROM (SELECT pname FROM sqlth_partitions ORDER BY start_time DESC LIMIT 1)
The only way I can think to do this is a dynamic SQL query using the PREPARE statement. Is this even possible in Ignition? How would I do so? I would like to avoid making a stored procedure in the database itself.
Yes, you can always dynamically make SQL. The thing you must consider is the context in which you are doing it and if you are opening yourself up to a SQL injection attack by doing so.
You could dynamically make it like
curDate = system.date.now()
year = system.date.getYear(curDate)
month = system.date.getMonth(curDate)
query = " SELECT * FROM sqlt_data_1_%i_%i"%(year, month)
and then run your query.
Now if you are doing this in a new vision client this might require you to turn ON legacy DB Access - if that is the case, don’t do it. Put the logic I just gave you in a gateway message handler, so that a vision client has to ask the gateway for the dataset. Doing dynamic SQL on the gateway, while it can get ugly, is at least safe. Doing dynamic SQL on a vision client requires extra access to the database that can be a security issue throughout your application. Dynamic SQL in Perspective is actually ok because it all runs on the gateway anyways.
@bkarabinchak.psi did a good explanation of how to do it but I’d be curious why your doing it instead of using the built in historian functions to pull data. Personally to avoid the complexity of querying like this, I use the built in functions even if I need to manipulate it using a script after the data is retrieved.
If you are dynamically making SQL in a script yo uare most likely going to use results = system.db.runQuery(someQuery) to get the results and do something with them.
I think I actually may have figured a way around it using a formatting expression to format the end date into the sqlt_data string that I need and pass that to a named query.