How do I run a SQL query from a button?

Before you can run your query

Running a Query from a Button

There are several steps involved with query execution on a button press:

Adding Your Button

You can add a button to your window by simply selecting it from the component palette in the designer and then clicking anywhere on your window. There are many different types of events you can add scripting to, but the main event associated with a button press is the actionPerformed event. To add a script to the actionPerfomed event or view all of the events the button supports, right click on the button you added to the window and select "Event Handlers".

The event handlers window provides you with a list of all the events available for the selected component, along with several script generation wizards for common tasks. In the cases where you wish to write your own custom script for an event, you will make use of the script editor tab. Any Python script added to this area will be executed when the event fires. You can find more information about the button component here: Button

Writing Your Script

There are some built in scripting functions that Ignition makes available for you to use when running queries against the database. Each function serves a different purpose, so the function you use will depend on what type of query you need to run and what sort of results you want to get back. The functions listed below are a couple of the functions that you're likely to use the most. Pay close attention to the options provided to you by each of the different functions. Options such as the ability to return auto generated key for insert queries can be extremely helpful and eliminate the need to hit the database multiple times. Examples of each of these functions can be found in their linked topic sections.

Once you determine the function that best suits the query that you want to run you have to add your script to the script editor section of the actionPerformed event for your button.

Example:

name = "John Doe"

results = system.db.runPrepQuery("SELECT * FROM Customers WHERE Name = ?", [name])

Handling Results From SELECT Query

SELECT queries return resultsets in the form of PyDataSets. You can assign these results directly to the data property of a table on your window or any other dataset property you may have on a currently open window. Assigning the PyDataSet to a table will cause the table to refresh and then display the results from your query. The reference to your table (myTable) is unique so make sure you select it from the property browser:

myTable = event.source.parent.getComponent('Table')

myTable.data = results

Merely running a query and then assigning the resulting PyDataSet to a table is really no different than just binding the data property of the table to a SQL Query binding. Much of the time when you run a query in a script it is because you want to examine the resultset in the script and then do some action based on your findings. Python, like many other programming languages, provides looping capabilities that make iterating through a resultset quite easy. There is some basic information about Python Control Flow statements that you should read over as well as the section on dealing with looping through data in PyDataSets. Below is a quick example of looping through a PyDataSet

stmt = "select * from customers"

results = system.db.runPrepQuery(stmt)

#loop through the resultset and print the value in the lastname column

#for each row in the query results

for row in results:

 print row["lastname"]

#loop through the resultset and print each column value for each row

for row in results:

 for column in row:

         print column

The above example is very simple, but it shows how you can use for loops to loop through resultsets. Scripts that you implement in your projects will likely be a lot more complex and it may be difficult at first to determine how exactly they should be written in order to accomplish exactly what you need. These simple loops involving print statements are great for debugging and initial script development. Print statments (when run in the designer or client) get output to the console. You can access the console in the designer by selecting it from the Tools menu. Printing things out as your script is running helps you to see what is actually happening and compare it to what you think should be happening. When you are new to Python and scripting print statements will help you be able to better visualize what is going on and help you get a better grasp on how your script is functioning. Read through the Python section in the user manual starting with the About Python section for a quick tutorial and overview of how Python works.