Convert Date time from SQL to TWX

[Jamal8548]

@JK_10565232  I would like to connect my thingworx with MS SQL database and i have seen your this post as you have already done that. Would you please share with me some insights about it. It would be great if you would also share some knowledge that how have you done this task and either it is usefull to do that all work.

Currently we are using datatables but because of limitations of the datatables i would like to connect the external database and use it. Currently i have 5 datatables with each datatable having 100K rows and i am showing on the mashup only latest 100 entries for each datatable becasue i am storing it in latestentrydatatable the primary key of the entries which maintains only latest 100 and then it is easy for me quickly fetch them and show on the thingworx mashup but now the new requirement came that they want search feature in the mashup and for that they asked us to search on 4 or 5 fields and thats whyi also have to do indexing to achieve this goal. I am thinking to connect with MS SQL server database and then use it for such cause. what do you think in this regard the best solution.? Datatables are quite handy and good if the entries are less and not more then 100K. but for such a case where we have a lot of entries we need something for performance. 

[JK_10565232]

HI All,

I would like to understand.

Why if I have a column of the Time type in the database where I only save the time.

Results from DB looks like that Im using MY SQL SERVER

And I invoke it on the TWX side

Does the time move exactly 50 minutes and 39 seconds forward?

What's going on inside TWX with date data types?

I checked the location of the server and browser - we are in the same time zone.

How can I display exactly the same value like I have on DB?

[Surya_Tiwari]

Hi @JK_10565232 

ThingWorx supported UTC zone and you need to change it on your DB side as well, and if required to convert it to your local time zone then use it 

[Surya_Tiwari]

Hi @JK_10565232 

Do you still require help with it? 

[JK_10565232]

Hi @Surya_Tiwari 

I found the source of my problems.

I had a column of the Time type and the input for entering data into this table was DateTime, hence problems with writing and reading

thank you for help. 

Jan

[JK_10565232 · Accepted solution]

Hello,

I can help you without any problem. This is a very right way to change from datatables to SQL database. I have 8 years exp with Thingworx and datatables its not good way to store a large amount of information.

1. First of all please check what kind of persistence provider you have. Each DB has own TWX version. 
you can find it here:

You need to select 'show system objects'

I have MYSSQL persistence provider. You can find postgree persistence provider.

I mentioned about it because if you have MYSQL persistence provider this kind of DB will be easier to connect with TWX. 

Next you need to crate thing with DataBase or MYSQL Thing Template and you should choose Configuration Tab.

You should provide all information about your DB. 

After when you establish connection with your DB you are able to create SQL Query and Commands (Commands are dedicate for Insert, Update,Delete)

Please don t mix SQL services with JS services inside one DB thing because it will create bottle neck with performance. 

Let me know if you will have any additional questions.

Best regards

Jan

[Jamal8548]

Thank you so much for all the information. I have checked it and it is already connected with MS SQL and all the fields are already filled which you have showed me in screenshot. My question is i am not using the MS SQL to store the data into the Database then why there is still a database connected and what we have there? Actually i am working in a different location and our servers are deployed into another city but i am really curious that why do we have then MS SQL as we are not using it or it comes with thingworx? can you please let me know or whats a purpose of it. I have already raised a question about it in our team.

[Jamal8548]

@JK_10565232 I think i have got the answer of my question. The persistance provider is the one where the whole platform data and information is stored like all entities including user information, things etc

but on the other hand side if we want to store the data like which we store from user that we can directly also save in database MS SQL directly not in datatables. please correct me if i am wrong.

[JK_10565232]

@Jamal8548 

The persistence privider is responsible for establishing connections to a given type of database.

So, by all means, you can create an MS SQL server, create a table there with the same column definitions as you have in your datatable, and save the data that comes from the user there.

I do not recommend creating tables or databases on the same server as thingworx.

[bchaudhary]

Hello @Jamal8548 

If you find the resolution of your query, Pls mark the respective answer as accepted solution for the benefit of other community members. 

Regards

Bhawna

[Jamal8548]

Hey @JK_10565232  i am back to my topic now. Can you please help me in this regard. So i have download in my PC the MS SQL express server with SQL server Management Studio also. 

Here is the link which i am following from PTC. I just want to make a connection with thingworx with my local database running in my pc. All the details are on first page when you open this link.

https://community.ptc.com/t5/IoT-Tips/Working-with-External-Databases-Part-1/ta-p/833992

I have imported the file SQLSeverDatabaseController and i also have imported all the database files in my database which is named as tempdb in my laptop. Now how can i establish a connection with my database. 

as you can see in the image. should i need to download SQL server Jar File also? in my laptop i have below files came with SQL server as i have downloaded it. In my persistence provider there is also MS sQL server and there are also configurations but i want to connect it with my SQL server running on my laptop for testing and learning it. please help me guide. 

here is my Database thing controller configurations which i have. I just put my database sql credentials of myself and changed the database name only

MORE DETAILS: 

my SQL  SERVER i have installed locally in my laptop but thingworx is up on server and live NOT locally. I am unable to make a connection with my local database . When i run the service this is the error which i am getting in the thingworx. can you please support me .I just want to connect to my local database. IDK whats a reason may be the port is blocked.

[Jamal8548]

