How to invoke maria sql query using python system.db.runPrepQuery?

Ignition
1

Hi All
I am trying to invoke maria db sql query via python system.db.runPrepQuery but the query is a little more complex than usually. The query runs fine in maria db and it has 2 global parameters at the beginning which define start and end time needed for the query.

I tried the syntax below which does not work and error is pointing at the very first line and parameter that cannot be assigned - any ideas how to fix that? In particular how to pass or wrap in quotes the global parameters. Example of the function is below:

def RetrieveDataFromMaria(Params):
SQLQuery =
“”"
SET @StartTime = DATE_FORMAT(str(?),’%Y-%m-%d %H:%i:%s’);
SET @EndTime= DATE_FORMAT(str(?),’%Y-%m-%d %H:%i:%s’);
SELECT SOME SQL CODE HERE WHICH DOES NOT MATTER
“”"
resultsPyDataSet= system.db.runPrepQuery(SQLQuery,Params)
resultsDataSet = system.dataset.toDataSet(resultsPyDataSet)

return resultsDataSet

2

Strictly speaking, that’s not a SQL Query. That is a SQL script composed of three statements. JDBC drivers are only required to support single statements at a time. A few support multi-statement scripts. MariaDB and MySQL were not among them, last I checked.

Use a stored procedure.

Edit: Actually, use a named query with named parameters. That way you can use parameters passed in once in multiple places. You can also avoid converting date objects to strings (that way be dragons).

The SQL code that “does not matter” actually does matter.

3 Likes
3

Thank you so much for answering my question i am just about to try to use named query. I am intrigued by your comment when you mentioned that JDBC drivers are required to support single statement at the time. I am failing to grasp what does it actually mean in practice - any chance you elaborate on this a little bit?

4

This is 3 SQL statements (2 variable set statements and 1 Select statement), so you cannot run in system.db.runQuery or PrepQuery because JDBC drivers only supports one statement at a time. This is what he meant.

You're definitely better off just making a named query, put your dates as parameters in your where clause (or wherever you need them), and feed them with runPrepQuery - let ignition handle inserting dates into your SQL for you (and all variables for that matter) as it will properly handle all the escaping and formatting for you behind the scenes.

1 Like
5

Setting variables is not part of the SQL standard, and different DB brands use different syntax. JDBC, as a cross-brand standard, has to stick to what is common across all brands.

If you want to be sure your code is robust when future you changes DBs, use single statements. (Also, use double-quotes for identifiers instead of brand-specific punctuation.)

1 Like