DELETE all the Records of a SQL Table

Ignition
1

Hi,

I trying to delete the records of one SQL Table,

I have a MEMORY TAG, when I add the SCRIPT on "Value Changed",
I added the following Script:

system.db.runPrepUpdate("DELETE FROM TANK1")

this Script can not delete the SQL Database, looks like the "system.db.runPrepUpdate" doesn't work,

Could you please let me know what will be the proper script to delete ALL the records of the SQL Table?

2

I highly recommend NOT using a tag value change event to do a SQL table deletion.

In general, don't do anything that interacts with the DB in a tag value change event. You risk locking up your entire tag system. Instead, use a Client Tag Change Event or Gateway Tag Change Event.

runPrepUpdate has required arguments you must provide. In the gateway context, you must provide the statement, list of values for placeholders, and the target database. In your case you have only provided one of these(the query).

2 Likes
3

Also, TRUNCATE is significantly faster to delete all rows in a table if you don't need a filter.

2 Likes
4

Also also, it is perfectly fine to run this query in a tag change script if you run it by the store and forward system e.g.

system.db.runSFPrepUpdate('TRUNCATE TABLE tank1')

(I also suggest that you follow the IA Exchange styleguide for table naming conventions - i.e. avoid capital letters as these table names are far more difficult to deal with in some databases like PostgreSQL. It's far easier to conform to a baseline so if you ever need to use another DB, it's not such a pain to refactor all of your queries. Unfortunately, the default audit log config goes completely against this and uses all caps...)