bentley i-model odbc driver
Post on 10-Oct-2015
293 Views
Preview:
DESCRIPTION
TRANSCRIPT
-
5/20/2018 Bentley I-model ODBC Driver
1/28
i-model ODBC Driver for
Windows 7
User Manual
Access business data in i-models through ODBC
BENTLEY SYSTEMS, INC.
www.bentley.com
-
5/20/2018 Bentley I-model ODBC Driver
2/28
-
5/20/2018 Bentley I-model ODBC Driver
3/28
Bentley i-model ODBC Driver User Manual (- 3 -)
Table of Contents
About ......................................................................................................................................................................... 4
Terms ......................................................................................................................................................................... 4Drivers (32-bit vs. 64-bit).................................................................................................................................. 5
Installation .............................................................................................................................................................. 6
Configuration .......................................................................................................................................................... 8
Opening the Configuration Panel ............................................................................................................... 8
Working with the Configuration Panel .................................................................................................... 9
Accessing Data By Class or Saved Query ............................................................................................... 10
Identification of Business Keys ................................................................................................................. 12
Using the i-model ODBC Driver for Windows 7 ...................................................................................... 14 Working with Microsoft Excel ................................................................................................................... 15
Working with OBDC-Compliant Databases .......................................................................................... 20
Working with SAP Crystal Reports ......................................................................................................... 24
Using VBA for Automation ......................................................................................................................... 27
Developing Applications with ADO.net ...................................................................................................... 27
Legal Notices ......................................................................................................................................................... 28
-
5/20/2018 Bentley I-model ODBC Driver
4/28
Bentley i-model ODBC Driver User Manual (- 4 -)
About
The i-model ODBC Driver for Windows 7 is a product used to expose business data in i-models as anODBC data source. It offers direct access of i-models from ODBC-compatible third-party
applications such as Microsoft Office, reporting systems, and Business Intelligence applications. It
allows application developers to use familiar tools to access and present i-model data fromdatabases.
Terms
The table below shows how an i-model and its contents map to an ODBC data source and related
database components.
i-model ODBC data source
File Name Database (name normalized . To _) 1
Schema Schema (name normalized . To _) 1
Class or Saved Query Table2
Property Column (name normalized . To _)3
Provenance Provenance table and Provenance Extension table
Data typeSQL data type
4
1The . character in the name is replaced with the _ character, e.g., plant.i.dgn becomes
plant_i_dgn.
2 Class names and file names of saved queries are used as table names. If a table name conflicts with
an ODBC reserved keyword, a _ prefix is added.
3If a property has a display label associated with it, the label is used as the column name; otherwise,
the name of the property is used. In both cases, the name is normalized and if it exceeds 64
characters in length, a shorten name is created. NOTE: Field names must be unique.
4 Array and Struct data types are converted to SQL text and their values delimited by | .
-
5/20/2018 Bentley I-model ODBC Driver
5/28
Bentley i-model ODBC Driver User Manual (- 5 -)
Drivers (32-bit vs. 64-bit)
The Bentley i-model ODBC driver is available in both a 32-bit and 64-bit version. The 32-bit
driver will only run with 32-bit applications. Similarly, the 64-bit driver will only run with
64-bit applications.
Note: It is very common for 32-bit applications to run on a 64-bit Windows system; for
example, 32-bit Microsoft Office runs on 64-bit Windows 7. If you run a 32-bit
application on a 64-bit Windows system, the 32-bit i-model ODBC driver is needed.
Keep this in mind as you configure the corresponding data source on your system.
Also, when working with extremely large i-models, you may need to use the 64-bit driver to
overcome the memory limitations of 32-bit applications.
-
5/20/2018 Bentley I-model ODBC Driver
6/28
Bentley i-model ODBC Driver User Manual (- 6 -)
Installation
Bentley provides 32-bit and 64-bit installers. The 32-bit installer installs the 32-bit driver, which
works with 32-bit applications. The 64-bit installer installs 64-bit drivers.
Sample i-models and a sample C# ado.net project for creating customized applications is deliveredwith the i-model ODBC Driver for Windows 7. The default location to which the data is installed ispresented and can be redefined during installation.
-
5/20/2018 Bentley I-model ODBC Driver
7/28
Bentley i-model ODBC Driver User Manual (- 7 -)
During installation, a Bentley shortcut is created under the Start menu, if one does not already
exist. An i-model ODBC Driver for Windows 7 shortcut is also added under the Bentley root
shortcut, which includes shortcuts for the delivered sample C# ado.net project and this UserManual.
-
5/20/2018 Bentley I-model ODBC Driver
8/28
Bentley i-model ODBC Driver User Manual (- 8 -)
Configuration
Opening the Configuration Panel
An i-model datasource system DSN is created during installation, which can be used for accessingthe business data contained in an i-model. Additional data sources can also be created and used ifdesired.
To configure a 64-bit data source on 64-bit Windows 7 or 32-bit data source on 32-bit Windows 7,
use the Windows ODBC configuration panel. Note: To configure a 32-bit data source on 64-bit
Windows 7, run c:\windows\syswow64\odbcad32.exe. This Windows application brings up 32-
bit ODBC configuration panel on 64-bit Windows 7.
Procedure to open the Windows ODBC configuration panel on Windows 7:
1. From the Windows Start menu, select Settings > Control Panel.
2. In the Control Panel window, select System and Security and open Administrative Tools.
3. Double-click Data Sources (ODBC).
-
5/20/2018 Bentley I-model ODBC Driver
9/28
Bentley i-model ODBC Driver User Manual (- 9 -)
Working with the Configuration Panel
1. In the ODBC Data Source Administrator dialog, open the System DSN tab and select thei-model data source.
2. In the Data Source Configuration dialog, set up the default folder for i-models, identify baseclasses to be exposed and specify the folder containing saved item queries to be executed.
NOTE: Multiple base classes can be specified and must be comma delimited. Also, onlythose base classes and saved component queries that are applicable to the opened i-model
will be presented and available for selection.
-
5/20/2018 Bentley I-model ODBC Driver
10/28
Bentley i-model ODBC Driver User Manual (- 10 -)
Accessing Data By Class or Saved Query
The items in an i-model are exposed based on their class. When a class is selected (e.g.,CHECK_VALVE), all items of that class are returned. In this example, all check valves contained in
the open i-model will be returned.
Items can also be accessed based on a higher level classification, defined by a base class (e.g.,
VALVE). When a base class is selected, all items of classes derived from the base class are returned.In this example, all valves will be returned, including check, gate and other valve types.
-
5/20/2018 Bentley I-model ODBC Driver
11/28
Bentley i-model ODBC Driver User Manual (- 11 -)
NOTE: The base classes exposed are defined in the i-model Data Source Configuration dialog and only
those which pertain to the opened i-model will be displayed.
Items can be accessed based on a saved item query (e.g., all pipe items belonging to a specific
process line). When a query is selected, all items which meet the criteria defined by the query are
returned. In this example, all piping items associated with line #01-L-L125-mEX-OPM are returned.
NOTE: The saved item queries exposed are defined in the i-model Data Source Configuration dialog
and only those which pertain to the opened i-model will be displayed.
Item queries can be created and saved (to an external file) using the Search tool delivered with theItems Browser. The Item Browser is delivered with a number of Bentley products, including
-
5/20/2018 Bentley I-model ODBC Driver
12/28
Bentley i-model ODBC Driver User Manual (- 12 -)
Bentley Navigator, i-model Composer and MicroStation.
Identification of Business Keys
A business key is a human readable tag that uniquely identifies items of a particular class within aproject. As an example, the NAME property of a PUMP item may be designated as the business key.
In this case, the NAME property would have a unique value for each PUMP item, e.g., P100, P101,etc.
Any property of a class can be designated as the business key and this information is stored asmetadata on the class, which in turn, is part of the schema that is embedded in the i-model. When
an i-model is loaded, this information is exposed as the BusinessKey table.
-
5/20/2018 Bentley I-model ODBC Driver
13/28
Bentley i-model ODBC Driver User Manual (- 13 -)
This table includes the names of all classes which have business keys defined, along with the namesof properties which have been designated as business keys. As an example, the NAME property is
defined as the business key for the PUMP class below.
NOTE: The assignment of business keys can be done either directly as part of the class definition or
added to it via a supplemental schema. In the latter case, the configuration/environment
DGN_HONOR_SUPPLEMENTAL_ECSCHEMA must be defined and assigned a non-blank value inorder for the information to be accessible.
-
5/20/2018 Bentley I-model ODBC Driver
14/28
Bentley i-model ODBC Driver User Manual (- 14 -)
Using the i-model ODBC Driver for Windows 7
With the i-model ODBC Driver for Windows 7, i-model business data are exposed as an ODBC datasource. Many applications support the ODBC data source natively. Check the applications manual to
see how to use ODBC data sources.
This document provides examples of using ODBC in:
Microsoft Excel
Microsoft Access
SAP Crystal Reports
VBA
-
5/20/2018 Bentley I-model ODBC Driver
15/28
Bentley i-model ODBC Driver User Manual (- 15 -)
Working with Microsoft Excel
You can import ODBC data to Excel using Excels Data > From Other Sources menu item. On this
menu, the two options you can use are From Data Connection Wizard and From Microsoft Query.Refer to the Excel Help topic "Connect to (import) external data"for more information.
Procedure to import ODBC data to Excel using the Connection Wizard:
1. From Excels Data menu, select From Other Sources > From Data Connection Wizard.
-
5/20/2018 Bentley I-model ODBC Driver
16/28
Bentley i-model ODBC Driver User Manual (- 16 -)
2. In the Data Connection Wizard that opens, select ODBC DSN and click Next.
3. In the ODBC data sources list window, select the i-model data source and click Next.
-
5/20/2018 Bentley I-model ODBC Driver
17/28
Bentley i-model ODBC Driver User Manual (- 17 -)
The Select i-model dialog opens.
4. Select an i-model file and click Open.
5. In the Data Connection Wizard dialog, select the database and table that contains the datayou want and click Next.
-
5/20/2018 Bentley I-model ODBC Driver
18/28
Bentley i-model ODBC Driver User Manual (- 18 -)
6. In the Data Connection Wizard dialog, enter a name and description for the new DataConnection file and click Finish.
7. In the Import Data dialog, select how the data should be view in the workbook and click OK.
-
5/20/2018 Bentley I-model ODBC Driver
19/28
Bentley i-model ODBC Driver User Manual (- 19 -)
8. The business data will be imported into the workbook.
-
5/20/2018 Bentley I-model ODBC Driver
20/28
Bentley i-model ODBC Driver User Manual (- 20 -)
Working with OBDC-Compliant Databases
Microsoft Access supports importing data from an ODBC database.
Procedure to import data from an i-model to Access:
1. From the Microsoft Access External Data menu, select More > ODBC Database.
-
5/20/2018 Bentley I-model ODBC Driver
21/28
Bentley i-model ODBC Driver User Manual (- 21 -)
2. In the Get External Data ODBC Database dialog, select the option: Import the source datainto a new table in the current database, and click OK.
3. In the Select Data Source dialog that opens, select the i-model data source and click OK.
-
5/20/2018 Bentley I-model ODBC Driver
22/28
Bentley i-model ODBC Driver User Manual (- 22 -)
4. In the Select i-model dialog, choose the i-model file and click Open.
5. In the Import Objects dialog, select the tables you would like to import and click OK.
-
5/20/2018 Bentley I-model ODBC Driver
23/28
Bentley i-model ODBC Driver User Manual (- 23 -)
6. The selected component data will be imported into the database.
-
5/20/2018 Bentley I-model ODBC Driver
24/28
Bentley i-model ODBC Driver User Manual (- 24 -)
Working with SAP Crystal Reports
Procedure to import data from an i-model to Crystal Reports:
1. Open Crystal Reports and in the Crystal Reports Gallery dialog, select Using the Report
Wizard.
2. In the Choose an Expert window, select Standard to open the Standard Report CreationWizard.
3. In this wizard, click ODBC (RDO) and in the dialog that opens, select the i-model datasource.
-
5/20/2018 Bentley I-model ODBC Driver
25/28
Bentley i-model ODBC Driver User Manual (- 25 -)
4. In the Select i-model dialog that opens; choose an i-model and click Open.
-
5/20/2018 Bentley I-model ODBC Driver
26/28
Bentley i-model ODBC Driver User Manual (- 26 -)
5. In the Standard Report Creation Wizard, select the data on which you want to report andclick Finish.
-
5/20/2018 Bentley I-model ODBC Driver
27/28
Bentley i-model ODBC Driver User Manual (- 27 -)
Using VBA for Automation
VBA is supported in many applications for automation. An ODBC data source can be accessed
through ADO using VBA. i-model ODBC Driver for Windows 7 can be accessed through i-model
datasource DSN configured during installation.To connect to i-model datasource DSN, you need to supply DSN with i-model name in the
connection string and construct your query text.
A typical connection string is similar to:
DSN=i-model datasource;imodel=C:\imodels\building.i.dgn;
Below is an excel example using VBA to import data from an i-model.
Sub ImportDataToActiveSheet()With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=i-model datasource;imodel=C:\imodefolder\building.i.dgn;", _
Destination:=Range("$A$1")).QueryTable.CommandText = "SELECT * FROM master_i_dgn.pds_01_00.PDS_EQUIP".SavePassword = False.Refresh
End WithEnd Sub
Developing Applications with ADO.net
.NET developers can use ADO.net to work with ODBC data sources. Refer to msdn ADO.netfor using
ADO.net.
A sample application showing how the business schema and data can be accessed from an i-modelvia ADO.net is shipped with i-model ODBC Driver for Windows 7. NOTE: The sample application
was created using Visual Studio 2008. If you are using an earlier version of Visual Studio, you
will need to recreate the project.
The connection string for i-model datasource DSN is:
DSN=i-model datasource;imodel=$your-i-mode-path-and-file;
An example is shown below:
stringconnectionString = "DSN= i-model datasource;imodel=C:\\imodels\\building.i.dgn;";connection = newOdbcConnection(connectionString);connection.Open ();DataSet ds = new DataSet ();
OdbcDataAdapteradapter = new OdbcDataAdapter(textBox1.Text, connection);adapter.Fill (ds);
BindingSourcebs = new BindingSource();bs.DataSource = ds;
bs.DataMember = ds.Tables[0].TableName;
dataGridView1.DataSource = bs;dataGridView1.Refresh ();
-
5/20/2018 Bentley I-model ODBC Driver
28/28
Bentley i-model ODBC Driver User Manual (- 28 -)
Legal Notices
Boost Software License - Version 1.0 - August 17th, 2003
Permission is hereby granted, free of charge, to any person or organization obtaining a copy
of the software and accompanying documentation covered by this license (the "Software")
to use, reproduce, display, distribute, execute, and transmit the Software, and to prepare
derivative works of the Software, and to permit third-parties to whom the Software isfurnished to do so, all subject to the following:
The copyright notices in the Software and this entire statement, including the above license
grant, this restriction and the following disclaimer, must be included in all copies of theSoftware, in whole or in part, and all derivative works of the Software, unless such copies or
derivative works are solely in the form of machine-executable object code generated by asource language processor.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,FITNESS FOR A PARTICULAR PURPOSE, TITLE AND NON-INFRINGEMENT. IN NO EVENT
SHALL THE COPYRIGHT HOLDERS OR ANYONE DISTRIBUTING THE SOFTWARE BE LIABLE
FOR ANY DAMAGES OR OTHER LIABILITY, WHETHER IN CONTRACT, TORT OR
OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE
USE OR OTHER DEALINGS IN THE SOFTWARE.
Portions Copyright Simba Technologies Inc.
Intergraph Raster File Formats Copyright (c) 1993 Intergraph Corporation.
top related