How to send the data from a table created in Hostinger Cloud Database to ThingWorx (version 9.3) ?

[NH_shree]

Hi , can anyone help me how to send the data to the properties created in thingworx-thing from the database located in the Hostinger with steps as i am new to use thingworx platform. i tried to import csv file successfully to thingworx but i am unable to find the imported csv file stored in which location inside the thingworx composer, please help me out with this issue , i am expecting some quick reply from community to this message please .

[jensc]

Hello,

I am not really sure what your problem is.

Are you trying to import CSV files or do you want to connect to a database that is hosted on Hostinger?

For importing CSV files, you can use the file upload widget:

To upload your file to a repository which you will find under "Manage":

You can also upload the file directly into the repository if you want.

Then you can use the CSVParser to read the file and import whatever data you need from it.

Here you can read more about the CSV parser.

Here you can see how you can use the CSV parser.

If you want to connect to an external database you need to create a thing that is using the database base thing template:

Then in the configuration of this thing you would add your configurations for the database.

I couldn't really find any good guides on how to do this. But it is pretty self explanatory. If you need more help though, don't hesitate to ask here again.

Regards,

Jens

[NH_shree]

Hi @jensc ,

Exactly I want to connect on a database that is hosted on hostinger , but i am not sure how to connect my thingworx to hostinger database.

Regards,

Shree

[jensc]

Hello,

Okay. Do you mean you want to run your thingworx instance together with the database you are hosting there?

Or do you just want to  get some data from it?

I am not familiar with this particular hosting platform so I can't say for sure this will work.

But normally if it is a MSSQL database you would use the "Database" base thing template when creating a thing.

This will give you a configuration page on that thing that looks like this:

In this configuration page you would provide the connection string (similar to what you would use when connecting to your database using for example SSMS (SQL server management studio).

You also need to provide an account to use.

Once you save the configuration, you can go to the "properties and alerts" page of the thing and check if it has connected:

These are just the steps that we use to connect to our internal MSSQL databases. So you might need to try and find some other drivers if you are using a different database.

Regards,

Jens

[NH_shree]

Hi @jensc ,

this is my hostinger platfiorm where the database is created and stored 

it uses MYSQL database 

i want to access data from a table created inside the database as shown in the below image

i have created a thing in the thingworx 

with the properties temperature and humidity in the properties and alerts table .

now i have to make a connection in such a way that the table data of humidity and temperature should get assigned to the properties created in thing(i.e., i should connect to the database table to get the humidity and temperature data automatically from hostinger database to thingworx ) 

This is the problem i am facing with , can you help me regarding this?

Regards ,

Shree

[jensc · Accepted solution]

Hello,

As I said I'm not really familiar with Hostinger hosting.

But looks like you would need to first give access to your MySQL server; link

Then I found this PTC support article on how to connect thingworx to a MySQL DB.

I am not sure if the MySQL JDBC drivers are installed out of the box on thingworx. You might have to follow this support article to get it to work.

There are also some related topics at the very bottom that might be good to read up on.

Hope this helps.

Regards,

Jens

[NH_shree]

Hi,

i followed the same steps , i have made my database remote 

but here what is the driver file that needs to be uploaded in the 3rd step , can you please guide me here  !

[jensc]

Hello,

I think I found a somewhat updated version of the support article here.

Seems like you might have to find a JDBC driver from MySQL for example.

And then import it using the JDBC extension.

I have never done this though.

So perhaps it would be best to wait and see if someone who has experience with this can give you some pointers.

Regards,

Jens

[NH_shree]

Hello ,

I have followed the steps with the help of the below article

https://www.ptc.com/en/support/article/CS243088

and also i tried to do using this https://www.ptc.com/en/support/article/CS318728

but still i am unable to connect to my database  

The above shown is the hostinger database which is made as remote mysql database 

These are the images which shows my thing template information which is created after i import jdbc driver jar file

The above shown are the images which i created a thing after getting the thing template and its configuration , i did not get the isconnected checkbox filled , how to establish connection , is there any mistake from me , please help me to establish the connection .

Regards,

Shree

[jensc]

Hello,

Thank you for the screenshots. I think it will help if someone else has experience with using MySQL with thingworx.

I did notice one thing that you might want to try to change. 

Here is my MSSQL database thing. Notice in the JDBC Connection String, it starts with just "jdbc", not "jjdbc" as in your screenshot.

Perhaps this could be the reason for your issue.

Also, it might be worth checking out if there are any error messages in any of the different log files.

Please try to change it and get back to us with the results.

Regards,

Jens

[NH_shree]

Hi,

Thanks for your quick response @jensc 

I changed it but still i am not getting what could be done , Below are the screen captures 

After changing , i saved it and i refreshed the page still i could not achieve the connection 

Regards,

Shree

[NH_shree]

Hi ,

Yes i have an warning in my application log stating as follows

Regards,

Shree

[jensc]

Hello,

Do you get any errors in any of the platform logs like Application log?

I'm not sure if I can be of any more help as I have no experience with this.

I hope someone else might have this experience.

I found this PTC support article as well that might be of interest. However I'm not sure what access you have to the TWX platform itself.

Regards,

Jens

[NH_shree]

Hi ,

Yes i have an warning in my application log stating as follows

Regards,

Shree

[jensc]

Hello,

Would it be possible for you to upload the screenshot again? It is too small to see.

Regards,
Jens

[NH_shree]

Hello,

This is the error i am getting in my application log

Regards,

Shree

[jensc]

Hello,

Okay, well it doesn't look like it is related to your DB.

I'm afraid I'll probably not be able to assist you further with this.

If no one else gets back to you here on the forum I would suggest opening a support ticket with PTC.

Regards,

Jens

[NH_shree]

Hello,

if anyone have solution to my post please respond.

Regards,

Shree 

[DDabrowski]

Hello @NH_shree,

I have a few clarification questions:

1. What version of MySQL are you currently using?
2.  What version of the JDBC Driver are you currently using?
3. Could you explain your JDBC Driver import process in detail, please? The process typically requires copying the JDBC Driver to the Tomcat Home lib folder, and then restarting tomcat. Are these the steps that you took?

Regards,
Dan

 

[NH_shree]

Hello @DDabrowski ,

1)Hostinger Mysql database - php 8.0 version

