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

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

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.

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).

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

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.