Skip to main content
P
ptc-62272491-Visitor
1-Visitor
October 5, 2015
Question

Hi All, does anybody have experience using PostgreSQL and Thingworx? for a POC i need to connect remotely to a Postgres DB and i could use some help. any recomendations? Should i use the ADO connector? Thanks in advance

  • October 5, 2015
  • 2 replies
  • 3326 views

Hi All, does anybody have experience using PostgreSQL and Thingworx? for a POC i need to connect remotely to a Postgres DB and i could use some help. any recomendations? Should i use the ADO connector? Thanks in advance

2 replies

P
paic1-Visitor
1-Visitor
October 5, 2015

You need to connect to PostGreSQL as an external database? I would recommend using JDBC and follow the instructions on how to connect to relational databases.

Also in the marketplace there is a handy extension to create an actual Thingworx extension from the JDBC jar file.

B
BryanK16-Pearl
16-Pearl
November 15, 2016

Hi,

For my case, I have a software monitor that can log 100's of items to a csv file. I would use the postgres to import the csv file and read that information using the ADO as an edge server. Instead of trying to learn 14 new programming languages to get sensor data and return it to TW this seems like the quick solution to get proof of concept results to TW.

Am I way off the mark, or do you have any other suggestions that I should follow.

Thanks

B
BryanK16-Pearl
16-Pearl
November 15, 2016

Hi,

Did you get any further with this?

I have a similar requirement the documentation for the set up seemed simple enough but then again we all know the answer to that...

For my POC I don't need any security so I have disabled what needs to be disabled.

All I seem to get is this.

2016-11-16 08:18:05.992Z [L: Information] [T: 1] [U: SYSTEM] [O: com.thingworx.services.HostService] Initialization complete.

2016-11-16 08:18:05.996Z [L: Information] [T: 4] [U: SYSTEM] [O: com.thingworx.services.HostService] Starting Service...

2016-11-16 08:18:06.025Z [L: Information] [T: 4] [U: SYSTEM] [O: com.thingworx.services.HostService] Service started.

2016-11-16 08:18:06.319Z [L: Warning] [T: 5] [U: SYSTEM] [O: com.thingworx.services.HostService] Encryption is disabled.

2016-11-16 08:18:06.408Z [L: Information] [T: 6] [U: SYSTEM] [O: com.thingworx.communications.client.TwApiWrapper] Registering 0 Properties for ThingName: AdoThing

2016-11-16 08:18:06.426Z [L: Information] [T: 6] [U: SYSTEM] [O: com.thingworx.communications.client.TwApiWrapper] Registering 4 Services for ThingName: AdoThing

2016-11-16 08:18:06.458Z [L: Error] [T: 6] [U: SYSTEM] [O: com.thingworx.ado.AdoClient] Error while initializing new AdoThing, or opening connection to Platform. : System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.     at com.thingworx.communications.client.TwApiWrapper.twApi_Connect(UInt32 timeout, Int32 retries)     at com.thingworx.communications.client.TwApiWrapper.Connect(UInt32 timeout, Int16 retries)     at com.thingworx.communications.client.BaseClient.start()     at com.thingworx.ado.AdoClient.run()

A
Aanjan5-Regular Member
5-Regular Member
November 15, 2016

Bryan, just to check, does the user you're authenticating as has admin privileges? What version of the ADO Connector are you using?

B
BryanK16-Pearl
16-Pearl
November 15, 2016

Hi,

Yes the user has admin rights, the version of ado is "ado-service-5.6.1.1100-Release"

------------------------------------ Update--------------------------

I'm not sure if this expected behavior or not but this is what I'm getting.

When I reset the config file back to the very basics the AdoThing Connects VERY briefly and then disconnects.

From starting the service till it stops (automatically) is about 5 seconds.

I can see that it connected if I look at the AdoThing>remotedatabase>properties>LastConnection.

The log file also tells me that it connected BUT I think that its crashing because there is nothing after that.

2016-11-16 09:10:39.440Z [L: Information] [T: 1] [U: SYSTEM] [O: com.thingworx.services.HostService] Initializing Service...

2016-11-16 09:10:39.553Z [L: Information] [T: 1] [U: SYSTEM] [O: com.thingworx.services.HostService] Initialization complete.

2016-11-16 09:10:39.557Z [L: Information] [T: 4] [U: SYSTEM] [O: com.thingworx.services.HostService] Starting Service...

2016-11-16 09:10:39.587Z [L: Information] [T: 4] [U: SYSTEM] [O: com.thingworx.services.HostService] Service started.

2016-11-16 09:10:39.842Z [L: Warning] [T: 5] [U: SYSTEM] [O: com.thingworx.services.HostService] Encryption is disabled.

2016-11-16 09:10:39.910Z [L: Information] [T: 6] [U: SYSTEM] [O: com.thingworx.communications.client.TwApiWrapper] Registering 0 Properties for ThingName: AdoThing

2016-11-16 09:10:39.930Z [L: Information] [T: 6] [U: SYSTEM] [O: com.thingworx.communications.client.TwApiWrapper] Registering 4 Services for ThingName: AdoThing

2016-11-16 09:10:39.950Z [L: Critical] [T: 6] [U: SYSTEM] [O: com.thingworx.communications.client.TwApiWrapper] [Critical] twWs_Connect: Websocket connected!

2016-11-16 09:10:39.951Z [L: Information] [T: 7] [U: SYSTEM] [O: com.thingworx.communications.client.ConnectedThingClient] Websocket connected

2016-11-16 09:10:39.957Z [L: Information] [T: 9] [U: SYSTEM] [O: com.thingworx.communications.client.ConnectedThingClient] Websocket connected and authenticated

-----------------another update------------------------------------

I finally got my connection string to the db working.

the drivers for odbc drivers are available here

PostgreSQL: File Browser

I'm using the x64 driver.

And this is my connection string.

    "ConnectionType": "Odbc",
    "ConnectionString": "Driver={PostgreSQL ANSI(x64)};Server=localhost;Port=5432;Database=Kariba;Uid=username;Pwd=password;",

The problem is that service is crashing.

From event viewer:

Faulting application name: ThingWorxADOService.exe, version: 5.6.1.1100, time stamp: 0x56ab8163

Faulting module name: SSLEAY32.dll, version: 1.0.1.13, time stamp: 0x551e1eed

Exception code: 0xc0000005

Fault offset: 0x000000000002878d

Faulting process id: 0x%9

Faulting application start time: 0x%10

Faulting application path: %11

Faulting module path: %12

Report Id: %13

Faulting package full name: %14

Faulting package-relative application ID: %15

The ThingWorxAdoClient service terminated unexpectedly.  It has done this 15 time(s).

Terms & ConditionsAccessibility statement