PostgreSQL support

Author: Sergey Sorokin

Published: 2011-07-29 16:09:00

Last update: 2013-10-20 07:26:11

Tags: HMI
SCADA
database
postgresql
sql
tag

Slug: PostgreSQL-support

Recently, we have tested CSWorks SQL LiveData provider (version 2.0.4115.0) against PostgreSQL database (connecting using npgsql 2.0.11.91), and it worked fine. Here are the settings we used in the CSWorks.Server.LiveDataService.config.  

Provider reference:

<system.data>
  <DbProviderFactories>
    ...
    <!-- Make sure that npgsql and its dependencies are accessible -->
    <remove invariant="npgsql"/>
    <add invariant="npgsql" name="PostreSQL Provider for .NET"
      description=".NET PostreSQL Provider for .NET"
      type="Npgsql.NpgsqlFactory, npgsql, Version=2.0.11.91, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
  </DbProviderFactories>
</system.data>

Data source description:

<sqlLiveDataSource name="Database01" sampleBufferLength="16" sqlProviderInvariantName="npgsql"
  connectionString="User Id=postgres;Password=pg123!;Server=localhost;Port=5432;Database=postgres;"
  updateRate="1000" maxQueryLength="65535" queryDelimiter=";" useZeroAndOneForBoolean="false">

Yes, you can use boolean data type to store discrete values, see our sample PostgreSQL table definition:

CREATE TABLE measurements(
PointName varchar(64) NULL,
Sensor1 int2 NULL,
Sensor2 smallint NULL,
Sensor4 int NULL,
Sensor8 bigint NULL,
Sensor1u int2 NULL,
Sensor2u smallint NULL,
Sensor4u int NULL,
Sensor8u bigint NULL,
Sensor10 float NULL,
Sensor50 varchar(64) NULL,
Sensor100 boolean NULL)
insert into measurements values('Point101', 1,2,4,8,1,2,4,8,8.0,'TestString',true)

Another SQL database under CSWorks' belt!