SQL query through script

Ignition
1

How to run SQL query in python script using built in function in ignition and syntax
query=“SELECT ID,EQUIPMENT,STREAMFUNCTION,Message FROM Messages
WHERE STREAMFUNCTION LIKE “%S6%” and Reply = 1
ORDER BY ID DESC
LIMIT 3”

2

Probably can’t, because you have used double quotes in your LIKE clause–that isn’t valid. SQL requires single quotes for values. (Make sure queries run in the designer’s query browser to find such problems.)

After you fix that, try the DB scripting functions described in the manual, starting with system.db.runQuery().

3

Okay,
I will do that

4

I tried to run the next query, succesfully:
table = system.db.runQuery("SELECT DISTINCT name FROM users INNER JOIN user_ci ON users.id = user_ci.user_id WHERE date IS NULL AND station_number = 18", "MySQL_DB")

but now I want to do a dynamic query including a STRING VAR (station) instead of 18:
station = 18
table = system.db.runQuery("SELECT DISTINCT name FROM users INNER JOIN user_ci ON users.id = user_ci.user_id WHERE date IS NULL AND station_number = 'station' ", "MySQL_DB")
it runs without any result! How can I do that (station_number is INT and station is STRING).

5

https://docs.inductiveautomation.com/display/DOC81/Named+Queries

https://docs.inductiveautomation.com/display/DOC81/Named+Query+Parameters

Pay attention to the security risks when using QueryStrings.

Tip: use the </> code formatting button to preserve code indentation and apply syntax highlighting. There's an edit button 🖉 below your post so you can fix it.

6

You might try using runPrepQuery which lets you pass arguments to your query:

https://docs.inductiveautomation.com/display/DOC81/system.db.runPrepQuery

HTH.
-Shane

1 Like
7

I really appreciate your quick reply Mr. Shane:
For all people I found at night another way with the same successful result:

table = system.db.runQuery("SELECT DISTINCT name FROM users INNER JOIN user_ci ON users.id = user_ci.user_id WHERE date IS NULL AND station_number = %s"%(station), "MySQL_DB")

I hope it helps a lot of people.

8

Depending on the source of the station variable, you're opening yourself to SQL injection risks. Use a prepared statement. In addition to being safer, it's more performant.

3 Likes