Updating a value in SQL table using where clause

Ignition
1

Hi Guys,

I have to write value to a cell in SQL table when a button is pressed in Igntion Screen.

I am trying to write SQL query in button’s actionperformed function using

queryValue = event.source.parent.getComponent(‘Numeric Label’).value
system.db.runUpdateQuery(“UPDATE TestDB SET ComponentRemQuantity = %s” % (queryValue), ‘SQLExpress’)

But this updates the value in all the rows of that particular column and i am not able to use a WHERE clause with this query also.

Am i doing something wrong or is there any other way to do this?

Thanks in Advance…!!

2

A WHERE clause is appropriate here. Are you getting an error. SQL is applying the change to all rows because the WHERE clause is missing.https://www.w3schools.com/sql/sql_where.asp

3

Should be:

queryValue = event.source.parent.getComponent(‘Numeric Label’).value
RecordID = event.source.parent.getComponent(‘RecordID’).value
databaseConnection = "SQLExpress"
system.db.runPrepUpdate(`"UPDATE TestDB SET ComponentRemQuantity = ? WHERE tblIDField = ? )", [queryValue, RecordID], databaseConnection)

Replace the RecordID/tblIDField with the value of the record you are trying to update unique identifier.

4

If you truly are trying to update an entire table, your database may have a default SAFE_MODE type function that prevents this. I know MySQL by default has SAFE_MODE on and would not allow you to do an update like that, SQL Express may have similar.

5

If this is the case, then your only option is to update the entire table. But I suspect that this isn't the case..

6

yes i got an error with a where clause, but may be because i was trying to use it in the next line than runUpdateQuery command.

7

Thanks for that suggestion… but i got somewhere near i guess. Now i am using the code:

queryValue = event.source.parent.getComponent(‘Numeric Label’).value
value1=event.source.parent.getComponent(‘Label 2’).text
value2=event.source.parent.getComponent(‘Label 3’).text

system.db.runUpdateQuery("UPDATE TestDB SET ComponentRemQuantity = %s WHERE ComponentNumber=‘value1’ and OrderName=‘value2’ " % (queryValue), ‘SQLExpress’)

The problem here is, the query is not working when i am using ComponentNumber=‘value1’ and OrderName=‘value2’ whereas
if i use a direct string value like ComponentNumber=‘ABCD’ and OrderName=‘XYZ’ it works.

Does this mean i can’t use an indirect string value in the runUpdateQuery where clause?? I don’t think so.

8

You’re using %s to substitute one variable, you need to use the same for the other two variables. At the moment you’re using fixed strings for your where clause values.
The format, which is standard python, is:
‘%s %s %s’ % (1,2,3)

Or otherwise, you can use @MMaynard 's suggestion and use runPrepQuery instead, which is far easier to pass variables into as it does all the type conversions for you. I.e. You don’t have to worry about encasing strings in quotes, formatting dates, etc.

3 Likes
9

I will checkout both those cases. Thanks :slight_smile: