How to display the SQL query into LED and Gauge Widget

[AP_10483126]

I am trying to get one SQL query into the LED widget and another SQL query into the Gauge Widget. I am unable to do the bindings from the MSSQL connection thing to the Mashup.

[jensc]

Hello,

Perhaps this guide could help you.

In this guide they describe quite well how to bind data from a service to a widget.

Regards,

Jens

[DanZ]

Did you set a datashape in your service? If not, you can't bind the data because the mashup builder doesn't know how the data is structured.

In addition: It is usually better to use another thing with custom services acting as a "UI-controller" between connector things and the mashup builder. In that way you are able to add error handling or do transformations of the returned results.

[AP_10483126]

Thank you for the inputs .

Please note i have followed the below steps:

1)Created a MSSQL database thing and got all the tables into TWX.

2)Created a query service for a specific row and a column(Single value) Ex: GetPressure( Latest Pressure Value).

3)Execute the Query and created a DataShape of the result as PressureDataShape

4)For the GetPressure service the output table is set as InfoTable and the datashape is mapped to PressureDataShape

5)On the Mashup tried to get the MSSQL Service GetPressure and tried to bind it to a LED Display but it doesnt happen.

What i am doing wrong ?

[DanZ]

Can't you drag and drop the binding to the "Data" element of the widget? Have you tried it alternatively via the "Configure Bindings" popup? For this you have to click on the triangle when you have selected the widget and then on "Configure Bindings".

Or does the binding exist but the values are not displayed? Then you should make sure that the service is triggered.

[AP_10483126]

Yes i was able to bind it but it gives a blank display 

[DanZ]

This looks to me like your service is returning nothing or an undefined value. Is there really an value in the first row of your infotable, specifically in the column you binded?

Have you tried another widget, like a simple label, to display your value?

[AP_10483126]

i tried to put in a label. Yes i am getting blank. What is going wrong ?

[jensc]

Hello,

Could you take a screenshot of your bindings?

Something similar to this:

This could help us understand your bindings and make sure they look correct.

Also, you have made sure that your service is returning your expected values?

If you are using an infotable as your service output, then it is important to bind the rows value and not the "All data" if the targeted binding is not of type "infotable":

If you want to bind to a string for example, you should use in this case the "CostCode" and not the "All Data".

Regards,

Jens

[AP_10483126]

the values are not populating.

[jensc · Accepted solution]

Hello,

What you have in your screenshot is just that the loaded event of your mashup is triggering the "GetProperties" service of your DB connection thing.

You want something similar to this:

Although as previously described, if you only want to use one row, and show it in a LED or Gauge widget, then you can not use an infotable binding and should instead use the row value of the infotable as shown before:

I'm not sure if this "GetProperties" is the out of the box service that many things have. If that is the case, it might not work for what you are trying to do.

Instead you want to make your own service. 

Here are some resources you can use to get you on your way to create your own services:

PTC Help center - Creating, Implementing, and Testing Services

PTC IoT Tips - Implement Services, Events, and Subscriptions

PTC IoT Tips - Working with External Databases 

Hope this helps.

Regards,

Jens

[AP_10483126]

unable to access all the pages in the solution. 

My Requirement is : 

I have created a Database thing which is a MSSQL connection to MS database. Now i  have created a service of SQL query for the Database thing inside the thingworx whose output is a single value result. How can i use this value to a LED mashup . I am unable to create a bind here. 

[jensc]

Hello,

Okay, if you have that then you can follow this PTC help center post: link

It explains how to connect your new service to a widget.

To follow best practices though, you should first create a separate thing for your mashup so as to not use your data layer directly.

It can be like this: Mashup -> UI thing -> Database thing.

The UI thing would call your database thing service that you have created and from there you should be able to follow the PTC help center post.

Regards,

Jens

[AP_10483126]

It can be like this: Mashup -> UI thing -> Database thing.

Where can i get the UI Thing in Mashup?

I have already created a Database Thing. The issue i have created a query service on the database thing but this data is not coming up in the mashup. Is there any documentation on getting a database data into the mashup not as a Grid put a single value row into a LED? 

[AP_10483126]

I get the pressure value when i use a grid. What i need to get this same value into the LED on the left ?

when i map the result to the LED i have a non stop running bar screenshot below.

below are my bindings for pressure and grid

[jensc]

Hello,

What version of TWX are you using? And what widget?

I just did a quick test with the LED widget and it seems to work just fine for me:

Widget:

Service:

Service returns one row with string 55.0

Binding:

Have you set any particular properties on your widget that might cause some issues?

Regards,

Jens

[AP_10483126]

Hi,

I am using ThingWorx 9.3.5-b1258 .I am using a LED display widget. What is LEDTest in your case? In my case GetPressure is a service(SQL query) which has infotable as the output and i am unable to bind it as a data into LED Widget. . The PressureDS is a data shape created from the output of the query. What wrong am i doing ?

[jensc]

Hello,

The LEDTest is just a normal thing service, however I am not using an infotable as output.

But in general it should be pretty much the same as you SQL service.

However, did you name the variable in your data shape exactly the same as the name from the database?

I have found that there can be unexpected behavior unless it is the same.

Your variable in the data shape should also be "pressure", uncapitalized.

The strange part for me is:

Your returned data has a "result" instead of "pressure".

What you could do just to make sure everything works as expected, would be to create a normal thing, and in that a normal service.

Then use a normal string as output:

Like the one I created ^.

But, I do think the issue is that you have named the variable in your data shape as "result" instead of "pressure".

Try it out and get back to us here if you face further issues.

Regards,

Jens

[AP_10483126]

Below is my DS:

Issue is i already have the Database thing created. What is going wrong in the service ? Are the services created correctly ?

This output shows correctly in the grid in masup. What i a missing to get the same value into the LED?

[jensc]

Hello,

Okay, so your data shape and query service looks correct.

Then my biggest confusion is why you have this

In your mashup. The variable in the infotable "All Data" should be named "pressure" and not "result".

I quickly created some tests on my end:

If I were you, I would try to add the service again to the mashup to make sure it is using the correct data shape like you can see mine is.

If you still face issues I would suggest opening a support case with PTC.

Regards,

Jens

: