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.