Skip to main content
N
nshariatzadeh1-Visitor
1-Visitor
November 18, 2015
Question

sql server connection

  • November 18, 2015
  • 2 replies
  • 7586 views

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.

      2 replies

      A
      Aanjan5-Regular Member
      5-Regular Member
      November 18, 2015

      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.

      N
      nshariatzadeh1-VisitorAuthor
      1-Visitor
      November 19, 2015

      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

      A
      Aanjan5-Regular Member
      5-Regular Member
      November 19, 2015

      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.

      V
      vmihai1-Visitor
      1-Visitor
      December 9, 2015

      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

      Terms & ConditionsAccessibility statement