20 Replies Latest reply on Dec 9, 2015 4:55 AM by veronicamih RSS
    nshariatzadeh Explorer

    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.

      • Re: sql server connection
        aanjan Heavyweight Champ

        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.

          • Re: sql server connection
            nshariatzadeh Explorer

            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

              • Re: sql server connection
                aanjan Heavyweight Champ

                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.

                  • Re: sql server connection
                    nshariatzadeh Explorer

                    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.

                      • Re: sql server connection
                        nshariatzadeh Explorer

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

                          • Re: sql server connection
                            aanjan Heavyweight Champ

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

                              • Re: sql server connection
                                nshariatzadeh Explorer

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

                                And recently I imported GoogleWidgets_ExtensionPackage and MailandSQLExtensions

                                  • Re: sql server connection
                                    aanjan Heavyweight Champ

                                    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).

                                      • Re: sql server connection
                                        nshariatzadeh Explorer

                                        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.

                                          • Re: sql server connection
                                            aanjan Heavyweight Champ

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

                                              • Re: sql server connection
                                                nshariatzadeh Explorer

                                                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

                                                  • Re: sql server connection
                                                    nshariatzadeh Explorer

                                                    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

                                                      • Re: sql server connection
                                                        aanjan Heavyweight Champ

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

                                                          • Re: sql server connection
                                                            nshariatzadeh Explorer

                                                            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.

                                                              • Re: sql server connection
                                                                aanjan Heavyweight Champ

                                                                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?

                                                                  • Re: sql server connection
                                                                    nshariatzadeh Explorer

                                                                    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.

                                                                      • Re: sql server connection
                                                                        aanjan Heavyweight Champ

                                                                        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.

                                                                          • Re: sql server connection
                                                                            nshariatzadeh Explorer

                                                                            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.

                                                                              • Re: sql server connection
                                                                                aanjan Heavyweight Champ

                                                                                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).

                                            • Re: sql server connection
                                              veronicamih Creator

                                              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