Database Import Module
The Database Import Module provides a mechanism for extracting data from a Power Monitoring Expert or third-party SQL Server database, making query results available within the Virtual Processor for further processing. This module is only available in the VIP.
Module icon
Overview
When the Execute Now input is pulsed, this module queries the specified database using a user-defined query. The results of the query are sent to the output registers. Numeric results are loaded into the numeric outputs, and text results are loaded into the String outputs.
This module can be used to perform a number of advanced functions, such as:
- Importing data from other SQL Server databases for use in KPI calculations.
- Obtaining values at specific times.
- Calculating specific KPIs or analytical indicators.
This module is intended for advanced users with knowledge of database connection strings, SQL query syntax and query result sets.
NOTE: The Power Monitoring Expert databases are not guaranteed to be consistent between releases. You should use the database views that are provided rather than querying the tables directly.
Inputs
Argument 1–24
These optional inputs can be linked to other module outputs. The inputs allow external values to be included in the SQL Query.
Enable
This input is optional; if you leave it unlinked, the module is enabled by default. This input enables or disables the module. If the module is disabled, the output values are set to not available, and the module stops processing Execute Now input.
Execute Now
When the Execute Now input receives a pulse, a database connection is made using the information in the Connection String setup register. If the database connections succeeds, the query defined in the SQL Query setup register is executed. When the query completes, the appropriate output registers are populated. The Execute Now input should be directly linked to the pulse output register of the ION module that produces the desired trigger condition.
If the subsequent Execute Now pulses are received while a previous database query is still in progress, the pulses are ignored.
Setup registers
Connection String
This register holds the database connection string and must include the following bits of information:
- Provider
- Data Source
- Initial Catalog
- User ID
- Password
The following is an example of a complete connection string used to connect to a SQL Server instance called STANDALONE/ION and a database name ION_Data, using an ION database user called ION and its associated password:
Provider=SQLOLEDB; Data Source=STANDALONE\ION;
Initial Catalog=ION_Data; User Id=ION; Password= <actualpassword>
NOTE: The connection string includes a plain text password. Click Encryption in the configuration dialog in Designer and click Yes on the message dialog to encrypt the connection string and protect the Virtual Processor configuration file. Click No to cancel the encryption and to close the message dialog.
For convenience, the module also recognizes <ION_Network> and <ION_SystemLog> as a connection string short form for connecting to the respective database.
SQL Query
This register holds the SQL query that will be run once the database connection is established. There are a number of ways this register can be configured. You can just type in the SQL query that will return the result set you are interested in.
You can build up the SQL Query dynamically by using @1, @2 ….@24 as placeholders. These placeholder tokens are replaced with the value of the registers linked to the respective inputs for Arguments 1-24, that is, @1 will be replaced by the value in the Argument 1 input, and so on.
For example, the following query returns the values from the datalog table where the quantity name equals the value of @1, the source name equals the value of @2, and the value is less than the value of @3.
Note that the parameters must be the correct datatype to avoid an error in the query. That is, @1 and @2 are strings, and @3 is a float.
SELECT
|
|
|
value
|
FROM
|
|
|
datalog2 d
|
|
JOIN quantity q on q.id = d.QuantityID
|
|
JOIN source s on s.id = d.SourceID
|
WHERE
|
|
|
q.name = @1
|
|
AND s.name = @2
|
|
AND d.value < @3
|
Note that only the first column returned from the SQL Query is mapped to the respective output. All other columns are ignored. For this reason, it is best to construct queries that only return a single column of data.
Event Priority
This numeric bounded register allows you to set the event priority of a database connection or SQL query error from 0 (lowest priority) to 255 (highest priority).
Output registers
Numeric Out 1-32
These registers contain up to 32 results from the SQL query if the returned data type is numeric or Boolean.
String Out 1-32
These registers contain up to 32 results from the SQL query if the returned data type is string.
Processing Time
This register contains the time in seconds that the query took to execute. It can be used for diagnostic purposes. For example, longer query times can indicate a potential for query optimization.
Load Complete
When the database results are available, the Load Complete register is pulsed.
Event
Any events produced by the Database Import Module are recorded in the Event register.
Possible events and their associated priority numbers are shown in the following table.
| Event priority group | Priority | Description |
|---|---|---|
| Setup Change | 10 | Input links, setup registers or labels have changed. |
| Database Import | 25 (default) | Database results could not be parsed. Could not query the database. |