How to read the mysql data to the generic thing properties in the Thingworx (9.3.0) ?

[NH_shree]

Hello,

I have the Thing which is connected to the mysql database and I am able to retrieve the data to the thingworx mashup by writing the services in the database thing.

 

So now i need to get the parameters value of the mysql database table in the generic thing where the properties .are there

 

instead of getting the data from the database thing by fetching the data through the services , can we get the parameters values into thing properties which should be updating every time when the new data is logged in the database.

If anyone have tried this, please help me to sort out this issue.

Regards,

Shree

[Velkumar]

Hi @NH_shree 

I don't think we can get new logged data events/triggers from DB.

Maybe based on your requirement you can create a scheduler and update data from the Database to Thing Property

/VR

[NH_shree]

Hi @Velkumar ,

Right now i am able to get the data updating on the mashup but it is not the historical data which will store in the query property history because i have the thing which is directly fetching the data from the database through the sql queries written in the services .

I need to get the historical data which will be also stored in the thing properties so that the thing can be used in the vuforia  AR /VR experience , so how can i get the mysql data to the generic thing template properties ? 

Regards,

Shree

[Velkumar]

Hi @NH_shree 

What do you mean by you want to store historical data in Thing property?

Do you want to store all rows from DB to store in the Thing property or only the latest value should be stored in the Thing property?

/VR

[NH_shree]

Hi @Velkumar 

I want to store all rows from the Database to store in the Thing Property.

Regards,

Shree

[NH_shree]

Hi @Velkumar 

In my Database Thing i am not having any properties in the thing as you can see the below image , hope you can get to know what i am trying to tell 

only the inherited properties are available because it is connected to the database, there is no properties other than that 

if i need to store and display the values in the properties  for example if i have temperature value in database and if i try to add the temperature in the properties and alerts tab then can i get the temperature column values from db to the properties added in the same thing ?

Regards,

Shree

[Velkumar]

Hi @NH_shree 

First thing storing all data from DB to Thing Property as InfoTable is like duplicating data which is not recommended. If you want to show all rows in your Vuforia App, you can directly call DB service in Vuforia Studio and bind it to Vuforia Widgets 

Based on your example: You can create Temperature Property in DB Thing or in another Thing. 

Based on Scheduler / Timer event, query data from DB and update your property value (PFB Image for sample code)

This way you can configure an alert for Temperature property and you can display last value in Vuforia Studio

/VR

[NH_shree]

Hello @Velkumar ,

I have written some services in the generic thing service tab where now i am able to get the data in the infotable and i have assigned it to the properties also , but values are not getting updated in the properties tab but once if i execute the service then at that time of execution it is getting updated

what could be done ? if you have tried this kind of method please let me know

the service which i am used to bring from the database thing to the genereic thing is

/ result: INFOTABLE dataShape: "extrudertool"
let result = Things["MYSQLHOSTINGERDBCONNECTION"].extrudertool();

if (result !== undefined && result.rows.length > 0) {
var row = result.rows[0]; // Get the first row of the INFOTABLE

// Update the properties in the "Thing:IntegratedSmartFoundry" with the retrieved values
Things["IntegratedSmartFoundry"].Bed_Temperature = row.Bed_Temperature;
Things["IntegratedSmartFoundry"].Extruder_0_Filament_Run_Out = row.Extruder_0_Filament_Run_Out;
}

Regards,

Shree 

[Velkumar · Accepted solution]

Hi @NH_shree 

As I said earlier use Timer / Scheduler Event (based on your requirement) to execute the update script.

For example: If you want to update property value every 5 mins

1. Create a TimerThing with Timer Template 

2. Set the update rate to 300000 milliseconds (5 mins)

3. Create a subscription in Timer Thing with Timer Event

4. Call propertyUpdateScript in Subscription

So property value will be updated every 5 mins

Note: Fetching data from DB with a low update rate (say every second or 2) will cause performance issues. 

/VR