Hi All,
I am working on Batch process and has db tables. on button click i am storing the data in the Batchinfo table except actual amount and Endtime. When the condition of actual amount meet, i need to store the data of Actual amount and end time in the same row at which i saved the data on button click.
I tried with update query but it is not working and it is adding new row of data as actual amount and endtime instead of on same row.
Thanks.
Hello,
Just to make things clear:
You have a table that looks like
data | actual_amount | end_time
On click, you insert a row that contains only data.
Then, at some point based on a tag change, you need to update that row with actual_amount and end_time.
What do you use to identify the row to update ?
Couldn’t you wait until the tag change and push everything at once ?
Hi pascal,
My requirement is to update the data. so my target amount comes first when clicked and then actual amount starts to increase slowly. When actual amount reached target amount, at that instance we need to send the data in table in that same where my target amount is stored.
I am sharing clip of table.
*identifying the row with batchID.
What does your query look like that is adding a new row instead of updating an existing a new one?
Why do you want to add the actual amount if it’s only ever added when it is equal to target amount ? You’ll have 2 columns containing the exact same data.
On your issue, I’d expect a runPrepUpdate() to do the work.
Can you share your queries ?
Hi,
below script is for adding actual amount.
| Actual = system.tag.readBlocking(’[default]FB_process/Recipe/Actual_Amount’)[0] |
|---|
| a = Actual.value |
| system.db.runUpdateQuery(INSERT INTO BatchInfo (ActualAmount) + VALUES (’%f’) %(a)) |
and other one is i am trying with endtime.
Endtime = system.date.now()
query = “UPDATE BatchInfo SET %d = ?” % (EndTime)
arg = [Endtime]
system.db.runPrepUpdate(query, arg)
i am trying two different script but both are not working.
As i am running batch 22 so it should send the data to row 22 of table like in above table.
One thing I notice is that your update statement does not have a WHERE clause, so that “UPDATE BatchInfo SET %d = ?” % (EndTime) will update every single row in the table every time it runs (if its even allowed to run, for example if MySQL SQL_SAFE_MODE=1, then you would be denied from running such a statement).
Also
Endtime = system.date.now()
query = “UPDATE BatchInfo SET %d = ?” % (EndTime)
Would become
query = "UPDATE BatchiInfo SET theCurrentdateTime=?
which is not what I imagine you want, since your column names are not going to be changing on every second to the current datetime.
Ultimately you probably want something like
query = "UPDATE BatchiInfo SET Endtime=? WHERE BatchID = ?
If you really do want the full table updated, try this instead
Endtime = system.date.now()
query = “UPDATE BatchInfo SET EndTime = ?”
values = [Endtime]
system.db.runPrepUpdate(query, values)
I don’t think those queries are right. INSERT INTO will insert a new row, not update an existing one.
And, as @bkarabinchak.psi noted, the formatting looks a bit wonky.
I’m guessing you want something like this:
query = "update BatchInfo set ActualAmount=?, EndTime=? where BatchId=?"
args = [
system.tag.readBlocking(’[default]FB_process/Recipe/Actual_Amount’)[0].getValue(),
system.date.now(),
batch_id
]
system.db.runPrepUpdate(query, args)
Thanks pascal.
It worked lke what i wanted only where condition is missing which i have added and that satisfied my overall process.
Right, I forgot the where clause…
Glad it works.