Skip to main content
M
mnaeem1-Visitor
1-Visitor
November 21, 2019
Solved

External Database Connection

  • November 21, 2019
  • 2 replies
  • 1848 views

Hello,
Q1.According to previous guides I have downloaded Metadata.xml file from ptc marketplace and after importing in thingworx platform it has created a mashup with the name jdbccreater to upload the file and it is not accepting any kind of file. (Didn't modify metà.XML file).
Q2. I have created a thing testDatabase with base template as Database. After writing in configurations and saving it, only the lasconnected property has been modified, while isConnected is false.
I am using postgresql which is also my persistence provider and SqlServer 2014. But having the same results isConnected:false

Thank you!
Naeem

Best answer by mnaeem

Thanks to the solution of https://community.ptc.com/t5/ThingWorx-Developers/Using-a-third-party-Jar-file-for-Database-connection/td-p/514861. Here are the steps to connect to MSSQL Database.

1.metadata.xml

<Entities>
<ExtensionPackages>
<!-- The name attribute of the ExtensionPackage element correlates to the name of the zip archive for the extension -->
<ExtensionPackage name="DatabaseMSSQL_ExtensionPackage" description="DatabaseMSSQL JDBC Extension" vendor="write something" packageVersion="1.0" minimumThingWorxVersion="5.0.0">
<JarResources>
<!-- The JDBC JAR file being used to connect to DatabaseXYZ -->
<FileResource type="JAR" file="sqljdbc4-2.0.jar" description="DatabaseMSSQL JDBC JAR"/>
</JarResources>
</ExtensionPackage>
</ExtensionPackages>
<ThingTemplates>
<!-- A Database ThingTemplate for the DatabaseXYZ extension -->
<ThingTemplate name="DatabaseMSSQL" baseThingTemplate="Database" description="DatabaseMSSQL ThingTemplate"/>
</ThingTemplates>
</Entities>

2. Created a folder lib/common and saved mssql jar file in common.

3. Selecte lib and metadata.xml and created zip folder.

4. Imported as extension in Thingworx platform.

5. Created a thing (MSSQL_Database) with base Thing template created after step 4. (In my case DatabaseMSSQL) and save.

6. Edit MSSQL_Database thing's configurations as follows:

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

    JDBC Connection String: jdbc:sqlserver://<twServer>\<MSSQL instance>;databaseName=<your database>

    connectionValidationString: SELECT 1

7. Save

 

Finally, it is connected.

2 replies

P
PaiChung22-Sapphire I
22-Sapphire I
November 22, 2019

Try editing the upload mashup, then just make some minor change to the file upload widget like change the Widget Name

Save it, and relaunch the mashup.

Then try again.

M
mnaeem1-VisitorAuthor
1-Visitor
November 22, 2019

Thank you @PaiChung 
Can you please elaboate a bit that What do I have to edit in the upload mashup.

There are connections with Thing JDBCExtensionCreater, to explain in a better way I've attached the screenshots with the message.

upload1.JPG

upload2.JPG

P
PaiChung22-Sapphire I
22-Sapphire I
November 22, 2019

Click the file upload widget itself

and change a trivial setting on it.

M
mnaeem1-VisitorAuthorAnswer
1-Visitor
November 25, 2019

Thanks to the solution of https://community.ptc.com/t5/ThingWorx-Developers/Using-a-third-party-Jar-file-for-Database-connection/td-p/514861. Here are the steps to connect to MSSQL Database.

1.metadata.xml

<Entities>
<ExtensionPackages>
<!-- The name attribute of the ExtensionPackage element correlates to the name of the zip archive for the extension -->
<ExtensionPackage name="DatabaseMSSQL_ExtensionPackage" description="DatabaseMSSQL JDBC Extension" vendor="write something" packageVersion="1.0" minimumThingWorxVersion="5.0.0">
<JarResources>
<!-- The JDBC JAR file being used to connect to DatabaseXYZ -->
<FileResource type="JAR" file="sqljdbc4-2.0.jar" description="DatabaseMSSQL JDBC JAR"/>
</JarResources>
</ExtensionPackage>
</ExtensionPackages>
<ThingTemplates>
<!-- A Database ThingTemplate for the DatabaseXYZ extension -->
<ThingTemplate name="DatabaseMSSQL" baseThingTemplate="Database" description="DatabaseMSSQL ThingTemplate"/>
</ThingTemplates>
</Entities>

2. Created a folder lib/common and saved mssql jar file in common.

3. Selecte lib and metadata.xml and created zip folder.

4. Imported as extension in Thingworx platform.

5. Created a thing (MSSQL_Database) with base Thing template created after step 4. (In my case DatabaseMSSQL) and save.

6. Edit MSSQL_Database thing's configurations as follows:

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

    JDBC Connection String: jdbc:sqlserver://<twServer>\<MSSQL instance>;databaseName=<your database>

    connectionValidationString: SELECT 1

7. Save

 

Finally, it is connected.

Terms & ConditionsAccessibility statement