# How to use system.db.PrepUpdate query **URL:** \ **Category:** Ignition\ **Created:** [February 23, 2018, 2:52pm UTC](https://forum.inductiveautomation.com/t/how-to-use-system-db-prepupdate-query/17110 "2018-02-23T14:52:34Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![tim.marbury](https://avatars.discourse-cdn.com/v4/letter/t/f19dbf/32.png) [@tim.marbury](https://forum.inductiveautomation.com/u/tim.marbury)\ **Post date:** [February 23, 2018, 2:52pm UTC](https://forum.inductiveautomation.com/t/how-to-use-system-db-prepupdate-query/17110/1 "2018-02-23T14:52:34Z")
I need help on a error that I’m getting when trying to run a query on at sql table and then update the data if it returns nothing. serialNumber = (‘701000973’) sqlString = “SELECT SerialNumber FROM SerialTracking WHERE serialNumber = ‘%s’” % (serialNumber) values = system.db.runScalarQuery(sqlString, “Database”) if serialNumber == values: print ‘1’ elif serialNumber != values: query = “INSERT INTO SerialTracking (DateTime,SerialNumber) VALUES (?,?)” arg = (‘NULL’, serialNumber) system.db.runPrepUpdate(query,arg, ‘Database’) else: print “none” ---
**Author:** ![ingraham](https://avatars.discourse-cdn.com/v4/letter/i/3ab097/32.png) [@ingraham](https://forum.inductiveautomation.com/u/ingraham)\ **Post date:** [February 23, 2018, 3:13pm UTC](https://forum.inductiveautomation.com/t/how-to-use-system-db-prepupdate-query/17110/2 "2018-02-23T15:13:51Z")
What error are you getting, at what point in the code? What database? As a guess, is serial number a string or some number format? If it’s a number, the fact that you’re using it as a string is an issue. If serial numbers are in fact strings, the only other thing that jumps out is whether your DateTime field allows NULL. Side note: DateTime is a reserved word in MySQL, but not Sql Server or PostgreSQL. ---
**Author:** ![bfuson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bfuson/32/4597_2.png) [@bfuson](https://forum.inductiveautomation.com/u/bfuson)\ **Post date:** [February 23, 2018, 3:14pm UTC](https://forum.inductiveautomation.com/t/how-to-use-system-db-prepupdate-query/17110/3 "2018-02-23T15:14:55Z")
Can you include the actual error you’re getting? And be sure to wrap code in your posts within blocks like this: [code] some code [/code] or this: ```some code ``` to preserve your formatting. ---
**Author:** ![tim.marbury](https://avatars.discourse-cdn.com/v4/letter/t/f19dbf/32.png) [@tim.marbury](https://forum.inductiveautomation.com/u/tim.marbury)\ **Post date:** [February 23, 2018, 3:17pm UTC](https://forum.inductiveautomation.com/t/how-to-use-system-db-prepupdate-query/17110/4 "2018-02-23T15:17:35Z")
Error Code java.lang.ClassCastException: java.lang.ClassCastException: Cannot coerce value ‘701000973’ into type: class [Ljava.lang.Object; ---
**Author:** ![tim.marbury](https://avatars.discourse-cdn.com/v4/letter/t/f19dbf/32.png) [@tim.marbury](https://forum.inductiveautomation.com/u/tim.marbury)\ **Post date:** [February 23, 2018, 3:18pm UTC](https://forum.inductiveautomation.com/t/how-to-use-system-db-prepupdate-query/17110/5 "2018-02-23T15:18:20Z")
Yes it is a number and the table column is a varchar(50) MSSQL ---
**Author:** ![tim.marbury](https://avatars.discourse-cdn.com/v4/letter/t/f19dbf/32.png) [@tim.marbury](https://forum.inductiveautomation.com/u/tim.marbury)\ **Post date:** [February 23, 2018, 3:29pm UTC](https://forum.inductiveautomation.com/t/how-to-use-system-db-prepupdate-query/17110/6 "2018-02-23T15:29:49Z")
I have also changed it to the following with the same error ‘’’ serialNumber = (‘701000973’) sqlString = “SELECT SerialNumber FROM SerialTracking WHERE serialNumber = (serialNumber)” values = system.db.runScalarQuery(sqlString, "Database’) if serialNumber == values: print ‘1’ elif serialNumber != values: ``` query = "INSERT INTO SerialTracking (SerialNumber) VALUES (?)" arg = (serialNumber) system.db.runPrepUpdate(query,arg, 'Database') ``` else: print “none” ‘’’ ---
**Author:** ![JGJohnson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jgjohnson/32/29582_2.png) [@JGJohnson](https://forum.inductiveautomation.com/u/JGJohnson)\ **Post date:** [February 23, 2018, 3:41pm UTC](https://forum.inductiveautomation.com/t/how-to-use-system-db-prepupdate-query/17110/7 "2018-02-23T15:41:17Z")
When using runPrepUpdate, your arguments must be a list. `system.db.runPrepUpdate(query, [arg], "Database')` ---
**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:** [February 23, 2018, 3:44pm UTC](https://forum.inductiveautomation.com/t/how-to-use-system-db-prepupdate-query/17110/8 "2018-02-23T15:44:20Z")
> [@JGJohnson](#): > > your arguments must be a list Syntax note: parentheses will make a tuple only when at least one comma is present, and a tuple is acceptable as an arg list for runPrepUpdate(). For a tuple of just one element, use this: ```auto arg = (serialNumber,) ``` ---
**Author:** ![tim.marbury](https://avatars.discourse-cdn.com/v4/letter/t/f19dbf/32.png) [@tim.marbury](https://forum.inductiveautomation.com/u/tim.marbury)\ **Post date:** [February 23, 2018, 4:00pm UTC](https://forum.inductiveautomation.com/t/how-to-use-system-db-prepupdate-query/17110/9 "2018-02-23T16:00:51Z")
Changed it to this “”" serialNumber = ‘701000973’ sqlString = “SELECT SerialNumber FROM SerialTracking WHERE serialNumber = (serialNumber)” values = system.db.runScalarQuery(sqlString, “Database”) if serialNumber == values: print ‘1’ elif serialNumber != values: ``` query = "INSERT INTO SerialTracking (SerialNumber) VALUES (?)" arg = (serialNumber,) system.db.runPrepUpdate(query, [arg], 'Database') ``` else: print “none” “”"" And I get this error java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO SerialTracking (SerialNumber) VALUES (?) In all of this I have also tried using system.db.runUpdateQuery, but still get errors. ---
**Author:** ![JGJohnson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jgjohnson/32/29582_2.png) [@JGJohnson](https://forum.inductiveautomation.com/u/JGJohnson)\ **Post date:** [February 23, 2018, 4:04pm UTC](https://forum.inductiveautomation.com/t/how-to-use-system-db-prepupdate-query/17110/10 "2018-02-23T16:04:48Z")
You have to chose one of the methods. Pturmel and I gave two different approaches. I’m not sure mine was correct for the way you had scripted your arg variable. Use either ```auto arg = (serialNumber,) system.db.runPrepUpdate(query, arg, 'Database') ``` or ```auto serialNumber = '701000973' system.db.runPrepUpdate(query, [serialNumber], 'Database') ``` ---
**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:** [February 23, 2018, 4:05pm UTC](https://forum.inductiveautomation.com/t/how-to-use-system-db-prepupdate-query/17110/11 "2018-02-23T16:05:10Z")
You followed both JGJohnson’s suggestion and my suggestion. They were alternatives. What you have now is a string inside a tuple inside a list. You need a string inside either a tuple or a list. ---
**Author:** ![tim.marbury](https://avatars.discourse-cdn.com/v4/letter/t/f19dbf/32.png) [@tim.marbury](https://forum.inductiveautomation.com/u/tim.marbury)\ **Post date:** [February 23, 2018, 4:10pm UTC](https://forum.inductiveautomation.com/t/how-to-use-system-db-prepupdate-query/17110/12 "2018-02-23T16:10:21Z")
Way 1 ‘’‘arg = (serialNumber,) system.db.runPrepUpdate(query, arg, ‘Database’) ‘’’ Gets error java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO SerialTracking (SerialNumber) VALUES (?), [701000973], Database, , false, false) Way 2 ‘’‘serialNumber = ‘701000973’ system.db.runPrepUpdate(query, [serialNumber], ‘Database’)’’’ Gets error java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO SerialTracking (SerialNumber) VALUES (?), [701000973], Database, , false, false) ---
**Author:** ![tim.marbury](https://avatars.discourse-cdn.com/v4/letter/t/f19dbf/32.png) [@tim.marbury](https://forum.inductiveautomation.com/u/tim.marbury)\ **Post date:** [February 23, 2018, 4:56pm UTC](https://forum.inductiveautomation.com/t/how-to-use-system-db-prepupdate-query/17110/13 "2018-02-23T16:56:02Z")
I’ve tried to change to this ‘’’ query =“INSERT INTO SerialTracking (SerialNumber) VALUES (’%s’)” % (serialNumber) system.db.runPrepUpdate(query, ‘Database’) ‘’’ I get a different error message java.lang.ClassCastException: java.lang.ClassCastException: Cannot coerce value ‘Database’ into type: class [Ljava.lang.Object; ---
**Author:** ![JGJohnson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jgjohnson/32/29582_2.png) [@JGJohnson](https://forum.inductiveautomation.com/u/JGJohnson)\ **Post date:** [February 23, 2018, 5:21pm UTC](https://forum.inductiveautomation.com/t/how-to-use-system-db-prepupdate-query/17110/14 "2018-02-23T17:21:48Z")
> [@tim.marbury](#): > > I've tried to change to this > > '''query ="INSERT INTO SerialTracking (SerialNumber) VALUES ('%s')" % (serialNumber) system.db.runPrepUpdate(query, 'Database')''' > > I get a different error messagejava.lang.ClassCastException: java.lang.ClassCastException: Cannot coerce value 'Database' into type: class [Ljava.lang.Object; That's because runPrepUpdate expects the 2nd argument to be an iterable object. I suggest trying to run your SQL insert in the Database Query Browser first, and verify that is a valid query.