Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
OpenPlant | AutoPLANT
  • Product Communities
OpenPlant | AutoPLANT
OpenPlant | AutoPLANT Wiki Fundamentals of External Database Query in OpenPlant Modeler
    • Sign In
    • -OpenPlant | AutoPLANT Wiki
      • -OpenPlant
        • +CONNECT Services - OpenPlant
        • +Example Configuration - CONNECT Edition
        • +Installations - OpenPlant CONNECT Edition Applications
        • +OpenPlant Download Links & Videos
        • OpenPlant Product Compatibility
        • +OpenPlant Modeler CONNECT
        • +OpenPlant PID CONNECT
        • +OpenPlant Isometrics Manager CONNECT
        • +OpenPlant Orthographics Manager CONNECT
        • +OpenPlant Support Engineering CONNECT
        • +OpenPlant Project Administrator CONNECT
        • +OpenPlant CONNECT Edition-PlantSight Integration
        • -OpenPlant Modeler v8i
          • +Component - OpenPlant Modeler
          • +Drawing Generation- OpenPlant Modeler
          • +Errors, Logs, Messages, Crashes - OpenPlant Modeler
          • +Import/Export- OpenPlant Modeler
          • +Installation - OpenPlant Modeler
          • OpenPlant HVAC-OpenPlant Modeler
          • OpenPlant Modeler [FAQ]
          • +OpenPlant Specification Generator
          • +ProjectWise - OpenPlant Modeler
          • -Properties - OpenPlant Modeler
            • -Fundamentals of External Database Query in OpenPlant Modeler
              • How to Add Database Connection String Variable in Managed Workspace
              • Populate Service Code in Pipeline Based on Service Description
            • How to Add Component's Facing Property in Element Information dialog
            • How to Add Inch Sign with Nominal Diameter in Pipeline Tag
            • How to Add New/Existing Property to Nozzle Manager Dialog in OpenPlant Modeler
            • How to Add/Remove Properties in Flyover Hints
            • How to Associate the Specification Based on Service
            • How To Change Attributes of Pipe Based on Component State Using Named Expression
            • How to Change the Units of Measurement for ECProperty in OPM
            • How To Create and Populate Service Description Property on Piping Component Element Information
            • How to Exclude Plant Area Property From Consistency Checker
            • How to get Joint Type Information on Gaskets
            • How to Identify Inconsistency of Component Data with Consistency Checker
            • How to Map Spec Field with OpenPlant Modeler Property
            • How to Modify Supports in Bulk in OpenPlant Modeler
            • How to Move Properties Between Categories
            • How to Populate Header Nominal Diameter for Tap Connection in OpenPlant Modeler
            • How to Prefix 'DN' with Pipe Size in OPM
            • How to Set Default Value on EC Property
            • How to Show Nominal Diameter in Three-Digit Format for Support Tag Name
            • How to Update Bolts and Gaskets in OpenPlant Modeler
            • How to Update Pipeline Tag Name or Component Property After any Changes in Schema
            • To Modify Equipment Tag For FreeForm Nozzle in OpenPlant Modeler
            • To Modify Pipeline Tag Name in OpenPlant Modeler
            • To Obtain Arc Length for Pipe Bends in OpenPlant Modeler
          • +Release Notes - OpenPlant Modeler
          • +Schema - OpenPlant Modeler
          • +Settings/Attributes - OpenPlant Modeler
          • +Structural Modeling Components (SMC)- OpenPlant Modeler
          • +Tasks - OpenPlant Modeler
          • +Training - OpenPlant Modeler
        • +OpenPlant Isometrics Manager v8i
        • +OpenPlant Project Administrator v8i
        • +OpenPlant PID v8i
        • +OpenPlant Orthographics Manager v8i
        • +OpenPlant Support Engineering v8i
        • +OpenPlant ModelServer v8i
        • +OpenPlant Reporting v8i
        • How to Connect with Bentley Technical Support Team using Microsoft Teams
        • How to Connect with Bentley Technical Support Team using Microsoft Teams via Browser
        • How to get list of installed applications/programs on Windows Machine using Command Prompt.
        • How to get list of Windows Updates installed on the machine using Command Prompt.
        • How to Package WorkSpace to be Shareable to Users for OpenPlant Projects
        • How to find iModel.dgn file of another domain in PlantSight
      • +Bentley AutoPLANT
      • +Bentley Navigator
      • +Bentley PlantSpace
      • +Plant Project Tools
      • +Bentley Raceway and Cable Management
      • i-model Composition Service for S3D
      • Working from home with OpenPlant
      • Working from home with Bentley Raceway and Cable Management
      • Working from home with AutoPLANT Modeler
      • Your Feedback is important to us!
    • Learning Content for OpenPlant

     
     Questions about this article, topic, or product? Click here. 

    Fundamentals of External Database Query in OpenPlant Modeler

    Applies To
    Product(s): OpenPlant Modeler
    Version(s): 08.11.09.XXX (SS5) | 08.11.11.XX (SS6)
    Environment: Windows 7/8.1/10 (64 bit)
    Area: Properties
    Subarea: External Database Query
    Original Author: Harpreet Singh, Bentley Product Advantage Group

    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. 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

    Play this video
    Deepak Singh
    How to Use External DB Query in OpenPlant Modeler
    By Deepak Singh over 4 years ago in OpenPlant | AutoPLANT > OpenPlant | AutoPLANT Files

    • 08.11.11.xxx
    • OpenPlant Modeler
    • External database
    • Concept of External Database in OpenPlant Modeler
    • Select statement
    • 08.11.09.XXX
    • OpenPlant Modeler External DB
    • query
    • 08.11.09.xxxx
    • Custom Attribute
    • Share
    • History
    • More
    • Cancel
    • Harpreet Singh Created by Harpreet Singh
    • When: Sat, Oct 15 2016 9:05 AM
    • Deepak Singh Last revision by Deepak Singh
    • When: Thu, Jan 3 2019 11:52 AM
    • Revisions: 4
    • Comments: 0
    Recommended
    Related
    Communities
    • Home
    • Getting Started
    • Community Central
    • Products
    • Support
    • Secure File Upload
    • Feedback
    Support and Services
    • Home
    • Product Support
    • Downloads
    • Subscription Services Portal
    Training and Learning
    • Home
    • About Bentley Institute
    • My Learning History
    • Reference Books
    Social Media
    •    LinkedIn
    •    Facebook
    •    Twitter
    •    YouTube
    •    RSS Feed
    •    Email

    © 2023 Bentley Systems, Incorporated  |  Contact Us  |  Privacy |  Terms of Use  |  Cookies