External Database Query Setup
This example is being applied to OpenPlant PID schema but will work in OpenPlant Modeler as well.
- Open the Bentley Class Editor and load the OpenPlant_PID.01.08.ecschema.xml schema.
-
Next, load the OpenPlant_CustomAttribute.08.11.ecschema.xml
schema.
This will let you add the custom attribute EXTERNAL_DATSOURCE_DEFINITION_MAPPING to the DEVICE_TYPE_CODE property.
- Search for the Control Valve class and click the DEVICE_TYPE_CODE from the Properties tab.
- Override the Device Type Code property as it is disabled.
- Add the following custom attributes to the DEVICE_TYPE_CODE property.
- Remove the Value description attribute custom attribute if present.
- In the ExtendType attribute add the following string exactly as shown:
- In the External Data source Definition Mapping attribute, fill out the field with information as follows:
SQL Query
User can define the whole query including, file to be queried, table name, whereclause, and display order. It should be a standard SQL query with the following exceptions.
- All the field names in the whereclause should be incapsulated in square brackets "[FIELD NAME]".
- All the EC Property names which are the placeholder for their actual values should be encapsulated in greater than and lesser than signs. '<EC PROPERTY NAME>'.
- The EC Property names and FIELD names are also case sensitive use uppercase in both.
Attention: In notepad this needs to be added to the "EXTERNAL_DATSOURCE_DEFINITION_MAPPING" custom attribute:
<PROPERTY_MAPPING>
<UPDATE_DEFINITION_STRUCTURE>
<TABLE_FIELDNAME>VALUE</TABLE_FIELDNAME>
<ECPROPERTY>DEVICE_TYPE_CODE</ECPROPERTY>
</UPDATE_DEFINITION_STRUCTURE>
</PROPERTY_MAPPING>
Query Data from Excel Files
For an Excel query all the steps are the same except for the query statement. In Excel, you have to define the query in a way that Excel understands it and can access data from the sheets. "Data" as defined in the example below is the sheet name.
-
In the SQL Select Statement field enter the following sample
query:
Select [Data$].VALUE,[Data$].DESCRIPT from [Data$] Where [Data$].VAR='ITYP'
OR, for a more detailed query the statement below searches for the value from a specific (SERVICE) column in the database:
Select [Data$].VALUE,[Data$].DESCRIPT from [Data$] Where [Data$].VAR='ITYP' and [Data$].SERVICE = '<SERVICE>'
To setup your data in the excel sheet you have to make put data into a table.
Connecting to SQL Server Databases using External Database Query
In order to connect to a SQL Server Database using the external database query, the connection string can be formed in the following ways:
-
Standard security:
Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;
-
Trusted connection:
Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;
-
Connection to a SQL Server instance:
Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;Password=myPassword;
Connecting to Oracle Databases using External Database Query
In order to connect with an Oracle Database using External Database Query, firstly you must have Oracle Client installed on your machine, where the databases shall reside.
The connection string in the following case is formed as:
Where "Svrora12201" is the Hostname, "1521" is the Port and "orcl" is the Service name. The data source is followed by the DBA (Database administrator) privilege, followed by the User ID and the Password.
One can simply get this information from the properties of the database connection.
And in the class editor it becomes: