How can i read in data from a database, creating ThingTemplates from my data programmatically?

[emasiala]

I am very new to ThingWorx, so any guidance is appreciated. I need to read data from an existing SQL database, and programmatically create ThingShapes, ThingTempates and Things from my data. I am not sure how to read in the data ... any tutorials would be helpful too. the existing video tutorials do not address my questions.

[paic]

The eLearning content should have covered how to retrieve data from a database.

Together with the EntityServices library you should have all the tools you need.

I recommend you check articles that deal with CreateThing/CreateThingTemplates etc.

Is there a particular use case though that you would need to go to this extend?

Generally it is recommended to have the 'skeleton' model in place and not completely dynamically define all entities, but use that to spin of Entities based on a defined model.

[emasiala]

I now have the basic templates in place. I still can't find a tutorial on reading in data from a database.

The videos cover creating things and templates etc, but not how to connect to an external data source - at least, I am yet to find one that does...

[ibanham]

Hi

Which videos are you referring to? If you have access to the e-learning, look at section 3, connecting to External Data Sources. You will need a simple extension to ThingWorx to add connectivity to the particular database you are using. There's a video for this in the on-line help for ThingWorx 6.0 for how to create a JDBC extension, search for the on-line video tutorials in the documentation (I searched for "JDBC") it's the last but one video in the list. There's also a tool on Marketplace that simplifies the process of creating a JDBC extension.

Regards

Ian

[pschmaltz]

Here's a link to a specific post on the ThingWorx Community page that speaks to SQL Server (includes some video links as Ian mentioned).

https://twc.thingworx.com/Thingworx/Runtime/index.html#master=ThingWorxCommunity&mashup=TWCWikiSearchResult&wiki=General…

I recently had some issues getting my JDBC connection to work with SQL Server (even after following the above instructions). I had a JDBC driver that I knew worked fine for another application and I wanted to reuse that in ThingWorx. Per the ThingWorx documentation I found that I could manually put that jdbc driver's jar file into my Tomcat instance's webapps/Thingworx/WEB_INF/lib directory, and upon restarting I had no issue using the same connection strings in the configuration of my db thing as I was successfully using for my other application.

Doing that sort of manual application of a specific jdbc driver probably isn't the best approach however. Creating an extension that then can survive subsequent updates/installations etc. is better if this is meant for more than just demo/sandbox usage. Check out ThingWorx How to Create a JDBC Extension - YouTube where Pai provides an example of this.

FWIW.

peter.

[emasiala]

Thank you very much. I am working through that today... i hope to get my data in, so I can work on visualkization with Mashup.

[emasiala]

Hello Peter, the 'How to create a JDBC Extension' video refers to the metadata file which needs to be edited and bundled up with the driver jar file. A sample of the metadata file content should be on our community site - that's base don the comment on the YouTube video - but it does not say where, and my searches didn't turn up anything on the community page. Do you have a sample to share? Thanks.

[pschmaltz]

