INSERT INTO first row to the top

Ignition
1

Hi all,
I’m fairly new to Ignition and especially to tables and SQL.
I’m using the script below on a boolean tag to insert data from a string into a new row of a table but I’d like to have the new row always added to the top. It works but the new row is always added to the bottom.
Thanks!

if currentValue.value == 1:
	query = "INSERT INTO mytable (firstcolumn) VALUES (?)"
	args = [system.tag.read("[.]stringvalue").value]
	db = 'PostgreSQL'
	system.db.runPrepUpdate(query, args, db)
2

An interesting thing about SQL is that there isn’t a guaranteed order unless you specify one with an ORDER BY clause. How are you viewing your table? Can you show the query that fetches the data from the databse?

3

SQL doesn’t really work that way. Tables are ‘SETS’ of data. In other words they are unordered, unless you do something to force the order. This is known as an ORDER BY clause, and it is the only way to guarantee that results are returned in any specific order.

Don’t worry about the order when inserting rows, only when returning them from the DB

1 Like
4

Thanks! I’ll try the ORDER BY clause

5

So where would I use the ORDER BY clause for a table that I’m displaying on a window?
I tried adding it in another query to the same boolean tag that inserts the row but it doesn’t work.
Works if I setup a Named Query but the order changes only when testing it, doesn’t change to the table on my window.

6

It would get added to the SELECT query that populates the table.

7

In the SQL Query Binding of the Data property for the table.

SELECT OrderedColumn,Column2,Column3
FROM myTable
--WHERE CLAUSE would go here
ORDER BY OrderedColumn
8

I figured it out, thanks for the help.
I have another thing I’ve never done before, how do I reference or use the value of a tag into a SQL query?

9

That depends on a few things:

  1. What the tag value represents?
  2. How you are calling the query (Binding, Named Query, Scripting, some combination)?
  3. What datatype is the column?

The basic idea is you create a query with parameters and you set those parameters to the value of the tag.

For the most basic SQL Query Binding you would write your query something like this:

SELECT orderedColumn,conditionedColumn
FROM myTable
WHERE conditionedColumn = {[myTagProvider]myTagPath}
ORDER BY orderedColumn

You can easily insert a tag path by clicking on the ‘Insert Tag’ button to the right of the Query Field.

image
image786×594 42.7 KB

Also, don’t forget to set your database connection.

10

One warning on SQL Query Bindings: They only support curly-brace tag and property substitution, which works by stringifying the value and letting your DB decipher everything. That means strings must have quotes outside the curly braces and may not contain quotes or anything that will confuse the DB’s parser. (This is how SQL injection attacks work.)

Use named queries if at all possible, with value parameters. No quoting required, and no restrictions on string contents, as all value parameters are passed in native form through the JDBC connection. (Same handling as scripted “Prep” queries, but those aren’t available in bindings.)

3 Likes
11

Thanks all for the help. I figured it out and it works well

12

Worth noting ORDER BY orderedColumn is shorthand for ORDER BY orderedColumn ASC (ascending) and if you want to reverse the order you’d say ORDER BY orderedColumn DESC (descending)

1 Like