Getting Unique Values From Database with Scripting

Ignition
,
1

I'm trying to use scripting to get the unique values of a tag from a database. In the Database Query Browser this line works and gets the data I want:
SELECT distinct "RecipeName" FROM WaxPressParts

This doesn't work when I try to add it to scripting for a component:

    queryBase = "SELECT distinct RecipeName FROM WaxPressParts "
    queryClause = "WHERE (CONVERT(varchar(25), t_stamp, 126) >= ? AND CONVERT(varchar(25), t_stamp, 126) < ?)"
    queryText = queryBase + queryClause + " ORDER BY t_stamp DESC"
    self.props.data = system.db.runPrepQuery(queryText)

This gives a source error. Replacing 'distinct' with 'top 1000' gets rid of errors and returns the top 1000 results so I know that the rest of the script is good and 'distinct' is causing the issue. I've also tried 'unique' instead of 'distinct' and am getting the same issue. Is there a good way to get the unique values from a database while scripting on a component?

2

You're not passing any actual parameters in to runPrepQuery? You've got two parameters indicated (?) but runPrepQuery takes an additional argument for those parameters that's not being supplied in your excerpt.

As a tangent, consider using a triple quoted string to avoid unpleasant concatentation:

    query = """
        SELECT distinct RecipeName FROM WaxPressParts 
        WHERE (CONVERT(varchar(25), t_stamp, 126) >= ? AND CONVERT(varchar(25), t_stamp, 126) < ?)
        ORDER BY t_stamp DESC
    """
    self.props.data = system.db.runPrepQuery(queryText)
1 Like
3

I tried the triple quotes and got the same error. I'm modifying an older table that someone else initially set up and that uses the question marks. I tried running the query without the WHERE statement and also got the same error. Given that the top 1000 query worked just fine, I don't think the question mark line is an issue. Is there a good way to trouble shoot the 'unknown source' error?

4

Well, could you paste the actual stacktrace/full details here?

1 Like
5

This is the error:

com.inductiveautomation.ignition.common.script.JythonExecException
Traceback (most recent call last):
  File "<function:runAction>", line 2, in runAction
  File "<custom-method getDies>", line 12, in getDies
            SELECT distinct RecipeName FROM WaxPressParts 
        WHERE (CONVERT(varchar(25), t_stamp, 126) >= ? AND CONVERT(varchar(25), t_stamp, 126) < ?) ORDER BY t_stamp DESC, AutomationData, [2023-08-10%, 2023-08-19%], )

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepQuery(
            SELECT distinct RecipeName FROM WaxPressParts 
        WHERE (CONVERT(varchar(25), t_stamp, 126) >= ? AND CONVERT(varchar(25), t_stamp, 126) < ?) ORDER BY t_stamp DESC, AutomationData, [2023-08-10%, 2023-08-19%], )

	caused by org.python.core.PyException
Traceback (most recent call last):
  File "<function:runAction>", line 2, in runAction
  File "<custom-method getDies>", line 12, in getDies
            SELECT distinct RecipeName FROM WaxPressParts 
        WHERE (CONVERT(varchar(25), t_stamp, 126) >= ? AND CONVERT(varchar(25), t_stamp, 126) < ?) ORDER BY t_stamp DESC, AutomationData, [2023-08-10%, 2023-08-19%], )

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepQuery(
            SELECT distinct RecipeName FROM WaxPressParts 
        WHERE (CONVERT(varchar(25), t_stamp, 126) >= ? AND CONVERT(varchar(25), t_stamp, 126) < ?) ORDER BY t_stamp DESC, AutomationData, [2023-08-10%, 2023-08-19%], )

	caused by Exception: Error executing system.db.runPrepQuery(
            SELECT distinct RecipeName FROM WaxPressParts 
        WHERE (CONVERT(varchar(25), t_stamp, 126) >= ? AND CONVERT(varchar(25), t_stamp, 126) < ?) ORDER BY t_stamp DESC, AutomationData, [2023-08-10%, 2023-08-19%], )
	caused by SQLServerException: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Ignition v8.1.23 (b2022121308)
Java: Azul Systems, Inc. 11.0.16.1
6

SQLServer is telling you what's wrong:

3 Likes