Using Databases with ODBC and MicroStation/J FAQ

Index:

1. What is ODBC?
2. What ODBC connections does MicroStation/J support?
3. How do I obtain ODBC drivers?
4. How do I create a data source?
5. Are screen forms supported in ODBC?
6. Are there any special considerations for interfacing with Xbase?
7. Are there any special considerations for interfacing with SQL Server?

Answer: Microsoft's Open Database Connectivity (ODBC) interface lets applications, through database drivers, access data in a variety of database management systems (DBMS). This allows users to evaluate their own database environment, and add ODBC database drivers without building a customized MicroStation interface.

MicroStation/J provides a rewritten database server with an ODBC interface. The MicroStation/J ODBC server passes requests through an ODBC database driver to the underlying DBMS. MicroStation does NOT deliver the ODBC database driver(s). The user is responsible for purchasing or downloading ODBC database drivers for the DBMSs that need to be accessed. There are several companies that provide ODBC drivers. These were evaluated before development was started and the initial development and testing has been done with Intersolv and Microsoft drivers (and Oracle drivers for an Oracle ODBC connection).

It is extremely important that the latest version 3.51.x of the Microsoft ODBC drivers be used with MicroStation/J v7.0 (all versions). MicroStation/J v7.1 (version 07.01.00.62 and higher) has been certified with ODBC version 4.00.4202.00. Previously functioning database connections established with other versions of the Microsoft ODBC drivers will no longer operate correctly. A link to the Microsoft web site is provided under Question #3 in this FAQ.

Question 2: What ODBC connections does MicroStation/J support?

Answer:

For a complete listing of databases supported in MicroStation/J see:
selectservices.bentley.com/technotes/faqs/6172.htm ­ Using Databases with MicroStation/J FAQ

Question 3:How do I obtain ODBC drivers?

MicroStation/J v7.1:
The Microsoft 4.00.4202.00 32-bit Desktop Driver Pack is available at http://www.microsoft.com/data/download_21242023.htm. Please note that these are not the same ODBC drivers that are installed with Microsoft Office 2000.

MicroStation/J v7.0:

The Microsoft ODBC 3.51.1029 (or higher) 32-bit Desktop Driver Pack is available on the Microsoft web site at:

http://www.microsoft.com/msdownload/uda/mdac_typ.asp

To install it on Windows 95 or Windows NT you may need to have a valid Microsoft 32 bit application already installed.

Intersolv DataDirect ODBC drivers can be ordered from Intersolv at 1-800-876-3101 or downloaded from their web site at http://www.intersolv.com

Question 4: How do I create a data source in ODBC?

Answer: ODBC uses a data source to maintain the connection information for a database. MicroStation uses an ODBC data source name for the connection string in the Settings > Database > Connect dialog box. The ODBC driver(s) must be loaded before data sources can be created. After the ODBC drivers have been installed (refer to your ODBC driver documentation) open the ODBC driver administrator by double-clicking on the ODBC icon from the Control Panel. Click the Add button, then select the ODBC Driver for the DBMS that contains your data. The data source definition screen will appear. Define the data source name (Note: this is the string you will use in the MicroStation connect string) and other information as it pertains to your configuration.

Oracle
In the ODBC setup Oracle, "Server Name" is the Oracle remote connection string. If you are connecting remotely to an Oracle server enter the Oracle remote connection string, for example T:MY_SERVER:DEMO. If the database is local leave this field blank.

Question 5: Are screen forms supported in ODBC?

Answer: MicroStation/J supports screen forms in the ODBC interface through the Visual SQL Query Builder for all supported databases. Oracle and Informix forms are also supported but require additional database software and configuration. VSQL (Visual SQL Query Builder) will create a data display/entry form that may be saved and used for future query and updates. The form contains all of the database query and review buttons as well as linkage attach, detach, review and locate functions.

Question 6: Are there any special considerations for interfacing with Xbase?

Answer: In MicroStation/J, the Xbase interface is supported via an ODBC connection. The ODBC server does not use a data dictionary, so the user needs to be aware of certain Xbase restrictions:

• File names cannot be larger than 8 characters
• MSCATLOG is the name of the mscatalog under the ODBC Xbase interface. If you try to operate on the mscatalog table using MSCATALOG as the table name, you will get ODBC Error 4101, `The connection, statement, or query handle you provided is not valid'.
• In the GIS example, the PARCEL_DAS table is called PARD.GIS. Pushpin and Parcel Locate will not execute properly due to a dependency on the name of the mscatalog.
• Column names can not be larger than 10 characters. This is the same restriction that was in the Version 5 MicroStation Xbase interface. However in the ODBC interface, instead of truncating greater than 10 character column names, the library functions return an error.
• MDX index files can not contain expressions. The delivered Xbase GIS example index files (and possibly some of your index files) have the expression `FOR.NOT.DELETED()'. The Intersolv ODBC Xbase driver can not access information in a table that has an associated index file that contains a expression. The expressions must be removed. This should be fixed in future Intersolv ODBC driver releases, though no date has been given.

Answer: SQL Server treats NULLs in a non-ANSI manner. While ANSI standards specify that a column by default can accept NULL values, in SQL Server you must specify a column as NULL in order for it to accept NULL values. The NOT NULL option on columns works as expected. This can lead to problems with active entity creation. If a column in the active entity table does not have a value, the active entity cannot be inserted into a table unless the create table statement defined the no value column with the NULL option. The The SQL Server Edit Database Options tab will override this setting, if the "Columns Null by Default" is turned on.

6173