Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
OpenPlant | AutoPLANT
  • Product Communities
OpenPlant | AutoPLANT
OpenPlant | AutoPLANT Wiki DBQuery : usage, logging, debugging
    • 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
        • -OpenPlant Isometrics Manager v8i
          • -OpenPlant Isometrics Manager for OpenPlant
            • Installation/Upgradation Steps for OpenPlant Isometrics Manager
            • +Errors, Logs, Messages, Crashes - OpenPlant Isometrics Manager
            • +Open/Save - OpenPlant Isometrics Manager
            • +Processing - OpenPlant Isometrics Manager
            • -Settings/Attributes - OpenPlant Isometrics Manager
              • +Configuring the BOM and Reports
              • -DBQuery : usage, logging, debugging
                • How to Get Updated Revision in Isometrics Drawing Using DBQuery
                • [Microsoft][ODBC Excel Driver] too few parameters. Expected 2
              • +Grid Lines - OpenPlant Isometrics Manager
              • Connectivity Information Lost in Isometric After Detaching/Attaching Reference Model
              • How To Add Detail Sketches in OpenPlant Isometrics Manager Using OpenPlant Modeler
              • How to Add NEW Schema Field to Component for Custom Tagging in OpenPlant Isometrics Manager
              • How to Adjust Imperial Accuracy in OpenPlant Isometrics Manager
              • How to Assign FieldFit Weld Allowances or Other Weld Types for BOM & Cut List in OpenPlant Isometrics Manager
              • How to Change Numbering Sequences For Spools, Welds & CUTID
              • How to Control Socket Weld Symbols in OpenPlant Isometrics Manager
              • How to Define Existing lines in OpenPlant Isometrics Manager
              • How to Delete Levels and Create Custom Level List in OPIM Seed File
              • How To Display Co-ordinates Using Multipliers
              • How to Display Nozzle Rating and Facing Information on Connection & Continuation in OpenPlant Isometrics Manager
              • How To Display Pipe Length using Multipliers
              • How To Feature Microstation Tags And AutoCAD Attributes
              • How to Format Data in Fractions Listed Under Drawing Tag Data
              • How to Get Exclusive Support PARTID Report with Prefix
              • How to Hide Sizes Of Bolts in Bill Of Material
              • How To Modify Valve Tag
              • How to Obtain Different Descriptions for Trimmed Elbow in OpenPlant Isometrics Manager BOM
              • How to Remove Information from Isometric Output
              • How to Rotate Support Symbols in OpenPlant Isometrics Manager
              • How to Round Off Values After Decimal For Support Co-ordinates
              • How to Split the Components on Different Description and to Avail Different PartID in BOM
              • Managing Isometrics Styles
              • +Modifying Dimensions
              • +Modifying Symbols in OpenPlant Isometrics Manager
              • +Modifying Text and Annotation Settings
              • +Modifying the Isometric Content
              • +Setting File Type (DGN or DWG) and Paper Setting
              • To Specify Various Imperial Value Output Type in OpenPlant Isometrics Manager
              • To Concatenate Length Property in Description Field for BOM
              • To Define Symbology for Multiple Component State in OpenPlant Isometrics Manager
              • To Display Handwheel Orientation For GearBox Operator in OpenPlant Isometrics Manager
              • To Display Multiple Rows for Notes as Labels
              • To Display New Property in Description Field For BOM in OpenPlant Isometrics Manager
              • To Display Right Angle Operator Direction with Spindle in OpenPlant Isometrics Manager
              • To Display Support Orientation in OpenPlant Isometrics Manager
              • Additional 3D.DWG file Created in OpenPlant Isometrics Manager
              • How to insert spaces between PARTID labels in OpenPlant Isometrics Manager
          • +OpenPlant Isometrics Manager for AutoPLANT
          • +Release Notes - OpenPlant Isometrics Manager
          • OpenPlant Isometrics Manager [FAQ]
        • +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!

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

    DBQuery : usage, logging, debugging

    Applies To
    Product(s): OpenPlant Isometrics Manager
    Version(s): N\A
    Environment:  N\A
    Area:  Settings/Attributes
    Subarea:  DBQuery
    Original Author:  Artiom Jakubovic, Bentley Product Advantage Group

    Background

    This section is dedicated to database query (DB Query) related issue. OpenPlant Isometrics Manager provides an option to link the database by writing queries inside textnodes.txt which could be found in styles folder. Each styles has its own customized Textnodes file. See below path for default project to get this file:

    For OpenPlant Modeler-OpenPlant Isometrics Manager, see below default path
    (C:\ProgramData\Bentley\OpenPlantModeler V8i\WorkSpace\Projects\OPModeler_Metric\DataSet\Isometrics\Styles\IFC\Config)

    For AutoPLANT Modeler-OpenPlant Isometrics Manager, see below default path
    (C:\ProgramData\Bentley\Plant V8i\Bentley Plant V8i Projects\SAMPLE_METRIC\Config\OpenPlant\Isometrics\styles\IFC\config)

    Below information help user to write queries related to database. Also the same piece of information can be found inside textnodes.txt file.

    #-----------------------------------------------------------------------------
    # A macro has the form of $(<name>) and resolves to the value of:
    # - another drawing attribute
    # - a configuration variable, example: $(IE_CONF)
    # - an OS environment variable, example: $(NUMBER_OF_PROCESSORS)
    #
    #-----------------------------------------------------------------------------
    # The DBQUERY has the format:
    # DBQUERY (<dsn>, <sql>)
    #
    # <dsn> is an ODBC data source specification and can reference a local DSN as
    # well as an ODBC connection string.
    #
    # Local DSN Examples: (DSN = PSDS 8.9 SI, username = proj, passwd = none)
    # - PSDS 8.9 SI
    # - PSDS 8.9 SI;proj
    # - PSDS 8.9 SI;proj;none
    #
    # Connection string examples: (network or mapped drive)
    # - Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=\\pluif7010reml\PSDS_Imperial\english.mdb;
    # - Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=Z:\english.mdb;
    #
    # Connection strings for almost every type of database can be found
    # at: http://www.connectionstrings.com/ 
    #
    # <sql> Any SQL statement
    #
    # Example:
    # IE_DSN = PSDS 8.9 SI;proj;none
    # code = 2605-2
    # MyVar = DBQUERY ($(IE_DSN), select * from code_desc where code='$(CODE)')
    #
    #-----------------------------------------------------------------------------
    # The DATE funtion has the format 
    # DATE (<date style>, <date format>)
    # <date style> 0 - US style (month, day, year)
    # 1 - European style (day, month, year)
    # <date format> examples shown for US style (January 2 2014)
    # 0 - 1-2-14
    # 1 - 01-02-14
    # 2 - 1-2-2014
    # 3 - 01-02-2014
    # 4 - 1/2/14
    # 5 - 01/02/14
    # 6 - 1/2/2014
    # 7 - 01/02/2014
    # 8 - 20140102
    #-----------------------------------------------------------------------------

    Logging

    The extended drawing attribute (DBQUERY) logging capability has been improved in order to help debug DBQUERY related issues.

    The standard production log will list the following messages
    • The location of the textnodes.txt definition file:
    • Warning in case of drawing attribute redefinition
    • DBQUERY related errors

    Debug

    By adding DEBUG = 1 in textnodes.txt the logging will additionally list:

    • The definitions present in textnodes.txt
    • The drawing attributes defined by OpenPlant Modeler or AutoPlant Modeler
    • Extended reporting on DBQUERYs

    When a select statement result in more than one result column a list of column names will be produced. This is great for debugging EXCEL type queries. It is best used in combination with DEBUG = 1. In order to list all column names one would use a SQL statement like:

    Select * from <table name>

    Drivers

    OpenPlant Isometrics Manager needs 32 bit ODBC drivers. In case DBQUERYs are used on Microsoft products like Access or Excel one must check if the Windows OS contains recent 32 bit ODBC drivers.

    Start this application: C:\Windows\SysWOW64\odbcad32.exe

    Go to the drivers tab and check if the following driver is present:

       

    Note: If not it is advised to install a more recent driver set, to be found here - >>>>

    When installing it is referred to as Microsoft Access database engine 2010. It does however also installs Excel drivers.

    Using DBQUERY on Excel spreadsheets

    The general format of a DBQUERY statement is:

    <DA_NAME> = DBQUERY ( <ODBC specification>, <SQL statement>)

    With:

    DA_NAME                   The name of the drawing attribute that will hold the result
    ODBC Specification    A reference to an ODBC connection (connection string)
    SQL statement            The statement to retrieve data

    Excel ODBC connection string examples

    Connection string for excel spreadsheet linelist.xls located in c:\tmp\excel

    DB_1 = Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=c:\tmp\excel\linelist.xls

    Connection string for excel spreadsheet linelist.xls located on a server

    DB_2 = Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=\\Pluif7010reml\excel\linelist.xls

    Note -  Never locate the spreadsheet in the drive or server root. Don’t use for instance

    DBQ=c:\linelist.xls
    DBQ=\\Pluif7010reml\linelist.xls

    Note -   Always use a variant of this driver: Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}

    ODBC driver interpretation of the Excel spreadsheet

    Work area

    The ODBC driver limits the work area to an area with non-empty cells. For the spreadsheet shown below the work area is D4:F7.



    Note - This is not an absolute truth. Depending on previous editing actions in Excel the work area may be larger than expected

    Column names

    The first row of the work area specifies the column names. When a cell in the first row is empty a column name is assigned in the format of F<column index>. When the first row partly consists of merged cells the ODBC driver uses an alternative view. In case one wants to be sure what the actual column names are it is suggested to use a “Select *” on the sheet (see logging remarks”

    Example #1: a “normal” spreadsheet

      is interpreted as  

    Note - The right hand shows the column name in the shaded top row

    Example #2: A spreadsheet with some empty header cells

      is interpreted as 

    Note: Third column is named F3

    Example #3: A spreadsheet with partly merged cells in the first row

      is interpreted as

    Note - In this case one just follows the uppermost cells for the column name

      is interpreted as 

      is interpreted as 

    DBQuery Examples

    DSN_2 = Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=c:\tmp\excel\linelist.xls
    A = DBQUERY ($(DSN_2), select [COL1] from [linelist all specs $] where [line number] = ‘$(LINE_NUMBER )’)
    B = DBQUERY ($(DSN_2), select [F5] from [linelist all specs $] where [line number] = ‘$(LINE_NUMBER )’) 

    → The sheet name is specified in square brackets and using a $ sign: select [COL1] from [linelist all specs $] where [line number] = ‘$(LINE_NUMBER )’
    → Always enclose the column names in square bracket pairs:   Select select [COL1] from [linelist all specs $] where [line number] = ‘$(LINE_NUMBER )’

    See Also

    Product TechNotes and FAQs

    OpenPlant Isometrics Manager [FAQ]

    External Links

    Bentley Technical Support KnowledgeBase

    Bentley LEARN Server

    Comments or Corrections?

    Bentley's Product Advantage Group requests that you please confine any comments you have on this Wiki entry to this "Comments or Corrections?" section. THANK YOU!

    • Settings/Attributes
    • AutoPLANT Modeler
    • AutoPlant V8i
    • OpenPlant Isometrics Manager
    • SELECTsupport
    • Openplant
    • Share
    • History
    • More
    • Cancel
    • Artiom J Created by Artiom J
    • When: Mon, Dec 15 2014 11:54 AM
    • Kestutis Mitkus Last revision by Bentley Colleague Kestutis Mitkus
    • When: Mon, Dec 17 2018 1:21 PM
    • Revisions: 13
    • 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