ThingWorx 8 Part 2 Error: Unable to Invoke Service GetAllRecords on ACMESQLDatabase : null

[domderose]

Summary of Issue:

While taking the Training Course: ThingWorx 8 Part 2: Connecting to External Data, I'm at the section: "SQL Queries and SQL Commands">"Verifying the Database Connection".

When attempting to test the service, I receive the following error: 

"Error Executing Service, Unable to Invoke Service GetAllRecords on ACMESQLDatabase : null"

Checking the Application logs reveals about the same information. 

Question:

I've double and triple checked a lot of details but can't determine my error. 

Has anyone come across this?

Additional Details:

The ACMESQLDatabase thing has the following properties/configuration:

JDBC Driver Class Name = com.mysql.jdbc.Driver

JDBC Connection String = jdbc:mysql://127.0.0.1:3306/thingworxtrainingsample?allowMultiQueries=true

connectionValidationString = SELECT NOW()

Maximum Number of Connections = 5

Database UserName and Pass = root

[posipova]

When you go to the service creation screen, click on the tab "tables/columns" - does it actually load the data from the db or does it say "not found"?

[domderose]

Hello posipova, 

It says "No tables found" and has an infinite "Loading..." nearby.

[posipova]

That means your db is not connected.

 Try this

Connection Test/String Query	SELECT GetDate()
JDBC Connection URL	jdbc:sqlserver://<Server>\<Instance>;databaseName=<Database name>
JDBC Driver Class Name	com.microsoft.sqlserver.jdbc.SQLServerDriver

[domderose]

I'm having difficulty with this. Ive made a few unsuccessful attempts to use that syntax you've provided but can't determine the "JDBC Connection URL" syntax. 

A few follow-up questions: They may sound off since I'm new to this.

1. Does it matter that I've installed ThingWorx with PostGreSQL? Would that change the information?

2. Here is a screen shot of my postgresql in case it helps:

3. I'm assuming the original location mentioned by the training course had a database called thingworxtrainingsample and that information doesn't match my current system and is no longer useful?

[slangley]

Hi @domderose.

It doesn't matter that you set up your ThingWorx database on PostgreSQL.  This is actually an external database you will be querying.  Did you see the following note in the How to Steps for Importing Mail and SQL Extensions:

MySQL and the sample integration data must be installed on your virtual machines for further exercises. MySQL for Windows and installation instructions are available in the lab files package.

The instructions and installer for MySQL are available in the lab files that can be downloaded for this training module.  The data you will be querying is available once you complete the import detailed in the .pdf.

Please let me know if I'm missing something in regard to this post.

Regards.

--Sharon

[rosharma]

Hi @domderose

1. from the ThingWorx training material -- it comes an example to work with MySQL

- so you have to install MySQL database to your machine

- provide configuration on entity on composer to connect to Database MySQL ( provide mySQL jdbc drivers, URL's to configuration tab as mentioned in doc of training materials)

- also place the specific mysqljdbc.jar to tomcat installation lib folder and restart the tomcat

- see that isConnected value is turn to true -- on entity over composer from where you are executing the service

further there is no difference in using the postgresql, Ms SQL server, MySQL database - these are only the different databases

ThingWorx provides default already inbuilt persistence providers like H2, postgresql , MS SQL server

you can download any specific version of ThingWorx from PTC downloads 

for postgresql, MS SQL server -- ptc installation document provides scripts to setup the database and schema

for H2 - there are no such scripts-- it comes with as inbuilt feature in ThingWorx

2. from your screenshot placed for postgres database thingworx setup - i think the database setup is not done completely. ideally the number of tables should be 58 but in your case they are 51

it seems something wrong with executing the scripts completely

On this you first cleanup the database using clean up scripts which comes with PTC software downloads( install folder)

then again re run the database and schema scripts as mentioned in installation documents

3. the training course had a database called thingworxtrainingsample -- this is ready to use database as mentioned in training material. once setup for MySQL is done followed by imports--thingworxtrainingsample database is observed at DB side

[slangley]

Hi @domderose.

If one of the previous responses resolved your issue, please mark it as the Accepted Solution for the benefit of others who may have the same question.

Regards.

--Sharon

[DCraig]

I had this same problem.  I think the issue is caused by the .zip file being blocked by Windows when it is downloaded.  Before you unzip the package, right click the archive, select "Properties" and then click "Unblock" in the lower right.  You can then unzip the archive.  If you have already imported the extensions you will need to delete anything from your project that uses/references the extension objects, then you can delete them from the Manage Extensions page and import them again.