Skip to main content
M
MM_96654281-Visitor
1-Visitor
October 23, 2020
Solved

store the SQL result in a Datatable entity

  • October 23, 2020
  • 1 reply
  • 3412 views

Hi, hoping you're doing just fine..

 

I'm pretty new with thingworx and I need to develop mashups that read/write data from/to SQLServer and handle some of that data to do custom reports

 

So, I have some troubles to store sql results in a database entity to use in the example mashup that I've been working

 

what I did was:

 

- Create a test data shape with the sql table structure:Test_Data_Shape.PNG

- Create a test data table with the adding the shapes I just create:

 

Test_Data_Table.PNG

 

- Create a Database Thing that make the connection to SQL server and in a javascript do the query.... you'll se the results down (I also tryied to set the test data shape in the output and nothing)

 

 

Thing_SQLServer_query.PNG

 

 

 

- In the database thing I also added properties

 

Thing_SQLServer_properties.PNG

 

From there I'm lost, I don;t know how to set the properties with the data returned

 

Can you help me with some code to base on, so I can store the values in the properties and display in a widget (can be a grid for start) in the mashup?

 

I've been looking for some complete examples and until now I haven't found any...

 

Best Regards,

Marcos

Best answer by jman87

@MM_9665428 

This is one demo I have done:

1. Create a database thing (in my demo, the name is ACMESQLDatabase ) as you have done

2. Create a services  GetAllRecords:

  1. Select SQL (Query).
  2. Type SELECT * FROM SampleTable in the Script text field.

3. Refer below steps to create the related database based the outputs of GetAllRecords services:

  1. Click the Test button for the GetAllRecords service.
  2. Click the Execute Query button.
  3. Click the + DataShape  button to create datashape:

    4. Type SQLGetAllRecordsData in the Name field and save the datashape (the properties in Filed Definitions will be generated automatically based on the sql result)

4. Configure the GetAllRecords service to return an InfoTable that uses the new DataShape as below:

  2020-10-25_22-01-29.jpg

 

5. Creating a Mashup to View Data from the Service as below:

     

  1. Task 1. Create a responsive mashup.

    1. Type +M in the spotlight search field of the Classic Composer.
    2. Select +Mashup to create a mashup with name SQLMashup

  2. Task 2. Add a layout with one row and a header.

    1. Type La in the Filter Widgets field.
    2. Drag a Layout widget into the mashup.
    3. Select a Vertical layout.
    4. Select the Header check box.
    5. Click the Rows drop-down list to expand it.
    6. Select 1.
    7. Click the Done button.

  3. Task 3. Retrieve data from the GetAllRecords service and add it to the mashup.

    1. Click the Add entity 
       

       icon in the Data panel.

    2. Type Acmes in the Search entities field.
    3. Select ACMESQLDatabase.
    4. Type Geta in the Filter field.
    5. Click the Add 
       
       icon for the GetAllRecords service.
    6. Select the Mashup Loaded? check box.
    7. Click the Done button.

  4. Task 4. Load the service data into a Grid widget.

    1. Type G in the Filter Widgets field.
    2. Drag a Grid widget from the Widgets panel to the bottom row of the layout area.
    3. Drag All Data from the Data panel to the grid.
    4. Select Data as the Binding Target.

  5. Task 5. Rename the grid columns.

    1. Click the upper-left corner of the grid in the layout to expand a drop-down list.
    2. Select Configure Grid Columns.
    3. Type Text in the Column Title field and press TAB.
    4. Click the NumberField link.
    5. Type Number in the Column Title field and press TAB.
    6. Click the BooleanField link.
    7. Type Yes/No in the Column Title field and press TAB.
    8. Click the DateField link.
    9. Type Date and Time in the Column Title field.
    10. Click the Done button.

2020-10-25_22-09-10.jpg

6. Click the View Mashup button of the mashup and check the data values.

7. You could also create other services for inserting records, updating records with the related sql commands and then control the actions in the mashup. For example as below:

2020-10-25_22-18-13.jpg

 

 

Best Wishes!

Teresa

1 reply

J
jman8715-MoonstoneAnswer
15-Moonstone
October 25, 2020

@MM_9665428 

This is one demo I have done:

1. Create a database thing (in my demo, the name is ACMESQLDatabase ) as you have done

2. Create a services  GetAllRecords:

  1. Select SQL (Query).
  2. Type SELECT * FROM SampleTable in the Script text field.

