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

Community Tip - When posting, your subject should be specific and summarize your question. Here are some additional tips on asking a great question. X

Running SQL queries on Thingworx data?

tyadav
1-Newbie

Running SQL queries on Thingworx data?

Hi,

This is my shorter version of another similar post in which I was looking for more of the theoretical explanations but here I just want to focus on the implementation part of it.

For. eg: If I have a car which has speed, distance and tire pressure as its properties, then how can I use the MsSQL to query the different properties and a combination of these?

Thanks!

7 REPLIES 7
PaiChung
22-Sapphire I
(To:tyadav)

Thingworx doesn't directly support MS SQL, if you are using MS SQL as the persistence provider, you could write queries against the schema using a separate JDBC connection.

Else within Thingworx use QueryDataTableEntries / QueryStreamEntriesWithData etc

Here is a description of how to create the Query object

PTC

Hi Pai Chung,

Thanks for the reply. Yes, I am using MSsql as the persistence provider.

Is there an example available as to how to write queries using JDBC connection? I thought that the SQL querying can be done by creating a SQL (query) based service using the Relational DB Connector extension. Is it not a correct way?

It would be really helpful if you can give an example of how to run an SQL query.

Also, with the QueryDataTableEntries / QueryStreamEntriesWithData, after writing the sample queries as here (PTC)  All I am getting is the names of the properties as columns and not their values. An example to run this query would be appreciated as well.

Thanks!

PaiChung
22-Sapphire I
(To:tyadav)

You can check the following on Streams and Datatables to see if there is any data, by going to the home mashup section in Composer of that entity. it will show data if anything is stored.

If you are storing in a value stream, you will need to use querypropertyhistory or similar from the Thing that is storing into the valuestream.

Relational DB connector is the same as making a JDBC connection, I'm just referring to what Thingworx leverages which are JDBC drivers.

Hi Pai Chung

Yes, I am storing the properties in the value streams and I am querying using querypropertyhistory . The thing is, the querypropertyhistory displays the entire table (or the properties which are logged). What I wanted to know is that, is there a way to run custom queries for eg. just displaying the speed and the timestamp or three columns of the entire table without having to remove the logging of the properties from the things.


But originally what I wanted to know was, how can I use SQL queries to get the same results ( property values) as an output. A simple example would be great.


Or in other words, I want to run the different combinations of queries on-the-go (like the BI queries). For eg: Average speed for a particular time interval or something like that.


Thanks!

Thaveshni
5-Regular Member
(To:tyadav)

I also want to know the same thing. 

 

How do I get the average or total number of products for the week from my Stream?

 

Can someone provide a guide?

Malcolmlsh
6-Contributor
(To:tyadav)

Hi @tyadav , any solutions on this? Thanks!

Create a secondary JDBC connection to your postgres DB.  Once you have that you can create a new SQL Query service like:

 

SELECT *
FROM public.value_stream
WHERE source_id='<your thing name here>' and property_name='<your property name here>' and time >= [[startDate]]::timestamp and time < [[endDate]]::timestamp
order by time

 

where startDate and endDate are service parameters

Top Tags