Trouble integrating PostgreSQL database into TestStand

[dan100]

Hi all,

I have been having some trouble integrating PostgreSQL (v9.4) database into TestStand. I have installed the PgOleDB (v1.0.0.20) driver and this registers in the 'Data Link Properties' dialog box when building the connection string as 'PostgreSQL OLE DB Provider' (see Postgres_connect1.png) and I think I have got the connection string correct as can test it and it passes (see Postgres_connect2.png). I also had some luck using the built in database step types - modifying the connection string in the 'Database Step Types.seq' example provided. I used the same connection string in the 'Open Database' step and it connects ok. Then I modified the SQL commands in the 'Drop Table' and 'Create Table' and checked that they were created in my postgres database (see Database Example 1 and 2 attached. So I know the connection string must be right. But when I go back to turning on the integrated database logging and run a simple sequence I get the following error:  

"An error occurred calling 'LogResults' in 'ITSDBLog' of 'zNI TestStand Database Logging' An error occurred initializing a connection to a data link. Connection String: Provider=PostgreSQL OLE DB Provider; Data Source=localhost; Location=teststand; User ID=postgres; Item cannot be found in the collection corresponding to the requested name or ordinal.  Source: TSDBLog   -2146825023; User-defined error code.  Step 'Log Results to Database' of sequence 'LogToDatabase' in 'NI_DatabaseLogger.seq'"  

So it seems like a problem connecting to the database now when connecting using the integrated logging. I have tried to debug the sequence but it gets to the 'LogToDatabase' step in 'NI_DatabaseLogger.seq' and this is an Active X object which you can't step into. So I'm not sure what the difference here is or how to debug it? I know it's not a directly supported DBMS but just wondering if anyone has had some experience with this or could give me some advise on how to debug it?  

Thanks

Daniel

[Aleramir]

There could be a compatibility issue between  PostgreSQL and TestStand. I does seem to recognize the database but some of the functions may not be supported by TestStand. I have no experience using this DBMS but I would recommend you to use a supported one. 

[Bilby42]

Hi dan100,

Did you ever solve this problem? I've got exactly the same errors using PostgreSQL, and cannot change to a different DBMS as our company is using this for our database system.

Regards,

Tom

[Bumblebee_Tuna]

Take a look here at this forum post discussing using PostgreSQL in TS: 

http://forums.ni.com/t5/NI-TestStand/Database-loogging-with-TS-postgresql/td-p/1599862

[Bilby42]

Thanks for replying BumbleBee,

Had already seen this post.

However since posting my message I've made considerable progress using PostgreSQL with TestStand 2014. I'm still not totally out of the woods, but at least I can now write to PostgreSQL tables with the teststand database. My initial error was using the wrong connection string. This was generated by the TestStand "Build" function, and although the "Test Connection" button showed no errors the connection string was still wrong.

The connection string I'm using successfully points to a different driver as follows (use the exact syntax);

"Driver={PostgreSQL ANSI}; Server=localhost; Port=5432; Database=your_database_here; Uid=your_username_here; Pwd=your_password_here;"

Obviously replace the "your_xxx" with your information. Fixing the connection string has allowed me to write to my tables, although I'm still struggling with autogenerated primary keys - Teststand tries to overwrite them for some reason.

Regards,

Tom