I believe I have been able to find that metadata.xml file referenced in Pai's video. Not seeing a way to simply attach it as a file here, so I'm just going to paste it below for your reference (not too large, so shouldn't be much trouble to grab and repurpose). Hope this helps!

-------------------

<Entities>

  <ExtensionPackages>

    <ExtensionPackage name="MYSQL_ExtensionPackage"

  description="My SQL JDBC Extension"

  vendor="ThingWorx Customer Service"

  packageVersion="1.0"

  minimumThingWorxVersion="4.0.0">

      <JarResources>

        <FileResource type="JAR" file="mysql-connector-java-5.1.22-bin.jar" description="MySQL JDBC JAR" />

      </JarResources>

    </ExtensionPackage>

  </ExtensionPackages>

  <ThingTemplates>

    <ThingTemplate baseThingTemplate="Database" description="MySQL Server" documentationContent="" effectiveThingPackage="" homeMashup="" lastModifiedDate="2012-11-27T11:40:35.356-05:00" name="MySqlServer" tags="" thingPackage="">

      <ThingShape description="" documentationContent="" lastModifiedDate="2012-11-27T11:40:35.355-05:00" name="" tags="">

        <PropertyDefinitions/>

        <ServiceDefinitions/>

        <EventDefinitions/>

        <ServiceImplementations/>

        <ServiceMappings/>

        <Subscriptions/>

      </ThingShape>

      <ImplementedShapes/>

      <EffectiveImplementedShapes/>

  <ConfigurationTables>

  <ConfigurationTable description="" isMultiRow="false" name="ConnectionInfo">

  <DataShape>

  <FieldDefinitions>

  <FieldDefinition aspect.defaultValue="5.0" baseType="NUMBER" description="Maximum number of connections in the pool" name="maxConnections" ordinal="0"/>

  <FieldDefinition aspect.defaultValue="jdbc" baseType="STRING" description="jDBCConnectionURL" name="jDBCConnectionURL" ordinal="0"/>

  <FieldDefinition aspect.defaultValue="SELECT NOW()" baseType="STRING" description="connectionValidationString" name="connectionValidationString" ordinal="0"/>

  <FieldDefinition aspect.defaultValue="com.yourdatabase.yourDriver" baseType="STRING" description="jDBCDriverClass" name="jDBCDriverClass" ordinal="0"/>

  <FieldDefinition baseType="STRING" description="Database user name" name="userName" ordinal="0"/>

  <FieldDefinition baseType="PASSWORD" description="Database password" name="password" ordinal="0"/>

  </FieldDefinitions>

  </DataShape>

  <Rows>

  <Row>

  <jDBCConnectionURL><![CDATA[jdbc:mysql://localhost:3306/demo?user=root&password=password&allowMultiQueries=true]]></jDBCConnectionURL>

  <maxConnections>100.0</maxConnections>

  <connectionValidationString><![CDATA[SELECT NOW()]]></connectionValidationString>

  <jDBCDriverClass><![CDATA[com.mysql.jdbc.Driver]]></jDBCDriverClass>

  <userName />

  <password />

  </Row>

  </Rows>

  </ConfigurationTable>

  <ConfigurationTable description="" isMultiRow="false" name="ConnectionMonitoring">

  <DataShape>

  <FieldDefinitions>

  <FieldDefinition aspect.defaultValue="1.0" baseType="NUMBER" description="Number of retries" name="numberOfRetries" ordinal="0"/>

  <FieldDefinition aspect.defaultValue="2000.0" baseType="NUMBER" description="Retry delay in milliseconds" name="retryDelay" ordinal="0"/>

  <FieldDefinition aspect.defaultValue="false" baseType="BOOLEAN" description="Enable connection monitoring" name="enableMonitor" ordinal="0"/>

  <FieldDefinition aspect.defaultValue="30000.0" baseType="NUMBER" description="Monitor rate in milliseconds" name="connectionMonitorRate" ordinal="0"/>

  </FieldDefinitions>

  </DataShape>

  <Rows>

  <Row>

  <numberOfRetries>1.0</numberOfRetries>

  <retryDelay>2000.0</retryDelay>

  <enableMonitor>false</enableMonitor>

  <connectionMonitorRate>3000.0</connectionMonitorRate>

  </Row>

  </Rows>

  </ConfigurationTable>

  </ConfigurationTables>

      <avatar/>

      <DesignTimePermissions>

        <Create/>

        <Read/>

        <Update/>

        <Delete/>

        <Metadata/>

      </DesignTimePermissions>

      <RunTimePermissions/>

      <InstanceDesignTimePermissions>

        <Create/>

        <Read/>

        <Update/>

        <Delete/>

        <Metadata/>

      </InstanceDesignTimePermissions>

      <InstanceRunTimePermissions/>

    </ThingTemplate>

  </ThingTemplates>

</Entities>

[emasiala]

Thank you.. I got it..

[emasiala]

It's weird.. I have tried importing the template, but i keep getting this error:

The file is located on my C drive in the ThingWorxStorage folder, and manual attempts to delete it, result in a message that the file is open in Tomcat...

I was going over other discussions on this issue, and i noticed the following:

1. i did not get the sqljdbc4.jar file by default in my TomcatInstallDirectory/webapps/Thingworx/WEB-INF/lib folder. Not sure if that is important - i got that off another discussion on this issue.

2. When i create a thing based off of the Database template, i don't get any database-specific configuration properties. I am assuming that even without impoirting a template, i should be able to setup a database connection.

I am running on ThingWorx version 6.0.3.

Thank you

[emasiala]

Thank you Ian. I did find the on-line video help forThingWorx 6.0 on how to create a JDBC extension - and that should be sufficient for now. But, I am curious about the eLearning site that you referred to earlier. I can't find it on the PTC support website. I will send an email to support asking about it - is this a paid course of some kind?

[emasiala]

I did find the eLearning site with a free 30 day trial, but it seems ThingWorx is not one of the products covered - there are lots of changes to the PTC website recently - I wonder if it is only just temporarily unavailable. I will check it again later. Thanks for your help.

[emasiala]

It's weird.. I have tried importing the template, but i keep getting this error:

The file is located on my C drive in the ThingWorxStorage folder, and manual attempts to delete it, result in a message that the file is open in Tomcat...

I was going over other discussions on this issue, and i noticed the following:

1. i did not get the sqljdbc4.jar file by default in my TomcatInstallDirectory/webapps/Thingworx/WEB-INF/lib folder. Not sure if that is important - i got that off another discussion on this issue.

2. When i create a thing based off of the Database template, i don't get any database-specific configuration properties. I am assuming that even without impoirting a template, i should be able to setup a database connection.

I am running on ThingWorx version 6.0.3.

Thank you

[Aanjan]

Did you delete the old extension before you imported the new one? Also, restarting Tomcat after deleting an extension (prior to importing a newer version of the same extension) would clear out any broken links/ files/ dependencies, so please do try that.