Hi All, does anybody have experience using Postgre...

ptc-6227249 · ‎Oct 05, 2015

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

paic · ‎Oct 05, 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.

BryanK · ‎Nov 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

BryanK · ‎Nov 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()

Aanjan · ‎Nov 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?

BryanK · ‎Nov 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).

ptc-6227249 · ‎Nov 16, 2016

Bryan

Back at that time i got it working and if i dont remember wrong was with JDBC Driver not ODBC. I manage to connect to a remote PostgreSQL database without any problem.

i needed to import a template that some co worker help me get and just configure

JDBC driver class name: org.postgresql.Driver

JDBC connection string: jdbc:postgresql://IPorURL:PORT/DBname

my email is jortiz@ptc.com and feel free to send me an email and maybe i can share with you what i used to get it done.

regards.

BryanK · ‎Nov 16, 2016

Hi,

Thanks for the replys, I've managed to get it working using the JDBC connector. BUT...there always seems to be a BUT.

What I'm interested in is being able to run the ADO Service as an "edge server" that will handle the connections to TWX. I can then create a remote thing that I can easily see if its connected or not.

If there is a way to use the ADO to connect using the JDBC then happy days.

The other option is to then find a dummy's guide to LUA and spend many hours trying to figure out how to get sensor data..