| Previous | Table of Contents | Next |
ODBC Setup, Usage, and Limitations
First-time users will find the following section on setup and usage useful in getting ODBC configured to access Oracle8.
Oracle8 ODBC Setup
There are two basic steps in installing a working ODBC driver for Oracle8. The first is the installation and configuration of Oracle’s Net8, which is covered in Chapter 3, “Oracle8 Network Computing,” and the second is the installation and configuration of the ODBC software for Oracle8.
Installing Oracle8 ODBC Drivers
As mentioned previously, ODBC drivers for Oracle can be downloaded from the Oracle Web site for free. After downloading the file, double-click on the file to unZip it and read the readme.txt file located in the directory you have unzipped the file in. It will guide you through installing the software via Oracle’s Installer.
Configuring Oracle8 ODBC Drivers
The following are the steps for configuring Oracle8 ODBC drivers:
- 1. In Windows NT or 95, double-click the 32-bit Administrator icon from the Control Panel or in the Oracle for Windows NT/95 folder, as shown in Figure 26.2.
Figure 26.2. Accessing the ODBC Administrator.- 2. In the User DSN (Data Source Name) or the System DSN, choose the Add button, as shown in Figure 26.3.
Figure 26.3. Adding the ODBC Data Source Name.
NOTE:
Use the System DSN tab if you are the sole user of your machine (that is, no one else has a separate Windows login name and password) or you want everyone who uses your machine to have access to these ODBC configurations. Otherwise, specify the ODBC data source in the User DSN tab.
- 3. In the list of Data Drivers, select Oracle ODBC Driver and choose the Finish button as shown in Figure 26.4.
Figure 26.4. Create a new ODBC Data Source.- 4. The Oracle8 ODBC Driver Setup dialog box is displayed as illustrated in Figure 26.5. In the Oracle8 ODBC Setup dialog box, enter the following:
Figure 26.5. ODBC Driver Setup.- • Data Source Name:The name that will be displayed to anyone who wants to use this ODBC configuration to connect to the data source you are setting up.
- • Description: A full description of the data source you are connecting to. This description will be displayed when selecting this data source in the future.
- • Service Name: For Oracle, this is usually referred to as the Host String or Connect String. It is the name for the Oracle database found in the TNSNAMES.ORA file or on the Oracle Names server, which is used by Net8 to find the correct Oracle database. You can set up this name in the Oracle Net8 Assistant.
- • UserID: (Optional) You can specify the user ID of the Oracle database user here. If not specified, you will be prompted for the username when you connect through this ODBC connection.
- • Connect to Database in Read-only Mode: If selected, data can only be read via this ODBC connection.
- • Prefetch Count: This defines how many records will be read from the database at a time. The performance implications of this option are discussed in the ODBC Optimization section later in this chapter.
- • Option: Not supported as of Oracle V8.0.3.
- • Library:Not supported as of Oracle V8.0.3.
- • Description: A full description of the data source you are connecting to. This description will be displayed when selecting this data source in the future.
- 5. At this point your ODBC connection is configured. Click the OK button and the data source should appear in the Data Source Administrator main window, as depicted in Figure 26.6.
Figure 26.6. Data Source Administrator window. - 2. In the User DSN (Data Source Name) or the System DSN, choose the Add button, as shown in Figure 26.3.
)
)
)
)
)
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
Connecting to Oracle8
The following is a code segment from a Visual Basic program that connects to a database as user Scott with password Tiger and the ODBC data source Customer Database (as specified in Figure 26.5). It then issues a query through ODBC to get all the usernames from the database we have specified previously and display them (one at a time) in a message box.
Dim oraDb As Database
Dim oraRset As Recordset
Set oraDb = OpenDatabase(ODBC, False, False, _”ODBC;DSN=Customer Database;
UID=SCOTT;PWD=TIGER”)
Set oraRset = oraDb.OpenRecordset(“select username from all_users”,
dbOpenDynaset)
While Not oraRset.EOF
MsgBox oraRset(1)
oraRset.MoveNext
Wend
Oracle ODBC Driver Capabilities and Limitations (V8.0.3)
Although most capabilities of ODBC and Oracle8 are mutually supported, there are some limitations, as outlined in the next sections.
ODBC Functions Not Supported
- • SQLExtendedFetch Only FETCH_NEXT is available.
- • SQLMoreResults The Oracle database does not support multiple result sets, therefore more results are not available. SQL_NO_DATA_FOUND will always be returned by this function.
- • SQLBrowseConnect Not currently supported.
- • SQLSetPos Usage support can be achieved through the Microsoft ODBC cursor library.
- • SQLMoreResults The Oracle database does not support multiple result sets, therefore more results are not available. SQL_NO_DATA_FOUND will always be returned by this function.
SQL Syntax Not Supported
- • WHERE CURRENT OF CURSOR-NAME Not supported for a positioned DELETE or UPDATE statement.
- • DROP TABLE, DROP VIEW, and REVOKE These statements cannot specify the CASCADE and RESTRICT clauses.
NOTE:
DATE Datatype Because the ODBC driver maps the DATE datatype to the SQL_TIMESTAMP datatype, it always returns both the date and time parts of a DATE column.
Oracle8 ODBC Optimization and Troubleshooting
Due to the subjective nature of performance tuning, information in the following sections has been grouped based on the type of operations being performed.
| Previous | Table of Contents | Next |