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
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:
- Data connection string: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
- File Name of database (can be blank): Schema.mdb
- Path location of database (can be blank): c:\
- Sql Select Statement:
Select VALUE,DESCRIPT from ins_pick Where VAR='ITYP'
and [SERVICE] = '<SERVICE>'
Note: This statement reflects the fields available in the example Schema.mdb file. This may change depending on the database you are using.
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:
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
[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.