Add row to table in SQL Database

Ignition
1

I have a button within a program that adds a row to a table in the program after data has been entered into corresponding text fields, and the code looks like this:

[code]string = event.source.parent.getComponent(‘Group’).getComponent(‘Text Field’).text
string2 = event.source.parent.getComponent(‘Group’).getComponent(‘Text Field 1’).text
string3 = event.source.parent.getComponent(‘Group’).getComponent(‘Text Field 3’).text
string4 = event.source.parent.getComponent(‘Group’).getComponent(‘Text Field 2’).text
string5 = event.source.parent.getComponent(‘Group’).getComponent(‘Text Area’).text
number = event.source.parent.getComponent(‘Group’).getComponent(‘Numeric Text Field’).intValue
table = event.source.parent.getComponent(‘Table’)

newRow = [string, string2, number, string3, string4, string5]

table.data = system.dataset.addRow(table.data, newRow)

event.source.parent.getComponent(‘Group’).getComponent(‘Text Field’).text = ‘’
event.source.parent.getComponent(‘Group’).getComponent(‘Text Field 1’).text = ‘’
event.source.parent.getComponent(‘Group’).getComponent(‘Text Field 2’).text = ‘’
event.source.parent.getComponent(‘Group’).getComponent(‘Text Field 3’).text = ‘’
event.source.parent.getComponent(‘Group’).getComponent(‘Text Area’).text = ‘’
event.source.parent.getComponent(‘Group’).getComponent(‘Numeric Text Field’).intValue = 0[/code]

Is there a way that I can add this code to a table within a table in a database? My thought was then I would just be able to tie the table to a DB query and the table would update like it currently does, but with a SQL table, I would be able to manipulate the data easier in order to say sort and display data using a dropdown menu.

So bottom line question is can i add rows to a SQL table the same way I add a row to a table within ignition?

2

Bind your ignition table ‘Data’ property to a database table using an SQL Query.
Then you can add rows to the database table with the code below.
The ‘system.db.refresh’ function will update your ignition table.

string = event.source.parent.getComponent('Group').getComponent('Text Field').text
string2 = event.source.parent.getComponent('Group').getComponent('Text Field 1').text
string3 = event.source.parent.getComponent('Group').getComponent('Text Field 3').text
string4 = event.source.parent.getComponent('Group').getComponent('Text Field 2').text
string5 = event.source.parent.getComponent('Group').getComponent('Text Area').text
number = event.source.parent.getComponent('Group').getComponent('Numeric Text Field').intValue
table = event.source.parent.getComponent('Table')

db = "MyDatabase"

# Create the insert query
insertQuery = """INSERT INTO [%s] ([string], [string2], [string3], [string4], [string5], [number]) 
				 VALUES ('%s', '%s', '%s', '%s', '%s', %d)""" % (tableName, string, string2, string3, string4, string5, number)

# Insert the record in the database table
system.db.runUpdateQuery(insertQuery,db)

# Refresh the table data
system.db.refresh(table, "data")
3

In the line of code that says

db = “MyDatabase”

the “MyDatabase” portion is going to be the name of the Database I’m using, correct? So if my Database is DB, the code would look like

db = DB

Also, in line 12 there is a variable named tableName that is not previously defined in the code. I’m assuming thats the name of the tablename, but where is it defined from?

Thanks :slight_smile: :slight_smile:

4

Yes, you’re correct.
Change “MyDatabase” to the name of the database you are using.

tableName is the name of your table in the database.
to define it you will need to add this line below where the database name is defined:
tableName = “YourTableName”

This assumes you have a database connection set up in the gateway.

5

script error
script error1062×514 63.7 KB

I tried to add some values into my database. But got some errors.

However on searching the forum, i got to see your post and some steps.

I followed the same, but I am getting error as per the picture attached.

Dont understand where exactly i am making a mistake.

Initially i tried to do as per Inductive University video. That didnt work and so i tried your script.

Both gave me the same error message.

I think something i am missing in my computer to do these scripting.

As i am new to iginition, python and stuff, i am not able to track the issue.

Below is the script I have used. I have 3 text field and the datas in the text field have to be added in the database when i press an button,

MY SCRIPT

idtag = event.source.parent.getComponent(‘container1’).getComponent(‘bee1’).text
tagvalue = event.source.parent.getComponent(‘container1’).getComponent(‘bee2’).text
tagname = event.source.parent.getComponent(‘container1’).getComponent(‘bee3’).text
table = event.source.parent.getComponent(‘Table’)

db = “tagvalue”

Create the insert query

insertQuery = “”“INSERT INTO [%s] ([idtag], [tagvalue], [tagname])
VALUES (’%s’, ‘%s’, ‘%s’, ‘%s’, ‘%s’, %d)”"" % (tableName, idtag, tagvalue, tagname)

