PostgreSQL and methods of accessing it in MAPS/SmartUI

[damiand]

Similarly to MySQL , PostgreSQL database has no straight-forward OLEDB plugin and there is need to either use scripting or ODBC to access to it. Here are 2 sample methods (there are also some 3rd party methods, but not tested by us):

1. npgSQL in graphic form/server script. This one is for data representation. It is basically the same case as in MySQL. Just remember when installing npgSQL on Your machine to add it to GAC to have npgsql.dll in Your system assemblies instead of using just nuget.

Add references to Your graphic form script:

using Npgsql;
using System.Data;

Now draw a datagridview on Your graphic form, add a button, expose the datagridview to script and double-click on the button to create a click event inside the script.
All is left to do is to put Your PostgreSQL connector code behind the button_click event.

DataSet ds = new DataSet();
NpgsqlConnection conn = new NpgsqlConnection(“Server=localhost;Port=5432;User Id=postgres;Password=password;Database=MAPS;”);
try
{
conn.Open();
string sql = “SELECT “DT”, “Value1” FROM public.“MAPSTest””;
NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
da.Fill(ds);
dataGridView.DataSource = ds.Tables[0];
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
conn.Close();

Done! Remember to properly address Your schemas and queries - it is little bit different from other databases.

2. ODBC in DBAccess this one is for logging/interaction with agents using DBAccess agent and ODBC connector. Now, remember that our software is 32-bit, hence it requires 32-bit ODBC connector and configured dns in 32-bit version of Your windows ODBC configurator.
   Pick from the postgreSQL corresponding version to Your server and install it over.
   Take a look at this screenshot of sample PostgreSQL configuration in DBAccess (I had to add “” qotation marks to Table and Column names even that from pick list it didn’t fill in these characters. Also, custom date formatting was required and that can be checked in Help topic about DBAccess agent - field format specifiers).
   
   

[Frits]

It is also possible to log Alarms to PostgreSQL:

1. First create the PostgreSQL ODBC connection as shown in the abovementioned post

• The PostgreSQL StackBuilder can be used to install the correct 32 bit version of ODBC for Postgres:
  
• Once this is downloaded, install the ODBC driver:
  
• Then create a ODBC 32 bit System DSN:

2. Create an appropriate Table in the PostgreSQL Database that will contain the Alarm Data, for example below we have defined three columns - AgtFld, AgtData, EvtTime:

3. Create an EventOutput Agent that is configured for this Database:

4. Make sure that the columns are defined:

5. Start the EventOutput Agent

NOTE: Make sure only Size printing is Enabled. The other methods must be Disabled.

6. Add the EventOutput Agent to an Alarm Route

7. Make sure this route is alarmed, for the Agents required, for example:

8. Now all alarms on this route will also be sent to the PostgreSQL database