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#-----------------------------------------------------------------------------
The extended drawing attribute (DBQUERY) logging capability has been improved in order to help debug DBQUERY related issues.
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>
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.
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 resultODBC 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.xlsDBQ=\\Pluif7010reml\linelist.xls
Note - Always use a variant of this driver: Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}
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
Note: Third column is named F3
Example #3: A spreadsheet with partly merged cells in the first row
Note - In this case one just follows the uppermost cells for the column name
DSN_2 = Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=c:\tmp\excel\linelist.xlsA = 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 )’
Product TechNotes and FAQs
OpenPlant Isometrics Manager [FAQ]
Bentley Technical Support KnowledgeBase
Bentley LEARN Server
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!