Please Note This forum exists for community support for the Mango product family and the Radix IoT Platform. Although Radix IoT employees participate in this forum from time to time, there is no guarantee of a response to anything posted here, nor can Radix IoT, LLC guarantee the accuracy of any information expressed or conveyed. Specific project questions from customers with active support contracts are asked to send requests to support@radixiot.com.
How SQLDS "update statement" example in "point details" works
-
Could anyone successfully work with the "update statement" in SQLDS point details ? Could anyone give me some examples.
Thanks !
-
We've made some improvements to this module and I'll post the help file here:
But here is an example of a simple update statement:
update Batch_Tracking set Stage=? where ID=1234the value that you set the point to is be inserted where the "?" is in your statment.
In the new version of the module we allow for multipul paramaters that can be passed into the point comma separated.
You can get the latest module here: http://store.infiniteautomation.com/module/sqlds
From the help file:
Update statements
An Update statement can optionally be defined for a point, which determines how the point's value can be set in the database. This is a standard SQL statement using "update" or "insert". If this statement is not defined the point will be considered non-settable.
Modify Table Only
Points may be set as table modifiers that allow custom UPDATE or INSERT statements to be created. These statements are parameterized and each parameter must be defined in order and by type. Modify Table Only points are not polled during the usual data source polling, they are only used when a value is set to them. To invoke the statement simply write a formatted string to the point. The format for the String value to write to the point is (1,'string',date). The parameter group is surrounded by parentheses, String parameters must be single quote ' delimited and each parameter must be separated by a comma ,.
The Date Format is the format to use for all date parameters in the statement. This string is parsed using the Simple Date Format, more information can be found here.
Example 1
The statement: update myTable set date=? where name=?
will update all dates in the table named 'myTable' where row values in column named 'name' matches the second parameter. By writing (1999-03-22T12:00:23,'test') all rows with column named 'name' that equals 'test' will be updated with this date.
The Date Format for this example is: yyyy-MM-dd'T'HH:mm:ss
Example 2
The statement: insert into table values (?,?,?)
will insert a new row into the table named 'table'. This table has 3 columns id (INT), name (VARCHAR(10)), and date (DATETIME). By Writing (1,'newName',1999-03-22T12:00:23) one row with id 1 will be inserted.
The Date Format for this example is: yyyy-MM-dd'T'HH:mm:ss
-
Thank you for your information. I will try it out.
