cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Want the oppurtunity to discuss enhancements to PTC products? Join a working group! X

How to connect ThingWorx Edge WebSocket-based MicroServer (WS EMS) to MySQL DB using lua scripting?

thingworxuser
10-Marble

How to connect ThingWorx Edge WebSocket-based MicroServer (WS EMS) to MySQL DB using lua scripting?

Hi,

I need to send data (in JSON format) from MySQL database to EMS. Need to know how do we connect using lua scripts and transfer json data.

Regards & Thanks,
Shiva
8 REPLIES 8

Hi

You'll need to configure the Lua Script Resource to query your database, I had a quick google and Lua Database Access looks quite hopeful.

You'll propably need to create your own handler(s) that run the appropriate SQL queries and provides data to the matching property to then be pushed to ThingWorx. Once you provide data to the property, the EMS will handle the transfer of the data to ThingWorx. If your querying for a table of results, you'd probably have to put that into an InfoTable property but in many cases if you want to be able to log property history etc you're better off transmitting individual properties to ThingWorx than a table.

If you want to query for a table, you could create a remote service with a return type of InfoTable (or JSON, InfoTable will take less work on the platform side). The service runs a query, you'll need to convert the output to an InfoTable and then the result will be passed back to ThingWorx. You could then set up a timer subscription on the platform to query every "n" seconds/minutes/hours.

Regards

Ian

Hi Ian,

Thanks a lot. I am able to establish the connection from EMS to MySQL DB and able to fetch data by Querying in the EMS side.


I am trying to set properties in MeterReading by creating dataShapes as my Database fields,

dataShapes.MeterReading(

  { name = "VMId", baseType = "STRING", aspects = {defaultValue="Unknown"} },

  { name = "Name", baseType = "STRING", aspects = {defaultValue="Unknown"} }

)

And then I am populating above created dataShape and converting to infotable to as to access on Thingworx server side as below in lua script,

cursor,errorString = conn:execute([[select * from vmdetails]])

print(cursor,errorString)

row = cursor:fetch ({}, "a")

while row do

    print(string.format("VMId: %s, Name: %s", row.vmid, row.vmname))

    row = cursor:fetch (row, "a")

end

-- close everything

cursor:close()

conn:close()

env:close()

-- Setting Properties

services.GetMeterReading = function(row)

  tw_mutex.lock()

  local row = DataShape.MeterReading:clone()

  tw_mutex.unlock()

  local it = tw_infotable.createInfoTable(row)

 

  local success, err = it:addRow({

  

  })

if err then return 400, err end

  return 200, row

end

I am unable to set the properties as Infotable from above script. Can I know is there a simple way to set these database properties and populate to the dataShape I created and then view on Thingworx side.


Thanks,

Shiva

Regards & Thanks,
Shiva

Shivaraj, is there a reason why you chose to use the EMS to connect to your MySQL DB? We have alternatives, like our Edge ADO Service, that you can use to connect to your DB. The Help Center link I've linked has more information on how to connect and use the ADO Service. You can download the ADO service from the ThingWorx Edge SDK section at the eSupport portal.

We intend to use Thingworx Utilities. Some part of the utilities(especially software content management) is not fully supported by Edge SDK. So we decided to go with EMS.

Regards & Thanks,
Shiva

Hi Aanjan, Please let me know if ThingWorx-Software-Content-Management supports with Edge SDK (other Utilities such as File transfer are working with Edge SDK).

Regards & Thanks,
Shiva

Are you looking for any specific features? I can check locally and see what's on the road map; as you noted we currently have file transfer and tunneling.

We wanted to package and deploy our sample software using Edge Java SDK 6.0. But Software-Content-Management isn't supporting in it.

Regards & Thanks,
Shiva

Edge WebSocket-based MicroServer (WS EMS) is supported with Software-Content-Management  (we are able to package and deploy). But we are unable to set properties as JSON to Thingworx server using WS EMS. So started using Edge Java SDK.

Regards & Thanks,
Shiva
Top Tags