system.db.runPrepUpdate not working for update query, but working for insert query

Ignition
1

I am using below script which is bind to button component. If button clicked, update query is not working.Just for checking I tried insert, that was working fine! Below is my code.

i=self.view.params.row_id
system.db.runPrepUpdate("INSERT INTO drawing_master (name, drawingno, product) VALUES (?,?,?)", [self.parent.parent.getChild("FlexContainer_0").getChild("Name_text").props.text, self.parent.parent.getChild("FlexContainer_0").getChild("drawing_no_text").props.text, self.parent.parent.getChild("FlexContainer_1").getChild("TextField").props.text])
system.db.runPrepUpdate("UPDATE drawing_master SET name = ?, drawingno = ?,product = ? WHERE id = ?",[self.parent.parent.getChild("FlexContainer_0").getChild("Name_text").props.text,self.parent.parent.getChild("FlexContainer_0").getChild("drawing_no_text").props.text,self.parent.parent.getChild("FlexContainer_1").getChild("TextField").props.text,i])

Table data is not getting updated. My DB is MYSQL. Kindly help me to resolve this. I was stuck.

2

Can you verify that 'id' is designated in the database as a primary key constraint?

1 Like
3

Are you getting any errors in the gateway log?

Can you share the table definition for this table?

4

yes It was primary key in my table.also autoincrement

5

There is no error i am facing!
Table Definition:
"CREATE TABLE drawing_master (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
drawingno VARCHAR(50) NOT NULL,
product VARCHAR(100) NOT NULL
);"

6

So no error in the gateway logs?

Is any of the data NULL? You may want to print out all your data to the web browser console using system.perspective.print and verify that you don't have any NULL data that you are trying to update.

Also.. if you can insert but not update with the data, it might be that this

i=self.view.params.row_id

isn't returning the data you think it is. Inspect that data as well in the console.

Side note... rather than using the relative paths, you may want to have your components be bound to custom properties and use those instead. Just in case you decide to move things.

3 Likes
7

Thank you for your input. Now it is working.
Corrected Code:
system.db.runPrepUpdate("UPDATE drawing_master SET name = ?, drawingno = ?,product = ? WHERE id = ?",[self.parent.parent.getChild("FlexContainer_0").getChild("Name_text").props.text,self.parent.parent.getChild("FlexContainer_0").getChild("drawing_no_text").props.text,self.parent.parent.getChild("FlexContainer_1").getChild("TextField").props.text,self.view.params.row_id])

8

If you follow the scheme I explained in answer to your other question, Need to execute the script when it entered the perspective page - #2 by Transistor, your corrected code could be written as,

query = """UPDATE drawing_master 
           SET name = ?, drawingno = ?,product = ? 
           WHERE id = ?"""
values = [self.custom.name,
		  self.custom.drawingno,
		  self.custom.product,
		  self.view.params.row_id
         ]
system.db.runPrepUpdate(query, values)

Which would be more readable and more robust?

1 Like
9

Hey Transistor,

I felt this is more readable and robust one.