Product(s):	Bentley WaterGEMS
Version(s):	V8i, Connect Edition
Area:	Layout and Data Input

Problem

Is there way to connect to sqlite database using SCADAConnect?

Solution

Sometimes as a temporary solution to check the data you may want to connect to a SQLITE database using SCADAConnect. For that purpose, you can select ODBC Source as the data source type in the SCADA signals editor in SCADAConnect.

Select SCADA Signals from Analysis > SCADAConnect Simulator > SCADA Signals > Database Source > ODBC Source > Custom connection string.

You can select the custom connection string for ODBC source as follows

Driver={SQLite3 ODBC Driver};database=C:\PathToDatabase\Database.db

When using Julianday function in SQL statements date formats

If the date format used in the queries is formatted to use function julianday and for this julianday function to work, the jdconv=1 setting is required in the connection string (jdconv => enable julian day conversion).

Example of custom connection string in that case is - Driver={SQLite3 ODBC Driver};database= C:\PathToDatabase\Database.db;jdconv=1

Also here is how SQL statements would be formatted if you are using julianday function.

Additional Settings

In the advanced settings in the Database connection window, select Single Quotes, for SQL Date / Time Query delimiters (the default value is #).

Update SQL Statements as shown below

The column names, Tag, Value, and the DateTime might be different from database to database (as per your database created). Based on the way datetime is stored in the database, the query below might need some adjustment, see the “yyyy-MM-dd HH:mm:ss” part.

select [tag],[Value],[DateTime]

from [SCADAData]

where [tag] in (@requestedsignals)

and [DateTime]>=datetime(@startdatetime("yyyy-MM-dd HH:mm:ss"))

and [DateTime]<=datetime(@enddatetime("yyyy-MM-dd HH:mm:ss"))

With these changes you should be able to import data from a SQLITE database using SCADAConnect.

Connecting to a SQLITE data source in SCADAConnect

Problem

Solution

See Also