Insert the record in the database table

system.db.runUpdateQuery(insertQuery,db)

Refresh the table data

system.db.refresh(table, “data”)

6

Based on the error this is the line that is causing issues. Verify the component path is correct by using the built in property browser:
image

From the popup navigate to the bee1 component and see if the path changes from what you have.

1 Like
7

Hi,

While i browsed through the internal property browser, i receive the following,

idtag = event.source.parent.getComponent(‘bee1’).text
tagvalue = event.source.parent.getComponent(‘bee2’).text
tagname = event.source.parent.getComponent(‘bee3’).text

table = event.source.parent.getComponent(‘Table’)

db = “tagvalue”

insertQuery = “”“INSERT INTO [%s] ([idtag], [tagvalue], [tagname])
VALUES (’%s’, ‘%s’, ‘%s’)”"" % (db, idtag, tagvalue, tagname)

system.db.runUpdateQuery(insertQuery, db)

system.db.refresh(table, “data”)

However now i am getting an error as per the picture attached

script error2
script error21920×1080 70.4 KB

8

Try enabling the Legacy Database Access in the project properties.

image
image583×519 40.3 KB

1 Like
9

Now i get a new error as attached :thinking: :thinking:

script error3
script error31151×470 42.9 KB

10

What is your database Named as in Gateway ? ‘tagvalue’ ?

1 Like
11

ok got it. think i am confused with table name and database name… :mask: :mask:

My database name in the gateway is “MySQL” and the table inside is called “Tagvalue”

I am sure there is a small error which i am doing and not able to point it out.

Still After Changing the correct database name i am getting an error

12

can anyone support me.

13

I don’t know what ignition version you are using, but I rather use system.db.runPrepUpdate instead. It’s quite simple… take a look here first: https://docs.inductiveautomation.com/display/DOC79/system.db.runPrepUpdate

14

Hi I am using Ignition 8.0.12 and MySQL 8.0.

Thanks for the link. I shall check and update back

15

Try this:
This snippet assumes you have a MySQL database called MySQL on your gateway. Also assumes you have a table called Training with the columns idtag, tagvalue and tagname

idtag = event.source.parent.getComponent(‘bee1’).text
tagvalue = event.source.parent.getComponent(‘bee2’).text
tagname = event.source.parent.getComponent(‘bee3’).text

table = event.source.parent.getComponent(‘Table’)

insertQuery = "INSERT INTO Training (idtag, tagvalue, tagname) VALUES (?,?,?)"
args = [idtag, tagvalue, tagname]

system.db.runPrepUpdate(insertQuery, args, database='MySQL')

system.db.refresh(table, 'data')
1 Like
16

Hi @code_skinn,

i got the script working as per the instructions in your post. however i am having some doubts, can you support me with that.

I have attached some photos below namely as per order below are,

  1. Add Button Script
  2. Connection 1
  3. Connection 2
  4. Vision Window DataSource
  5. MySQL Workbench

Add Button Script
Add Button Script1920×1080 289 KB

Connection 1
Connection 11920×939 71.3 KB

connection 2
connection 21920×935 42.1 KB

vision window Datasource
vision window Datasource1920×1080 266 KB

mysql workbench
mysql workbench1920×1080 124 KB

If you see in the connection 1 picture, i am linking to a database called Training and my connection name is only MySQL.

If you see in the Datasource picture, I see my table is under a database called MySQL (Which is not the case as per my connection properties)

As per my MySQL Workbench, the tagvalue Table is Under the Database Training.

Having all these, I dont find a connectivity between the names in my Connection configuration, Vision Window and MySql. I Dont see a through flow in this.

Kindly support me with a clear picture for me to understand better.

MAYBE I KEPT DOING SOMETHING AS IT WAS NOT WORKING AND NOW I ENDED UP LIKE THIS

17

Just my two cents as I'm not used to ignition 8

  • as you have only one databse, can you try it without the database argument?

system.db.runPrepUpdate(insertQuery, args)

  • maybe the table is case senstive? tagvalue instead of Tagvalue

Best of luck

1 Like
18

thats fine @LSimao , I got it working through the post of @code_skin codes

i have my table names in smaller case only and i don’t think they are case sensitive

19

I think I understand the confusion.

The pictures you have shared provide the precise flow that you are looking for.

In Connection 1, notice that you create a datasource with the name “Mysql”. In the connect URL, you are connecting to your MySQL database (training).

If you were to rename your datasource to “training” it might clarify a few things for you.

1 Like
20

@KGarner, thanks for the post. Yeah now i understood the connection name is the one which is shown on the vision window component datasource

1 Like