External Database Query Setup

The example below details how to apply an external database query to a specific property on a component. For this example we will be applying the query to the DEVICE_TYPE_CODE for a CONTROL_VALVE. Once defined, when you place a new instrument, the DEVICE_TYPE_CODE field will provide a drop down list with values to choose from which are stored in an existing external database. The same procedure can be followed for any property and any EC Class.

This example is being applied to OpenPlant PID schema but will work in OpenPlant Modeler as well.

Note: For this example we have used the default Schema.mdb file from a Plant Project database but the process can be used to pull data from any Access or Excel database.

Open the Bentley Class Editor and load the OpenPlant_PID.01.08.ecschema.xml schema.
Note: For OpenPlant Modeler Load the OpenPlant_3D.01.08.ecschema.xml scehma instead of the PID 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.

Note: Previously this was in the PID.01.08.ecschema.xml but was moved to the OpenPlant_ CustomAttribute.08.11 so that both OpenPlant PID and OpenPlant Modeler can use it.
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.
- External Data source Definition Mapping
- ExtendType
Remove the Value description attribute custom attribute if present.
Note: This will remove the pick list.
In the ExtendType attribute add the following string exactly as shown:

Important: The string is case sensitive.
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=
  Note: NOTE the connection string is case and space sensitive.
- File Name of database (can be blank): Schema.mdb
  Note: This can be replaced with the name of your database if you have one.
- 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.
  
  Note: A new filed SERVICE was added to the ins_pick table in Schema.mdb. Populate the Fields with the appropriate Service values that you intend to use in the project so that this query can filter on the basis of service.

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.

The CONNECT Edition of the Class editor coming with Connect edition has an known issue. It is unable to deal with an ECStruct array. So either use Notepad to edit the schema manually (shown below) to add "Property Mapping" structure or if you have the V8i Class Editor then use that for modification of the schema.

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>

Update the "Category" attribute so that Device Type Property is shown in Tag Information tab in the Placement dialog:
Limitations:
- This feature is only tested for String type fields and EC properties.
- This feature is only tested for "LIKE" and "=" operators in the SQL query.

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.

Paste the data columns into a new excel sheet and then select all the data along with the Header and select Format as Table option from the "Home" Task Bar.
Rename the sheet to Data.

The Excel database can now be queried using the query described above.

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;

Note: The server/instance name syntax used in the server option is the same for all SQL Server connection strings.

Sample database using the Microsoft SQL Server Management Studio with the credentials as:

The name of my sample database is toTest as:

And on the schema end everything remains the same except for the part where we make the connection string and query the database, it becomes:

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:

DATA SOURCE=Svrora12201:1521/orcl;DBA PRIVILEGE=SYSDBA;USER ID=sys;PASSWORD=mgr
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:

Note: Use this selection for connecting to Oracle Version > 10g.