# RunPrepQuery Issue **URL:** \ **Category:** Ignition\ **Created:** [September 12, 2019, 8:12pm UTC](https://forum.inductiveautomation.com/t/runprepquery-issue/28228 "2019-09-12T20:12:17Z")\ **Posts on this page:** 17\ **Page:** 1
**Author:** ![esmarks](https://avatars.discourse-cdn.com/v4/letter/e/e47774/32.png) [@esmarks](https://forum.inductiveautomation.com/u/esmarks)\ **Post date:** [September 12, 2019, 8:12pm UTC](https://forum.inductiveautomation.com/t/runprepquery-issue/28228/1 "2019-09-12T20:12:17Z")
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])` ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [September 12, 2019, 8:28pm UTC](https://forum.inductiveautomation.com/t/runprepquery-issue/28228/2 "2019-09-12T20:28:38Z")
What are you setting your date variables to? These should just be standard Java dates, e.g. `system.date.now()` ---
**Author:** ![esmarks](https://avatars.discourse-cdn.com/v4/letter/e/e47774/32.png) [@esmarks](https://forum.inductiveautomation.com/u/esmarks)\ **Post date:** [September 12, 2019, 8:33pm UTC](https://forum.inductiveautomation.com/t/runprepquery-issue/28228/3 "2019-09-12T20:33:51Z")
It is a script that runs on sunday so it is the following: ```auto PreviousSunday = system.date.addDays(system.date.now(), -7) PreviousFriday = system.date.addDays(PreviousSunday, 5) ``` ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [September 12, 2019, 8:37pm UTC](https://forum.inductiveautomation.com/t/runprepquery-issue/28228/4 "2019-09-12T20:37:48Z")
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? ---
**Author:** ![esmarks](https://avatars.discourse-cdn.com/v4/letter/e/e47774/32.png) [@esmarks](https://forum.inductiveautomation.com/u/esmarks)\ **Post date:** [September 12, 2019, 8:45pm UTC](https://forum.inductiveautomation.com/t/runprepquery-issue/28228/5 "2019-09-12T20:45:09Z")
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. ```auto WARN: Error invoking script.Traceback (most recent call last): File "", 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], ) ``` ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [September 12, 2019, 8:49pm UTC](https://forum.inductiveautomation.com/t/runprepquery-issue/28228/6 "2019-09-12T20:49:41Z")
> [@esmarks](#): > > Cell = CD06-01H That should have single quotes around it right? 'CD06-01H' ---
**Author:** ![esmarks](https://avatars.discourse-cdn.com/v4/letter/e/e47774/32.png) [@esmarks](https://forum.inductiveautomation.com/u/esmarks)\ **Post date:** [September 12, 2019, 8:52pm UTC](https://forum.inductiveautomation.com/t/runprepquery-issue/28228/7 "2019-09-12T20:52:10Z")
I also apologize for that because I have tried everything that I can think. Having the quotes doesnt help ```auto WARN: Error invoking script.Traceback (most recent call last): File "", 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], ) ``` ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [September 12, 2019, 8:55pm UTC](https://forum.inductiveautomation.com/t/runprepquery-issue/28228/8 "2019-09-12T20:55:35Z")
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 ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [September 12, 2019, 9:04pm UTC](https://forum.inductiveautomation.com/t/runprepquery-issue/28228/9 "2019-09-12T21:04:14Z")
There may be a more complete exception recorded in the gateway log, if not available in the error popup. Full error details would help. ---
**Author:** ![esmarks](https://avatars.discourse-cdn.com/v4/letter/e/e47774/32.png) [@esmarks](https://forum.inductiveautomation.com/u/esmarks)\ **Post date:** [September 12, 2019, 9:05pm UTC](https://forum.inductiveautomation.com/t/runprepquery-issue/28228/10 "2019-09-12T21:05:52Z")
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. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [September 12, 2019, 9:11pm UTC](https://forum.inductiveautomation.com/t/runprepquery-issue/28228/11 "2019-09-12T21:11:19Z")
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. ---
**Author:** ![esmarks](https://avatars.discourse-cdn.com/v4/letter/e/e47774/32.png) [@esmarks](https://forum.inductiveautomation.com/u/esmarks)\ **Post date:** [September 13, 2019, 4:13pm UTC](https://forum.inductiveautomation.com/t/runprepquery-issue/28228/12 "2019-09-13T16:13:17Z")
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. ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [September 13, 2019, 7:14pm UTC](https://forum.inductiveautomation.com/t/runprepquery-issue/28228/13 "2019-09-13T19:14:00Z")
If you are only interested in ValueAt(0,0)… try change your query to a scalarPrepQuery ---
**Author:** ![esmarks](https://avatars.discourse-cdn.com/v4/letter/e/e47774/32.png) [@esmarks](https://forum.inductiveautomation.com/u/esmarks)\ **Post date:** [September 13, 2019, 8:04pm UTC](https://forum.inductiveautomation.com/t/runprepquery-issue/28228/14 "2019-09-13T20:04:56Z")
Thank you for that tip. Do you know how to convert the nonetype value that it returns to something I can do math on? ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [September 13, 2019, 8:14pm UTC](https://forum.inductiveautomation.com/t/runprepquery-issue/28228/15 "2019-09-13T20:14:43Z")
`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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [September 14, 2019, 2:18pm UTC](https://forum.inductiveautomation.com/t/runprepquery-issue/28228/16 "2019-09-14T14:18:20Z")
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. ---
**Author:** ![michael.ruel](https://avatars.discourse-cdn.com/v4/letter/m/ecd19e/32.png) [@michael.ruel](https://forum.inductiveautomation.com/u/michael.ruel)\ **Post date:** [September 18, 2019, 4:01pm UTC](https://forum.inductiveautomation.com/t/runprepquery-issue/28228/17 "2019-09-18T16:01:02Z")
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.