2) jdbc driver i have used is mysql-connector-j8.0.32 

3) first i downloaded thr jdbc connector extension package from thingworx -> Download software-> there selected my version thingworx 9.3-> from that list i got jdbc connector extension, 

Then after that i imported the above zip file to my thingworx composer,  then a jdbc creator mashup is generated  from that,

once after getting jdbc creator mashup , went to view mashup, there i had coose file option to upload, 

So i downloaded mysql-installer as shown below

Then i installed it in my C drive , A MYSQL folder is created inside the c folder , inside the Program Files(x86), where the folder contains the connector J8.0 folder which has the mysqlconnectorj 8.0.32.jar file in it.

I uploaded this jar file i the jdbc creator mashup , then i got the extension file from that , i  downloaded the extension file  and imported the extension file to thingworx composer

a thing template is created from that imported file by the name mysql-connector-j-8032JDBCTemplate

i created a  thing out of it where the base thing template is assigned as mysql-connector-j-8032JDBCTemplate , after saving it, i configured  with my credentials i.e., i have mysql database in hostinger cloud which i made one of the required database name as remote mysql so that after making my database as remote it has generated a ip adress as the servername to access ,

so i used the generated ip of the remote mysql database as server name and port number as 3306 and my database name is given as the remote data base name suppose for example if in localhost i have abc_123 as database name and  abc_000 as the username and abc as the password ,

after making abc_123 database as remote the new database name is shown in remote mysql inside the hostinger is as abc_xyz(where xyz is the name assigned for very database which is made as remote) and there will be access host which is % means any ip can access  

Before configuration i copied the mysqlconnectorj 8.0.32.jar file to the tomcat lib where the thingworx is installed (i followed the steps of <tomcat Home>/webapps/Thingworx/WEB-INF/lib>

After this i restarted the service of the thingworx foundation where apache tomcat is installed ,

then i configured the thing with the details that is 

jdbc driver class name as com.mysql.jdbc.Driver

and the connection string as dbc:mysql://<remotemysql server name>:3306;databasename=abc_xyz(remote database generated  name)

connectionValidationString as SELECT GETDATE()

Database User Name as abc_000

and passwors as abc

These are the steps i have carried out to establish the connection from hostinger database to thingworx

Regards,

Shree

[NH_shree]

Hello,

Have you checked with my previous post, is any changes needs to be made?

Regards,

Shree

[DDabrowski]

Hello @NH_shree ,

It appears you installed mysql on the ThingWorx machine, but the intial question was how to connect to mysql in hostinger. Can you clarify your intent? 

It also appears that the JDBC file was not copied to the tomcat home lib folder. If you used the installer for ThingWorx, the location will be something like C:\Program Files\ThingWorxFoundation\tomcat\apache-tomcat-9.0.63\lib

It may be appropriate to open a case for this. Please private message me with your PTC support email address and I will be happy to open the case on your behalf.

Regards,
Dan Dabrowski

[NH_shree]

Hello @DDabrowski ,

I have not installed mysql on the thingworx mahine

we have hostinger cloud server, which provides the database mysql to store the data in a bulk, so we are using it to store the bulk data and hosting it in the cloud, where as even i do not have any idea on that how to work in the hostinger . But still to access the database in it it has localhost as the server name and 3306 as the port number and if we are not able to access it then we can make the particular database name as remote mysql database to access from any host so that it generates some ip address as the server name after we make the particular database as remote

say for example you can see in the below image

here if you enter into phpmyadmin you will get the servername with port number there which i used to try it in the configuration details but connection was not established ( the version of phpmyadmin used in this is 8.0)

so as connection was not established i made this database as remote mysql database 

so in the last image i got an ip address which i used with the port number 3306 but still the connection was not possible 

Here is the image where i have installed the jdbc jar file in the tomcat lib

also i have restarted the service after placing the jar file in the tomacat 

Regards,

Shree

[NH_shree]

Hello,

I have also tried using my local system database that is MSSQL server but for that also the same connection problem is getting as above 

please help.

Regards,

Shree

[DDabrowski]

Hello @NH_shree 

Apologies that this thread was overlooked. Have you resolved your issue? 
If the issue is still pending, it sounds like you are missing the correct host name for the database server, and possibly the correct port.

Please let us know if we can help any further on this topic. Thank you!

Kind Regards,
Dan Dabrowski