Inserting Data into a Database

Inserting Data on a Button Press

A common way to insert information into a database is to execute a SQL query after the user presses a Button. The button can run a script to collect the information it needs and then execute a SQL INSERT statement to push that data into the correct Database table. A script executed on the Button's actionPerformed event handler would collect the relevant properties and insert them into a database. The script would look like this:

Pseudocode - Collects Data and Insert into the Database

value1 = {component property reference}
value2 = {tag value}
value3 = {static value}
  
query = "INSERT INTO table (col1, col2, col3) VALUES (?,?,?)"
args = [value1, value2, value3]
system.db.runPrepUpdate(query, args)

Notice that the script isn't limited from taking values from any one place, grabbing tag values and property values and inserting them into a table. It is important to note that using the runPrepUpdate function will require that certain Client Permissions have been enabled.

Example - Inserting Values

Say we have a table called machines in a database with three columns: an id column, a machine_name column, and an area_number column. We can build a query and a script that will insert the data into the database:

1. Create a new Named Query. Set up security to fit your needs and name it appropriately. For more information on creating Named Queries, see Using Named Queries - Example.

   1. Set the Query Type to Update Query.

   2. Create two Value type Parameters.

      1. The first will be a string data type and will be for the name of the machine so it can be called machineName.
      2. The second will be an Int2 data type and will be for the area number so it can be called areaNumber.

   3. Create the insert query.

      SQL - Inserting Values from Components on the Window

      INSERT INTO machines (machine_name, area_number) VALUES (:machineName, :areaNumber)

      

2. On a Main Window, add a Button component, a Text Field component, and a Dropdown Component.

   1. On the Dropdown's Data property, create a dataset that looks like this:

      Value	Label
      1	Area 1
      2	Area 2
      3	Area 3

   2. On the Button's Text property, change the value to something like "Submit".

      

3. Right-click on the Button and select Scripting. Navigate to the Script Editor tab on the actionPerformed Event Handler.

   1. Copy in this script, which will pull in the value from the Dropdown and the Text Field and insert them into the table using the Named Query we built in step 1.
   Python - Insert Values into a Database Table

   # Grab the area number and machine name from the components we added to the window.
   areaNum = event.source.parent.getComponent('Dropdown').selectedValue
   machineName = event.source.parent.getComponent('Text Field').text
   
   # A call to our Named Query, inserting the two parameters using dictionary syntax.
   system.db.runNamedQuery("Insert Data", {"machineName":machineName, "areaNumber":areaNum})

4. Test it out by selecting an area, entering in a machine name, and clicking the submit button. You can check out the new rows using the Database Query Browser to select the values from that table.