Inserting Data into a Database

A common way to insert information into a database is to run a script after a user presses a Button component that will collect data and execute a Named Query to push that data into a database table. Using a script allows values to be collected from tag values and property values as needed to insert into a table. The following syntax shows how this script can be structured.

Pseudocode - Collect 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)

It is important to note that if you choose to use the runPrepUpdate function in Vision, you'll be required to enable certain Client Permissions.

Inserting Values on a Button Press Example

This example demonstrates how to insert data into a database for both Vision and Perspective using the database table and Named Query created in the steps below:

1. Open the Database Query Browser from the top navigation bar under Tools.

2. Create a machines table in a database with a machine_name and an area_number column. This example uses the following statement for the Default database.

   Create Table

   CREATE TABLE machines (
     machine_name varchar(255),
     area_number int
   );

3. Open the Named Queries workspace from the Project Browser.

4. Enter Insert Values into the New Query name field.

5. Select Update Query.

6. Select the Authoring tab.

7. Update the two default Value type Parameters.

   1. Rename the first value to machineName and leave the data type as String.
   2. Rename the second value to areaNumber and change the data type to Int2.

8. Enter the following Named Query statement:

   SQL - Insert Values

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

   

Refer to the Vision and Perspective sections for the respective steps to build out a window or view for the rest of this example.

Vision

1. On a Main window, add a Button component, a Text Field component, and a Dropdown component.

2. On the Dropdown's data property, create a dataset that contains the following values and labels:

   Value	Label
   1	Area 1
   2	Area 2
   3	Area 3

3. Change the Button's text property to Submit.

   

4. Right-click on the Button and select Scripting.

5. Navigate to the Script Editor tab under Event Handlers > action > actionPerformed.

6. Enter the following script, which will pull in the values from the Dropdown and the Text Field, and then insert them into the table using the Insert Values Named Query created above.

   Python - Button actionPerformed Script

   # 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.execUpdate("Insert Values", {"machineName":machineName, "areaNumber":areaNum})

Verify functionality by placing the Designer in Preview mode, selecting an area, entering in a machine name, and clicking Submit. Open the Database Query Browser and execute a SELECT query to see the new row.

Perspective

1. Drag a Button component, a Text Field component, and a Dropdown component onto a view.

2. On the Dropdown's options property, add three array elements to enter the following value/label pairs:

   • options.0
     • value: 1
     • label: Area 1
   • options.1
     • value: 2
     • label: Area 2
   • options.2
     • value: 3
     • label: Area 3

3. Change the Button's text property to Submit.

   

4. Right-click on the Button and select Configure Events....

5. Select Component Events > onActionPerformed.

6. Click the Add icon and select Script.

7. Enter the following script, which will pull in the values from the Dropdown and the Text Field, and then insert them into the table using the Insert Values Named Query created above.

   Python - Button actionPerformed Script

   # Grab the area number and machine name from the components we added to the window.
   areaNum = self.getSibling("Dropdown").props.value
   machineName = self.getSibling("TextField").props.text
   
   # A call to our Named Query, inserting the two parameters using dictionary syntax.
   system.db.execUpdate("Insert Values", {"machineName":machineName, "areaNumber":areaNum})

Verify functionality by placing the Designer in Preview mode, selecting an area, entering in a machine name, and clicking Submit. Open the Database Query Browser and execute a SELECT query to see the new row.