Fundamentals of External Database Query in OpenPlant Modeler

Background

This article explains the fundamentals of external database query in OpenPlant Modeler. Basically, it requires a new custom attribute in the schema which will help in pulling the values from external database (Access/SQL) through select statements.

Steps to Accomplish

1. Launch Class Editor and load OpenPlant_CustomAttributes.08.11 schema. This schema is located in C:\ProgramData\Bentley\OpenPlantModeler V8i\WorkSpace\OPModeler folder.
   
   
2. Right click on the class node and click Add Class.
   
   
   
   
3. Name the class as EXTERNAL_DATABASE_QUERY. Change “IsCustomAttributeClass” to True.
   
   
   
   
4. Switch to properties tab and click ADD button to add new property.
   
   
   
   
5. Name the property as CONFIG_VARIABLE_FOR_CONNECTION_STRING.
   
   
   
   
6. Similarly add three more properties as shown below.
   
   
   
   
   
   Note: Change the Type to Boolean as highlighted above.
   
   
   
   
7. SAVE the changes in this schema.

How External Database Query Custom Attribute Works

Sample custom attribute with values

1. Config. Var. for Connection String: This contains the configuration variable defined in the project pcf. File, which holds the database connection information. Below is the example of added variable in PCF file.
   
   
   
   
2. Default Value: This should be the string which user wants to appear as the property value, when its associated SQL query returns empty result. Which can be the case if the depended values in the where clause are invalid.
   
   
   1. Note: Default value cannot be blank.
      
      
3. Pick First Entry from the List: It’s a boolean type property and defines the default behavior of the pick list selection. The explanations of its values are as follows.
   
   
   1. True: If set true, based on the query results on first launch or if any value change for the property it SQL query depends on, it will automatically select the first entry from the results and set it as the selected value. This will save the clicks on the user side when there is always 1 result to be selected like description etc.
      
      
   2. False: If set false. On first launch of ec data pan or, change in value of any ec property which is the part of its where clause. Its value will be reset to “Default Value”. And will force the user to select the right value from the drop down. That is to save the user from scenario of overlooking the wrong value selection.
   
   
   
4. SQL Query: User can define the whole query, including, field to be queried, table name or view name, where clause and display order.

1. 1. All the field names in where clause should be encapsulated in the square brackets like [FIELD_NAME].
      
      
   2. All the EC Property names which are the placeholder for their actual values should be encapsulated in double quotes and angle brackets. “<EC_PROPERTY>”.

See Video