PrepUpdate

Ignition
1

I’m trying to delete all of the data in a table. How do you wild card the arg?

system.db.runPrepUpdate(“DELETE FROM history_pens WHERE GROUP_NAME = ?”, [*])

2

This query doesn’t need a wildcard, if you want to delete every row you can do this:

system.db.runPrepUpdate(“DELETE FROM history_pens”)

You can find out a lot about SQL Queries from places like sqlzoo

3

Tried that as well as other variations with no success. Had to query from the MS enterprise manager to get it to work.

4

I’m glad you got it working. For future reference, the format you are using expects a variable name in the square brackets [] like:

myvariable = "the group name is green" system.db.runPrepUpdate("DELETE FROM history_pens WHERE GROUP_NAME = ?", [myvariable])

5

Tried system.db.runPrepUpdate(“DELETE FROM history_pens”) from Ignition using both MSSQL and Postgres databases without success. It does work when querying from the MSSQL and Postgres query analyzer not from Ignition.

6

runPrepUpdate requires a list of values, even if not holders are in teh query, so you will have to run

system.db.runPrepUpdate("DELETE FROM history_pens",[])

or

system.db.runUpdateQuery("DELETE FROM history_pens")
7

This works. Thanks

system.db.runPrepUpdate(“DELETE FROM history_pens”,[])

8

I’m pretty sure you can fall back to:

system.db.runScalarQuery("DELETE FROM history_pens")
9

No, for DELETES, UPDATES, and INSERTS you should use either:

  1. system.db.runPrepUpdate or
  2. system.db.runUpdateQuery

It is recommended to use runPrepUpdate because:

[quote]1. This method avoids the problematic technique of concatenating user input inside of a query, which can lead to syntax errors, or worse, a nasty security problem called a SQL injection attack. For example, if you have a user-supplied string that is used in a WHERE clause, you use single-quotes to enclose the string to make the query valid. What happens in the user has a single-quote in their text? Your query will fail. Prepared statements are immune to this problem.

2.This is the only way to write an INSERT or UPDATE query that has binary or BLOB data. Using BLOBs can be very hand for storing images or reports in the database, where all clients have access to them.
[/quote]