Hello Forum, can anyone please help me understand how to get a runprepquery to work with passing in variables. I am just passing in standard dates, and I have tried hard writing in the dates and it still wont work.
result = system.db.runPrepQuery("Select SUM(HoursProd) FROM CD06_I4 Where Cell = CD06-01H AND (DateColumn Between ? and ? )",[PreviousSunday,PreviousFriday])
What are you setting your date variables to? These should just be standard Java dates, e.g. system.date.now()
It is a script that runs on sunday so it is the following:
PreviousSunday = system.date.addDays(system.date.now(), -7)
PreviousFriday = system.date.addDays(PreviousSunday, 5)
What is the error you’re getting? I’ve had issues writing to MS sql server datetime2 fields due to ignition using an older version of the jdbc driver. Updating it to the latest resolved my issues.
Also, are you sure you just haven’t spelt “datecolumn” wrong?
Sorry for that typo up there, I simplified it for the forum and that wasnt quite what I was trying to do. I am trying to run this within a report.
WARN: Error invoking script.Traceback (most recent call last):
File "<function:updateData>", line 18, in updateData
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepQuery(AbstractDBUtilities.java:287)
at sun.reflect.GeneratedMethodAccessor237.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepQuery(Select SUM(HoursProd) FROM CD06_I4 Where Cell = CD06-01H AND (Date Between ? and ? ), , [19/09/05, 19/09/10], )
That should have single quotes around it right? 'CD06-01H'
I also apologize for that because I have tried everything that I can think. Having the quotes doesnt help
WARN: Error invoking script.Traceback (most recent call last):
File "<function:updateData>", line 18, in updateData
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepQuery(AbstractDBUtilities.java:287)
at sun.reflect.GeneratedMethodAccessor237.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepQuery(Select SUM(HoursProd) FROM CD06_I4 Where Cell = 'CD06-01H' AND (Date Between ? and ? ), , [19/09/05, 19/09/10], )
What database are you using?
Can you execute that query, minus the date clause, directly e.g. Using the database browser in ignition? And can you execute it with the date clause, adding properly formatted dates?
Those date that are being passed in to me don’t look right, they’re missing the time components, but that could just be how the error handler is formatting them
There may be a more complete exception recorded in the gateway log, if not available in the error popup. Full error details would help.
It is Microsoft SQL and yes I can get this query to work without an issue without this prepquery and yes that appears to just be the error handler.
Out of interest, can you try using square brackets around your date column in the where clause? [Date] between... I doubt that’s the issue, but worth a try.
Thank you for your help nick, the problem was for some reason the date kept changing so I had to force it with system.date.format(PreviousSunday ,"yyyy/MM/dd" ). Another question though is with the results from a prepquery how do you do math?
results = result.getValueAt(0, 0)
AverageProd =(results / 5) /3
If I try to do this I get :
TypeError: unsupported operand type(s) for /: 'NoneType' and 'int'
But I am able to store result.getValueAt(0, 0) and use it as a variable that I display in a bar chart.
If you are only interested in ValueAt(0,0)… try change your query to a scalarPrepQuery
Thank you for that tip. Do you know how to convert the nonetype value that it returns to something I can do math on?
NoneType means that your query isn’t returning anything - Python’s None is the absence of a value, equivalent to null in most other programming languages.
In this case, with the SUM() aggregate function, it is in fact returning an actual NULL, which is what you get if the SUM() function is fed nothing but NULL rows, or no rows at all.
@esmarks, consider returning DateColumn and ProdHours (instead of summing) to verify the rest of your query is giving the rows you expect.
You’re date format needs to match the column date format. The default Date format in MS SQL is YYYY-MM-DD. Using the BETWEEN you need to have the search date in #01/07/1996# for the / format or ‘1996-01-20’ for the default format.