3. Refer below steps to create the related database based the outputs of GetAllRecords services:

  1. Click the Test button for the GetAllRecords service.
  2. Click the Execute Query button.
  3. Click the + DataShape  button to create datashape:

    4. Type SQLGetAllRecordsData in the Name field and save the datashape (the properties in Filed Definitions will be generated automatically based on the sql result)

4. Configure the GetAllRecords service to return an InfoTable that uses the new DataShape as below:

  2020-10-25_22-01-29.jpg

 

5. Creating a Mashup to View Data from the Service as below:

     

  1. Task 1. Create a responsive mashup.

    1. Type +M in the spotlight search field of the Classic Composer.
    2. Select +Mashup to create a mashup with name SQLMashup

  2. Task 2. Add a layout with one row and a header.

    1. Type La in the Filter Widgets field.
    2. Drag a Layout widget into the mashup.
    3. Select a Vertical layout.
    4. Select the Header check box.
    5. Click the Rows drop-down list to expand it.
    6. Select 1.
    7. Click the Done button.

  3. Task 3. Retrieve data from the GetAllRecords service and add it to the mashup.

    1. Click the Add entity 
       

       icon in the Data panel.

    2. Type Acmes in the Search entities field.
    3. Select ACMESQLDatabase.
    4. Type Geta in the Filter field.
    5. Click the Add 
       
       icon for the GetAllRecords service.
    6. Select the Mashup Loaded? check box.
    7. Click the Done button.

  4. Task 4. Load the service data into a Grid widget.

    1. Type G in the Filter Widgets field.
    2. Drag a Grid widget from the Widgets panel to the bottom row of the layout area.
    3. Drag All Data from the Data panel to the grid.
    4. Select Data as the Binding Target.

  5. Task 5. Rename the grid columns.

    1. Click the upper-left corner of the grid in the layout to expand a drop-down list.
    2. Select Configure Grid Columns.
    3. Type Text in the Column Title field and press TAB.
    4. Click the NumberField link.
    5. Type Number in the Column Title field and press TAB.
    6. Click the BooleanField link.
    7. Type Yes/No in the Column Title field and press TAB.
    8. Click the DateField link.
    9. Type Date and Time in the Column Title field.
    10. Click the Done button.

2020-10-25_22-09-10.jpg

6. Click the View Mashup button of the mashup and check the data values.

7. You could also create other services for inserting records, updating records with the related sql commands and then control the actions in the mashup. For example as below:

2020-10-25_22-18-13.jpg

 

 

Best Wishes!

Teresa

    M
    MM_96654281-VisitorAuthor
    1-Visitor
    October 26, 2020

    Thanks a lot Teresa.... Your recommedations helped me a lot......

     

    A few more of questions

     

    Test_SQL_query_results.PNG

     

    1) How do I hide or remove the top tool bar?

     

    2) Do I need other services if I need data to fill a List or a Graph?

     

    3) Is there a site where I can find examples just like yours?

     

    4) Can you share your full code example with me? I'm interested to test the insert / update / delete records as you mentioned...

     

    Best Regards,

    Marcos

     

    J
    jman8715-Moonstone
    15-Moonstone
    October 29, 2020

    @MM_9665428 

     

    Please refer below:

    1) How do I hide or remove the top tool bar?

    There are two ways to hide the debugging tool bar on Mashup

    1. Remove "&__fromNextGen=xxx" part from URL on browser and reload a Mashup page
    2. Press Ctrl+Alt+F9 on a Mashup page

    2) Do I need other services if I need data to fill a List or a Graph?

    The demo I provided is using one services to fill the grid. Whether it need other services it depends on what data you would like to fill a List or Graph.

    For example as below I still use the same service which I have used for Grid to fill the list with the Number got from the service:

     

    2020-10-29_16-26-42.jpg

     

    2020-10-29_16-27-35.jpg

    3) Is there a site where I can find examples just like yours?

    We have PTCU (PTC university ) for some demo, but it need to purchase to access it. You may also refer Help >Help Center in the right top of thingworx UI page.

    4) Can you share your full code example with me? I'm interested to test the insert / update / delete records as you mentioned...:

     

    Insert code demo as below based on my previous post example scenario for you:

    2020-10-29_16-33-24.jpg

     

     

    Best Wishes!

    Teresa

    Terms & ConditionsCookie settingsAccessibility statement