Help with SQL query

I have a transaction group transferring 600 values from a PLC DINT array to a table called “valve_timeout”. Using "SELECT * FROM valve_timeout " I have populated a table with all 600 results. But I only want to display results that are greater than 3. My tags are in the format “Valve_Timeout_0_” to “Valve_Timeout_599_”.

Thanks in advance.

Im not quite sure what you are looking for and need to know what columns are in your table. if you are trying to pull out just the tag values that are greater than 3 try this. replace “value” with whatever the column name is that holds your values.

select * from valve_timeout where value > 3

if you are looking for somethign different let us know.

This is what my SQL table contains:

I have got the results table in ignition to sort of work (for 1 column only) by using:

“SELECT Valve_Timeout_1_ FROM valve_timeout WHERE valve_timeout_1_ > 3”

Based on the data you can see in my 1st image above, I would like to display results from any column (where value inside column > 3) so that my results table looks something like the results below:

I didnt realize you had a column for each tag. you should probably change that to be a row for each tag which will make querying easier. Someone else may be able to help you make what you have work though.

OK Thanks. Is there a setting in transaction groups that I can change to apply it to rows instead of columns?

you would create a new block group. setup your items in order as a block item from 0-600. Make sure you select the option to store row id. if you are storing history to this table then use insert new block under ‘Table action’ and also store your block id. If you are just wanting the latest values I believe you would use update/select the first block. You may want to read up on the block groups as I may be incorrect. its my first time really looking at it.

OK I think it’s pretty much working. I set the table action to “insert changed rows”.

My SQL query is:

“SELECT row_id, Valve_Timeout_0_, t_stamp FROM valve_timeout WHERE valve_timeout_0_ > 3”

This is the result:

Now next question. It won’t let me execute the sql query “DELTEE FROM valve_timeout”. Is there anyway to clear all my data (with a separate button)?

Thanks.

You can do this to add your Tagpath part to the rowid
“SELECT ‘Valve_Timeout_’ + cast(row_id as varchar), Valve_Timeout_0_, t_stamp FROM valve_timeout WHERE valve_timeout_0_ > 3”

“DELTEE FROM valve_timeout”
You misspelled delete

Oops yes I mis-spelt on the forum, when I enter it spelt correctly I get this error:

Exception: Error running query:
SQLQuery(query=delete from valve_timeout, database=MySQL)@5000ms
On: F4 Engineering.Root Container.Button 1.text
	caused by GatewayException: Can not issue data manipulation statements with executeQuery().
	caused by SQLException: Can not issue data manipulation statements with executeQuery().

Ignition v7.6.3 (b2013090513)
Java: Sun Microsystems Inc. 1.6.0_31

post the code that you have on the pushbutton.

For DELETE, you probably need to use system.db.runUpdateQuery instead of system.db.runQuery.

It’s simply “DELETE FROM valve_timeout”… Do I need a ‘WHERE’ clause to make it work? I can execute it in MySQL workbench ok without a WHERE. Does the “Can not issue data manipulation statements with executeQuery” detail in the error message mean Ignition can’t execute delete statements?

Adam, do you mean enter the query into the “update query” field in the property binding? Or it is entered somewhere else…

How are you executing the DELETE command? As diat150 suggests, post your code, please.

What function you use is of essence (that’s why we want to see the code). Check out the following descriptions from the User Manual, for example:

[quote]system.db.runQuery
Description
Runs a SQL query, usually a SELECT query, against a database… [/quote]

[quote]system.db.runUpdateQuery
Description
Runs a query against a database connection, returning the number of rows affected. Typically this is an UPDATE, INSERT, or DELETE query…[/quote]
If you are taking user input that is used in the query, the “Prep” version is better as it sanitizes user input.

OK I have it working, I was entering my code in the wrong place earlier.

Now using:

system.db.runUpdateQuery("DELETE FROM valve_timeout")

in the correct place and it’s working.

Thanks again.