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

Debug

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

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!