UPDATE Query Syntax issue

Ignition
1

So I started with my SQL Code within the Database Query Browser and I was able to do an update query with no issue :slight_smile: This is just a basic edit row SQL script so I could make sure I got the syntax correct:

UPDATE sensor_notes SET operator = 'admin' WHERE operator = 'rodney'

Then I took this code, and tried to make it dynamic so that the user would be able to edit data within the database based on text fields within the screen. This is what I got:

[code]name = event.source.parent.getComponent(‘Text Field 4’).text
operator = event.source.parent.getComponent(‘Text Field 5’).text
latitude = event.source.parent.getComponent(‘Text Field 2’).text
longitude = event.source.parent.getComponent(‘Text Field 3’).text
description = event.source.parent.getComponent(‘Text Field 1’).text
db = “DB”
updateQuery = “”“UPDATE sensor_data SET name = ?, operator = ?, latitude = ?, longitude = ?, description = ? WHERE name = ?
VALUES (?,?,?,?,?,?)”""

system.db.runPrepUpdate(updateQuery, [name, operator, latitude, longitude, description, name], db)[/code]

Unfortunately, I keep getting an error code :angry:

This is the entirety of the error I keep getting:
Traceback (most recent call last):

File “event:actionPerformed”, line 10, in

VALUES (?,?,?,?,?,?), [Sensor 2, Dave, 12.34, 2.4.68, D2, Sensor 2], DB, , false, false)

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(UPDATE sensor_data SET name = ?, operator = ?, latitude = ?, longitude = ?, description = ? WHERE name = ?

VALUES (?,?,?,?,?,?), [Sensor 2, Dave, 12.34, 2.4.68, D2, Sensor 2], DB, , false, false)

caused by Exception: Error executing system.db.runPrepUpdate(UPDATE sensor_data SET name = ?, operator = ?, latitude = ?, longitude = ?, description = ? WHERE name = ?

VALUES (?,?,?,?,?,?), [Sensor 2, Dave, 12.34, 2.4.68, D2, Sensor 2], DB, , false, false)
caused by GatewayException: SQL error for “UPDATE sensor_data SET name = ?, operator = ?, latitude = ?, longitude = ?, description = ? WHERE name = ?
VALUES (?,?,?,?,?,?)”: No value specified for parameter 7
caused by SQLException: No value specified for parameter 7

Ignition v7.7.5 (b2015071516)
Java: Oracle Corporation 1.8.0_51

Any ideas as to what’s going on? It says no value specified for parameter 7, but I only have 6 parameters there, right? I’m just a tad confused as to what’s going on :scratch:

I’m currently running Version 7.7.5, so I believe that I am up to date on my software as well.

Thanks in Advance for all the help! :smiley:

1 Like
2

Count the number of question marks in your updateQuery statement.

“VALUES (?,?,?,?,?,?)” is not needed.

3

I counted the ?s and there were 6 in the query and in the values, but…

Getting rid of the “VALUES (?,?,?,?,?,?)” Worked!! Thank you so much!

Do you know why it is that I need to use the question marks in some SQL scripts, but not in others? Thanks so much!

4

You are already specifying the values that you want the columns to be inside of your query:

UPDATE sensor_data SET name = ?, operator = ?, latitude = ?, longitude = ?, description = ? WHERE name = ?

Each question mark in the above statement gets replaced with the values in your python list [] when using a prepQuery function. This is how you can make these queries dynamic with code. If you don’t need a dynamic query then you don’t need the ?s at all.

You only have to use the VALUES() statement with an INSERT INTO query, like so:

INSERT INTO table_name (column1, column2, column3, etc...) VALUES(value1, value2, value3, etc...)

5

Did it just happen to work out for me because the names of my columns were also the names of the variables that I had defined in my code? Just want to make sure I know what I’m doing so next time this happens, I can troubleshoot it myself and not have to pester the good people on this forum :wink:

Thank you for the question mark clarification on the INSERT statements as well :slight_smile:

6

[quote=“alewayhar”]I counted the ?s and there were 6 in the query and in the values, but…

Getting rid of the “VALUES (?,?,?,?,?,?)” Worked!! Thank you so much!

Do you know why it is that I need to use the question marks in some SQL scripts, but not in others? Thanks so much![/quote]

When you had VALUES (?, ?, ?, ?, ?, ?) in your updateQuery variable there were a total of 12 question marks.

The question marks are simply a placeholder for values in the system.db.runPrepUpdate function.

The VALUES clause is not used in T-SQL update statements.

Edit:

You are getting mixed up between basic T-SQL syntax and Ignition scripting. The question marks have nothing to do with the actual SQL query.

I recommend becoming very familiar with basic T-SQL statement and syntax outside of Ignition before you go much further. Solidifying these concepts should make things much clearer for you.

T-SQL Update statement

7

Thank you so much for the help! I’m glad to know you references W3. That’s where I’ve been getting a lot of my info from. Thanks again!