Problem with inserting a datetime to SQL server from Ignition button

Ignition
1

Using Ignition 7.9.10 and SQL Server 2014

I am just trying to run this query

system.db.runUpdateQuery("INSERT INTO acd_history(t_stamp, success) VALUES (%s,1)"%(system.date.format(event.source.parent.startTime, "yyyyMMdd HH:mm:ss")))

where event.source.parent.startTime is just system.date.now(). The error I am getting is

Traceback (most recent call last):
  File "<event:actionPerformed>", line 2, in <module>

java.lang.Exception: java.lang.Exception: Error executing system.db.runUpdateQuery(INSERT INTO acd_history(t_stamp, success) VALUES (20190220 10:16:20,1), , , false)

	caused by Exception: Error executing system.db.runUpdateQuery(INSERT INTO acd_history(t_stamp, success) VALUES (20190220 10:16:20,1), , , false)
	caused by GatewayException: SQL error for "INSERT INTO acd_history(t_stamp, success) VALUES (20190220 10:16:20,1)": Incorrect syntax near '10'.
	caused by SQLServerException: Incorrect syntax near '10'.

I’ve tried the date with and without hyphens. I also checked to make sure and t_stamp column is of type datetime. What’s going on?

Edit: So I had to put ’ around the date time. Like this "'"+system.date.format(event.source.parent.startTime, "yyyyMMdd HH:mm:ss")+"'". Is this the way I have to do it?

2

You need quotes around the datetime.

Edit: Just saw your edit. I would put the quotes around the %s to make it look cleaner.

3 Likes
3

Even better is running a prepUpdate: https://docs.inductiveautomation.com/display/DOC79/system.db.runPrepUpdate

The prepUpdate will take care of the quoting, and send the parameter to the JDBC driver as a date/time object. Then the JDBC driver is responsible for making sure the DB understands the date/time.

What you do now is converting the date into a string to a format your SQL DB understands. But that’s DB specific.

system.db.runPrepUpdate("INSERT INTO acd_history(t_stamp, success) VALUES (?,1)", [event.source.parent.startTime])

Letting the JDBC driver take care of the quoting also makes it safer against (accidental or malicious) SQL injection. Though for complete protection against SQL injection, you need to use Named Queries with the parameters.

3 Likes