Hi @JK_10565232  SUCCESSFULLY! The database is connected with my thingworx. actually my firewall was making an issue about it and now i am enable it for domain for SQL server. Please ignore my above long query. but it would be great if you would explain the below point which you have written once in my reply. 

Please don t mix SQL services with JS services inside one DB thing because it will create bottle neck with performance. 

[JK_10565232]

Hi Jamal,

Gongrats!! 

TWX works like Java + JS mixed application. You have to keep all stuff which should be connect with your DB on one place. 
Twx will execute SQL query very fast, JS code need t have some more time to be executed. If you will overload one single object like SQL connector thing with execute JS and SQL code TWX will create queue where your SQL code will wait for JS code. 

Second thing is architecture patterns. Keep it simple as much as it possible. My recommendation is create connectors for each blocks of you application. Next you should create provider for this connector like generic thing where you are able execute peace of code from connectors and add business logic.  

[Jamal8548]

Hi @JK_10565232 

Thank you! So if i am not mistaken. For eg. I will create a generic thing which recieves the data from front end and then from database thing i will call the SQL QUery service here in this generic thing to insert the data into database? So that both logics are in different controllers right?

In generic Thing for eg i will call it like that: 

Things["YourDatabaseThingName"].YourInsertServiceName(inputParams)

I hope that i am going into right direction. can you confirm.

[Jamal8548]

Hi @JK_10565232  do you have short time can you please reply me.

filterInput.JS It is taking around 15 seconds to show me data on the mashup. I am fetching the latest 100 records on the mashup. I have created only table in SQL server. do you think i am doing some performance mistake? Or is there any better to fetch the data? 

let finalResults = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
    infoTableName: "InfoTable",
    dataShapeName: "V2.QualityData.DS"
});

// Loop over each line name
lineNamesListArray.forEach(lineName => {
    let result = Things["V2.MYSQLDatabaseController"].SQLQuery_GetEntriesByLineName({lineName: lineName});
    
    if (result && result.rows && result.rows.length > 0) {
        for (var i = 0; i < result.rows.length; i++) {
            var row = result.rows[i];
            finalResults.AddRow(row); // Add each row to the final results
        }
    }
});


let result = finalResults;

SQL QUERY(SEPARATE CONTROLLER). The linename and qualityCheckTimestamp both are indexed. I want to display only latest 100 entries for each line. Currently i am testing with 3 million entries. I have 10 lines and each line has 300k entries. 

SELECT TOP 1 *
FROM dbo.ML1QualityDataTable
WHERE lineName = [[lineName]]
ORDER BY qualityCheckTimestamp ASC;

[JK_10565232]

If you want to work with this large amout of data you need to have very strong server

Can you tell me your server specification ?

How many CPU and ram memory do you have?

[Jamal8548]

OUR SERVER SPECIFICATIONS:- 

The goal is only that when user starts mashup he can see 100 latest entries on the mashup. For search everything is working fine and really fast. Our customers are from different lines no customer can see more then two or max three lines at a time but for me as a admin i can see results from all the lines so then it takes a lot of time. Or do you have anyother suggestion for me. But even for only three lines it takes 8 seconds to load the result on mashup. 

[JK_10565232]

first at all. Increase amount of CPU cores.

Minimal TWX req is 2 or 4. 

I wwill increase your from one to 8 and it should works better.

I worring about part of core where you ask db 100 times about each lines. How many results  you query 

SQLQuery_GetEntriesByLineName

can return?

[Jamal8548]

Thanks for suggestion. Actually with this below query i will get the latest 100 entries from database from each line. for example line1 will return latest 100 entries then for line2 latest 100 entries. Currently i have 9 lines and it returns me 100 latest entries from each line so in total 900 entries. I order it by qualityCheckTimestamp which is a composite index with lineName. So basically from dropdown in mashup the lineNames are selected and get the data to show on mashup by using this query. Currently i have 2 million entries not 3 million and each line has 200K entries. In the DATABASE i only one table in which all the entries are stored. My primarykey is serialNumber and when i make a query with serialNumber to find one entry thats very fast because its a primary key. I hope you understand my usecase. 

The goal is only to show the customer when he loads the mashup a few entries thats it! NOT all the entries he has in database only few latest entries and if he wants some entry speicifically he can give a serialNumber and get his required entry. thats it!

SELECT TOP 100 *
FROM dbo.ML1QualityDataTable
WHERE lineName = [[lineName]]
ORDER BY qualityCheckTimestamp DESC;

[Jamal8548]

Hi @JK_10565232  one more thing

CREATE NONCLUSTERED INDEX idx_lineName_qualityCheckTimestamp ON dbo.ML1QualityDataTable (lineName, qualityCheckTimestamp ASC);

the above is the index which i have made on table now thing is when i fetch the data only for ML10 it works so fast but when i fetch the data with ML1 its slow because of this ASC in the above index query which i made.

Now if i make the index with DESC then ML1 data comes so fast and the higher the number of line goes for eg ML10 the data comes so slow and if i do with ASC then ML10 data comes so fast and decrease the number of lines comes so slow ...... i want latest 100 entries from each line. do you recommend some other technique.

[Jamal8548]

Thanks @JK_10565232 for your help my all questions are resolved!