How to use system.db.PrepUpdate query

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”

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.

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.

Error Code

java.lang.ClassCastException: java.lang.ClassCastException: Cannot coerce value ‘701000973’ into type: class [Ljava.lang.Object;

Yes it is a number and the table column is a varchar(50) MSSQL

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”
‘’’

When using runPrepUpdate, your arguments must be a list.

system.db.runPrepUpdate(query, [arg], "Database')

1 Like

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:

arg = (serialNumber,)
2 Likes

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.

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

arg = (serialNumber,)
system.db.runPrepUpdate(query, arg, 'Database')

or

serialNumber = '701000973'
system.db.runPrepUpdate(query, [serialNumber], 'Database')

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.

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)

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;

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.