I try to use system.db.runPrepQuery and add a 'GROUP BY' CLAUSE, script without group by works fine.
Without group by
data['a'] = system.db.runPrepQuery("SELECT AREA, DATEPART(week, CREATED), STATUS_TEXT FROM TABLE WHERE STATUS_TEXT = 'Open' AND DATEPART(week, CREATED) LIKE ? ", [param] )
With GROUP BY
param = '%' + param + '%'
group = 'GROUP BY AREA, STATUS_TEXT'
data['a'] = system.db.runPrepQuery("SELECT AREA, DATEPART(week, CREATED), STATUS_TEXT FROM TABLE WHERE STATUS_TEXT = 'Open' AND DATEPART(week, CREATED) LIKE ? ", [param], [group] )
error:
com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 32, in updateData at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepQuery(AbstractDBUtilities.java:302) at jdk.internal.reflect.GeneratedMethodAccessor402.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepQuery(SELECT COUNT(AREA), AREA, DATEPART(week, CREATED), STATUS_TEXT FROM TABLE WHERE STATUS_TEXT = 'Open' AND DATEPART(week, CREATED) LIKE ? , db.user.name, [%26%GROUP BY AREA, STATUS_TEXT], )
First thing is your params should be in one list together like system.db.runPrepQuery("SELECT * FROM someTable WHERE param1=? AND param2=?",[param1, param2])
Second thing is since your group by statement is hardcoded GROUP BY AREA, STATUS_TEXT I don’t see any reason to include it as a param instead of just hardcoding it into your query.
Params are represented by ? in your query and get substituted/sanitized for you, you don’t have a ? correspodning to where the group clause would go, but again, I think since that never changes it’s a misuse of params to inject it like that.
Try
data['a'] = system.db.runPrepQuery("SELECT AREA, DATEPART(week, CREATED), STATUS_TEXT FROM TABLE WHERE STATUS_TEXT = 'Open' AND DATEPART(week, CREATED) LIKE ? GROUP BY AREA, STATUS_TEXT", [param])
Does this query (that you’re adapting for use in Ignition) run fine in SSMS (I’m assuming SQL Server based on DATEPART function)? I imagine you might run into a complaint about your GROUP BY complaining about DATEPART(week, CREATED) being invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
As @bkarabinchak.psi pointed out, you can't substitute schema objects in prepared statements. That means any (Table Names, Column names, etc...). That isn't what is causing this error though (I'm guess as you haven't shared the entire stack trace). More likely is the fact that you are providing [group] to the datasource parameter of the function, which I am guessing isn't going to work. See @bkarabinchak.psi post for the correct structure when calling runPrepQuery().
If you do need the GROUP BY clause to be dynamic, this can be accomplished using a Named Query with a QueryString parameter and calling system.db.runNamedQuery(). All SQL Injection risks apply here so do not allow for user input into the query string parameter.
