Frequently Asked Questions (FAQ) --------------------------------------------------------------------------- Subject: ODBC General Questions Product: MicroStation 95 ® Document Number: 6128 Last updated: Wednesday, December 31, 1996 --------------------------------------------------------------------------- Purpose The purpose of the document is to address some of the frequently asked questions concerning using MicroStation 95 and Microsoft ODBC database interface. --------------------------------------------------------------------------- Index: 1. What is ODBC? 2. What ODBC connections does MicroStation 95 support? 3. How do I create a data source? 4. Are screen forms supported in ODBC? 5. Are there any special considerations for interfacing with Xbase? 6. Are there any special considerations for interfacing with SQL Server? 7. How do I install 32 bit ODBC interface over existing 16 bit MicroStation applications? 8. What files are installed for MicroStation's ODBC interface? --------------------------------------------------------------------------- Question 1: What is ODBC? Answer:Microsoft's Open Database Connectivity (ODBC) interface allows applications, through database drivers, access to data in a variety of database management systems (DBMS). This allows users to evaluate their own database environment and add ODBC database drivers without the need for changes to the ODBC enabled application. MicroStation 95 provides a database server written with the ODBC interface. The MicroStation 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 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. Question 2: What ODBC connections does MicroStation 95 support? For a complete listing of databases supported in MicroStation 95 see: http://www.bentley.com/tools/faqs/6115.htm - Using Databases with MicroStation 95 FAQ. Answer: MicroStation currently supports ODBC connections: Windows 3.11 Intersolv 2.0 Driver pack (16 Bit):- Oracle 6 & 7, SQL Server, Xbase*, Informix Microsoft 2.0 Desktop Driver Pack (16 Bit):- Access Windows NT 3.51 Intersolv 2.0 Driver pack (32 Bit):- Oracle 6 & 7, SQL Server, Xbase*, Informix Microsoft 2.0/3.x Desktop Driver Pack (32 Bit):- Access Note: Windows NT 3.51 must be installed to use Microsoft Access. There is an OLE conflict with Windows NT 3.5. When you start MicroStation 95 it initializes OLE. When you try to use ODBC it also tries to initialize OLE. This is resolved in Microsoft Windows NT 3.51. Windows 95 Intersolv 2.0 Driver pack (32 Bit):- Oracle 6 & 7, SQL Server, Xbase*, Informix Microsoft 3.x Desktop Driver Pack (32 Bit):- Access * Refers to dBase IV, dBase III+ and FoxPro The Microsoft ODBC 3.x 32 bit drivers are available on the Microsoft web site. http://www.microsoft.com/kb/softlib/mslfiles/ge1263.exe They can be installed on Windows 95 and Windows NT 3.51. To install them on Windows NT 3.51 you need to have a valid Microsoft 32 bit application already installed. Question 3: How do I create a data source? Answer: ODBC uses a data source, similar to a RIS schema, to maintain the connection information for a database. MicroStation uses an ODBC data source name for the connection string, i.e. `DB=`or in the new Settings Database Connect 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. Select 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 `DB=` statement) and other information as it pertains to your configuration. Several fields need specific settings, these are supplied below. Refer to your ODBC driver manual for specifics on other fields. Xbase `Locking' is the level of locking on a dbf file. The locking field should be set to `File'. This will insure that with multiple users, active entities will not collide. If your index files are the same name as your dbf files, then you do NOT have to hit the `define' button to associate the index file to the dbf. Oracle `Server Name' is the Oracle remote connection string. If you are connecting remotely to an Oracle server enter the Oracle remote connection string, i.e. T:MY_SERVER:DEMO. If the database is local leave this field blank. Question 4: Are screen forms supported in ODBC? Answer:MicroStation 95 supports screen forms in ODBC interface through the use of a MicroStation Basic macro that communicates with your chosen screen forms manager through DDE. An example macro that uses Microsoft Access is described in Bentley Tech-note #8101: Using Microsoft Access 2.0/7.0 Forms with MicroStation 95 Question 5: Are there any special considerations for interfacing with Xbase? Answer:In V5 MicroStation, the Xbase interface used a data dictionary to force the interface into a SQL paradigm. The ODBC server does not use a data dictionary, so the user needs to be aware of certain Xbase restrictions: * File names can not 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 just 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. Question 6: Are there any special considerations for interfacing with SQL Server? Answer: * SQL Server does not recognize some ANSI SQL data types such as number, numeric, decimal, integer, logical. The following data types ARE recognized: money, smallmoney, int, smallint, tinyint, char(n), varchar(n), text, datetime, smalldatetime, bit, timestamp, sysname, float, real, binary(n), varbinary(n), image * 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 can not be inserted into a table unless the create table statement defined the no value column with the NULL option. * The Intersolv ODBC SQL Server driver does not support SQL statement over 2400 characters at this time. Question 7: How do I install 32 bit ODBC interface over existing 16 bit MicroStation applications. Answer:If you install the 32 bit version of MicroStation 95 and the ODBC interface under Windows 95 where the 16 bit version of MicroStation had already been installed, then the 32 bit version of the 'qelib.dll' (called qelibnt.dll in your \ustation\database\odbc directory) will not be copied into the system directory. This is due to the fact that the 16 bit version of the file (also called 'qelib.dll') already exists in the directory. To solve this, the user must copy the \ustation\database\odbc\qelibnt.dll to the system directory, renaming it 'qelib.dll'. This will overwrite the 16 bit version of the 'qelib.dll'. Since the 'qelib.dll' has the same name for both the 16 bit and 32 bit versions of the DLLs, both the 16 bit and 32 bit versions of MicroStation 95 and the ODBC interface can not exist on the same machine unless they are installed under different operating systems with different system directories. The error in the command window is 'Unsuccessful Connect Statement'. This situation can also occur when users already have Intersolv (Q+E) libraries installed or have copied DLLs. Question 8: What files are installed for MicroStation's ODBC interface? Answer:The /ustation/database/odbc directory contains the following DLLs: 32 Bit ODBC.DLLs QESQL04.DLL 425,472 12/05/95 QELIBNT.DLL 203,264 12/05/95 QEUTL04.DLL 169,984 12/05/95 ODBC32.DLL 67,584 12/05/95 CTL3D32.DLL 27,648 12/05/95 QEMDS04.DLL 16,896 12/05/95 16 Bit ODBC.DLLs QESQL03.DLL 293,648 12/05/95 QELIB.DLL 145,376 12/05/95 QEUTL03.DLL 90,688 12/05/95 ODBC.DLL 55,792 12/05/95 QEGUI03.DLL 14,512 12/05/95 QEMDS03.DLL 5,488 12/05/95 When installing the 16 bit version of MicroStation, the 16 bit DLLs are copied into the /windows/system directory. When installing the 32 bit version of MicroStation, the 32 bit DLLs are copied into the /winnt35/system32 under Windows NT 3.5x or into the /windows/system directory under Windows 95. In the 32 bit case, 'qelibnt.dll' is renamed to 'qelib.dll' when it is copied into the system directory. --------------------------------------------------------------------------- Where to find updated versions of this, and other, Bentley Technical FAQ: WWW: http://www.bentley.com/tools/ Anonymous FTP: connect to ftp.bentley.com and go to /pub/help/faqs/ directory Compuserve: MicroStation Forum (!GO MSTATION) Fax: Dial +1-(610)-458-6485 from the handset of your fax machine, then request an index of available documents. If you have any other questions concerning this Technical FAQ, please contact your MVAR or Bentley Support at: Americas Europe Asia - Pacific Email: support@bentley.com support@bentley.nl support@bentley.com.au Phone: +1-(610)-458-5999 +31 23 5560 555 +61 3 9699 8699 Fax: +1-(610)-458-1056 +31 23 5560 556 +61 3 9699 8677 For additions/suggestions about this Technical Bulletin, please contact kenneth.cooper@bentley.com. --------------------------------------------------------------------------- Copyright 1996 by Bentley Systems, Incorporated.