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

sql server connection

nshariatzadeh
1-Newbie

sql server connection

Hello!

I created a database thing with the template ”MySqlServer”.Our database is on our sql servers(not localhost). I set the JDBC connection setting according to conguration that I found in the website.When I run my sample query I get the error Thing [ARASDATABASE] is not running. According to the previous comments I must restart tomcat but our thingworx instance is test instance that we got to evaluate it. I do not have a war file to deploy on Tomcat. So how can I solve this problem? I also do not know if I should first download and import JDBC connector Extension first or not? I was also following the tutorial lab 2 to create ACMESQLDatabase. I imported MailandSQLextensionpackage and created ACMESQLDatabase(I have already installed MySQL and tested it via MYSQL 5.6 Command Line Client) but when I run my query I get the error “Unable to Invoke Service GetAllRecords on ACMESQLDatabase : Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.” I would appreciate very much if you can help me with these issues.

20 REPLIES 20

Navid, couple things - is the ARASDATABASE Thing enabled? Does it have a check mark in the 'Active' box under 'General Information'? Can you post the configuration setting you are using to connect? Regarding the second question, is it possible that your firewall might be blocking the connection to the ACME database provided in the tutorials? Please do check that too.

Thank you very much for your answers.

my "ARASDATABASE" Thing setting is as below:

JDBC Driver Class Name: com.microsoft.sqlserver.jdbc.SQLServerDriver

JDBC Connection String: jdbc:sqlserver://atlas2.iip.kth.se;databaseName=EIF_Navid

connectionValidationString: SELECT GetDate()

Maxium Number of Connections:5

Database User Name: sa

and I have entered my password

I also turned off the firewall for ACME database but it did not work.

I apprciate if you can help me.

Cheers

The JDBC connection strings seems to be correct. Did you check if the Thing is active? Go into Services, expand Generic Services and run both 'EnableThing' and 'RestartThing' service in that order.

I ran both services and refresh Thingworx but still the connection with database is not established and it says the same message that the ARASDATABASE is nut running.

Sorry i forgot to write,yes the Thing is active.

What version of ThingWorx is this instance and what Template have you based your Thing on? What extensions have you currently imported?

My instance is ThingWorx 5.O on the http://kth.cloud.thingworx.com/Thingworx/Composer

And recently I imported GoogleWidgets_ExtensionPackage and MailandSQLExtensions

Can you create a temporary user access for us? I can atleast check the connectivity issue with the ACME database (provided you are connecting to the example server listed in the tutorials).

Thanks a lot,i just created a user name:nsh1269@gmail.com and password:firstpassword, I also very appreciate if you can take a look to ARASSQLDATABASE thing as well.This thing is a database which is supposed to connect to the database of our PLM system on the server.

Thanks in advance.

Navid, can you recheck the credentials? It doesn't seem to be valid.

Oh I am sorry,the password was wrong and it thecurrent one is  P3eNexEf is :

However when i login as this user i do not have permission to edit my things.

Could you please use my current account:

username:navidsz@kth.se

password:GbplUFIYnMv

Dear Aanjan,hope you are fine I guess you were very busy these days.Do you think you will find some time to look at my 2 database things?ACME machine and ARASDATABASE? is it possible to have a teamviwer meeting so you can also take a look to both  our sqlserver and mysql hich is on local host.

Many thanks in advance

/Navid

Navid, for the ACMESQLDatabase, do you have a MySQL server running on that local machine?

Hi again,yes I have and I verified  it through MySQL 5.6 Command Line Client (I run the sql query and I get the result). I also shut it off the firewall but still get the same error.As I said I get the Property"IsConnected"= True so i assume the connection with server is done but it cannot load the tables.

I logged in with your credentials, and I don't see why it shouldn't work, as your setup/ configuration seems to be correct. What version of the extensions did you import? Would it be possible for you to recheck and import the latest one corresponding to your platform?

I downloaded the extension from part 2 of the tutorial.Well in the metadata of the extentision i can see the package version is"1.0",minimumThingWorxversion is""4.0.0" and MYSQLSERVER template last modified is 2012_11_27.But this extension is not in market place.

But one more question to connect the SQL server (not mySQl) do I need another extension to import?if yes what is it?I ma aking this for my ARASDATABASE thingwhich you already saw.Thanks.

For your DB server connections, you need the right connector .jar file. For example, if you want to use MS SQL, you would need the corresponding sqljdbc driver/ jar file. Once you get that, here is a link to the Marketplace for the JDBC Connector Extension. You can use this Extension to create and import your custom JDBC connector.

Thnaks a lot  once gain so we can come  to this conclusion that we cannot manage that since my thingwox version is 5 and jdbc extension works for thigworx with version of 6.Moreover since my server instance is on the server of thingworx  wecannot have suitable jar file  in the tomcat path(.....jar must be installed with the ThingWorx server.  It is located in TomcatDir\webapps\Thingworx\WEB-INF\lib\.This path is not applicable for organizations which got the test server instance becasue we do not get access to war file to delpoy on tomcat..However we are  still suprised why a tutorial in the very beging for new learners cannot work.I mean MySQL server connetion.becasue for that, the extension is provided by the company and it explicitly mentions that thingworx 5 must function it .Many other part of tutorials did not work for us either like xml parsing.Is thingworx.com/feed still there?or not) to get info from a url with xml presentation. We tried to follow all 5 parts of the tutorial according to PTC university for thingworx 5 and many parts did not work. It seems for us thing does worx is better name:)

Any how we would like to appreciate you one more time.

Navid, is a MySQL server running on that kth.cloud instance? The connection string is set to 127.0.0.1, which is essentially a loopback connection. I'm asking this again, because I want to make sure we're on the same page, and you have directory access to the server. Once you import an extension, and if it contains a .jar file, it will be copied over to the suitable path (for example, here, WEB-INF\lib).

Hi Navid,

The material on precision lms is not fully adapted for users using a hosted instance exclusively. The database that you installed locally on your computer needs to be publically exposed in order for you to be able to access it from your thingworx hosted instance. Some guidance for doing this are to run the following command form your mysql command line

GRANT ALL PRIVILEGES ON *.* TO  root@'%' IDENTIFIED  BY  'root';

And then FLUSH PRIVILEGES;

This grants all privileges on all your databases to the user root using the password root and connecting from any ip address.

You would also need to add an inbound rule in your firewall settings for the port 3306 on which MySQL is running.

Depending on your local network setup, you may need to adjust other settings like NAT settings.

If you do not succeed in making your database publically available, you can use this connection JDBC Connection String  jdbc:mysql://www.db4free.net:3306/thingworx?user=thingworx&password=thingworx&allowMultiQueries=true

in the configuration section of your ACMEDatabase Thing. 

Use

Select * from SampleTable;

to query all record from the datatable.  I used db4free.com to host the database sample offered in the training on precision lms.

Please let me know if you need further assistance.

Thank you,

Veronica

Top Tags