Page 1316
The transport_code is a single character used to specify the SQL*Net driver to be used (T for TCP/IP, X for IPX/SPX, and so on). The host_name is the name, alias, or network address of the server. The database argument is necessary only if more than one Oracle database exists on the host. In this case, the argument should be the system name of the database, as specified when the database was created with the CREATE DATABASE command. Consult the Oracle ODBC driver release notes and the SQL*Net documentation for further information on the SQL*Net Connect String.
For the purposes of this example, assume that the database resides on a UNIX host and will be accessed from the Windows workstation using TCP/IP. Enter ORACLE for the data source name, Oracle 7.1 for the description, and T:ORACLE_SERVER for the SQL*Net connect string. The dialog box should now look like the one shown in Figure 57.8.
Figure 57.8.
The Oracle7 ODBC
Setup dialog box
requires a name and a
SQL*Net Connect
String. The description
is optional.
The Options button enables the user to select a code page translator, assuming that a translator was installed with the driver. In most cases, no translation is necessary. Click OK to complete the data source setup. The new data source should appear in the Data Sources dialog box as shown in Figure 57.9.
Figure 57.9.
The Data Sources
dialog box shows that
the new Oracle7 Data
Source was successfully
added.
The data source is now fully configured and ready to be accessed by an application. Note that the setup routine in Figure 57.7 and Figure 57.8 is specific to the driver (Oracle 7.1 version 1.11.0002, in this case). This setup dialog will vary slightly from driver to driver, but it always requires similar information.
Page 1317
Before your application connects to the ODBC data source, some memory allocation and initialization must be performed. First, the application must call SQLAllocEnv, passing a pointer to memory allocated to store an environment handle. This handle is used to establish connections and for transaction processing. The application might need to establish more than one environment handle, but a single environment handle is usually sufficient, except in multithreaded environments.
Next, the application should call SQLAllocConnect, passing the previously established environment handle and a pointer to storage allocated for the connection handle. The driver manager allocates storage for connection information and associates the resulting connection handle with the environment handle. Multiple connections can be established for a single environment handle, but each connection can only be associated with a single environment. The connection handle is used to allocate statement handles and process embedded SQL transactions.
Finally, the application may call either SQLConnect or SQLDriverConnect, passing the connection handle instantiated by the call to SQLAllocConnect. The primary difference between these two functions is that SQLDriverConnect accepts a full connection string, rather than separate arguments for the data source name, userid , and password. This allows for additional database-specific parameters to be passed to the driver as part of the connection string. Additionally, SQLDriverConnect provides an argument used to define the behavior of the driver manager and a window handle to be used as the parent of the data sources dialog box (if one is presented). The arguments to SQLDriverConnect are, in order:
Page 1318
NOTE |
A typical connection string looks like this: DSN=ORACLE;UID= scotty ;PWD=tiger; Additional database-specific parameters may be provided, or the connection string may be partial, or empty, in which case the driver provides a dialog box requesting the information required to connect. If SQL_DRIVER_NOPROMPT is passed as the completion constant, the application must provide all required information in the connection string. |
C applications should include ODBC.H (or SQL.H and SQLEXT.H, depending on the compiler), which contains all function prototypes, data types, and constants available in the ODBC API. When using other development tools, the developer must provide prototypes for all ODBC functions used by the application. Listings 57.1 and 57.2 demonstrate connecting to an ODBC data source in C and Visual Basic, in a Microsoft Windows application context.
Listing 57.1. This C function establishes a single connection to a data source.
int ConnectToDataSource( HENV *hEnv, /* used to store the environment handle */ HDBC *hDBc) /* used to store the connection handle */ { UCHAR *szConnect; UCHAR szConnectOut[SQL_MAX_MESSAGE_LENGTH]; SWORD iConnectOutLen; RETCODE iError; szConnect = strdup("DSN=ORACLE;UID=scotty;PWD=tiger;"); iError = SQLAllocEnv(hEnv); if (iError == SQL_SUCCESS) iError = SQLAllocConnect(*hEnv, hDBc); if (iError == SQL_SUCCESS) iError = SQLDriverConnect(*hDBc, NULL, szConnect, SQL_NTS, szConnectOut, (SQL_MAX_MESSAGE_LENGTH - 1), &iConnectOutLen, SQL_DRIVER_NOPROMPT); return(iError); }
Listing 57.2. This Visual Basic function establishes a connection to a data source.
` include these prototypes in the module: Declare Function SQLAllocEnv Lib "odbc.dll" (hEnv As Long) As Integer
Page 1319
Declare Function SQLAllocConnect Lib "odbc.dll" (ByVal hEnv As Long, hDBc As Long) As Integer Declare Function SQLDriverConnect Lib "odbc.dll" (ByVal hDBc As Long, ByVal hWnd As Integer, ByVal szCSin As String, ByVal iCSinLen As Integer, ByVal szCSOut As String, ByVal iCSOutMaxLen As Integer, iCSOutLen As Integer, ByVal iDriverComplete As Integer) As Integer ` also define these constants: Global Const SQL_SUCCESS = 0 Global Const SQL_SUCCESS_WITH_INFO = 1 Global Const SQL_STILL_EXECUTING = 2 Global Const SQL_NEED_DATA = 99 Global Const SQL_NO_DATA_FOUND = 100 Global Const SQL_ERROR = 1 Global Const SQL_INVALID_HANDLE = 2 Global Const SQL_NTS = 3 Global Const SQL_DRIVER_NOPROMPT = 0 Global Const SQL_MAX_MESSAGE_LENGTH = 512 Function ConnectToDataSource(hEnv As Long, hDBc As Long) As Integer Dim szConnect As String Dim szConnectOut As String Dim iConnectOutLen As Integer Dim iError As Integer szConnectOut = Space$(SQL_MAX_MESSAGE_LENGTH) szConnect = "DSN=ORACLE;UID=scotty;PWD=tiger;" iError = SQLAllocEnv(hEnv) If (iError = SQL_SUCCESS) Then iError = SQLAllocConnect(hEnv, hDBc) End If If (iError = SQL_SUCCESS) Then iError = SQLDriverConnect(hDBc, 0, szConnect, SQL_NTS, szConnectOut, (SQL_MAX_MESSAGE_LENGTH - 1), iConnectOutLen, SQL_DRIVER_NOPROMPT) End If ConnectToDataSource = iError End Function