Update row using button in perspective

General Discussion
, ,
1

Hi Friends,

I’m trying to update the row using the button in perspective. Please can you suggest to me what is worng from my side?

date = self.getSibling(“TextField”).props.text
if date == None:
id = self.view.params.ID
system.db.runPrepUpdate(‘UPDATE sample SET date = now() where id = ?’, [id])

Thanks,
Priyanka

2

what is not working as expected?
should date come from a textfield?
also now() might not work for all databases which are you useing?

1 Like
3

@victordcq Thank you for giving me a quick response. I’m trying to update the row using the button. when I have run the script then not found any error still not updated the row. I’m trying to update the current date when the date IS NULL using the button. I’m using MySQL database. and textfield used for display date column value in column container perspective.

4

could you add in some console log prints to be sure the date is None?
it might also be “” (an empty string) which != None

try use if date == None or date == "":

Altho it might be smart to be even more specific and add in regex to see if the date is in the right format aswell if you want to use a textfield

2 Likes
5

To add to @victordcq answer, you can get away with:

if not date:
    (do something)

None, 0, empty strings, etc. will return false when checked in an if statement. Also, be aware that if x == None can be fooled. The preferred way is to use if x is None

3 Likes
6

I think my issue is the DateTime format. MySQL database DateTime format some think like this →
2021-08-18 13:00:35. textfield DateTime format → " 2021-07-20T10:37:43.000Z" so not update row when run the script.

7

Please can you suggest how to convert correct DateTime format in MySQL using textfiled?

8

Hi @Priyanka.Khandge, if you believe it is a date format issue then you can use system.date.format - Ignition User Manual 8.1 - Ignition Documentation

However, I don’t think this is likely to be your issue, although I could be wrong :man_shrugging:

Also, do you have further code, as you are not updating the time from Ignition, you are doing it in the query in your example.

1 Like
9

its probably easier to convert it in python
system.date.parse

something like
formatedDate= system.date.parse(date, "yyyy-MM-d'T'H:m:s'.'SX")

1 Like
10

Formatting dates before inserting them is always the wrong move.
You’re already using runPrepUpdate, so the correct move would be to use parameterized substitution to insert a date value.
However, as written in your first post, you’re not inserting a date from Ignition at all; you’re using the function now() on the database to generate a timestamp. Ignition’s date formatting is not relevant at all in that case.

3 Likes
11

Slove my problem using system.date.format()

12

You just kicked the problem down the road a bit. As soon as you start working with multiple time zones, this will likely break. Learn to pass dates and strings and numbers through JDBC as parameters. If string formatting is needed to make your queries work, you are doing it wrong.

2 Likes
13

Good point. On that - and sorry to hijack somewhat… - how do you handle timestamps in the PLC? should PLC RTCs all be set to UTC as well as all timestamps written to SQL? (e.g. in unix epoch format)

14

Yes, always use UTC in machinery. AB Logix makes it easy–WallClock currentValue is 64-bit microseconds UTC.

1 Like