Database update syntax

Ignition
1

Greetings

I am very new at ignition.

What I am trying to do is update a table with data that I have placed in variables.

The syntax for update to the database is basically correct but I’m not sure how I get the variables into the where clause.

There don’t seem to be any examples of this in the help.

Just wondering if anyone could help. I suspect that it all to do with quotes etc…

Here is the syntax of my update.

All the variables have had data assigned to them sensor1-4 are floats the rest are strings.

system.db.runUpdateQuery ("UPDATE tempwheeldiameter SET sensor1 = ln_sensor1, sensor2 = ln_sensor2, sensor3 = ln_sensor3, sensor4 = ln_sensor4 WHERE WHEEL_RFID == ls_rfid AND OPERATORCODE == ls_operatorcode AND RADIALLOCATION == slidervalue AND WHEELMODEL == ls_assembly")

This is the error message:

Traceback (most recent call last):

File “event:propertyChange”, line 38, in

java.lang.Exception: java.lang.Exception: Error executing system.db.runUpdateQuery(UPDATE tempwheeldiameter SET sensor1 = ln_sensor1, sensor2 = ln_sensor2, sensor3 = ln_sensor3, sensor4 = ln_sensor4 WHERE WHEEL_RFID == ls_rfid AND OPERATORCODE == ls_operatorcode AND RADIALLOCATION == slidervalue AND WHEELMODEL == ls_assembly, , , false)

caused by Exception: Error executing system.db.runUpdateQuery(UPDATE tempwheeldiameter SET sensor1 = ln_sensor1, sensor2 = ln_sensor2, sensor3 = ln_sensor3, sensor4 = ln_sensor4 WHERE WHEEL_RFID == ls_rfid AND OPERATORCODE == ls_operatorcode AND RADIALLOCATION == slidervalue AND WHEELMODEL == ls_assembly, , , false)
caused by GatewayException: SQL error for "UPDATE tempwheeldiameter SET sensor1 = ln_sensor1, sensor2 = ln_sensor2, sensor3 = ln_sensor3, sensor4 = ln_sensor4 WHERE WHEEL_RFID == ls_rfid AND OPERATORCODE == ls_operatorcode AND RADIALLOCATION == slidervalue AND WHEELMODEL == ls_assembly": You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '== ls_rfid AND OPERATORCODE == ls_operatorcode AND RADIALLOCATION == slidervalue' at line 1
caused by MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '== ls_rfid AND OPERATORCODE == ls_operatorcode AND RADIALLOCATION == slidervalue' at line 1

Ignition v7.5.6 (b1317)
Java: Sun Microsystems Inc. 1.6.0_31

I think I may be close to success with this issue.

Ive added the following code and I am not getting errors. The table is not being updated but that is likely to be a data issue.

ls_querystring = "UPDATE tempwheeldiameter SET "
ls_querystring = ls_querystring + " sensor1 = " +  str(ln_sensor1) 
ls_querystring = ls_querystring + ", sensor2 = " +  str(ln_sensor2) 
ls_querystring = ls_querystring + ", sensor3 = " +  str(ln_sensor3) 
ls_querystring = ls_querystring + ", sensor4 = " +  str(ln_sensor4) 
ls_querystring = ls_querystring + " WHERE WHEEL_RFID = " + "'" + ls_rfid + "'"
ls_querystring = ls_querystring + " AND OPERATORCODE = " + "'" + ls_operatorcode + "'"
ls_querystring = ls_querystring + " AND RADIALLOCATION = " + "'" + str(slidervalue) + "'"
ls_querystring = ls_querystring + " AND WHEELMODEL = " + "'" + ls_assembly + "'"
system.gui.messageBox(ls_querystring)
system.db.runUpdateQuery(ls_querystring)
2

Your latest method is nearly there but is a bit clumsy. You should think about using string formatting:ls_querystring = """ UPDATE tempwheeldiameter SET sensor1 = %f, sensor2 = %f, sensor3 = %f, sensor4 = %f WHERE WHEEL_RFID = '%s' AND OPERATORCODE = '%s' AND RADIALLOCATION = '%s' AND WHEELMODEL = '%s' """ % (ln_sensor1, ln_sensor2, ln_sensor3, ln_sensor4, ls_rfid, ls_operatorcode, str(slidervalue), ls_assembly)Note the single quotes around string values. The triple quotes allow you to enter a string across multiple lines.

3

Here are the Manual pages with examples:
http://www.inductiveautomation.com/support/usermanuals/ignition/index.html?system_db_runupdatequery.htm
http://www.inductiveautomation.com/support/usermanuals/ignition/index.html?system_db_runprepupdate.htm